PDA

View Full Version : Appending two OSCommerce (or variant) databases:



kenlyle
10-06-2005, 03:31 AM
This is strictly YMMV (Your mileage may vary) documentation!!!
I tried to contribute it to OSCommerce's forums, but it got snipped because it mentions OSC variants.

In my scenario, I had a (special project) store/database (1) that was created in CRELoaded, and my main production store/database that was in OSCMax 1.7 (2). When I first did the upgrade to OSCMax, I noticed that there was actually CRE advertising in the files, and that the tables were almost identical. I did this by diffing the .sql files.

Unfortunately, the entire special project database has junk records, it includes all orders and customers from before the special project.

SO, what do I need from the (1) special project store?

Relevant:
-A)Customers and related records in Address Book
-B)Orders, Orders Products, Products, Products Description (I'll clean up any category assignment issues in the production database, saves trying to transfer 3 category related tables)
================

A) Found the most likely place where customer numbers in 1 diverge from those in (2): Customer IDs 204 and higher need to be moved over to 2.

Max Customer ID in my production database was 278. A little SQL, and I'd made a copy of customers to customers_new in (1), deleted all the customers 203 and below, and incremented the customer_id of remainder by 100.

Export the file, and run against the production database. Now we have the new customers.

Same with address_book->address_book_new, delete the old dupe rows, increment the customer ids to keep them matching: Update `address_book_new` set `customers_id`=`customers_id`+100;

except that address_book_id is up to 312 in the production database (2)...increment that field in `address_book_new` to not collide: Update `address_book_new` set `address_book_id`=`address_book_id`+100;

Export this file, and we have address entries for the new customers,

================================================== ================================================== =====================

B) Copied the Orders Table to Orders_new; incremented the ids of the 'new' customers, and left the 'old' ones alone:
Update `orders_new` set `customers_id`=`customers_id`+100 where `customers_id`>= 204;
Check...

Drop the unneeded rows from products and products_description, renaming to _new, respectively:
DElete FROM `products_description_new` WHERE `products_id`<105; increment the remainder by 10:
update `products_description_new` set `products_id`=`products_id`+10;
Had to remove 3 fields from the exported table to make it shoehorn into the aabox production table. CRE Loaded had a few more fields.

Update Product IDs....
Same for products.........update `products_new` set `products_id`=`products_id`+10; had to remove some fields ....

Same Procedure for orders_products, delete where the order ids aren't of interest,
update the product_ids, update the orders_products_id; update past 721

Need Orders_Total table, too....Orders_Total Ids must be over 1002, so do a query to increment them...

Hope this helps someone, somehow....

kenlyle
10-06-2005, 05:59 AM
also,

Had to update default_address_id in Customers table to match the incremented values in address_book table.

kenlyle
10-06-2005, 07:20 AM
need to move Orders_status_history

orders_status_history_id is up to 781 in main database. Starts at 546 through 600 in Perfect Health, increment them by 250...

and import into production database