Databases: Basics

[ Basics | Object Persistence ]

The entry point class for all database connections is the DriverManager class from the rdbms package.

  uses('rdbms.DriverManager');    
  
  $conn= DriverManager::getConnection('sybase://user:pass@server/NICOTINE');

The connection object returned is an instance of a driver-specific subclass of the DBConnection class. In the above example, the connection object would be instance of SybaseConnection.

To query a database using SQL statements, you can either use the generic query() method or one of the insert(), update(), delete() and select() methods.

query

The query() method returns a ResultSet object on which one can iterate. Its next() method return hashes with keys corresponding to the database fields and values to their values.

  $q= $conn->query('select person_id, realname, lastchange from person where person_id= 1');
  while ($record= $q->next()) {
    // record= [
    //   'person_id'  => 1,
    //   'realname'   => 'Übercoder',
    //   'lastchange' => util.Date<2008-05-10 12:14:00>
    // ]
  }

select

The select() method returns all results into an array of hashes and is particularily useful when it comes to small resultsets.

  $a= $conn->select('author_id, person_id from author where bz_id < 30001');
  // a = [
  //   0 => [
  //     'author_id'  => 1,
  //     'person_id'  => 7
  //   ],
  //   1 => [
  //     'author_id'  => 22,
  //     'person_id'  => 3
  //   ],
  // ]

Note that the SQL keyword "select" needs to be omitted from the above SQL statement string when using the select() method.

insert / update / delete

The insert, update() and delete() methods returns the number of affected rows:

  $n= $conn->insert('option_matrix (contract_id, option_id) values (10, 501)');
  // n= 1
  
  $n= $conn->update('contract set lastchange= getdate() where contract_id= 1');
  // n= 1 (assuming a contract w/ id #1 exists)
  
  $n= $conn->delete('from customer where customer_id= -1');
  // n= 0 (assuming no customer w/ negative ids exist)

All methods will throw exceptions for any errors encountered during their operation. All these exceptions are subclasses of SQLException, so to catch all possible errors, use it in the catch clause.

Basically, to handle errors nicely, you'd usually do something like this:

  try {
    $conn->query('update news set author_id= 2 where author_id= 1');
  } catch (SQLException $e) {
    // Handle it
  }

If you want to distinguish between the different cases, you can do so in a quite fine-grained way:

  try {
    $conn->query('update news set author_id= 2 where author_id= 1');
  } catch (SQLConnectionClosedException $ce) {
    // Connection was closed in the mean-time
  } catch (SQLDeadlockException $de) {
    // Query caused a deadlock
  } catch (SQLStatementFailedException $fe) {
    // Statement failed (e.g. syntax error in SQL)
  } catch (SQLStateException $se) {
    // Previous attempt to (re-)connect failed
  } catch (SQLException $e) {
    // Any other error
  }

In all of the above examples, you notice all the SQL is unparametrized. In most usecases, you'd want to pass parameters to the statements though. To "bind" parameters to an SQL query, all of the afforementioned methods offer a printf style tokenizer and support varargs syntax. These take care of NULL and proper escaping for you.

  // Selecting
  $q= $conn->query('select * from news where news_id= %d', $newsId);
  
  // Inserting
  $conn->insert('
    into news (
      caption, author_id, body, extended, created_at
    ) values (
      %s, -- caption
      %d, -- author_id
      %s, -- body
      %s, -- extended
      %s  -- created_at
    )',
    $caption,
    $authorId,
    $body,
    $extended,
    Date::now()
  );

Tokens

The following tokens are recognized:

In addition to the format token, the following rules apply:

• If the argument is NULL (not FALSE, or an empty string, === NULL in PHP terms), the token is replaced by the (unquoted) word NULL.
• Passing an array will result in a comma separated list of replaced tokens. This is particularily useful when dealing with (SQL) in (...).
• If the argument is a Date object, a rdbms-specific representation of the date is put in place of the token.
• Argument numbering is also supported: Any token may be preceded by an offset (like in PHP's printf), as follows: %1$s

To start a transaction, you can use the connection's begin method. It will return a Transaction instance.

  public function createAuthor(...) {
    try {
      $tran= $conn->begin(new Transaction('create_author'));
      
      $id= $conn->insert('into author ...');
      $conn->insert('into notify ...');
      
      $tran->commit();
      return $id;
    } catch (SQLException $e) {
      $tran && $tran->rollback();
      throw $e;
    }
  }

Note: Not all RDBMS' support transactions, and of those that do, not all support nested transactions. Be sure to read the manual pages of the RDBMS you are accessing.

When working with databases, it is most common to be log the client/server communication, especially during development. The database API integrates with the util.log package to support this. If you already have a logger set up, it is as easy as modifying the connection string:

  $conn= DriverManager::getConnection('mysql://localhost/NEWS?log=default');

To set up a logger category, use the following:

  Logger::getInstance()->getCategory()->addAppender(new FileAppender('debug.log'));

If you want to split usual debug output from SQL logging, you can use a different logger category:

  // Add a FileAppender logging to sql.log for the "sql" category 
  Logger::getInstance()->getCategory('sql')->addAppender(new FileAppender('sql.log'));
  
  // Make connection log to "sql" category
  DriverManager::getConnection('mysql://localhost/NEWS?log=sql');

Often it is necessary to use database connections in various places within sourcecode. Imagine a database-driven web application such as a weblog, forum, or even a configuration suite (such as 1&1's control panel). In either of the aforementioned you will probably use the database connection in state classes (the ones reflecting a page or a set of pages), in handlers (the classes handling submitted data), in helper classes, and maybe in even more places.

The singleton ConnectionManager class releaves you of the pain of always having to pass the database connection to all methods that (may) need one.

First, you'll need to set up the connection manager. You do this by either registering connections manually:

  ConnectionManager::getInstance()->register(
    DriverManager::getConnection('sybase://user:pass@server/CAFFEINE?autoconnect=1'),
    'caffeine'
  );

...or by using a configuration file:

  ConnectionManager::getInstance()->configure(
    new Properties('etc/connections.ini')
  );

For the latter case, the property file etc/connections.ini needed will look like this:

  ; Database configuration
  [caffeine]
  dsn="sybase://user:pass@server/CAFFEINE?autoconnect=1"

Note: It is generally a good idea to use the property file approach: This way, no database credentials exist in the sourcecode.

The connection manager, once set up, is globally available; its instance can be retrieved by using the getInstance() method.

  $conn= ConnectionManager::getInstance()->getByHost('caffeine', 0);
  $conn->query('exec sp_brew');

Table of contents

• Querying
• Exceptions
• Preparing statements
• Transactions
• Logging
• The connection manager