From 944de7874e237558129e33a7b8912a4bd73aa376 Mon Sep 17 00:00:00 2001 From: Constantin Michael Date: Mon, 18 Jul 2011 14:35:45 +0200 Subject: Add PostgreSQL database chapter --- doc/manual.xhtml | 587 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 587 insertions(+) diff --git a/doc/manual.xhtml b/doc/manual.xhtml index a06bfa6..f7c8238 100644 --- a/doc/manual.xhtml +++ b/doc/manual.xhtml @@ -509,6 +509,28 @@ for consistency. + 13PostgreSQL Database + + + + + + + + +
13.1PostgreSQL Type Mapping
13.2PostgreSQL Database Class
13.3PostgreSQL Connection Factory
13.4PostgreSQL Exceptions
13.5PostgreSQL Limitations + + + + + + +
13.5.1Query Result Caching
13.5.2Foreign Key Constraints
13.5.3Date-Time Format
13.5.4Timezones
13.5.5NUMERIC Type Support
+
+ + + + PART III PROFILES @@ -8019,6 +8041,571 @@ class person limitation.

+ + + +
+

13 PostgreSQL Database

+ +

To generate support code for the PostgreSQL database you will need + to pass the "--database pgsql" + (or "-d pgsql") option to the ODB compiler. + Your application will also need to link to the PostgreSQL ODB runtime + library (libodb-pgsql). All PostgreSQL-specific ODB + classes are defined in the odb::pgsql namespace.

+ +

ODB utilizes prepared statements extensively. PostgreSQL support for + prepared statements begins with version 7.4 with the introduction + of version 3.0 of the PostgreSQL messaging protocol. For this reason, + ODB supports only PostgreSQL versions 7.4 and later.

+ +

13.1 PostgreSQL Type Mapping

+ +

The following table summarizes the default mapping between basic + C++ value types and PostgreSQL database types. This mapping can be + customized on the per-type and per-member basis using the ODB + Pragmas Language (Chapter 10, "ODB Pragma + Language").

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
C++ TypePostgreSQL Type
boolBOOLEAN NOT NULL
charSMALLINT NOT NULL
signed charSMALLINT NOT NULL
unsigned charSMALLINT NOT NULL
shortSMALLINT NULL
unsigned shortSMALLINT NOT NULL
intINTEGER NOT NULL
unsigned intINTEGER NOT NULL
longBIGINT NOT NULL
unsigned longBIGINT NOT NULL
long longBIGINT NOT NULL
unsigned long longBIGINT NOT NULL
floatREAL NOT NULL
doubleDOUBLE PRECISION NOT NULL
std::stringTEXT NOT NULL
+ +

Note that unsigned short, unsigned int, and + unsigned long long are mapped to SMALLINT, + INTEGER, and BIGINT respectively. The sign bit + of the value stored by the database for these types will contain the most + significant bit of the actual unsigned value being persisted.

+ +

Additionally, by default, C++ enumerations are automatically + mapped to INT NOT NULL.

+ +

13.2 PostgreSQL Database Class

+ +

The PostgreSQL database class has the following + interface:

+ + 
+namespace odb
+{
+  namespace pgsql
+  {
+    class database: public odb::database
+    {
+    public:
+      database (const std::string& db,
+                const std::string& user,
+                const std::string& password,
+                const std::string& host = "",
+                unsigned int port = 0,
+                const std::string& extra_conninfo = "",
+                std::auto_ptr<connection_factory> = 0);
+
+      database (const std::string& db,
+                const std::string& user,
+                const std::string& password,
+                const std::string& host = "",
+                const std::string& socket_ext = "",
+                const std::string& extra_conninfo = "",
+                std::auto_ptr<connection_factory> = 0);
+
+      database (const std::string& conninfo,
+                std::auto_ptr<connection_factory> = 0);
+
+      database (int& argc,
+                char* argv[],
+                bool erase = false,
+                const std::string& extra_conninfo = "",
+                std::auto_ptr<connection_factory> = 0);
+
+      static void
+      print_usage (std::ostream&);
+
+
+    public:
+      const std::string&
+      host () const;
+
+      const std::string&
+      db () const;
+
+      const std::string&
+      user () const;
+
+      const std::string&
+      password () const;
+
+      unsigned int
+      port () const;
+
+      const std::string&
+      socket_ext () const;
+
+      const std::string&
+      extra_conninfo () const;
+
+      const std::string&
+      conninfo () const;
+
+    public:
+      details::shared_ptr<pgsql::connection>
+      connection ();
+    };
+  }
+}
+
+ +

You will need to include the <odb/pgsql/database.hxx> + header file to make this class available in your application.

+ +

The overloaded database constructors allow us to specify + the PostgreSQL database parameters that should be used when connecting + to the database. Both the first and second constructors have explicit + arguments for db, user, password, + and host. These arguments correspond to the + dbname, user, password, and + host keywords, respectively, of the conninfo + argument of the libpq function PQconnectdb. + For more information regarding the format of the conninfo + string, refer to the description of the libpq function + PQconnectdb in the PostgreSQL documentation.

