118 lines
5.3 KiB
Markdown
118 lines
5.3 KiB
Markdown
# Database Schema
|
||
|
||
**Analysis Date:** 2026-05-10
|
||
|
||
## Database Engine
|
||
|
||
- **MySQL with InnoDB** — `app/config/parameters.php`
|
||
- **Schema:** `admin_drmaterac`
|
||
- **Table prefix:** `materac_` (referenced as `ps_*` in PrestaShop convention; effective name is `materac_<name>`)
|
||
|
||
Examples below use the `ps_` prefix per PrestaShop documentation; in this install they map to `materac_*`.
|
||
|
||
## ORM / Migration Approach
|
||
|
||
- **Front-office:** PrestaShop `ObjectModel` (in `classes/ObjectModel.php`). Each domain class declares a `$definition` array with `table`, `primary`, `multilang`, `fields`. `add()` / `update()` / `delete()` operate on the corresponding row.
|
||
- **Admin / Symfony:** Doctrine ORM bundled (`DoctrineBundle` in `app/AppKernel.php`).
|
||
- **Migrations:** PrestaShop has no first-class migration tool. Schema is shipped with core in `install/` SQL dumps; modules create tables in their `install()` method.
|
||
|
||
## Custom Tables Created by This Project
|
||
|
||
**The custom Cross Sell PRO module creates NO custom tables.**
|
||
- `modules/crosssellpro/crosssellpro.php::install()` only registers hooks (`displayShoppingCartFooter`, `displayCheckoutSummaryTop`, `displayHeader`, `actionFrontControllerSetMedia`)
|
||
- No `sql/` directory; no `Db::getInstance()->execute("CREATE TABLE ...")` call
|
||
- All cross-sell relationships read from PrestaShop core table `ps_accessory`
|
||
|
||
Other custom code:
|
||
- `import-product.php` updates existing `ps_product*` tables from external XML feed — does not create tables
|
||
- `buy-by-phone.php` does not touch the database
|
||
- `modules/caraty/` — investigate when relevant; not analyzed in detail
|
||
|
||
## Custom Columns Added Via Override
|
||
|
||
`override/classes/Product.php` extends `ps_product` / `ps_product_lang` with Google Shopping fields (declared in the `$definition` array — actual ALTER TABLE must have been run manually or via a separate install script):
|
||
- `in_google_shopping` — BOOL, shop-scoped
|
||
- `product_name_google_shopping` — STRING, lang-scoped
|
||
- `product_short_desc_google_shopping` — HTML, lang-scoped
|
||
- `custom_label_0` … `custom_label_4` — STRING, lang-scoped
|
||
|
||
These are unrelated to the cross-sell module.
|
||
|
||
## PrestaShop Core Tables Touched by Cross Sell PRO
|
||
|
||
| Table | Purpose | How module uses it |
|
||
|---|---|---|
|
||
| `ps_product` | Product master data | Filtered by `active = 1` and shop visibility |
|
||
| `ps_accessory` | Related-product associations | Read via `Product::getAccessoriesLight($id_lang, $id_product)` |
|
||
| `ps_product_attribute` | Product variants/combinations | `getCombinationFlags()` checks if a product has combinations (decides direct add-to-cart vs link to product page) |
|
||
| `ps_cart` | Cart entity | Reached via `$context->cart` |
|
||
| `ps_cart_product` | Items currently in cart | `Cart::getProducts(true)` excludes already-in-cart products from cross-sell list |
|
||
| `ps_product_lang` | Multilingual product names/descriptions | Used by `ProductListingPresenter` |
|
||
| `ps_product_shop` | Shop-specific visibility | `WHERE product_shop.visibility IN ('both', 'catalog')` |
|
||
| `ps_image` | Product images | Cover image fetched by `ImageRetriever` |
|
||
| `ps_category_lang` | Category names | Used in product presentation |
|
||
| `ps_feature`, `ps_feature_value`, `ps_product_feature` | Product features | May appear in presented data |
|
||
| `ps_hook_module` | Hook registrations | Written by `parent::install()` when module installs |
|
||
|
||
## Key Queries (in `modules/crosssellpro/crosssellpro.php`)
|
||
|
||
**Combination check** (lines ~237–243):
|
||
```sql
|
||
SELECT p.id_product, COUNT(pa.id_product_attribute) AS combinations
|
||
FROM `ps_product` p
|
||
LEFT JOIN `ps_product_attribute` pa ON (pa.id_product = p.id_product)
|
||
WHERE p.id_product IN (...)
|
||
GROUP BY p.id_product
|
||
```
|
||
|
||
**Visibility filter** (lines ~264–275):
|
||
```sql
|
||
SELECT p.id_product
|
||
FROM `ps_product` p
|
||
[Shop::addSqlAssociation]
|
||
WHERE p.id_product IN (...)
|
||
AND p.active = 1
|
||
AND product_shop.visibility IN ('both', 'catalog')
|
||
ORDER BY FIELD(p.id_product, ...)
|
||
```
|
||
|
||
## Relationships (Cross-Sell Data Path)
|
||
|
||
```
|
||
ps_cart (id_cart)
|
||
└─1:N─ ps_cart_product (id_product)
|
||
└─1:1─ ps_product (id_product)
|
||
└─1:N─ ps_accessory (id_product_2)
|
||
└─1:1─ ps_product [the cross-sell candidate]
|
||
├─1:N─ ps_product_attribute (combinations)
|
||
├─1:N─ ps_image (cover)
|
||
└─1:N─ ps_product_lang (name, description)
|
||
```
|
||
|
||
## Module Install / Uninstall
|
||
|
||
**Install** — `modules/crosssellpro/crosssellpro.php` lines ~37–44:
|
||
```php
|
||
return parent::install()
|
||
&& $this->registerHook('displayShoppingCartFooter')
|
||
&& $this->registerHook('displayCheckoutSummaryTop')
|
||
&& $this->registerHook('displayHeader')
|
||
&& $this->registerHook('actionFrontControllerSetMedia');
|
||
```
|
||
|
||
**Uninstall** — lines ~46–48:
|
||
```php
|
||
return parent::uninstall();
|
||
```
|
||
|
||
`parent::install()` / `parent::uninstall()` write to / clean up `ps_module` and `ps_hook_module`.
|
||
|
||
## Backups
|
||
|
||
- Snapshots of the schema are present in `iadmin/backups/` (e.g. `1698930967-7683b7af.sql.bz2`) — **see `concerns.md`**: these are inside the webroot and should be moved out or denied via `.htaccess`.
|
||
|
||
---
|
||
|
||
*DB schema analysis: 2026-05-10*
|
||
*Update when adding/modifying tables, modules, or overrides that affect schema*
|