Databases

Userland database driver implementations

at 2010-07-09 in Experiments, Databases by friebe

Following a discussion we had about software packaging for our test, qa and live clusters (most of which run Debian Lenny, meaning it brings PHP 5.2.6 with the mssql extension, unfortunately unsuitable for ASE 15 / univarchar fields), we decided two things:

We need to compile a more recent PHP version with sybase_ct ourselves
We should investigate in more independence from installed system libraries and PHP extensions

The second point is really not a new decision, the XP Framework has a long history of userland implementations, reflection without the reflection library in PHP4 times, XML builders without ext/dom, the HTTP protocol without ext/curl or http_build_query or JSON without ext/json, to name just a few, and - more recently, FTP without ext/ftp, partially due to the fact that PHP didn't provide these functionalities at the time we implemented them and partially due to the fact that we couldn't count on the extensions being available everywhere we wanted to install and run the XP Framework.

(more)

RFC #0203: Unbuffered queries

at 2010-06-04 in Databases, RFCs by friebe

Scope of Change
Unbuffered queries will be supported by a dedicated API. Instead of using rdbms.DBConnection::query() (or any of the insert, update, delete or select methods), the result-only rdbms.DBConnection::open() method will support unbuffered queries.

Rationale
Incremental row processing to save memory.

Read the full RFC here

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()