Magento: app/code/core/Mage/Sales/sql/sales_setup/mysql4-upgrade-0.8.29-0.9.0.php Source File

00001 <?php
00002 /**
00003  * Magento
00004  *
00005  * NOTICE OF LICENSE
00006  *
00007  * This source file is subject to the Open Software License (OSL 3.0)
00008  * that is bundled with this package in the file LICENSE.txt.
00009  * It is also available through the world-wide-web at this URL:
00010  * http://opensource.org/licenses/osl-3.0.php
00011  * If you did not receive a copy of the license and are unable to
00012  * obtain it through the world-wide-web, please send an email
00013  * to license@magentocommerce.com so we can send you a copy immediately.
00014  *
00015  * DISCLAIMER
00016  *
00017  * Do not edit or add to this file if you wish to upgrade Magento to newer
00018  * versions in the future. If you wish to customize Magento for your
00019  * needs please refer to http://www.magentocommerce.com for more information.
00020  *
00021  * @category   Mage
00022  * @package    Mage_Sales
00023  * @copyright  Copyright (c) 2008 Irubin Consulting Inc. DBA Varien (http://www.varien.com)
00024  * @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
00025  */
00026 
00027 $installer = $this;
00028 /* @var $installer Mage_Sales_Model_Entity_Setup */
00029 $installer->startSetup();
00030 $installer->run("
00031 CREATE TABLE `{$installer->getTable('sales_flat_quote')}` (
00032     `entity_id` int(10) unsigned NOT NULL auto_increment,
00033     `store_id` smallint(5) unsigned NOT NULL default '0',
00034     `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
00035     `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
00036     `converted_at` datetime NOT NULL default '0000-00-00 00:00:00',
00037 
00038     `is_active` tinyint(1) unsigned default '1',
00039     `is_virtual` tinyint(1) unsigned default '0',
00040     `is_multi_shipping` tinyint(1) unsigned default '0',
00041 
00042     `items_count` int(10) unsigned default '0',
00043     `items_qty` decimal(12,4) default '0.0000',
00044 
00045     `orig_order_id` int(10) unsigned default '0',
00046 
00047     `store_to_base_rate` decimal(12,4) default '0.0000',
00048     `store_to_quote_rate` decimal(12,4) default '0.0000',
00049     `base_currency_code` varchar(255) default NULL,
00050     `store_currency_code` varchar(255) default NULL,
00051     `quote_currency_code` varchar(255) default NULL,
00052 
00053     `grand_total` decimal(12,4) default '0.0000',
00054     `base_grand_total` decimal(12,4) default '0.0000',
00055 
00056     `checkout_method` varchar(255) default NULL,
00057 
00058     `customer_id` int(10) unsigned default '0',
00059     `customer_tax_class_id` int(10) unsigned default '0',
00060     `customer_group_id` int(10) unsigned default '0',
00061     `customer_email` varchar(255) default NULL,
00062     `customer_prefix` varchar(40) default NULL,
00063     `customer_firstname` varchar(255) default NULL,
00064     `customer_middlename` varchar(40) default NULL,
00065     `customer_lastname` varchar(255) default NULL,
00066     `customer_suffix` varchar(40) default NULL,
00067     `customer_dob` datetime default NULL,
00068     `customer_note` varchar(255) default NULL,
00069     `customer_note_notify` tinyint(1) unsigned default '1',
00070     `customer_is_guest` tinyint(1) unsigned default '0',
00071 
00072     `remote_ip` varchar(32) default NULL,
00073     `applied_rule_ids` varchar(255) default NULL,
00074     `reserved_order_id` varchar(64) default '',
00075     `password_hash` varchar(255) default NULL,
00076     `coupon_code` varchar(255) default NULL,
00077     PRIMARY KEY  (`entity_id`),
00078     KEY `FK_SALES_QUOTE_STORE` (`store_id`),
00079     KEY `IDX_CUSTOMER` (`customer_id`,`store_id`,`is_active`)
00080 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
00081 
00082 
00083 CREATE TABLE `{$installer->getTable('sales_flat_quote_address')}` (
00084     `address_id` int(10) unsigned NOT NULL auto_increment,
00085     `quote_id` int(10) unsigned NOT NULL default '0',
00086     `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
00087     `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
00088 
00089     `customer_id` int(10) unsigned default NULL,
00090     `save_in_address_book` tinyint(1) default '0',
00091     `customer_address_id` int(10) unsigned default NULL,
00092     `address_type` varchar(255) default NULL,
00093     `email` varchar(255) default NULL,
00094     `prefix` varchar(40) default NULL,
00095     `firstname` varchar(255) default NULL,
00096     `middlename` varchar(40) default NULL,
00097     `lastname` varchar(255) default NULL,
00098     `suffix` varchar(40) default NULL,
00099     `company` varchar(255) default NULL,
00100     `street` varchar(255) default NULL,
00101     `city` varchar(255) default NULL,
00102     `region` varchar(255) default NULL,
00103     `region_id` int(10) unsigned default NULL,
00104     `postcode` varchar(255) default NULL,
00105     `country_id` varchar(255) default NULL,
00106     `telephone` varchar(255) default NULL,
00107     `fax` varchar(255) default NULL,
00108 
00109     `same_as_billing` tinyint(1) unsigned NOT NULL default '0',
00110     `free_shipping` tinyint(1) unsigned NOT NULL default '0',
00111     `collect_shipping_rates` tinyint(1) unsigned NOT NULL default '0',
00112     `shipping_method` varchar(255) NOT NULL default '',
00113     `shipping_description` varchar(255) NOT NULL default '',
00114     `weight` decimal(12,4) NOT NULL default '0.0000',
00115 
00116     `subtotal` decimal(12,4) NOT NULL default '0.0000',
00117     `base_subtotal` decimal(12,4) NOT NULL default '0.0000',
00118     `subtotal_with_discount` decimal(12,4) NOT NULL default '0.0000',
00119     `base_subtotal_with_discount` decimal(12,4) NOT NULL default '0.0000',
00120     `tax_amount` decimal(12,4) NOT NULL default '0.0000',
00121     `base_tax_amount` decimal(12,4) NOT NULL default '0.0000',
00122     `shipping_amount` decimal(12,4) NOT NULL default '0.0000',
00123     `base_shipping_amount` decimal(12,4) NOT NULL default '0.0000',
00124     `shipping_tax_amount` decimal(12,4) default NULL,
00125     `base_shipping_tax_amount` decimal(12,4) default NULL,
00126     `discount_amount` decimal(12,4) NOT NULL default '0.0000',
00127     `base_discount_amount` decimal(12,4) NOT NULL default '0.0000',
00128     `grand_total` decimal(12,4) NOT NULL default '0.0000',
00129     `base_grand_total` decimal(12,4) NOT NULL default '0.0000',
00130 
00131     `customer_notes` text,
00132     PRIMARY KEY  (`address_id`),
00133     KEY `FK_SALES_QUOTE_ADDRESS_SALES_QUOTE` (`quote_id`),
00134     CONSTRAINT `FK_SALES_QUOTE_ADDRESS_SALES_QUOTE` FOREIGN KEY (`quote_id`) REFERENCES `{$installer->getTable('sales_flat_quote')}` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
00135 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
00136 
00137 CREATE TABLE `{$installer->getTable('sales_flat_quote_address_item')}` (
00138     `address_item_id` int(10) unsigned NOT NULL auto_increment,
00139     `quote_address_id` int(10) unsigned NOT NULL default '0',
00140     `quote_item_id` int(10) unsigned NOT NULL default '0',
00141     `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
00142     `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
00143 
00144     `applied_rule_ids` text,
00145     `additional_data` text,
00146     `weight` decimal(12,4) default '0.0000',
00147     `qty` decimal(12,4) NOT NULL default '0.0000',
00148     `discount_amount` decimal(12,4) default '0.0000',
00149     `tax_amount` decimal(12,4) default '0.0000',
00150 
00151     `row_total` decimal(12,4) NOT NULL default '0.0000',
00152     `base_row_total` decimal(12,4) NOT NULL default '0.0000',
00153     `row_total_with_discount` decimal(12,4) default '0.0000',
00154     `base_discount_amount` decimal(12,4) default '0.0000',
00155     `base_tax_amount` decimal(12,4) default '0.0000',
00156     `row_weight` decimal(12,4) default '0.0000',
00157     PRIMARY KEY  (`address_item_id`),
00158     KEY `FK_QUOTE_ADDRESS_ITEM_QUOTE_ADDRESS` (`quote_address_id`),
00159     KEY `FK_SALES_QUOTE_ADDRESS_ITEM_QUOTE_ITEM` (`quote_item_id`),
00160     CONSTRAINT `FK_QUOTE_ADDRESS_ITEM_QUOTE_ADDRESS` FOREIGN KEY (`quote_address_id`) REFERENCES `{$installer->getTable('sales_flat_quote_address')}` (`address_id`) ON DELETE CASCADE ON UPDATE CASCADE,
00161     CONSTRAINT `FK_SALES_QUOTE_ADDRESS_ITEM_QUOTE_ITEM` FOREIGN KEY (`quote_item_id`) REFERENCES `{$installer->getTable('sales_flat_quote_item')}` (`item_id`)
00162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
00163 
00164 CREATE TABLE `{$installer->getTable('sales_flat_quote_item')}` (
00165     `item_id` int(10) unsigned NOT NULL auto_increment,
00166     `quote_id` int(10) unsigned NOT NULL default '0',
00167     `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
00168     `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
00169 
00170     `product_id` int(10) unsigned default NULL,
00171     `super_product_id` int(10) unsigned default NULL,
00172     `parent_product_id` int(10) unsigned default NULL,
00173     `is_virtual` tinyint(1) unsigned default NULL,
00174 
00175     `sku` varchar(255) NOT NULL default '',
00176     `name` varchar(255) default NULL,
00177     `description` text,
00178     `applied_rule_ids` text,
00179     `additional_data` text,
00180     `free_shipping` tinyint(1) unsigned NOT NULL default '0',
00181     `is_qty_decimal` tinyint(1) unsigned default NULL,
00182     `no_discount` tinyint(1) unsigned default '0',
00183 
00184     `weight` decimal(12,4) default '0.0000',
00185     `qty` decimal(12,4) NOT NULL default '0.0000',
00186     `price` decimal(12,4) NOT NULL default '0.0000',
00187     `base_price` decimal(12,4) NOT NULL default '0.0000',
00188     `custom_price` decimal(12,4) default NULL,
00189     `discount_percent` decimal(12,4) default '0.0000',
00190     `discount_amount` decimal(12,4) default '0.0000',
00191     `base_discount_amount` decimal(12,4) default '0.0000',
00192     `tax_percent` decimal(12,4) default '0.0000',
00193     `tax_amount` decimal(12,4) default '0.0000',
00194     `base_tax_amount` decimal(12,4) default '0.0000',
00195     `row_total` decimal(12,4) NOT NULL default '0.0000',
00196     `base_row_total` decimal(12,4) NOT NULL default '0.0000',
00197     `row_total_with_discount` decimal(12,4) default '0.0000',
00198     `row_weight` decimal(12,4) default '0.0000',
00199     PRIMARY KEY  (`item_id`),
00200     KEY `FK_SALES_QUOTE_ITEM_SALES_QUOTE` (`quote_id`),
00201     CONSTRAINT `FK_SALES_QUOTE_ITEM_SALES_QUOTE` FOREIGN KEY (`quote_id`) REFERENCES `{$installer->getTable('sales_flat_quote')}` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
00202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
00203 
00204 CREATE TABLE `{$installer->getTable('sales_flat_quote_item_option')}` (
00205     `option_id` int(10) unsigned NOT NULL auto_increment,
00206     `item_id` int(10) unsigned NOT NULL,
00207     `product_id` int(10) unsigned NOT NULL,
00208     `code` varchar(255) NOT NULL,
00209     `value` text NOT NULL,
00210     PRIMARY KEY  (`option_id`),
00211     KEY `FK_SALES_QUOTE_ITEM_OPTION_ITEM_ID` (`item_id`),
00212     CONSTRAINT `FK_SALES_QUOTE_ITEM_OPTION_ITEM_ID` FOREIGN KEY (`item_id`) REFERENCES `{$installer->getTable('sales_flat_quote_item')}` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE
00213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Additional options for quote item';
00214 
00215 CREATE TABLE `{$installer->getTable('sales_flat_quote_payment')}` (
00216     `payment_id` int(10) unsigned NOT NULL auto_increment,
00217     `quote_id` int(10) unsigned NOT NULL default '0',
00218     `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
00219     `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
00220     `method` varchar(255) default '',
00221 
00222     `cc_type` varchar(255) default '',
00223     `cc_number_enc` varchar(255) default '',
00224     `cc_last4` varchar(255) default '',
00225     `cc_cid_enc` varchar(255) default '',
00226     `cc_owner` varchar(255) default '',
00227     `cc_exp_month` tinyint(2) unsigned default '0',
00228     `cc_exp_year` smallint(4) unsigned default '0',
00229     `cc_ss_owner` varchar(255) default '',
00230     `cc_ss_start_month` tinyint(2) unsigned default '0',
00231     `cc_ss_start_year` smallint(4) unsigned default '0',
00232 
00233     `cybersource_token` varchar(255) default '',
00234     `paypal_correlation_id` varchar(255) default '',
00235     `paypal_payer_id` varchar(255) default '',
00236     `paypal_payer_status` varchar(255) default '',
00237     `po_number` varchar(255) default '',
00238     PRIMARY KEY  (`payment_id`),
00239     KEY `FK_SALES_QUOTE_PAYMENT_SALES_QUOTE` (`quote_id`),
00240     CONSTRAINT `FK_SALES_QUOTE_PAYMENT_SALES_QUOTE` FOREIGN KEY (`quote_id`) REFERENCES `{$installer->getTable('sales_flat_quote')}` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
00241 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
00242 
00243 CREATE TABLE `{$installer->getTable('sales_flat_quote_shipping_rate')}` (
00244     `rate_id` int(10) unsigned NOT NULL auto_increment,
00245     `address_id` int(10) unsigned NOT NULL default '0',
00246     `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
00247     `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
00248 
00249     `carrier` varchar(255) default NULL,
00250     `carrier_title` varchar(255) default NULL,
00251     `code` varchar(255) default NULL,
00252     `method` varchar(255) default NULL,
00253     `method_description` text,
00254     `price` decimal(12,4) NOT NULL default '0.0000',
00255     PRIMARY KEY  (`rate_id`),
00256     KEY `FK_SALES_QUOTE_SHIPPING_RATE_ADDRESS` (`address_id`),
00257     CONSTRAINT `FK_SALES_QUOTE_SHIPPING_RATE_ADDRESS` FOREIGN KEY (`address_id`) REFERENCES `{$installer->getTable('sales_flat_quote_address')}` (`address_id`) ON DELETE CASCADE ON UPDATE CASCADE
00258 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
00259 
00260 DELETE FROM `{$installer->getTable('log_quote')}`;
00261 ");
00262 
00263 $installer->addAttribute('order_item', 'is_virtual', array('type'=>'int'));
00264 $installer->endSetup();
00265 
00266 /**
00267  * Copy old quotes
00268  */
00269 @set_time_limit(0);
00270 
00271 $quoteFields = array_keys($installer->getConnection()->describeTable($installer->getTable('sales_flat_quote')));
00272 $itemFields  = array_keys($installer->getConnection()->describeTable($installer->getTable('sales_flat_quote_item')));
00273 
00274 $quoteRows = array();
00275 $query = $installer->getConnection()->query(
00276     $installer->getConnection()->select()
00277         ->from($installer->getTable('sales_quote'), 'entity_id')
00278 );
00279 while ($row = $query->fetch()) {
00280     $quoteRows[] = $row['entity_id'];
00281 }
00282 
00283 foreach ($quoteRows as $oldQuoteId) {
00284     $quoteInfo = $installer->getConnection()->fetchRow(
00285         $installer->getConnection()->select()
00286             ->from($installer->getTable('sales_quote'))
00287             ->where('entity_id=?', $oldQuoteId)
00288     );
00289 
00290     $quoteItems = $installer->getConnection()->fetchAll(
00291         $installer->getConnection()->select()
00292             ->from($installer->getTable('sales_quote_item'))
00293             ->where('parent_id=?', $oldQuoteId)
00294     );
00295 
00296     if (!empty($quoteItems)) {
00297         unset($quoteInfo['entity_id']);
00298 
00299         $quoteData = array();
00300         foreach ($quoteFields as $field) {
00301             if (isset($quoteInfo[$field])) {
00302                 $quoteData[$field] = $quoteInfo[$field];
00303             }
00304         }
00305 
00306         $installer->getConnection()->insert($installer->getTable('sales_flat_quote'), $quoteData);
00307         $quoteId = $installer->getConnection()->lastInsertId();
00308 
00309         foreach ($quoteItems as $itemInfo) {
00310             $itemData = array(
00311                 'quote_id' => $quoteId
00312             );
00313 
00314             foreach ($itemFields as $field) {
00315                 if (isset($itemInfo[$field])) {
00316                     $itemData[$field] = $itemInfo[$field];
00317                 }
00318             }
00319             $installer->getConnection()->insert($installer->getTable('sales_flat_quote_item'), $itemData);
00320         }
00321     }
00322 }
00323 
00324 $installer->startSetup();
00325 $installer->run("
00326 DROP TABLE IF EXISTS {$this->getTable('sales_quote')};
00327 DROP TABLE IF EXISTS {$this->getTable('sales_quote_address')};
00328 DROP TABLE IF EXISTS {$this->getTable('sales_quote_address_decimal')};
00329 DROP TABLE IF EXISTS {$this->getTable('sales_quote_address_int')};
00330 DROP TABLE IF EXISTS {$this->getTable('sales_quote_address_text')};
00331 DROP TABLE IF EXISTS {$this->getTable('sales_quote_address_varchar')};
00332 DROP TABLE IF EXISTS {$this->getTable('sales_quote_decimal')};
00333 DROP TABLE IF EXISTS {$this->getTable('sales_quote_entity')};
00334 DROP TABLE IF EXISTS {$this->getTable('sales_quote_entity_datetime')};
00335 DROP TABLE IF EXISTS {$this->getTable('sales_quote_entity_decimal')};
00336 DROP TABLE IF EXISTS {$this->getTable('sales_quote_entity_int')};
00337 DROP TABLE IF EXISTS {$this->getTable('sales_quote_entity_text')};
00338 DROP TABLE IF EXISTS {$this->getTable('sales_quote_entity_varchar')};
00339 DROP TABLE IF EXISTS {$this->getTable('sales_quote_int')};
00340 DROP TABLE IF EXISTS {$this->getTable('sales_quote_item')};
00341 DROP TABLE IF EXISTS {$this->getTable('sales_quote_item_decimal')};
00342 DROP TABLE IF EXISTS {$this->getTable('sales_quote_item_int')};
00343 DROP TABLE IF EXISTS {$this->getTable('sales_quote_item_text')};
00344 DROP TABLE IF EXISTS {$this->getTable('sales_quote_item_varchar')};
00345 DROP TABLE IF EXISTS {$this->getTable('sales_quote_text')};
00346 DROP TABLE IF EXISTS {$this->getTable('sales_quote_varchar')};
00347 DROP TABLE IF EXISTS {$this->getTable('sales_quote_rule')};
00348 
00349 
00350 DROP TABLE IF EXISTS {$this->getTable('sales_counter')};
00351 DROP TABLE IF EXISTS {$this->getTable('sales_discount_coupon')};
00352 DROP TABLE IF EXISTS {$this->getTable('sales_giftcert')};
00353 ");
00354 $installer->endSetup();