CRUD and related operations in the CleverStyle Framework

After the article describing the basic interfaces for working with the database, there were enough comments suggesting more high-level tools for working. The CleverStyle Framework has similar sorts of tools in the form of cs\CRUD and cs\CRUD_helpers . Together, they allow for fairly typical situations to replace a large sheet of pattern code with one function call. About what it is, and what set of tasks allows to solve and this article will be.

cs \ CRUD - basics

This treit has 4 main methods to use: create() , read() , update() and delete() . What to do with them is clear without comments, the call format is as follows:

 ->create($arguments : mixed[]) ->create(...$arguments : mixed[]) ->read($id : int|int[]|string|string[]) ->update($arguments : mixed[]) ->update(...$arguments : mixed[]) ->delete($id : int|int[]|string|string[]) 

You can read and delete both single elements and arrays of elements. When creating and updating elements, you can use a series of arguments or a single argument in the form of an array (both indexed and associative with an arbitrary order of keys are possible). Another caveat when creating elements: if the number of arguments (array keys) corresponds to the number of elements in the table model, then the identifier is set explicitly, if it is 1 less then the identifier will be automatically generated by the database tools.

In order for all this to communicate with the database, you need to define an abstract cdb() method that returns the database identifier, as well as the $table properties with the $table name and $data_model with a description of the table structure (and associated tables, if any).

Optionally, $data_model_ml_group and $data_model_files_tag_prefix properties can be defined to support multilingual and download files, respectively.

$ table

The name of the main table (also used as a prefix for related tables), for example:

  protected $table = '[prefix]shop_items'; 

$ data_model

The easiest way to describe an example:

  protected $data_model = [ 'id' => 'int', 'date' => 'int', 'category' => 'int', 'price' => 'float', 'in_stock' => 'int', 'listed' => 'int:0..1', 'attributes' => [ 'data_model' => [ 'id' => 'int', 'attribute' => 'int', 'numeric_value' => 'float', 'string_value' => 'text', 'text_value' => 'html' ] ], 'images' => [ 'data_model' => [ 'id' => 'int', 'image' => 'text' ] ], 'videos' => [ 'data_model' => [ 'id' => 'int', 'video' => 'text', 'poster' => 'text', 'type' => 'text' ] ], 'tags' => [ 'data_model' => [ 'id' => 'int', 'tag' => 'html' ], 'language_field' => 'lang' ] ]; 

Among the supported types are numbers (reduced to numbers when reading from MySQL / PostgreSQL) with a limited range, strings with automatic clearing of XSS and cropping when exceeding a given length, JSON (serialized during recording, deserialized during reading), as well as other buns, in more detail about types of the supported fields it is possible to read in documentation .

When an array is specified instead of a type, then we are dealing with related tables.

Related Tables

These are tables that contain one-to-one or one-to-many auxiliary data. In the example above, we have the main table [prefix]shop_items , and the associated attributes in the database are represented as [prefix]shop_items_attributes .

The data_model table is described in the nested key data_model , there may also be an option language_field key, which indicates that the associated data depends on the language (the field itself is not indicated in the model), respectively, reading / updating data should be done taking into account the current language.

