Phormium documentation¶

Prerequisites¶

Phormium requires PHP 5.3 or greater with the PDO extension loaded, as well as any PDO drivers for databases to wich you wish to connect.

Via Composer¶

The most flexible installation method is using Composer.

Create a composer.json file in the root of your project:

{
    "require": {
        "phormium/phormium": "0.*"
    }
}

Install composer:

curl -s http://getcomposer.org/installer | php

Run Composer to install Phormium:

php composer.phar install

To upgrade Phormium to the latest version, run:

php composer.phar update

Once installed, include vendor/autoload.php in your script to autoload Phormium.

require 'vendor/autoload.php';

From GitHub¶

The alternative is to checkout the code directly from GitHub:

git clone https://github.com/ihabunek/phormium.git

In your code, include and register the Phormium autoloader:

require 'phormium/Phormium/Autoloader.php';
\Phormium\Autoloader::register();

Once you have installed Phormium, the next step is to set it up.

Configure database connections¶

Create a JSON configuration file which contains database definitions you wish to use. Each database must have a DSN string, and optional username and password if required.

{
    "databases": {
        "testdb": {
            "dsn": "mysql:host=localhost;dbname=testdb",
            "username": "myuser",
            "password": "mypass"
        }
    }
}

For details on database specific DSNs consult the PHP documentation.

A more detailed config file reference can be found in the configuration chapter.

Create a database model¶

You need a database table which will be mapped. For example, the following SQL will create a MySQL table called person:

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100),
    birthday DATE,
    salary DECIMAL
);

The table does not have to have a primary key, but if it doesn’t Phormium will not perform update or delete queries.

Create a Model class¶

To map the person table onto a PHP class, a corresponding Model class is defined. Although this class can be called anything, it’s sensible to name it the same as the table being mapped.

class Person extends Phormium\Model
{
    // Mapping meta-data
    protected static $_meta = array(
        'database' => 'testdb',
        'table' => 'person',
        'pk' => 'id'
    );

    // Table columns
    public $id;
    public $name;
    public $birthday;
    public $salary;
}

Public properties of the Person class match the column names of the person database table.

Additionaly, a protected static $_meta property is required which holds an array with the following values:

database

Name of the database, as defined in the configuration.

table

Name of the database table to which the model maps.

pk

Name of the primary key column (or an array of names for composite primary keys).

Try it out¶

Create a few test rows in the database table and run the following code to fetch them:

require 'vendor/autoload.php';
require 'Person.php';

Phormium\DB::configure('config.json');

$persons = Person::objects()->fetch();

Learn more about usage in the next chapter.

Databases¶

Each entry in databases has the following configuration options:

dsn

The Data Source Name, or DSN, contains the information required to connect to the database. See PDO documentation for more information.

username

The username used to connect to the database.

password

The username used to connect to the database.

attributes

Associative array of PDO attributes with corresponding values to be set on the PDO connection after it has been created.

When using a configuration array PDO constants can be used directly (e.g. PDO::ATTR_CASE), whereas when using a config file, the constant can be given as a string (e.g. "PDO::ATTR_CASE") instead.

For available attributes see the PDO attributes documentation.

Examples¶

Phormium\DB::configure([
    "databases" => [
        "db1" => [
            "dsn" => "mysql:host=localhost;dbname=db1",
            "username" => "myuser",
            "password" => "mypass",
            "attributes" => [
                PDO::ATTR_CASE => PDO::CASE_LOWER,
                PDO::ATTR_STRINGIFY_FETCHES => true
            ]
        ],
        "db2" => [
            "dsn" => "sqlite:/path/to/db2.sqlite"
        ]
    ]
]);

{
    "databases": {
        "db1": {
            "dsn": "mysql:host=localhost;dbname=db1",
            "username": "myuser",
            "password": "mypass",
            "attributes": {
                "PDO::ATTR_CASE": "PDO::CASE_LOWER",
                "PDO::ATTR_STRINGIFY_FETCHES": true
            }
        },
        "db2": {
            "dsn": "sqlite:\/path\/to\/db2.sqlite"
        }
    }
}

