RDBMS support for the XP Framework

RDBMS access APIs, connection manager, reverse engineering, O/R mapping.

The DriverManager model

To retrieve a connection class from the driver manager, you need to use the rdbms.DriverManager class.

use rdbms\DriverManager;

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

The DriverManager class expects a unified connection string (we call it DSN).

Supported drivers

The DriverManager will select an appropriate driver from the DSN string via its name. This will load an implemenation class which is either based on a PHP extension or implements the protocol to communicate with the database system in userland code. For the latter case, you need not do anything to your PHP setup; if there's a hard dependency on a PHP extension, you need to install that before you can use the driver.

Database system	DSN name	PHP Extensions	Userland driver
MySQL	mysql	ext/mysql or ext/mysqli	✅
PostgreSQL	pgsql	ext/pgsql
SQLite3	sqlite	ext/sqlite3
Interbase/FireBird	ibase	ext/interbase
Sybase	sybase	ext/sybase-ct	✅
MSSQL	mssql	ext/mssql or ext/sqlsrv	✅

Basics

Once we have fetched a specific database connection class, we can now invoke a number of methods on it.

Selecting

Selecting can be done with the "one-stop" method select() which will return all results into an array. Alternatively, the query() method allows iterative fetching.

$news= $conn->select('news_id, caption, author_id from news');
// $news= [
//   [
//     'news_id'   => 12,
//     'caption'   => 'Hello World',
//     'author_id' => 1549
//   ]
// ]

$q= $conn->query('select news_id, caption, author_id from news');
while ($record= $q->next()) {
  // $record= [
  //   'news_id'   => 12,
  //   'caption'   => 'Hello World',
  //   'author_id' => 1549
  // ]
}

Inserting

To "bind" parameters to an SQL query, the query, select, update, delete and insert methods offer a printf style tokenizer and support varargs syntax. These take care of NULL, type handling and proper escaping for you.

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

Updating

The update() and delete() methods will return the number of affected rows, in case you're interested.

$conn->update('news set author_id= %d where author_id is null', $authorId);

Deleting

Even if your RDBMS requires you to use single quotes (or what-else), the API will take care of rewriting string literals for you.

$conn->delete('from news where caption = "[DELETE]"');

Exceptions

All of the above methods will throw exceptions for failed SQL queries, syntax errors, connection failure etc. All these exceptions are subclasses of rdbms.SQLException, so to catch all possible errors, use it in the catch clause:

+ rdbms.SQLException
|-- rdbms.ConnectionNotRegisteredException
|-- rdbms.SQLConnectException
|-- rdbms.SQLStateException
`-- rdbms.SQLStatementFailedException
    |-- rdbms.SQLConnectionClosedException
    `-- rdbms.SQLDeadlockException

Transactions

To start a transaction, you can use the connection's begin(), commit() and rollback() methods as follows:

public function createAuthor(...) {
  $tran= $conn->begin(new Transaction('create_author'));

  try {
    $id= $conn->insert('into author ...');
    $conn->insert('into notify ...');

    $tran->commit();
    return $id;
  } catch (SQLException $e) {
    $tran->rollback();
    throw $e;
  }
}

Note: Not all database systems 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.