Database

Bifröst is not an ORM and it will never be one! It´s usage is to have a nice and secure way to write queries for an PDO supported database. If you don´t use database specific commands it will be, thanks to pdo, database independent.

How Create a new database instance

To create a new Bifröst instance you need a Config object. The Config object have different options like username, password, the DSN for the connection and the default DateTimeFormat for your Database. You will use the default PDO DSN string without username and password! You also can add PDO options with the method

Don´t overwrite the ErrorMode (PDO::ATTR_ERRMODE)!

$config->addPdoOption('PDO::MYSQL_ATTR_INIT_COMMAND', 'SET NAMES utf8');

Examples for PDO DSN:

mysql (PHP Documentation)

sqlite (PHP Documentation)

Usage:

    $config = new Config();
    $config->setUsername('username');
    $config->setPassword('secrect');
    $config->setDsn('mysql:host=localhost;dbname=testdb');
    $config->setDateTimeFormat('Y-m-d H:i:s'); // This is the default
    $config->addPdoOption('PDO::MYSQL_ATTR_INIT_COMMAND', 'SET NAMES utf8');
    $database = new Database($config);

You should not switch between databases with a SQL command. This will cause troubles! Bifröst ist designed to be used with multiple database instances.

Bifröst will not establish a connection to the database until it is really required to. That means it´s cheap to produce all your database objects -but still you should use a factory or DI-Container. I tried also to look at detail on the object count references to be long running process compatible without memory leaks.

For Example:

    $masterConfig = new Config();
    ...
    $slaveConfig = new Config();
    ...
    $masterDatabase = new Database($masterConfig);
    $slaveDatabase = new Database($slaveConfig);

    // or

    $product1Config = new Config();
    ...
    $product2Config = newConfig();
    ...
    $product1Database = new Database($product1Config);
    $product2Database = new Database($product2Config);

Run SQL

You can run directly SQL with the Bifröst instance. This is usefully for fixtures, init scripts and UnitTests which will create a new database or alter some tables, etc. You should use this method just for that case!

...
$db = new Database($config);
    $createSQL = file_get_contents(__DIR__ . '/fixtures.sql');
    $db->run($createSQL);

AddActionAfterConnect

Some PDO drivers (namely SQLite) support the possibility to add functions which are available to SQL. This feature is currently marked as experimental but Bifröst supports it. It´s important to know, that the actions will be added after a connection is established and just can be added before a connection is established. The Method addActionAfterConnect awaits a Closure (PHP 5.3 compatible) which gets a PDO instance as first param.

Example:

    $config = new Config();
    $config->setUsername('user');
    $config->setDsn('sqlite::memory:');
    $sqliteDb = new Database($config);

    $sqliteDb->addActionAfterConnect(function (PDO $pdo) {
        $pdo->sqliteCreateFunction('md5rev', function ($string) {
            return strrev(md5($string));
            }, 1);
    });

    $sqliteDb->run(self::$createSQL);

    $row = $sqliteDb->query("SELECT username, md5rev(username) FROM  users 
                            WHERE id = :ID")
        ->bindInt('ID', 1)
        ->fetchRow();

Query

The Query class use a fluent interface. It´s designed to be nice, logical and natural. It enforces strong typing, so you need to parse and cast your values. Internally it uses the PreparedStatement from PDO. In that combination, you should be not vulnerable to SQL Injection and type problems with your SQL ('1' != 1). Look at Examples if you wan´t to see usage examples.

How to use Bifröst to query your database

You need a Bifröst instance and then you call the method "query" with your SQL query. Bifröst will automatically handle the rest for you (like establish connection to the database, is this a new query?, can I reuse this already prepared query, etc).

Example

    ...
    $database = new Database($config);
    $database->query("SELECT * FROM `users`")
        ->fetchAll();

Usage

You define your query with placeholders. A placeholder should be uppercase and start with a double dash like: :ID, :START_DATE, :END_DATE, :USERNAME.

SELECT * FROM `users` WHERE id = :ID

Now you can bind values to the :ID placeholder with the bind methods. Caution: You don´t use the double dash in the key with the bind methods! )

    $database = new Database($config);
    $database->query('SELECT * FROM `users` WHERE id = :ID')
        ->bindInt('ID', 1)

Bind methods

The bind methods needs the key they should binded to and the value which should be bindet. The value must be typesafe or else it will throw an \Sindri\Database\Exception\InvalidArgumentException 

    bindInt($key, $value)
    bindFloat($key, $value)
    bindString($key, $value)

If your query is in prepared state (Fetch-Method, execute(), prepare(...) ) and you try to bind a key, which is not known to it, it will throw an \Sindri\Database\Exception\InvalidArgumentException.

BindArray method

The bind array method does some internal handling. It determines the type of the values and bind with the correct method. It´s also possible to bind an array with no elements. This will not break your SQL query if you write your query in the correct syntax. The syntax is

    "SELECT * FROM `users` WHERE id IN (:IDS)"

It´s important to understand how Array-Binding works. If you bind an array with 4 entries to the key IDS Bifröst will transform it in something like ID_0, ID_1, ID_2, ID_3 and bind its value to it. When the query get´s in prepared state (Fetch-Method, execute(), prepare(...) ), then you get an \Sindri\Database\Exception\InvalidArgumentException, if you try to reuse the query with a differend sized array!

    bindArray($key, $value)

BindDate methods

There are two bindDate methods. One will bind your DateTime object with your timezone setting the other one will bind your DateTime object converted to the UTC timezone. It is possible to modfy for the complete query the Date-Time-Format without changing the default Date-Time-Format of your Database. If you call setDateTimeFormat, this will change the format for the whole life-cycle (think about prepared queries!)

    setDateTimeFormat($format)

Will use your timezone settings

    bindDate($key, DateTime $date)

Will convert the date in UTC (does not modify your object!)

    bindDateUTC($key, DateTime $date)

Execute method

This method is for UPDATE, INSERST, etc. which will not fetch value from the database. This method will establish a connection. After calling execute you can´t register any new "actionAfterConnect". The query is also be prepared now. This is important for Array-Binding. See "BindArray method" for more informationen about that topic.

    execute()

Prepare method

With the prepare method, you can prepare your sql queries for later usage. The syntax may looks a bit weired at first, so.

Say we want a query which selects users which ids in the array(1,2,3,4) and a register date later then 2013-01-01. We need this query more then one time and want to prepare it.

    $userQuery = $database->query("SELECT * FROM `users` 
        WHERE id IN (:IDS) and `registered` > :STARTDATE")
        ->prepare(array(
            array('IDS', 4), // Important! bindArray with key IDS and 4 entries
            'STARTDATE' // The key STARTDATE is now known
        ));
// now we can run the prepared query like
$userQuery->bindArray('IDS', array(1,2,3,4)
    ->bindDateUTC('STARTDATE', new Date('2013-01-01'))
    ->fetchAll();

Puh... the method:

    prepare(array $bindings)

Fetch methods

How you will get your query results.

This will return all the result in assoc

    fetchAll()

This will just return the first value of the first row. If there is no result, it will return the $nullValue (default '')

    fetchValue($nullValue = '')

Thi will return the $column as an array

    fetchColumn($column = 0)

This will return a row in assoc

    fetchRow()

Transactions

todo (version 0.8)

Stored Procedures

todo (version 0.9)

Examples

Assumed the following table (users)

id username passwort registered
1 bill god 2013-01-02 15:08:23
2 jack love 2012-06-12 11:36:42
3 jones secret 2013-03-07 18:44:51
4 seb leet 2013-05-01 13:22:01
5 peter foo 2012-04-02 01:21:03
6 hans nerd 2012-10-12 07:38:33

SQL (saved as example.sql)

CREATE TABLE [users] (
    [id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
    [username] varCHAR(50)  UNIQUE NULL,
    [password] varCHAR(32)  NULL,
    [registered] DATETIME  NULL
);

INSERT INTO users (id, username, password, registered) VALUES (1, 'bill', 'god', '2013-01-02 15:08:23');
INSERT INTO users (id, username, password, registered) VALUES (2, 'jack', 'love', '2012-06-12 11:36:42');
INSERT INTO users (id, username, password, registered) VALUES (3, 'jones', 'secret', '2013-03-07 18:44:51');
INSERT INTO users (id, username, password, registered) VALUES (4, 'seb', 'leet', '2013-05-01 13:22:01');
INSERT INTO users (id, username, password, registered) VALUES (5, 'peter', 'foo', '2012-04-02 01:21:03');
INSERT INTO users (id, username, password, registered) VALUES (6, 'hans', 'nerd', '2012-10-12 07:38:33');

Initialize Code

use \Sindri\Database\Config;
use \Sindri\Database\Database;

$config = new Config();
$config->setDsn('sqlite::memory:');
$db = new Database($config);
$db->run(file_get_contents(__DIR__ . '/example.sql'));

Simple Query

$result = $db->query('SELECT * FROM `users` WHERE id = :ID')
    ->bindInt('ID', 2)
    ->fetchRow();
var_dump($result);

// Result

array(4) {
  'id' =>
  string(1) "2"
  'username' =>
  string(4) "jack"
  'password' =>
  string(4) "love"
  'registered' =>
  string(19) "2012-06-12 11:36:42"
}

More will come =)