Phormium\DB::configure('/path/to/config.json');

databases:
    db1:
        dsn: 'mysql:host=localhost;dbname=db1'
        username: myuser
        password: mypass
        attributes:
            'PDO::ATTR_CASE': 'PDO::CASE_LOWER'
            'PDO::ATTR_STRINGIFY_FETCHES': true
    db2:
        dsn: 'sqlite:/path/to/db2.sqlite'

Phormium\DB::configure('/path/to/config.yaml');

Bootstrap¶

If you installed Phormium via Composer, just include vendor/autoload.php in your application and Phormium will be autoloaded. Afterwards, you have to configure Phormium using your configuration file.

require 'vendor/autoload.php';

Phormium\DB::configure('/path/to/config.json');

Alternatively, if you are not using Composer, Phormium has it’s own autoloader:

require '/path/to/phormium/src/Phormium/Autoloader.php';

Phormium\Autoloader::register();
Phormium\DB::configure('/path/to/config.json');

Querying individual records¶

Fetch a single record by primary key; throws exception if it doesn’t exist.

Person::get(13);

Fetch a single record by primary key; return null if it doesn’t exist.

Person::find(13);

Check if a record exists with the given primary key.

Person::exists(13);

Also works for composite primary keys:

Trade::get('2013-01-01', 123);
Trade::find('2013-01-01', 123);
Trade::exists('2013-01-01', 123);

Primary key can be given as an array:

$tradeID = array('2013-01-01', 123);
Trade::get($tradeID);
Trade::find($tradeID);
Trade::exists($tradeID);

Querying multiple records¶

To fetch all data from a table, run:

Person::all();

This is shorthand for calling:

Person::objects()->fetch();

The objects() method will return a QuerySet object which is used for querying data, and fetch() will form and execute the corresponding SQL query and return the results as an array of Person objects.

Filtering data¶

In order to retrieve only selected rows, QuerySets can be filtered. Filters are used to consturct a WHERE clause in the resulting SQL query.

Person::objects()
    ->filter('birthday', '<' '2000-01-01')
    ->fetch();

SELECT ... FROM person WHERE birthday < ?;

Person::objects()
    ->filter($column, '=',  $value)
    ->filter($column, '!=', $value)
    ->filter($column, '>',  $value)
    ->filter($column, '>=', $value)
    ->filter($column, '<',  $value)
    ->filter($column, '<=', $value)
    ->filter($column, 'IN', $array)
    ->filter($column, 'NOT IN', $array)
    ->filter($column, 'LIKE', $value)
    ->filter($column, 'ILIKE', $value)  // case insensitive like
    ->filter($column, 'NOT LIKE', $value)
    ->filter($column, 'BETWEEN', array($low, $high))
    ->filter($column, 'IS NULL')
    ->filter($column, 'NOT NULL')

use Phormium\Filter\Filter;

$filter = Filter::col('birthday', '<' '2000-01-01');

Person::objects()
    ->filter($filter)
    ->fetch();

Person::objects()
    ->filter('birthday', '<', '2000-01-01')
    ->filter('income', '>', 10000)
    ->fetch();

SELECT ... FROM person WHERE birthday < ? AND income > ?;

Table::objects()
    ->filter("col1 > col2")
    ->fetch();

SELECT ... FROM table WHERE col1 > col2;

PriceList::objects()
    ->filter('unit_price * quantity < ?', [100])
    ->fetch();

SELECT ... FROM price_list WHERE unit_price * quantity < ?;

use Phormium\Filter\RawFilter;

// Either by instantiating the RawFilter class directly
$filter = new RawFilter("col1 > col2");
$filter = new RawFilter("col1 > ?", [100]);

// Or using the raw() factory method
$filter = Filter::raw("col1 > col2");
$filter = Filter::raw("col1 > ?", [100]);

