[Sommaire] [Imprimer]

   1  # This SQL script upgrades the core Zen Cart database structure from v1.2.0 to v1.2.1

   2  #

   3  # $Id: mysql_upgrade_zencart_120_to_121.sql 4243 2006-08-24 10:55:28Z drbyte $

   4  #

   5  
   6  ## CONFIGURATION TABLE

   7  UPDATE configuration set configuration_title = 'Define Conditions of Use' WHERE configuration_key = 'DEFINE_CONDITIONS_STATUS';
   8  UPDATE configuration SET configuration_description = 'Automatically check to see if a new version of Zen-Cart is available. Enabling this can sometimes slow down the loading of Admin pages. (Displayed on main Index page after login, and Server Info page.)' WHERE configuration_key = 'SHOW_VERSION_UPDATE_IN_HEADER';
   9  INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Display Prices with Tax in Admin', 'DISPLAY_PRICE_WITH_TAX_ADMIN', 'false', 'Display prices with tax included (true) or add the tax at the end (false) in Admin(Invoices)', '1', '21', 'zen_cfg_select_option(array(\'true\', \'false\'), ', now());
  10  UPDATE configuration SET configuration_description='Products Display Order by<br />0= Product ID<br />1= Product Name<br />2= Model<br />3= Price, Product Name<br />4= Price, Model<br />5= Product Name, Model<br />6= Product Sort Order', set_function='zen_cfg_select_drop_down(array(array(\'id\'=>\'0\', \'text\'=>\'Product ID\'), array(\'id\'=>\'1\', \'text\'=>\'Name\'), array(\'id\'=>\'2\', \'text\'=>\'Product Model\'), array(\'id\'=>\'3\', \'text\'=>\'Product Price - Name\'), array(\'id\'=>\'4\', \'text\'=>\'Product Price - Model\'), array(\'id\'=>\'5\', \'text\'=>\'Product Name - Model\'), array(\'id\'=>\'6\', \'text\'=>\'Product Sort Order\')),' WHERE configuration_key='PRODUCT_INFO_PREVIOUS_NEXT_SORT';
  11  UPDATE configuration SET configuration_title = 'Enable phpBB linkage?', configuration_description = 'Should Zen Cart synchronize new account information to your (already-installed) phpBB forum?' WHERE configuration_key = 'PHPBB_LINKS_ENABLED';
  12  INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Customers Referral Status', 'CUSTOMERS_REFERRAL_STATUS', '0', 'Customers Referral Code is created from<br />0= Off<br />1= 1st Discount Coupon Code used<br />2= Customer can add during create account or edit if blank<br /><br />NOTE: Once the Customers Referral Code has been set it can only be changed in the Admin Customer', '5', '80', 'zen_cfg_select_option(array(\'0\', \'1\', \'2\'), ', now());
  13  INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Show Category Counts - Admin', 'SHOW_COUNTS_ADMIN', 'true', 'Show Category Counts in Admin?', '1', '130', 'zen_cfg_select_option(array(\'true\', \'false\'), ', now());
  14  INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Display "Newsletter Unsubscribe" Link?', 'SHOW_NEWSLETTER_UNSUBSCRIBE_LINK', 'true', 'Show "Newsletter Unsubscribe" link in the "Information" side-box?', '12', '70', 'zen_cfg_select_option(array(\'true\', \'false\'),', now());
  15  
  16  
  17  ## Table Structure updates to handle better use of decimal points

  18  ALTER TABLE customers_basket CHANGE COLUMN customers_basket_quantity customers_basket_quantity FLOAT DEFAULT '0' NOT NULL;
  19  ALTER TABLE orders_products CHANGE COLUMN products_quantity products_quantity FLOAT DEFAULT '0' NOT NULL;
  20  ALTER TABLE products CHANGE COLUMN products_quantity products_quantity FLOAT DEFAULT '0' NOT NULL;
  21  ALTER TABLE products CHANGE COLUMN products_ordered products_ordered FLOAT DEFAULT '0' NOT NULL;
  22  ALTER TABLE products CHANGE COLUMN products_quantity_order_min products_quantity_order_min FLOAT DEFAULT '1' NOT NULL;
  23  ALTER TABLE products CHANGE COLUMN products_quantity_order_units products_quantity_order_units FLOAT DEFAULT '1' NOT NULL;
  24  ALTER TABLE products CHANGE COLUMN products_quantity_order_max products_quantity_order_max FLOAT DEFAULT '0' NOT NULL;
  25  ALTER TABLE products_discount_quantity CHANGE COLUMN discount_qty discount_qty FLOAT DEFAULT '0' NOT NULL;
  26  
  27  ## Customers Table additions

  28  ALTER TABLE customers ADD COLUMN customers_referral VARCHAR(32) NOT NULL default '';
  29  
  30  ## Add Coupon_Code to Order Table:

  31  ALTER TABLE orders ADD COLUMN coupon_code varchar(32) NOT NULL default '' AFTER payment_method;
  32  
  33  ## LAYOUT BOXES TABLE

  34  INSERT INTO layout_boxes (layout_template, layout_box_name, layout_box_status, layout_box_location, layout_box_sort_order, layout_box_sort_order_single, layout_box_status_single) VALUES ('default_template_settings', 'document_categories.php', 1, 0, 0, 0, 0);
  35  INSERT INTO layout_boxes (layout_template, layout_box_name, layout_box_status, layout_box_location, layout_box_sort_order, layout_box_sort_order_single, layout_box_status_single) VALUES ('default_template_settings', 'music_genres.php', 1, 1, 0, 0, 0);
  36  INSERT INTO layout_boxes (layout_template, layout_box_name, layout_box_status, layout_box_location, layout_box_sort_order, layout_box_sort_order_single, layout_box_status_single) VALUES ('default_template_settings', 'record_companies.php', 1, 1, 0, 0, 0);
  37  UPDATE layout_boxes SET layout_box_status_single=127 WHERE layout_template = 'default_template_settings' AND layout_box_name = 'banner_box.php';
  38  
  39  ## Query Builder Update

  40  UPDATE query_builder set query_string ='select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o where c.customers_newsletter = \'1\' AND c.customers_id = o.customers_id and o.date_purchased < subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC' WHERE query_id='3';
  41  UPDATE query_builder set query_string ='select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o where c.customers_newsletter = \'1\' AND c.customers_id = o.customers_id and o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC' WHERE query_id='4';
  42  UPDATE query_builder set query_string ='select 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 o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC' WHERE query_id='5';
  43  
  44  ## Reset Sales and Salemaker expiry dates if null or 0000-00-00

  45  UPDATE specials SET expires_date='0001-01-01' where expires_date='0000-00-00';
  46  UPDATE specials SET specials_date_available ='0001-01-01' where specials_date_available ='0000-00-00';
  47  
  48  
  49  ## PayPal IPN Updates

  50  ALTER TABLE orders_products_attributes ADD products_options_id INT( 11 ) DEFAULT '0' NOT NULL;
  51  ALTER TABLE orders_products_attributes ADD products_options_values_id INT( 11 ) DEFAULT '0' NOT NULL;
  52  
  53  DROP TABLE IF EXISTS orders_session_info;
  54  CREATE TABLE orders_session_info (
  55    txn_signature varchar(32) NOT NULL default '',
  56    orders_id int(11) NOT NULL default '0',
  57    sendto int(11) NOT NULL default '1',
  58    billto int(11) NOT NULL default '1',
  59    language varchar(32) NOT NULL default '',
  60    currency char(3) NOT NULL default '',
  61    firstname varchar(32) NOT NULL default '',
  62    lastname varchar(32) NOT NULL default '',
  63    content_type varchar(32) NOT NULL default '',
  64    PRIMARY KEY (txn_signature,orders_id),
  65    KEY idx_orders_session_info_txn_signature (txn_signature)
  66  );
  67  
  68  DROP TABLE IF EXISTS paypal;
  69  CREATE TABLE paypal (
  70    paypal_ipn_id int(11) unsigned NOT NULL auto_increment,
  71    txn_type varchar(10) NOT NULL default '',
  72    reason_code varchar(15) default NULL,
  73    payment_type varchar(7) NOT NULL default '',
  74    payment_status varchar(17) NOT NULL default '',
  75    pending_reason varchar(14) default NULL,
  76    invoice varchar(64) default NULL,
  77    mc_currency char(3) NOT NULL default '',
  78    first_name varchar(32) NOT NULL default '',
  79    last_name varchar(32) NOT NULL default '',
  80    payer_business_name varchar(64) default NULL,
  81    address_name varchar(32) default NULL,
  82    address_street varchar(64) default NULL,
  83    address_city varchar(32) default NULL,
  84    address_state varchar(32) default NULL,
  85    address_zip varchar(10) default NULL,
  86    address_country varchar(64) default NULL,
  87    address_status varchar(11) default NULL,
  88    payer_email varchar(96) NOT NULL default '',
  89    payer_id varchar(32) NOT NULL default '',
  90    payer_status varchar(10) NOT NULL default '',
  91    payment_date datetime default NULL,
  92    business varchar(96) NOT NULL default '',
  93    receiver_email varchar(96) NOT NULL default '',
  94    receiver_id varchar(32) NOT NULL default '',
  95    txn_id varchar(17) NOT NULL default '',
  96    parent_txn_id varchar(17) default NULL,
  97    num_cart_items tinyint(4) unsigned NOT NULL default '1',
  98    mc_gross decimal(7,2) NOT NULL default '0.00',
  99    mc_fee decimal(7,2) NOT NULL default '0.00',
 100    payment_gross decimal(7,2) default NULL,
 101    payment_fee decimal(7,2) default NULL,
 102    settle_amount decimal(7,2) default NULL,
 103    settle_currency char(3) default NULL,
 104    exchange_rate decimal(4,2) default NULL,
 105    notify_version decimal(2,1) NOT NULL default '0.0',
 106    verify_sign varchar(128) NOT NULL default '',
 107    last_modified datetime default NULL,
 108    date_added datetime default NULL,
 109    memo text,
 110    PRIMARY KEY (paypal_ipn_id,txn_id),
 111    KEY idx_paypal_paypal_ipn_id (paypal_ipn_id)
 112  );
 113  
 114  DROP TABLE IF EXISTS paypal_payment_status;
 115  CREATE TABLE paypal_payment_status (
 116    payment_status_id int(11) NOT NULL auto_increment,
 117    payment_status_name varchar(64) NOT NULL default '',
 118    PRIMARY KEY (payment_status_id)
 119  );
 120  INSERT INTO paypal_payment_status VALUES (1, 'Completed');
 121  INSERT INTO paypal_payment_status VALUES (2, 'Pending');
 122  INSERT INTO paypal_payment_status VALUES (3, 'Failed');
 123  INSERT INTO paypal_payment_status VALUES (4, 'Denied');
 124  INSERT INTO paypal_payment_status VALUES (5, 'Refunded');
 125  INSERT INTO paypal_payment_status VALUES (6, 'Canceled_Reversal');
 126  INSERT INTO paypal_payment_status VALUES (7, 'Reversed');
 127  
 128  DROP TABLE IF EXISTS paypal_payment_status_history;
 129  CREATE TABLE paypal_payment_status_history (
 130    payment_status_history_id int(11) NOT NULL auto_increment,
 131    paypal_ipn_id int(11) NOT NULL default '0',
 132    payment_status varchar(17) NOT NULL default '',
 133    pending_reason varchar(14) default NULL,
 134    date_added datetime NOT NULL default '0001-01-01 00:00:00',
 135    PRIMARY KEY (payment_status_history_id)
 136  );
 137  
 138  ## These tables are obsolete after the new PayPal IPN is installed

 139  DROP TABLE IF EXISTS paypal_ipn_address_status;
 140  DROP TABLE IF EXISTS paypal_ipn_mc_currency;
 141  DROP TABLE IF EXISTS paypal_ipn_payment_status;
 142  DROP TABLE IF EXISTS paypal_ipn_payment_type;
 143  DROP TABLE IF EXISTS paypal_ipn_pending_reason;
 144  DROP TABLE IF EXISTS paypal_ipn_reason_code;
 145  DROP TABLE IF EXISTS paypal_ipn_txn_type;
 146  
 147  ## Migrate Paypal IPN data from 1.2.0 format to 1.2.1 format:

 148  #NEXT_X_ROWS_AS_ONE_COMMAND:1

 149  INSERT INTO paypal 
 150  (paypal_ipn_id, txn_type, reason_code, payment_type, payment_status, pending_reason, 
 151  invoice, mc_currency, first_name, last_name, payer_business_name, address_name, 
 152  address_street, address_city, address_state, address_zip, address_country, address_status, 
 153  payer_email, payer_id, payer_status, 
 154  payment_date, business, receiver_email, receiver_id, txn_id, 
 155  num_cart_items, mc_gross, mc_fee, payment_gross, payment_fee, settle_amount, 
 156  exchange_rate, notify_version, verify_sign, date_added, memo )
 157  SELECT p.paypal_ipn_id, p.txn_type, p.reason_code, p.payment_type, p.payment_status, 
 158  p.pending_reason, p.invoice, p.mc_currency, 
 159  p.first_name, p.last_name, p.payer_business_name, p.address_name, p.address_street, 
 160  p.address_city, p.address_state, p.address_zip, p.address_country, p.address_status, 
 161  p.payer_email, p.payer_id, p.payer_status, 
 162  p.payment_date, p.business, p.receiver_email, p.receiver_id, p.txn_id, 
 163  po.num_cart_items, po.mc_gross, po.mc_fee, po.payment_gross, po.payment_fee, po.settle_amount, 
 164  po.exchange_rate, p.notify_version, p.verify_sign, p.date_added, pm.memo
 165  FROM (paypal_ipn p, paypal_ipn_orders_memo pm
 166  LEFT JOIN paypal_ipn_orders po
 167  ON p.paypal_ipn_id = po.paypal_ipn_id)
 168  WHERE p.paypal_ipn_id = pm.paypal_ipn_id;
 169  
 170  UPDATE paypal SET payment_status='Completed' where payment_status=1;
 171  UPDATE paypal SET payment_status='Pending' where payment_status=2;
 172  UPDATE paypal SET payment_status='Failed' where payment_status=3;
 173  UPDATE paypal SET payment_status='Denied' where payment_status=4;
 174  UPDATE paypal SET payment_status='Refunded' where payment_status=5;
 175  UPDATE paypal SET payment_status='Cancelled' where payment_status=6;
 176  UPDATE paypal SET payment_type='instant' where payment_type=1;
 177  UPDATE paypal SET payment_type='echeck' where payment_type=2;
 178  UPDATE paypal SET pending_reason='' where pending_reason=0;
 179  UPDATE paypal SET pending_reason='echeck' where pending_reason=1;
 180  UPDATE paypal SET pending_reason='multi-currency' where pending_reason=2;
 181  UPDATE paypal SET pending_reason='intl' where pending_reason=3;
 182  UPDATE paypal SET pending_reason='Verify' where pending_reason=4;
 183  UPDATE paypal SET pending_reason='address' where pending_reason=5;
 184  UPDATE paypal SET pending_reason='upgrade' where pending_reason=6;
 185  UPDATE paypal SET pending_reason='unilateral' where pending_reason=7;
 186  UPDATE paypal SET pending_reason='other' where pending_reason=8;
 187  UPDATE paypal SET reason_code='' where reason_code=0;
 188  UPDATE paypal SET reason_code='chargeback' where reason_code=1;
 189  UPDATE paypal SET reason_code='guarantee' where reason_code=2;
 190  UPDATE paypal SET reason_code='buyer_complaint' where reason_code=3;
 191  UPDATE paypal SET reason_code='other' where reason_code=4;
 192  UPDATE paypal SET txn_type='web_accept' where txn_type=1;
 193  UPDATE paypal SET txn_type='cart' where txn_type=2;
 194  UPDATE paypal SET txn_type='send_money' where txn_type=3;
 195  UPDATE paypal SET txn_type='reversal' where txn_type=4;
 196  UPDATE paypal SET mc_currency='USD' where mc_currency=1;
 197  UPDATE paypal SET mc_currency='GBP' where mc_currency=2;
 198  UPDATE paypal SET mc_currency='EUR' where mc_currency=3;
 199  UPDATE paypal SET mc_currency='CAD' where mc_currency=4;
 200  UPDATE paypal SET mc_currency='JPY' where mc_currency=5;
 201  UPDATE paypal SET address_status='confirmed' where address_status=1;
 202  UPDATE paypal SET address_status='unconfirmed' where address_status=2;
 203  
 204  #NEXT_X_ROWS_AS_ONE_COMMAND:1

 205  INSERT INTO paypal_payment_status_history 
 206  (paypal_ipn_id, payment_status, pending_reason, date_added)
 207  SELECT paypal_ipn_id, payment_status, pending_reason, date_added
 208  FROM paypal;
 209  
 210  DROP TABLE IF EXISTS paypal_ipn;
 211  DROP TABLE IF EXISTS paypal_ipn_orders;
 212  DROP TABLE IF EXISTS paypal_ipn_orders_memo;
 213  
 214  ## END PAYPAL_IPN_MIGRATION

 215  
 216  
 217  
 218  ## THE FOLLOWING SHOULD BE THE "LAST" ITEMS IN THE FILE, so that if the upgrade fails prematurely, the version info is not updated.

 219  UPDATE project_version SET project_version_major='1', project_version_minor='2.1', project_version_patch_major='', project_version_date_applied=now() WHERE project_version_key = 'Zen-Cart Main';
 220  UPDATE project_version SET project_version_major='1', project_version_minor='2.1', project_version_patch_major='', project_version_date_applied=now() WHERE project_version_key = 'Zen-Cart Database';
 221  
 222  ## one final typo:

 223  ## the following fixes misspelled column name from 1.2.0:

 224  ALTER TABLE product_types CHANGE COLUMN date_addded date_added datetime NOT NULL default '0001-01-01 00:00:00';
 225  
 226  #####  END OF UPGRADE SCRIPT