Migrating an OSCommerce shop to Drupal ubercart

Here are my notes from migrating an OSCommerce installation to a Drupal ubercart system.

Firstly dump all the products with their names and ids from the OScommerce database. Then import this data with the node import module. I've imported the id field into the description because at this point in time I couldn't figure where the sku was stored.

mysql -e "select p.products_id, products_name 
from products p, products_description pd 
where p.products_id=pd.products_id" -N oscommerce >ids.txt

Switch to the Drupal database now.

Then grab the prices from oscommerce and update the respective table in Drupal ubercart. Note that OSCommerce stores net prices, hence the rounding and truncating.

update uc_products ucp, oscommerce.products p, 
  oscommerce.products_description pd, node_revisions n 
  set sell_price= truncate(round(products_price + (products_price/100*19)),2)  
  where p.products_id=pd.products_id and pd.products_id=n.body and ucp.nid=n.nid ;

Now move the sku from description to the model field (aka sku). We'll use this information again and again.

update uc_products ucp,node_revisions n set model= n.body  where ucp.nid=n.nid ;

Then migrate the descriptions from oscommerce.

update uc_products ucp, oscommerce.products p, 
  oscommerce.products_description pd, node_revisions n 
  set n.body=pd.products_description  
  where p.products_id=pd.products_id 
  and pd.products_id=n.body 
  and ucp.nid=n.nid ;

Now migrate all the category information from OSCommerce to Ubercart.

Grab the vocabulary ids from Drupal

select t.* 
  from term_node r inner join term_data t on r.tid = t.tid 
  inner join vocabulary v on t.vid = v.vid 
  where r.nid = 1 order by v.weight, t.weight, t.name;

And then migrate the categories by inserting new rows into term_node. I'm lazy here and there are only a handful of categories, so I did this in a couple of manual steps.

CDs

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (35,38,24,34,36,37,41);

LPs

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (24,32,33,34,36,37,39,40,41);

T-Shirts

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (21,47);

Buttons

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (22);

Patches

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (23);

DVD & VHS

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (29);

Tapes

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (25);

Books & Fanzines

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (28);

10% 10

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (27);

Trade list

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (42);

Posters

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (46);

Demo CDRs

insert into term_node 
  select distinct n.nid, 3 from 
  node_revisions n, uc_products ucp, oscommerce.products_to_categories ptc  
  where ucp.nid=n.nid
  and ptc.products_id=ucp.model 
  and ptc.categories_id in (48);

A lot of the data was wrongly categorized, so I am doing some manual cleaning here.

delete from term_node where tid=2 and nid in (select nid from node where title like "%CD");
delete from term_node where tid=2 and nid in (select nid from node where title like "%Ep");
delete from term_node where tid=2 and nid in (select nid from node where title like '%7"');
delete from term_node where tid=2 and nid in (select nid from node where title like '%10"');
delete from term_node where tid=2 and nid in (select nid from node where title like '%tape');

delete from term_node where tid=3 and nid in (select nid from node where title like "%LP");
delete from term_node where tid=3 and nid in (select nid from node where title like "%Ep");
delete from term_node where tid=3 and nid in (select nid from node where title like '%7"');
delete from term_node where tid=3 and nid in (select nid from node where title like '%10"');
delete from term_node where tid=3 and nid in (select nid from node where title like '%tape');

insert into term_node select distinct n.nid,10 from node n 
where title like '%10"' and nid not in (select nid from term_node where tid=10);

insert into term_node select distinct n.nid,1 from node n 
where title like '%7"' and nid not in (select nid from term_node where tid=1);
insert into term_node select distinct n.nid,1 from node n 
where title like '%Ep' and nid not in (select nid from term_node where tid=1);

insert into term_node select distinct n.nid,2 from node n 
where title like '%LP%' and nid not in (select nid from term_node where tid=2);
insert into term_node select distinct n.nid,3 from node n 
where title like '%CD%' and nid not in (select nid from term_node where tid=3);
insert into term_node select distinct n.nid,8 from node n 
where title like '%tape%' and nid not in (select nid from term_node where tid=8);

Now migrate the manufacturers to the manufacturers category in Drupal ubercart

insert into term_data (vid, name) select distinct 1, manufacturers_name from oscommerce.manufacturers;
insert into term_hierarchy select tid, 0 from term_data where vid=1;

Assign the correct category to the drupal node

insert into term_node
select ucp.nid, td.tid
from  oscommerce.manufacturers mf, oscommerce.products p, 
uc_products ucp,node_revisions n, term_data td
where mf.manufacturers_id = p.manufacturers_id
and ucp.model=p.products_id
and ucp.nid=n.nid
and td.name=mf.manufacturers_name;

connect uc_manufacturers to drupal's categories

insert into uc_manufacturers (tid) select tid from term_data where vid=1;

correct the sequences

select max(tid) from term_data;
update sequences set id=213 where name='term_data_tid';

Then copy all the images from the old site to the files/images directory.

9708 is what is in the sequences table at the moment, so I am using this as a starting point to generate unique ids.

insert into files (fid, nid, filename, filepath, filemime)
select 9708+p.products_id, n.nid, p.products_image, 
concat("/files/images/", p.products_image) as filepath , 'image/jpeg'
from oscommerce.products p, uc_products ucp,node_revisions n
 where p.products_image is not null
and ucp.model=p.products_id
and ucp.nid=n.nid
and (products_image like '%jpg'
or products_image like '%jpeg'
or products_image like '%JPG'
or products_image like '%JPEG');

insert into files (fid, nid, filename, filepath, filemime)
select 9708+p.products_id, n.nid, p.products_image, 
concat("/files/images/", p.products_image) as filepath , 'image/gif'
from oscommerce.products p, uc_products ucp,node_revisions n
 where p.products_image is not null
and ucp.model=p.products_id
and ucp.nid=n.nid
and (products_image like '%gif'
or products_image like '%GIF');

Then insert the file information into content_field_image_cache

insert into content_field_image_cache
select distinct nid, 0, nid, fid, replace(filepath, "files/", ""), replace(filepath, "files/", "") from files ;

And correct the sequence.

update sequences set id=12000 where name='files_fid';

It is very likely that I'll need to correct more sequences and migrate a little more data. Note: I didn't and will not migrate user or order data.