Database abstraction layer

Qbix provides excellent facilities for using databases. There are several reasons to use them in your app, including:

• Automatically sanitizing database queries to prevent SQL injection attacks. Without this, you might expose your site to serious security risks.
• It fires Qbix's events, allowing you to attach a hook wherever you may need it later, such as for logging database queries or to implement sharding.
• Writes correct SQL code for you, and uses the the PHP compiler to ensure correct syntax, even with arbitrary expressions.

Qbix also provides an object-relational mapping layer. Using this layer will let you use many kinds of databases for persistence, and your social apps can be designed to scale horizontally and handle millions of users out of the box.

For now, only MySQL is supported. If you'd like to contribute to the project, you are more than welcome to write an adapter for your favorite DBMS, such as PostgreSQL, SQLite or Riak!

Connections

Your app can have one or more database connections, which you would normally set up in the "Db"/"connections" config field. Note that this information might be different for each development machine, and therefore connections should be specified APP_DIR/local/app.json config file. Here is an example:

{
  "Db": {
    "connections": {
      "*": { /* common fields */
        "dsn": "mysql:host=localhost;dbname=YM",
        "username": "YouMixer",
        "password": "somePassword",
        "driver_options": {
          "3": 2
        }
      },
      "YouMixer": {
        "prefix": "ym_",
      },
      "Users": {
        "prefix": "users_",
      },
      "Streams": {
        "prefix": "streams_",
      },
    }
  }
}

