I am in the process of converting some older osCommerce based web sites to osCmax and the underlying database structure is different. The following is the step by step that I have worked through for one site.
Updated: 24th Aug 2011: Embedded updates for tables in the following as I worked through the conversion.
In preparing for this I have exported the structure only from the old site and from the current osCmax svn version that I have running on my system. I use Beyond Compare from Scooter Software for my file / folder comparison work and I have opened up the two structure files for comparison.
Note: the old osCommerce is based on an early 2.1 version updated and modified for unique requirements over the 10 years or so it has been in operation. It’s been a reluctance to even start contemplating this change that has led it to be so long.
Table: address_book:
The first two fields are in reverse order and the address_book_id is now a unique auto_increment field.
The address_book_id field in the old db will need to be dumped and the auto_increment value accepted.
entry_company_tax_id is a new field
entry_telephone is no longer used – checking my data they are all NULL so not required
Tables: address_book and address_book_new both exist in the old osCommerce database but not in osCmax. Both tables are populated so I’ll need to compare the data in the two.
Update: address_book_new is from an attempt to convert to creloaded some years ago, it is not an osCommerce table and can be ignored.
Table: anti_robotreg does not exist in osCmax and is empty in the old system. Dump this.
Tables: admin, etc. through to authors_info do not exist in the old system. These can be ignored.
Table: banners and banners_history no change.
Update: There is data in the new system that can be cleared.
Table: cache does not exist in the old.
Table: categories. Need to add new fields to old to align with new.
after sort_order
`categories_featured_until` date default NULL,
`categories_featured` tinyint(1) default ‘0’,
after last_modified add
`categories_hide_from_groups` varchar(255) NOT NULL default ‘@’,
In old remove the fields
`categories_customizable` tinyint(1) DEFAULT ‘1’,
`tpl_file` varchar(30) DEFAULT NULL,
these are not in the new
Table: categories_description
Add two new fields to old to align with new
`categories_description` text,
`categories_heading_title` varchar(64) default NULL,
Update: the categories column order changed to match and confirmed that data in new can be cleared.
Tables: configuration, configuration_group, counter, & counter_history are the same barring some minor formatting and default settings that should not impact.
Update: configuration table has a huge difference in the number of entries. The data content needs to be compared and merged rather than overwritten.
Table: countries
Add a new field to old to align with new
`active` tinyint(3) unsigned default ‘1’,
Table: currencies, no change.
Table: customers
Add new field after customers_id
`purchased_without_account` tinyint(1) unsigned NOT NULL default ‘0’,
Change `customers_default_address_id` int(5) to `customers_default_address_id` int(11)
Add new fields after customers_newsletter
`customers_newsletter_type` varchar(32) NOT NULL default ‘html’,
`guest_account` tinyint(1) NOT NULL default ‘0’,
`customers_login` varchar(96) default NULL,
`customers_group_name` varchar(27) NOT NULL default ‘Retail’,
`customers_group_id` int(11) NOT NULL default ‘0’,
`customers_group_ra` enum(‘0′,’1’) NOT NULL default ‘0’,
`customers_payment_allowed` varchar(255) NOT NULL,
`customers_shipment_allowed` varchar(255) NOT NULL,
`customers_paypal_payerid` varchar(20) default NULL,
`customers_paypal_ec` tinyint(1) unsigned NOT NULL default ‘0’,
Table: customers_basket no change.
Table: customers_basket_attributes
add new field after products_options_value_id
`products_options_value_text` varchar(32) default NULL,
Tables: customers_groups is new customers_info no change.
Table customers_new is in old but not new. Need to validate data in this and customers prior to export.
Tables: customers_notes and wishlist related, extra fields, and feeds are only in new.
Table: geozones no change, new tables for google etc only in new.
Table: languages
Add new field after sort_order
`meta_keywords` varchar(255) default NULL,
Table: manufacturers
Add new fields after manufacturers_image
`manufacturers_featured_until` date default NULL,
`manufacturers_featured` tinyint(1) default ‘0’,
`manufacturer_featured_until` date default NULL,
`manufacturer_featured` tinyint(1) default ‘0’,
Table: manufacturers_info
Add new field after manufacturers_url
`manufacturers_description` text,
Table: orders
Add new field after customers_address_format_id
`customers_dummy_account` tinyint(3) unsigned NOT NULL,
Remove `delivery_telephone` varchar(32) DEFAULT NULL, from old table as not in new data is all null.
Remove `billing_telephone` varchar(32) DEFAULT NULL,
Old field `cc_ccv` varchar(3) DEFAULT NULL COMMENT ‘CCV 3 Digits’, is not in new but seems like a necessary field. Need to include for the moment and dump later if not required. Some values are present.
Old field `payment_method` varchar(32) NOT NULL DEFAULT ”, is changed to
`payment_method` varchar(255) NOT NULL DEFAULT ”
Not sure how this interacts with payment processes. Will retain field as payment_method_old leaving the new field as payment_method. Refer to table orders_pay_methods which is also retained and linked to this field but is not in the new structure.
Three fields in old can be dropped as they are all empty.
`account_name` varchar(32) NOT NULL DEFAULT ”,
`account_number` varchar(20) DEFAULT NULL,
`po_number` varchar(12) DEFAULT NULL,
New fields to add after currency_value
`paypal_ipn_id` int(11) NOT NULL default ‘0’,
`fedex_tracking` varchar(20) NOT NULL,
`purchased_without_account` tinyint(1) unsigned NOT NULL default ‘0’,
`shipping_tax` decimal(7,4) NOT NULL default ‘0.0000’,
`shipping_module` varchar(255) default NULL,
Table: orders_pay_methods not in new. Retain in new until determining how this works.
Table: orders_premade_comments not in old.
Table: orders_products
Add new field after `products_model`
`products_code` varchar(40) NOT NULL,
Add new field after `products_quantity`
`products_stock_attributes` varchar(255) default NULL,
Table: orders_products_attributes
Add new field after `products_options_values`
`code_suffix` varchar(16) NOT NULL,
Add new fields after `price_prefix`
`weight_prefix` char(1) NOT NULL,
`options_values_weight` decimal(6,3) NOT NULL default ‘0.000’,
Table: orders_products_download no change.
Table: orders_ship_methods not in new. appears to relate to new field in orders regarding shipping_module. Retain and test post change.
Table: orders_status
Add new fields after `orders_status_name`
`public_flag` int(11) default ‘1’,
`downloads_flag` int(11) default ‘0’,
Tables: orders_status_history and orders_total no change
Table: packaging is new
Table: paypalipn_txn appears the same / similar to paypal_ipn. Will need to look at data changes.
There are many fields in old and not in new and vice-versa. Trying to match the data will be time-consuming. Need to assess if this needs to be done or not. Retain old table as itself for the time being.
Table: pm_configuration is new.
Table: products is also complex in the changes. Need to review.
Table: products_attributes
Add new fields after `options_values_id`
`code_suffix` varchar(16) NOT NULL,
`suffix_sort_order` int(4) NOT NULL default ‘0’,
and Add new fields after `price_prefix`
`weight_prefix` char(1) NOT NULL,
`options_values_weight` decimal(6,3) NOT NULL default ‘0.000’,
`products_options_sort_order` int(10) unsigned NOT NULL default ‘0’,
Table: products_attributes_download no change
Table: products_description
Add new field after `products_name`
`products_short` text,
Add new fields after `products_description` text,
`tab1` text,
`tab2` text,
`tab3` text,
`tab4` text,
`tab5` text,
`tab6` text,
Table: products_groups is new
Table: products_notifications no change
Table: products_options
Add new fields after `products_options_name`
`products_options_track_stock` tinyint(4) NOT NULL default ‘0’,
`products_options_type` int(5) NOT NULL default ‘0’,
`products_options_length` smallint(2) NOT NULL default ’32’,
`products_options_comment` varchar(32) default NULL,
`products_options_sort_order` int(10) unsigned NOT NULL default ‘0’,
Table: products_options_products is not in new.
Table: products_options_description is new
Tables: products_options_values, products_options_values_to_products_options are the same
Tables: products_price_break & products_stock are new
Table: products_to_categories is the same
Tables: products_to_discount_categories, products_xsell, & quick_links are new
Table: reviews
Add new field after `reviews_rating`
`approved` tinyint(3) unsigned default ‘0’,
Tables: scart, all search*, shipping_manifest and slideshow are new.
Table: sessions no change.
Table: specials
Add new field after `status`
`customers_group_id` int(11) NOT NULL default ‘0’,
Table: suppliers is custom to the old system. links in products and currencies need to be managed. relates to proposed module to manage outbound purchase orders and product costing.
Table: specials_retail_prices is new
Tables: tax_class, tax_rates no change
Table: theme_configuration is new
Table: tep_meta_tags is not in new
Tables: topics* and usu_cache are new
Table: whos_online
Add new fields after `ip_address`
`hostname` varchar(255) NOT NULL,
`country_code` varchar(2) NOT NULL,
`country_name` varchar(64) NOT NULL,
`region_name` varchar(64) NOT NULL,
`city` varchar(64) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
Add new fields after `last_page_url` text NOT NULL,
`http_referer` varchar(255) NOT NULL,
`user_agent` varchar(255) NOT NULL,
Tables: zones* are no change
Next is to build a script to make the structure changes and then extend it to convert the data. I need this in order to do some trial runs before a live cut-over. It will be useful for the creLoaded stores that I need to do as well.