Working with database

Artaengine provides two ways for storing and retrieving data. Both use a relational DBMS(currently MySQL and PostgreSQL). One method is using a database abstract object to connect and run commands on database, the other way is by using Models. Models are like an ORM layer.

Connecting

The recommended way to connect to a database is by declaring database connection data inside the application configuration . A database object will be available for each defined connection everywhere within the application codes.

An object factory is responsible to make a database connection and create a database object. On an object request, the Database factory will check if the object is already created or not. If yes it will return the object otherwise it will first create the object and make a connection to the database.

It is also possible to manually pass the connection configs to the Database factory to create a database object and connect to the database.

Connect to database and create a new object:

// if the database connection configs for myObject have been put inside the
// application configs
$mydbo = Database::getCreateNew('myObject');

// pass the connection configs in code
$mydbo = Database::getCreateNew(
    'myNewObject',
    array(
        'engine'   => 'postgresql', //'mysql',
        'server'   => 'localhost',
        'dbname'   => 'test',
        'user'     => 'test',
        'password' => 'test'
    )
);
// to get the above object afterwards
$mydbo = Database::get('myNewObject');

To disconnect from the database and remove the object:

$dbo = Database::remove('objectname');

The above examples are for creating/getting an instance of IDbAbstract. To have advanced methods you would need an IDbAdvanced instance. The IDbAdvanced extends the IDbAbstract with methods such as creatingTable, alterTable and meta-data getters. You can add advanced = on to the connection configs to get an IDbAdvanced instance. Also you can always upgrade an IDbAbstract to IDbAdvanced:

// create IDbAdvanced, advanced = on or 'advanced' => 'on' must be set in the
// database configs section in the applications configs
$mydbo = Database::getCreateNew('myObject');

// create IDbAdvanced in code
$mydbo = Database::getCreateNew(
    'myNewObject',
    array(
        'engine'   => 'postgresql', //'mysql',
        'server'   => 'localhost',
        'dbname'   => 'test',
        'user'     => 'test',
        'password' => 'test'
        'advanced' => true,
    )
);

// upgrade IDbAbstract to IDbAdvanced and get the object
$advanced = Database::upgrade('abstractObjName');

Get info:

// get the settings used to connect
$connectionSettings = $dbo->connection();
/* get info about class, connection and DBMS */
$info = $dbo->info();
/* get info */
echo $dbo::DBMS;   // 'postgresql'
echo $dbo::ENGINE; // 'PostgreSQL'
echo $dbo::MAX_IDENTIFIER_NAME; // 63

Cleanup:

/* free open reault resources */
$dbo->free();
/* close connection */
$dbo->close();

Query

Method query is an advanced method to run SQL command on the database. This method accepts parameters and makes them SQL safe.

If a database error happens then throws a DatabaseError.

Method query() is flexible and can be used as shown bellow:

$dbo->query("INSERT INTO users (name) VALUES ('Myname');");

$dbo->query('INSERT INTO users (name) VALUES ($1);', 'Myname');

$dbo->query(
    'INSERT INTO users (name, email) VALUES ($name, $email);',
    array(
        'name'  => 'Myname',
        'email' => 'MyEmail',
    )
);

$dbo->query('SELECT * FROM users WHERE id = 1;');

$dbo->query('SELECT * FROM users WHERE id = $1;', 1);

$dbo->query(
    'SELECT * FROM users WHERE group_id = $1 AND id IN ($2)',
    1, array(1, 2, 3)
);

$dbo->query(
    'SELECT * FROM users WHERE id = $1 AND group_id > $1;',
     1
);

$dbo->query(
    'SELECT * FROM users WHERE id = $user AND group_id IN ($group);',
    array('user' => 1, 'group' => array(1, 2, 3, 4))
);

Query page

This method is for querying a page of data. Use this method when dividing data into pages with fixed number of rows.

Return value of this method can be fed to a Paging object to create raw(array) or rendered(HTML) paging links.

The arguments of this method are the same as query except that the last argument is always a config. Last argument is either a config array or a number which is the page number.

/* get the 4th page using default settings */
$params = $dbo->page(
    'SELECT * FROM users WHERE id > $1 or name = $2',
    5,
    'Mehdi',
    4 // last param is settings - page number
);
/* get the 4th page where each page has 50rows */
$pagingSettings = $dbo->page(
    'SELECT * FROM users WHERE id > $id',
    array('id' => 5),
    array(
        'page'     => 4,
        'max_rows' => 50,
    )
);
/* same as above but dont execute SQL just return it */
$pagingSettings = $dbo->page(
    'SELECT * FROM users WHERE id > $id',
    array('id' => 5),
    array(
        'page'     => 4,
        'max_rows' => 30,
        //'count'     => 100,
        //'count_sql' => 'SELECT cnt FROM v_users;',
        'exec'     => false,
    )
);

Configs can be:

  • Number of the page to query.
  • Number of max rows per the page.
  • Specify this if you know the total number of rows. Otherwise a query would be executed to get total row count.
  • Set the query which gives total row count. Otherwise count will be done on the main query.
  • Run or not to run the page query.

