其他 #440: v4.0: Database - Record all SQL changes - IT: OTS (China) - Redmine

其他 #440

*Background* 
 # New requirement for v4.0 OTS 
   See: OneDrive\赤蓝商贸（上海）有限公司\Shanghai Group - IT\开发相关\OTS V4\开发需求 

 *Task Details* 
 * *Rule (v4.0)* *Rule* 
 # %{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_ots'; 

 SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT 
 FROM information_schema.columns 
 WHERE column_default = '0000-00-00 00:00:00' 
 AND table_schema = 'redblue_ots'; 

 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_ots' 
   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_ots' 
   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` VARCHAR(128) NOT NULL DEFAULT '' AFTER `zone_id`, 
	 DROP COLUMN `image`; 

 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 `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`;  
 </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*

Back

Project

General

Profile

IT: OTS (China)

其他 #440