Thursday, April 24, 2014
   
Text Size
Login

Migrate JComments from Joomla 1.5.x to Joomla 2.5.x

Thought I'd add the migration script I've been using to test my JComments migration from my Joomla CMS site version 1.5.20 to Joomla CMS site version 2.5.6.

DISCLAIMER

  1. I do not work for either Joomla or JoomlaTune (Jcomments)
  2. This script is provided as is without warranty
  3. If you do not understand what this script is doing, let someone who does run it instead

INSTALL INSTRUCTIONS

  1. Install JComments on your upgraded website (at time of print: Jcomments v2.3.0 on Joomla v2.5.4)
  2. Copy the below script to a text file
    1. Change the database names to match your setup
    2. Change the table name prefixes to match your setup
  3. Run the modified SQL script against your database.
Note: It is not advisable to run this on a production website!

  1.  -- -------------------------------------------------------------------------------- 
  2.  -- MIGRATION SCRIPT FOR JCOMMENTS IN JOOMLA v1.5.x TO JOOMLA v2.5.x 
  3.  -- -------------------------------------------------------------------------------- 
  4.   
  5.  -- NOTE: Replace string "mydb_livecopy_j15" to your Joomla 1.5.x database name 
  6.  -- -- -- (example: "anolddatabase") -- 9 occurrences 
  7.  -- NOTE: Replace string "mydb_livecopy_j15.myprefix_" to your Joomla 1.5.x database name & table prefix 
  8.  -- -- -- (example: "anolddatabase.jos_") -- 8 occurrences 
  9.   
  10.  -- NOTE: Replace string "mydb_upgrade_j25" to your Joomla 2.5.x database name 
  11.  -- -- -- (example: "anewdatabase") -- 9 occurrences 
  12.  -- NOTE: Replace string "mydb_upgrade_j25.myprefix_" to your Joomla 2.5.x database name & table prefix 
  13.  -- -- -- (example: "anewdatabase.rndpfx1_") -- 8 occurrences 
  14.   
  15.  -- -------------------------------------------------------------------------------- 
  16.  -- _jcomments 
  17.  -- -------------------------------------------------------------------------------- 
  18.   
  19.  INSERT INTO 
  20.       mydb_upgrade_j25.myprefix_jcomments 
  21.       ( 
  22.            id, parent, thread_id, path, level, object_id, object_group, object_params, lang, 
  23.            userid, name, username, email, homepage, title, comment, ip, date, isgood, ispoor, 
  24.            published, subscribe, source, source_id, checked_out, checked_out_time, editor 
  25.       ) 
  26.  SELECT 
  27.       id, parent, 
  28.       CASE WHEN SUBSTRING(path, LOCATE(',', path)+1, LOCATE(',', path, 2))='' THEN 0 
  29.       ELSE SUBSTRING(path, LOCATE(',', path)+1, LOCATE(',', path, 2)) END AS thread_id, 
  30.       path, level, object_id, object_group, object_params, lang, 
  31.       userid, name, username, email, homepage, title, comment, ip, date, isgood, ispoor, 
  32.       published, subscribe, source, source_id, checked_out, checked_out_time, editor 
  33.  FROM 
  34.       mydb_livecopy_j15.myprefix_jcomments; 
  35.   
  36.   
  37.  -- -------------------------------------------------------------------------------- 
  38.  -- _jcomments_custom_bbcodes 
  39.  -- -------------------------------------------------------------------------------- 
  40.   
  41.  -- NOTE: This is disabled as the new version includes the old bbcodes setup (no change) 
  42.   
  43.  -- INSERT INTO 
  44.  --      mydb_upgrade_j25.myprefix_jcomments_custom_bbcodes 
  45.  --      ( 
  46.  --           id, `name`, simple_pattern, simple_replacement_html, simple_replacement_text, pattern, 
  47.  --           replacement_html, replacement_text, button_acl, button_open_tag, button_close_tag, 
  48.  --           button_title, button_prompt, button_image, button_css, button_enabled, ordering, 
  49.  --           published 
  50.  --      ) 
  51.  -- SELECT 
  52.  --      id, `name`, simple_pattern, simple_replacement_html, simple_replacement_text, pattern, 
  53.  --      replacement_html, replacement_text, button_acl, button_open_tag, button_close_tag, 
  54.  --      button_title, button_prompt, button_image, button_css, button_enabled, ordering, 
  55.  --      published 
  56.  -- FROM 
  57.  --      mydb_livecopy_j15.myprefix_jcomments_custom_bbcodes; 
  58.   
  59.   
  60.   
  61.  -- -------------------------------------------------------------------------------- 
  62.  -- _jcomments_reports 
  63.  -- -------------------------------------------------------------------------------- 
  64.   
  65.  INSERT INTO 
  66.       mydb_upgrade_j25.myprefix_jcomments_reports 
  67.       ( 
  68.            id, commentid, userid, name, ip, date, reason, status 
  69.       ) 
  70.  SELECT 
  71.       id, commentid, userid, name, ip, date, reason, status 
  72.  FROM 
  73.       mydb_livecopy_j15.myprefix_jcomments_reports; 
  74.   
  75.   
  76.  -- -------------------------------------------------------------------------------- 
  77.  -- _jcomments_settings -- ignored as this will load factory settings 
  78.  -- -------------------------------------------------------------------------------- 
  79.  /* 
  80.  INSERT INTO 
  81.       mydb_upgrade_j25.myprefix_jcomments_settings 
  82.       ( 
  83.            component, lang, name, value 
  84.       ) 
  85.  SELECT 
  86.       component, lang, name, value 
  87.  FROM 
  88.       mydb_livecopy_j15.myprefix_jcomments_settings; 
  89.  */ 
  90.   
  91.  -- -------------------------------------------------------------------------------- 
  92.  -- _jcomments_subscriptions 
  93.  -- -------------------------------------------------------------------------------- 
  94.   
  95.  INSERT INTO 
  96.       mydb_upgrade_j25.myprefix_jcomments_subscriptions 
  97.       ( 
  98.            id, object_id, object_group, lang, userid, name, email, hash, published 
  99.       ) 
  100.  SELECT 
  101.       id, object_id, object_group, lang, userid, name, email, hash, published 
  102.  FROM 
  103.       mydb_livecopy_j15.myprefix_jcomments_subscriptions; 
  104.   
  105.   
  106.  -- source=??? 
  107.   
  108.  -- -------------------------------------------------------------------------------- 
  109.  -- _jcomments_version 
  110.  -- -------------------------------------------------------------------------------- 
  111.   
  112.  INSERT INTO 
  113.       mydb_upgrade_j25.myprefix_jcomments_version 
  114.       ( 
  115.            version, previous, installed, updated 
  116.       ) 
  117.  SELECT 
  118.       version, previous, installed, updated 
  119.  FROM 
  120.       mydb_livecopy_j15.myprefix_jcomments_version; 
  121.   
  122.   
  123.  -- -------------------------------------------------------------------------------- 
  124.  -- _jcomments_votes 
  125.  -- -------------------------------------------------------------------------------- 
  126.   
  127.  INSERT INTO 
  128.       mydb_upgrade_j25.myprefix_jcomments_votes 
  129.       ( 
  130.            id, commentid, userid, ip, date, value 
  131.       ) 
  132.  SELECT 
  133.       id, commentid, userid, ip, date, value 
  134.  FROM 
  135.       mydb_livecopy_j15.myprefix_jcomments_votes; 
  136.   
  137.   
  138.   
  139.   
  140.  -- -------------------------------------------------------------------------------- 
  141.  -- _jcomments_objects  *** NEW FOR JOOMLA 2.5.X 
  142.  -- -------------------------------------------------------------------------------- 