This method returns an array with this keys:

  • Page number.
  • Max page rows.
  • Total number of rows.
  • True/False if the method ran the query or not.
  • The SQL which gives this page rows.

Queried data can be fetched the same way as data queried by query as described in next chapter.

The return value of this method can be fed into Paging class to get paging links:

$pagingSettings = $dbo->page('SELECT * FROM users', 4);

// add other settings to $pagingSettings here

$page = new Paging($pagingSettings);
$htmlLinks = $page->render();

Read Paging object.

Fetching data

Fetching data after querying:

/* fetch one row as object */
$dbo->query('SELECT * FROM users;');
if ($row=$dbo->next()) {
    $id   = $row->id;
    $name = $row->name;
}
/* fetch one row - class magic getter */
$dbo->query('SELECT * FROM users;');
$id   = $dbo->id;
$name = $dbo->name;
/* fetch rows as object */
$dbo->query('SELECT * FROM users;');
while ($row=$dbo->next()) {
    $id   = $row->id;
    $name = $row->name;
}
// or
while ($dbo->next()) {
    $id   = $dbo->id;
    $name = $dbo->name;
}
/* fetch rows as array */
$dbo->query('SELECT * FROM users;');
while ($row=$dbo->row()) {
    $id   = $row['id'];
    $name = $row['name'];
}
/* fetch all rows at in an array */
$rows = $dbo->rows();
foreach ($rows as $row => $val) {
    $id   = $row['id'];
    $name = $row['name'];
}

Count

Get row count of last query:

$dbo->query('SELECT * FROM users;');
$rowCount = $dbo->count();

Get row count of a query:

$rowCount = $dbo->count('SELECT id, name FROM users WHERE group_id = $1;', 2);

$rowCount = $dbo->count(
    'SELECT * FROM users WHERE id = $1 or user_id IN ($2)',
    1, array(1, 2, 3)
);

$rowCount = $dbo->count(
    'SELECT * FROM users WHERE id = $user or group_id IN ($group);',
    array('user' => 1, 'group' => array(1, 2, 3, 4))
);

Get affected rows:

$dbo->query("UPDATE users SET active = 'Y';");
$rowCount = $dbo->affected();

Get field count of a query result:

$dbo->query('SELECT * FROM users;');
$fieldCount = $dbo->fieldCount();

SQL Queues and Transactions

It is possible to create queues of SQL commands and run them at once either in a database transaction or not.

// the parameters are the same as for "query()"
$dbo->add('INSERT INTO users (name) VALUES ($1)', 'me');
$dbo->add('INSERT INTO users (name) VALUES ($1)', 'you');
$dbo->add('INSERT INTO users (name) VALUES ($1)', 'her');
// runs queued commands in a database transations
$dbo->commit();
// runs queued commands but NOT IN A database transations
$dbo->commit(false);
// ignores and removes SQLs from queue
$dbo->rollback();

More than one SQL queues can be defined. Each queue must have a name, each queue is accessed by it's name. You can switch between queues to make one active and add queries to it, commit or roll it back:

// add some SQLs to default transaction queue
$dbo->add("INSERT INTO groups (name) VALUES ($1)", 'g1');
$dbo->add('UPDATE users SET group_id = $1', 3);

// defining another transaction and switching to it
$dbo->begin('my-transaction');
$dbo->add('INSERT INTO users (name) VALUES ($1)', 'me');
$dbo->add('INSERT INTO users (name) VALUES ($1)', 'you');

// defining another transaction and switching to it
$dbo->begin('your-transaction');
$dbo->add('INSERT INTO clients (name) VALUES ($1)', 'him');
$dbo->add('INSERT INTO clients (name) VALUES ($1)', 'her');

// switch to 'my-transaction' and add an SQL to it
$dbo->toggle('my-transaction');
$dbo->add('INSERT INTO users (name) VALUES ($1)', 'her');

// to get all the SQLs set to a transaction (queue SQLs)
$sqls = $dbo->que('my-transaction');

// select/activate transaction by name then commit or roll
if ($doIt) {
    $dbo->toggle('default')->commit();
    $dbo->toggle('my-transaction')->commit();
    $dbo->toggle('your-transaction')->commit();
} else {
    $dbo->toggle('default')->rollback();
    $dbo->toggle('my-transaction')->rollback();
    $dbo->toggle('your-transaction')->rollback();
}

The bellow high-level methods can also be used to add commands to the active queue:

Insert

To add an insert SQL to the active transaction:

/* inserts into table user. Values will be made SQL safe */
$dbo->insert(
    'users', // table name
    array(   // will be made SQL safe col => val
        'name'   => 'Arta',
        'gender' => 'M',
        'email'  => 'arta@artaengin.com',
    )
);
/* inserts into table user. Values will be made SQL safe */
$dbo->insert(
    'users', // table name
    array(   // will be made SQL safe col => val
        'name'   => 'Arta',
        'gender' => 'M',
        'email'  => 'arta@artaengin.com',
        // to not escape the value
        '!datetime_create' => 'NOW()',
    )
);
/* inserts into table user. Values will be made SQL safe */
$dbo->insert(
    'users', // table name
    array(   // will be made SQL safe col => val
        'name'   => 'Arta',
        'gender' => 'M',
        'email'  => 'arta@artaengin.com',
    )
    // will not be escaped
    array(
        'datetime_create' => 'NOW()',
    )
);
/* commit transaction */
$dbo->commit();

