Developer's Guide
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Macros Groups Pages
Database connector object

Introduction

P6R's database component provides one simple interface to any number of commercial available databases. Currently, SQLite3 and Postgres8 are supported, but anyone can extended this support by building plugin database connectors (see next section).

In addition, the P6R database component does not restrict a set of specific SQL statements that are available across all database backends. This means that when calling a SQLite3 database, for example, the SQL statements used should/can be specific to the SQL implementation in SQLite. The caller has the option to avoid SQL extensions made by each specific database or the caller can make use of these different implemenations as they seem fit.

Includes

Interfaces

Postgres and standard SQL

Postgres uses a positional notation for bind variables (e.g., INSERT INTO tableA (a, b, c) VALUES( $1, $2, $3) ). However, other databases, like SQLite, use question marks instead (e.g., INSERT INTO tableA (a, b, c) VALUES( ?, ?, ?) ). To make it easy to use one set of SQL queries interchangeable between database backends this component will translate the use of question marks ('?') into positional notation ('$1') when using postgres. Thus, for example, internally the query INSERT INTO tableA (a, b, c) VALUES( ?, ?, ?) is translated by the P6R database component into the query INSERT INTO tableA (a, b, c) VALUES( $1, $2, $3). This translation will happen in any query using bind variables via the prepare() method. The user can still use the original Postgress query with the postitional notation (e.g., INSERT INTO tableA (a, b, c) VALUES( $1, $2, $3)) if desired.

Postgress does not have a "blob" type (SQLite and Mysql both support blob types). Instead Postgres defines a binary data types(e.g., "bytea" ) [http://www.postgresql.org/docs/8.1/static/datatype-binary.html]. Again, to support the use of interchangeable SQL queries, the P6R database component will translate all "blob" types into the Postgres "bytea" type. The user can still use the original Postgress bytea type if desired.

In addition, P6R's SQL translation will take a query like:

* CREATE TABLE test( ID INTEGER PRIMARY KEY AUTOINCREMENT, value1 float, value2 numeric(21));
*

and convert it into the valid Postgres equivalent of:

* CREATE TABLE test( ID serial PRIMARY KEY, value1 float, value2 numeric(21));
*

Note that if the above described translations are causing a problems with a query it can be disabled by passing in the P6DBC_DISABLECOMPAT flag into the component's initialize() method.

How to add more databases

Each separate database implemenation is destinguished via the URI passed into the P6R::p6IDBC::connect() and P6R::p6IDBC::connectSigned() methods. So for example the URI: "sqlite3://simpletest1" requests access to a local SQLite3 database with the name of "simpletest1". And for Postgres the URI has the format of: 'postgresql8://user-name/password/hostname/hostaddr/port/database-name', so that "postgresql8://frank/frank5678/testb.corp.p6r.com//5432/p6runits", defines a Postgres8 database on a remove machine testb.corp.p6r.com. Each database implemenation has different requirements for its URI and these are defined in the API documentation for the P6R::p6IDBC::connect() method. Any new database implementation must also define its own URI that does not conflict with already existing URIs.

To implement your own database implementation (e.g., one for Oracle) the following steps are required:

1) Implementations of the following interfaces: P6R::p6IDBImpl, P6R::p6IDBStmtImpl, and P6R::p6IDBCResultSet are required.

2) The implmentation of P6R::p6IDBCStmt::execute() (e.g., CSqlitedbImpl::execute() for SQLite) needs to return a P6R::p6IDBCResultSet component via code like the following. Note, that the class CDBCResultSet is an implementation of the P6R::p6IDBCResultSet interface.

* P6COMMETHODIMPL CSqlitedbImpl::execute( p6IDBStmtImpl* pStmt, // IN
* p6IDBCResultSet** pResults, // OUT
* P6UINT32* pRowCount ) // OUT
* CDBCResultSet* pTemp = NULL;
* P6ERR err = eOk;
* . . . . .
*
* pTemp = new (std::nothrow) CDBCResultSet();
* err = pTemp->initialize( pStmt );
* *pResults = static_cast<p6IDBCResultSet*>( pTemp );
* return err;
*

3) Add the CID GUID of the new database implementation component to the p6dbc.conf file. For example, here is what that file looks like:

* [p6db-schemes]
* sqlite3 = "{6D006BFA-795D-4087-9599-34E41CD60A8C}"
* postgresql8 = "{D299264D-FE6B-4bef-AEA7-19D64FA6CEC4}"
*

Note, that the scheme name used in the database URI must match the string on the left hand side of the equal sign in the p6dbc.conf file (i.e., "sqlite3" above matches the URI used in "sqlite3://simpletest1").

The new GUID must be defined in the implementations "comdef.cpp" file, for example:

* // The Sqlite implementation is included in this DLL
* // {6D006BFA-795D-4087-9599-34E41CD60A8C}
* #define COMP_p6DBSqliteImpl {0x6d006bfa,0x795d,0x4087,{0x95,0x99,0x34,0xe4,0x1c,0xd6,0xa,0x8c}}
*
* P6DECLARE_CID( p6DBSqliteImpl );
* . . . . . . .
*
* static const P6COMPONENTDATA g_ComponentDataArray[] = {
* {
* &CID_p6DBSqliteImpl,
* (P6R::P6CREATEINST)ip6r::CSqlitedbImpl::createInstance,
* P6TEXT("p6IDBImpl"),
* NULL,
* 0
* },
* };
*

The above CID GUID definition allows the P6R::p6IDBC component to dynamically find and load the new database implemenation.

Signning Databases

The P6R::p6IDBC::connectSigned( .., P6SIGNHMAC signAlg, p6ICryptoKey* pSignKey, ..) method, supports the signning of a database's contents when the P6R::p6IDBC::close() method is called. Once a database is signed then that signature is verified when the connectSigned() method is called. An error is returned if a signature failure occurs. Note that the p6IDBC API documentation describes a method on how to recover from a signature failure (see the P6R::P6DBCFLAGS flags). This feature thus provides the detection of unauthorized modification to a database.

This is an optional feature for a database implemenation and if not supported an 'eNotSupported' error is returned from the connectSigned() method. P6R's SQLite database implementation supports this feature. In this case, a signature file is generated in the same directory that the SQLite file is created. This signature file has the same name as the SQLite database with a ".sig" extension. The P6R::p6IDBC::connectSigned() method allows the caller to select the HMAC algorithm and signature key to use. Thus each signed database can use different keys.

Please see the P6R::p6IKeystore component as a means to organizing your keys.