Qbix Platform lets developers generate models from any database table, resulting in PHP and Node.js classes and methods that automatically produce SQL, bind parameters, match parentheses, and more. Each table gets its own class, grouped in a folder with other tables from that same module / namespace.
For example, a simple call to select() on any table’s class results in a well-formed SQL query that you can then execute:
php > Streams_Stream::select()->getSQL('echo');
SELECT publisherId,name,insertedTime,updatedTime,type,title,icon,content,attributes,readLevel,writeLevel,adminLevel,permissions,inheritAccess,messageCount,invitedCount,participatingCount,leftCount,closedTime
FROM TokenSociety3.streams_stream
It’s easy to add criteria to filter the results by, adding arrays, ranges, and even multidimensional vectors, by writing some simple code in PHP and having it produce the SQL. Here is a combined example you can study:
php > Streams_Stream::select()->where([
'publisherId' => 'a',
'streamName' => ['a', 'b', 'c'],
'title' => new Db_Range('foo', true, false, true),
'publisherId,name' => [['a', 'b'], ['c', 'd']]
])->getSQL('echo');
SELECT publisherId,name,insertedTime,updatedTime,type,title,icon,content,attributes,readLevel,writeLevel,adminLevel,permissions,inheritAccess,messageCount,invitedCount,participatingCount,leftCount,closedTime
FROM TokenSociety3.streams_stream
WHERE `publisherId` = 'a'
AND `streamName` IN ('a','b','c')
AND `title` >= 'foo' AND `title` < 'fop'
AND (`publisherId`,`name`) IN (
('a','b'),
('c','d')
By expressing the structure and variables in PHP, the queries can also be routed to the correct shards, allowing the database to become distributed across many machines, even across the Internet. The primary keys used in the queries can help determine the shards.
The database engine is designed to support multiple adapters (for MySQL, Postgres, SQLite etc.) while exposing the same API to the PHP application.
Models
The models also support an Object-Relational-Mapping, or ORM. Each model extends the Db_Row class, which has methods such as retrieve() and save() to retrieve rows, modify them, and save them back to the database.
$stream = new Streams_Stream([
'publisherId' => 'a',
'name' => 'b'
])->save(); // insert a new streams_stream row in the database
$stream->title = 'foo';
$stream->save(); // update the row
or for example
if ($stream = new Streams_Stream([
'publisherId' => 'foo',
'name' => 'bar'
])->retrieve()) { // if stream is found
$stream->title .= ' cool'; // append to title
$stream->save(); // update the row
}
Going back to our earlier example, we can select multiple streams:
$streams = Streams_Stream::select()->where([
...
])->fetchDbRows();
foreach ($streams as $stream) {
// Here, we have $stream instanceof Streams_Stream.
$stream->content = 'update';
$stream->save(); // updates the stream
// we can save and also post a message on the stream
// telling everyone watching it what changed:
$stream->changed();
}
The Streams plugin and API extends Db_Row quite significantly, for example. Methods like stream->changed() result in various messages being posted on the stream. This causes participants in the stream – users who called $stream->join() previously – to receive updates in their browsers via websockets, updating their views in real time. Meanwhile, subscribers to the stream – users who called $stream->subscribe() previously – would get offline notifications delivered to them according to their notification preferences (iOS, Android, Web Push, Email, SMS, etc.) See more in Streams → Subscriptions and Notifications.
Indexes
As your database tables grow, it becomes increasingly important that all your queries use indexes, for all operations. In fact, if your database is distributed, the primary key (the main index of your table) is used to identify which shard to execute the query on. That means, by using primary keys in all your queries, the Qbix Platform can easily help your application be distributed to shards across multiple machines and database engines. Primary Keys are also how the models know how and where to save() previously retrieve()d rows.
Transactions
The database engine supports nested transactions. In a sharded environment, the atomicity guarantees of transactions are only local to each shard they are executed on.
Streams_Stream::begin()->where([
'publisherId' => 'a',
'streamName' => 'b'
])->execute();
This query starts a transaction on all the shards based on the criteria passed to where(). The actual SQL doesn’t contain a WHERE clause, it’s just used to know where the transactions should take place. When ready, you can commit the transaction:
Streams_Stream::commit()->where([
'publisherId' => 'a',
'streamName' => 'b'
])->execute();
What’s more, Qbix supports nested transactions, a feature the underlying database often doesn’t natively support. This means different part of your code can call begin() and the transaction would be started only once. A counter is incremented every time transactions are started on the same shard. Calling commit() decrements the counter, while calling rollback() unwinds the nesting counter to zero and rolls back the actual transaction on the database. Throwing an uncaught exception in your scripts likewise results in a rollback(). As usual, Qbix will warn you if you forgot to commit a nested transaction that you began.
Testing and Simulations
As you develop and test new functionality, you want to be careful not to corrupt your database. Qbix apps are designed to be used by smaller communities, rather than one monolithic data center for the whole world. Thus, the database can often be easily cloned and your app’s local config can be updated to use the “test database”. You can do this on your local machine or your shared staging environment, before you ever ship anything to production.
In addition, you can use transactions to do “dry-run” simulations of what your script would do, before executing the queries on your database. Consider adding an event handler early on in your script lifecycle to call $db->begin() and a handler towards the end that calls $db->rollback(). In the meantime, your application can set up a hook to log all queries to a logfile, which you can then use to see what would happen if your script actually ran. This way you can take advantage of the MVCC features of database engines to perfectly simulate what would happen, with multiple reads and writes, in the context of a transaction that is eventually rolled back. You can turn this “testing mode” on and off in your configuration.
Note that, since transactions are local to each shard, nested transaction counters are per-shard also. If you are trying to do atomic transactions across shards, you may end up in a rare situation where some transactions committed but others never did, leaving the shards in an inconsistent state. To recover from this, you’ll need to make your scripts restartable by using Streams/task functionality, leading to eventual consistency. Note that this only works if your PHP and Node.js scripts are the only ones writing to the database. Qbix can’t control software outside of itself, after all.
For More Complete Information:
General Resources: