Skip to content

Instantly share code, notes, and snippets.

@rubo77
Last active December 27, 2015 13:09
Show Gist options
  • Save rubo77/7330900 to your computer and use it in GitHub Desktop.
Save rubo77/7330900 to your computer and use it in GitHub Desktop.
needed changes to upgrade oscommerce database from v2.2 to v2.3
-- needed changes to upgrade oscommerce database from v2.2 to v2.3:
-- missing keys:
-- ALTER TABLE `$table` ADD $indextype ( `$field` )
-- orders_products :
-- KEY idx_orders_products_orders_id (orders_id)
ALTER TABLE orders_products ADD INDEX (orders_id);
-- KEY idx_orders_products_products_id (products_id)
ALTER TABLE orders_products ADD INDEX (products_id);
-- orders_products_attributes:
KEY idx_orders_products_att_orders_id (orders_id)
-- orders_products_download:
KEY idx_orders_products_download_orders_id (orders_id)
-- orders_status:
KEY idx_orders_status_name (orders_status_name)
-- orders_status_history:
KEY idx_orders_status_history_orders_id (orders_id)
-- exists products:
KEY idx_products_model (products_model)
-- products_attributes:
KEY idx_products_attributes_products_id (products_id)
-- reviews:
KEY idx_reviews_products_id (products_id),
KEY idx_reviews_customers_id (customers_id)
-- specials:
KEY idx_specials_products_id (products_id)
-- zones:
KEY idx_zones_country_id (zone_country_id)
-- additional needed alterations:
create table if not exists products_images (
id int(11) not null auto_increment,
products_id int(11) not null ,
image varchar(64) ,
htmlcontent text ,
sort_order int(11) not null ,
PRIMARY KEY (id),
KEY products_images_prodid (products_id)
);
alter table whos_online modify last_page_url text not null;
alter table reviews add reviews_status tinyint(1) default '0' not null after last_modified;
alter table configuration modify configuration_title varchar(255) NOT NULL;
alter table configuration modify configuration_key varchar(255) NOT NULL;
alter table customers_info add password_reset_key char(40);
alter table customers_info add password_reset_date datetime;
alter table orders modify payment_method varchar(255) NOT NULL;
alter table orders_status add public_flag int(11) default '1';
alter table orders_status add downloads_flag int(11) default '0';
create table if not exists administrators (
id int(11) not null auto_increment,
user_name varchar(255) not null ,
user_password varchar(60) not null ,
PRIMARY KEY (id)
);
-- source: http://forums.oscommerce.de/topic/79178-umzug-datenbank-nach-23/
alter table address_book modify entry_gender char(1);
alter table address_book modify entry_company varchar(255);
alter table address_book modify entry_firstname varchar(255) NOT NULL;
alter table address_book modify entry_lastname varchar(255) NOT NULL;
alter table address_book modify entry_street_address varchar(255) NOT NULL;
alter table address_book modify entry_suburb varchar(255);
alter table address_book modify entry_postcode varchar(255) NOT NULL;
alter table address_book modify entry_city varchar(255) NOT NULL;
alter table address_book modify entry_state varchar(255);
alter table administrators modify user_name varchar(255) binary NOT NULL;
alter table configuration modify configuration_value text NOT NULL;
alter table countries modify countries_name varchar(255) NOT NULL;
alter table customers modify customers_gender char(1);
alter table customers modify customers_firstname varchar(255) NOT NULL;
alter table customers modify customers_lastname varchar(255) NOT NULL;
alter table customers modify customers_email_address varchar(255) NOT NULL;
alter table customers modify customers_telephone varchar(255) NOT NULL;
alter table customers modify customers_fax varchar(255);
alter table orders modify customers_name varchar(255) NOT NULL;
alter table orders modify customers_company varchar(255);
alter table orders modify customers_street_address varchar(255) NOT NULL;
alter table orders modify customers_suburb varchar(255);
alter table orders modify customers_city varchar(255) NOT NULL;
alter table orders modify customers_postcode varchar(255) NOT NULL;
alter table orders modify customers_state varchar(255);
alter table orders modify customers_country varchar(255) NOT NULL;
alter table orders modify customers_telephone varchar(255) NOT NULL;
alter table orders modify customers_email_address varchar(255) NOT NULL;
alter table orders modify delivery_name varchar(255) NOT NULL;
alter table orders modify delivery_company varchar(255);
alter table orders modify delivery_street_address varchar(255) NOT NULL;
alter table orders modify delivery_suburb varchar(255);
alter table orders modify delivery_city varchar(255) NOT NULL;
alter table orders modify delivery_postcode varchar(255) NOT NULL;
alter table orders modify delivery_state varchar(255);
alter table orders modify delivery_country varchar(255) NOT NULL;
alter table orders modify billing_name varchar(255) NOT NULL;
alter table orders modify billing_company varchar(255);
alter table orders modify billing_street_address varchar(255) NOT NULL;
alter table orders modify billing_suburb varchar(255);
alter table orders modify billing_city varchar(255) NOT NULL;
alter table orders modify billing_postcode varchar(255) NOT NULL;
alter table orders modify billing_state varchar(255);
alter table orders modify billing_country varchar(255) NOT NULL;
alter table orders modify cc_owner varchar(255);
alter table reviews modify customers_name varchar(255) NOT NULL;
alter table whos_online modify full_name varchar(255) NOT NULL;
alter table zones modify zone_name varchar(255) NOT NULL;
DROP TABLE IF EXISTS sec_directory_whitelist;
CREATE TABLE sec_directory_whitelist (
id int NOT NULL auto_increment,
directory varchar(255) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO sec_directory_whitelist values (null, 'admin/backups');
INSERT INTO sec_directory_whitelist values (null, 'admin/images/graphs');
INSERT INTO sec_directory_whitelist values (null, 'images');
INSERT INTO sec_directory_whitelist values (null, 'images/banners');
INSERT INTO sec_directory_whitelist values (null, 'images/dvd');
INSERT INTO sec_directory_whitelist values (null, 'images/gt_interactive');
INSERT INTO sec_directory_whitelist values (null, 'images/hewlett_packard');
INSERT INTO sec_directory_whitelist values (null, 'images/matrox');
INSERT INTO sec_directory_whitelist values (null, 'images/microsoft');
INSERT INTO sec_directory_whitelist values (null, 'images/sierra');
INSERT INTO sec_directory_whitelist values (null, 'includes/work');
INSERT INTO sec_directory_whitelist values (null, 'pub');
alter table customers modify customers_password varchar(60) NOT NULL;
DROP TABLE IF EXISTS action_recorder;
CREATE TABLE action_recorder (
id int NOT NULL auto_increment,
module varchar(255) NOT NULL,
user_id int,
user_name varchar(255),
identifier varchar(255) NOT NULL,
success char(1),
date_added datetime NOT NULL,
PRIMARY KEY (id),
KEY idx_action_recorder_module (module),
KEY idx_action_recorder_user_id (user_id),
KEY idx_action_recorder_identifier (identifier),
KEY idx_action_recorder_date_added (date_added)
);
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Installed Modules', 'MODULE_ACTION_RECORDER_INSTALLED', 'ar_admin_login.php;ar_contact_us.php;ar_tell_a_friend.php', 'List of action recorder module filenames separated by a semi-colon. This is automatically updated. No need to edit.', '6', '0', now());
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Minimum Minutes Per E-Mail', 'MODULE_ACTION_RECORDER_CONTACT_US_EMAIL_MINUTES', '15', 'Minimum number of minutes to allow 1 e-mail to be sent (eg, 15 for 1 e-mail every 15 minutes)', '6', '0', now());
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Minimum Minutes Per E-Mail', 'MODULE_ACTION_RECORDER_TELL_A_FRIEND_EMAIL_MINUTES', '15', 'Minimum number of minutes to allow 1 e-mail to be sent (eg, 15 for 1 e-mail every 15 minutes)', '6', '0', now());
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Allowed Minutes', 'MODULE_ACTION_RECORDER_ADMIN_LOGIN_MINUTES', '5', 'Number of minutes to allow login attempts to occur.', '6', '0', now());
INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Allowed Attempts', 'MODULE_ACTION_RECORDER_ADMIN_LOGIN_ATTEMPTS', '3', 'Number of login attempts to allow within the specified period.', '6', '0', now());
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment