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....
Import Taxonomies
-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"));For terms without parent, we must linked with themself
INSERT INTO term_hierarchy( tid, parent )
SELECT td.tid,0
FROM term_data td
WHERE td.tid NOT IN (SELECT th.tid FROM term_hierarchy th)If you use pathauto module remember run Bulk generate aliases for terms that are not aliased
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.
Import Nodes
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 ,log)
SELECT jos_content.id, 2 AS Expr2, jos_content.title, jos_content.introtext,
concat(jos_content.introtext,"<br>",jos_content.fulltext) AS Expr3, "Imported from joomla"
FROM joomla.jos_content;Userid will be 2 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;This importation don't handle i18n support, if you need set the language field with 'en' i.e:
INSERT INTO node ( vid, type, title, uid,language )
SELECT mos_content.id, "story" AS Expr4, mos_content.title, 2 AS Expr2, 'en'
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.
Import Joomla/Mambo Modules as Nodes
Sometimes Joomla used modules to represent content, for this reason depends of Joomla/Mambo implementation could be a good idea import modules with content and published.
INSERT INTO drupal.node_revisions(vid,uid,title,body,log)
SELECT id+57,2,title,content,'Imported from joomla'
FROM joomla.jos_modules
where jos_modules.published = 1 and jos_modules.content != ''INSERT INTO drupal.node ( vid, type, title, uid )
SELECT id+57,"story",title,2
FROM joomla.jos_modules
where jos_modules.published = 1 and jos_modules.content != ''Import Users
INSERT INTO users(name,pass,mail,status)
SELECT ju.name,ju.email,ju.password,1
FROM joomla.jos_users juAll users well be active, change the constant 1 to 0 if you want import as disabled users