The database abstraction layer is a simple library built on top of PDO to simplify the access to the database and to provide database indepentence. The library consists of a single class, called Database with many static functions and a global static state. Currently, it has been tested with MySql and Oracle databases, but it probably works with other vendors as well.
The static architecture makes it hard sometimes to mock the operations for testing purposes. It is planned that this will change into a dynamic extensible architecture in the future. However, the global state will be optionally used as it is very convenient for a good percentage of simple, single-database applications.
The database layer maintains stack of active database connections. To open a connection, use the function Database::Connect(). This will add the new connection to the top of the stack. Database::Disconnect() on the other hand, will close the connection and remove it from the stack. The call of this function is not mandatory because it will be called automatically at the end of the script.
Database::Connect( $server1, $schema1, $username1, $password1, Database::MYSQL ); // we are now connected to a MySQL database Database::Connect( $server2, $schema2, $username2, $password2, Database::ORACLE ); // we are now connected to an Oracle database Database::Disconnect(); // we have been disconnected from Oracle, and returned to the MySql database.
In addition to Database::Connect(), there is also the function Database::ConnectManaged() which will connect to a database and also run the database upgrade system to ensure that the schema is up to date. This system is presented at the end of this chapter. The function will also empty the stack and close any open connections before connecting. Therefore, there can be no more than one managed connection and all other unmanaged connections should be place on top of it.
The details of the current active connection can be retrieved with the following set of functions:
Database::IsConnected(); // boolean Database::GetServer(); // string Database::GetSchema(); // string Database::GetType(); // int (Database::MYSQL or Database::ORACLE)
All queries are handled by the function Database::Execute(). This function takes an SQL query in as string form, plus a number of arguments to be bound to placeholders marked with ?. The arguments can have be of any type supported by the type system (see the chapter on the Type System).
Database::Execute('DELETE FROM Books WHERE id=?', $id);
If the query is a SELECT, then the results are return in the form of a DBReader, which is a forward only cursor. Each column is a wrapped DBValue which should be imported by using the proper type.
$dr = Database::Execute('SELECT * FROM Books'); while ($dr->Read()){ $id = $dr['id']->CastTo( MetaID::Type() ); $title = $dr['id']->CastTo( MetaString::Type() ); echo $id->AsHex(); echo $title; } $dr->Close();
Data Manipulation Language (DML) functions are those functions that deal with the content of the tables. The Database class offers a few helpers for common DML commands.
This function will return directly a DBValue with the first column of the first row of the results. If there are none, then a wrapped null will be returned.
$count = Database::ExecuteScalar('SELECT COUNT(*) FROM Books')->CastTo( MetaInteger::Type() ); $id = Database::ExecuteScalar('SELECT id FROM Books WHERE Name=?', 'Alice in wonderland')->CastTo( MetaID::Type() );
This function will return an array of the first column of the results. Each element of the array will be casted to the predefined type.
$author_ids = Database::ExecuteColumnOf( MetaID::Type() , 'SELECT idAuthor FROM Books WHERE idAuthor IS NOT NULL' );
Insert a row into a table with name-value pairs
// INSERT INTO `Books` (`id`,`Title`) VALUES (?, ?) // bind [3,'Alice in wonderland'] Database::ExecuteInsert('Books' ,'id', new ID(3) ,'Title', 'Alice in wonderland' );
Update name-value pairs into a table with or without a WHERE clause.
// UPDATE `Books` SET `Title` = ? WHERE `id`=3 // bind ['Alice in wonderland'] Database::ExecuteUpdate('Books',new SqlIden('id').'='.new Sql(new ID(3)) ,'Title', 'Alice in wonderland' ); // UPDATE `Books` SET `DateDeleted` = ? // bind [null] Database::ExecuteUpdateAll('Books' ,'DateDeleted', null );
Update rows from a table with or without a WHERE clause.
// DELETE FROM `Books` WHERE `id`=3 Database::ExecuteDelete('Books',new SqlIden('id').'='.new Sql(new ID(3)) ); // DELETE FROM `Books` Database::ExecuteDeleteAll('Books');
Data Manipulation Language (DDL) functions are those functions that deal with the structure of the tables. The Database class offers a few helpers for common DDL commands that are vendor independent.
These functions will create a table in the database based on name-type pairs. The standard table variant will also insert a column named id with type Sql::ID and set it as the primary key.
// CREATE TABLE `Books` (`id` INT NOT NULL, `Title` VARCHAR(255), `Summary` TEXT, `NumberOfPages` INT) PRIMARY KEY ( `id` ) Database::ExecuteCreateStandartTable('Books' ,'Title', Sql::String255 ,'Summary', Sql::Text ,'NumberOfPages', Sql::Integer );
Remames a table...
Database::ExecuteRenameTable('Books','BookEntries');
Deletes a table...
Database::ExecuteDropTable('Books');
Add or remove fields in a table
Database::ExecuteAddFields('Books' ,'Author',Sql::String255 ,'Translator',Sql::String255 ); Database::ExecuteDropFields('Books' ,'Author' ,'Translator' );
Changes the name of one or more fields. Because of SQL limitations, it is necessary to pass the type of each field also.
Database::ExecuteRenameFields('Books' ,'NumberOfPages','NumPages',Sql::Integer // old_name -> new_name -> type ,'Translator','TranslatorName',Sql::String255 // old_name -> new_name -> type );
Changes the type of one or more fields.
Database::ExecuteRecastFields('Books' ,'NumberOfPages',Sql::Integer // name -> type ,'Translator',Sql::String255 // name -> type );
If the database driver supports transactions, then these can be used with the three following functions.
Database::TransactionBegin(); try { ... Database::TransactionCommit(); } catch (Exception $ex){ Database::TransactionRollback(); }
One of the problems when working with databases is that they constantly need upgrading as the code changes. The database upgrade system tries to tie the version control of the database to the version control of the code. This is not perfect, as this tie is forward only. If the code is reverted to a previews version, then there is no way back for the database. In addition, there is no conflict resolution if two developers try to change simultaneously the same part of the database. Having said that, the database upgrade system is an adequate solution for most of the cases.
The version of the database is just not one number. As in code version control, the changes are not linear because several developer work in parallel. Instead, each developer has its own separate versioning sequence. The actual version of the database is the concatenation of the version of each developer. These numbers are stored inside the database, in a dedicated table.
The upgrade system is modular, in the sense that each module should have it own separate upgrade script. In addition, each module has its own developers and a completely independent versioning system.
The upgrade script has this structure:
-------------------------------- | Initialisation | -------------------------------- | Developer 1 patch 1 | -------------------------------- | Developer 1 patch 2 | -------------------------------- | Developer 2 patch 1 | -------------------------------- | Developer 1 patch 3 | -------------------------------- | Developer 2 patch 2 | --------------------------------
Because the upgrade script is just plain code, the management of patches from different developers is done by using the merging features of the code versioning system.
The initialisation block will check if the version tracking table exists, and if not it will try to create it. This is the initialisation block of a hypothetical module, called hydrogen:
Database::SetPatchingSystem( 'hydrogen' , 'hyd_settings' ); // the name of the module and the version tracking table Database::AddPatcher('li','VersionLi'); // developer 1 Database::AddPatcher('rb','VersionRb'); // developer 2 if (Database::BeginPatchingSystem()) { // create the tracking table as it does not exist: Database::ExecuteCreateStandardTable('hyd_settings','VersionLi',Sql::Integer,'VersionRb',Sql::Integer); Database::ExecuteInsert('hyd_settings','id',new ID(0),'VersionLi',0,'VersionRb',0); }
Then each of the patches section looks like this:
if (Database::BeginPatch('li', 1, 'Creating the first table')){ // developer - version - comment Database::ExecuteCreateStandardTable('hyd_books' ,'Title',Sql::String255 ,'Author',Sql::String255 ); Database::ApplyPatch(); } if (Database::BeginPatch('li', 2, 'Author have their own table')){ Database::ExecuteCreateStandardTable('hyd_authors' ,'Name',Sql::String255 ); Database::ExecuteAddFields('hyd_books' ,'idAuthor',Sql::ID ); Database::ExecuteDropFields('hyd_books' ,'Author' ); Database::ApplyPatch(); }
If the second developer wants to make a change, he or she must use a separate numbering sequence.
... if (Database::BeginPatch('rb', 1, 'Adding a comments table')){ Database::ExecuteCreateStandardTable('hyd_comments' ,'Sender',Sql::String255 ,'Message',Sql::Text ); Database::ApplyPatch(); }
Adding another developer to the team is a patch by itself:
... if (Database::BeginPatch('li', 2, 'Adding jc as a developer')){ Database::ExecuteAddFields('hyd_settings','VersionJc',Sql::Integer); Database::ExecuteUpdateAll('hyd_settings','VersionJc',0); Database::ApplyPatch(); } Database::AddPatcher('jc','VersionJc'); // The patcher command should come here, after the table is created.
The upgrade script should be both backwards and forwards compatible. When executed on an old database it should bring it to date. On the other hand, anything that exists in the patches should not depend on any other part of the module, because this is not guarranded to exist in future versions, and the whole point of the system is to work inside future versions.
The patches will be actually applied the first time the script is executed, when a managed connection is opened to the database. Then they wont be executed again unless they are changed.