+ +

The port argument of the first constructor takes an + integer value specifying the port number to connect to at the server host + when utilizing TCP/IP sockets. It corresponds to the port + keyword of the conninfo string. Zero specifies the default + port and is equivalent to omitting the port option from the + conninfo string.

+ +

The socket_ext argument of the second constructor takes a + string value specifying the socket file name extension for Unix-domain + socket connections when utilizing Unix-domain connections.

+ +

The third constructor allows us to specify all database parameters + via a single conninfo string.

+ +

The last constructor extracts the database parameters + from the command line. The following options are recognized:

+ + 
+  --user <login> | --username <login>
+  --password <password>
+  --database <name> | --dbname <name>
+  --host <host>
+  --port <integer>
+  --options-file <file>
+
+ +

The --options-file option allows us to specify some + or all of the database options in a file with each option appearing + on a separate line followed by space and an option value.

+ +

If the erase argument to this constructor is true, + then the above options are removed from the argv + array and the argc count is updated accordingly. + This is primarily useful if your application accepts other + options or arguments and you would like to get the PostgreSQL + options out of the argv array.

+ +

This constructor throws the odb::pgsql::cli_exception + exception if the PostgreSQL option values are missing or invalid. + See section Section 13.4, "PostgreSQL Exceptions" + for more information on this exception.

+ +

The static print_usage() function prints the list of options + with short descriptions that are recognized by this constructor.

+ +

All the constructors accept additional database connection parameters + via the conninfo or extra_conninfo argument. + The format of this string follows that of the conninfo + argument to the libpq function PQconnectdb. + All database parameters provided via this argument will take preference + over those explicitly specified by a constructor argument.

+ +

The last argument to all of the constructors is a pointer to the + connection factory. If we pass a non-NULL value, the + database instance assumes ownership of the factory instance. The + connection factory interface as well as the available implementations + are described in the next section.

+ +

The set of accessor functions following the constructors allows us + to query the parameters of the database instance. Note that + the conninfo() accessor returns a reference to a string + containing the full conninfo string passed to the database, including + explicitly specified constructor arguments, as well as extra parameters + specified by the extra_conninfo argument (if provided). The + extra_conninfo() accessor will return a reference to a copy + of the string passed as the extra_conninfo constructor + argument if it was provided. Otherwise, it will return a reference to + an empty string.

+ +

The connection() function returns the PostgreSQL database + connection encapsulated by the odb::pgsql::connection + class. Normally, we wouldn't call this function directly and + instead let the ODB runtime manage the database connections. However, + if for some reason you need to access the underlying PostgreSQL connection + handle, refer to the PostgreSQL ODB runtime source code for the interface + of the connection class.

+ +

13.3 PostgreSQL Connection Factory

+ +

The connection_factory abstract class has the + following interface:

+ + 
+namespace odb
+{
+  namespace pgsql
+  {
+    class connection_factory
+    {
+    public:
+      virtual void
+      database (pgsql::database&) = 0;
+
+      virtual details::shared_ptr<pgsql::connection>
+      connect () = 0;
+    };
+  }
+}
+
+ +

The database() function is called when a connection + factory is associated with a database instance. This happens in + the odb::pgsql::database class constructors. The + connect() function is called whenever a database + connection is requested.

+ +

The two implementations of the connection_factory + interface provided by the PostgreSQL ODB runtime are + new_connection_factory and + connection_pool_factory. You will need to include + the <odb/pgsql/connection-factory.hxx> + header file to make the connection_factory interface + and these implementation classes available in your application.

+ +

The new_connection_factory class creates a new + connection whenever one is requested. When a connection is no + longer needed, it is released and closed. The + new_connection_factory class has the following + interface:

