Introduction

TROOL is an object-oriented interface for SQL databases. The design builds on RAII for resource management and transaction safety, and iterators for resultset retrieval. Multiple SQL DBMSs are supported.

# begin backend-or-driver-specific code

require_once 'impl/pg.php';

$dsn = array(
    'host' => 'localhost',
    'user' => 'roman',
    'passwd' => '',
    'dbname' => 'test',
);
$ddl = "CREATE TABLE t (i INT NOT NULL PRIMARY KEY, c VARCHAR(100))";
$ins = "INSERT INTO t (i, c) VALUES (1, 'Hello'), (2, 'world')";
$sel = "SELECT i, c FROM t";

# begin backend-and-driver-agnostic code

require_once 'trool.php';

$conn = new TROOL($driver, $dsn);

$txn = $conn->txn();

$txn->query($ddl);
$txn->query($ins);

$rs = $txn->query($sel);

foreach ($rs as $i => $row) {
    printf("%d: %s\n", $i, var_export($row, 1));
}

unset($txn);
unset($conn);

0: array (
   'i' => '1',
   'c' => 'Hello',
)
3: array (
   'i' => '2',
   'c' => 'world',
)

Downloads

The latest source can be checked out from the Mercurial repository.

TROOL source releases are distributed as bzip2ed tarballs. Your operating system should include compatible extraction tools.

All TROOL releases can be found in the download directory.

Features

Supported backends

Currently supported backends are:

• Microsoft SQL Server (sqlsrv_* functions)
• MySQL (mysql_* functions)
• ODBC (odbc_* functions)
• PostgreSQL (pg_* functions)
• SQLite (sqlite_* functions)

Example

$dsn = array(
    'host' => 'localhost',
    'user' => 'roman',
    'passwd' => '',
    'dbname' => 'test',
);

$f = new TROOLFactory_default;
$driver = new TROOLImpl_pg;

try {
    # either succeeds (connects to the requested database)
    # or throws TROOLConnectionFailed
    $conn = $f->conn($driver, $dsn);

    # begins a transaction or throws TROOLTransactionFailed
    $txn = $conn->txn();

    # queries the database or throws TROOLQueryFailed
    $rs = $txn->query('SELECT * FROM r');
    foreach ($rs as $row) {
        # ...
    }

    # updates the database or throws TROOLQueryFailed
    $txn->query(
        'UPDATE r SET a = $1 WHERE b = $2'
      , array($a, $b)
    );

    # commits the transaction or throws TROOLTransactionFailed
    $txn->commit();
} catch (TROOLFailure $e) {
    # $txn has been rolled back
}

Reference

interface TROOLConn
{
    TROOLTxn txn();
}

interface TROOLTxn
{
    void commit();
    TROOLStmt query(string $sql, array $params = array());
}

interface TROOLStmt
extends
  Iterator
{
}

class TROOL
implements
  TROOLConn
;

interface TROOLImpl;

interface TROOLFactory
{
    TROOLConn conn(TROOLImpl $impl, array $options);
    TROOLTxn txn(TROOLImpl $impl, resource $conn);
    TROOLStmt stmt(TROOLImpl $impl, resource $rsrc);
}

interface TROOLImpl
{
    resource connect(array $options);
    unspecified disconnect(resource $conn);
    bool begin(resource $conn);
    bool commit(resource $conn);
    bool rollback(resource $conn);
    resource query(resource $conn, string $query, array $options);
    unspecified free_result(resource $rsrc);
    unspecified data_seek(resource $rsrc, int $pos);
    array (associative) fetch_assoc(resource $rsrc, int $pos);
    array (numeric) fetch_row(resource $rsrc, int $pos);
}