Database Queries and Procedure for Migrating to Drupal

Here is a database based procedure for porting Joomla to Drupal. It's got plusses (you can do tricks like concatenate fields, or change the case, do joins, or use other database functions) and minuses (perhaps not as simple as using a module, export and import).

I used Access, and created a new database, and an ODBC connection to my Drupal (test) database.
Then Import-Link Tables, and select all the tables. Answer some questions about which are the key fields of certain tables, and you will be able to browse your Drupal database.

First, create a vocabulary, i.e. vid=1

Here are some of the queries I used:

The bizarre where clauses are just because the Joomla data is
perverted...some of the categories reference sections (which should be
numeric section IDs) like "com_weblinks", so ignore those....

-Insert the Sections into Term_data:
INSERT INTO term_data ( vid, name, description )
SELECT 1 AS Expr1, mos_sections.name, mos_sections.description
FROM mos_sections;

(the vid is a constant, 1)

Then insert the Sections into the Hierarchy, with Parent 0:
Sorry, overwrote it by accident....

Insert the Categories into the Term_data table:
INSERT INTO term_data ( vid, name, description )
SELECT 1 AS Expr1, mos_categories.name, mos_categories.description
FROM mos_categories
WHERE (((mos_categories.section)<"a" And
(mos_categories.section)<>"12" And (mos_categories.section)<>"7"));

Again, vid is a constant, 1

-Insert the Categories into the Hierarchy
INSERT INTO term_hierarchy ( tid, parent )
SELECT term_data.tid, mos_categories.section
FROM term_data INNER JOIN mos_categories ON term_data.name =
mos_categories.name
WHERE (((term_data.tid)>7) AND ((mos_categories.section)<"a" And
(mos_categories.section)<>"12" And (mos_categories.section)<>"7"));

This gets the termid and the parent termid, which is what goes into the hierarchy.

If your Sections have moved around, you may have some Categories
associated with the wrong section. It may be easier to just re-number
(update the ID of the Section) rather than update all of the
categories, which will likely be more numerous. Since the IDs are in
a unique key, say you are swapping 1 and 3, make 1 like 999 or
somehthing, then switch the other one 3, to 1, then put 999 to 3, and
when you refresh your Categories, things should line up better.

Appending the Content to nodes_revisions per the node 80195, is really easy, once you realize that vid in this case is not the vocabularyid, but is a revisionid that must be unique across the entire database.

That query looks like this:

INSERT INTO node_revisions ( vid, uid, title, teaser, body )
SELECT mos_content.id, 2 AS Expr2, mos_content.title, mos_content.introtext, [introtext] & "
" & [fulltext] AS Expr3
FROM mos_content;

2 is just the userid to which you want to assign the content.

But nid in nodes_revisions is not autoincrement, which seems odd.

You can do essentially the same insert on nodes:

INSERT INTO node ( vid, type, title, uid )
SELECT mos_content.id, "story" AS Expr4, mos_content.title, 2 AS Expr2
FROM mos_content;

Then you just have to get the nodeid (nid) updated in node_revisions, which is easier, because we used the same id field from mos_content as the vid in both tables, so we use that to join the tables, and updated the node_revisions.nid with the node.nid:

UPDATE node INNER JOIN node_revisions ON node.vid = node_revisions.vid SET node_revisions.nid = [node].[nid];

There may be an issue with the vids being the same...Pro Drupal Development, p. 84, says vid has to be unique across nodes AND node revisions, so we may have to add 1000 or something to make them unique....haven't gotten there yet.