+ + 
+namespace odb
+{
+  namespace pgsql
+  {
+    class new_connection_factory: public connection_factory
+    {
+    public:
+      new_connection_factory ();
+    };
+};
+
+ +

The connection_pool_factory class implements a + connection pool. It has the following interface:

+ + 
+namespace odb
+{
+  namespace pgsql
+  {
+    class connection_pool_factory: public connection_factory
+    {
+    public:
+      connection_pool_factory (std::size_t max_connections = 0,
+                               std::size_t min_connections = 0)
+    };
+};
+
+ +

The max_connections argument specifies the maximum + number of concurrent connections that this pool factory will + maintain. Similarly, the min_connections argument + specifies the minimum number of available connections that + should be kept open.

+ +

Whenever a connection is requested, the pool factory first + checks if there is an unused connection that can be returned. + If there is none, the pool factory checks the + max_connections value to see if a new connection + can be created. If the total number of connections maintained + by the pool is less than this value, then a new connection is + created and returned. Otherwise, the caller is blocked until + a connection becomes available.

+ +

When a connection is released, the pool factory first checks + if there are blocked callers waiting for a connection. If so, then + one of them is unblocked and is given the connection. Otherwise, + the pool factory checks whether the total number of connections + maintained by the pool is greater than the min_connections + value. If that's the case, the connection is closed. Otherwise, the + connection is added to the pool of available connections to be + returned on the next request. In other words, if the number of + connections maintained by the pool exceeds min_connections + and there are no callers waiting for a new connection, + the pool will close the excess connections.

+ +

If the max_connections value is 0, then the pool will + create a new connection whenever all of the existing connections + are in use. If the min_connections value is 0, then + the pool will never close a connection and instead maintain all + the connections that were ever created.

+ +

If you pass NULL as the connection factory to one of the + database constructors, then the + connection_pool_factory instance will be created by default + with the min and max connections values set to 0. The + following code fragment shows how we can pass our own connection factory + instance:

+ + 
+#include <odb/database.hxx>
+
+#include <odb/pgsql/database.hxx>
+#include <odb/pgsql/connection-factory.hxx>
+
+int
+main (int argc, char* argv[])
+{
+  auto_ptr<odb::pgsql::connection_factory> f (
+    new odb::pgsql::connection_pool_factory (20));
+
+  auto_ptr<odb::database> db (
+    new pgsql::database (argc, argv, false, "", f));
+}
+
+ +

13.4 PostgreSQL Exceptions

+ +

The PostgreSQL ODB runtime library defines the following + PostgreSQL-specific exceptions:

+ + 
+namespace odb
+{
+  namespace pgsql
+  {
+    class database_exception: odb::database_exception
+    {
+    public:
+      unsigned int
+      error () const;
+
+      const std::string&
+      message () const;
+
+      const std::string&
+      sqlstate () const;
+
+      virtual const char*
+      what () const throw ();
+    };
+
+    class cli_exception: odb::exception
+    {
+    public:
+      virtual const char*
+      what () const throw ();
+    };
+  }
+}
+
+ +

You will need to include the <odb/pgsql/exceptions.hxx> + header file to make these exceptions available in your application.

+ +

The odb::pgsql::database_exception is thrown if + a PostgreSQL database operation fails. The PostgreSQL-specific error + information is accessible via the error(), + message() and sqlstate() functions. All this + information is also combined and returned in a human-readable form by + the what() function.

+ +

The odb::pgsql::cli_exception is thrown by the + command line parsing constructor of the odb::pgsql::database + class if the PostgreSQL option values are missing or invalid. The + what() function returns a human-readable description + of an error.

+ +

13.5 PostgreSQL Limitations

+ +

The following sections describe PostgreSQL-specific limitations imposed + by the current PostgreSQL and ODB runtime versions.

+ +

13.5.1 Query Result Caching

+ +

The PostgreSQL ODB runtime implementation will always perform query result + caching (Section 4.4, "Query Result") even when + explicitly requested not to. This is a result of libpq + behaviour, specifically that control is only returned to the client + once all the results of a query are cached.

+ +

13.5.2 Foreign Key Constraints

+ +

ODB relies on deferred checking of foreign key constraints. That is to + say, ODB requires that foreign key constraints be checked when the + containing transaction is committed. Default PostgreSQL behaviour is + to check constraints immediately. Any schema that defines a foreign + key constraint must specify the constraint as + INITIALLY DEFERRED in order for ODB to behave correctly, + as shown in the following example:

+ +
+CREATE TABLE employee (
+  name TEXT,
+  employer BIGINT REFERENCES employer INITIALLY DEFERRED);
+
+ +

13.5.3 Date-Time Format

+ +

ODB is only compatible with PostgreSQL servers that represent date-time + types using 64-bit integers. On creation of every connection, ODB + examines the server parameter integer_datetimes. If it is + not set to on, the + odb::pgsql::database_exception is thrown. You may check the + parameter value for your server by executing the SQL statement + SHOW integer_datetimes.

+ +

13.5.4 Timezones

+ +

ODB does not currently support date-time types with timezone + information.

+ +

13.5.5 NUMERIC Type Support

+ +

Support provided for the PostgreSQL NUMERIC type is limited + to provision of the raw binary data, as received from the PostgreSQL + server, to the user. The binary data may be accessed as shown in the + following example:

+ + 
+#include <odb/pgsql/traits.hxx>
+
+struct numeric
+{
+  std::size_t len;
+  char* data;
+};
+
+template <>
+struct value_traits<numeric, odb::pgsql::id_numeric>
+{
+  static void
+  set_value (numeric& v,
+             const odb::details::buffer& b,
+             std::size_t n,
+             bool is_null)
+  {
+    if (!is_null)
+    {
+      v.len = n;
+      v.data = new char[n];
+      std::memcpy (v.data, b.data (), n);
+    }
+  }
+
+  static void
+  set_image (odb::details::buffer& b,
+             std::size_t& n,
+             bool& is_null
+             numeric v)
+  {
+    is_null = false;
+    n = v.len;
+
+    if (n > b.capacity ())
+      b.capacity (n);
+
+    if (n != 0)
+      std::memcpy (b.data (), v.data, n);
+  }
+};
+
+ +

Refer to the PostgreSQL documentation for more information regarding + the binary format used to represent the NUMERIC type.

+ -- cgit v1.1