Accessing the database

Accessing the database

The database structure

PrestaShop's database tables start with the ps_ prefix. Note that this can be customized during installation

All table names are in lowercase, and words are separated with an underscore character ("_").

When a table establishes the links between two entities, the names of both entities are mentioned in the table's name. For instance, ps_category_product links products to their category.

A few details to note:

• Use the id_lang field to store the language associated with a record.
• Use the id_shop field to store the store associated with a record.
• Tables which contain translations must end with the _lang suffix. For instance, ps_product_lang contains all the translations for the ps_product table.
• Tables which contain the records linking to a specific shop must end with the _shop suffix. For instance, ps_category_shop contains the position of each category depending on the store.

The ObjectModel class

This is the main object of PrestaShop's object model. It can be overridden with precaution.

It is an Active Record kind of class (see: http://en.wikipedia.org/wiki/Active_record_pattern). PrestaShop's database table attributes or view attributes are encapsulated in the class. Therefore, the class is tied to a database record. After the object has been instantiated, a new record is added to the database. Each object retrieves its data from the database; when an object is updated, the record to which it is tied is updated as well. The class implements accessors for each attribute.

Defining the model

You must use the $definition static variable in order to define the model.

For instance:

/**
* Example from the CMS model (CMSCore) 
*/
public static $definition = array(
  'table' => 'cms',
  'primary' => 'id_cms',
  'multilang' => true,
  'fields' => array(
    'id_cms_category'  => array('type' => self::TYPE_INT, 'validate' => 'isUnsignedInt'),
    'position'         => array('type' => self::TYPE_INT),
    'active'           => array('type' => self::TYPE_BOOL),
    // Lang fields
    'meta_description' => 
        array('type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isGenericName', 'size' => 255),
    'meta_keywords'    => 
        array('type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isGenericName', 'size' => 255),
    'meta_title'       => 
        array('type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isGenericName', 'required' => true, 'size' => 128),
    'link_rewrite'     =>
        array('type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isLinkRewrite', 'required' => true, 'size' => 128),
    'content'          => 
        array('type' => self::TYPE_HTML,   'lang' => true, 'validate' => 'isString', 'size' => 3999999999999),
  ),
);

A model for many stores and/or languages

In order to retrieve an object in many languages:

'multilang' => true

In order to retrieve an object depending on the current store

'multishop' => true

In order to retrieve an object which depends on the current store, and in many languages:

'multilang_shop' => true

The main methods

Any overriding of the ObjectModel methods is bound to influence how all the other classes and methods act. Use with care.

The DBQuery class

The DBQuery class is a query builder which helps you create SQL queries. For instance:

$sql = new DbQuery();
$sql->select('*');
$sql->from('cms', 'c');
$sql->innerJoin('cms_lang', 'l', 'c.id_cms = l.id_cms AND l.id_lang = '.(int)$id_lang);
$sql->where('c.active = 1');
$sql->orderBy('position');
return Db::getInstance()->executeS($sql);

Here are some of the methods from this class: