General information Database

Database

The Database class is used to work with the database and implements the interaction with the database management system (DBMS) through the PDO extension. The database access settings are specified in the system settings with the prefix db..

The initialization and connection to the database are performed during the application initialization stage. Access to the class object is done as follows:

$this->db - in the context of modules, models, plugins, and other classes that inherit the base Component class (priority)

bff::database() - in the global context and classes that do not inherit Component

Connection Settings

The connection parameters are specified in the system settings file /config/sys.php in the section "Database Access".

'db.type' => 'mysql', # database type
'db.host' => 'localhost', # local connection (localhost, 127.0.0.1) or external IP address
'db.port' => '3306', # connection port, default port for MySQL is 3306
'db.name' => 'name', # database name
'db.user' => 'user', # user login
'db.pass' => 'pass', # user password

Building SQL queries

To execute SELECT SQL queries, the following methods of the Database class are available:

select - retrieve multiple rows from a table

$data = $this->db->select('SELECT id, title FROM ' . TABLE_TEST . ' WHERE id > 10');
/* example:
array(2) {
    0 => ['id' => 11, 'title' => 'Title A'],
    1 => ['id' => 12, 'title' => 'Title B']
}
*/

select_key - retrieve multiple rows from a table with grouping by key (column):

$data = $this->db->select_key('SELECT id, title FROM ' . TABLE_TEST . ' WHERE id > 10', 'id');
/* example of grouping data by the id column:
array(2) {
    11 => ['id' => 11, 'title' => 'Title A'],
    12 => ['id' => 12, 'title' => 'Title B']
}
*/

select_one_column - retrieve multiple rows with data from a single column:

$data = $this->db->select_one_column('SELECT id FROM ' . TABLE_TEST . ' WHERE id > 10', 'id');
/* example:
array(2) {
    0 => 11,
    1 => 12
}
*/

one_data - retrieve a single value:

$title = $this->db->one_data('SELECT title FROM ' . TABLE_TEST . ' WHERE id = :id', [':id'=>11]);
$count = $this->db->one_data('SELECT COUNT(id) FROM ' . TABLE_TEST . ' WHERE id >= :num', [':num'=>11]);

one_array - getting a single row:

$data = $this->db->one_array('SELECT id, title FROM ' . TABLE_TEST . ' WHERE id = :id', [':id'=>11]);
/* example:
array(2) {
    id => 11,
    title => Title A
}
*/

Several methods are available for simple SELECT queries as well:

$data = $this->db->select_rows(TABLE_TEST, ['*'], ['id'=>5]);
# final query: SELECT * FROM test WHERE id = 5
# used to retrieve information about multiple records

$count = $this->db->select_rows_count(TABLE_TEST, ['status'=>['>', 5]]);
# final query: SELECT COUNT(*) FROM test WHERE status > 5
# used to count the number of records

$data = $this->db->select_row(TABLE_TEST, ['*'], ['id'=>5]);
# final query: SELECT * FROM test WHERE id = 5
# used to retrieve information about a single record

$data = $this->db->select_data(TABLE_TEST, 'title', ['id'=>5]);
# final query: SELECT title FROM test WHERE id = 5
# used to retrieve information about a specific column of a single record

And others: select_rows_key, select_rows_column, ...

Methods used for other types of queries:

insert - inserting a row into a table, INSERT queries

$id = $this->db->insert(TABLE_TEST, ['title'=>'Title A', 'status'=>1]);
# final query: INSERT INTO test (title, status) VALUES ('Title A', 1)
# the result will be the ID of the newly added row

multiInsert - inserting multiple rows into a table, INSERT multiple rows query

# example of inserting multiple rows
$this->db->multiInsert(TABLE_TEST, [
    ['title'=>'Title A', 'status'=>1],
    ['title'=>'Title B', 'status'=>2],
]);
# final query: INSERT INTO test (title, status) VALUES ('Title A', 1), ('Title B', 2)

update - updating a row in a table, UPDATE queries

$this->db->update(TABLE_TEST, ['title' => 'Title AA'], ['id'=>11]);
# final query: UPDATE test SET title = 'Title AA' WHERE id = 11
# as a result of this query, the row with id=11 will update the value of the title column to 'Title AA'

$this->db->update(TABLE_TEST, ['status' => 2], ['status' => ['<=',3]]);
# final query: UPDATE test SET status = 2 WHERE status <= 3

$this->db->update(TABLE_TEST, ['status' => 2], ['status' => [2,3], 'id' => ['!=', 2]]);
# final query: UPDATE test SET status = 2 WHERE status IN(2,3) AND id != 2

$this->db->update(TABLE_TEST, ['status' => 3, 'num = num + 1'], ['title' => ['LIKE', 'example%']]);
# final query: UPDATE test SET status = 3 AND num = num + 1 WHERE title LIKE 'example%'

delete - deleting a row from a table, DELETE queries

$this->db->delete(TABLE_TEST, ['id' => 11]);
# resulting query: DELETE FROM test WHERE id = 11
# as a result of this query, the row with id=11 will be deleted

exec - a basic method that executes the final query when any of the above-mentioned methods are called. It is also used for non-standard queries, such as a DELETE query using multiple tables.

Each of the methods listed above allows passing data to be substituted for placeholders in the query string.

Additionally, there are methods that simplify the construction of individual parts of the query:

prepareIN - building IN() conditions

$this->db->select('SELECT id, title FROM ' . TABLE_TEST . '
           WHERE '.$this->db->prepareIN('id', [11,12]));
# resulting query: SELECT id, title FROM test WHERE id IN (11,12)

prepareLimit - building LIMIT

$this->db->select('SELECT id, title FROM ' . TABLE_TEST . ' 
           WHERE id > :id '.$this->db->prepareLimit(0, 2), [':id'=>10]);
# resulting query: SELECT id, title FROM test WHERE id > 10 LIMIT 0, 2

Recommendations for composing SQL query text

Let's consider a simple SELECT query:

'SELECT COUNT(S.id)
 FROM ' . TABLE_SHOPS . ' S
     LEFT JOIN ' . TABLE_USERS . ' U ON S.id = U.shop_id
 WHERE S.id = :id AND S.moderated = 0
 GROUP BY S.id'

We recommend the following guidelines when writing queries:

  • When declaring a query string, always use single quotes.
  • Write all keywords in uppercase.
  • Use line breaks for readability.
  • Preserve the width of the query. If the list of selected columns exceeds the visible area, move it to the next line.
  • Substitute parameters using placeholders, for example, :id, except for those that can be specified explicitly (e.g., moderated = 0 in the example).
  • Avoid using backticks (`) for column names if they conflict with reserved words; rename them instead.
  • If there are conflicts between table names, use capital letters as abbreviations. Use the name of the entity stored in the table or the initial letters of the define words for the abbreviation (except for the word "TABLE").

Model

A model is implemented using the Model class and is an integral part of the application module, combining all database queries accessible through invocations of its methods.

In the module, the model methods are called using $this->model->, where access to the database object is available through $this->db->.

The model class includes a convenient prepareFilter method, the purpose of which is to build the WHERE clause with placeholders and substitute the table prefix if necessary.

Here is an example usage:

<?php
$filter = $this->prepareFilter(array(
    'moderated' => 1,
    ':status' => array('(A.status >= :min OR A.status <= :max)', ':min'=>1, ':max'=>3),
    'id' => array(10,20,30),
    'title IS NOT NULL',
), 'A');

// result:
array(
    'where' => 'WHERE A.moderated = :moderated AND
               (A.status >= :min OR status <= :max) AND
                A.id IN (10,20,30),
                A.title IS NOT NULL',
    'bind' => array(
        ':moderated' => 1,
        ':min' => 1,
        ':max' => 3,
    ),
);

The naming of model methods is always done according to the following logic:
{module entity name}{action name}

Examples:

itemSave - add/edit a record

itemData - retrieve record data

itemsList - retrieve a list of records

itemsSave - save data for multiple records

Table Naming and Structure

All tables are declared as defines in the format TABLE_{module name}_{entity name} or TABLE_{entity name}.

The first option is used when the module uses more than one key entity. Non-key entities, such as categories and tags, are not considered key entities.

The declaration of these defines is allowed in the model file of the corresponding application module, at the beginning of the file.

Always use the common prefix DB_PREFIX when declaring these constants. An example declaration can be seen in the /config/db.tables.php file.

Examples of table naming:

TABLE_USERS - user table

TABLE_SHOPS - shop table

TABLE_BLOG_CATEGORIES - blog category table (blog module)

Examples of tables with one-to-many relationships:

TABLE_BBS_ITEMS_IMAGES - images table associated with an ad (bbs module)

TABLE_BBS_ITEMS_COMMENTS - comments table associated with an ad (bbs module)

TABLE_BLOG_POSTS_TAGS - post-tag relationship table (blog module)

TABLE_SHOPS_IN_CATEGORIES - shop-category relationship

Tables with localizable data always end with _LANG. Examples:

TABLE_PAGES - static pages table

TABLE_PAGES_LANG - localized data for static pages

TABLE_BLOG_CATEGORIES - blog category table

TABLE_BLOG_CATEGORIES_LANG - localized data for blog categories

Table Structure

MySQL tables should always use the InnoDB engine, except when it is necessary to use FULLTEXT indexes or there are other arguments in favor of using MyISAM.

For cases where foreign keys are used, the export must take into account the correct sequential deletion/creation of tables with foreign keys. For example, use SET FOREIGN_KEY_CHECKS=0; and SET FOREIGN_KEY_CHECKS=1;.

It is also important to include a general comment about the table - what entity it stores - and comments for individual columns if their purpose is not clear from the column name.

Database Types

Currently, only MySQL (pdo_mysql) is supported.

In the application, all SQL queries that use specific constructions are marked with comments. For example, constructs like GROUP_CONCAT, INSERT ... ON DUPLICATE KEY UPDATE are specific only to MySQL. In such cases, the query is marked with the comment # MySQL only.

This label will help with a possible transition to another database type in the future.

Migrations

The basis of database structure migrations is the Phinx library version 0.8.x. The documentation for this tool and writing migrations can be found here.

Application Migrations

You can create a migration from the admin panel interface in the "Site Settings / Updates / Migrations" section. First, you need to enable debug mode.

If the creation is successful, a migration file will be created in the /files/migrations/*.php directory. In this file, you will find the migrate function where you should write the code that modifies the database structure. The rollback function is responsible for the code that rolls back the database structure.

Migrations are executed during the installation of an update or manually in the same section.

You can also run migrations from the console by executing the following command:

php bffc migrations/migrate -t 2.4.0

Change the migration version to the required version.

Extension Migrations

You can create a migration from the admin panel interface in the plugin settings, under the "Developer / Migrations" tab.

If the creation is successful, a migration file will be created in the plugin directory
/plugins/plugin-name/migrations/*.php

Migrations are executed during the installation of the plugin or manually in the same section. You can also run migrations from the console by executing the following command:

php bffc migrations/migrate -x plugin/name -t 2.4.0

Replace name with the name of the plugin's directory and the version number with the required migration version. For a theme, use the following command:

php bffc migrations/migrate -x theme/name -t 2.4.0

Access to the extension object in the migration class is available through $this->extension.