gistfile1.sql

-- -----------------------------------------------------------------
-- SQL to Upgrade Opencart 1.5.6.4 database to Opencart 2.0.1.1
-- @theme ULTIMATUM
-- @author Felipe Marques
-- @email contato@felipemarques.com.br
-- @date 2015-03-31
-- @desc Upgrade all tables in opencart 1.5.6.4 to 2.0.1.1
-- -----------------------------------------------------------------
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

ALTER TABLE `terracotaedito`.`oc_address` 
DROP COLUMN `tax_id`,
DROP COLUMN `company_id`,
CHANGE COLUMN `company` `company` VARCHAR(40) NOT NULL ,
ADD COLUMN `custom_field` TEXT NOT NULL AFTER `zone_id`;

ALTER TABLE `terracotaedito`.`oc_affiliate` 
CHANGE COLUMN `company` `company` VARCHAR(40) NOT NULL ;

ALTER TABLE `terracotaedito`.`oc_attribute` 
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_banner_image` 
ADD COLUMN `sort_order` INT(3) NOT NULL DEFAULT '0' AFTER `image`;

ALTER TABLE `terracotaedito`.`oc_category` 
ADD INDEX `parent_id` (`parent_id` ASC),
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_category_description` 
ADD COLUMN `meta_title` VARCHAR(255) NOT NULL AFTER `description`,
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_category_to_store` 
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_custom_field` 
DROP COLUMN `position`,
DROP COLUMN `required`,
CHANGE COLUMN `location` `location` VARCHAR(7) NOT NULL ,
ADD COLUMN `status` TINYINT(1) NOT NULL AFTER `location`;

ALTER TABLE `terracotaedito`.`oc_customer` 
CHANGE COLUMN `customer_group_id` `customer_group_id` INT(11) NOT NULL AFTER `customer_id`,
CHANGE COLUMN `ip` `ip` VARCHAR(40) NOT NULL ,
ADD COLUMN `custom_field` TEXT NOT NULL AFTER `address_id`,
ADD COLUMN `safe` TINYINT(1) NOT NULL AFTER `approved`;

ALTER TABLE `terracotaedito`.`oc_customer_group` 
DROP COLUMN `tax_id_required`,
DROP COLUMN `tax_id_display`,
DROP COLUMN `company_id_required`,
DROP COLUMN `company_id_display`;

ALTER TABLE `terracotaedito`.`oc_download` 
DROP COLUMN `remaining`;

ALTER TABLE `terracotaedito`.`oc_information_description` 
ADD COLUMN `meta_title` VARCHAR(255) NOT NULL AFTER `description`,
ADD COLUMN `meta_description` VARCHAR(255) NOT NULL AFTER `meta_title`,
ADD COLUMN `meta_keyword` VARCHAR(255) NOT NULL AFTER `meta_description`;

ALTER TABLE `terracotaedito`.`oc_language` 
DROP COLUMN `filename`;

CREATE TABLE IF NOT EXISTS `terracotaedito`.`oc_marketing` (
  `marketing_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `description` TEXT NOT NULL,
  `code` VARCHAR(64) NOT NULL,
  `clicks` INT(5) NOT NULL DEFAULT '0',
  `date_added` DATETIME NOT NULL,
  PRIMARY KEY (`marketing_id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `terracotaedito`.`oc_modification` (
  `modification_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  `code` VARCHAR(64) NOT NULL,
  `author` VARCHAR(64) NOT NULL,
  `version` VARCHAR(32) NOT NULL,
  `link` VARCHAR(255) NOT NULL,
  `xml` TEXT NOT NULL,
  `status` TINYINT(1) NOT NULL,
  `date_added` DATETIME NOT NULL,
  PRIMARY KEY (`modification_id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `terracotaedito`.`oc_module` (
  `module_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  `code` VARCHAR(32) NOT NULL,
  `setting` TEXT NOT NULL,
  PRIMARY KEY (`module_id`))
ENGINE = MyISAM
AUTO_INCREMENT = 31
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

ALTER TABLE `terracotaedito`.`oc_order` 
DROP COLUMN `payment_tax_id`,
DROP COLUMN `payment_company_id`,
CHANGE COLUMN `payment_company` `payment_company` VARCHAR(40) NOT NULL ,
CHANGE COLUMN `shipping_company` `shipping_company` VARCHAR(40) NOT NULL ,
ADD COLUMN `custom_field` TEXT NOT NULL AFTER `fax`,
ADD COLUMN `payment_custom_field` TEXT NOT NULL AFTER `payment_address_format`,
ADD COLUMN `shipping_custom_field` TEXT NOT NULL AFTER `shipping_address_format`,
ADD COLUMN `marketing_id` INT(11) NOT NULL AFTER `commission`,
ADD COLUMN `tracking` VARCHAR(64) NOT NULL AFTER `marketing_id`;

ALTER TABLE `terracotaedito`.`oc_order_product` 
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_order_recurring` 
DROP COLUMN `profile_reference`,
DROP COLUMN `profile_description`,
DROP COLUMN `profile_name`,
DROP COLUMN `profile_id`,
DROP COLUMN `created`,
CHANGE COLUMN `status` `status` TINYINT(4) NOT NULL AFTER `trial_price`,
ADD COLUMN `reference` VARCHAR(255) NOT NULL AFTER `order_id`,
ADD COLUMN `recurring_id` INT(11) NOT NULL AFTER `product_quantity`,
ADD COLUMN `recurring_name` VARCHAR(255) NOT NULL AFTER `recurring_id`,
ADD COLUMN `recurring_description` VARCHAR(255) NOT NULL AFTER `recurring_name`,
ADD COLUMN `date_added` DATETIME NOT NULL AFTER `status`;

ALTER TABLE `terracotaedito`.`oc_order_recurring_transaction` 
DROP COLUMN `created`,
CHANGE COLUMN `type` `type` VARCHAR(255) NOT NULL AFTER `reference`,
ADD COLUMN `reference` VARCHAR(255) NOT NULL AFTER `order_recurring_id`,
ADD COLUMN `date_added` DATETIME NOT NULL AFTER `amount`;

ALTER TABLE `terracotaedito`.`oc_order_total` 
DROP COLUMN `text`,
ADD INDEX `order_id` (`order_id` ASC),
DROP INDEX `idx_orders_total_orders_id` ;

ALTER TABLE `terracotaedito`.`oc_product` 
CHANGE COLUMN `viewed` `viewed` INT(5) NOT NULL DEFAULT '0' AFTER `status`,
CHANGE COLUMN `isbn` `isbn` VARCHAR(17) NOT NULL ,
CHANGE COLUMN `date_available` `date_available` DATE NOT NULL DEFAULT '0000-00-00' ,
DROP INDEX `idx_3` ,
DROP INDEX `idx_2` ,
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_product_description` 
CHANGE COLUMN `tag` `tag` TEXT NOT NULL AFTER `description`,
ADD COLUMN `meta_title` VARCHAR(255) NOT NULL AFTER `tag`,
DROP INDEX `idx_2` ,
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_product_image` 
ADD INDEX `product_id` (`product_id` ASC);

ALTER TABLE `terracotaedito`.`oc_product_option` 
DROP COLUMN `option_value`,
ADD COLUMN `value` TEXT NOT NULL AFTER `option_id`;

ALTER TABLE `terracotaedito`.`oc_product_recurring` 
DROP COLUMN `store_id`,
ADD COLUMN `recurring_id` INT(11) NOT NULL AFTER `product_id`,
ADD COLUMN `customer_group_id` INT(11) NOT NULL AFTER `recurring_id`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`product_id`, `recurring_id`, `customer_group_id`);

ALTER TABLE `terracotaedito`.`oc_product_to_category` 
ADD INDEX `category_id` (`category_id` ASC),
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_product_to_store` 
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_return` 
CHANGE COLUMN `date_ordered` `date_ordered` DATE NOT NULL DEFAULT '0000-00-00' ;

ALTER TABLE `terracotaedito`.`oc_review` 
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_setting` 
DROP COLUMN `group`,
ADD COLUMN `code` VARCHAR(32) NOT NULL AFTER `store_id`,
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_url_alias` 
ADD INDEX `query` (`query` ASC),
ADD INDEX `keyword` (`keyword` ASC),
DROP INDEX `idx_2` ,
DROP INDEX `idx_1` ;

ALTER TABLE `terracotaedito`.`oc_user` 
ADD COLUMN `image` VARCHAR(255) NOT NULL AFTER `email`;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;