Beyond Compare from Scooter Software<\/a> for my file \/ folder comparison work and I have opened up the two structure files for comparison.<\/p>\nNote: 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.<\/p>\n
Table: address_book:<\/p>\n
The first two fields are in reverse order and the address_book_id is now a unique auto_increment field.
\nThe address_book_id field in the old db will need to be dumped and the auto_increment value accepted.
\nentry_company_tax_id is a new field
\nentry_telephone is no longer used – checking my data they are all NULL so not required<\/p>\n
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.
\nUpdate: address_book_new is from an attempt to convert to creloaded some years ago, it is not an osCommerce table and can be ignored.<\/p>\n
Table: anti_robotreg does not exist in osCmax and is empty in the old system. Dump this.<\/p>\n
Tables: admin, etc. through to authors_info do not exist in the old system. These can be ignored.<\/p>\n
Table: banners and banners_history no change.
\nUpdate: There is data in the new system that can be cleared.<\/p>\n
Table: cache does not exist in the old. <\/p>\n
Table: categories. Need to add new fields to old to align with new.
\nafter sort_order
\n`categories_featured_until` date default NULL,
\n`categories_featured` tinyint(1) default ‘0’,
\nafter last_modified add
\n`categories_hide_from_groups` varchar(255) NOT NULL default ‘@’,<\/p>\n
In old remove the fields
\n`categories_customizable` tinyint(1) DEFAULT ‘1’,
\n`tpl_file` varchar(30) DEFAULT NULL,
\nthese are not in the new<\/p>\n
Table: categories_description
\nAdd two new fields to old to align with new
\n `categories_description` text,
\n `categories_heading_title` varchar(64) default NULL,<\/p>\n
Update: the categories column order changed to match and confirmed that data in new can be cleared.<\/p>\n
Tables: configuration, configuration_group, counter, & counter_history are the same barring some minor formatting and default settings that should not impact.<\/p>\n
Update: configuration table has a huge difference in the number of entries. The data content needs to be compared and merged rather than overwritten.<\/p>\n
Table: countries
\nAdd a new field to old to align with new
\n `active` tinyint(3) unsigned default ‘1’,<\/p>\n
Table: currencies, no change.<\/p>\n
Table: customers
\nAdd new field after customers_id
\n `purchased_without_account` tinyint(1) unsigned NOT NULL default ‘0’,<\/p>\n
Change `customers_default_address_id` int(5) to `customers_default_address_id` int(11)
\nAdd new fields after customers_newsletter
\n `customers_newsletter_type` varchar(32) NOT NULL default ‘html’,
\n `guest_account` tinyint(1) NOT NULL default ‘0’,
\n `customers_login` varchar(96) default NULL,
\n `customers_group_name` varchar(27) NOT NULL default ‘Retail’,
\n `customers_group_id` int(11) NOT NULL default ‘0’,
\n `customers_group_ra` enum(‘0′,’1’) NOT NULL default ‘0’,
\n `customers_payment_allowed` varchar(255) NOT NULL,
\n `customers_shipment_allowed` varchar(255) NOT NULL,
\n `customers_paypal_payerid` varchar(20) default NULL,
\n `customers_paypal_ec` tinyint(1) unsigned NOT NULL default ‘0’,<\/p>\n
Table: customers_basket no change.<\/p>\n
Table: customers_basket_attributes
\nadd new field after products_options_value_id
\n `products_options_value_text` varchar(32) default NULL,<\/p>\n
Tables: customers_groups is new customers_info no change.<\/p>\n
Table customers_new is in old but not new. Need to validate data in this and customers prior to export.<\/p>\n
Tables: customers_notes and wishlist related, extra fields, and feeds are only in new.<\/p>\n
Table: geozones no change, new tables for google etc only in new.<\/p>\n
Table: languages
\nAdd new field after sort_order
\n `meta_keywords` varchar(255) default NULL,<\/p>\n
Table: manufacturers
\nAdd new fields after manufacturers_image
\n `manufacturers_featured_until` date default NULL,
\n `manufacturers_featured` tinyint(1) default ‘0’,
\n `manufacturer_featured_until` date default NULL,
\n `manufacturer_featured` tinyint(1) default ‘0’,<\/p>\n
Table: manufacturers_info
\nAdd new field after manufacturers_url
\n `manufacturers_description` text,<\/p>\n
Table: orders
\nAdd new field after customers_address_format_id
\n `customers_dummy_account` tinyint(3) unsigned NOT NULL,<\/p>\n
Remove `delivery_telephone` varchar(32) DEFAULT NULL, from old table as not in new data is all null.
\nRemove `billing_telephone` varchar(32) DEFAULT NULL,<\/p>\n
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.<\/p>\n
Old field `payment_method` varchar(32) NOT NULL DEFAULT ”, is changed to
\n `payment_method` varchar(255) NOT NULL DEFAULT ”
\nNot 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.<\/p>\n
Three fields in old can be dropped as they are all empty.
\n `account_name` varchar(32) NOT NULL DEFAULT ”,
\n `account_number` varchar(20) DEFAULT NULL,
\n `po_number` varchar(12) DEFAULT NULL,<\/p>\n
New fields to add after currency_value
\n `paypal_ipn_id` int(11) NOT NULL default ‘0’,
\n `fedex_tracking` varchar(20) NOT NULL,
\n `purchased_without_account` tinyint(1) unsigned NOT NULL default ‘0’,
\n `shipping_tax` decimal(7,4) NOT NULL default ‘0.0000’,
\n `shipping_module` varchar(255) default NULL,<\/p>\n
Table: orders_pay_methods not in new. Retain in new until determining how this works.<\/p>\n
Table: orders_premade_comments not in old.<\/p>\n
Table: orders_products
\nAdd new field after `products_model`
\n `products_code` varchar(40) NOT NULL,<\/p>\n
Add new field after `products_quantity`
\n `products_stock_attributes` varchar(255) default NULL,<\/p>\n
Table: orders_products_attributes
\nAdd new field after `products_options_values`
\n `code_suffix` varchar(16) NOT NULL,<\/p>\n
Add new fields after `price_prefix`
\n `weight_prefix` char(1) NOT NULL,
\n `options_values_weight` decimal(6,3) NOT NULL default ‘0.000’,<\/p>\n
Table: orders_products_download no change.<\/p>\n
Table: orders_ship_methods not in new. appears to relate to new field in orders regarding shipping_module. Retain and test post change.<\/p>\n
Table: orders_status
\nAdd new fields after `orders_status_name`
\n `public_flag` int(11) default ‘1’,
\n `downloads_flag` int(11) default ‘0’,<\/p>\n
Tables: orders_status_history and orders_total no change<\/p>\n
Table: packaging is new<\/p>\n
Table: paypalipn_txn appears the same \/ similar to paypal_ipn. Will need to look at data changes.<\/p>\n
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.<\/p>\n
Table: pm_configuration is new.<\/p>\n
Table: products is also complex in the changes. Need to review.<\/p>\n
Table: products_attributes
\nAdd new fields after `options_values_id`
\n `code_suffix` varchar(16) NOT NULL,
\n `suffix_sort_order` int(4) NOT NULL default ‘0’,
\nand Add new fields after `price_prefix`
\n `weight_prefix` char(1) NOT NULL,
\n `options_values_weight` decimal(6,3) NOT NULL default ‘0.000’,
\n `products_options_sort_order` int(10) unsigned NOT NULL default ‘0’,<\/p>\n
Table: products_attributes_download no change<\/p>\n
Table: products_description
\nAdd new field after `products_name`
\n `products_short` text,<\/p>\n
Add new fields after `products_description` text,
\n `tab1` text,
\n `tab2` text,
\n `tab3` text,
\n `tab4` text,
\n `tab5` text,
\n `tab6` text,<\/p>\n
Table: products_groups is new<\/p>\n
Table: products_notifications no change<\/p>\n
Table: products_options<\/p>\n
Add new fields after `products_options_name`
\n `products_options_track_stock` tinyint(4) NOT NULL default ‘0’,
\n `products_options_type` int(5) NOT NULL default ‘0’,
\n `products_options_length` smallint(2) NOT NULL default ’32’,
\n `products_options_comment` varchar(32) default NULL,
\n `products_options_sort_order` int(10) unsigned NOT NULL default ‘0’,<\/p>\n
Table: products_options_products is not in new.<\/p>\n
Table: products_options_description is new<\/p>\n
Tables: products_options_values, products_options_values_to_products_options are the same<\/p>\n
Tables: products_price_break & products_stock are new<\/p>\n
Table: products_to_categories is the same<\/p>\n
Tables: products_to_discount_categories, products_xsell, & quick_links are new<\/p>\n
Table: reviews
\nAdd new field after `reviews_rating`
\n `approved` tinyint(3) unsigned default ‘0’,<\/p>\n
Tables: scart, all search*, shipping_manifest and slideshow are new.<\/p>\n
Table: sessions no change.<\/p>\n
Table: specials
\nAdd new field after `status`
\n `customers_group_id` int(11) NOT NULL default ‘0’,<\/p>\n
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.<\/p>\n
Table: specials_retail_prices is new<\/p>\n
Tables: tax_class, tax_rates no change<\/p>\n
Table: theme_configuration is new<\/p>\n
Table: tep_meta_tags is not in new<\/p>\n
Tables: topics* and usu_cache are new<\/p>\n
Table: whos_online
\nAdd new fields after `ip_address`
\n `hostname` varchar(255) NOT NULL,
\n `country_code` varchar(2) NOT NULL,
\n `country_name` varchar(64) NOT NULL,
\n `region_name` varchar(64) NOT NULL,
\n `city` varchar(64) NOT NULL,
\n `latitude` float NOT NULL,
\n `longitude` float NOT NULL,<\/p>\n
Add new fields after `last_page_url` text NOT NULL,
\n `http_referer` varchar(255) NOT NULL,
\n `user_agent` varchar(255) NOT NULL,<\/p>\n
Tables: zones* are no change<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,12],"tags":[],"class_list":["post-699","post","type-post","status-publish","format-standard","hentry","category-mysql","category-oscommerce-oscmax"],"_links":{"self":[{"href":"https:\/\/howden.net.au\/thowden\/wp-json\/wp\/v2\/posts\/699","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/howden.net.au\/thowden\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/howden.net.au\/thowden\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/howden.net.au\/thowden\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/howden.net.au\/thowden\/wp-json\/wp\/v2\/comments?post=699"}],"version-history":[{"count":0,"href":"https:\/\/howden.net.au\/thowden\/wp-json\/wp\/v2\/posts\/699\/revisions"}],"wp:attachment":[{"href":"https:\/\/howden.net.au\/thowden\/wp-json\/wp\/v2\/media?parent=699"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/howden.net.au\/thowden\/wp-json\/wp\/v2\/categories?post=699"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/howden.net.au\/thowden\/wp-json\/wp\/v2\/tags?post=699"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}