The data model–how and where data is stored

Let’s start this journey into PrestaShop by presenting how data is stored.

Data is all mutable information stored on the server to make the shop work, such as product information, customer details, orders, category information, employee settings, and so on.

All this information is stored in a MySQL database on the server. You may already know this because you have been requested to provide the database details to access the MySQL database during the installation steps.

Let’s browse the data to see how it works.

Browsing the data

To browse all the data stored in the PrestaShop database, please follow these steps:

1. Connect to the database created for your PrestaShop with your favorite database client such as phpMyAdmin.
2. Show all tables.

There, you will see a list of all tables, named following these patterns:

• prefix_nameofentity (for example, ps_cms and ps_access):

All entities should use the prefix_nameofentity table name to store all non-localized data, without any restrictions on the number of columns. The first column for those tables is always the unique ID (UID) of each row, and its name always follows the same pattern: id_nameofentity. This is useful for creating relationships with other entities and makes data management easier.

• prefix_nameofentity_lang (for example, ps_cms_lang and ps_carrier_lang):

If an entity has localizable data, you should use another table called prefix_nameofentity_lang to store fields depending on the user’s language.

It should represent a many-to-many relationship between the prefix_nameofentity table and prefix_lang table that contains all available languages in your PrestaShop platform. That’s why you will always find an id_nameofentity column to represent the link with the prefix_nameofentity table and an id_lang column to represent the link with the prefix_lang table. These tables are not mandatory if you don’t have localized data to store.

• prefix_nameofentity_shop (for example, ps_cms_shop and ps_product_shop):

If an entity is shop-dependent (in the context of a multistore website, as PrestaShop makes this possible), you will find another table called prefix_nameofentity_shop. It represents a many-to-many relationship between the prefix_nameofentity table and the ps_shop table containing all available stores created in your PrestaShop platform. Exactly the same way as _lang tables do, you will always find an id_nameofentity column to represent the link with the prefix_nameofentity table and an id_shop column to represent the link with the prefix_shop table. These tables are only present if you want to make your entity multistore compliant.

What are prefix_ and nameofentity?

prefix_ is a string that is set during the installation process, in the database details step, and by default, it’s set to ps_, but you may have chosen a different one.

In the rest of the book, we will assume that you chose ps_ as a prefix.

nameofentity stands for the name of the entity stored. For example, for products, the name of the entity is product; that’s why you should find a ps_product table in the database!

Reverse engineering the contact entity

The contact entity contains all information about the recipients of contact queries done through the Contact us page of your store. You can put as many recipients of services as you need. Usually, there are two of them automatically set: one for technical queries and the other for customer/sales queries.

The best way to understand how things work is by doing reverse engineering. So, let’s explore how the contact entity is built in the database. By browsing the tables list, considering that our prefix is set to ps_, you will find three tables corresponding to the contact entity:

• The ps_contact table:

Name	Type	Extra information
id_contact	Int(10)	Auto_increment
email	Varchar(255)
customer_service	TinyInt(1)
position	TinyInt(2)

Table 1.1 – The ps_contact table

This table contains all universal, non-translatable fields about contact information: the email address, a flag (true=1/false=0) to inform the system if the recipient provides customer service, and the position for the display order on the contact form. Please note that there is always a key column following the id_nameofentity pattern in entities. It is auto-incrementing and set as the primary key. It enables us to identify and manipulate rows easily. It is also mandatory if we have to create a many-to-one relationship with this table.

• The ps_contact_lang table:

Name	Type	Extra information
id_contact	Int(10)
id_lang	Int(10)
name	Varchar(255)
description	Text

Table 1.2 – The ps_contact_lang table

This table contains all localizable data languages linked. It is used to provide translated content to the system. For the contact entity, there are only two fields requiring localization: the name field, designating the recipient of contact queries, and the description field, explaining the function of the recipient. This table materializes the many-to-one relationship with ps_contact, as there are many translations for only one contact. The id_contact column is used to map translations with the corresponding ps_contact rows. The id_lang column is used to map translations with the corresponding languages (1 for English, 2 for French, and so on, depending on your settings).

• The ps_contact_shop table:

Name	Type	Extra information
id_contact	Int(11)
id_shop	Int(11)

Table 1.3 – The ps_contact_shop table

This table represents the many-to-many relationship between the ps_contact entity and the ps_shop entity. It links contacts to the store in which they are available to customers/visitors. The id_contact column stands for the linked contact, and id_shop stands for the shop where the corresponding contact is available.

As you may have seen, the tables are not linked by foreign keys, which would be normal with a many-to-many relationship, but in PrestaShop, this is not the case yet.

You now know with this example how things are linked and stored together in the database.

Tip

If you want to inspect the whole structure and the links between tables, the structure is available during the installation process—that is, if you have not yet removed the install folder (which is recommended), at this path: /install/data/db_structure.sql.