An example of an insert in this configuration:

 $id = $this->create( date(), $category, $price, $in_stock, $listed, [ [$attr1_id, 1, '', ''], //  id  ,         [$attr2_id, 2, '', ''] // ( ,      ) ], [ 'http://example.com/pic1.jpg', //   id  ,        'http://example.com/pic2.jpg' ], [], //    ,   [ 'tag1', //   ,        `lang` 'tag2', // (   ) 'tag3' ] ); 

One can only imagine how many queries should be made to the database in order to crank up such functionality.
When reading, the data will be converted back into the same format, that is, you can do $this->update($changes + $this->read($id)) .

Multilingual content in the CleverStyle Framework

Before describing the device of multilingual content in more detail in cs\CRUD it is necessary to describe how it is usually solved in the framework in general.

To ensure multilingual content, the system class cs \ Text is usually used. It, as well as system of permissions, operates with groups and tags. For example, in the administration interface, things that may depend on the language (the name of the site, the signature in the letters sent, and the like) are implemented in a similar way:

 $result = \cs\Text::instance()->set( \cs\Config::instance()->module('System')->db('texts'), 'System/Config/core', 'name', 'New site name' ); 

In $result will be a string of the form {¶$id} . This string can then be passed to cs\Text::process() to get back the text in the current language (or on the one on which there is a translation in general).

 $site_name = \cs\Text::instance()->process( \cs\Config::instance()->module('System')->db('texts'), $result ); 

Under the hood, 2 tables are used in the database, the index of which is passed in the first parameter. The first [prefix]texts associates group + label with a unique identifier, and [prefix]texts_data contains, actually, translations for each language.

$ data_model_ml_group

Now, when we have an idea of ​​how the general multi-lingual content can be used in the CleverStyle Framework, it becomes clear why $data_model_ml_group . This is nothing but the second argument in the cs\Text::set() call. But how to indicate what should be multilingual and what should not? The prefix ml: used for this ml: in the data type.

If in the example above the category depended on the language, then we would change the corresponding line to:

  'category' => 'ml:int', 

As a result, when recording under the hood, a call will be made:

 $category = \cs\Text::set( $this->cdb(), //    $this->data_model_ml_group, 'category', $category ); 

And when reading:

 $category = \cs\Text::process( $this->cdb(), $category ); 

For related tables, a separate field is used in the related table (specified in language_field ), where this mechanism is not used.

Processing downloads in the CleverStyle Framework

Before describing in more detail how uploaded files are processed in cs\CRUD it is necessary to describe how it is usually solved in the framework in general.

The framework out of the box does not have the functionality to upload files (such functionality is provided by third-party modules, one such is in the repository), but defines the interface for this functionality. Each downloaded file is registered in the database and can be associated with certain tags. That is, the file is loaded on the frontend and in response the absolute path along which the downloaded file is available arrives. But if the file is not signed by at least one tag, it will be deleted after some time.

To sign the file, you need to generate the System/upload_files/add_tag , the corresponding module will react to the event and add a tag for the file. An example of how this is used for user avatars (the option is available on the frontend only if a module is installed that provides this functionality):

 \cs\Event::instance()->fire( 'System/upload_files/add_tag', [ 'url' => $new_avatar, 'tag' => "users/$user/avatar" ] ); 

If the file is no longer used, the tag must be deleted, and the file will also be deleted:

 \cs\Event::instance()->fire( 'System/upload_files/del_tag', [ 'url' => $old_avatar, 'tag' => "users/$user/avatar" ] ); 

$ data_model_files_tag_prefix

Now, when we have an idea how the downloaded files are processed in a general way, it becomes clear why $data_model_files_tag_prefix . This is nothing but a common prefix for tags when generating an add / delete event for files.

Each time the data is inserted / changed, cs\CRUD analyzes all fields and fields of all related tables for links, compares which links already exist, which ones do not yet exist, and generates calls similar to the following under the hood:

 $clang = \cs\Language:instance()->clang; //   , : en, ru, uk $tag = "$this->data_model_files_tag_prefix/$id/$clang"; \cs\Event::instance()->fire( 'System/upload_files/del_tag', [ 'tag' => $tag, 'url' => $unused_file ] ); \cs\Event::instance()->fire( 'System/upload_files/add_tag', [ 'tag' => $tag, 'url' => $new_file ] ); 

Methods for searching for links and signing with their tags can be reused separately, they are also available in cs\CRUD and have the following call format:

 ->find_urls($array : array) : string[] //     ,        ->update_files_tags($tag : string, $old_files : string[], $new_files : string[]) 

Summary of cs \ CRUD

cs\CRUD can take a huge layer of routine from the developer even in multilingual configurations, providing incredibly simple interfaces for many typical situations. However, sometimes only part of the functionality can be used directly, but even in this case, there will undoubtedly be a benefit.

cs \ CRUD_helpers

This is an auxiliary trait that uses cs\CRUD under the hood and has the same requirements.
The main thing that this method provides is the search() method (which, however, is more like a filter) with the following call format:

 ->search($search_parameters = [] : mixed[], $page = 1 : int, $count = 100 : int, $order_by = 'id' : string, $asc = false : bool) : false|int|int[]|string[] 

This method allows you to search for exact matches, matches from several alternative values, as well as ranges from ... to for numbers. In this case, both the fields of the main table and the fields of the related tables can participate in the filter. In addition, with multilingual configuration, the search function will take this into account and will make the corresponding JOIN with the table [prefix]texts_data .

There is also the possibility of paginal output of results, the ability to sort by columns (including from related tables) and to obtain the number of results by one number search.

It looks like this:

 $this->search([ 'argument' => 'Value', //   'argument2' => ['Value1', 'Value2'], //       'argument2' => [ //   ,     'from' => 2, //   ,  'to' => 5 //   ,  ], 'joined_table' => [ //    ,    'argument' => 'Yes' ] ]); 

More examples and a description of the format of calls in the documentation , as well as more detailed examples, including some extreme cases, can be seen in tests ( one , two ) (they very well cover both traits and all the work with the database).

Also, cs\CRUD_helpers contains a number of more atomic methods for building a query and performing a search, which can be used if you are missing what cs\CRUD_heplers , but you would like to reuse existing parts of the search engine. But these methods are not officially documented yet, so use them with some caution.

Eventually

If your task fits into the possibilities of cs\CRUD and cs\CRUD_helpers , then consider yourself very lucky.
You will not have to write a single line of SQL, no problems with multilingualism and accounting for downloaded files, the search will be just as elementary. If this is not enough, then at least you can explore how it works, or even reuse some of the ready-made methods.

» GitHub repository
» Framework documentation
» Relevant tests as additional examples of all possible use cases