[Sommaire] [Imprimer]

   1  #

   2  # * This SQL script upgrades the core Zen Cart database structure from v1.3.0.2 to v1.3.5

   3  # *

   4  # * @package Installer

   5  # * @access private

   6  # * @copyright Copyright 2003-2006 Zen Cart Development Team

   7  # * @copyright Portions Copyright 2003 osCommerce

   8  # * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0

   9  # * @version $Id: mysql_upgrade_zencart_1302_to_135.sql 6536 2007-06-26 01:10:18Z drbyte $

  10  #

  11  
  12  ## CONFIGURATION TABLE

  13  UPDATE configuration SET configuration_description = 'Set the Default Customer Default Email Preference<br />0= Text<br />1= HTML<br />' WHERE configuration_key = 'ACCOUNT_EMAIL_PREFERENCE';
  14  UPDATE configuration SET configuration_description = 'Show on Shopping Cart Update Cart Button Location as:<br /><br />1= Next to each Qty Box<br />2= Below all Products<br />3= Both Next to each Qty Box and Below all Products' where configuration_key = 'SHOW_SHOPPING_CART_UPDATE';
  15  UPDATE configuration SET configuration_title = 'Display Product Add to Cart Button (0=off; 1=on; 2=on with Qty Box per Product)', configuration_description = 'Do you want to display the Add to Cart Button?<br /><br /><strong>NOTE:</strong> Turn OFF Display Multiple Products Qty Box Status to use Option 2 on with Qty Box per Product' WHERE configuration_key= 'PRODUCT_LIST_PRICE_BUY_NOW';
  16  UPDATE configuration SET configuration_description = 'Customer should be asked about product notifications after checkout success and in account preferences<br />0= Never ask<br />1= Ask (ignored on checkout if has already selected global notifications)<br /><br />Note: Sidebox must be turned off separately' WHERE configuration_key = 'CUSTOMERS_PRODUCTS_NOTIFICATION_STATUS';
  17  UPDATE configuration set configuration_value='false' where configuration_key ='STORE_DB_TRANSACTIONS';
  18  INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Include Product Listing Alpha Sorter Dropdown', 'PRODUCT_LIST_ALPHA_SORTER', 'false', 'Do you want to include an Alpha Filter dropdown on the Product Listing?', '8', '50', 'zen_cfg_select_option(array(\'true\', \'false\'), ', now());
  19  INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Include Product Listing Sub Categories Image', 'PRODUCT_LIST_CATEGORIES_IMAGE_STATUS', 'false', 'Do you want to include the Sub Categories Image on the Product Listing?', '8', '52', 'zen_cfg_select_option(array(\'true\', \'false\'), ', now());
  20  INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Include Product Listing Top Categories Image', 'PRODUCT_LIST_CATEGORIES_IMAGE_STATUS_TOP', 'false', 'Do you want to include the Top Categories Image on the Product Listing?', '8', '53', 'zen_cfg_select_option(array(\'true\', \'false\'), ', now());
  21  
  22  # need to change

  23  # 'Previous Next - Navigation Includes Category Position', 'PRODUCT_INFO_CATEGORIES

  24  DELETE from configuration where configuration_key = 'SHOW_ACCOUNT_LINKS_ON_SITE_MAP' and configuration_group_id = 18;
  25  
  26  UPDATE product_type_layout SET configuration_description = 'Display Record Company on Product Info 0= off 1= on' WHERE configuration_key = 'SHOW_PRODUCT_MUSIC_INFO_RECORD_COMPANY';
  27  
  28  INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('cURL Proxy Status', 'CURL_PROXY_REQUIRED', 'False', 'Does your host require that you use a proxy for cURL communication?', '1', '50', 'zen_cfg_select_option(array(\'True\', \'False\'), ', now());
  29  INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('cURL Proxy Address', 'CURL_PROXY_SERVER_DETAILS', '', 'If you have GoDaddy hosting or other hosting services that require use of a proxy to talk to external sites via cURL, enter their proxy address here.<br />format: address:port<br />ie: for GoDaddy, enter: 64.202.165.130:3128', 1, 51, NULL, now(), NULL, NULL);
  30  INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('Previous Next - Navigation Includes Category Name and Image Status', 'PRODUCT_INFO_CATEGORIES_IMAGE_STATUS', '2', 'Product\'s Category Image and Name Status<br />0= Category Name and Image always shows<br />1= Category Name only<br />2= Category Name and Image when not blank', 18, 20, now(), now(), NULL, 'zen_cfg_select_drop_down(array(array(\'id\'=>\'0\', \'text\'=>\'Category Name and Image Always\'), array(\'id\'=>\'1\', \'text\'=>\'Category Name only\'), array(\'id\'=>\'2\', \'text\'=>\'Category Name and Image when not blank\')),');
  31  
  32  
  33  #Other

  34  UPDATE banners SET banners_url='http://www.zen-cart.com/index.php?main_page=infopages&pages_id=30' where banners_url='http://www.zen-cart.com/modules/freecontent/index.php?id=29';
  35  UPDATE query_builder SET query_string = 'select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC' WHERE query_name = 'Dormant Customers (>3months) (Subscribers)';
  36  
  37  
  38  ################################################################

  39  
  40  #table alterations

  41  ALTER TABLE query_builder CHANGE COLUMN query_category query_category varchar(40) NOT NULL default '';
  42  ALTER TABLE query_builder CHANGE COLUMN query_name query_name varchar(80) NOT NULL default '';
  43  ALTER TABLE query_builder CHANGE COLUMN query_description query_description TEXT NOT NULL;
  44  ALTER TABLE query_builder CHANGE COLUMN query_string query_string TEXT NOT NULL;
  45  ALTER TABLE query_builder CHANGE COLUMN query_keys_list query_keys_list TEXT NOT NULL;
  46  
  47  
  48  #Index Optimizations

  49  ALTER TABLE layout_boxes ADD INDEX idx_layout_box_sort_order_zen (layout_box_sort_order);
  50  ALTER TABLE media_clips ADD INDEX idx_clip_type_zen (clip_type);
  51  ALTER TABLE media_manager ADD INDEX idx_media_name_zen (media_name);
  52  ALTER TABLE media_types ADD INDEX idx_type_name_zen (type_name);
  53  ALTER TABLE products ADD INDEX idx_products_date_available_zen (products_date_available);
  54  ALTER TABLE products ADD INDEX idx_products_ordered_zen (products_ordered);
  55  ALTER TABLE products ADD INDEX idx_products_model_zen (products_model);
  56  ALTER TABLE products ADD INDEX idx_products_price_sorter_zen (products_price_sorter);
  57  ALTER TABLE products ADD INDEX idx_master_categories_id_zen (master_categories_id);
  58  ALTER TABLE products ADD INDEX idx_products_sort_order_zen (products_sort_order);
  59  ALTER TABLE products ADD INDEX idx_manufacturers_id_zen (manufacturers_id);
  60  ALTER TABLE products_attributes ADD INDEX idx_opt_sort_order_zen (products_options_sort_order);
  61  ALTER TABLE products_options ADD INDEX idx_products_options_sort_order_zen (products_options_sort_order);
  62  ALTER TABLE products_options ADD INDEX idx_products_options_name_zen (products_options_name);
  63  ALTER TABLE products_options_values ADD INDEX idx_products_options_values_name_zen (products_options_values_name);
  64  ALTER TABLE products_options_values ADD INDEX idx_products_options_values_sort_order_zen (products_options_values_sort_order);
  65  ALTER TABLE products_options_values_to_products_options ADD INDEX idx_products_options_id_zen (products_options_id);
  66  ALTER TABLE products_options_values_to_products_options ADD INDEX idx_products_options_values_id_zen (products_options_values_id);
  67  ALTER TABLE product_music_extra ADD INDEX idx_artists_id_zen (artists_id);
  68  ALTER TABLE product_music_extra ADD INDEX idx_record_company_id_zen (record_company_id);
  69  
  70  ALTER TABLE admin ADD INDEX idx_admin_email_zen (admin_email);
  71  ALTER TABLE banners ADD INDEX idx_expires_date_zen (expires_date);
  72  ALTER TABLE banners ADD INDEX idx_date_scheduled_zen (date_scheduled);
  73  ALTER TABLE featured ADD INDEX idx_expires_date_zen (expires_date);
  74  ALTER TABLE specials ADD INDEX idx_expires_date_zen (expires_date);
  75  ALTER TABLE orders ADD INDEX idx_date_purchased_zen (date_purchased);
  76  
  77  ALTER TABLE countries ADD INDEX idx_address_format_id_zen (address_format_id);
  78  ALTER TABLE countries ADD INDEX idx_iso_2_zen (countries_iso_code_2);
  79  ALTER TABLE countries ADD INDEX idx_iso_3_zen (countries_iso_code_3);
  80  ALTER TABLE zones ADD INDEX idx_zone_country_id_zen (zone_country_id);
  81  ALTER TABLE zones ADD INDEX idx_zone_code_zen (zone_code);
  82  ALTER TABLE zones_to_geo_zones ADD INDEX idx_zones_zen (geo_zone_id, zone_country_id, zone_id);
  83  
  84  ALTER TABLE product_type_layout ADD INDEX idx_type_id_sort_order_zen (product_type_id, sort_order) ;
  85  
  86  ALTER TABLE reviews ADD INDEX idx_status_zen (status);
  87  ALTER TABLE reviews ADD INDEX idx_date_added_zen (date_added);
  88  
  89  ALTER TABLE salemaker_sales ADD INDEX idx_sale_date_start_zen (sale_date_start);
  90  ALTER TABLE salemaker_sales ADD INDEX idx_sale_date_end_zen (sale_date_end);
  91  
  92  #

  93  ALTER TABLE authorizenet ADD PRIMARY KEY (id );
  94  ALTER TABLE authorizenet DROP INDEX idx_auth_net_id;
  95  
  96  
  97  #############

  98  
  99  #### VERSION UPDATE COMMANDS

 100  ## THE FOLLOWING 2 SECTIONS SHOULD BE THE "LAST" ITEMS IN THE FILE, so that if the upgrade fails prematurely, the version info is not updated.

 101  ##The following updates the version HISTORY to store the prior version's info (Essentially "moves" the prior version info from the "project_version" to "project_version_history" table

 102  #NEXT_X_ROWS_AS_ONE_COMMAND:3

 103  INSERT INTO project_version_history (project_version_key, project_version_major, project_version_minor, project_version_patch, project_version_date_applied, project_version_comment)
 104  SELECT project_version_key, project_version_major, project_version_minor, project_version_patch1 as project_version_patch, project_version_date_applied, project_version_comment
 105  FROM project_version;
 106  
 107  ## Now set to new version

 108  UPDATE project_version SET project_version_major='1', project_version_minor='3.5', project_version_patch1='', project_version_patch1_source='', project_version_patch2='', project_version_patch2_source='', project_version_comment='Version Update 1.3.0.2->1.3.5', project_version_date_applied=now() WHERE project_version_key = 'Zen-Cart Main';
 109  UPDATE project_version SET project_version_major='1', project_version_minor='3.5', project_version_patch1='', project_version_patch1_source='', project_version_patch2='', project_version_patch2_source='', project_version_comment='Version Update 1.3.0.2->1.3.5', project_version_date_applied=now() WHERE project_version_key = 'Zen-Cart Database';
 110  
 111  
 112  #####  END OF UPGRADE SCRIPT

 113