// And passing it into filter()
Table::objects()
    ->filter($filter)
    ->fetch();

// Conditions which don't involve any columns
Filter::raw("current_time < ?", ['16:00:00']);

// Mathematical expressions
Filter::raw("col1 * col2 / col3 < col4");

// Using SQL functions
Filter::raw("round(col1) < 0");

use Phormium\Filter\Filter;

$filter = Filter::_or(
    Filter::col('age', '<', 10),
    Filter::col('age', '>', 20),
);

Person::objects()
    ->filter($filter)
    ->fetch();

SELECT ... FROM person WHERE age < ? OR age > ?;

use Phormium\Filter\Filter;

$filter = Filter::_or(
    array('age', '<', 10),
    array('age', '>', 20),
);

use Phormium\Filter\Filter as f;

$filter = f::_or(
    f::col('age', '<', 10),
    f::col('age', '>', 20),
);

use Phormium\Filter\Filter as f;

Person::objects()->filter(
    f::_or(
        f::_and(
            f::col('id', '>=', 10),
            f::col('id', '<=', 20)
        ),
        f::_and(
            f::col('id', '>=', 50),
            f::col('id', '<=', 60)
        ),
        f::col('id', '>=', 100),
    )
)->fetch();

SELECT
    ...
FROM
    person
WHERE ((
    (id >= ? AND id <= ?) OR
    (id >= ? AND id <= ?) OR
    id >= ?
));

Lazy execution¶

QuerySets are lazy - no queries will be executed on the database until one of the fetch methods are called.

QuerySets are immutable. Filtering and ordering of querysets produces a new instance, instead of changing the existing one.

Therefore watch out not to do this by accident:

$qs = Person::objects();
$qs->filter('id', '>', 10); // QUERYSET NOT CHANGED
$qs->fetch();

Instead do this:

$qs = Person::objects();
$qs = $qs->filter('id', '>', 10); // Better
$qs->fetch();

Ordering data¶

QuerySets can also be ordered to determine the order in which matching records will be returned.

To apply ordering:

Person::objects()
    ->orderBy('id', 'desc')
    ->fetch();

Ordering by multiple columns:

Person::objects()
    ->orderBy('id', 'desc')
    ->orderBy('name', 'asc')
    ->fetch();

Fetching data¶

There are several methods for fetching data. All these methods perform SQL queries on the database.

Person::objects()
    ->filter('birthday', '<', '2000-01-01')
    ->filter('income', '>', 10000)
    ->fetch();

Person::objects()
    ->filter('id', '=', 13)
    ->single();

Person::get(13);

Person::objects()->values('id', 'name');

array(
    array('id' => '1', 'name' => 'Ivan'),
    array('id' => '1', 'name' => 'Marko'),
)

Person::objects()->valuesList('id', 'name');

array(
    array('1', 'Ivan'),
    array('1', 'Marko'),
)

Person::objects()->valuesFlat('name');

array(
    'Ivan',
    'Marko'
)

Person::objects()
    ->filter('income', '<', 10000)
    ->count();

Person::objects()
    ->filter('birthday', '>=', '2001-01-01')
    ->distinct('name');

Person::objects()
    ->filter('birthday', '>=', '2001-01-01')
    ->distinct('name', 'income');

array('Ivan', 'Marko');

array(
    array(
        'name' => 'Ivan',
        'income' => '5000'
    ),
    array(
        'name' => 'Ivan',
        'income' => '7000'
    ),
    array(
        'name' => 'Marko',
        'income' => '3000'
    ),
)

Person::objects()
    ->filter('birthday', '<', '2000-01-01')
    ->avg('income');

Limited fetch¶

Limited fetch allows you to retrieve only a portion of results matched by a QuerySet. This will limit the data returned by fetch(), values() and valuesList() methods. distinct() is currently unaffected.

QuerySet::limit($limit, $offset)

If a $limit is given, that is the maximum number of records which will be returned by the fetch methods. It is possible fetch will return fewer records if the query itself yields less rows. Specifying NULL means without limit.

If $offset is given, that is the number of rows which will be skipped from the matched rows.

For example to return a maximum of 10 records:

Person::objects()
    ->limit(10)
    ->fetch();

It often makes sense to use limit() in conjunction with orderBy() because otherwise you will get un unpredictable set of rows, depending on how the database decides to order them.

Person::objects()
    ->orderBy('name')
    ->limit(10, 20)
    ->fetch();

This request returns a maximum of 10 rows, while skipping the first 20 records ordered by the name column.

Writing data¶

$person = new Person();
$person->name = "Frank Zappa";
$person->birthday = "1940-12-20";
$person->save();

// This is quivalent to the above example
$personData = array(
    "name" => "Frank Zappa",
    "birthday" => "1940-12-20"
);
Person::fromArray($personData)->save();

$person = Person::get(37);
$person->birthday = "1940-12-21";
$person->salary = 10000;
$person->save();

// This is quivalent to the above example
$updates = array(
    "birthday" => "1940-12-21"
    "salary" => 10000
);

$person = Person::get(37);
$person->merge($updates);
$person->save();

$person = Person::objects()
    ->filter('name', 'like', 'X%')
    ->update([
        'name' => 'Xavier'
    ]);

Person::get(37)->delete();

$person = Person::objects()
    ->filter('salary', '>', 100000)
    ->delete();

Custom queries¶

Every ORM has it’s limits, and that goes double for Phormium. Sometime it’s necessary to write the SQL by hand. This is done by fetching the desired Connection object and using provided methods.

Connection::execute($query)

// Lowercase all names in the person table
$query = "UPDATE person SET name = LOWER(name);
$conn = DB::getConnection('myconn');
$numRows = $conn->execute($query);

Connection::query($query[, $fetchStyle[, $class]])

$query = "SELET * FROM x JOIN y ON x.pk = y.fk";
$conn = DB::getConnection('myconn');
$data = $conn->query($query);

Connection::preparedQuery($query[, $arguments[, $fetchType[, $class]]])

$query = "SELET * FROM x JOIN y ON x.pk = y.fk WHERE col1 > ? AND col2 < ?";
$arguments = array(10, 20);
$conn = DB::getConnection('myconn');
$data = $conn->preparedQuery($query, $arguments);

$query = "SELET * FROM x JOIN y ON x.pk = y.fk WHERE col1 > :val1 AND col2 < :val2";
$arguments = array(
    "val1" => 10,
    "val2" => 20
);
$conn = DB::getConnection('myconn');
$data = $conn->preparedQuery($query, $arguments);

$pdo = DB::getConnection('myconn')->getPDO();
$stmt = $pdo->prepare($query);
$stmt->execute($args);
$data = $stmt->fetchAll();

Callback transactions¶

By passing a callable to DB::transaction(), the code within the callable will be executed within a transaction. If an exception is thrown within the callback, the transaction will be rolled back. Otherwise it will be commited once the callback is executed.

For example, if you wanted to increase the salary for several Persons, you might code it this way:

$ids = array(10, 20, 30);
$increment = 100;

DB::transaction(function() use ($ids, $increment) {
    foreach ($ids as $id) {
        $p = Person::get($id);
        $p->income += $increment;
        $p->save();
    }
});

If any of the person IDs from $ids does not exist, Person::get($id) will raise an exception which will roll back any earlier changes done within the callback.

Manual transactions¶

It is also possible to control the transaction manaully, however this produces somewhat less readable code.

Equivalent to the callback example would look like:

$ids = array(10, 20, 30);
$increment = 100;

DB::begin();

try {
    foreach ($ids as $id) {
        $p = Person::get($id);
        $p->income += $increment;
        $p->save();
    }
} catch (\Exception $ex) {
    DB::rollback();
    throw new \Exception("Transaction failed. Rolled back.", 0, $ex);
}

DB::commit();

Query events¶

The following events are emitted when running a database query.

