Extension of system (and not only) tables in MODX Revolution

    At the moment, I am engaged in remaking one news portal on MODX Revolution. Since traffic to the site can be up to 100,000 people per day, the issue of productivity is one of the most important. Considering that at the moment there are more than 75,000 articles in the database, with the wrong (and even with the traditional approach to MODX development) site brakes are almost guaranteed, and if the frequency of visits exceeds the query execution time, the server will go down altogether. Here is a part of the techniques involved here to solve these problems, and I will describe in this article.

    1. Long cache generation.


    Surely, many people know that when updating the MODX cache, it goes through all the documents and stuffs the resource map into the context cache. If someone does not know, I wrote in detail about it here . And although in MODX starting from version 2.2.7 (or in the region of that one), you can disable resource map caching in the settings ( cache_alias_map system setting ) this problem is only partially solved - MODX does not cache document URLs, but it doesn’t matter with the ID structure, sorting through all the documents from the database. This leads to the fact that, firstly, the context cache file grows, and secondly, the script may simply fail to execute in 30 seconds and the cache file breaks, which can lead to fatal errors and make the site inoperative.

    But even if the server is still able to pull all documents and fill everything in the cache, let's look at the comparative numbers for one request with different settings. These numbers will be very relative because a lot depends on the server settings and on different servers the memory consumption of the same site will be different, but in comparison these numbers will give an idea of ​​the difference in state. To estimate memory consumption, I will call the getdata processor to receive 10 articles.

    So, option one: Full caching of the resource map is enabled.
    Context cache file size: 5,792,604 bytes.
    Memory consumption on request: 28.25 Mb
    Time: 0.06-0.1 sec.


    Option two: Full caching of the resource map is disabled (system setting cache_alias_map == false).
    Context cache file size: 1,684,342 bytes.
    Memory consumption on request: 15.5 Mb
    Time: 0.03-0.06 seconds.


    Option Three: Completely disabled caching of the resource map with the cacheOptimizer patch .
    Context cache file size: 54,945 bytes.
    Request memory consumption: 4.5 Mb
    Time: 0.02-0.03 sec.


    And that’s just 75,000 resources. In hundreds of thousands, the difference will be much more noticeable.

    There are of course here and cons. For example, Wayfinder will not work, which builds a menu based on alias map data. Here you have to collect the menu itself. Most often I use the menu processor that I wrote about here (see section 2. Replacing Wayfinder ).

    2. Low productivity due to the TV-parameters of documents.


    But this is the main and most interesting reason for writing this topic. Probably there is not a single MODX developer who would not use TVs TV-fields. They solve two problems at once: 1. add custom fields to documents, 2. give different interfaces for editing them depending on the type of field.

    But they have a serious minus - they are all stored in one table. This adds several problems at once:

    1. You cannot control the uniqueness of values ​​at the database level.

    2. You cannot use different data types for different TV fields. All data of TV fields is contained in a single column valuewith the mediumtext data type. That is, we cannot use a larger amount of data, and the numeric values ​​we have will be stored as lowercase (which imposes additional requirements for the formation of the query with sorting), and we do not have to compare the data from the different columns, and we don’t configure secondary keys and many, many more unpleasant things because of this.

    3. Poor performance when fetching from multiple tables. For example, for one document we have several TV fields, of which at least 2-3 fields are almost always filled. We want to receive immediately in the request data and documents and fields to them. We have two main options for generating a request for this:

    1. Just join the table of TV-shek.
    $q = $modx->newQuery("modResource");
    $alias = $q->getAlias();
    $q->leftJoin("modTemplateVarResource", "tv", "tv.contentid = {$alias}.id");
    $c->select(array(
        "tv.*",
        "{$alias}.*",
    ));
    

    But there is a serious minus: in the resulting table we get C * TV the number of records, where C is the number of records in site_content, and TV is the number of records in the site_tmplvar_contentvalues ​​table for each document separately. That is, if, for example, we have 100 records of documents and 3 TV records for each document (on average), then we will get 100 * 3 = 300 records in total.

    Since for this reason, as a result, there was more than one resulting record per document, then at the PHP level you have to additionally process the received data to generate unique data. This is done in our getdata processor as well . And it also increases the load and increases the execution time.

    Here in this news portal I just had on average 3 main entries per document. As a result, ~ 225,000 TV recordings. Even with query optimization, execution with conditions took 1-4 seconds, which is a very long time.

    2. Join each TV field individually.
    Sample request:
    $q = $modx->newQuery("modResource");
    $alias = $q->getAlias();
    $q->leftJoin("modTemplateVarResource", "tv1", "tv1.tmplvarid = 1 AND tv1.contentid = {$alias}.id");
    $q->leftJoin("modTemplateVarResource", "tv2", "tv2.tmplvarid = 2 AND tv2.contentid = {$alias}.id");
    // .........
    $c->select(array(
        "tv1.value as tv1_value",
        "tv2.value as tv2_value",
        "{$alias}.*",
    ));
    

    Such a request will be processed faster, since the resulting table will have as many records as document records, but still the load will not be small when the record count goes to tens and hundreds of thousands, and the number of TV sheks exceeds ten (because each TV the rack is plus one more table joining).

    By far, the best option in this case is to store TV values ​​in the site_content system table itself, that is, each value is stored in a separate column of this table.

    If anyone thinks this is the next lesson on the pitted CRC topic, then this is not entirely true. Traditionally, we were taught to expand the available classes with our own and to add the columns we need there (or even write our own table). But this path is not optimal. The main problem here is that we somehow extend the class, but do not change it ourselves. Extensions concern only the expanding (and not extensible) class, as well as those expanding classes that will extend our class. Confusing, but hard to say. Will explain. We have the base class modResource. The classes modDocument, modWebLink, modSimLink, etc. extend it. They all inherit from the modResource map map. If we extend the modResource class with our class, then in our class there will be new columns that we add, but they will not be in the modDocument class, since it does not extend our class. In order for information about new columns to appear in all extending modResource classes, this information must be in the modResource class itself. But how to do this without touching the system files themselves? .. In fact, I wrote partially about this more than two years ago (articlemoved here ), but only now it is implemented in combat mode. We do this:

    1. Create a new component that will be loaded as extensionPackage (wrote about it in detail here ).

    2. Create new columns in the site_content table via phpMyAdmin or something like that.

    3. Using CMPGenerator, we generate a separate package with the map of the site_content table. In this map there will be a description of your new columns and tables.

    4. We write in your package in the metadata.mysql.php file the data of your columns and indexes (an example of such a file can also be seen in our ShopModxBox assembly).
    For example, my file looks something like this
     array(
            "fields"    => array(
                "article_type"  => array(
                    "defaultValue"  => NULL,
                    "metaData"  => array (
                        'dbtype' => 'tinyint',
                        'precision' => '3',
                        'attributes' => 'unsigned',
                        'phptype' => 'integer',
                        'null' => true,
                        'index' => 'index',
                    ),
                ),
                "image"  => array(
                    "defaultValue"  => NULL,
                    "metaData"  => array (
                      'dbtype' => 'varchar',
                      'precision' => '512',
                      'phptype' => 'string',
                      'null' => false,
                    ),
                ),
            ),
            "indexes"   => array(
                'article_type' => 
                array (
                  'alias' => 'article_type',
                  'primary' => false,
                  'unique' => false,
                  'type' => 'BTREE',
                  'columns' => 
                  array (
                    'article_type' => 
                    array (
                      'length' => '',
                      'collation' => 'A',
                      'null' => true,
                    ),
                  ),
                ),
            ),
        ),
    );
    foreach($custom_fields as $class => $class_data){
        foreach($class_data['fields'] as $field => $data){
            $this->map[$class]['fields'][$field] = $data['defaultValue'];
            $this->map[$class]['fieldMeta'][$field] = $data['metaData'];
        }
        if(!empty($class_data['indexes'])){
            foreach($class_data['indexes'] as $index => $data){
                $this->map[$class]['indexes'][$index] = $data;
            }
        }
    }
    

    Read it carefully. It adds information about two columns and one index to the site_content table.

    Let's make sure the columns have really been added. Let's execute this code in the console:
    $o = $modx->newObject('modDocument');
    print_r($o->toArray());
    


    We will see this result:
    Array
    (
        [id] => 
        [type] => document
        [contentType] => text/html
        [pagetitle] => 
        [longtitle] => 
        // Тут еще куча колонок перечислено
        // и в конце наши две колонки
        [article_type] => 
        [image] => 
    )
    


    Now we can work with the system table with our custom fields. For example, you can write like this:
    $resource = $modx->getObject('modResource', $id);
    $resource->article_type = $article_type;
    $resource->save();
    

    Our value will be written to the table for this document.

    Create your own columns and indexes on pure MODX.


    It is clear that with this approach, we have the problem of migrating from such a custom site to pure MODX, because the tables do not have our custom fields and indexes. But in fact, this is not a problem at all. The fact is that as we generate map from tables, we can create tables, columns and indexes from map descriptions of classes. Creating a column or index is very simple:
    // Получаем менеджер работы с базой данных
    $manager = $modx->getManager();
    // Создаем колонку 
    $manager->addField($className, $fieldName);
    // Создаем индекс
    $manager->addIndex($className, $fieldName);
    

    In this case, it is not necessary to indicate any column and index data except for their names. XPDO will get this data from our map and use it when creating the described column or index.

    If you assemble your component into a normal installation package, then you can directly register a script so that when you install the package your custom columns and indexes are created in the tables immediately.

    Rendering your custom data in TV fields when editing documents.


    As I said above, the convenience of TV-shek lies in the fact that various control elements (text fields, drop-down lists, checkboxes, radio boxes, etc.) are created for them. Plus, in the native form editor, you can differentiate the rights to certain TV fields, so that anyone who is not paid can not see / edit private fields. In fact, you can, if you really want to, but still private fields will not be an eyesore to anyone without a trace.And just these mechanisms I would not want to lose, because otherwise you would have to figure out your own interfaces to manage this data, and this is very labor-intensive. Nevertheless, I would like to use the native resource editor for editing such data. There is no ideal mechanism here, but I worked out a less suitable option. Its meaning is to substitute a TV field with its custom value at the time of rendering the document editing form at the plugin level, and when saving the document, intercept the TV-channel data and save this data in our custom fields. Unfortunately, it’s not possible to wedge in here as it should (simply because the API does not allow it), so we cannot affect the data transmitted to the document processor, which is why TV data will still be written to the TV table, but this is not a problem - just after saving the document, we automatically clean this plate and that's it. Here is an example of a plug-in that triggers three events (1. rendering a document editing form with TV-field substitution and custom data, 2. receiving data and changing a document object before saving it, 3. cleaning up unnecessary data).
    View code
    event->name){
        /*
            Рендеринг ТВшек
        */
        case 'OnResourceTVFormRender':
            $categories = & $scriptProperties['categories'];
            foreach($categories as $c_id => & $category){
                foreach($category['tvs'] as & $tv){
                    /*
                        Рендеринг тэгов
                    */
                    if($tv->id == '1'){
                        if($document = $modx->getObject('modResource', $resource)){
                            $q = $modx->newQuery('modResourceTag');
                            $q->select(array(
                                "GROUP_CONCAT(distinct tag_id) as tags",
                            ));
                            $q->where(array(
                                "resource_id" => $document->id,
                            ));
                            $tags = $modx->getValue($q->prepare());
                            $value = str_replace(",", "||", $tags);
                            $tv->value = $value;
                            $tv->relativeValue = $value;
                            $inputForm = $tv->renderInput($document, array('value'=> $tv->value));
                            $tv->set('formElement',$inputForm);
                        }
                    }
                    /*
                        Рендеринг картинок
                    */
                    else if($tv->id == 2){
                        if($document = $modx->getObject('modResource', $resource)){
                            $tv->value = $document->image;
                            $tv->relativeValue = $document->image;
                            $inputForm = $tv->renderInput($document, array('value'=> $tv->value));
                            $tv->set('formElement',$inputForm);
                        }
                    }
                    /*
                        Рендеринг статусов
                    */
                    else if($tv->id == 12){
                        if($document = $modx->getObject('modResource', $resource)){
                            $tv->value = $document->article_status;
                            $tv->relativeValue = $document->article_status;
                            $inputForm = $tv->renderInput($document, array('value'=> $tv->value));
                            $tv->set('formElement',$inputForm);
                        }
                    }
                }
            }
            break;
        // Перед сохранением документа
        case 'OnBeforeDocFormSave':
            $resource = & $scriptProperties['resource'];
            /*
                Тэги.
                Перед сохранением документа мы получим все старые 
                теги и установим им active = 0.
                Всем актуальным тегам будет установлено active = 1.
                После сохранения документа в событии OnDocFormSave мы удалим все не активные теги
            */ 
            if(isset($resource->tv1)){
                $tags = array();
                foreach((array)$resource->Tags as $tag){
                    $tag->active = 0;
                    $tags[$tag->tag_id] = $tag;
                }
                // $tags = array(); 
                if(!empty($resource->tv1)){
                    foreach((array)$resource->tv1 as $tv_value){
                        if($tv_value){
                            if(!empty($tags[$tv_value])){
                                $tags[$tv_value]->active = 1;
                            }
                            else{
                                $tags[$tv_value] = $modx->newObject('modResourceTag', array(
                                    "tag_id"    => $tv_value,
                                ));
                            }
                        }
                    }
                }
                $resource->Tags = $tags;
                $tags_ids = array();
                foreach($resource->Tags as $tag){
                    if($tag->active){
                        $tags_ids[] = $tag->tag_id;
                    }
                }
                $resource->tags = ($tags_ids ? implode(",", $tags_ids) : NULL);
            }
            /*
                Обрабатываем изображение
            */
            if(isset($resource->tv2)){
                $resource->image = $resource->tv2;
            }
            /*
                Обрабатываем статусы
            */
            if(isset($resource->tv12)){
                $resource->article_status = $resource->tv12;
            }
            break;
        /*
            Сохранение документа
        */
        case 'OnDocFormSave':
            $resource =& $scriptProperties['resource'];
            /*
                Удаляем все не активные теги
            */
            $modx->removeCollection('modResourceTag',array(
                'active' => 0,
                'resource_id' => $resource->id,
            ));
            /*
                Удаляем TV-картинки, так как они сохраняются в системную таблицу
                Удаляем TV-статусы, так как они сохраняются в системную таблицу
            */
            $modx->removeCollection('modTemplateVarResource',array(
                'tmplvarid:in' => array(
                    1,  // Тэги
                    2,  // Картинки
                    12, // Статусы
                ),
                'contentid' => $resource->id,
            ));
            break; 
    }
    


    Thanks to this plugin, custom data is rendered into the document editing form and processed when it is saved.

    Total


    Out of 225+ thousand entries in the table of additional fields, only 78 remain. Of course, not all TVs will appear in the system table (but only those used for searching and sorting), and some data will certainly be in the table of TV fields, but the load nevertheless, it has seriously decreased, and requests have become simpler.

    UPD: A more versatile plugin for rendering and processing your TV to.

    Also popular now: