Atomik Framework

Each instances managed by Atomik_Db must be named.

You can add instances to Atomik_Db using addAvailableInstance(). The first argument is the name and the second the instance object.

					
Atomik_Db::addAvailableInstance('db1', $instance);
				

You can then check if an instance is available using isInstanceAvailable() and retrieve all instances using getAvailableInstances().

Finally, it is possible to create and register an instance using createInstance(). It takes as first argument the instance name and then the same as argument as Atomik_Db_Instance's constructor. There is a fifth parameter, which is true by default, that, if true, will set the new instance as the default one.

					
$instance = Atomik_Db::createInstance('db1', $dsn, $username, $password);
				

All Atomik_Db_Instance methods are available as static methods on the Atomik_Db class. Calling these methods will forward the call to the default instance.

The default instance can be set using Atomik_Db::setInstance(). It takes as argument an instance name or object. It can be retrieve using getInstance().

					
Atomik_Db::setInstance($instance);
$result = Atomik_Db::findAll('table');
				

Thus, all methods covered in the next section about instances can also be used on the Atomik_Db class.

Atomik_Db_Instance supports lazy connections. This means that it will connect to the database only if a query is performed.

Connection information can be defined when creating the object.

					
$instance = new Atomik_Db_Instance($dsn, $username, $password);
				

You can then force the connection by calling Atomik_Db_Instance::connect() without any arguments.

If you created a Atomik_Db_Instance object without any constructor arguments, you can use connect() to connect to the database.

					
$instance->connect('mysql:host=localhost;dbname=mydb', 'root', '');
				

Using Atomik_Db_Query can be thought as similar to writing sql queries. Methods can be chained so that the flow of a query is not disrupted.

					
$query = new Atomik_Db_Query();
$query->select()->from('users')->where('id = ?', 1);
				

select() takes as arguments which fields to select.

from() takes as argument a table name. This method can be called several times to query from multiple tables. An alias can be specified as second argument.

where() can be used in multiple ways. As shown above, it can be an sql string. This string can contain positional parameter which can then be specified as method arguments.

The method can also take as first argument an array where keys are field's name. All values will be automatically escaped. This can be prevented using the expr() method of the query object. It takes as argument the value which won't be escaped.

Multiple call to where() can be performed. They will be concatenated using the AND sql operator. If you want to use OR instead you can use the orWhere() method.

					
$query = new Atomik_Db_Query();
$query->select()->from('users')->where('id = 1');
				

					
$query = new Atomik_Db_Query();
$query->select()->from('users')->where('name = ?', 'peter')->where('password = ?', 'foo');
				

					
$query = new Atomik_Db_Query();
$query->select()->from('users')->where('join_date = ?', $query->expr('NOW()'));
				

					
$query = new Atomik_Db_Query();
$query->select()->from('users')->where(array('id' => 1))->orWhere('id = 2');
				

Atomik_Db_Query also allows you to perform joins using join(), group by using groupBy(). You can also specify an having clause using having() (which works the same as where());

An order by clause can be specified using orderBy() which takes a field name as first argument and optionally the direction (ASC or DESC) as second.

					
$query = new Atomik_Db_Query();
$query->select()->from('users')->orderBy('name');
				

					
$query = new Atomik_Db_Query();
$query->select()->from('users')->orderBy('name ASC');
				

					
$query = new Atomik_Db_Query();
$query->select()->from('users')->orderBy('name', 'DESC');
				

Finally, you can specify a limit clause which takes either a single argument which will be the length or two arguments where the first one will be the offset and the second the length.

					
$query = new Atomik_Db_Query();
$query->select()->from('users')->limit(10);
				

					
$query = new Atomik_Db_Query();
$query->select()->from('users')->limit(10, 10);
				

Atomik_Db_Query can also be used to generate ddl statements (insert, update and delete) using the same approach.

					
$query = new Atomik_Db_Query();
$query->insertInto('users')->values(array('username' => 'peter', 'password' => 'foo'));
				

					
$query = new Atomik_Db_Query();
$query->update('users')->set(array('password' => 'bar'))->where('username = ?', 'peter');
				

					
$query = new Atomik_Db_Query();
$query->delete()->from('users')->where('username = ?', 'peter');
				

You can define a table prefix for all queries using the static method setDefaultTablePrefix().

You can get the generated sql using toSql() or using the object in a string context (echoing for example).

If you're using parameters in your query, they won't be included in the sql string (the question mark, or the key will be kept). All parameters can be retrieved using getParams().

					
$stmt = $pdo->prepare($query->toSql());
$stmt->execute($query->getParams());
				

However, this can be simplified by simply calling the execute() method of the query object with a PDO object as argument.

					
$result = $query->execute($pdo);
				

When using this method, the result is returned as a Atomik_Db_Query_Result object that will cover in the next section.

You can also pass query objects to Atomik_Db_Instance::query() without the need to specify the parameters as second argument.

					
$result = $instance->query($query);
				

You can define a parameter at any time using setParam().

It is also possible to define a pdo object for a query using setPdo() or a default pdo object for all queries using the setDefaultPdo() static method. In this case, it's not needed to pass a pdo object to execute().

Rather than directly returning the PDOStatement object, Atomik_Db_Query::execute() returns an Atomik_Db_Query_Result object. The reason is that PDOStatement can only be iterate over once whereas the result object can be iterate as anytime you want.

For the most part, result objects work the same way as PDOStatement and have the same methods. However, one of the main differences is that the fetch mode can only be set before any rows are fetched. This is to ensure that all cached data are coherent.

As a result of the previous condition, the fetch() method does not have a fetch mode argument.

					
while ($row = $result->fetch()) {
	// do something
}
				

					
foreach ($result as $row) {
 // do something
}
				

					
for ($i = 0; $i < $result->rowCount(); $i++) {
	$row = $result[$i];
}
				

You can check if the data have already been fetched using isCached(). You can also reload the data using reload() (this will re-execute the query and reset the result object).

fetchAll() is also available. There's also an alias method named toArray(). Note that fetchColumn() is not available.

You can still access the PDOStatement using getStatement() (unless the query is cached) and the associated query using getQuery().

We've seen that query result can be queried over many times without re-executing the query. But what about the same query being executed multiple times. Atomik_Db_Query allows you to cache queries so the same query with the same parameters won't be executed a second time.

For a query to be cached, call setCacheable() with true as argument. You can also specify to cache all queries by default using the static method setCacheAllQueries().

You can check if query result are cached using isCached() or empty the cache using emptyCache().