Database Abstraction

The Sourdough framework provides you with an extensive database abstraction layer. It is neatly integrated into Sourdough and it is recommended to use the global Sourdough object to initialize it.

  • Philosophy
  • Supported database systems
  • Connecting to the database
  • SQLHelper vs. DB object
  • SELECTing Data
    • Basic Loop
    • Limiting Results
    • How many values returned?
    • Getting Column Values
    • ResultSet Indexing
    • Scrolling ResultSet Methods
    • ResultSetIterator
  • Updating the Database
  • Advanced Queries
    • JOINs
    • Multiple ResultSets

Philosophy

There are a lot of database abstraction libraries around for PHP. Most of them try to support every single database feature even if it is only available in one single database system (DBS). Actually this is not the point of an abstraction layer. The main advantage of such a layer is portability. We should be able to run an application on one DBS and then switch the next day to another without noticing anything.

This was the intention of Sourdough's database abstraction layer. It won't support any special features like triggers, foreign key generation, and stored procedures, as long as not ALL supported database systems do support them.

So, from time to time you're going to find something that can not be done with Sourdough's database abstraction layer. But on the other hand you will be more than greatfull about it's true portability!


Supported database systems

The following database systems have been tested:

  • MySQL [1] 3.23.x/4.0.x (mysql)
  • MySQL [1] > 4.1 (mysqli)
  • PostgreSQL [2] (pgsql)
  • SQLite [3] (sqlite)
  • Microsoft SQL Server [4] (mssql)

There are drivers for some additional database systems but they haven't been tested yet and are not expected to work yet. If you're planning to use one of those systems and could do some testing, let us know.

  • Sybase [5] (sybase)
  • Borland InterBase [6] (ibase)
  • FrontBase [7] (fbase)

Connecting to the database

Database connection settings belong to the global Sourdough configuration array, so all we need to do is to override the default settings. Sourdough uses PEAR-style DSN arrays for connecting to the database. Sourdough also provides a parseDSN() method for converting a connection URL to the DSN hash.

$conf['dsn'] = array(
    'driver'   => 'mysql',
    'username' => 'dbuser',
    'password' => 'dbpass',
    'hostspec' => 'localhost',
    'port'     => null,
    'database' => 'testdb'
);

$SOURD = new Sourdough($conf);
$DB = $SOURD->Db();

This could also be written as:

$conf['dsn'] = "mysql://dbuser:dbpass@localhost/testdb";

$SOURD = new Sourdough($conf);
$DB = $SOURD->Db();

Important: Each Sourdough object got one single database object, say one single connection. If you would like to work with several different connections, you would need to use a separate instance of the global Sourdough object. This works. Sourdough is designed to support multiple "frameworks" in one single script without interfering eachother.


SQLHelper vs. DB object

Sourdough provides you with a nice so called SQLHelper that helps you build your SQL queries.

Before using the SQLHelper, let us show how you could use the database object directly to execute your queries:

$DB->execute("SELECT id, name FROM users");
while($DB->moveNext()) {
    echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n"
}

This would basically do it. For this simple SQL query we wouldn't even run into any trouble. But we want to design our code more portable, we don't want to pass a plain SQL command to the database class. We don't want to worry about different notation of different DBS. That's where the SQLHelper comes in.

Look at the following example which does the same as the above:

$SQL = $SOURD->SqlHelper();

$SQL->create('users');
$SQL->addFields(array('id', 'name'));
while($DB->moveNext()) {
    echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n"
}

This is the preferred way of doing it! You should always use SQLHelper instead of passing plain SQL-statements to the database object. The database object is also accessible through the SQLHelper object, so you might as well use $SQL->db->moveNext() instead of $DB->moveNext(). They both refer to the same object.

In all the examples below, we are going to use the SQLHelper to build our queries. Nobody hinders you of using $DB->execute("...") but if you wish to design your application really portable go the "SQLHelper way"!

Remember: Use SQLHelper ($SQL) to create your SQL commands and use the database object ($DB) to get information about a recordset / browse through it.


SELECTing Data

When we are talking about SELECTing data, we are talking about SQL that returns results, as opposed to SQL that changes the data in your database.


Basic Loop

Here's a very basic query and result-fetch loop (same as above, extended by a 'WHERE' statement):

$SQL->create('users', 'id > 10');
$SQL->addFields(array('id', 'name'));
$SQL->execute();
while($DB->moveNext()) {
    echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n"
}

Variation: instead of directly specifying the WHERE-part in your create statement, you could also use $SQL->setWhere('id > 10').

Warning: If you have set a database table prefix in your global Sourdough configuration with e.g. $conf['dboptions']['prefix'] = 'sourd_';, this prefix will get automatically prepended to your table name. In the above example this would result in 'sourd_users'. If you don't use the SQLHelper, '{prefix}users' would accomplish the same.


Limiting Results

SQLHelper supports setting LIMIT/OFFSET for queries. For drivers that support this in SQL, the SQL query will be modified. For other drivers (e.g. MS SQL Server) the LIMIT/OFFSET will be emulated by the ResultSet. The behavior of the ResultSet methods will be identical regardless of whether LIMIT/OFFSET is emulated or performed natively (in SQL).

$SQL->setLimit(10, 5);

...whereas the first parameter is the limit and the second the offset value.

