Tablas del módulo ! Importante!

Creada por Nicolás Orellana, Modificado el Wed, 20 Mar 2024 a las 01:15 PM por Nicolás Orellana

Bienvenido al articulo de Base de datos! este articulo tiene como objetivo enseñarte cuales son las interacciones con la base de datos que usa el módulo de centry para prestashop.

Partiremos con el guardado de Configuraciones.

Al momento de instalar el modulo de prestashop este te lleva inmediatamente a la pantalla de configuracion del modulo

Toda la información de esta pantalla se guarda en la tabla de configuraciones nativa de prestashop. Y estas son consultadas y actualizadas por el modulo usando las funciones dejadas de ejemplo a continuacion.

(Imagen de centry_ps_esclavo/classes/ConfigurationCentry.php)

En esta tabla de configuraciones todos aquellos registros que comiencen con "CENTRY_" son configuraciones de esta panatalla.

Ahora a explicar lo entrenido:

La tablas que crea el módulo para asegurar la comunicación entre Centry y Prestashop:

Nombre Generico	Tabla_Centry	Id_foreing	Tabla_Relación	Primary_key	Select * From Tabla_Centry;
Homologación Variantes	ps_variant_centry	id_prestashop	ps_product_attribute	id_product_attribute	Select * From ps_variant_centry;
Homologación Títulos de Color/Talla	ps_attribute_group_centry	id_prestashop	ps_attribute_group	id_attribute_group	Select * From ps_attribute_group_centry;
Homologación de marcas	ps_brand_centry	id_prestashop	ps_manufacturer	id_manufacturer	Select * From ps_brand_centry;
Homologación Categorías	ps_category_centry	id_prestashop	ps_category	id_category	Select * From ps_category_centry;
Homologación Valores Color	ps_color_centry	id_prestashop	ps_attribute	id_attribute	Select * From ps_color_centry;
Homologación de Atributos	ps_feature_centry	id_prestashop	ps_feature	id_feature	Select * From ps_feature_centry;
Homologación de Valores de Atributos	ps_feature_value_centry	id_prestashop	ps_feature_value	id_feature_value	Select * From ps_feature_value_centry;
Homologación de Valores de Atributos Texto Simple	ps_feature_value_centry	product_id	ps_product	id_product	Select * From ps_feature_value_centry;
Homologación de imagenes	ps_image_centry	id_prestashop	ps_image	id_image	Select * From ps_image_centry;
Homologación de pedidos	ps_order_centry	id_prestashop	ps_orders	id_order	Select * From ps_order_centry;
Homolgación de Estados de pedidos	ps_order_status_centry	id_prestashop	ps_order_state	id_order_state	Select * From ps_order_status_centry;
Homolgación de productos	ps_product_centry	id_prestashop	ps_product	id_product	Select * From ps_product_centry;
Homologación de productos simplres a variantes	ps_simple_product_variant_centry	id_prestashop	ps_product	id_product	Select * From ps_simple_product_variant_centry;
Homologación de Valores de Tallas	ps_size_centry	id_prestashop	ps_attribute	id_attribute	Select * From ps_size_centry;

Estas Tablas en la base de dato son las tablas relacionales que permiten asegurar la comunicacion y traduccion de objetos centry con los objetos Prestashop.

La definicion de creacion de estas tablas las puedes encontrar en los archivos de clasess/Models/homologation

Se informa en la tabla anteriormente descrita la consulta que puedes configurar en la seccion Base de datos del panel de prestashop para consultar las relaciones guardadas. El prefijo puede variar de prestashop en prestashop.

Adicionalmente se recomienta prestar atencion al motor con el cual se definieron las tablas, se recomienda InnoDB debido a que este permite la relacion de llaves foraneas ,asi la BD elimina las relaciones de objetos que se eliminen en prestashop. Sin este comportamiento el modulo Podria caer en condiciones Borde, En caso de que por alguna razon el motor definido en las tablas sea otro distinto y no permita la definicion de llaves foranea y eliminacion en cascada se hace refrencia a la siguiente tabla que tienes las definiciones de triggers para asegurar este comportamiento.

