CSDB Data Access Layer Documentation

Need a portable, cross-platform, cross-database, lightweight, debuggable, replication-aware, migration-friendly, transaction-capable, data access layer (DAL) for PHP 5.3 and later? Don't want Zend_Db, Doctrine, or various ORM/DAL dependencies? Don't want to depend on a MVC pattern? Then you may have found what you are looking for.

CSDB aims to be a collection of useful, lightweight classes intended to be dropped into projects that need database access. By default, they rely on the PHP PDO class, but CSDB is flexible enough that it can support something as crazy as EFSS as a pseudo-database or, more rationally, the older PHP database access classes and functions. One of the goals of this project is to allow users to only include the pieces they are interested in using. For example, if only MySQL or Maria DB is needed, then only the files 'db.php' and 'db_mysql.php' have to be included in the project. No need to drop in the kitchen sink.

The main premise for this project is cross-database functionality - mostly for writing SQL queries exactly one time for open source software products. Each database product out there does things a little bit differently - just differently enough that writing SQL queries for one database won't work in others. CSDB follows the methodology of taking an array of information and using that to generate one or more SQL queries and execute them. Using the array approach allows for more refined control over how a query is generated without having to go crazy over making various method calls as seen in other DAL products (also known as taking Object-Oriented Programming too far).

Currently supports: MySQL/Maria DB, PostgreSQL, SQLite.

CSDB also has beta support for: Oracle (missing some important features).

Don't see your favorite database product in the list? Ask for it in the forums!

Features

The following is a list of several features of CSDB:

And more.

Live Demos

The Admin Pack address book demos have been updated to use CSDB.

License

Like Barebones CMS, CSDB is dual-licensed under a MIT or LGPL license - your choice. The license and restrictions are identical to the Barebones CMS License.

Download

CSDB 1.0RC4 is the third release candidate of CSDB.

Download csdb-1.0rc4.zip

If you find CSDB useful, please donate toward future development efforts.

Connecting to a Database

Setting up a connection to a database is easy! Just instantiate the derived class of the appropriate database and call the Connect() function with either a DSN string or an array:

<?php
	require_once "support/db.php";
	require_once "support/db_mysql.php";

	try
	{
		$db = new CSDB_mysql();
		$db->SetDebug(fopen("out.txt", "wb"));
		$db->Connect(array("host" => "localhost"), "username", "*******");
		$db->SetMaster(array("host" => "otherhost"), "masterusername", "*******");
		$db->Query("USE", "testdb");
	}
	catch (Exception $e)
	{
		echo $e->getMessage() . "\n";
		exit();
	}
?>

The above instantiates the 'CSDB_mysql' class, enables debugging to a log file, connects to a MySQL database on localhost, sets a replication master database, and selects the target database. If an error occurs, it stops executing code.

Enabling debugging to a log file on a production server should be restricted to a single user. A common approach is to use the IP address of your machine or some unique session identifier so that the log file doesn't get loaded down with lots of queries from multiple users. Plus writing each query to disk takes time, so the application slows down when debugging is enabled.

When a query is run, the class automatically determines if it is a change query. If so, the class connects to the replication master database prior to running the query. Replication is an optional feature. When using replication, queries that make changes should be run as late as possible so that there are fewer SELECT queries run against the master database. When connecting to the replication master, the same database as the slave is connected to (if the 'USE' command was used) and the same number of transactions (if any) that began on the slave are restored.

Disconnecting from a database requires all references to the underlying database objects to vanish. There is a Disconnect() method, but it won't necessarily work as expected. Ending the current PHP session is really the only way to guarantee that the database object is freed up. This has more to do with how PDO works than anything else. However, most scripts that use a database rush to set up the database connection at the beginning and then don't terminate the database connection until the very end, so letting PHP handle the teardown of the connection is a rational approach.

Transactions

Maintaining database integrity can be difficult in a web environment. If the application crashes in the middle of a set of queries, the database connection drops, or PHP terminates for whatever reason (e.g. out of memory, time limit exceeded), the database can be left in a messy state. Transactions were created to solve these and other problems and using them can also improve application performance.