Post-Installation

Go to JComments Settings and specify the same "Choose categories for JComments to work in" as you have for your 1.5 website. The above script ignores your previous settings and leaves the factory settings that come with the JComments extension. Check all JComments settings are what you want using the Joomla Admin interface.

If the article IDs match then the comments will also match. If however your article IDs don't match in both versions of your site, consider using the following update script, this will change the IDs of the JComments comments to be associated to articles from your Joomla 2.5 site (no changes to article IDs but backup your original database (J1.5) anyway before running the following):


  1.  UPDATE  
  2.       my_joomla_1_5_db.my_1_5_db_prefix_content j1a,    -- Joomla 1.5.x Content 
  3.       my_joomla_1_5_db.my_1_5_db_prefix_jcomments j1b,  -- Joomla 1.5.x JComments 
  4.       my_joomla_2_5_db.my_2_5_db_prefix_content j2a,    -- Joomla 2.5.x Content 
  5.       my_joomla_2_5_db.my_2_5_db_prefix_jcomments j2b   -- Joomla 2.5.x JComments 
  6.   
  7.  SET  
  8.       j2b.`object_id`=j2a.`id` 
  9.   
  10.  WHERE 
  11.       j1a.`alias`=j2a.`alias` 
  12.   
  13.  AND 
  14.       j1a.`id`=j1b.`object_id` 
  15.   
  16.  AND      
  17.       j2b.`comment`=j1b.`comment` 

