mysql_upgrade_zencart_114_to_120.sql

Zen Cart是真正的电子商务艺术

#
# This SQL script upgrades the core Zen Cart database structure from v1.1.4 to v1.2.0
#
# $Id: mysql_upgrade_zencart_114_to_120.sql 4243 2006-08-24 10:55:28Z drbyte $
#


# First the items from v1.1.4-patch1, just in case they weren't done already:
Update configuration set configuration_title = 'Package Tare Small to Medium - added percentage:weight' where configuration_key= 'SHIPPING_BOX_WEIGHT';
Update configuration set configuration_description= 'What is the weight of typical packaging of small to medium packages?<br />Example: 10% + 1lb 10:1<br />10% + 0lbs 10:0<br />0% + 5lbs 0:5<br />0% + 0lbs 0:0' where configuration_key= 'SHIPPING_BOX_WEIGHT';
Update configuration set configuration_title = 'Larger packages - added packaging percentage:weight' where configuration_key= 'SHIPPING_BOX_PADDING';
Update configuration set configuration_description= 'What is the weight of typical packaging for Large packages?<br />Example: 10% + 1lb 10:1<br />10% + 0lbs 10:0<br />0% + 5lbs 0:5<br />0% + 0lbs 0:0' where configuration_key= 'SHIPPING_BOX_PADDING';

# Small touch to bring it up-to-date even more:
#INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Package Tare Small to Medium - added percentage:weight', 'SHIPPING_BOX_WEIGHT', '0:3', 'What is the weight of typical packaging of small to medium packages?<br />Example: 10% + 1lb 10:1<br />10% + 0lbs 10:0<br />0% + 5lbs 0:5<br />0% + 0lbs 0:0', '7', '4', now());
#INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Larger packages - added packaging percentage:weight', 'SHIPPING_BOX_PADDING', '10:0', 'What is the weight of typical packaging for Large packages?<br />Example: 10% + 1lb 10:1<br />10% + 0lbs 10:0<br />0% + 5lbs 0:5<br />0% + 0lbs 0:0', '7', '5', now());
UPDATE configuration set configuration_value = '10:0' WHERE configuration_key = 'SHIPPING_BOX_PADDING' AND configuration_value = '10';
UPDATE configuration set configuration_value = '0:3' WHERE configuration_key = 'SHIPPING_BOX_WEIGHT' AND configuration_value = '3';


### Now the Diff's from v1.1.4 to v1.2.0

ALTER TABLE banners CHANGE COLUMN date_added date_added datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE banners CHANGE COLUMN banners_group banners_group varchar(15) NOT NULL default '';
ALTER TABLE banners ADD COLUMN banners_open_new_windows int(1) NOT NULL default '1';
ALTER TABLE banners ADD COLUMN banners_on_ssl int(1) NOT NULL default '1';
ALTER TABLE banners_history CHANGE COLUMN banners_history_date banners_history_date datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE categories ADD COLUMN categories_status tinyint(1) NOT NULL default '1';
ALTER TABLE categories ADD INDEX idx_sort_order (sort_order);
ALTER TABLE categories_description ADD COLUMN categories_description text NOT NULL;
ALTER TABLE configuration CHANGE COLUMN configuration_key configuration_key varchar(255) NOT NULL default '';
ALTER TABLE configuration CHANGE COLUMN set_function set_function text;
ALTER TABLE configuration CHANGE COLUMN use_function use_function text;
ALTER TABLE configuration CHANGE COLUMN date_added date_added datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE coupon_email_track CHANGE COLUMN date_sent date_sent datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE coupon_gv_queue CHANGE COLUMN date_created date_created datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE coupon_redeem_track CHANGE COLUMN redeem_date redeem_date datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE coupons CHANGE COLUMN coupon_start_date coupon_start_date datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE coupons CHANGE COLUMN date_created date_created datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE coupons CHANGE COLUMN date_modified date_modified datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE coupons CHANGE COLUMN coupon_expire_date coupon_expire_date datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE currencies CHANGE COLUMN symbol_right symbol_right varchar(24) default NULL;
ALTER TABLE currencies CHANGE COLUMN symbol_left symbol_left varchar(24) default NULL;
ALTER TABLE customers CHANGE COLUMN customers_dob customers_dob datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE customers ADD COLUMN customers_nick varchar(96) NOT NULL default '' AFTER customers_email_address;
ALTER TABLE customers ADD COLUMN customers_group_pricing int(11) NOT NULL default '0';
ALTER TABLE customers ADD COLUMN customers_email_format varchar(4) NOT NULL default 'TEXT';
ALTER TABLE customers ADD COLUMN customers_authorization int(1) NOT NULL default '0';
ALTER TABLE geo_zones CHANGE COLUMN date_added date_added datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE newsletters CHANGE COLUMN date_added date_added datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE newsletters ADD COLUMN content_html text NOT NULL AFTER content;
ALTER TABLE orders ADD COLUMN paypal_ipn_id int(11) NOT NULL default '0';
ALTER TABLE orders_products ADD COLUMN onetime_charges decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE orders_products ADD COLUMN products_priced_by_attribute tinyint(1) NOT NULL default '0';
ALTER TABLE orders_products ADD COLUMN product_is_free tinyint(1) NOT NULL default '0';
ALTER TABLE orders_products ADD COLUMN products_discount_type tinyint(1) NOT NULL default '0';
ALTER TABLE orders_products ADD COLUMN products_discount_type_from tinyint(1) NOT NULL default '0';
ALTER TABLE orders_products_attributes ADD COLUMN product_attribute_is_free tinyint(1) NOT NULL default '0';
ALTER TABLE orders_products_attributes ADD COLUMN products_attributes_weight decimal(8,4) NOT NULL default '0.0000';
ALTER TABLE orders_products_attributes ADD COLUMN products_attributes_weight_prefix char(1) NOT NULL default '';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_discounted tinyint(1) NOT NULL default '1';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_price_base_included tinyint(1) NOT NULL default '1';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_price_onetime decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_price_factor decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_price_factor_offset decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_price_factor_onetime decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_price_factor_onetime_offset decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_qty_prices text;
ALTER TABLE orders_products_attributes ADD COLUMN attributes_qty_prices_onetime text;
ALTER TABLE orders_products_attributes ADD COLUMN attributes_price_words decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_price_words_free int(4) NOT NULL default '0';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_price_letters decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE orders_products_attributes ADD COLUMN attributes_price_letters_free int(4) NOT NULL default '0';

ALTER TABLE orders_status_history CHANGE COLUMN date_added date_added datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE products CHANGE COLUMN products_date_added products_date_added datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE products ADD COLUMN products_type int(11) NOT NULL default '1' AFTER products_id;
ALTER TABLE products ADD COLUMN products_sort_order int(11) NOT NULL default '0';
ALTER TABLE products ADD COLUMN products_discount_type tinyint(1) NOT NULL default '0';
ALTER TABLE products ADD COLUMN products_discount_type_from tinyint(1) NOT NULL default '0';
ALTER TABLE products ADD COLUMN products_price_sorter decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE products ADD COLUMN master_categories_id int(11) NOT NULL default '0';
ALTER TABLE products ADD COLUMN products_mixed_discount_quantity tinyint(1) NOT NULL default '1';
ALTER TABLE products_attributes ADD COLUMN attributes_price_onetime decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE products_attributes ADD COLUMN attributes_price_factor decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE products_attributes ADD COLUMN attributes_price_factor_offset decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE products_attributes ADD COLUMN attributes_price_factor_onetime decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE products_attributes ADD COLUMN attributes_price_factor_onetime_offset decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE products_attributes ADD COLUMN attributes_qty_prices text;
ALTER TABLE products_attributes ADD COLUMN attributes_qty_prices_onetime text;
ALTER TABLE products_attributes ADD COLUMN attributes_price_words decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE products_attributes ADD COLUMN attributes_price_words_free int(4) NOT NULL default '0';
ALTER TABLE products_attributes ADD COLUMN attributes_price_letters decimal(15,4) NOT NULL default '0.0000';
ALTER TABLE products_attributes ADD COLUMN attributes_price_letters_free int(4) NOT NULL default '0';
ALTER TABLE products_attributes ADD COLUMN attributes_required tinyint(1) NOT NULL default '0';
ALTER TABLE products_notifications CHANGE COLUMN date_added date_added datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE products_options_types DROP COLUMN language_id;
ALTER TABLE products_options_types DROP PRIMARY KEY;
ALTER TABLE products_options_types ADD PRIMARY KEY (products_options_types_id);
ALTER TABLE reviews ADD COLUMN status int(1) NOT NULL default '1';
ALTER TABLE salemaker_sales CHANGE COLUMN sale_date_start sale_date_start date NOT NULL default '0001-01-01';
ALTER TABLE salemaker_sales CHANGE COLUMN sale_date_end sale_date_end date NOT NULL default '0001-01-01';
ALTER TABLE salemaker_sales CHANGE COLUMN sale_date_added sale_date_added date NOT NULL default '0001-01-01';
ALTER TABLE salemaker_sales CHANGE COLUMN sale_date_last_modified sale_date_last_modified date NOT NULL default '0001-01-01';
ALTER TABLE salemaker_sales CHANGE COLUMN sale_date_status_change sale_date_status_change date NOT NULL default '0001-01-01';
ALTER TABLE specials CHANGE COLUMN expires_date expires_date date NOT NULL default '0001-01-01';
ALTER TABLE specials ADD COLUMN specials_date_available date NOT NULL default '0001-01-01';
ALTER TABLE tax_class CHANGE COLUMN date_added date_added datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE tax_rates CHANGE COLUMN date_added date_added datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE zones_to_geo_zones CHANGE COLUMN date_added date_added datetime NOT NULL default '0001-01-01 00:00:00';
ALTER TABLE products_options ADD COLUMN products_options_images_per_row int(2) default '5' AFTER products_options_size;
ALTER TABLE products_options ADD COLUMN products_options_images_style int(1) default '0';

# reset expiry dates if left blank.
UPDATE specials SET expires_date='0001-01-01' where expires_date='0000-00-00';
UPDATE specials SET specials_date_available ='0001-01-01' where specials_date_available ='0000-00-00';


DROP TABLE IF EXISTS authorizenet;
CREATE TABLE authorizenet (
  id int(11) unsigned NOT NULL auto_increment,