The CSDB class supports transactions. Here's an example:

<?php
	try
	{
		$db->BeginTransaction();

		...

		$db->Commit();
	}
	catch (Exception $e)
	{
		$db->Rollback();
	}
?>

By default, transactions are automatically committed if the application completes successfully, but it is a good idea to just explicitly declare a commit because it could still throw an exception.

Note that a lot of databases have problems with consistency when it comes to transactions and schema changes. Stick to SELECT, INSERT, UPDATE, and DELETE statements for maximum portability.

CSDB supports nested transactions. Only the first BeginTransaction() and the last Commit() will actually do anything when they are nested. Rollback() will undo all nested transactions, so it only needs to be called at the top level of the application.

Query Methods

If you have ever used the data access layers in PEAR (e.g. DB or MDB2), you will feel somewhat right at home with the query methods in this package for running queries and retrieving results. The following is the list of available methods and a brief description of each one:

Remember that PHP function and method calls are case-insensitive. So, if you prefer query(), nextRow(), getRow(), etc. that's cool as long as you write consistent, readable code that other developers can follow.

Running Queries

Each query method takes the same information as input. The first parameter is one of the following commands as a case-insensitive string:

Each database class attempts to implement each feature in an identical fashion so that the same input information has the same or a similar result. Some classes may have to emulate the feature set, but the result will be transparent to the application.

There are "lite" versions of each class (e.g. 'CSDB_mysql_lite'). These classes only support SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE, SET, and USE commands for reduced memory usage to around 300KB RAM per request. Full support for all commands uses 450KB to 570KB RAM per request, depending on the database.

The second parameter is an array containing information relevant to the command. Since each command and query is different, the second parameter's values will be different depending on the query being executed. The next few subsections cover the details on the second parameter for each command.

Alternatively, the first parameter can be a boolean of false and the second parameter a string containing the parameterized query to run. This is useful for running database-specific queries, but obviously loses all of the portability aspects of CSDB. This type of query forces a switch to the master database if replication is used.

The third parameter can be either an array or a scalar value (string, int, etc.). When it is an array, the contents of the array are treated the same as having third, fourth, fifth, and so on parameters as part of the main call. The query methods all accept a variable number of arguments as a result. The purpose of these two options (single array vs. multiple scalars) is to make prepared statements (parameterized SQL queries) really easy to use and make SQL injection attacks a thing of the past. If this is confusing, just check out some of the examples that follow and it should make more sense.

Subqueries

Subqueries are generally SELECT commands embedded inside another command. Some databases have really bad subquery support (e.g. poor performance). For that reason alone, avoid subqueries and prefer joins.

Commands that accept subqueries allow for the "SUBQUERIES" option. Strings with the format {subquerynum} are replaced with the associated subquery from the "SUBQUERIES" array. An example:

<?php
	$result = $db->Query("SELECT", array("*", "FROM" => "?",
		"WHERE" => "id IN {0}",
		"ORDER BY" => "name",
		"LIMIT" => "5",
		"SUBQUERIES" => array(
			array(array("id", "FROM" => "?", "WHERE" => "name = 'somename'"), "test")
		)), "test");
	while ($row = $result->NextRow())
	{
		var_dump($row);
		echo date("M, j Y @ H:i", CSDB::ConvertFromDBTime($row->created)) . "\n";
	}
?>

In the example, the subquery (SELECT id FROM test WHERE name = 'somename') replaces the string {0} in the "WHERE" clause. Subqueries get a bit unwieldy to work with in CSDB, which is another reason to not use them very often.

Joins

When developing a cross-database friendly application, the two types of joins that are best supported are:

Avoid the other types of joins to maximize application portability across databases.

Foreign Keys, Views, Triggers, Stored Procedures

As far as cross-database functionality goes, CSDB currently has limited support for foreign keys and non-existent support for views, triggers, and stored procedures. Triggers and stored procedures are particularly tricky because each database implements them differently, especially stored procedures. Also, not every database supports stored procedures. Views seem somewhat more universally supported in some sane format. My personal approach to SQL is to stick to core SQL wherever possible to avoid issues with portability, so my exposure to these four aspects of SQL is rather limited.