Nombre	Prefijo	ps_		En caso de que se encuentre tabla sin motor de relacion limpieza previa a definicion de trigger	Definición de Triggers
Nombre Generico	Tabla_Centry	Id_foreing	Tabla_Relación	DELETE FROM `Tabla_Centry` WHERE `Id_foreing` NOT IN ( SELECT `Primary_key` FROM `Tabla_Relación`);	DELIMITER $$ CREATE TRIGGER `delete_Tabla_Centry` AFTER DELETE ON `Tabla_Relación` FOR EACH ROW BEGIN DELETE FROM `Tabla_Centry` WHERE `Id_foreing` = OLD.`Primary_key`; END $$ DELIMITER ;
Homologación Variantes	ps_variant_centry	id_prestashop	ps_product_attribute	DELETE FROM `ps_variant_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_product_attribute` FROM `ps_product_attribute`);	DELIMITER $$ CREATE TRIGGER `delete_ps_variant_centry` AFTER DELETE ON `ps_product_attribute` FOR EACH ROW BEGIN DELETE FROM `ps_variant_centry` WHERE `id_prestashop` = OLD.`id_product_attribute`; END $$ DELIMITER ;
Homologación Títulos de Color/Talla	ps_attribute_group_centry	id_prestashop	ps_attribute_group	DELETE FROM `ps_attribute_group_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_attribute_group` FROM `ps_attribute_group`);	DELIMITER $$ CREATE TRIGGER `delete_ps_attribute_group_centry` AFTER DELETE ON `ps_attribute_group` FOR EACH ROW BEGIN DELETE FROM `ps_attribute_group_centry` WHERE `id_prestashop` = OLD.`id_attribute_group`; END $$ DELIMITER ;
Homologación de marcas	ps_brand_centry	id_prestashop	ps_manufacturer	DELETE FROM `ps_brand_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_manufacturer` FROM `ps_manufacturer`);	DELIMITER $$ CREATE TRIGGER `delete_ps_brand_centry` AFTER DELETE ON `ps_manufacturer` FOR EACH ROW BEGIN DELETE FROM `ps_brand_centry` WHERE `id_prestashop` = OLD.`id_manufacturer`; END $$ DELIMITER ;
Homologación Categorías	ps_category_centry	id_prestashop	ps_category	DELETE FROM `ps_category_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_category` FROM `ps_category`);	DELIMITER $$ CREATE TRIGGER `delete_ps_category_centry` AFTER DELETE ON `ps_category` FOR EACH ROW BEGIN DELETE FROM `ps_category_centry` WHERE `id_prestashop` = OLD.`id_category`; END $$ DELIMITER ;
Homologación Valores Color	ps_color_centry	id_prestashop	ps_attribute	DELETE FROM `ps_color_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_attribute` FROM `ps_attribute`);	DELIMITER $$ CREATE TRIGGER `delete_ps_color_centry` AFTER DELETE ON `ps_attribute` FOR EACH ROW BEGIN DELETE FROM `ps_color_centry` WHERE `id_prestashop` = OLD.`id_attribute`; END $$ DELIMITER ;
Homologación de Atributos	ps_feature_centry	id_prestashop	ps_feature	DELETE FROM `ps_feature_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_feature` FROM `ps_feature`);	DELIMITER $$ CREATE TRIGGER `delete_ps_feature_centry` AFTER DELETE ON `ps_feature` FOR EACH ROW BEGIN DELETE FROM `ps_feature_centry` WHERE `id_prestashop` = OLD.`id_feature`; END $$ DELIMITER ;
Homologación de Valores de Atributos	ps_feature_value_centry	id_prestashop	ps_feature_value	DELETE FROM `ps_feature_value_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_feature_value` FROM `ps_feature_value`);	DELIMITER $$ CREATE TRIGGER `delete_ps_feature_value_centry` AFTER DELETE ON `ps_feature_value` FOR EACH ROW BEGIN DELETE FROM `ps_feature_value_centry` WHERE `id_prestashop` = OLD.`id_feature_value`; END $$ DELIMITER ;
Homologación de Valores de Atributos Texto Simple	ps_feature_value_centry	product_id	ps_product	DELETE FROM `ps_feature_value_centry` WHERE `product_id` NOT IN ( SELECT `id_product` FROM `ps_product`);	DELIMITER $$ CREATE TRIGGER `delete_ps_feature_value_centry` AFTER DELETE ON `ps_product` FOR EACH ROW BEGIN DELETE FROM `ps_feature_value_centry` WHERE `product_id` = OLD.`id_product`; END $$ DELIMITER ;
Homologación de imagenes	ps_image_centry	id_prestashop	ps_image	DELETE FROM `ps_image_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_image` FROM `ps_image`);	DELIMITER $$ CREATE TRIGGER `delete_ps_image_centry` AFTER DELETE ON `ps_image` FOR EACH ROW BEGIN DELETE FROM `ps_image_centry` WHERE `id_prestashop` = OLD.`id_image`; END $$ DELIMITER ;
Homologación de pedidos	ps_order_centry	id_prestashop	ps_orders	DELETE FROM `ps_order_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_order` FROM `ps_orders`);	DELIMITER $$ CREATE TRIGGER `delete_ps_order_centry` AFTER DELETE ON `ps_orders` FOR EACH ROW BEGIN DELETE FROM `ps_order_centry` WHERE `id_prestashop` = OLD.`id_order`; END $$ DELIMITER ;
Homolgación de Estados de pedidos	ps_order_status_centry	id_prestashop	ps_order_state	DELETE FROM `ps_order_status_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_order_state` FROM `ps_order_state`);	DELIMITER $$ CREATE TRIGGER `delete_ps_order_status_centry` AFTER DELETE ON `ps_order_state` FOR EACH ROW BEGIN DELETE FROM `ps_order_status_centry` WHERE `id_prestashop` = OLD.`id_order_state`; END $$ DELIMITER ;
Homolgación de productos	ps_product_centry	id_prestashop	ps_product	DELETE FROM `ps_product_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_product` FROM `ps_product`);	DELIMITER $$ CREATE TRIGGER `delete_ps_product_centry` AFTER DELETE ON `ps_product` FOR EACH ROW BEGIN DELETE FROM `ps_product_centry` WHERE `id_prestashop` = OLD.`id_product`; END $$ DELIMITER ;
Homologación de productos simplres a variantes	ps_simple_product_variant_centry	id_prestashop	ps_product	DELETE FROM `ps_simple_product_variant_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_product` FROM `ps_product`);	DELIMITER $$ CREATE TRIGGER `delete_ps_simple_product_variant_centry` AFTER DELETE ON `ps_product` FOR EACH ROW BEGIN DELETE FROM `ps_simple_product_variant_centry` WHERE `id_prestashop` = OLD.`id_product`; END $$ DELIMITER ;
Homologación de Valores de Tallas	ps_size_centry	id_prestashop	ps_attribute	DELETE FROM `ps_size_centry` WHERE `id_prestashop` NOT IN ( SELECT `id_attribute` FROM `ps_attribute`);	DELIMITER $$ CREATE TRIGGER `delete_ps_size_centry` AFTER DELETE ON `ps_attribute` FOR EACH ROW BEGIN DELETE FROM `ps_size_centry` WHERE `id_prestashop` = OLD.`id_attribute`; END $$ DELIMITER ;

Adicionalmente a estas Tablas, Se definen tablas de apoyo para cola de consumo, webhooks creados en centry y tablas de apoyo para registro de errores y log de tareas.

Consulta para revision de tareas en Cola
select * from ps_centry_pending_task

Conculta para revision de Logs :

select * from ps_centry_pending_task_log