其他 #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>
DROP TABLE `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_activity`;
DROP TABLE `ots_customer`;
CREATE TABLE `ots_customer` (
`customer_id` INT NOT NULL AUTO_INCREMENT,
`customer_company_id` INT NOT NULL,
`firstname` VARCHAR(32) NOT NULL,
`lastname` VARCHAR(32) NOT NULL,
`abbreviation_name` VARCHAR(32) NOT NULL,
`email` VARCHAR(96) NOT NULL,
`telephone` VARCHAR(32) NOT NULL,
`username` VARCHAR(32) NOT NULL,
`password` VARCHAR(40) NOT NULL,
`salt` VARCHAR(9) NOT NULL,
`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,
`abbreviation_name` VARCHAR(32) NOT NULL,
`order_prefix_code` VARCHAR(4) NOT NULL,
`manufacturer_code` VARCHAR(32) NOT NULL,
`manufacturer_origin` VARCHAR(64) NOT NULL,
`manufacturer_rank` VARCHAR(64) NOT NULL,
`manager_firstname` VARCHAR(32) NOT NULL,
`manager_lastname` VARCHAR(32) NOT NULL,
`email` VARCHAR(96) NOT NULL,
`telephone` VARCHAR(32) NOT NULL,
`vat` VARCHAR(20) NOT NULL,
`bank_name` VARCHAR(128) NOT NULL,
`bank_account` VARCHAR(32) NOT NULL,
`shipping_address_id` INT NOT NULL,
`invoice_address_id` INT NOT NULL,
`se_info` TINYINT NOT NULL DEFAULT '0',
`moq_check` TINYINT NOT NULL DEFAULT '0',
`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_company_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_customer_accessible_company` (
`customer_id` INT NOT NULL,
`customer_company_id` INT NOT NULL,
PRIMARY KEY (`customer_id`, `customer_company_id`),
INDEX `customer_id` (`customer_id`),
INDEX `customer_company_id` (`customer_company_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_customer_accessible_product` (
`customer_id` INT NOT NULL,
`product_id` INT NOT NULL,
PRIMARY KEY (`customer_id`, `product_id`),
INDEX `customer_id` (`customer_id`),
INDEX `product_id` (`product_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,
`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` TINYINT NOT NULL DEFAULT '0',
`invoice_address` TINYINT NOT NULL DEFAULT '0',
PRIMARY KEY (`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,
`customer_role_group_id` INT NOT NULL,
`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` INT NOT NULL,
`date_added` DATETIME NOT NULL,
PRIMARY KEY (`customer_id`, `order_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
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 '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> |
* *Record all SQL changes (v3)*
|. Issue |. SQL |
| #441 | <pre>CREATE TABLE `t_user_order_product` (
`id` INT NOT NULL AUTO_INCREMENT,
`user_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `order_product_id` (`order_product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;</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*