Databases

5.7.4-RELEASE: Databases

at 2009-08-25 in Databases, Announcements, Releases by friebe

The XP team is proud to announce the release of 5.7.4. This release features various improvements to the rdbms API:

Microsoft SQL server support
InterBase/Firebird support
Deadlock handling via rdbms.SQLDeadlockException
Verbose handling for the case a database driver is not available in DriverManager
PostgreSQLConnection behaviour fixes
Refactoring and code cleanup

...and more, all of which is listed here!

Deadlock handling

at 2009-06-16 in Examples, Databases, RFCs by friebe

The changes implemented in RFC #1059 make handling deadlocks easier. During deadlocks you might want to your program behave differently than during "regular" statement failures. For example, in Sybase, all transactions, regardless of their nesting level, are rolled back during a deadlock!

You may have written sourcecode as follows in the past:

<?php 
  try {
    // ...
  } catch (SQLStatementFailedException $e) {
    if (1205 == $e->getErrorcode()) {
      // Deadlock
    }
  }
?>

The problems should be obvious to the reader: The hardcoded errorcode (here the relevant one for Sybase), the very unelegant if, the missing portability for other drivers (PostgreSQL for example).

(more)

rdbms.DriverManager and supported connections

at 2009-06-09 in Databases, Announcements by friebe

The rdbms.DriverManager class has been changed to only support drivers when the required extension is available. This changes fatal errors to exceptions in the following use-case:

<?php 
  $conn= DriverManager::getConnection('mysql://localhost')->connect();
?>

Before (assuming ext/mysql is not loaded), this would print Fatal error: Call to undefined function mysql_connect(), now it raises an exception:

  Exception rdbms.DriverNotSupportedException (No driver registered for mysql
    - is the library loaded?) 
    at rdbms.DriverManager::getConnection((0x11)'mysql://localhost') [...]
    at ...

(more)

RFC #0159: Deadlock handling

at 2008-04-12 in Databases, RFCs by friebe

Scope of Change
A new class rdbms.SQLDeadlockException will be introduced. The rdbms drivers will handle dead lock situations specially and throw this new exception instead of a generic SQLStatementFailedException.

Rationale
Deadlocks are a very special case in database systems and often need special programmatic handling.

Read the full RFC here

Tracking SQL statements sent

at 2007-07-18 in Unittests, Databases by friebe

The XP framework's RDBMS access API offers logging of SQL queries sent to the server by adding observers to database connections:

<?php 
  // First, set up logger
  Logger::getInstance()->getCategory('sql')->addAppender(new ConsoleAppender());
 
  // Retrieve connection and add the log observer for the "sql" category to the connection
  $conn= DriverManager::getConnection('sybase://user:pass@dbserv01');
  $conn->addObserver(LogObserver::instanceFor('sql'));
?>

Now SQL statements and other connection events will be logged to the console. For most purposes, this will suffice.

If you need to store all SQL statements sent to the server in an array, for example to assert on them in unittest environments, here's how:

(more)

Database logging beautified

at 2007-07-05 in Databases by friebe

On a short note: Database logging output has been made more condense.

Old output:

[23:05:34 4248 debug] rdbms.DBEvent(query) {'select max(account_id) as `value` from test.account'} 
[23:05:34 4248 debug] rdbms.DBEvent(queryend) {MySQLResultSet::__set_state(array(
   'handle' => NULL,
   'fields' => 
  array (
    'value' => 'int',
  ),
   '__id' => NULL,
))}

New output:

[23:06:09 4232 debug] rdbms.DBEvent(query) {"select max(account_id) as `value` from test.account"} 
[23:06:09 4232 debug] rdbms.DBEvent(queryend) {rdbms.mysql.MySQLResultSet(Resource id #140)@[
  value => "int"
]}

I especially hated the ugly __set_state thing being generated by var_export()