Changeset 16546 for branches/feature-module-update/html

Timestamp:

2007/10/24 14:01:35 (19 years ago)

Author:

nanasess

Message:

複数カテゴリ対応

Location:

branches/feature-module-update/html/install/sql

Files:

• create_table_mysql.sql (modified) (1 diff)
• create_table_pgsql.sql (modified) (1 diff)
• create_view.sql (modified) (2 diffs)
• drop_table.sql (modified) (1 diff)
• insert_data.sql (modified) (3 diffs)

branches/feature-module-update/html/install/sql/create_table_mysql.sql

@@ -356 +356 @@
     del_flg  smallint NOT NULL DEFAULT 0
 ) TYPE=InnoDB ;
+
+CREATE TABLE dtb_product_categories (
+    product_id int4 NOT NULL,
+    category_id int4 NOT NULL,
+    rank int4,
+    PRIMARY KEY(product_id, category_id)
+) TYPE=InnoDB;
 
 CREATE TABLE dtb_bat_order_daily (

branches/feature-module-update/html/install/sql/create_table_pgsql.sql

@@ -355 +355 @@
     update_date timestamp,
     del_flg  int2 NOT NULL DEFAULT 0
+);
+
+CREATE TABLE dtb_product_categories (
+    product_id int4 NOT NULL,
+    category_id int4 NOT NULL,
+    rank int4,
+    PRIMARY KEY(product_id, category_id)
 );
 

branches/feature-module-update/html/install/sql/create_view.sql

@@ -48 +48 @@
 
 CREATE VIEW vw_products_allclass AS
-     SELECT product_id,
+     SELECT T5.product_id,
             product_code_min,
             product_code_max,
@@ -60 +60 @@
             stock_unlimited_max,
             category_rank,
-            category_id,
-            del_flg,
-            status,
-            name,
-            comment1,
-            comment2,
-            comment3,
-            rank,
-            main_list_comment,
-            main_image,
-            main_list_image,
-            product_flag,
-            deliv_date_id,
-            sale_limit,
-            point_rate,
-            sale_unlimited,
-            create_date,
-            deliv_fee
+            T5.category_id,
+            T5.del_flg,
+            T5.status,
+            T5.name,
+            T5.comment1,
+            T5.comment2,
+            T5.comment3,
+            T5.rank,
+            T5.main_list_comment,
+            T5.main_image,
+            T5.main_list_image,
+            T5.product_flag,
+            T5.deliv_date_id,
+            T5.sale_limit,
+            T5.point_rate,
+            T5.sale_unlimited,
+            T5.create_date,
+            T5.deliv_fee
        FROM
-             ((dtb_products AS T1
-     LEFT JOIN
-               dtb_product_categories AS T2
-            ON T1.product_id = T2.product_id)
-   RIGHT JOIN
-      (SELECT
-              product_id AS product_id_sub,
-              MIN(product_code) AS product_code_min,
-              MAX(product_code) AS product_code_max,
-              MIN(price01) AS price01_min,
-              MAX(price01) AS price01_max,
-              MIN(price02) AS price02_min,
-              MAX(price02) AS price02_max,
-              MIN(stock) AS stock_min,
-              MAX(stock) AS stock_max,
-              MIN(stock_unlimited) AS stock_unlimited_min,
-              MAX(stock_unlimited) AS stock_unlimited_max
-         FROM dtb_products_class GROUP BY product_id) AS T3
-         ON T1.product_id = T3.product_id_sub) AS T4
-  LEFT JOIN
-      (SELECT rank AS category_rank,
-              category_id AS sub_category_id
-        FROM dtb_category) AS T5
-         ON T4.category_id = T5.sub_category_id;
+             ((SELECT T1.product_id,
+                      T1.del_flg,
+                      T1.status,
+                      T1.name,
+                      T1.comment1,
+                      T1.comment2,
+                      T1.comment3,
+                      T1.main_list_comment,
+                      T1.main_image,
+                      T1.main_list_image,
+                      T1.product_flag,
+                      T1.deliv_date_id,
+                      T1.sale_limit,
+                      T1.point_rate,
+                      T1.sale_unlimited,
+                      T1.create_date,
+                      T1.deliv_fee,
+                      T2.category_id,
+                      T1.rank
+                 FROM dtb_products AS T1
+            LEFT JOIN dtb_product_categories AS T2
+                   ON T1.product_id = T2.product_id) AS T3
+    RIGHT JOIN
+          (SELECT product_id AS product_id_sub,
+                  MIN(product_code) AS product_code_min,
+                  MAX(product_code) AS product_code_max,
+                  MIN(price01) AS price01_min,
+                  MAX(price01) AS price01_max,
+                  MIN(price02) AS price02_min,
+                  MAX(price02) AS price02_max,
+                  MIN(stock) AS stock_min,
+                  MAX(stock) AS stock_max,
+                  MIN(stock_unlimited) AS stock_unlimited_min,
+                  MAX(stock_unlimited) AS stock_unlimited_max
+             FROM dtb_products_class
+         GROUP BY product_id) AS T4
+               ON T3.product_id = T4.product_id_sub) AS T5
+    LEFT JOIN
+        (SELECT rank AS category_rank,
+                category_id AS sub_category_id
+           FROM dtb_category) AS T6
+          ON T5.category_id = T6.sub_category_id;
 
 CREATE VIEW vw_products_allclass_detail AS

