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

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

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.

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

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
  // ]
}

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

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

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]"');

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

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.