Behind the scenes, Qbix's database abstraction layer uses PDO, and all the connection information is used to establish a PDO connection (exception the prefix, which is used to prefix table names). When adding a plugin to the app, you'd typically have to add the database connections it uses (which are, by the way, named in the plugin's config/plugin.json file under "pluginInfo"/pluginName/"connections"), and then run the installer again.

To use a connection, you could write the following:

// returns Db object for the YouMixer connection
$youmixer_db = Db::connect('YouMixer');

but the connection is not made until you actually execute the first query against the database. If you call Db::connect multiple times with the same connection name, it will just return the same object.

When adding a new database schema to an app, you are advised to pick a new connection name (typically named after the app itself) and add it to the "Q"/"appInfo"/"connections" array. Then you'll be able to write scripts for the installer to run when installing or updating the app.

Making queries

Q's DB abstraction layer includes several classes, which are all found in the "Db" package. Among them:

• The Db class represents a database connection. Among other things, it is a wrapper for PDO objects.
• The Db_Query class represents a database query (customized to the particular DBMS that the connection was made to), that you are in the process of building and then executing.
• The Db_Result class represents a result set from executing a query. Among other things, it is a wrapper for PDOStatement objects.
• The Db_Row class represents a row in the database. It is used by the ORM, and is discussed in the article about Classes and Models.

When you get a Db object, you can call methods on it, such as select, insert, update and delete. They return an object of type Db_Query. That class, in turn, has its own methods, most of which also return a Db_Query object. Here are some examples demonstrating a bunch of functionality at once:

$db = Db::connect('YouMixer');

$q = $db->select('*', 'mix')
   ->where(array(
      'name' => 'My Mix',
        // 'My Mix' will be sanitized, 
        // and "=" will be prepended

      'by_user_id <' => 100,
        // Here, an extra "=" is not prepended

      'title LIKE ' => '%somethin%'
        // Here it's also not prepended
        // because of the space after "LIKE".
   ))->orderBy('songCount', false)
   ->limit(5, 1);
   // you can chain these as much as you need

// add more things to the query at any time
$q2 = $q->orWhere('id < 3');

$q3 = $db->update('mix')
   ->set(array(
     'a' => 'b', 
     'c' => 'd'
   ))->where(array(
     'a' => 5
   ));

$q4 = $db->delete('mix')
   ->where(array('id' => $mix_id));
     // $mix_id will be sanitized

$q5 = $db->insert('mix', 
  compact('name', 'by_user_id')
);

$q6 = $db->rawQuery(
  "SELECT name, by_user_id FROM mix"
);

Executing queries

There are a couple ways you can execute a query and fetch the results. One way is to get a Db_Result object, and then fetch:

$r = $q->execute();
$r->fetchAll(PDO::FETCH_ASSOC);

// or all in one line:
Db::connect('YouMixer')
   ->select('*', 'mix')
   ->where('id > 5') // you can pass strings here
   ->execute()->fetchAll();

A second way involves calling "fetch"-type methods directly on a query:

// just fetch an array
$q->fetchAll(PDO::FETCH_ASSOC);

// or all in one line:
Db::connect('YouMixer')
   ->select('*', 'mix')
   ->where('id > 5')
   ->fetchAll(PDO::FETCH_BOTH);

The second way implicitly executes the query (and obtains a Db_Result) before fetching. Besides being shorter, the second way makes use of caching based on the query's SQL content. That means, if you use it twice in the same PHP script, it will only hit the database once, having cached the results.

An actual (PDO) connection is made to the database only when the first query is executed against that connection. You can also hook the "Db/query/execute" event for your own needs. For example, Qbix does this in order to implement sharding in the application layer.

Lists of values

You can specify lists of values as an array in your where() clauses, as follows:

Db::connect('Users')
   ->select('*', 'users_vote')
   ->where(array(
     'userId' => 'tlnoybda',
     'forType' => array('type1', 'type2', 'type3')
   ));

The SQL that is generated looks like this:

SELECT * FROM users_vote
WHERE userId = 'tlnoybda'
AND forType IN ('type1', 'type2', 'type3)

Vector value lists

Sometimes you want to test several values at once, as a vector. Qbix supports this as well, although in this case, this causes database sharding to map such a query to run on every shard.

Db::connect('Users')
   ->select('*', 'streams_stream')
   ->where(array(
     'publisherId, name' => array(
       array('tlnoybda', 'firstName'),
       array('tlnoybda', 'lastName'),
       array('uqoeicuz', 'firstName'),
       array('uqoeicuz', 'lastName')
     ),
     'something' => 'else'
   ));

The SQL that is generated looks like this:

SELECT * FROM streams_stream
WHERE (publisherId, streamName) IN (
  ('tlnoybda', 'firstName'),
  ('tlnoybda', 'lastName'),
  ('uqoeicuz', 'firstName'),
  ('uqoeicuz', 'lastName')
) AND something = 'else'

Database ranges

Instead of exact values, you can specify ranges in your where() clauses, as follows:

$range = new Db_Range(
  $min, $includeMin, $includeMax, $max
);
Db::connect('Users')
   ->select('*', 'vote')
   ->where(array(
     'forType' => 'article',
     'weightTotal' => $range
   ));

This will produce the appropriate inequalities when composing the database query. It also works with database sharding.

Database expressions

By default, Qbix's database library sanitizes values that you pass when building queries. For example, if you wrote:

$results = $db->select('*', 'mix')
   ->where('time_created >' => 
     "CURRENT_TIMESTAMP - INTERVAL 1 DAY"
   )->fetchAll(PDO::FETCH_ASSOC);

then Qbix would treat the value as a string, and sanitize it as such. If what you really want is to insert an arbitrary database expression instead of a value, you would use the Db_Expression class:

$results = $db->select('*', 'mix')
   ->where('time_created >' => new Db_Expression(
     "CURRENT_TIMESTAMP - INTERVAL 1 DAY"
   ))->fetchAll(PDO::FETCH_ASSOC);

In general, you can nest database expressions as much as you want, and Qbix will write the SQL for you. When doing this, valid PHP code will automatically produce evenly matched parentheses, so you don't have to worry about it. Here are some examples:

// Subquery:

$results = $db->select('*', 'mix')
   ->where(array('id IN ' => 
     $db->select('id', 'mix')
        ->where('name' => 'foo')
   ))->fetchAll(PDO::FETCH_ASSOC);

// Multiple clauses and lots of crazy stuff:

$criteria = new Db_Expression(
  "id > 4 AND ", 
  array('name' => "some name"), " AND ",
  new Db_Expression(
    "name IS NULL OR", 
    array('name' => "blah")
  )
);
$results = $db->select('*', 'mix')
   ->where($criteria)
   ->fetchAll(PDO::FETCH_ASSOC);

You can use Db_Expression anywhere that you would provide a value. In fact, Db_Query objects are Db_Expressions themselves, and can be used in place of one.

Other functionality

The Qbix database library also has a few other functions you might be interested in, such as $db->insertManyAndExecute, $db->rank, $db->fromDateTime, $db->toDateTime, and $db->scriptToQueries. It also has $db_query->fetchDbRows(), which we will explore in the Classes and Models article.