branches/feature-module-update/html/install/sql/drop_table.sql

@@ -17 +17 @@
 DROP TABLE dtb_classcategory;
 DROP TABLE dtb_category;
+DROP TABLE dtb_product_categories;
 DROP TABLE dtb_bat_order_daily;
 DROP TABLE dtb_bat_order_daily_hour;

branches/feature-module-update/html/install/sql/insert_data.sql

@@ -1 @@
-CREATE INDEX dtb_products_category_id_key ON dtb_products(category_id);
+--CREATE INDEX dtb_products_category_id_key ON dtb_products(category_id);
 CREATE INDEX dtb_products_class_product_id_key ON dtb_products_class(product_id);
 CREATE INDEX dtb_order_detail_product_id_key ON dtb_order_detail(product_id);
@@ -308 +308 @@
 
 INSERT INTO dtb_products (name,deliv_fee,sale_limit,sale_unlimited,category_id,rank,status,product_flag,point_rate,comment1,comment2,comment3,comment4,comment5,comment6,file1,file2,file3,file4,file5,file6,main_list_comment,main_list_image,main_comment,main_image,main_large_image,sub_title1,sub_comment1,sub_image1,sub_large_image1,sub_title2,sub_comment2,sub_image2,sub_large_image2,sub_title3,sub_comment3,sub_image3,sub_large_image3,sub_title4,sub_comment4,sub_image4,sub_large_image4,sub_title5,sub_comment5,sub_image5,sub_large_image5,sub_title6,sub_comment6,sub_image6,sub_large_image6,del_flg,creator_id,create_date,update_date,deliv_date_id)
-VALUES ('アイスクリーム', NULL, NULL, 1, 5, 1, 1, '10010', 10, NULL, NULL, 'アイス,バニラ,チョコ,抹茶', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '暑い夏にどうぞ。', '08311201_44f65122ee5fe.jpg', '冷たいものはいかがですか？', '08311202_44f6515906a41.jpg', '08311203_44f651959bcb5.jpg', NULL, '<b>おいしいよ<b>', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2, now(), now(), 2);
+VALUES ('アイスクリーム', NULL, NULL, NULL, 5, 1, 1, '10010', 10, NULL, NULL, 'アイス,バニラ,チョコ,抹茶', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '暑い夏にどうぞ。', '08311201_44f65122ee5fe.jpg', '冷たいものはいかがですか？', '08311202_44f6515906a41.jpg', '08311203_44f651959bcb5.jpg', NULL, '<b>おいしいよ<b>', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2, now(), now(), 2);
 INSERT INTO dtb_products (name,deliv_fee,sale_limit,sale_unlimited,category_id,rank,status,product_flag,point_rate,comment1,comment2,comment3,comment4,comment5,comment6,file1,file2,file3,file4,file5,file6,main_list_comment,main_list_image,main_comment,main_image,main_large_image,sub_title1,sub_comment1,sub_image1,sub_large_image1,sub_title2,sub_comment2,sub_image2,sub_large_image2,sub_title3,sub_comment3,sub_image3,sub_large_image3,sub_title4,sub_comment4,sub_image4,sub_large_image4,sub_title5,sub_comment5,sub_image5,sub_large_image5,sub_title6,sub_comment6,sub_image6,sub_large_image6,del_flg,creator_id,create_date,update_date,deliv_date_id)
-VALUES ('おなべ', NULL, 5, NULL, 4, 1, 1, '11001', 5, NULL, NULL, '鍋,なべ,ナベ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '一人用からあります。', '08311311_44f661811fec0.jpg', 'たまには鍋でもどうでしょう。', '08311313_44f661dc649fb.jpg', '08311313_44f661e5698a6.jpg', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2, now(), now(), 3);
+VALUES ('おなべ', NULL, 5, NULL, NULL, 1, 1, '11001', 5, NULL, NULL, '鍋,なべ,ナベ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '一人用からあります。', '08311311_44f661811fec0.jpg', 'たまには鍋でもどうでしょう。', '08311313_44f661dc649fb.jpg', '08311313_44f661e5698a6.jpg', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 2, now(), now(), 3);
 
 INSERT INTO dtb_products_class (product_id,classcategory_id1,classcategory_id2,product_code,stock,stock_unlimited,sale_limit,price01,price02,status,creator_id,create_date,update_date)
@@ -357 +357 @@
 INSERT INTO dtb_category (category_name,parent_category_id,level,rank,creator_id,create_date,update_date,del_flg) VALUES ('なべ', 1, 2, 3, 2, now(), now(), 0);
 INSERT INTO dtb_category (category_name,parent_category_id,level,rank,creator_id,create_date,update_date,del_flg) VALUES ('アイス', 3, 3, 1, 2, now(), now(), 0);
+
+INSERT INTO dtb_product_categories (product_id,category_id,rank) VALUES (1,5,1);
+INSERT INTO dtb_product_categories (product_id,category_id,rank) VALUES (2,4,2);
 
 INSERT INTO dtb_category_count VALUES (4, 1, now());