Lichtman Consulting

Nathan did a nice article on tools and tricks for creating new sites in Drupal. Worth a read: http://bomshteyn.com/2009/12/29/couple-of-things-i-learned-while-creating-sites-in-drupal/

The following blog entry describes the solution to an issue encountered by Jeremy and Nathan in moving data from an old website to a new one. Based on a brief search for a solution, it looks like many other people may find a use for the (admittedly rather crude) code that we wrote to solve the problem. We hope this helps!

We’ve recently had to convert data from an old custom-built shopping cart, to a new website based on Drupal and Ubercart. The old website had approximately 5000 products, with a data structure that was completely unlike the one used by Drupal to store data. We initially tried using the Drupal data loader module, but as other people have discovered, it isn’t necessarily a good fit for loading Ubercart product data, particularly when the products have images, or – and this is especially the case – custom data fields.

You can download a copy of our script here.

In the end, we put together a custom php script to pull data directly from one database to another. The script is fairly crude, but it may be useful to somebody trying to import data into Ubercart from another non-standard system.

As a result, I will link a copy of the script below. Please bear in mind that a) we accept no responsibility  whatsoever for its use (i.e. back up your databases first!!!), and b) while we have documented our assumptions, this is a crude script that is specifically designed for our particular needs. Be that as it may, you may be able to find a use for this.

Additionally, it was a pain just getting a list of the Drupal and Ubertcart tables that needed to be looked at in order to do the data conversion. The list below may be useful to you as well.

How it works:

The old shopping cart had two relevant tables: a category listing (hierarchical), and a products table, which included some customer specific fields, as well as a single url to a product photo.

Drupal/Ubercart, on the other hand, uses a large number of tables to represent the same data. This is because products use the same node-based system as the rest of Drupal, in addition to a number of product specific tables.

In addition, product images are dealt with using Drupal’s file cacheing system, which is relatively complex in nature. All in all, our data goes from two tables to approximately nine (depending on your specific needs) in Drupal.

The script has some handling – very buggy still – in place for keeping track of the current position in the old database. This means that you can run the data conversion in small chunks, checking at each point to see how well it is doing. You can also set how many records to convert each time. In order for this to work, a very simple table to track the record pointer needs to be added into the old database.

We did not cover the conversion of product categories, since typically there are a much smaller number of categories than products, and in addition, the Drupal data loader does a reasonably good job of loading them.

List of tables involved:

• node – in Drupal, everything is a node, including products. Each new product record will need a corresponding node of the correct type.
• node_revisions – this table contains much of the actual textual data (i.e. product description text) involved in displaying the node.
• content_type_product – if you need to create custom fields for your Ubercart products, the data will go here. Our code in this section will probably not map directly to what you’re working on.
• uc_product – where the product records “live”. There will be one record here for each of the product nodes.
• uc_product_stock – contains stock-related data (i.e. how many of the product you have on hand etc) for products.
• files – each product image needs to have a record in this table, which stores the file names and locations – but does not map these images directly to the product records (you need another table for that!).
• content_field_image_cache – this is the mechanism that ties a product image to the underlying product. We didn’t really try to work with Drupal’s actual cache mechanism, so after you have loaded the products, you may want to clear the cache.
• term_data – this is where product category information resides, based on Drupal’s vocabulary / hierarchy system. We hacked the solution in a fairly inelegant way – by doing a category name lookup to retrieve the term id.
• term_node – a connection table between a term (i.e. a product category) and a node (in this case the products).

Base assumptions:

• We assumed that the old and new websites both have mysql databases.
• Both databases will probably have to be on the same server (or at least one of them needs to be externally accessible if that isn’t the case)
• The script should be uploaded and run from the new website’s location.
• We assumed that there is only one product image per product – although this would be fairly easy to modify.
• We manually added our categories to the new website
• We manually moved product images across – there are comments in the script where you could automate this though.
• Error checking is extremely rudimentary; if this scripts hits a problem it just stops.
• If you hit data conv snags, the fastest way to proceed is to restore your backup database, reset the pointer record, and start over.

I’ve finally taken Nathan’s advice and installed Disqus to handle the comments on this site. Please let me know if you notice anything broken as a result. Supposedly there are some nice features, including the ability to leave video comments.