Sourdough does not support using OFFSET w/o LIMIT. Some drivers (e.g. Oracle) can do this natively, but others (MySQL, PostgreSQL, SQLite) cannot.


How many values returned?

Use the getRowsCount() method to return the number of records returned by current SELECT statement.

echo $Db->getRowsCount();

If you specified a LIMIT/OFFSET then getRowsCount() will only return the number of results after LIMIT/OFFSET has been applied.


Getting Column Values

The default method to get column values is to directly access the public record property which contains the values of the current row. In the future, there will be get*() methods which will typecast the values, but for now, stick with the record array.

while($DB->moveNext()) {
    echo $DB->record['name'] . " (" . $DB->record['id'] . ")\n";
}

// or
while($DB->moveNext()) {
    foreach($DB->record as $key => $value) {
        print "$key: $value\n";
    }
}

Currently you need to do type conversions yourself, e.g. check for this:

$val = $DB->record['id'];
if ($val !== null) $val = (int) $val;

Also you cannot rely on the type of the returned results. Many (most?) native PHP db driver methods will return strings for every column type, but others (e.g. MS SQL Server) will return integers for numeric columns, etc. So if you are doing type-sensitive PHP scripting, you should be aware of this.


ResultSet Indexing

By default results are indexed by field name (associative). You can specify numeric indexing after executing the query.

Numerically indexed columns start with 0. (This may change in the future. Following JDBC convention they start with 1.)

$SQL->create('users');
$SQL->addFields(array('id', 'name'));
$SQL->execute();

$DB->setFetchMode(Sd_Db::FETCH_NUM);

while($DB->moveNext()) {
    echo $DB->record[1] . " (" . $DB->record[0] . ")\n"
}

Warning: Before each query execution fetch mode will be reset to Sd_Db::FETCH_ASSOC. You always need to set the mode after an executed query if you with to use FETCH_NUM or FETCH_BOTH.

It is not recommended to use both numeric and assoc indexing (FETCH_BOTH) because not all drivers handle this natively and support for this might get deprecated some day.


Scrolling ResultSet Methods

Scrolling ResultSet features are supported well in MySQL, PostgreSQL, and SQLite. In other DBS it might be emulated.

// Move to first record
$DB->moveFirst();

// Move ahead 3 rows
$DB->moveRelative(3);

// Scroll backwards
$DB->movePrevious();

// Move to last position
$DB->moveLast();


ResultSetIterator

This is a planned feature which will be implemented before version 1.0. For now, use the traditional scrolling functions.


Updating the Database

To perform a database update you proceed the same way as you would with building a regular SELECT statement. All you need to do is to change the statement to change the execution command type.

// on statement creation...
$SQL->create('users', null, Sd_SqlHelper::EXEC_UPDATE);

// or manually afterwards...
$SQL->setExecMode(Sd_SqlHelper::EXEC_UPDATE);

An existing SELECT statment can be transformed into an UPDATE statement without creating a new one.

$SQL->create('users', 'id = 5');
$SQL->addField('name', 'Philip');
echo $SQL->getCommand();   // SELECT name FROM sourd_users WHERE id = 5;
$SQL->execute();

$SQL->setExecMode(Sd_SqlHelper::EXEC_UPDATE);
echo $SQL->getCommand();   // UPDATE sourd_users SET name = 'Philip' WHERE id = 5;
$SQL->execute();

You could as well use Sd_SqlHelper::EXEC_DELETE to create a DELETE statement. $SQL->getCommand() is intended for debugging purposes and always returns the final statement.

Information about the executed statement can again be taken from the database object:

$numaffected = $DB->getAffectedRows();

Advanced Queries

JOINs

Again the SQLHelper comes into use. To build a JOIN we need to specify all tables that will be used in the $SQL->create() statement. A join condition is defined by first creating a join with the addJoin() method and then adding its join ID to the addJoinCondition() method where we specify the affected fields.

$SQL->create(array('U' => 'users', 'S' => 'salary'));
$SQL->addFields(array('U.id', 'S.amount'));
$SQL->addJoinCondition('id', 'userid', $SQL->addJoin('U', 'S'));
$SQL->execute();

while ($DB->moveNext()){
    echo 'user: '.$DB->record['id'].' salary: '.$DB->record['amount'];
}

This example would produce a simple inner JOIN:

SELECT U.id, S.amount FROM users U, salary S WHERE U.id = S.userid;

SQLHelper also supports left and right outer joins. Also, you are not limited to the usage of a single join condition. You might add as many conditions as make sense to you.


Multiple ResultSets

In some occasions we need to use multiple resultsets. Usually a single resultset is fine, but as soon as we need to execute a query inside the loop of another resultset, we may run into trouble. In this case the query inside the loop would overwrite the above's resource ID and free its data.

Building a resultset basically means we clone an existing one. The original resultset then is ready to be overwritten by a new query. That's the way to do it:

$SQL->create('users');
$SQL->execute();

$rs = $DB->getRecordset();
while($rs->moveNext()) {
    $SQL->create('salary', 'userid = '.$rs->record['id']);
    $SQL->execute();
    echo 'userid: '.$DB->record['userid'].' '.
         'amount: '.$DB->record['amount']."\n";
}

The above sample actually doesn't make any sense as we could run this query also as a simple join, but it should demonstrate the use of multiple recordsets in a simple way. Please note the use of both $rs->record and $DB->record resultsets inside the loop.




Driven by coWiki 0.3.4 web collaboration tool.