Comments   

alexaner
# alexaner Fri, 5th April 2013
For non-techies I'd suggest automated tool for JComments conversion
http://www.cms2cms.com/
Like | Dislike | -2 Reply | Reply with quote | Quote
maxi
# maxi Wed, 24th April 2013
Excelente! me funcionooo, muchas gracias!! I mean, thanks a lot :P
Like | Dislike | +1 Reply | Reply with quote | Quote
Pfefferoni
# Pfefferoni Sun, 15th December 2013
Thanks for the script. Exactly what I was looking for. It completed my migration to Joomla 2.5 :lol:
Like | Dislike | 0 Reply | Reply with quote | Quote
Jaidev
# Jaidev Sun, 19th January 2014
How to rename reference to database when DB name has been changed?
Like | Dislike | 0 Reply | Reply with quote | Quote
# Webmaster Mon, 20th January 2014
Hi Jaidev,

Do you mean two different servers or different databases?

The script (1st) above needs to be modified using any text editor like notepad or vi. Change the name of the tables as appropriate. Specify the "USE" command if you want to specify which database to look at. I'm not sure if MySQL supports the T-SQL way of referring to other databases:
Code:
SELECT t1.* FROM [server_name_1].[database_name].[schema].[table_name] t1
INNER JOIN
[server_name_2].[database_name].[schema].[table_name] t2
ON t2.id=t1.id


I have heard of using a FEDERATED engine for cross-server commands using MySQL.

Hope that helps!

Joe
Like | Dislike | 0 Reply | Reply with quote | Quote
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • 301 Redirect using htaccess file

    • Tue 15-Apr-14
      Further suggestion:
      RewriteRule ^(.*/)?assets/s 2dmain.html\?/( .*/)? $2 [R=301,L]
      Webmaster  
    • Thu 10-Apr-14
      Playing with some RegEx testers
      RewriteRule ^(.*\?/)?(?:$|( .+?)(?:(\.[^.]$ )|$)) $2 [R=301,L] ...
      Webmaster  
    • Wed 09-Apr-14
      I tried to redirect links from my old site to my new site, based on what I read in this thread. The old ...
      pelle
  • Joes Revolver Map (JRM)

    • Tue 22-Apr-14
      Hi Manfred, Some downloaders have reported that the version of this module intended for Joomla 2.5.x ...
      Webmaster  
    • Sun 20-Apr-14
      Hello, is it possible to use this module also in joomla 3.2? Thanks Manfred
      Manfred V.