其他 #440
Updated by Junyi Zhang 9 months ago
*Background* # New requirement for v4.0 OTS See: OneDrive\赤蓝商贸（上海）有限公司\Shanghai Group - IT\开发相关\OTS V4\开发需求 *Task Details* * *Rule (v4.0)* # %{color:red}DONE%: ENGINE = MyISAM -> InnoDB ## 修改date/datetime的值 0000-0000-000 -> null (遇到在修改) <pre> sample: 1. 查询所有默认值为0000-00-00 / 0000-00-00 00:00:00的表 SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT FROM information_schema.columns WHERE column_default = '0000-00-00' AND table_schema = 'redblue_ots4.0'; SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT FROM information_schema.columns WHERE column_default = '0000-00-00 00:00:00' AND table_schema = 'redblue_ots4.0'; 2. 修改列：允许为null，默认值为null ALTER TABLE `ots_product_discount` CHANGE COLUMN `date_start` `date_start` DATE NULL DEFAULT NULL AFTER `price`, CHANGE COLUMN `date_end` `date_end` DATE NULL DEFAULT NULL AFTER `date_start`; Note: 已修改的表 ots_zone_to_geo_zone (如果数据是 0000-00-00 00:00:00的话，需要修改成有效的 datetime，然后执行以上SQL) ots_return ots_product ots_product_special ots_product_discount ots_coupon </pre> ## 修改表（一个一个执行）的ENGINE = MyISAM -> InnoDB <pre> sample: 1. 先查需要修改的表 SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') FROM information_schema.tables WHERE table_schema = 'redblue_ots4.0' AND engine = 'MyISAM'; 2. MyISAM -> InnoDB ALTER TABLE `ots_order_status` ENGINE=InnoDB; </pre> # %{color:red}DONE%: Don’t need to specify the length or precision for your *numeric* data. (遇到在修改) <pre> SELECT table_name, column_name, data_type, numeric_precision, numeric_scale FROM information_schema.columns WHERE table_schema = 'redblue_ots4.0' AND data_type IN ('tinyint','int', 'bigint', 'decimal', 'numeric', 'float', 'double') AND (numeric_precision IS NOT NULL OR numeric_scale IS NOT NULL); </pre> # %{color:red}DONE%: Set the default text type obviously -> utf8mb4_general_ci <pre> 1. 查询表COLLATE为'utf8mb3_general_ci'的表 SELECT table_name FROM information_schema.tables WHERE table_schema = 'redblue_ots4.0' AND table_collation = 'utf8mb3_general_ci'; 2. 修改：utf8mb3_general_ci -> UTF8MB4_GENERAL_CI ALTER TABLE `ots_unit_class` CONVERT TO CHARACTER SET utf8mb4 COLLATE UTF8MB4_GENERAL_CI; </pre> # %{color:red}TO-DO%: 需要维护v3数据库 * *Record all SQL changes (v4)*: ENGINE = MyISAM -> InnoDB |. Issue |. SQL | | #441 | <pre> ALTER TABLE `ots_order_status` ADD COLUMN `code` INT NOT NULL AFTER `name`, ADD COLUMN `hex` VARCHAR(32) NOT NULL DEFAULT '' AFTER `code`, ALTER TABLE ots_order_status ADD COLUMN status TINYINT NOT NULL DEFAULT '1' AFTER hex; ALTER TABLE ots_order_status ADD COLUMN shipped TINYINT NOT NULL DEFAULT '0' AFTER hex, ADD COLUMN sort_order INT NOT NULL DEFAULT (0) AFTER shipped; </pre> | | #454 | <pre> CREATE TABLE `ots_unit_class` ( `unit_class_id` INT NOT NULL AUTO_INCREMENT, `status` TINYINT(1) NOT NULL DEFAULT '0', PRIMARY KEY (`unit_class_id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4_general_ci; CREATE TABLE `ots_unit_class_description` ( `unit_class_id` INT NOT NULL AUTO_INCREMENT, `language_id` INT NOT NULL, `title` VARCHAR(32) NOT NULL, `unit` VARCHAR(4) NOT NULL, PRIMARY KEY (`unit_class_id`, `language_id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4_general_ci; --------------------------------------------------------- ALTER TABLE `ots_manufacturer` ADD COLUMN `status` TINYINT NOT NULL DEFAULT (0) AFTER `manufacturer_id`, ADD COLUMN `country_id` INT NOT NULL AFTER `name`, ADD COLUMN `zone_id` INT NOT NULL AFTER `country_id`, ADD COLUMN `address_1` VARCHAR(128) NOT NULL DEFAULT '' AFTER `zone_id`, ADD COLUMN `address_2` VARCHAR(128) NOT NULL DEFAULT '' AFTER `address_1`, DROP COLUMN `image`; ALTER TABLE `ots_manufacturer` ADD COLUMN `city` VARCHAR(128) NOT NULL DEFAULT '' AFTER `zone_id`; DROP TABLE `ots_manufacturer_to_store`; ALTER TABLE `ots_category` DROP COLUMN `image`, DROP COLUMN `top`, DROP COLUMN `column`; ALTER TABLE `ots_category_description` DROP COLUMN `meta_title`, DROP COLUMN `meta_description`, DROP COLUMN `meta_keyword`; DROP TABLE `ots_category_filter`; DROP TABLE `ots_category_to_layout`; DROP TABLE `ots_category_to_store`; --------------------------------------------------------- DROP TABLE `ots_option`; CREATE TABLE `ots_option` ( `option_id` INT NOT NULL AUTO_INCREMENT, `et_option_id` INT NOT NULL DEFAULT '0', `type` VARCHAR(32) NOT NULL, `option_delivery_min` INT NOT NULL DEFAULT '0', `option_delivery_max` INT NOT NULL DEFAULT '0', `delivery` TINYINT NOT NULL DEFAULT '0', `extra` TINYINT NOT NULL DEFAULT '0', `sort_order` INT NOT NULL, PRIMARY KEY (`option_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB; DROP TABLE `ots_option_description`; CREATE TABLE `ots_option_description` ( `option_id` INT NOT NULL, `language_id` INT NOT NULL, `name` VARCHAR(128) NOT NULL, `short_text` TEXT NOT NULL, `description` TEXT NOT NULL, PRIMARY KEY (`option_id`, `language_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB; DROP TABLE `ots_option_value`; CREATE TABLE `ots_option_value` ( `option_value_id` INT NOT NULL AUTO_INCREMENT, `et_option_value_id` INT NOT NULL DEFAULT '0', `option_id` INT NOT NULL, `image` VARCHAR(255) NOT NULL, `class` VARCHAR(45) NOT NULL DEFAULT '', `option_value_price` VARCHAR(255) NULL DEFAULT NULL, `color_id` INT NULL DEFAULT NULL, `option_type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed', `option_value_startcost_price` VARCHAR(255) NULL DEFAULT NULL, `option_value_startcost_type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed', `option_value_startcost_quantity` INT NOT NULL, `option_value_startcost_quantity_display` TINYINT NOT NULL DEFAULT '1', `option_value_delivery_min` INT NOT NULL DEFAULT '0', `option_value_delivery_max` INT NOT NULL DEFAULT '0', `branding` TINYINT NOT NULL DEFAULT '0', `branding_group_id` INT NOT NULL DEFAULT '0', `design_type` ENUM('upload','proof','online') NULL DEFAULT NULL, `sort_order` INT NOT NULL, PRIMARY KEY (`option_value_id`), INDEX `option_id` (`option_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB; DROP TABLE `ots_option_value_description`; CREATE TABLE `ots_option_value_description` ( `option_value_id` INT NOT NULL, `language_id` INT NOT NULL, `option_id` INT NOT NULL, `delivery_service` VARCHAR(128) NOT NULL, `name` VARCHAR(128) NOT NULL, `video_id` VARCHAR(64) NOT NULL, `addition` VARCHAR(50) NOT NULL, `startcost` VARCHAR(255) NOT NULL, `text` TEXT NOT NULL, PRIMARY KEY (`option_value_id`, `language_id`), INDEX `option_id` (`option_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB; CREATE TABLE `ots_branding_group` ( `branding_group_id` INT NOT NULL AUTO_INCREMENT, `image` VARCHAR(255) NOT NULL DEFAULT '', `display` TINYINT NOT NULL DEFAULT '0', `status` TINYINT NOT NULL DEFAULT '1', `sort_order` INT NOT NULL DEFAULT '0', PRIMARY KEY (`branding_group_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB; CREATE TABLE `ots_branding_group_description` ( `branding_group_id` INT NOT NULL, `language_id` INT NOT NULL, `title` VARCHAR(64) NOT NULL, `description` TEXT NOT NULL, PRIMARY KEY (`branding_group_id`, `language_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB; ALTER TABLE ots_option AUTO_INCREMENT = 10000; ALTER TABLE ots_option_value AUTO_INCREMENT = 1000000; ALTER TABLE ots_branding_group AUTO_INCREMENT = 10000; --------------------------------------------------------- DROP TABLE `ots_product_attribute`; DROP TABLE `ots_product_filter`; DROP TABLE `ots_product_recurring`; DROP TABLE `ots_product_related`; DROP TABLE `ots_product_reward`; DROP TABLE `ots_product_special`; DROP TABLE `ots_product_to_download`; DROP TABLE `ots_product_to_layout`; DROP TABLE `ots_product_to_store`; DROP TABLE `ots_product`; CREATE TABLE `ots_product` ( `product_id` INT NOT NULL AUTO_INCREMENT, `et_product_id` INT NOT NULL, `model` VARCHAR(64) NOT NULL, `quantity` INT NOT NULL DEFAULT '0', `product_group_id` INT NOT NULL DEFAULT '0', `tax_class_id` INT NOT NULL, `date_available` DATE NULL DEFAULT NULL, `date_unavailable` DATE NULL DEFAULT NULL, `weight` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000', `weight_class_id` INT NOT NULL DEFAULT '0', `length` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000', `width` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000', `height` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000', `length_class_id` INT NOT NULL DEFAULT '0', `unit_class_id` INT NOT NULL DEFAULT '0', `unit_price` TINYINT NOT NULL DEFAULT '1', `price_label` ENUM('min','max') NOT NULL DEFAULT 'max', `delivery_min` INT NOT NULL DEFAULT '0', `delivery_max` INT NOT NULL DEFAULT '0', `declaration_cn_hs_code` VARCHAR(64) NOT NULL, `declaration_se_hs_code` VARCHAR(64) NOT NULL, `declaration_g_w_g` VARCHAR(64) NOT NULL, `declaration_w_v` VARCHAR(64) NOT NULL, `sort_order` INT NOT NULL DEFAULT '0', `status` TINYINT NOT NULL DEFAULT '0', `date_added` DATETIME NOT NULL, `date_modified` DATETIME NOT NULL, PRIMARY KEY (`product_id`), UNIQUE INDEX `product_id` (`product_id`), INDEX `model` (`model`), INDEX `sort_order` (`sort_order`), INDEX `status` (`status`), INDEX `date_available` (`date_available`), INDEX `date_unavailable` (`date_unavailable`) ) COLLATE='utf8mb4_general_ci' ENGINE=INNODB AUTO_INCREMENT=90000 ; DROP TABLE `ots_product_description`; CREATE TABLE `ots_product_description` ( `product_id` INT NOT NULL, `language_id` INT NOT NULL, `name` VARCHAR(255) NOT NULL, `short_name` VARCHAR(100) NOT NULL, `declaration_name` VARCHAR(255) NOT NULL, `description` TEXT NOT NULL, PRIMARY KEY (`product_id`, `language_id`), INDEX `name` (`name`), INDEX `product_id` (`product_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `ots_product_to_manufacturer` ( `product_id` INT NOT NULL, `manufacturer_id` INT NOT NULL, `default` TINYINT NOT NULL DEFAULT '0', `declaration_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', `minimum` INT NOT NULL DEFAULT '1', `quantity_start` INT NOT NULL DEFAULT '0', `price_calculation` TINYINT NOT NULL DEFAULT '0', `price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', `price_type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed', `campaign_price` VARCHAR(255) NOT NULL, `campaign_type` ENUM('fixed','percent') NULL DEFAULT 'percent', `campaign_end` DATE NULL DEFAULT NULL, `campaign_base` TINYINT NOT NULL DEFAULT '0', `price_min` DECIMAL(15,2) NOT NULL DEFAULT '0.00', `price_min_branding` DECIMAL(15,2) NOT NULL DEFAULT '0.00', `formula` VARCHAR(255) NOT NULL DEFAULT '', `depth_enable` TINYINT NOT NULL DEFAULT '0', `depth_max` INT NOT NULL DEFAULT '0', `depth_min` INT NOT NULL DEFAULT '0', `height_enable` TINYINT NOT NULL DEFAULT '0', `height_max` INT NOT NULL DEFAULT '0', `width_enable` TINYINT NOT NULL DEFAULT '0', `height_min` INT NOT NULL DEFAULT '0', `width_max` INT NOT NULL DEFAULT '0', `width_min` INT NOT NULL DEFAULT '0', `length_enable` TINYINT NOT NULL DEFAULT '0', `length_min` INT NOT NULL DEFAULT '0', `length_max` INT NOT NULL DEFAULT '0', `startcost_enabled` TINYINT NOT NULL DEFAULT '0', `startcost_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', `startcost_type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed', `startcost_quantity` INT NOT NULL DEFAULT '0', `startcost_dependant_option_enabled` TINYINT NOT NULL DEFAULT '0', `startcost_dependant_option_id` INT NOT NULL DEFAULT '0', `startcost_dependant_option_value_id` INT NOT NULL DEFAULT '0', PRIMARY KEY (`product_id`, `manufacturer_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `ots_product_to_manufacturer_description` ( `product_id` INT NOT NULL, `manufacturer_id` INT NOT NULL, `language_id` INT NOT NULL, `startcost` VARCHAR(255) NOT NULL, PRIMARY KEY (`product_id`, `manufacturer_id`, `language_id`), INDEX `product_id` (`product_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `ots_product_group` ( `product_group_id` INT NOT NULL AUTO_INCREMENT, `prefix` VARCHAR(20) NOT NULL, `sort_order` INT NOT NULL DEFAULT '0', PRIMARY KEY (`product_group_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=INNODB AUTO_INCREMENT=1000 ; CREATE TABLE `ots_product_group_description` ( `product_group_id` INT NOT NULL, `language_id` INT NOT NULL, `name` VARCHAR(64) NOT NULL, PRIMARY KEY (`product_group_id`, `language_id`) USING BTREE ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; DROP TABLE `ots_product_option`; CREATE TABLE `ots_product_option` ( `product_option_id` INT NOT NULL AUTO_INCREMENT, `product_id` INT NOT NULL, `manufacturer_id` INT NOT NULL, `option_id` INT NOT NULL, `value` TEXT NOT NULL, `required` TINYINT NOT NULL, `status` TINYINT NOT NULL DEFAULT '1', `hidden` TINYINT NOT NULL DEFAULT '0', `display` TINYINT NOT NULL DEFAULT '1', `connect` TINYINT NOT NULL DEFAULT '0', `color_type` TINYINT NOT NULL DEFAULT '0', `color_display` TINYINT NOT NULL DEFAULT '0', `color_preview` TINYINT NOT NULL DEFAULT '1', `surface_id` TINYINT NOT NULL DEFAULT '0', `product_image_id` INT NOT NULL DEFAULT '0', `sort_order` INT NOT NULL DEFAULT '0', PRIMARY KEY (`product_option_id`), INDEX `option_id` (`option_id`), INDEX `product_id` (`product_id`), INDEX `manufacturer_id` (`manufacturer_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=INNODB AUTO_INCREMENT=1000000 ; CREATE TABLE `ots_product_option_description` ( `product_option_description_id` INT NOT NULL AUTO_INCREMENT, `product_option_id` INT NOT NULL, `product_id` INT NOT NULL, `language_id` INT NOT NULL, `name` VARCHAR(100) NOT NULL DEFAULT '', `description` VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (`product_option_description_id`), INDEX `product_id` (`product_id`), INDEX `language_id` (`language_id`), INDEX `product_option_id` (`product_option_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=INNODB AUTO_INCREMENT=10000000 ; DROP TABLE `ots_product_option_value`; CREATE TABLE `ots_product_option_value` ( `product_option_value_id` INT NOT NULL AUTO_INCREMENT, `product_option_id` INT NOT NULL, `product_id` INT NOT NULL, `manufacturer_id` INT NOT NULL, `option_id` INT NOT NULL, `option_value_id` INT NOT NULL, `quantity` INT NOT NULL, `subtract` TINYINT NOT NULL, `price` VARCHAR(255) NOT NULL DEFAULT '0.0000', `type` ENUM('fixed','percent','formula') NOT NULL, `price_prefix` VARCHAR(1) NOT NULL, `points` INT NOT NULL, `points_prefix` VARCHAR(1) NOT NULL, `minimum` INT NOT NULL DEFAULT '1', `weight` DECIMAL(15,8) NOT NULL, `weight_prefix` VARCHAR(1) NOT NULL, `default` TINYINT NOT NULL DEFAULT '0', `extra_option_enabled` TINYINT NOT NULL, `dependant_option_enabled` TINYINT NOT NULL DEFAULT '0', `dependant_option_id` INT NOT NULL DEFAULT '0', `dependant_option_value_id` INT NOT NULL DEFAULT '0', `product_price_enabled` TINYINT NOT NULL, `delivery_enabled` TINYINT NOT NULL, `delivery_max` INT NOT NULL, `startcost_enabled` TINYINT NOT NULL, `startcost_price` VARCHAR(255) NOT NULL DEFAULT '0.0000', `startcost_type` ENUM('fixed','percent','formula') NOT NULL, `startcost_quantity` INT NOT NULL, `startcost_dependant_option_enabled` TINYINT NOT NULL DEFAULT '0', `startcost_dependant_option_id` INT NOT NULL DEFAULT '0', `startcost_dependant_option_value_id` INT NOT NULL DEFAULT '0', `startcost_product_price_enabled` TINYINT NOT NULL DEFAULT '0', `info_enabled` TINYINT NOT NULL, `image_layer` VARCHAR(255) NOT NULL DEFAULT '', `image_layer_color_id` INT NOT NULL DEFAULT '0', `image_z_index` TINYINT NOT NULL DEFAULT '0', `image_hover` VARCHAR(255) NOT NULL DEFAULT '', `image_hover_description` VARCHAR(255) NOT NULL DEFAULT '', `product_effect_id` INT NULL DEFAULT NULL, `logoarea_enabled` TINYINT NOT NULL, `surface_id` TINYINT NOT NULL DEFAULT '0', `branding_id` TINYINT NOT NULL DEFAULT '0', `logotype_id` INT NOT NULL DEFAULT '0', `status` TINYINT NOT NULL DEFAULT '1', `default_pantone_color_id` INT NOT NULL DEFAULT '0', `sort_order` INT NOT NULL DEFAULT '0', PRIMARY KEY (`product_option_value_id`), INDEX `product_option_id` (`product_option_id`), INDEX `product_id` (`product_id`), INDEX `manufacturer_id` (`manufacturer_id`), INDEX `option_id` (`option_id`), INDEX `option_value_id` (`option_value_id`), INDEX `sort_order` (`sort_order`) ) COLLATE='utf8mb4_general_ci' ENGINE=INNODB AUTO_INCREMENT=1000000 ; CREATE TABLE `ots_product_option_value_description` ( `product_option_value_description_id` INT NOT NULL AUTO_INCREMENT, `product_option_value_id` INT NOT NULL, `product_id` INT NOT NULL, `language_id` INT NOT NULL, `startcost` VARCHAR(255) NOT NULL DEFAULT '', `custom_option_title` VARCHAR(128) NULL DEFAULT '', PRIMARY KEY (`product_option_value_description_id`), INDEX `product_id` (`product_id`), INDEX `language_id` (`language_id`), INDEX `product_option_value_id` (`product_option_value_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=INNODB AUTO_INCREMENT=10000000 ; DROP TABLE `ots_product_discount`; CREATE TABLE `ots_product_discount` ( `product_discount_id` INT NOT NULL AUTO_INCREMENT, `product_id` INT NOT NULL, `manufacturer_id` INT NOT NULL, `quantity` INT NOT NULL DEFAULT '0', `quantity_type` ENUM('normal','extra') NOT NULL DEFAULT 'normal', `priority` INT NOT NULL DEFAULT '1', `type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed', `position` INT NOT NULL DEFAULT '0', `price` VARCHAR(255) NOT NULL DEFAULT '0.0000', `price_base` TINYINT NOT NULL DEFAULT '0', `date_start` DATE NULL DEFAULT NULL, `date_end` DATE NULL DEFAULT NULL, `discount_price` VARCHAR(255) NOT NULL, `discount_type` ENUM('fixed','percent') NOT NULL DEFAULT 'fixed', PRIMARY KEY (`product_discount_id`), INDEX `product_id` (`product_id`), INDEX `manufacturer_id` (`manufacturer_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=INNODB AUTO_INCREMENT=10000000 ; CREATE TABLE `ots_product_option_value_discount` ( `product_option_value_discount_id` INT NOT NULL AUTO_INCREMENT, `product_option_value_id` INT NOT NULL, `quantity_type` ENUM('normal','extra') NOT NULL DEFAULT 'normal', `price` VARCHAR(255) NOT NULL DEFAULT '0.0000', `price_base` TINYINT NOT NULL DEFAULT '0', `type` ENUM('fixed','percent','formula') NOT NULL, `position` INT NOT NULL DEFAULT '0', PRIMARY KEY (`product_option_value_discount_id`), INDEX `product_option_value_id` (`product_option_value_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=INNODB AUTO_INCREMENT=100000000 ; CREATE TABLE `ots_product_extra_option` ( `product_extra_option_id` INT NOT NULL AUTO_INCREMENT, `product_option_value_id` INT NOT NULL, `product_id` INT NOT NULL, `manufacturer_id` INT NOT NULL, `option_id` INT NOT NULL, `required` TINYINT NOT NULL DEFAULT '0', `color_type` TINYINT NOT NULL DEFAULT '0', `color_display` TINYINT NOT NULL DEFAULT '0', PRIMARY KEY (`product_extra_option_id`), INDEX `product_option_value_id` (`product_option_value_id`), INDEX `option_id` (`option_id`), INDEX `product_id` (`product_id`), INDEX `manufacturer_id` (`manufacturer_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=INNODB AUTO_INCREMENT=1000000 ; CREATE TABLE `ots_product_extra_option_value` ( `product_extra_option_value_id` INT NOT NULL AUTO_INCREMENT, `product_extra_option_id` INT NOT NULL, `option_value_id` INT NOT NULL, `product_id` INT NOT NULL, `manufacturer_id` INT NOT NULL, `option_id` INT NOT NULL, `default` TINYINT NOT NULL DEFAULT '0', `price` VARCHAR(255) NOT NULL DEFAULT '0.00', `type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed', `default_pantone_color_id` INT NOT NULL DEFAULT '0', `sort_order` INT NOT NULL DEFAULT '0', PRIMARY KEY (`product_extra_option_value_id`), INDEX `product_extra_option_id` (`product_extra_option_id`), INDEX `option_value_id` (`option_value_id`), INDEX `sort_order` (`sort_order`), INDEX `manufacturer_id` (`manufacturer_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=INNODB AUTO_INCREMENT=10000000 ; DROP TABLE `ots_product_to_category`; CREATE TABLE `ots_product_to_category` ( `product_id` INT NOT NULL, `category_id` INT NOT NULL, `sort_order` INT NOT NULL DEFAULT '0', PRIMARY KEY (`product_id`, `category_id`), INDEX `category_id` (`category_id`), INDEX `product_id` (`product_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; DROP TABLE `ots_product_image`; CREATE TABLE `ots_product_image` ( `product_image_id` INT NOT NULL AUTO_INCREMENT, `default` TINYINT NOT NULL DEFAULT '0', `default_category` TINYINT NOT NULL DEFAULT '0', `default_category_hover` TINYINT NOT NULL DEFAULT '0', `product_id` INT NOT NULL, `image` VARCHAR(255) NULL DEFAULT NULL, `zoom` FLOAT NOT NULL DEFAULT '1.00', `layer_image` VARCHAR(255) NULL DEFAULT NULL, `layer_z_index` TINYINT NOT NULL DEFAULT '0', `displacement_image` VARCHAR(255) NULL DEFAULT NULL, `transform` TEXT NULL DEFAULT NULL, `color_id` INT NULL DEFAULT NULL, `type` TINYINT NOT NULL DEFAULT '0', `sort_order` INT NOT NULL DEFAULT '0', PRIMARY KEY (`product_image_id`), INDEX `product_id` (`product_id`), INDEX `color_id` (`color_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1000000 ; CREATE TABLE `ots_product_image_description` ( `image_description_id` INT NOT NULL AUTO_INCREMENT, `product_image_id` INT NOT NULL, `product_id` INT NOT NULL, `language_id` INT NOT NULL, `title_text` VARCHAR(255) NULL DEFAULT NULL, `alt_text` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`image_description_id`), INDEX `product_id` (`product_id`), INDEX `product_image_id` (`product_image_id`), INDEX `language_id` (`language_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=10000000 ; CREATE TABLE `ots_product_option_value_hover_description` ( `product_option_value_hover_description_id` INT NOT NULL AUTO_INCREMENT, `product_option_value_id` INT NOT NULL, `product_id` INT NOT NULL, `language_id` INT NOT NULL, `description` TEXT NOT NULL, PRIMARY KEY (`product_option_value_hover_description_id`), INDEX `product_id` (`product_id`), INDEX `language_id` (`language_id`), INDEX `product_option_value_id` (`product_option_value_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1000000 ; CREATE TABLE `ots_product_option_value_layer_image` ( `product_option_value_layer_image_id` INT NOT NULL AUTO_INCREMENT, `product_id` INT NOT NULL, `product_option_value_id` INT NOT NULL, `product_image_id` INT NOT NULL DEFAULT '0', `image_layer` VARCHAR(255) NOT NULL DEFAULT '', `image_layer_color_id` INT NOT NULL DEFAULT '0', `image_z_index` TINYINT NOT NULL DEFAULT '0', PRIMARY KEY (`product_option_value_layer_image_id`), INDEX `product_option_value_id` (`product_option_value_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=10000000 ; CREATE TABLE `ots_product_square_meter_discount` ( `product_square_meter_discount_id` INT NOT NULL AUTO_INCREMENT, `product_id` INT NOT NULL, `manufacturer_id` INT NOT NULL, `quantity` DECIMAL(15,2) NOT NULL DEFAULT '0.00', `type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed', `price` VARCHAR(255) NOT NULL DEFAULT '0.0000', `position` INT NOT NULL DEFAULT '0', PRIMARY KEY (`product_square_meter_discount_id`), INDEX `product_id` (`product_id`), INDEX `manufacturer_id` (`manufacturer_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1000000 ; CREATE TABLE `ots_currency_round` ( `currency_round_id` INT NOT NULL AUTO_INCREMENT, `currency_id` INT NOT NULL, `value` FLOAT NOT NULL, `precision` TINYINT NOT NULL, PRIMARY KEY (`currency_round_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; -------------------------------------- ALTER TABLE `ots_product` DROP COLUMN `price_label`; ALTER TABLE `ots_manufacturer` ADD COLUMN `origin` VARCHAR(64) NOT NULL AFTER `name`; ALTER TABLE `ots_product` ADD COLUMN `declaration_base_info` VARCHAR(255) NOT NULL AFTER `declaration_w_v`, -- 申报要素的基本信息 ADD COLUMN `declaration_option` TINYINT NOT NULL DEFAULT (0) AFTER `declaration_base_info`, -- 申报要素中是否含有尺寸/容量的option ADD COLUMN `declaration_brand` TINYINT NOT NULL DEFAULT (0) AFTER `declaration_option`; -- 申报要素中是否包含品牌 ----------------------------- CREATE TABLE `ots_color` ( `color_id` INT NOT NULL AUTO_INCREMENT, `hex` VARCHAR(7) NOT NULL, `pantone` VARCHAR(64) NOT NULL, `status` TINYINT NOT NULL DEFAULT '0', `color_group_id` INT NULL DEFAULT NULL, `sort_order` INT NOT NULL DEFAULT '0', PRIMARY KEY (`color_id`), INDEX `color_group_id` (`color_group_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=10000 ; CREATE TABLE `ots_color_group` ( `color_group_id` INT NOT NULL AUTO_INCREMENT, `hex` VARCHAR(7) NOT NULL, `status` TINYINT NOT NULL DEFAULT '0', PRIMARY KEY (`color_group_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1000 ; CREATE TABLE `ots_color_group_description` ( `color_group_id` INT NOT NULL, `language_id` INT NOT NULL, `name` VARCHAR(64) NOT NULL, PRIMARY KEY (`color_group_id`, `language_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; ALTER TABLE `ots_option` ADD COLUMN `color_change` TINYINT NOT NULL DEFAULT '0' AFTER `type`; ----------------------------------------------- ALTER TABLE `ots_product` CHANGE COLUMN `declaration_brand` `declaration_brand` TINYINT NOT NULL DEFAULT '1' AFTER `declaration_option`; ALTER TABLE `ots_product` CHANGE COLUMN `declaration_base_info` `declaration_base_info` TEXT NOT NULL AFTER `declaration_w_v`; UPDATE ots_product SET declaration_brand = 1; ------------------------------------------------ ALTER TABLE `ots_product` ADD COLUMN `declaration_product_unit` VARCHAR(32) NOT NULL AFTER `declaration_w_v`, ADD COLUMN `declaration_required` TINYINT NOT NULL DEFAULT '1' AFTER `declaration_brand`; ------------------------------------------------- ALTER TABLE `ots_product_option_value` ADD COLUMN `extra_manufacturer_enabled` TINYINT NOT NULL AFTER `logotype_id`, ADD COLUMN `extra_manufacturer_id` INT NOT NULL DEFAULT '0' AFTER `extra_manufacturer_enabled`; </pre> | | #464 | <pre> </pre> | * *Record all SQL changes (v3)* |. Issue |. SQL | | #441 | <pre>CREATE DROP TABLE `t_user_order_product` `ots_customer_affiliate`; DROP TABLE `ots_customer_approval`; DROP TABLE `ots_customer_ip`; DROP TABLE `ots_customer_online`; DROP TABLE `ots_customer_reward`; DROP TABLE `ots_customer_transaction`; DROP TABLE `ots_customer_wishlist`; DROP TABLE `ots_customer`; CREATE TABLE `ots_customer` ( `id` `customer_id` INT NOT NULL AUTO_INCREMENT, `user_id` `customer_company_id` INT NOT NULL, `order_product_id` `name` VARCHAR(100) NOT NULL, `abbreviation_name` VARCHAR(32) NOT NULL, `email` VARCHAR(96) NOT NULL, `telephone` VARCHAR(32) NOT NULL, `password` VARCHAR(40) NOT NULL, `salt` VARCHAR(9) NOT NULL, `se_info` TINYINT NOT NULL DEFAULT '0', `moq_check` TINYINT NOT NULL DEFAULT '0', `accessible_companies` TEXT NOT NULL, `accessible_products` TEXT NOT NULL, `status` TINYINT NOT NULL DEFAULT '0', `is_delete` TINYINT NOT NULL DEFAULT '0', `date_added` DATETIME NOT NULL, `date_modified` DATETIME NOT NULL, PRIMARY KEY (`customer_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `ots_customer_company` ( `customer_company_id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, `create_time` datetime `abbreviation_name` VARCHAR(32) NOT NULL, `order_prefix_code` VARCHAR(4) NOT NULL, `manufacturer_code` VARCHAR(32) NOT NULL, `manager` VARCHAR(100) NOT NULL, `email` VARCHAR(96) NOT NULL, `telephone` VARCHAR(32) NOT NULL, `vat` VARCHAR(20) NOT NULL, `shipping_address_id` INT NOT NULL, `invoice_address_id` INT NOT NULL, `status` TINYINT NOT NULL DEFAULT '0', `is_delete` TINYINT NOT NULL DEFAULT '0', `date_added` DATETIME NOT NULL, `date_modified` DATETIME NOT NULL, PRIMARY KEY (`id`), (`customer_company_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `ots_customer_company_address` ( `customer_company_address_id` INT NOT NULL AUTO_INCREMENT, `customer_company_id` INT NOT NULL, `recipient_name` VARCHAR(32) NOT NULL, `recipient_telephone` VARCHAR(32) NOT NULL, `bank_name` VARCHAR(128) NOT NULL, `bank_account` VARCHAR(32) NOT NULL, `country_id` INT NOT NULL DEFAULT '0', `city` VARCHAR(128) NOT NULL, `postcode` VARCHAR(10) NOT NULL, `address_1` VARCHAR(128) NOT NULL, `address_2` VARCHAR(128) NOT NULL, `default` TINYINT NOT NULL DEFAULT '0', `shipping_address_id` TINYINT NOT NULL DEFAULT '0', `invoice_address_id` TINYINT NOT NULL DEFAULT '0', PRIMARY KEY `user_id` (`user_id`), (`customer_company_address_id`), INDEX `customer_company_id` (`customer_company_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `ots_customer_company_to_role_group` ( `customer_company_id` INT NOT NULL AUTO_INCREMENT, `customer_role_group_id` INT NOT NULL, PRIMARY KEY (`customer_company_id`, `customer_role_group_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `ots_customer_to_role` ( `customer_id` INT NOT NULL AUTO_INCREMENT, `customer_role_id` INT NOT NULL, PRIMARY KEY (`customer_id`, `customer_role_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `ots_customer_role_group` ( `customer_role_group_id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(32) NOT NULL, PRIMARY KEY (`customer_role_group_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `ots_customer_role` ( `customer_role_id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(32) NOT NULL, PRIMARY KEY (`customer_role_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `ots_customer_favorite` ( `customer_id` INT NOT NULL, `order_product_id` (`order_product_id`) INT NOT NULL, `date_added` DATETIME NOT NULL, PRIMARY KEY (`customer_id`, `order_product_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1 ; DROP TABLE `ots_customer_search`; CREATE TABLE `ots_customer_filter` ( `customer_id` INT NOT NULL, `filter_name` VARCHAR(100) NOT NULL, `filter_data` TEXT NOT NULL, `default` TINYINT NOT NULL DEFAULT CHARSET=utf8;</pre> '0', `is_delete` TINYINT NOT NULL DEFAULT '0', `date_added` DATETIME NOT NULL, `date_modified` DATETIME NOT NULL, PRIMARY KEY (`customer_id`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ;</pre> | | #441 | <pre>ALTER TABLE `t_user_search` ADD COLUMN `is_default` TINYINT NOT NULL DEFAULT '0' AFTER `search_data`;</pre> | *v4 coding standard:* # Git提交 #xxx(redmine number): xxx(redmine title) - xxx(任意：补充内容) # 代码规范 2.1: 结构：Public -> Protected -> Private 2.2: 其他代码规范同SBX # MVCL的v3/v4物理分割 3.1: v3/v4的代码分为两个文件，比如： order.php, order_v3.php 3.2: system也分为两个文件，暂时只有一个user_v3 - Note: front的$this->user在v4中会替换为$this->customer, 即v4中需要清除$this->user 3.3: 代码中，区分v3/v4的调用，比如：new User_V3 # 前台代码 twig, js, css (js, css尽量写在单独的css文件中) # 后台代码 MVCL (L：en，cn) # 注释 ## 类名： <pre> /** * xxx * * @copyright RedBlue-OTS 2024 * @version v4 * */ </pre> ## 函数名：参数类型 + 返回值类型 <pre> /** * xxx * * @param xxx $xxx * @param xxx $xxx * @return xxx */ </pre> *Output* * Report and Solution *Others*