Note that not all events are triggered for each query. Only prepared queries will trigger preparing and prepared events. Only queries which return data will trigger fetching and fetched events.

Event callback functions use the following arguments:

Transaction events¶

The following events are triggered when starting or ending a database transaction.

Callbacks for these events have a single argument: the PhormiumConnection on which the action is executed.

Examples¶

use Logger;
use Phormium\Events;

$log = Logger::getLogger('query');

Event::on('query.started', function($query, $arguments) use ($log) {
    $log->info("Running query: $query");
});

Event::on('query.error', function ($query, $arguments, $connection, $ex) use ($log) {
    $log->error("Query failed: $ex");
});

use Phormium\Event;

class Stats
{
    private $active;

    private $stats = array();

    /** Hooks onto relevant events. */
    public function register()
    {
        Event::on('query.started', array($this, 'started'));
        Event::on('query.completed', array($this, 'completed'));
    }

    /** Called when a query has started. */
    public function started($query, $arguments)
    {
        $this->active = array(
            'query' => $query,
            'arguments' => $arguments,
            'start' => microtime(true)
        );
    }

    /** Called when a query has completed. */
    public function completed($query)
    {
        $active = $this->active;

        $active['end'] = microtime(true);
        $active['duration'] = $active['end'] - $active['start'];

        $this->stats[] = $active;
        $this->active = null;
    }

    /** Returns the collected statistics. */
    public function getStats()
    {
        return $this->stats;
    }
}

$stats = new Stats();
$stats->register();

Example¶

Models for these tables might look like this:

class Person extends Phormium\Model
{
    protected static $_meta = array(
        'database' => 'exampledb',
        'table' => 'person',
        'pk' => 'id'
    );

    public $id;

    public $name;

    public function contacts()
    {
        return $this->hasChildren("Contact");
    }
}

class Contact extends Phormium\Model
{
    protected static $_meta = array(
        'database' => 'exampledb',
        'table' => 'contact',
        'pk' => 'id'
    );

    public $id;

    public $person_id;

    public $value;

    public function person()
    {
        return $this->hasParent("Person");
    }
}

Note that these functions return a filtered QuerySet, so you need to call one of the fetching methods to fetch the data.

// Fetching person's contacts
$person = Person::get(1);
$contacts = $person->contacts()->fetch();

// Fetching contact's person
$contact = Contact::get(5);
$person = $contact->person()->single();

Returning a QuerySet allows you to further filter the result. For example, to return person’s contact whose value is not null:

$person = Person::get(1);

$contacts = $person->contacts()
    ->filter('value', 'NOT NULL')
    ->fetch();

Overriding defaults¶

Phormium does it’s best to guess the names of the foreign key column(s) in both tables. The guesswork, however depends on:

• Naming classes in CamelCase (e.g. FooBar)
• Naming tables in lowercase using underscores (e.g. foo_bar)
• Naming foreign keys which reference the foo_bar table foo_bar_$id, where $id is the name of the primary key column in some_table.

The following code:

$this->hasChildren("Contact");

is shorthand for:

$this->hasChildren("Contact", "person_id", "id");

where person_id is the name of the foreign key column in the child table (Contact), and id is the name of the referenced primary key column in the parent table (Person).

If your keys are named differently, you can override these settings. For example:

$this->hasChildren("Contact", "owner_id");

Composite keys¶

Relations also work for tables with composite primary/foreign keys.

For example, consider these tables:

Models for these tables can be implemented as:

class Post extends Phormium\Model
{
    protected static $_meta = array(
        'database' => 'exampledb',
        'table' => 'post',
        'pk' => ['date', 'no']
    );

    public $date;

    public $no;

    public function tags()
    {
        return $this->hasChildren("Tag");
    }
}

class Tag extends Phormium\Model
{
    protected static $_meta = array(
        'database' => 'exampledb',
        'table' => 'tag',
        'pk' => 'id'
    );

    public $id;

    public $post_date;

    public $post_no;

    public function post()
    {
        return $this->hasParent("Post");
    }
}