➕ Tablas personalizadas en modulos

<?php

public function install(): bool
{
    return parent::install()
        && $this->registerHook('displayHome')
        && $this->createTables();
}

private function createTables(): bool
{
    $sql = [];

    // ── Tabla principal ──
    $sql[] = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'mymodule_item` (
        `id_mymodule_item` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `id_category` int(10) unsigned NOT NULL DEFAULT 0,
        `reference` varchar(64) NOT NULL DEFAULT \'\',
        `price` decimal(20,6) unsigned NOT NULL DEFAULT \'0.000000\',
        `active` tinyint(1) unsigned NOT NULL DEFAULT 1,
        `sort_order` int(10) unsigned NOT NULL DEFAULT 0,
        `date_add` datetime NOT NULL,
        `date_upd` datetime NOT NULL,
        PRIMARY KEY (`id_mymodule_item`),
        KEY `id_category` (`id_category`),
        KEY `active` (`active`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;';

    // ── Tabla multiidioma ──
    $sql[] = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'mymodule_item_lang` (
        `id_mymodule_item` int(10) unsigned NOT NULL,
        `id_lang` int(10) unsigned NOT NULL,
        `id_shop` int(10) unsigned NOT NULL DEFAULT 1,
        `name` varchar(255) NOT NULL,
        `description` text,
        PRIMARY KEY (`id_mymodule_item`, `id_lang`, `id_shop`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;';

    // ── Tabla de relaciones (N:N) ──
    $sql[] = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'mymodule_item_category` (
        `id_mymodule_item` int(10) unsigned NOT NULL,
        `id_category` int(10) unsigned NOT NULL,
        PRIMARY KEY (`id_mymodule_item`, `id_category`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;';

    foreach ($sql as $query) {
        if (!Db::getInstance()->execute($query)) {
            return false;
        }
    }

    return true;
}

<?php

public function uninstall(): bool
{
    return $this->dropTables()
        && parent::uninstall();
}

private function dropTables(): bool
{
    $tables = [
        'mymodule_item_category',  // Primero las tablas con FK
        'mymodule_item_lang',
        'mymodule_item',
    ];

    foreach ($tables as $table) {
        if (!Db::getInstance()->execute(
            'DROP TABLE IF EXISTS `' . _DB_PREFIX_ . $table . '`'
        )) {
            return false;
        }
    }

    return true;
}

// ── IMPORTANTE: El orden importa ──
// Si hay FOREIGN KEYS, eliminar primero las tablas hijas
// Si no hay FK, el orden no importa pero es buena practica
// empezar por las tablas de relacion y lang

<?php

// Archivo: classes/MyModuleItem.php
class MyModuleItem extends ObjectModel
{
    public $id_category;
    public $reference;
    public $price;
    public $active;
    public $sort_order;
    public $name;        // multilang
    public $description; // multilang
    public $date_add;
    public $date_upd;

    public static $definition = [
        'table'     => 'mymodule_item',
        'primary'   => 'id_mymodule_item',
        'multilang' => true,
        'fields'    => [
            'id_category' => ['type' => self::TYPE_INT,    'validate' => 'isUnsignedId'],
            'reference'   => ['type' => self::TYPE_STRING, 'validate' => 'isReference', 'size' => 64],
            'price'       => ['type' => self::TYPE_FLOAT,  'validate' => 'isPrice'],
            'active'      => ['type' => self::TYPE_BOOL,   'validate' => 'isBool'],
            'sort_order'  => ['type' => self::TYPE_INT,    'validate' => 'isUnsignedInt'],
            'date_add'    => ['type' => self::TYPE_DATE,   'validate' => 'isDate'],
            'date_upd'    => ['type' => self::TYPE_DATE,   'validate' => 'isDate'],
            'name'        => ['type' => self::TYPE_STRING, 'lang' => true, 'validate' => 'isCatalogName', 'required' => true, 'size' => 255],
            'description' => ['type' => self::TYPE_HTML,   'lang' => true, 'validate' => 'isCleanHtml'],
        ],
    ];

    // Registrar la clase en el autoloader de PS:
    // En mymodule.php:
    // if (!defined('_PS_VERSION_')) { exit; }
    // require_once __DIR__ . '/classes/MyModuleItem.php';
}

<?php

/**
 * Upgrade desde 1.0.x hasta 1.1.0
 * Agrega columna 'sku' y un indice a mymodule_item.
 */
if (!defined('_PS_VERSION_')) {
    exit;
}

function upgrade_module_1_1_0(MyModule $module): bool
{
    $db  = Db::getInstance();
    $sql = [];

    // ── Agregar columna nueva ──
    $sql[] = 'ALTER TABLE `' . _DB_PREFIX_ . 'mymodule_item`
        ADD COLUMN `sku` VARCHAR(64) NOT NULL DEFAULT \'\'  AFTER `reference`';

    // ── Agregar indice ──
    $sql[] = 'ALTER TABLE `' . _DB_PREFIX_ . 'mymodule_item`
        ADD INDEX `sku` (`sku`)';

    // ── Columna en tabla lang ──
    $sql[] = 'ALTER TABLE `' . _DB_PREFIX_ . 'mymodule_item_lang`
        ADD COLUMN `short_description` VARCHAR(512) NOT NULL DEFAULT \'\'  AFTER `name`';

    foreach ($sql as $query) {
        if (!$db->execute($query)) {
            return false;
        }
    }

    // ── Actualizar version en Configuration ──
    Configuration::updateValue('MYMODULE_VERSION', '1.1.0');
    return true;
}