The third optional argument is for data which are SQL safe and do not need to be escaped or be put betweens apostrophes like SQL functions.

Update

To add an update SQL to the active transaction:

/* update table data */
$dbo->update(
    // table name
    'users',
    // cols to be updated (values will be made SQL safe)
    array(
        'name'   => 'Arta',
        'gender' => 'M',
        'email'  => 'arta@artaengin.com',
    ),
    // condition - id = 2 and name = 'arta'
    array(
       'id'   => 2,
       'name' => 'arta',
    )
);
/* update table data */
$dbo->update(
    'users',
    array('gender' => 'F'),
    // condition - id > 2
    'id > 5'
);
/* update table data */
$dbo->update(
    'users',
    array('gender' => 'M'),
    // condition - SQL string and params
    'id = $1 OR id IN ($2) OR name = $3',
    4,
    array(9, 4, 7),
    'arta'
);
/* update table data */
$dbo->update(
    'users',
    array('gender' => 'M'),
    'id = $id',
    array('id' => 3)
);
/* update table data */
$dbo->update(
    'users',
    array('gender' => 'F'),
    // condition - SQL string and params
    'id = $id OR id IN ($ids) OR name = $name',
    array(
        'id'   => 4,
        'ids'  => array(9, 4, 7),
        'name' => 'arta',
    )
);
/* commit transaction */
$ok = $dbo->commit();

The third an further arguments are to define conditions. The update values in above examples will be made SQL safe. To sign a value not to be changed put the value in an array:

$dbo->update(
    'users',
    array('gender' => 'F'),
    // condition - SQL string and params
    'id = $id OR id IN ($ids) OR name = $name',
    array(
        // will be SQL safe
        'id'   => 4,
        'ids'  => array(9, 4, 7),
        'name' => 'arta',
        // if value is put inside an array it wont be SQL safe
        '!datetime_create' => 'NOW()',
    ),
    // condition - SQL string and params
    'id = $id OR id IN ($ids) OR name = $name',
    array(
        'id'   => 4,
        'ids'  => array(9, 4, 7),
        'name' => 'arta',
    )
);
$ok = $dbo->commit();

Delete

To add a delete SQL to the active transaction:

$dbo->delete(
    // table name
    'users',
    // conditions
    'id = $1 OR id = $2',
    4, 9
);
/* commit transaction */
$ok = $dbo->commit();

The second an further arguments are to define conditions.

Debugging

// to get or print the results as HTML
Arta::$HtmlDebug = true;
// to get or print the results as they are
Arta::$HtmlDebug = false;

// get the last ran SQL and the queued SQLs
$getTheResults = $dbo->inspect(false);
// print the last ran SQL and the queued SQLs
$dbo->inspect();
// return array
$sqls = $dbo->inspect(false);

// also trying to use the object as string will give the debug info
echo ''.$dbo;
// or
echo (string)$dbo;

Other methods

// SQL escape string
$string = $dbo->esc("What's this?");

// COALESCE - returns SQL COALESCE statement
$string = $dbo->isNull('name', 'no-name');
$string = $dbo->isNull('name', array('no-name1', 'no-name2'));

// Get SQL with LIMIT
$string = $dbo->limit($sql, 10, 0);

// CONCAT - returns concat of strings depending on DBMS
$string = $dbo->concat(array('str1', 'str2', 'str3'));

// col names of last query
$array = $dbo->fields();

// conver data to database bool value
$bool = $dbo->toBool(true);

// conver data to database time stamp
$ts = $dbo->toTimestamp('2009/04/27 1:1');

// check if a table exists
if ($dbo->exists('tablename')) {
    echo 'yes';
}
// check if a query has result
if ($dbo->exists('tablename', 'id = 5')) {
    echo 'yes';
}

if ($dbo->exists('tablename', array('id => 5'))) {
    echo 'yes';
}

if ($dbo->exists('tablename', 'id = $1', 5)) {
    echo 'yes';
}

// get value of auto increment(serial) if col name is id
$id = $dbo->serial('tablename', 'id');
// get value of next auto increment(serial) if col name is id
$id = $dbo->serial('tablename', 'id', 'next');

// is a col auto increment(serial)
if ($dbo->isSerial('tablename', 'id')) {
    echo 'yes';
}

Advanced database object

An advanced database object has all the methods of an abstract object plus more. Check the API:

IDbAdvanced

PostgreSQL

MySQL

You can replace an existing Abstract object to an Advanced object:

// by object
$dbo = Database::upgrade($dbo); 
// by object name
$dbo = Database::upgrade('dbo'); 

The Artaengine build uses the IDbAdvanced interface for synchronizing the data models with the database, however the models themselves only require an IDbAbstract instance to interact with the database.

Example

Database
TOP