By kzeng on

Since Drupal 4.7 will provide the free tagging function, I am thinking to convert my current Awtags into free tagging after upgrading to drupal 4.7. I guess that one way to do that is to create a new vocabulary (say, free tags), and then use some MySQL scripts to import&create terms for the vocabulary from Awtags table. But I am not sure how to do it gracefully. Has anybody already done this? Could you give me some help? Thanks a ton!

⋅ Categories: Drupal 4.7.x

Comments

Hybernaut’s picture

Hybernaut commented

The schemas are really similar, so this is straightforward:

I created a new vocabulary called 'tags' in the taxo module.
Check its id this way:

mysql> select vid,name from vocabulary;
   1 | topic
   2 | tags

In this example, I use 2 for 'tags'.
Next check and remember the highest term_data.tid:

mysql> select max(term_data.tid) from term_data;
  8

Then add that plus (at least) 1 to all the tid's from the awtags as you copy them in the taxo term_data table:

mysql> insert into term_data (tid,vid,name) select 10+awtags.tid, 2, awtags.tag from awtags;
Query OK, 216 rows affected (0.02 sec)
Records: 216  Duplicates: 0  Warnings: 0

Then copy the tags on the nodes, adding the same number again:

mysql> insert into term_node (tid,nid) select 10+awtags_node.tid,awtags_node.nid from awtags_node;
Query OK, 520 rows affected (0.03 sec)
Records: 520  Duplicates: 0  Warnings: 0

Once you're satisfied that the tags were copied correctly, you can drop the old awtags tables.

Brian Del Vecchio | bdv at hybernaut dot com | http://hybernaut.com/

kzeng’s picture

kzeng commented

Thanks a lot. I really appreciate it! :-)

--------------------------
http://www.kzeng.info

kzeng’s picture

kzeng commented

I found another problem when I attempted to do the coversion. The awtags table's char set and collation are both latin1_swedish_ci. So if I directly inserted tags into term_data table from awtags table, some characters were messed up because the char set and collation of term_data table are utf8_general-ci (English is OK, but non-Western languages have the problem) . So i am wondering how to solve the problem. i tried to change the char set and collation of awtags table before inserting its tags into term_data table using the following syntax:

ALTER TABLE `awtags` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

But it didn't work. The characters were still messed up. Could anybody give me a help? Thanks a lot!

--------------------------
http://www.kzeng.info

kzeng’s picture

kzeng commented

I know what's wrong. I should convert the table to binary before converting it to UTF-8.

--------------------------
http://www.kzeng.info

Hybernaut’s picture

Hybernaut commented

OK, one thing I missed in the above instructions. You need to update the id in the sequences table so that db_next_id(term_data) will work properly.

After you've completed the above, determine the max value, and update the sequences table:

mysql> select max(tid) from term_data;
      226 
mysql> replace into sequences values ('term_data_tid', 226);
Query OK, 1 rows affected (0.20 sec)

Brian Del Vecchio | bdv at hybernaut dot com | http://hybernaut.com/

kzeng’s picture

kzeng commented

Thanks!

--------------------------
http://www.kzeng.info

Hybernaut’s picture

Hybernaut commented

Oh yes, demonstrating that I don't fully grok the taxo module.

the other thing you need to do, after the above steps, for vocabulary

First, find the free tagging vocabulary:

mysql> select vid, name from vocabulary where tags=1;
   2 | tags

Now add rows to hierarchy for each imported tag in that vocab:

mysql> insert into term_hierarchy select tid, 0 from term_data where vid=2;

Now all the imported tags should show up in /admin/taxonomy/2

Brian Del Vecchio | bdv at hybernaut dot com | http://hybernaut.com/