Working with DB in CleverStyle Framework

At the expense of the database, at first glance it may seem that the functionality out of the box is very poor. This is partly true, but it is compensated by the fact that the arsenal is very well thought out, solves the assigned tasks and is focused on performance.

And if you need more functional tools - you can always set them up to your taste, it is much easier than to cut out a complicated sluggish monster.

This article will show the main interfaces, and the traits cs\CRUD and cs\CRUD_helpers will remain for another time.

Without ORM and Query builder

ORM does not fit into the ideology of the framework (the doctrine/orm , for example, without any dependencies, even without taking into account the doctrine/* package is one and a half times the most of the framework).

Query builder is also too far away, for example, I never understand why people write this (Laravel 5.2):

 DB::table('users')->where('name', 'John')->first() 

Instead of this:

 SELECT * FROM `users` WHERE `name` = 'John' LIMIT 1 

Or here's another (Yii2):

 new \yii\db\Query()) ->select(['id', 'email']) ->from('user') ->where(['last_name' => 'Smith']) ->limit(10) 

Again instead of:

 SELECT `id`, `email` FROM `user` WHERE `last_name` = 'Smith' LIMIT 10 

Readability (IMHO) is worse, there is no syntax highlighting, syntax checking, static analysis and autocompletion of commands and fields (with configured IDE), and if queries become more complex, it will still be easier to write pure SQL than to understand the intricacies of Query builder.

So, with the approach it became clear - we will write SQL.

SQL is different

The framework at the time of this writing (version 5.32.x) supports 3 database engines: MySQL, SQLite, PostgreSQL.
The problem here is that the syntax supported by these DBMS does not overlap by 100% even in some fairly frequently used things.

The framework here helps by transparently converting part of the MySQL dialect in such a way that it is suitable for SQLite and PostgreSQL.

Further examples for the most part duplicate the documentation .

Sqlite

There is only one very small incompatibility:

 --  INSERT IGNORE INTO `table_name` ( `text` ) VALUES ( ? ) --  INSERT OR IGNORE INTO `table_name` ( `text` ) VALUES ( ? ) 

PostgreSQL

Here everything is more complicated, but nevertheless, still quite simple transformations.

First, these are quotes:

 --  SELECT `id` FROM `table_name` --  SELECT "id" FROM "table_name" 

Then again INSERT IGNORE INTO , for PostgreSQL, it turns into INSERT INTO ... ON CONFLICT DO NOTHING (and therefore the framework requires PostgreSQL 9.5+ to work):

 --  INSERT IGNORE INTO "table_name" ( "text" ) VALUES ( ? ) --  INSERT INTO "table_name" ( "text" ) VALUES ( ? ) ON CONFLICT DO NOTHING 

Another similar command is the REPLACE INTO , which corresponds to a significantly longer INSERT INTO ... ON CONFLICT ON CONSTRAINT "{table_name}_primary" DO UPDATE SET ... :

 --  REPLACE INTO "table_name" ( "id", "item", "value" ) VALUES ( ?, ?, ? ) --  INSERT INTO "table_name" ( "id", "item", "value" ) VALUES ( ?, ?, ? ) ON CONFLICT ON CONSTRAINT "table_name_primary" DO UPDATE SET "id" = EXCLUDED."id", "item" = EXCLUDED."item", "value" = EXCLUDED."value" 

It is important to note that in this case the framework expects that there is a constraint for the table (I don’t know how to translate better) with the name of the table and the _primary suffix, for example, for the [prefix]users system table, it looks like this:

 ALTER TABLE ONLY "[prefix]users" ADD CONSTRAINT "[prefix]users_primary" PRIMARY KEY ("id"); 

The final caveat is in the format in which PostgreSQL wants to get server-prepared expressions, so you can always use it ? :

 --  SELECT "id" FROM "table_name" WHERE `number` > ? AND `age` < ? LIMIT ? --  SELECT "id" FROM "table_name" WHERE `number` > $1 AND `age` < $2 LIMIT $3 

A little bit about the database in general

The framework initially has the notion that there can be several databases. Each database can use different engines, or the same engines, but with different configurations. Database mirrors and simple query distribution in master-master and master-slave configurations are also supported.

The modules that use the database indicate in their meta.json 2 keys that refer to the database (an example from the system module):

 { ... "db" : [ "keys", "texts", "users" ], "db_support" : [ "MySQLi", "PostgreSQL", "SQLite" ], ...} 

In db_support indicated with which engines the module can work in principle, the db indicates the names of the databases that will be associated with any of the existing databases during installation.

Different names are used in order to be able to choose the most optimal database for a task. Of course, the tables should be distributed in such a way as not to do a JOIN between different databases.

Later, when you need to get the id of the database associated with the name, you can thus:

 $db_id = \cs\Config::instance()->module('System')->db('users'); 

Further, the identifier is used to obtain an object with a connection to the desired database:

 $write_connection = \cs\DB::instance()->db_prime($db_id); $read_connection = \cs\DB::instance()->db($db_id); 

Already here the developer clearly indicates whether he will write something in the database or not. The choice of a mirror with a corresponding configuration depends on it.

DBAL

Everything is simple here, as soon as you catch the principle - you can write requests with closed eyes very productively.

Simple request execution

Simple query execution:

 $result = $read_connection->q('SELECT `id` FROM `table_name`'); 

q is short for query . The method has several syntax options:

 ->q($query_string : string) ->q($query_string : string, ...$parameters : array) ->q($query_string : string, $parameters : array) ->q($query_string : string[]) ->q($query_string : string[], ...$parameters : array) ->q($query_string : string[], $parameters : array) 

The queries themselves can be used as server prepared expressions:

 $write_connection->q( [ 'DELETE FROM `items` WHERE `id` = ?' 'DELETE FROM `items_tags` WHERE `item` = ?' ], $item_id ); 

Similarly, client formatting in the form of the syntax of the sprintf() function:

 $write_connection->q( [ 'DELETE FROM `items` WHERE `id` = %d' "DELETE FROM `items_tags` WHERE `item` = '%s'" ], $item_id ); 

In the last example, before substitution, the data will be processed accordingly, so there will be no SQL injections in '%s' .

For server-prepared expressions, not all arguments are allowed (as opposed to the direct use of native interfaces):

 $write_connection->q( [ "DELETE FROM FROM `[prefix]articles` WHERE `id` = ?", "DELETE FROM FROM `[prefix]articles_comments` WHERE `article` = ? OR `date` < ?", "DELETE FROM FROM `[prefix]articles_tags` WHERE `article` = ?" ], [ $article_to_delete, time() - 24 * 3600 ] ); 

Data retrieval

The second useful method is intended for direct data acquisition:

 $read_connection->f($result); 

f is short for fetch . The method also has several optional parameters:

 ->f($query_result, $single_column = false : bool, $array = false : bool, $indexed = false : bool) 

$single_column === true instead of an array with columns, returns the scalar value of the first column:

 $read_connection->f( $read_connection->q('SELECT `id` FROM `table_name` WHERE `id` = 1') ); // ['id' => 1] $read_connection->f( $read_connection->q('SELECT `id` FROM `table_name` WHERE `id` = 1'), true ); // 1 

$array === true instead of a single line, it considers everything and returns the result as an array:

 $read_connection->f( $read_connection->q('SELECT `id` FROM `table_name` WHERE `id` < 3'), false, true ); // [['id' => 1], ['id' => 2]] $read_connection->f( $read_connection->q('SELECT `id` FROM `table_name` WHERE `id` = 1'), true, true ); // [1, 2] 

$indexed === true returns an indexed array instead of an associative array:

 $read_connection->f( $read_connection->q('SELECT `id` FROM `table_name` WHERE `id` < 3'), false, false, true ); // [1] $read_connection->f( $read_connection->q('SELECT `id` FROM `table_name` WHERE `id` = 1'), false, true, true ); // [[1], [2]] 

And now interesting cuts:

 ->qf() === ->f(->q(...)) ->qfa() === ->f(->q(...), false, true) ->qfs() === ->f(->q(...), true) ->qfas() === ->f(->q(...), true, true) 

a from array , and s from single .

For example, the following two constructions are equivalent, although the second is much easier to read and accompany:

 $read_connection->f( $read_connenction->q('SELECT `id` FROM `table_name` WHERE `id` = ?', 1), true, true ); // [1, 2] $read_connection->qfas( 'SELECT `id` FROM `table_name` WHERE `id` = ?', 1 ); // [1, 2] 

Insert data

There is also sometimes a useful method for inserting data:

 $write_connection->insert( 'INSERT INTO `table_name` (`id`, `value`) VALUES (?, ?)', [ [1, 12], [2, 13], [3, 14] ] ); 

The syntax is as follows:

 ->insert($query : string, $parameters : array|array[], $join = true : bool) 

If $join === true , then the example above will be rewritten as:

 $write_connection->q( 'INSERT INTO `table_name` (`id`, `value`) VALUES (?, ?), (?, ?), (?, ?)', [ 1, 12, 2, 13, 3, 14 ] ); 

Otherwise, the lines will be inserted one by one.

Other methods

There are a number of useful methods, for example, ->id() returns the identifier of the last inserted row, ->transaction() allows wrapping operations into a transaction:

 $write_connection->transaction(function ($c) { // `$c`   ,   `$write_connection` $c->insert(...); //      ,       $c->transaction(function ($c) { $c->id(); }); //      `false`     ,     }); 

There are methods for getting a list of tables and columns in a table that work the same for all supported databases and some other auxiliary things.

In general, see the documentation for details.

On this introduction to the basic work with the database everything

In many modules, instead of direct requests, convenient treits of cs\CRUD and cs\CRUD_helpers .
The first one, in addition to directly 4 banal database operations under the hood, still knows how to deal with multilingualism, normalization and some data processing (for example, JSON convert and write back and forth when writing and reading), processing downloaded files, and also supports associated tables (one to one, one to many), also with the support of all the mentioned good.

The second treit has a method for searching (in fact, it is a filter) of elements, again considering the multi-lingual nature of some fields / tables and also includes support for related tables.

If you add a description of both traits to the article, then it will be too big for one time, so it will be next time.

Thoughts on the convenience of interfaces and examples of more convenient (in your opinion) alternatives are welcome, I will be happy to discuss these points in a constructive manner and take feedback into account in future releases.

» GitHub repository
» Framework documentation