needed changes to upgrade oscommerce database from v2.2 to v2.3

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());