======================================================================
                                TROOL
======================================================================
----------------------------------------------------------------------
          Object-oriented interface to SQL databases for PHP
----------------------------------------------------------------------

:Author: Roman Neuhauser
:Contact: neuhauser@sigpipe.cz
:Copyright: This document is in the public domain.

.. contents::

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.

Hello World!
++++++++++++

This example program uses the PostgreSQL TROOL driver to create and
populate a table, select data from it, and finally rollback the whole
operation.

The structure of the array used to connect to a database server is
driver-specific, as is the SQL syntax and semantics.  For example,
Oracle is incapable of rolling back DDL statements; and Microsoft
SQL Server didn't *really* support the *VALUES* table constructor
until version 2008.

::

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

Output: ::

    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`_.

.. _Mercurial: http://hg-scm.org/
.. _repository: https://hg.sigpipe.cz/
.. _tarballs: http://people.freebsd.org/~kientzle/libarchive/
.. _bzip2ed: http://www.bzip.org/
.. _download directory: http://codex.sigpipe.cz/trool/dist/


Features
========

Query Language Syntax
+++++++++++++++++++++

TROOL's focus is on providing a good unified object-oriented interface,
The SQL side of things is outside the scope of this library.
it doesn't attempt to hide differences between SQL dialects and
feature sets.  There's no homegrown query language.

Parameter binding
+++++++++++++++++

Parameter binding works or not depending on the underlying PHP
extension.  Monkeypatching SQL parameter binding onto backends
without native support for this feature requires parsing vendor-
specific SQL grammars (a huge goal and a moving target).

Blobs and stored procedures
+++++++++++++++++++++++++++

Basically unsupported.

Supported backends
==================

Currently supported backends are:

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

mysql
+++++

* PHP configure option: ``--with-mysql``
* Parameter binding: no.

odbc
++++

* PHP configure option: see `ODBC Installation`_ in the PHP manual
* Parameter binding: no.

pg
++

* PHP configure option: ``--with-pgsql``
* Parameter binding: yes.

sqlite
++++++

* PHP configure option: ``--with-sqlite``
* Parameter binding: no.

sqlsrv
++++++

* PHP configure option: ``--enable-sqlsrv``
* Parameter binding: yes.
* Requires version 1.1 of the sqlsrv driver.

.. _ODBC Installation:
   http://www.php.net/manual/en/odbc.installation.php

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


.. vim: ft=rst tw=70