其他 #440
Updated by Junyi Zhang 10 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`;
DROP TABLE `ots_option_description`;
DROP TABLE `ots_option_value`;
DROP TABLE `ots_option_value_description`;
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;
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;
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,
`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;
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;
</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
# 数据层规范（Model）
3.1: 数据库操作只应出现在model或者system中。
3.2: v3/v4的model分为两个文件，比如： order.php, order_v3.php
# 前台代码
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*