其他 #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* 
 # %{color:red}DONE%: %{color:red}TO-DO%: ENGINE = MyISAM -> InnoDB 
 # %{color:red}DONE%: Don’t need to specify the length or precision for your *numeric* data.  
 
 # %{color:red}DONE%: Set Remain the default text type obviously -> utf8mb4_general_ci > utf8mb3_general_ci  
 # %{color:red}TO-DO%: 需要维护v3数据库 -不再需要维护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; 

 TRUNCATE TABLE ots_order_status; 
 INSERT INTO ots_order_status (order_status_id, language_id, name, code, hex, shipped, sort_order, status) VALUES 
 (5, 1, 'Packing', 60, '#ffe699', 0, 60, 1), 
 (4, 1, 'P-Finished', 50, '#66ffff', 0, 50, 1), 
 (3, 1, 'P-Started', 30, '#fce4d6', 0, 30, 1), 
 (2, 1, 'New', 20, '#ffffff', 0, 20, 1), 
 (6, 1, 'Labelled', 0, '#0099ff', 0, 0, 0), 
 (7, 2, 'Completed', 80, '#92d050', 1, 80, 1), 
 (8, 1, 'Shipped', 70, '#cc99ff', 1, 70, 1), 
 (6, 2, 'Labelled', 0, '#0099ff', 0, 0, 0), 
 (1, 1, 'Sample', 10, '#e7e6e6', 0, 10, 1), 
 (7, 1, 'Completed', 80, '#92d050', 1, 80, 1), 
 (8, 2, 'Shipped', 70, '#cc99ff', 1, 70, 1), 
 (1, 2, 'Sample', 10, '#e7e6e6', 0, 10, 1), 
 (5, 2, 'Packing', 60, '#ffe699', 0, 60, 1), 
 (3, 2, 'P-Started', 30, '#fce4d6', 0, 30, 1), 
 (4, 2, 'P-Finished', 50, '#66ffff', 0, 50, 1), 
 (2, 2, 'New', 20, '#ffffff', 0, 20, 1);</pre> |  
 | #437      |  
 *Support 'SE' language, and language id = 2.(EN = 1, SE = 2, CN = 3)* 
 <pre> 
 TRUNCATE TABLE ots_language; 
 INSERT INTO `ots_language` (`language_id`, `name`, `code`, `locale`, `image`, `directory`, `sort_order`, `status`) VALUES 
	 (1, 'English', 'en', 'en-US,en_US.UTF-8,en_US,en-gb,english', 'gb.png', 'english', 1, 1), 
	 (2, 'Swedish', 'se', 'sv_SE.UTF-8,sv_SE,swedish', '', '', 2, 1), 
	 (3, 'Chinese', 'zh', 'zh,zh-CN', '', '', 3, 1); 

 TRUNCATE TABLE ots_order_status; 
 INSERT INTO `ots_order_status` (`order_status_id`, `language_id`, `name`, `code`, `hex`, `shipped`, `sort_order`, `status`) VALUES 
	 (5, 2, 'Packing', 60, '#ffe699', 0, 60, 1), 
	 (4, 2, 'P-Finished', 50, '#66ffff', 0, 50, 1), 
	 (3, 2, 'P-Started', 30, '#fce4d6', 0, 30, 1), 
	 (2, 2, 'New', 20, '#ffffff', 0, 20, 1), 
	 (6, 2, 'Labelled', 0, '#0099ff', 0, 0, 0), 
	 (8, 2, 'Shipped', 70, '#cc99ff', 1, 70, 1), 
	 (1, 2, 'Sample', 10, '#e7e6e6', 0, 10, 1), 
	 (7, 2, 'Completed', 80, '#92d050', 1, 80, 1), 
	 (5, 1, 'Packing', 60, '#ffe699', 0, 60, 1), 
	 (4, 1, 'P-Finished', 50, '#66ffff', 0, 50, 1), 
	 (3, 1, 'P-Started', 30, '#fce4d6', 0, 30, 1), 
	 (2, 1, 'New', 20, '#ffffff', 0, 20, 1), 
	 (6, 1, 'Labelled', 0, '#0099ff', 0, 0, 0), 
	 (8, 1, 'Shipped', 70, '#cc99ff', 1, 70, 1), 
	 (1, 1, 'Sample', 10, '#e7e6e6', 0, 10, 1), 
	 (7, 1, 'Completed', 80, '#92d050', 1, 80, 1), 
	 (7, 3, 'Completed', 80, '#92d050', 1, 80, 1), 
	 (6, 3, 'Labelled', 0, '#0099ff', 0, 0, 0), 
	 (2, 3, 'New', 20, '#ffffff', 0, 20, 1), 
	 (4, 3, 'P-Finished', 50, '#66ffff', 0, 50, 1), 
	 (3, 3, 'P-Started', 30, '#fce4d6', 0, 30, 1), 
	 (5, 3, 'Packing', 60, '#ffe699', 0, 60, 1), 
	 (1, 3, 'Sample', 10, '#e7e6e6', 0, 10, 1), 
	 (8, 3, 'Shipped', 70, '#cc99ff', 1, 70, 1);</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=UTF8; 

     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=utf8; 
 </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