其他 #440
Updated by Junyi Zhang 7 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`;
</pre> |
| #464 | <pre>
-- Step 1
DROP TABLE IF EXISTS `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(10) NOT NULL,
`email` VARCHAR(96) NOT NULL,
`telephone` VARCHAR(32) NOT NULL,
`username` VARCHAR(32) NOT NULL,
`password` VARCHAR(60) 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
;
DROP TABLE IF EXISTS `ots_customer_company`;
CREATE TABLE `ots_customer_company` (
`customer_company_id` INT NOT NULL AUTO_INCREMENT,
`create_customer_id` INT NOT NULL,
`name` VARCHAR(100) NOT NULL,
`abbreviation_name` VARCHAR(10) 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,
`manager_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,
`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
;
DROP TABLE IF EXISTS `ots_product_to_customer`;
CREATE TABLE `ots_product_to_customer` (
`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
;
DROP TABLE IF EXISTS `ots_address`;
DROP TABLE IF EXISTS `ots_customer_company_address`;
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_email` VARCHAR(96) NOT NULL,
`recipient_telephone` VARCHAR(32) NOT NULL,
`country_id` INT NOT NULL DEFAULT '0',
`zone_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
;
DROP TABLE IF EXISTS `ots_customer_company_to_role_group`;
CREATE TABLE `ots_customer_company_to_role_group` (
`customer_company_id` INT NOT NULL,
`customer_company_role_group_id` INT NOT NULL,
PRIMARY KEY (`customer_company_id`, `customer_company_role_group_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_customer_company_role`;
CREATE TABLE `ots_customer_company_role` (
`customer_id` INT NOT NULL,
`customer_company_id` INT NOT NULL,
`customer_role_id` INT NOT NULL,
PRIMARY KEY (`customer_id`, `customer_company_id`, `customer_role_id`),
INDEX `customer_company_id` (`customer_company_id`),
INDEX `customer_role_id` (`customer_role_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_customer_company_role_group`;
CREATE TABLE `ots_customer_company_role_group` (
`customer_company_role_group_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
PRIMARY KEY (`customer_company_role_group_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
TRUNCATE TABLE `ots_customer_company_role_group`;
INSERT INTO `ots_customer_company_role_group` (`customer_company_role_group_id`, `name`) VALUES
(1, 'Administrators'),
(2, 'Retailers'),
(3, 'Distributors'),
(4, 'Manufacturers');
DROP TABLE IF EXISTS `ots_customer_role`;
CREATE TABLE `ots_customer_role` (
`customer_role_id` INT NOT NULL AUTO_INCREMENT,
`customer_company_role_group_id` INT NOT NULL,
`name` VARCHAR(32) NOT NULL,
PRIMARY KEY (`customer_role_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
TRUNCATE TABLE `ots_customer_role`;
INSERT INTO `ots_customer_role` (`customer_role_id`, `customer_company_role_group_id`, `name`) VALUES
(1, 1, 'Admin'),
(2, 2, 'Manager'),
(3, 2, 'Sales'),
(4, 2, 'Logistics'),
(5, 2, 'Guest'),
(6, 3, 'Manager'),
(7, 3, 'Operator'),
(8, 3, 'Logistics'),
(9, 4, 'Manager'),
(10, 4, 'Producer');
DROP TABLE IF EXISTS `ots_customer_favorite`;
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 IF EXISTS `ots_customer_search`;
DROP TABLE IF EXISTS `ots_customer_filter`;
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
;
-- Step 2
TRUNCATE TABLE `ots_customer`;
INSERT INTO `ots_customer` (`customer_id`, `customer_company_id`, `firstname`, `lastname`, `abbreviation_name`, `email`, `telephone`, `username`, `password`, `status`, `is_delete`, `date_added`, `date_modified`) VALUES
(1, 1, 'admin', 'admin', 'admin', 'info@redbluetrading.com', '', 'admin', '$2y$10$zm8clFn3EqbzKUt4lnHbLuWiL48WtBO.Q/SJCRWATiSVr2G39OQn.', 1, 0, '2025-03-19 16:21:02', '2025-03-26 12:24:52');
TRUNCATE TABLE `ots_customer_company`;
INSERT INTO `ots_customer_company` (`customer_company_id`, `create_customer_id`, `name`, `abbreviation_name`, `manufacturer_origin`, `manufacturer_rank`, `manager_firstname`, `manager_lastname`, `manager_email`, `telephone`, `vat`, `bank_name`, `bank_account`, `se_info`, `moq_check`, `status`, `is_delete`, `date_added`, `date_modified`) VALUES
(1, 1, 'Redblue', 'RBSH', '', '', 'Daping', 'Huang', 'daping@redbluetrading.com', '', '', '', '', 1, 0, 1, 0, '2025-03-25 14:58:09', '2025-03-26 12:26:45');
TRUNCATE TABLE `ots_customer_company_address`;
INSERT INTO `ots_customer_company_address` (`customer_company_address_id`, `customer_company_id`, `recipient_name`, `recipient_email`, `recipient_telephone`, `country_id`, `zone_id`, `city`, `postcode`, `address_1`, `address_2`, `default`, `shipping_address`, `invoice_address`) VALUES
(1, 1, 'Daping', 'daping@redbluetrading.com', 'Huang', 44, 708, '上海', '201615', '4th Floor, Building F', 'No.858 Jiujing Road', 1, 0, 1),
(2, 1, 'Daping', 'daping@redbluetrading.com', 'Huang', 44, 708, '上海', '201615', '4th Floor, Building F', 'No.858 Jiujing Road', 1, 1, 0);
TRUNCATE TABLE `ots_customer_company_role`;
INSERT INTO `ots_customer_company_role` (`customer_id`, `customer_company_id`, `customer_role_id`) VALUES
(1, 1, 1);
TRUNCATE TABLE `ots_customer_company_to_role_group`;
INSERT INTO `ots_customer_company_to_role_group` (`customer_company_id`, `customer_company_role_group_id`) VALUES
(1, 1),
(1, 2),
(1, 3);
TRUNCATE TABLE `ots_product_to_customer`;
-- Step 3:
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_custom_field`;
DROP TABLE `ots_custom_field_customer_group`;
DROP TABLE `ots_custom_field_description`;
DROP TABLE `ots_custom_field_value`;
DROP TABLE `ots_custom_field_value_description`;
--------------------------------------------
ALTER TABLE `ots_customer_history`
ADD COLUMN `action` VARCHAR(100) NOT NULL AFTER `customer_id`;
</pre> |
| #463 | <pre>
-- Step 1
-- Whether the product option value needs to extra manufacturer and whether it needs to be manually reviewed
ALTER TABLE `ots_product_option_value`
ADD COLUMN `extra_manufacturer_enabled` TINYINT NOT NULL DEFAULT '0' AFTER `logotype_id`,
ADD COLUMN `extra_manufacturer_id` INT NOT NULL DEFAULT '0' AFTER `extra_manufacturer_enabled`,
ADD COLUMN `manual_review` TINYINT NOT NULL DEFAULT '0' AFTER `extra_manufacturer_id`;
-- Step 2
-- Remove manufacturer
DROP TABLE `ots_manufacturer`;
-- Step 3
ALTER TABLE `ots_product_to_manufacturer`
ADD COLUMN `manual_review` TINYINT NOT NULL DEFAULT '0' AFTER `startcost_dependant_option_value_id`;
-- Step 4
ALTER TABLE `ots_product`
ADD COLUMN `is_stock` TINYINT NOT NULL DEFAULT '0' AFTER `status`;
ALTER TABLE `ots_product_to_manufacturer`
ADD COLUMN `stock_enabled` TINYINT NOT NULL DEFAULT '0' AFTER `manual_review`,
ADD COLUMN `stock_product_id` INT NOT NULL DEFAULT '0' AFTER `stock_enabled`;
ALTER TABLE `ots_product_option_value`
ADD COLUMN `stock_enabled` TINYINT NOT NULL DEFAULT '0' AFTER `manual_review`,
ADD COLUMN `stock_product_id` INT NOT NULL DEFAULT '0' AFTER `stock_enabled`;
-- Step 5
ALTER TABLE `ots_product_to_manufacturer`
ADD COLUMN `stock_multiplier` `stock_multiply` INT NOT NULL DEFAULT '1' AFTER `stock_product_id`;
ALTER TABLE `ots_product_option_value`
ADD COLUMN `stock_multiplier` `stock_multiply` INT NOT NULL DEFAULT '1' AFTER `stock_product_id`;
</pre> |
| #455 | <pre>
-- Step 1
DROP TABLE IF EXISTS `ots_order`;
CREATE TABLE `ots_order` (
`order_id` INT NOT NULL AUTO_INCREMENT,
`order_name` VARCHAR(255) NOT NULL,
`urgency` TINYINT NOT NULL DEFAULT '0',
`remake` TINYINT NOT NULL DEFAULT '0',
`retailer_company_id` INT NOT NULL DEFAULT '0',
`retailer_abbreviation_name` VARCHAR(10) NOT NULL,
`retailer_shipping_time` DATE NULL DEFAULT NULL,
`retailer_shipping_address_id` INT NOT NULL,
`retailer_shipping_country_id` INT NOT NULL,
`retailer_shipping_zone_id` INT NOT NULL,
`retailer_shipping_city` VARCHAR(128) NOT NULL,
`retailer_shipping_postcode` VARCHAR(10) NOT NULL,
`retailer_shipping_address_1` VARCHAR(128) NOT NULL,
`retailer_shipping_address_2` VARCHAR(128) NOT NULL,
`retailer_shipping_recipient_name` VARCHAR(32) NOT NULL,
`retailer_shipping_recipient_email` VARCHAR(96) NOT NULL,
`retailer_shipping_recipient_telephone` VARCHAR(32) NOT NULL,
`retailer_shipping_recipient_comment` TEXT NOT NULL,
`customer_shipping_time` DATE NULL DEFAULT NULL,
`customer_shipping_service` VARCHAR(32) NOT NULL,
`customer_shipping_country_id` INT NOT NULL,
`customer_shipping_zone_id` INT NOT NULL,
`customer_shipping_city` VARCHAR(128) NOT NULL,
`customer_shipping_postcode` VARCHAR(10) NOT NULL,
`customer_shipping_address_1` VARCHAR(128) NOT NULL,
`customer_shipping_address_2` VARCHAR(128) NOT NULL,
`customer_shipping_recipient_name` VARCHAR(32) NOT NULL,
`customer_shipping_recipient_email` VARCHAR(96) NOT NULL,
`customer_shipping_recipient_telephone` VARCHAR(32) NOT NULL,
`customer_shipping_recipient_comment` TEXT NOT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_order_product`;
CREATE TABLE `ots_order_product` (
`order_product_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`product_id` INT NOT NULL,
`model` VARCHAR(64) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`manufacturer_id` INT NOT NULL,
`manufacturer_name` VARCHAR(255) NOT NULL,
`manufacturer_origin` VARCHAR(255) NOT NULL,
`extra_info` TEXT NOT NULL,
`quantity` INT NOT NULL DEFAULT '0',
`unit_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', -- 产品单价（不含option，税前价格）
`total` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', -- 单价X数量（税前价格）
`weight` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`weight_class_id` INT NOT NULL DEFAULT '0',
`tax_class_id` INT NOT NULL,
`status` INT NOT NULL DEFAULT '0',
`expected_finishing_time` DATE NULL DEFAULT NULL,
`retailer_shipper` VARCHAR(20) NOT NULL,
`retailer_shipper_code` VARCHAR(200) NULL DEFAULT NULL,
`customer_shipper` VARCHAR(20) NOT NULL,
`customer_shipper_code` VARCHAR(200) NULL DEFAULT NULL,
`brand` VARCHAR(64) NOT NULL, -- if declaration_brand = 1
`declaration_unit_price` VARCHAR(200) NULL DEFAULT NULL,
`declaration_g_w_g` VARCHAR(64) NULL DEFAULT NULL,
`declaration_w_v` VARCHAR(64) NULL DEFAULT NULL,
`declaration_product_unit` VARCHAR(32) NOT NULL,
`declaration_cn_product_name` VARCHAR(255) NOT NULL,
`declaration_en_product_name` VARCHAR(255) NOT NULL,
`declaration_cn_hs_code` VARCHAR(64) NOT NULL,
`declaration_se_hs_code` VARCHAR(64) NOT NULL,
`declaration_base_info` TEXT NOT NULL,
`manual_reviewed` TINYINT NOT NULL DEFAULT '0', -- 是否人工干预过价格
`key` TEXT NULL DEFAULT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_product_id`),
INDEX `order_id` (`order_id`),
INDEX `product_id` (`product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_order_option`;
CREATE TABLE `ots_order_product_option` (
`order_product_option_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`option_id` INT NOT NULL,
`option_value_id` INT NOT NULL,
`product_option_id` INT NOT NULL,
`product_option_name` VARCHAR(100) NOT NULL,
`product_option_value_id` INT NOT NULL DEFAULT '0',
`extra_manufacturer_id` INT NOT NULL,
`extra_manufacturer_name` VARCHAR(255) NOT NULL,
`model` VARCHAR(64) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`quantity` INT NOT NULL DEFAULT '0',
`unit_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', -- option单价（不含option，税前价格）
`total` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', -- 单价X数量（税前价格）
`weight` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`manual_reviewed` TINYINT NOT NULL DEFAULT '0', -- 是否人工干预过价格
PRIMARY KEY (`order_product_option_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_order_product_files` (
`order_product_file_id` INT NOT NULL AUTO_INCREMENT,
`order_product_id` INT NOT NULL,
`name` VARCHAR(100) NOT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_product_file_id`),
INDEX `order_product_id` (`order_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_order_product_proof_images` (
`order_product_proof_image_id` INT NOT NULL AUTO_INCREMENT,
`order_product_id` INT NOT NULL,
`name` VARCHAR(100) NOT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_product_proof_image_id`),
INDEX `order_product_id` (`order_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- 由于v4不需要turnover功能，所以order_history只用于记录log
DROP TABLE IF EXISTS `ots_order_history`;
CREATE TABLE `ots_order_history` (
`order_history_id` INT NOT NULL AUTO_INCREMENT,
`customer_id` INT NOT NULL,
`order_id` INT NOT NULL,
`action` VARCHAR(100) NOT NULL,
`original` TEXT NOT NULL,
`altered` TEXT NOT NULL,
`date_added` DATETIME NOT NULL,
PRIMARY KEY (`order_history_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- Step 2 ots_customer_favorite -> ots_order_favorite，ots_customer_filter -> ots_order_filter
DROP TABLE IF EXISTS `ots_customer_favorite`;
CREATE TABLE `ots_order_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 IF EXISTS `ots_customer_filter`;
CREATE TABLE `ots_order_filter` (
`order_filter_id` INT NOT NULL AUTO_INCREMENT,
`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 (`order_filter_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- Step 3 标记order product是否是转移的产品
ALTER TABLE `ots_order_product`
ADD COLUMN `transferred` TINYINT NOT NULL DEFAULT '0' AFTER `manual_reviewed`;
-- Step 4
ALTER TABLE `ots_order_product_option`
ADD COLUMN `type` VARCHAR(32) NOT NULL AFTER `quantity`;
-- Step 5 等admin的order整理好之后在删除以下表
DROP TABLE `ots_order_recurring`;
DROP TABLE `ots_order_recurring_transaction`;
DROP TABLE `ots_order_shipment`;
DROP TABLE `ots_order_total`;
DROP TABLE `ots_order_voucher`;
</pre> |
| #471 | <pre>
-- Order product files + Order product proof images
DROP TABLE `ots_order_product_files`;
CREATE TABLE `ots_order_product_file` (
`order_product_file_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`original_name` VARCHAR(255) NOT NULL,
`save_path` VARCHAR(255) NOT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_product_file_id`),
INDEX `order_id` (`order_id`),
INDEX `order_product_id` (`order_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE `ots_order_product_proof_images`;
CREATE TABLE `ots_order_product_proof_image` (
`order_product_proof_image_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`original_name` VARCHAR(255) NOT NULL,
`save_path` VARCHAR(255) NOT NULL,
`create_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
PRIMARY KEY (`order_product_proof_image_id`),
INDEX `order_id` (`order_id`),
INDEX `order_product_id` (`order_product_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*