Welcome to Joel Lipman .Com

Preparing Content...


Loading...

Our Website Development Notes

We hope this helps!

Web Development // Joomla // 3rd-party Joomla!

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

Thursday, 10th September 2015
226,103 Reads

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`



Article Comments (5)

Monday, 20th January 2014
1 Vote
Gravatar for Joel
Joel
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:
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

Sunday, 19th January 2014
1 Vote
Gravatar for Jaidev
Jaidev
How to rename reference to database when DB name has been changed?

Sunday, 15th December 2013
1 Vote
Gravatar for Pfefferoni
Pfefferoni
Thanks for the script. Exactly what I was looking for. It completed my migration to Joomla 2.5 :lol:

Wednesday, 24th April 2013
2 Votes
Gravatar for maxi
maxi
Excelente! me funcionooo, muchas gracias!! I mean, thanks a lot :P

Friday, 5th April 2013
4 Votes
Gravatar for alexaner
alexaner
For non-techies I'd suggest automated tool for JComments conversion
http://www.cms2cms.com/


Recent Comments

Gravatar for Art
Oracle: order by subquery missing right parenthesis
Hello, what if I would like to add rownum to that code, but in my case it's all is subquery? How can I do this ?

20 Jan


Gravatar for Robert
CharIndex Reverse - find occurrence starting from end of string in TSQL
Very helpful thanks! This worked for me as well, and is a little shorter. Not sure if there are drawbacks. SELECT RIGHT(@Haystack,CHARINDEX(@Delimiter,REVERSE(@Haystack))-LEN(@Delimiter))

29 Dec


Gravatar for Translation

27 Dec


Gravatar for Tibbe
JComments 2.3.0 with ReCaptcha in Joomla 2.5.x
Hi there, This looks like a great solution to get rid of spam comments. How to integrate this in Joomla 3.x? Step 2 I did in settings.xml, but no result in frontend. Kind Regards, Tibbe

16 Dec


Gravatar for sach|n
SSRS Repeat Headers in PDF Report
Yes its working for me..thnx Joel.

7 Oct