PDOQuery

From Davical
Jump to navigationJump to search

This is largely academic now. DAViCal is currently in the process of moving to a new DB wrapper library which does access the database using PDO, and which does theoretically provide support for implementing missing DB features in a driver layer.

However to reflect the fact that the ultimate implementation was significantly different from the design ideas expressed on this page, the new library is called AwlQuery.


PDOQuery is a new library which is in the planning stages and designed to replace the current PostgreSQL specific database library.

Background

Why a new library?

The current PgQuery library is used in various projects developed by Andrew McMillan and will need to continue to exist in parallel with it's replacement for some time as it will take time to convert these various uses to a differently structured interface.

A differently structured interface?

Yes. The old library was originally developed back around 2001 or so and is not a well-crafted object. I understand object-orientation a lot better now and I think that there are a number of appropriate changes to make so we should start with designing the interface to the object.

So how will this happen?

  1. First we will design the object interface here, on this wiki.
  2. Then we will implement the new object, probably along with some sort of testing harness that exercises it.
  3. We then may change PgQuery under the covers to use the new object.
  4. We will then refactor all of the DAViCal code that uses PgQuery, changing each program to use PDOQuery directly.
  5. Profit!

Well, to be honest I'm not so sure about that last step. And maybe it won't turn out to be appropriate to do the 3rd one either.

Design

Just a quick suggestion:

$db = new PdoDatabase($dbtype, $dbhost, $dbname, ... );

Select:

$qry = $db->query("select * from bleh");

Update/insert/delete:

$affected_rows = $db->exec("delete from bleh");

Transaction:

$db->beginTransaction();
$db->exec("...");
$db->exec("...");
$db->commit();

You have ( unused for davical ) methods in PgQuery to go to previous records and such, which pdo doesnt have.. We could make our own PdoResultSet that keeps the results in memory ( array, .. ) so you can loop through them, request a certain record, etc..

My 2 cents ;)

Desired Functionality

  • Wrap the database connection process.
  • Database connection should be a single parameter containing all the needed host/port/... information
  • Wrap the query in logging/timing logic
  • Include an equivalent for $PgQuery::Fetch() .
  • Include methods for begin/commit/rollback

Desired Usage Characteristics

It is important to overcome the problem with PDO that perform is sometimes inefficiently planned, due to planning occurring before replacement of query parameters. To do this there will sadly need to be a method where the query parameter replacement is done in PHP before passing the fully expanded statement to PDO.

Style of Operation

This is an artificially contrived example intended to show some of the desired features, such as the operation of Prepare/Exec vs Query/Exec, and how it is reasonable to have multiple statements active at the same time. The actual function is not supposed to be reasonable!

$db = new PdoDatabase( $connection_string );
$db->Begin();
$statement = $db->Query("SELECT * FROM table WHERE id = ?", $my_id );
$statement->SlowIs(0.005);

$anotherst = $db->Prepare("INSERT INTO daily_totals ( tot_date, tot_amount) VALUES( current-date, ? )" );

$total = 0;
for( $statement->Exec(); $statement->rows > 0 && $row = $statement->Fetch(); ) {
  printf( "<p>Field %s is %s</p>", 'fieldname', $row->fieldname );
  $total += $row->amount;
}

$anotherst->Exec($total);

$db->Commit();

Class PdoDialect

PdoDialect( $connection_string )

Parses the connection string to ascertain the database dialect. Returns true if the dialect is supported and fails if the dialect is not supported. All code to support any given database should be within in an external include.

GetFields( $tablename_string )

Returns the SQL for the current database dialect which will return a two-column resultset containing a list of fields and their associated data types.

Translate( $sql_string )

Translates the given SQL string into a form that will hopefully work for this database dialect. This hook is expected to be used by developers to provide support for differences in database operation by translating the query string in an arbitrary way, such as through a file or database lookup.

The actual translation to other SQL dialects will usually be application-specific, so that any routines called by this will usually be external to this library, or will use resources external to this library.

Quote( $value )

Returns $value escaped in an appropriate way for this database dialect.

Class PdoDatabase

PdoDatabase( $connection_string )

The connection string is in the standard PDO format. The database won't actually be connected until the first database query is run against it.

The database object will also initialise and hold an PdoDialect object which will be used to provide database specific SQL for some queries, as well as translation hooks for instances where it is necessary to modify the SQL in transit to support additional databases.

Prepare( $sql_string, ... )

Returns a PdoQuery object created using this database, the supplied SQL string, and any parameters given.

Exec( $sql_string, ... )

Construct and execute an SQL statement from the sql_string, replacing the parameters into it. Returns true on success and false on error.

While this uses a PdoQuery internally, this is not exposed. It is intended for use with queries which are not needed after execution to know how many rows are affected, or to be able to process a result set.

Begin()

Begin a transaction.

Commit()

Complete a transaction.

Rollback()

Cancel a transaction in progress.

TransactionState()

Returns the current state of a transaction, indicating if we have begun a transaction, whether the transaction has failed, or if we are not in a transaction.

TotalDuration()

Returns the total duration of quries executed so far by this object instance.

TotalQueries()

Returns the total number of quries executed by this object instance.

GetFields( $tablename_string )

Returns an associative array of field types, keyed by field name, for the requested named table. Internally this calls PdoDialect::GetFields to get the required SQL and then processes the query in the normal manner.

PrepareTranslated()

Operates identically to PdoDatabase::Prepare, except that PdoDialect::Translate() will be called on the query before any processing.

TranslateAll( $onoff_boolean )

Switches on or off the processing flag controlling whether subsequent calls to PdoDatabase::Prepare are translated as if PrepareTranslated() had been called.

Class PdoQuery

A variable of this class is normally constructed through a call to PdoDatabase::Query or PdoDatabase::Prepare, associating it on construction with the database which is to be queried.

PdoQuery( $db, ... )

Where $db is a PdoDatabase object. This constructs the PdoQuery. If there are further parameters they will be in turn, the sql, and any positional parameters to replace into that, and will be passed to $this->Query() before returning.

Query( $sql_string, ... )

If the sql is supplied then PDO::prepare will be called with that SQL to prepare the query, and if there are positional parameters then they will be replaced into the sql_string (with appropriate escaping) before the call to PDO::prepare.

Exec( ... )

If there are (some) positional parameters in the prepared query, now is the last chance to supply them... before the query is executed. Returns true on success and false on error.

Fetch()

Will fetch the next row from the query into an object with elements named for the fields in the result.

FetchArray()

Will fetch the next row from the query into an array of fields.

FetchAll()

Will fetch all result rows from the query into an array of objects with elements named for the fields in the result.

Rows()

An accessor for the number of rows affected when the query was executed.

MaxDuration( $seconds_double )

Used to set the maximum duration for this query before it will be logged as a slow query.

Implementation

In due course...