Now that you know how and where data is stored in the database, let’s see where and how entities are coded in PHP in order to manage them.

Manipulating data by extending the ObjectModel class

PrestaShop is a set of PHP files, templates, and assets (such as images, JavaScript, or style sheets) working together to generate an e-commerce system.

Specifically, inside the PHP files, in the /classes/ObjectModel.php folder, you will find the definition of the ObjectModel class, which implements the EntityInterface interface defined in the /src/Code/Foundation/Database/EntityInterface.php folder with these methods:

public static function getRepositoryClassName();
public function save();
public function delete();
public function hydrate(array $keyValueData);

Almost all entities of the system extend this ObjectModel class, which contains all the necessary tools to manipulate the entity.

Practically, all entities’ definitions are PHP classes defined in /classes/NameOfEntity.php. Feel free to open some of the files to see how they work.

If you come back to the ObjectModel class, you will see that it contains a property named $definition. This is an array that will contain all metadata fields and properties of the entity. It has to follow this prototype:

public static $definition = [
    'table' => 'nameofentity',
    'primary' => 'id_nameofentity',
    'multilang' => true,//if not localizable set to false
    'multishop' => true,//if not multistore set to false
  //'multilang_shop' => true, 
  //can replace both previous fields set to true
    'fields' => array(
        'id_nameofentity'  => ['type' => self::TYPE_INT,
            'validate' => 'isUnsignedInt'],
        'field_name'           => ['type' => ... ],
     ....)
];

Member variables have to be defined to represent each field that will be hydrated with their value. For example, if you have a field named field_name in the database, it has to be added to the entity class, like this:

public $field_name;

As seen before, to comply with the EntityInterface interface, we have to implement the three following methods:

• The save() method implements, in the database, the insertion or updates of the row represented by the current entity instance
• The delete() method removes the current row
• The hydrate() method sets the member variables of the current instance in order to be able to use the object

Practically, after having defined an entity with the creation of its class, everywhere in the code of PrestaShop, we will be able to create a new instance of it by using this:

//To create an empty instance of NameOfEntity
$entityInstance = new NameOfEntity();

If you want an entity to be instanced and hydrated with the values of the row with id_nameofentity equal to $value_id_nameofentity, you can use this code:

/* To create a hydrated instance of NameofEntity with id_nameofentity=$value_id_nameofentity */
$entityInstanceHydrated = new NameOfEntity((int) $value_id_nameofentity);

Try this

As a practical exercise, try to open one of the classes defined in the /classes folder.

Using the Db class

As you may have seen by opening some classes of the /classes folder, the ObjectModel child class uses the Db class defined in the /classes/db/Db.php file to work with the database’s low-level actions, such as insert, update, and delete. We won’t dive deeply into this class—you just have to remember that you can use this class anywhere with the following methods. In this method, we’ll instantiate the Db class to use its functions:

/*Instantiate the Db class in order to be able use its functions*/
$db = \Db::get Instance();

The following are the SELECT methods:

/*To do a select query with _DB_PREFIX_ a constant that contains the prefix for tables*/
$select_query = 'SELECT * FROM `' . _DB_PREFIX_ . 'nameofentity` WHERE 1';
$result = $db->executeS($select_query);
/*To get the first line of the results as an array */
$select_query = "SELECT `id_nameofentity`, `field_name` FROM `' . _DB_PREFIX_ . 'nameofentity` WHERE 1";
$result = $db->getRow($request);

The following code can be used to insert, update, and delete and for custom query methods:

/*To insert row(s) with pSQL() a function to avoid SQL injections or other threats*/
$result = $db->insert('nameofentity ',
    [
        //First row
        [
            'field_name' => pSQL('field_value'),
            'field_name' => pSQL('field_value'),
        ],
        //Second row
        [
            'field_name' => pSQL('field_value'),
            'field_name' => pSQL('field_value'),
        ]
    ]
);
/*To update row with id_nameofentity=2*/
$result = $db->update('nameofentity', [
    'field_name' => pSQL('field_value'),
    'field_name' => pSQL('field_value'),
], 'id_nameofentity = 2', 1, true);
/*To delete row with id_nameofentity=2*/
$result = $db->delete('nameofentity', 'id_nameofentity = 2');
/*To execute a raw custom query*/
$sql_query = "UPDATE `' . _DB_PREFIX_ . 'nameofentity`  SET ` field_name `= 3 WHERE `id_nameofentity `=2";
$result = $db->execute($sql_query);

Important note

The way of managing data by using the ObjectModel extension is a bit old-fashioned. As PrestaShop is being migrated to a Symfony-based core, the target is to use the Doctrine library for all entities as soon as the migration is finished.

Currently, while the migration is still a work in progress, most entities are still using ObjectModel to manage the database interfaces, as illustrated in the following diagram:

Figure 1.1 – The data management process in PrestaShop

Now that we know how the data is stored and can be manipulated, we need to understand how FO and BO pages are generated by PrestaShop.