Good practices on class Db for Prestashop 1.4

Автор: Raphaël Malié
Опубликованно: 08 августа 2011
Источник: Good practices on class Db for Prestashop 1.4

Introduction

Most modules and developments on PrestaShop require you to use or enter information on a database. Any developer sees the core DB class as an essential part of the process. On top of providing potential abstraction for other types of relational database, the DB class has several tools to make life simpler!

Learn about the various methods, when to use them and the best practices for the development stage.

Class basics

The DB class is made up of two classes:

Although DB is a pseudo-singleton, it can still be activated manually if necessary as the developer is public. However, in PrestaShop it must be accessed as follows:

$db = Db::getInstance();

In some cases you may see requests as per the following code:

$db = Db::getInstance(_PS_USE_SQL_SLAVE_);

When the above is connected, it could enter slave servers if the PrestaShop user allows the use of slave MySQL servers in their architecture. The standard _PS_USE_SQL_SLAVE_ argument should only be used for read-only queries (SELECT, SHOW etc.), and only if a result does not need to be immediately updated. It is necessary you use the master server to make a select query just after entering something on the same table.

Different methods

1. autoExecute() method

This method automatically generates the insertion or update of the base from a table of data. This method should be used instead of making INSERT or UPDATE requests unless these requests are slightly complex (using SQL functions, intersect queries etc.). The benefit of using one method to do everything is to centralise requests. You can edit this method using PrestaShop’s 1.4 override system when there is a particular process to apply to tables during insertion..

Dummy example:

$target = Tools::getValue('id');
$name = Tools::getValue('name');
Db::getInstance()->autoExecute('target_table', array(
'id_target' =>    (int)$target,
'name' =>    pSQL($name),
), 'INSERT');

Requesting this method results in the following SQL query:

INSERT INTO `target_table` (`id_target`, `name`) VALUES (10, 'myName')

Important :

2. autoExecuteWithNullValues() method

This method does the same as autoExecute() but with one subtle difference: empty strings and null values are replaced by SQL NULLs. This method can be used if your fields accept null values and results in them being NULL rather than an empty string.

This method is particularly useful when using auto increment to enter an empty entry on a table. The unique key in auto increment will then be entered as NULL thus avoiding an entry query with no field.

3. Delete method ($table, $where = false, $limit = false, $use_cache = 1)

This method is the DELETE version of autoExecute(). It can be used for the same purpose. The $limit argument limits the number of saved items you can delete. The other benefit of this method is that it can be used with PrestaShop’s SQL query cache system and deletes the cached queries unless the $use_cache argument is false.

Example :

Db::getInstance()->delete(‘target_table’, ‘myField < 15’, 3);

will generate the following query

DELETE FROM target_table WHERE myField < 15 LIMIT 3

4. Execute($sql, $use_cache = 1) method

This method executes the given SQL query. It should only be used for write-only queries (INSERT, UPDATE, DELETE, TRUNCATE etc.) as it also deletes the query cache (unless the $use_cache argument is false).

Example :

$sql = ‘DELETE FROM ‘._DB_PREFIX_.’product WHERE date_upd < NOW()’;
if (!Db::getInstance()->Execute($sql))
die(‘Erreur etc.)’;

5.ExecuteS($sql, $array = true, $use_cache = 1) method

This method executes the given SQL query and loads all the results on a multidimensional table. It should not be used with read-only queries (SELECT, SHOW etc.). The query results will be cached unless the argument $use_cache is false. The second argument $array is depreciated and should not be used, leave it as true.

Exemple :

$sql = ‘SELECT * FROM ‘._DB_PREFIX_.’shop’;
if ($results = Db::getInstance()->ExecuteS($sql))
foreach ($results as $row)
    echo $row[‘id_shop’].’ :: ‘.$row[‘name’].’<br />’;

6. getRow($sql, $use_cache = 1) method

This method executes the given SQL query and collects the first line of results. It should only be used with read-only queries (SELECT, SHOW etc.). The query results will be cached unless the argument $use_cache is false.

Warning: this method automatically adds a LIMIT clause to the query. Ensure that you do not add one manually.

Example :

$sql = ‘SELECT * FROM ‘._DB_PREFIX_.’shop
WHERE id_shop = 42’;
if ($row = Db::getInstance()->getRow($sql))
echo $row[‘id_shop’].’ :: ‘.$row[‘name’];

7. getValue($sql, $use_cache = 1) method

This method executes the given SQL query and only collects the first result on the first line. It should only be used with read-only queries (SELECT, SHOW etc.). The query results will be cached unless the argument $use_cache is false.

Warning: this method automatically adds a LIMIT clause to the query. Ensure that you do not add one manually.

Example :

$sql = ‘SELECT COUNT(*) FROM ‘._DB_PREFIX_.’shop’;
$totalShop = Db::getInstance()->getValue($sql);

8. The NumRows() method

This method caches and displays the number of results from the last SQL query.

Warning: this method is not depreciated but we strongly advise you not to use it for reasons of best practice. It is actually better to collect the number of results via a SELECT COUNT(*) query beforehand.

9. Some other methods

Copyright © 2011, Магистр ДонНТУ Артёмов Артем Сергеевич