Basically, if you need these features, let's talk in the forums. I'm open to ideas.

Column Definitions

The CREATE TABLE and ADD COLUMN commands manipulate database table schemas. One database may accept VARCHAR while another only accepts TEXT columns. To make applications more portable, CSDB uses a limited subset of column types.

The following is the list of supported types:

The INTEGER and FLOAT types accept an optional second parameter that specifies the number of bytes of storage to use. For example, array("INTEGER", 2) will result in a SMALLINT under MySQL, capable of storing 16-bit (2 bytes) integers. The INTEGER type, under some databases, also accepts the boolean option UNSIGNED.

The DECIMAL type accepts up to two extra parameters that specifies the precision and scale of a DECIMAL type. Not all databases support this type, so the closest option is chosen.

The STRING and BINARY types accept an optional second parameter that specifies the number of bytes of storage to use. When the value of the second parameter is 1, a required third parameter specifies the maximum number of bytes of storage for the column. For example, array("STRING", 1, 100) will result in a VARCHAR(100) under MySQL. The BINARY type is virtually identical to STRING except binary column types are used. The STRING and BINARY types, under some databases, also accept the boolean option of FIXED, which will select a fixed-width column instead of variable-width.

The DATE, TIME, and DATETIME fields may result in an equivalent STRING type under some databases. The CSDB date/time manipulation functions should be used, which will mitigate this issue.

The BOOLEAN type may result in an equivalent INTEGER type under many databases.

The following is a list of common options for each column:

If an option isn't supported, it may be emulated or may simply be ignored.

Index/Key Definitions

The CREATE TABLE and ADD INDEX commands manipulate database table indexes. If a database doesn't support a certain type of index, it will generally ignore it. This is particularly true of foreign key constraints. To make applications more portable, CSDB uses a limited subset of index types.

The following is the list of supported index/key types:

The following is a list of common options for each index:

Not every database supports all index types, especially FULLTEXT indexes. For index types that aren't supported at all, the request will simply be ignored instead of throwing an error. For index types that aren't supported in specific contexts, the class will generally attempt to emulate creating the index through other means. For example, SQLite doesn't support KEY indexes inside of CREATE TABLE commands, but does support them via ADD INDEX, so the table is first created and then indexes are applied using multiple queries - all of which is completely transparent to the application. However, SQLite doesn't support FULLTEXT indexes at all, so it just ignores those.

All indexes should be named with the NAME option. Some databases require names while others don't.

CREATE TABLE

This command creates a new table in a database. Supports normal tables, temporary tables, and CREATE TABLE AS SELECT (CTAS) tables. Table schema declarations vary widely across database products. This command supports a fairly common subset of what most databases support.

Example CREATE TABLE command:

<?php
	$db->Query("CREATE TABLE", array("test", array(
			"id" => array("INTEGER", "UNSIGNED" => true, "NOT NULL" => true, "PRIMARY KEY" => true, "AUTO INCREMENT" => true),
			"name" => array("STRING", 1, 50, "NOT NULL" => true),
			"desc" => array("STRING", 1, 255, "NOT NULL" => true),
			"created" => array("DATETIME", "NOT NULL" => true),
		), array(
			array("KEY", array("name"), "NAME" => "name")
		), "TEMPORARY" => true));
?>

This creates a temporary table, which lasts for the remainder of the database connection, with a few fields, a primary key on an auto increment field, and a secondary index. What this will generate as far as a SQL command goes depends entirely on the database.

When not using CTAS, the second parameter is an array containing:

When using CTAS, the second parameter is an array containing:

Note that not every database supports CTAS. In addition, many database products will drop indexes/keys in CTAS tables.

The following options are commonly available:

Note that not all databases support these table-level options. Also, specifying these options will make the application less portable (e.g. one database might accept a character set string of 'utf-8' while another accepts 'utf8').

Many database products require extra user permissions to execute this command.

SELECT

This command selects rows from one or more database tables or the results of a database function. Many databases perform poorly when doing joins across databases, so support for cross-database joins is limited and not recommended. Also, for improved portability, applications should attempt to avoid database-specific extensions and functions. Keeping queries really generic goes a long way to avoiding problems later on.

Example SELECT command:

<?php
	$result = $db->Query("SELECT", array(
		"*",
		"FROM" => "?",
		"ORDER BY" => "name",
		"LIMIT" => "5"
	), "test");

	while ($row = $result->NextRow())
	{
		var_dump($row);
		echo date("M, j Y @ H:i", CSDB::ConvertFromDBTime($row->created)) . "\n";
	}
?>

The second parameter is an array containing:

The following options are commonly available:

Most of the options are pretty self-explanatory, but there should be a few notes.

DISTINCT is broken out specifically to accommodate SELECT prefixes that some databases support. Using the option is more portable.

When the FROM clause contains parameters, the parameters are replaced directly with the next argument on the stack after calling the database class' QuoteIdentifier() function. This has the natural side-effect of eliminating the ability to select an alternate database. For the most portable code, use the USE command to select a database and then run queries only on tables in that database with parameters for table names.

The LIMIT option is a bit weird. Not all databases support the LIMIT keyword, so in those instances, LIMIT is emulated via row filtering. LIMIT is also ignored when used inside of subqueries.

The EXPORT ROWS option returns rows in a format that is ready for use with the INSERT command. This is great for being able to create a general-purpose backup solution for any database product or quickly migrating data from one database to another.

INSERT

This command inserts a single row into a database table. Multiple rows can be inserted with the "SELECT" option. Inserting rows one at a time can be a lot slower over bulk inserts, but not every database supports multiple row inserts in a single command. However, some databases optimize transactions such that a bunch of inserts in a single transaction are collected together and then run simultaneously when the transaction is committed, which results in extremely fast performance (e.g. SQLite does this and inserting a million rows takes under a minute to complete within a transaction).

Note: The command is INSERT. Forgetting it and using "INSERT INTO" instead will result in an exception being raised.

Example INSERT command:

<?php
	for ($x = 1; $x < 10; $x++)
	{
		$db->Query("INSERT", array("test", array(
			"name" => "somename",
			"desc" => "somedesc",
			"created" => CSDB::ConvertToDBTime(time())
		), "AUTO INCREMENT" => "id"));

		echo $db->GetInsertID() . "\n";
	}
?>

When not using SELECT, the second parameter is an array containing:

When using SELECT, the second parameter is an array containing as follows:

For an example of using SELECT with INSERT, see the 'db_sqlite.php' file under the DROP COLUMN command. SQLite doesn't support dropping columns directly, so the entire table has to be recreated twice.

When a row is inserted into the database and a primary key column is an integer with an auto-increment attribute, most databases support a method of extracting the last inserted ID. This is exposed via the GetInsertID() member function.

UPDATE

This command updates specific rows in a database table. Most databases implement this SQL command consistently so there's not really much to discuss.

Example UPDATE command:

<?php
	$id = 5;
	$db->Query("UPDATE", array("test", array(
		"name" => "somename - " . $id,
		"desc" => $_REQUEST["desc"]
	), "WHERE" => "id = ?"), $id);
?>

The second parameter is an array containing:

The following options are commonly available:

Note that the LIMIT keyword is not emulated for UPDATE queries when a database doesn't support it. This happens because UPDATE doesn't return rows to the application.

DELETE

This command deletes specific rows from a database table. Most databases implement this SQL command consistently so there's not really much to discuss.

Example DELETE command:

<?php
	$id = 4;
	$db->Query("DELETE", array("test", "WHERE" => "id = ?"), array($id));
?>

The second parameter is an array containing:

The following options are commonly available:

Note that the LIMIT keyword is not emulated for DELETE queries when a database doesn't support it. This happens because DELETE doesn't return rows to the application.

TRUNCATE TABLE

This command deletes all rows in a database table.

Example TRUNCATE TABLE command:

<?php
	$db->Query("TRUNCATE TABLE", array("test"));
?>

The second parameter is an array containing:

When a database doesn't support this command natively, it is emulated using DELETE. Some database products require extra user permissions to execute this command.

SET

This command sets a session or database option. Options are usually not portable between database products and this command is mostly for internal class use.

Example SET command:

<?php
	$db->Query("SET", "NAMES 'utf8'");
?>

The second parameter is a string that will NOT be escaped or quoted. Strings should be hardcoded so there isn't any possibility of user input causing an application vulnerability. This is an unusual command since most commands take an array as the second parameter.

USE

This command selects a database. If the database product does not support multiple standard databases in a single session, the class will emulate this command, typically as a prefix.

Example USE command:

<?php
	$db->Query("USE", "testdb");
?>

The second parameter is a string that will be quoted as an identifier. This is an unusual command since most commands take an array as the second parameter.

CREATE DATABASE

This command creates a database. If the database product does not support multiple standard databases in a single session, the class will emulate this command, typically by ignoring the request.

Example CREATE DATABASE command:

<?php
	$db->Query("CREATE DATABASE", array("testdb"));
?>

The second parameter is an array containing:

The following options are commonly available:

Note that not all databases support database-level options. Also, specifying these options will make the application less portable.

Many database products require extra user permissions to execute this command.

DROP DATABASE

This command drops a database and all tables within the database.

Example DROP DATABASE command:

<?php
	$db->Query("DROP DATABASE", array("testdb"));
?>

The second parameter is an array containing:

If the database product does not support multiple standard databases in a single session, the class will emulate this command. Note that emulation may have unexpected results. Of course, dropping a whole database should be done carefully to begin with.

Many database products require extra user permissions to execute this command.

ADD COLUMN

This command adds a column to an existing database table.

Example ADD COLUMN command:

<?php
	$db->Query("ADD COLUMN", array("test", "updated", array("DATETIME", "NOT NULL" => true), "AFTER" => "created"));
?>

The second parameter is an array containing:

The following options are commonly available:

Not all databases support all options. If a database doesn't support an option, it is ignored.

Many database products require extra user permissions to execute this command.

DROP COLUMN

This command drops/removes a column from an existing database table. If it is the only column in the table, the table may be removed or an error thrown depending on the database.

Example DROP COLUMN command:

<?php
	$db->Query("DROP COLUMN", array("test", "created"));
?>

The second parameter is an array containing:

If the database doesn't support removing a column, it will be emulated by recreating the table twice, which may have side-effects. For example, SQLite doesn't have support for dropping columns, so the class emulates the process but attempts to retain column type information and indexes/keys.

Many database products require extra user permissions to execute this command.

ADD INDEX

This command adds an index/key to an existing database table.

Example ADD INDEX command:

<?php
	$db->Query("ADD INDEX", array("test", array("KEY", array("created", "updated"), "NAME" => "created_updated")));
?>

The second parameter is an array containing:

If a database doesn't support an index type it will either emit an error or silently ignore the request.

Many database products require extra user permissions to execute this command.

DROP INDEX

This command drops/removes an index/key from an existing database table.

Example DROP INDEX command:

<?php
	$db->Query("DROP INDEX", array("test", "KEY", "created_updated"));
?>

The second parameter is an array containing:

If a database doesn't support an index type it will either emit an error or silently ignore the request.

Many database products require extra user permissions to execute this command.

DROP TABLE

This command drops/removes an existing table from the database.

Example DROP TABLE command:

<?php
	$db->Query("DROP TABLE", array("test"));
?>

The second parameter is an array containing:

The following option is commonly available:

Not all databases support all options. If a database doesn't support an option, it is ignored.

Many database products require extra user permissions to execute this command.

SHOW DATABASES

This command shows all available databases that the user has access to.

Example SHOW DATABASES command:

<?php
	$db->Query("SHOW DATABASES", array());
?>

The second parameter is an array containing:

When the string is specified, only database names that contain the string will be returned.

Each returned row contains a column called 'name'.

SHOW TABLES

This command shows all available tables in a database.

Example SHOW TABLES command:

<?php
	$result = $db->Query("SHOW TABLES", array("FULL" => true));
	while ($row = $result->NextRow())
	{
		var_dump($row);
	}
?>

The second parameter is an array containing:

The following options are commonly available:

Not all databases support all options. If a database doesn't support an option, it is ignored.

Each returned row contains a column called 'name'.

SHOW CREATE DATABASE

This command obtains a CREATE DATABASE compatible response for the specified database.

Example SHOW CREATE DATABASE command:

<?php
	$row = $db->GetRow("SHOW CREATE DATABASE", array("testdb"));
	var_dump($row);
?>

The second parameter is an array containing:

Each returned row contains a column called "cmd" containing the string "CREATE DATABASE" and a column called "opts" containing an array of values suitable for passing as the second parameter in a future CREATE DATABASE command.

SHOW CREATE TABLE

This command obtains a CREATE TABLE compatible response for the specified table.

Example SHOW CREATE TABLE command:

<?php
	$row = $db->GetRow("SHOW CREATE TABLE", array("test"));
	var_dump($row);
?>

The second parameter is an array containing:

The following option is commonly available:

Not all databases support all options. If a database doesn't support an option, it is ignored.

Each returned row contains a column called "cmd" containing the string "CREATE TABLE" and a column called "opts" containing an array of values suitable for passing as the second parameter in a future CREATE TABLE command.

BULK IMPORT MODE

This command enables or disables the current session for performing bulk imports of data. This feature is primarily used as a precursor to initialize one or more offline database tables with large quantities of data. For most databases, enabling bulk import mode results in a series of queries that disable various safety checks that are considered fairly dangerous for maintaining data integrity. The tradeoff, however, is a very significant increase in INSERT performance on a scale of hundreds to thousands of times faster.

Example BULK IMPORT MODE command:

<?php
	// Enable.
	$db->Query("BULK IMPORT MODE", true);

	// Perform a LOT of INSERT queries here...

	// Disable.
	$db->Query("BULK IMPORT MODE", false);
?>

The second parameter is a boolean that will enable or disable bulk import mode.

Note that it is very important to disable bulk import mode prior to disconnecting. The CSDB class will not do this for you. Disabling bulk import mode gives the database an opportunity to do cleanup work to put the database into a consistent state (e.g. refresh indexes).

Date/Time Functions

Dates and times should always be stored in and retrieved from a database in UTC in the universal ODBC-compliant string format "Year-Month-Day Hour:Minute:Second". Doing this makes timezones irrelevant because it lets the scripting language translate to and from the local time. To aid in translating between database timestamps and something programmers are able to more easily manipulate, a few helper functions exist.

CSDB::ConvertToDBDate($ts, $gmt = true)

Parameters:

Returns: The result of gmdate() or date() depending on $gmt.

This static function converts a UNIX timestamp into a string suitable for a date field in a database. Strings take the format YYYY-MM-DD.

CSDB::ConvertToDBTime($ts, $gmt = true)

Parameters:

Returns: The result of gmdate() or date() depending on $gmt.

This static function converts a UNIX timestamp into a string suitable for a date and time field in a database. Strings take the format YYYY-MM-DD HH:MM:SS.

CSDB::ConvertFromDBTime($field, $gmt = true)

Parameters:

Returns: The result of gmmktime() or mktime() depending on $gmt.

This static function converts a date and time field string from a database into an integer representing a UNIX timestamp.

Miscellaneous Functions

There are a handful of functions that come in handy when writing application installers but are not terribly useful beyond that. Each class implements the following functions:

It is a little weird to have two different return types for IsAvailable(). While lazy, it was easier to have a string returned to be able to handle the PDO connect call correctly in the base class. The correct test for IsAvailable() is to compare to false (e.g. $db->IsAvailable() === false).

LargeResults() is only useful in certain circumstances. The function, for example, will enable unbuffered mode for MySQL. Under normal circumstances, PHP caches the results of a query in RAM from MySQL (aka 'buffered' mode). This is problematic for retrieving millions of rows, which can easily consume all available memory and cause PHP to unexpectedly stop working. It is important to note that by enabling unbuffered mode, MySQL requires all results to be read by the application before attempting to run another SQL query - otherwise, an error will occur.

© CubicleSoft