Welcome to Joel Lipman .Com

Preparing Content...


Loading...

Our Website Development Notes

We hope this helps!

Web Development // Joomla // Joomla! Core

Migrate Joomla! 1.5.x to 2.5.x+

Tuesday, 2nd July 2013
76,167 Reads

Update June 2013 - How to use this migration script:

  1. Make a backup copy of your old Joomla website (1.5.x)

  2. Download and install the latest Joomla CMS
    • Go through the Joomla Web-based Installation GUI until the process deletes the Installation folder.
    • Avoid installing Sample Data if possible (there will be some but this script has been reworked to potentially accommodate).

  3. Download my migration script
    • Decide on which script to use:
      1. If you have a small site (less than 10'000 articles), then you will use the single script "migrate_j15_to_j25_new_v1_8.sql"
      2. For larger sites (~200'000 articles), then you will need to use the 3-part script "migrate_j15_to_j25_new_v1_6_pt1.sql", "migrate_j15_to_j25_new_v1_6_pt2.sql", "migrate_j15_to_j25_new_v1_6_pt3.sql". Part 2 is dedicated to content migration, the commands are split to do about 5000 articles on each iteration so as to avoid session timeouts and memory issues.
    • Edit the script, renaming the database and table names used (DO NOT CONFUSE THE FOLLOWING DATABASE NAMES OR YOU WILL END UP WITH NEITHER SITE WORKING!!!)
      1. Open the SQL file in your favorite text-editor
      2. Rename any instance of "my_old_database.jos_" to the name of your old Joomla 1.5 database or a copy of (note the "jos_" prefix to all J15 tables, if you have changed this then reflect this in the SQL file).
      3. Rename any instance of "my_new_database" to the name of your new Joomla 3.0 database (don't change the table prefix for the new Joomla and instead do this as the next step).
      4. Rename any instance of "my_new_database.my_prefix_" to the name of your new Joomla 3.0 database (note that we're reflecting the prefix to use but also the database name - by this step, "my_new_database" should have been renamed).
      5. Save the SQL file, this is now unique to your website and this process. It does not contain usernames or passwords but has the real database names and all website content, so do not redistribute without taking security measures.

  4. Run the SQL against your new database
    • Open your favorite database management tool (phpMyAdmin, workBench, navicat, sqlyog?) and connect to the new database.
    • This script will use both databases, reading from the old Joomla database and writing to the new one.
    • To run this script, your user needs SELECT, INSERT and UPDATE privileges on the new database (only SELECT on the old database will do). Special care has been made to avoid the use of functions, stored procedures, temporary tables, etc. This allows broader compatibility and customers who do not have DBAs to run this themselves. Personally I grant the user who will be executing the script full privileges or at least the same as needed to install Joomla CMS.
    • Cross your fingers and use your DB management tool to run the SQL file (or copy the contents of the SQL file into the input field)

  5. Re-build/Auto-correct some Joomla inconsistencies
    • Login to your Joomla Admin Panel using a System Administrator account ("Super User"?)
    • Go to Categories > click on the "REBUILD" icon (top right)
    • Go to Menus > click on the "REBUILD" icon (top right)

  6. Check your Joomla Admin User
    • Each new Joomla version is trying a different method making this very diffcult to maintain. Check that the "super administrators" for Joomla 1.5 are in your new Joomla site (as "Super Users").
    • Check that the system administrator for the new Joomla site (created on installation) is still a "Super User" (e-mail address, receives sys emails) and note their ID (identifier number).
    • See if elements created on install show as created by the admin user and not by one of your users who coincidentally has the same identifier.
    • Check that your OddUser (will be last user listed in users table) can login and their respective articles are still attributed to them.

  7. Prepare for Go Live
    • This migration script is not a silver bullet. It was created to take away the hours I know I would spend without it.
    • This is not an upgrade; it is likely that your client may reconsider the template they are using or may want to add new 3rd-party components.
    • If asked: "how long will it take", do not say 5 minutes. This script runs on average in 15 seconds. The remaining hours, days, weeks will be spent on bringing your website up to standard with all issues addressed and sporting the latest version of the Joomla CMS!


UPDATE 2012 - Valid for Joomla 2.5.x but only experimental for Joomla 3.0.x!
I still do not recommend using this for your site if you have no test environment, however... I have used this script continuously to migrate J1.5.x websites to J2.5.x without issue. I have managed to do whole migrations in under 2 hours (this script runs in about 20 seconds based on 1000 articles and 5000 users) and my clients are satisfied! I am so pleased I made this script; it has saved me so much time!

What does this do?
After many tweaks and corrections having upgraded multiple sites in the past few years with this script, the version below migrates the following:
  • Articles
  • Banners
  • Categories & Sections
  • Menus * NEW
  • Messages
  • Newsfeeds
  • Users * UPDATED
  • Weblinks

So this is fun. We've waited and waited and Joomla 1.6 is now stable... Exciting because this is not a simple 1.5.21 to 1.5.22 upgrade. But don't believe the drama across the web, the system introduces only a few new fundamental improvements (particularly Access Control and Groups) which actually only means some minor database alterations.

I've googled, yahood and binged, but still can't find a good way to upgrade my site from Joomla version 1.5 to 1.6 so as usual I find myself writing the first migration script. Why use a script? Well apps like jUpgrade didn't work for me. This is how to manually migrate using a MySQL database management tool like PhpMyAdmin. I'm not too bothered about upgrading my own personal site (Joomla 1.5), it's just the silly number of client websites I have to upgrade.

This I find a lot simpler, my day job involves working with SQL so this script was rather basic and quick to write. You have two databases, one with your Joomla 1.5 old content, and one following a fresh install of the latest Joomla (at time of last print: 2.5.6 - preferably no sample data pre-installed). Change the database names in this script (including the new random prefix for J2.5) and run it. 20 seconds later your Joomla 1.6-2.5.x website will have all your J1.5 articles and users...

DISCLAIMER:

This is to help ME migrate Joomla 1.5 sites to Joomla 1.6 automatically. I have also been using this happily for Joomla 2.5.x websites and more recently for an experimental Joomla 3.0.x website.

  • I do not work for Joomla!
  • This script is for my own use.
  • Joomla! are in no way liable for this script.
  • You can copy this script as long as you specify a link to its source.
  • You can use this script as long as you don't hold me responsible for the results.
  • If you don't understand what this script is doing, you shouldn't be running it. Please ask someone who does.

  1. -- IMPORTANT NOTES (to be included in SQL script):  
  2. -- -- The script below is to migrate your existing Joomla! 1.5 database  
  3. -- -- to an instance of Joomla! version 1.6.x or greater.  
  4. -- -- You should have both sites up and running alongside each other.  
  5.  
  6. -- -- The instructions below uses 2 databases: LIVE and UPGRADE  
  7. -- -- Do not run this if you only have 1 database available!  
  8.  
  9. -- -- References to J15 indicate a Joomla version 1.5.x instance.  
  10. -- -- References to J30 indicate a Joomla version 1.6.x or greater.  
  11.  
  12.  
  13. -- REQUIRED:  
  14. -- -- A minimum of 2 databases. The original and the one to migrate to.  
  15. -- -- A login account (mysql user) with most privileges on BOTH databases.  
  16. -- -- To change the database names used in the below script to the ones you use!!!  
  17.  
  18.  
  19. -- SKIP MIGRATION:  
  20. -- -- Each section was written so that it could be migrated separate to the rest.  
  21. -- -- It is recommended to skip the menu migration and to do this manually.  
  22. -- -- This script was initially written to only migrate articles and users.  
  23.  
  24.  
  25. -- MINIMAL SETUP REQUIRED FOR USING THIS SCRIPT:  
  26. -- -- Live = The live and original version of the website (Joomla! 1.5.x)  
  27. -- -- Upgrade = Fresh install of Joomla! 1.6.x - 3.0.x (sample data optional).  
  28.  
  29.  
  30. -- -----------------------------------------------------------------------------  
  31.  
  32. -- TABLE OF CONTENTS  
  33.  
  34. -- I. Help on using the queries below  
  35. -- -- 1. replace all database names in the scripts below  
  36. -- -- 2. checking before you commit changes  
  37.  
  38. -- II. Copy content from the old database to the new database and tweak:  
  39. -- -- 1. setting some variables  
  40. -- -- 2. jos_banner -> #__banners  
  41. -- -- 3. jos_bannerclient -> #__banner_clients  
  42. -- -- 4. jos_bannertrack -> #__banner_tracks  
  43. -- -- 5. jos_categories -> #__categories  
  44. -- -- 6. jos_components -> #__extensions  
  45. -- -- 7. jos_contact_details -> #__contact_details  
  46. -- -- 8. jos_content -> #__content  
  47. -- -- 9. jos_content_frontpage -> #__content_frontpage  
  48. -- -- 10. jos_content_rating -> #__content_rating  
  49. -- -- 11. jos_core_log_items, jos_core_log_searches -> #__core_log_searches  
  50. -- -- 12. jos_menu -> #__menu, #__menu_types  
  51. -- -- 13. jos_menu_types -> merged with II.12.  
  52. -- -- 14. jos_messages -> #__messages  
  53. -- -- 15. jos_messages_cfg -> #__messages_cfg  
  54. -- -- 16. jos_modules -> #__modules  
  55. -- -- 17. jos_modules_menu -> #__modules_menu  
  56. -- -- 18. jos_newsfeeds -> #__newsfeeds  
  57. -- -- 19. jos_plugins -> #__extensions  
  58. -- -- 20. jos_users, jos_core_acl_groups_aro_map -> #__user_usergroup_map, #__users  
  59. -- -- 21. jos_weblinks -> #__weblinks  
  60.  
  61. -- III. Third-party Extensions  
  62. -- -- JComments  
  63. -- -- Others?  
  64.  
  65. -- IV. Post-Installation  
  66. -- -- IMPORTANT: Category Rebuild  
  67. -- -- IMPORTANT: Menu Rebuild  
  68.  
  69.  
  70. -- READY?  
  71.  
  72. -- -----------------------------------------------------------------------------  
  73. -- I. Help on using the queries below  
  74. -- -----------------------------------------------------------------------------  
  75.  
  76. -- There are a few notes about running the scripts.  
  77.  
  78. -- A new table in Joomla 1.6+ is the `jos_assets` which wants to list  
  79. -- each article, banner, category, contact, newsfeed, weblink. This is  
  80. -- why each snippet consists of more than one MySQL query.  
  81.  
  82. -- I have separated the queries so that they can be run individually  
  83. -- rather than running this entire script in one go but do try to run the  
  84. -- whole script as it maintains associations between users and content.  
  85.  
  86. -- Reminder: backticks have been used on reserved words so that this SQL  
  87. -- script runs on systems that do not have the most recent mySQL version  
  88. -- installed. These are NOT the same as apostrophes used in SQL to define  
  89. -- non-numerical values.  
  90.  
  91. -- -----------------------------------------------------------------------------  
  92. -- -- 1. replace all database names in the scripts below  
  93. -- -----------------------------------------------------------------------------  
  94.  
  95. -- Yeh well you've been warned BACKUP BACKUP BACKUP!  
  96.  
  97. -- I have several databases and websites setup with exact copies of the  
  98. -- data so I can risk this one not working. You need as a minimum, at  
  99. -- least 2 databases to use the queries in this script (Live and Upgrade).  
  100.  
  101. -- INSTRUCTIONS:  
  102.  
  103. -- #1 Replace "my_old_database.jos_" to the existing & current database  
  104. -- including the table prefix (Joomla 1.5.x).  
  105.  
  106. -- #2 Replace "my_new_database" to the name of the new/upgrade database  
  107. -- excluding the new table prefix (Joomla 1.6.x, 1.7.x, 2.5.x, 3.0.x).  
  108.  
  109. -- #3 Replace "my_new_database.my_prefix_" to the name of the new database  
  110. -- including the new table prefix (Joomla 1.6.x, 1.7.x, 2.5.x, 3.0.x).  
  111.  
  112. -- Tip: I copy & paste this code into a text editor, then do  
  113. -- "Replace All" with these 3 values, then copy & paste the resulting  
  114. -- SQL into a query against my new database.  
  115. -- - Replace Step #1: 58 Occurrences  
  116. -- - Replace Step #2: 169 Occurrences  
  117. -- - Replace Step #3: 167 Occurrences  
  118.  
  119.  
  120. -- -----------------------------------------------------------------------------  
  121. -- -- 2. checking before you commit changes  
  122. -- -----------------------------------------------------------------------------  
  123.  
  124. -- If you select the part of each expression from the SELECT and run these  
  125. -- to see what results are returned, it is reading only and not making any  
  126. -- changes. It will show you what data the query is planning to migrate.  
  127.  
  128. -- EXAMPLE:  
  129. -- SET @exampleLocalVariable=1;  
  130. -- INSERT INTO  
  131. -- `my_new_database`.my_prefix_sampletable  
  132. -- (`id`, `name`, `title`, `value`)  
  133. -- SELECT  
  134. -- 1, 'Joel Lipman', 'Webmaster', @exampleLocalVariable  
  135. -- FROM  
  136. -- `my_old_database`.jos_sampletable  
  137. -- WHERE  
  138. -- `my_old_database`.jos_sampletable.user_id=1;  
  139.  
  140. -- You could copy and paste this whole query and then remove everything  
  141. -- from the "INSERT INTO" to just before the "SELECT" (as below):  
  142.  
  143. -- SET @exampleLocalVariable=1;  
  144. -- SELECT  
  145. -- 1, 'Joel Lipman', 'Webmaster', @exampleLocalVariable  
  146. -- FROM  
  147. -- `my_old_database`.jos_sampletable  
  148. -- WHERE  
  149. -- `my_old_database`.jos_sampletable.user_id=1;  
  150.  
  151. -- This would return the data to migrate but without making any changes.  
  152.  
  153.  
  154. -- -----------------------------------------------------------------------------  
  155. -- II. 1. Declare some variables  
  156. -- -----------------------------------------------------------------------------  
  157.  
  158. -- A bit obsolete as we'll address each variable per section.  
  159.  
  160. -- NOTE: Because Joomla randomly selects an ID for the installing admin, I  
  161. -- have changed the script to determine what this ID is by finding out what  
  162. -- user_id is the first user created in the upgrade site. This is the ID  
  163. -- used when installing Joomla CMS. It is important that the script below  
  164. -- accounts for the fact that you may have a user with the same ID in your  
  165. -- previous Joomla CMS and we would want to maintain any relationships and  
  166. -- history of both entities.  
  167.  
  168. -- As a workaround, the installing admin ID will stay as is and will be  
  169. -- a new user in your users table. The user who originally was assigned  
  170. -- that ID will be moved to the end of the list and any associated content  
  171. -- and events will be re-linked to that user.  
  172.  
  173.  
  174. -- -----------------------------------------------------------------------------  
  175. -- II. 2. jos_banner to #__banners  
  176. -- -----------------------------------------------------------------------------  
  177.  
  178. -- For BANNERS, we're going to do the following:  
  179. -- 1) Create banner-category assets  
  180. -- 2) Import banner categories  
  181. -- 3) Migrate banners from J15 to J30  
  182. -- 4) Update foreign keys  
  183. -- 5) Remove notes used by script  
  184. -- 6) Associate category IDs  
  185. -- 7) Remove remaining migration notes [Optional Step]  
  186.  
  187. -- Affects: #__assets, #__banners, #__categories  
  188. -- * You need to run steps 1-7 for a full transfer of banners (does not  
  189. -- include clients and tracks, run the remainder of this section for these).  
  190.  
  191. -- -----------------------------------------------------------------------------  
  192. -- Step 1) Create banner-category assets:  
  193. -- - Import banner categories from J15 and insert as assets in J30  
  194.  
  195.  
  196. SET @new_jos_assets_last_inc=(  
  197. SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastcatid  
  198. FROM `my_new_database`.my_prefix_assets  
  199. WHERE `name` LIKE '%.category.%'  
  200. ORDER BY `lastcatid` DESC LIMIT 0,1);  
  201. SET @new_jos_assets_last_inc=(  
  202. SELECT IFNULL(@new_jos_assets_last_inc, 0));  
  203. SET @new_jos_assets_id_last_lft=(  
  204. SELECT MAX(`lft`) FROM `my_new_database`.my_prefix_assets);  
  205. SET @new_jos_banners_parent_id=(  
  206. SELECT MIN(`id`)  
  207. FROM `my_new_database`.my_prefix_assets  
  208. WHERE `name`='com_banners');  
  209. SET @new_jos_banners_parent_id = (  
  210. SELECT IFNULL(@new_jos_banners_parent_id, 0));  
  211.  
  212. INSERT INTO  
  213. `my_new_database`.my_prefix_assets (  
  214. `parent_id`, `lft`, `rgt`, `level`, `name`, `title`, `rules`)  
  215. SELECT  
  216. @new_jos_banners_parent_id AS parent_id,  
  217. @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2 AS lft,  
  218. @new_jos_assets_id_last_lft + 1 AS rgt,  
  219. 2 AS level,  
  220. CONCAT(  
  221. 'com_banners.category.',  
  222. @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1  
  223. ) AS name,  
  224. CONCAT(`title`, ' :|joes|', `id`, '|: ') AS title,  
  225. '{"core.create":[],"core.delete":[],"core.edit":[],"core.edit.state":[]}'  
  226. FROM  
  227. `my_old_database`.jos_categories  
  228. WHERE  
  229. `my_old_database`.jos_categories.section='com_banner';  
  230.  
  231.  
  232. -- -----------------------------------------------------------------------------  
  233. -- Step 2) Import banner categories:  
  234. -- - Import banner categories from J15 and insert as categories in J30  
  235.  
  236.  
  237. SET @new_jos_categories_extension_alias=(  
  238. SELECT `alias`  
  239. FROM `my_new_database`.my_prefix_categories  
  240. WHERE `extension`='com_content'  
  241. ORDER BY `id` ASC  
  242. LIMIT 0,1);  
  243. SET @new_jos_categories_id_last_lft=(  
  244. SELECT MAX(`lft`)  
  245. FROM `my_new_database`.my_prefix_categories);  
  246. SET @new_jos_banners_parent_id=(  
  247. SELECT MIN(`id`)  
  248. FROM `my_new_database`.my_prefix_categories  
  249. WHERE `extension`='system');  
  250. SET @new_jos_admin_id=(  
  251. SELECT `created_user_id`  
  252. FROM `my_new_database`.my_prefix_categories  
  253. WHERE `id`='1');  
  254.  
  255. INSERT INTO  
  256. `my_new_database`.my_prefix_categories (  
  257. `parent_id`, `lft`, `rgt`, `level`, `path`, `extension`, `title`,  
  258. `alias`, `note`, `description`, `published`, `access`, `params`,  
  259. `metadata`, `created_user_id`, `created_time`, `language`)  
  260. SELECT  
  261. @new_jos_banners_parent_id AS parent_id,  
  262. @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2 AS lft,  
  263. @new_jos_categories_id_last_lft+1 AS rgt, 1 AS `level`,  
  264. CAST(@new_jos_categories_extension_alias AS CHAR) AS path,  
  265. 'com_banners' AS extension, c.`title`, c.`alias`,  
  266. CONCAT('Import from J15. PreviousID=catid', c.`id`) AS note,  
  267. c.`description`, c.`published`,  
  268. CASE  
  269. WHEN c.`access`=0 THEN 1  
  270. WHEN c.`access`=1 THEN 2  
  271. WHEN c.`access`=2 THEN 7  
  272. END AS access,  
  273. '{"target":"","image":""}' AS params,  
  274. '{"page_title":"","author":"","robots":""}' AS metadata,  
  275. @new_jos_admin_id AS created_user_id, b.date AS created_time,  
  276. '*' AS `language`  
  277. FROM  
  278. `my_old_database`.jos_categories c  
  279. INNER JOIN `my_old_database`.jos_banner b ON b.catid=c.id  
  280. WHERE  
  281. c.section='com_banner';  
  282.  
  283.  
  284. -- -----------------------------------------------------------------------------  
  285. -- Step 3) Migrate banners from J15 to J30:  
  286. -- - Import banners from J15 and insert as banners in J30  
  287. -- - Maintain asset_id and category_id  
  288. -- - J30 Assigns ID of 42 to creator of vanilla (Joomla!) banners  
  289. -- -> Need to use ID of creator of first category as creator of banners.  
  290.  
  291. SET @new_jos_admin_id=(  
  292. SELECT `created_user_id`  
  293. FROM `my_new_database`.my_prefix_categories  
  294. WHERE `id`='1');  
  295.  
  296. INSERT INTO  
  297. `my_new_database`.my_prefix_banners (  
  298. `cid`, `type`, `name`, `alias`, `imptotal`, `impmade`, `clicks`,  
  299. `clickurl`, `state`, `catid`, `description`, `custombannercode`, `sticky`,  
  300. `ordering`, `params`, `track_clicks`, `track_impressions`, `publish_up`,  
  301. `publish_down`, `created`, `language`)  
  302. SELECT  
  303. `cid`, `type`, `name`, `alias`, `imptotal`, `impmade`, `clicks`,  
  304. `clickurl`, 1, `catid`, CONCAT(`description`, '(PreviousID=', `bid`, ')'),  
  305. `custombannercode`, `sticky`, `ordering`,  
  306. IF(  
  307. `params`='',  
  308. '',  
  309. CONCAT(  
  310. '{"',  
  311. REPLACE(REPLACE(`params`, CHAR(10), '","'), '=', '":"'),  
  312. '"}'  
  313. )  
  314. ),  
  315. 0, 0, `publish_up`, `publish_down`, `date`, '*'  
  316. FROM  
  317. `my_old_database`.jos_banner;  
  318.  
  319.  
  320. -- -----------------------------------------------------------------------------  
  321. -- Step 4) Update foreign keys in J30:  
  322. -- - Set categories asset_id to assets ID.  
  323.  
  324.  
  325. UPDATE  
  326. `my_new_database`.my_prefix_categories a,  
  327. `my_new_database`.my_prefix_assets b  
  328. SET a.`asset_id`=b.`id` WHERE b.`name` LIKE 'com_banners.category.%'  
  329. AND b.`title` LIKE '% :|joes|%'  
  330. AND SUBSTR(  
  331. b.`title`,  
  332. LOCATE(' :|joes|', b.`title`)+8,  
  333. (LOCATE('|: ', b.`title`) - ( LOCATE(' :|joes|', b.`title`)+8))  
  334. ) = SUBSTR( a.`note`, LOCATE(' PreviousID=catid', a.`note`) + 17 );  
  335.  
  336.  
  337. -- -----------------------------------------------------------------------------  
  338. -- Step 5) Remove script markers from assets:  
  339. -- - Remove everything after and including the string ":|joes|"  
  340.  
  341.  
  342. UPDATE  
  343. `my_new_database`.my_prefix_assets b,  
  344. `my_new_database`.my_prefix_categories a  
  345. SET b.`title`=TRIM(SUBSTR(b.`title`, 1, LOCATE(' :|joes|', b.`title`))),  
  346. b.`name`=CONCAT('com_banners.category.', a.`id`)  
  347. WHERE b.`title` LIKE '% :|joes|%' AND b.`id`=a.`asset_id`;  
  348.  
  349.  
  350. -- -----------------------------------------------------------------------------  
  351. -- Step 6) Associate category IDs:  
  352. -- - Determine IDs by script notes "PreviousID="  
  353.  
  354.  
  355. UPDATE  
  356. `my_new_database`.my_prefix_categories a,  
  357. `my_new_database`.my_prefix_banners b  
  358. SET b.`catid`=a.`id`  
  359. WHERE b.`catid`=SUBSTR( a.`note`, LOCATE('. PreviousID=catid', a.`note`)+18)  
  360. AND SUBSTR( a.`note`, LOCATE('. PreviousID=', a.`note`)+13)<>'';  
  361.  
  362. SET @new_jos_banners_parent_id=(  
  363. SELECT MIN(`id`)  
  364. FROM `my_new_database`.my_prefix_categories  
  365. WHERE `extension`='com_banners');  
  366.  
  367. UPDATE  
  368. `my_new_database`.my_prefix_banners a,  
  369. `my_old_database`.jos_categories b  
  370. SET a.`catid`=@new_jos_banners_parent_id  
  371. WHERE a.`catid` NOT IN (SELECT `id` FROM `my_old_database`.jos_categories);  
  372.  
  373.  
  374. -- -----------------------------------------------------------------------------  
  375. -- Step 7) Remove remaining migration notes [Optional Step]:  
  376. -- - Remove notes of "Import from J15. PreviousID=..."  
  377. -- * Comment this out with a preceding double-hyphen if you want to  
  378. -- leave the notes in the system. These say what the ID references  
  379. -- were in the J15 system. I leave them in until satisfied the  
  380. -- process worked. You can use the Joomla admin panel to remove them  
  381. -- later instead of running the below UPDATE.  
  382.  
  383.  
  384. UPDATE `my_new_database`.my_prefix_categories  
  385. SET `note`=''  
  386. WHERE `note` LIKE 'Import from J15. Previous%';  
  387.  
  388.  
  389. -- -----------------------------------------------------------------------------  
  390. -- II. 3. jos_bannerclient to #__banner_clients  
  391. -- -----------------------------------------------------------------------------  
  392.  
  393. -- For BANNER CLIENTS, we're going to do the following:  
  394. -- 1) Import banner clients from J15 and insert into J30  
  395. -- 2) Re-associate banner clients (in case J30 already includes new clients)  
  396.  
  397. -- Affects: #__banners, #__banner_clients  
  398.  
  399.  
  400. -- -----------------------------------------------------------------------------  
  401. -- Step 1) Import banner clients from J15 and insert into J30:  
  402. -- - Assign a default state of published (state did not exist in J15)  
  403.  
  404. INSERT INTO `my_new_database`.my_prefix_banner_clients (  
  405. `name`, `contact`, `email`, `extrainfo`, `state`, `checked_out`,  
  406. `checked_out_time`, `purchase_type`, `track_clicks`, `track_impressions`)  
  407. SELECT `name`, `contact`, `email`, `extrainfo`, 1, 0, 0, 0, 0, 0  
  408. FROM `my_old_database`.jos_bannerclient;  
  409.  
  410.  
  411. -- -----------------------------------------------------------------------------  
  412. -- Step 2) Re-associate banner clients (in case J30 already includes new clients)  
  413. -- - Re-associate if description contains migration notes  
  414.  
  415. UPDATE `my_new_database`.my_prefix_banners a  
  416. INNER JOIN `my_old_database`.jos_bannerclient c ON a.cid=c.cid  
  417. INNER JOIN `my_new_database`.my_prefix_banner_clients b ON b.email=c.email  
  418. SET a.cid=b.id  
  419. WHERE a.description LIKE '%(PreviousID=%';  
  420.  
  421.  
  422. -- -----------------------------------------------------------------------------  
  423. -- II. 4. jos_bannertrack to #__banner_tracks  
  424. -- -----------------------------------------------------------------------------  
  425.  
  426. -- For BANNER TRACKS, we're going to do the following:  
  427. -- 1) Import banner tracks from J15 and insert into J30.  
  428. -- & Re-associate banner track IDs to "new" IDs in J30.  
  429. -- 2) Remove migration notes in J30 [Optional Step]  
  430.  
  431. -- Affects: #__banner_tracks, #__banners  
  432.  
  433.  
  434. -- -----------------------------------------------------------------------------  
  435. -- Step 1) Import banner tracks from J15 and insert into J30 & associate IDs.  
  436.  
  437. INSERT INTO `my_new_database`.my_prefix_banner_tracks  
  438. (`track_date`, `track_type`, `banner_id`)  
  439. SELECT a.`track_date`, a.`track_type`, b.`id`  
  440. FROM `my_old_database`.jos_bannertrack a  
  441. INNER JOIN `my_new_database`.my_prefix_banners b  
  442. ON SUBSTR(  
  443. b.`description`,  
  444. LOCATE('(PreviousID=', b.`description`)+12,  
  445. LOCATE(')', b.`description`) - (LOCATE('(PreviousID=', b.`description`)+12)  
  446. )=a.banner_id;  
  447.  
  448.  
  449. -- -----------------------------------------------------------------------------  
  450. -- Step 2) Remove migration notes in J30 [Optional Step]  
  451. -- - Remove string "(PreviousID=" where it exists in descriptions  
  452.  
  453. UPDATE `my_new_database`.my_prefix_banners  
  454. SET description=SUBSTR(description, 1, LOCATE('(PreviousID=', description)-1)  
  455. WHERE description LIKE '%(PreviousID=%';  
  456.  
  457.  
  458. -- -----------------------------------------------------------------------------  
  459. -- II. 5. #__categories  
  460. -- -----------------------------------------------------------------------------  
  461.  
  462. -- NOTE: Categories in Joomla 1.5 have no parent category and sections do not  
  463. -- exist in Joomla 1.6.0 or greater  
  464.  
  465. -- For CATEGORIES, we're going to do the following:  
  466. -- 1) Import sections as new categories  
  467. -- 2) Import categories as sub-categories  
  468. -- 3) Update foreign keys for J30: #__categories.asset_id  
  469. -- 4) Remove Scripting Notes from J30: #__assets.name  
  470. -- 5) Import categories from J15 and insert as assets in J30  
  471. -- 6) Insert categories of J15 as categories in J30  
  472. -- 7) Update foreign keys in J30: #__categories.asset_id  
  473. -- 8) Remove scripting notes in J30: #__assets.name  
  474. -- 9) Correct category parent IDs based on sections  
  475. -- 10) Correct asset parent IDs based on assets  
  476.  
  477. -- note-to-self: don't import categories that are for others  
  478. -- (eg. com_banners - as these are migrated with banners)  
  479. -- J15 sections only exist for content?  
  480.  
  481. -- Affects: #__assets, #__categories  
  482.  
  483.  
  484. -- -----------------------------------------------------------------------------  
  485. -- Step 1) Import sections as new assets  
  486. -- - Import sections from J15 and insert as assets in J30  
  487.  
  488. SET @new_jos_assets_last_inc=(  
  489. SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastcatid  
  490. FROM `my_new_database`.my_prefix_assets  
  491. WHERE `name` LIKE '%.category.%'  
  492. ORDER BY `lastcatid` DESC LIMIT 0,1);  
  493. SET @new_jos_assets_last_inc=(  
  494. SELECT IFNULL(@new_jos_assets_last_inc, 0));  
  495. SET @new_jos_assets_id_last_lft=(  
  496. SELECT MAX(`lft`) FROM `my_new_database`.my_prefix_assets);  
  497. SET @new_jos_assets_id_parent=(  
  498. SELECT MIN(`id`)  
  499. FROM `my_new_database`.my_prefix_assets  
  500. WHERE `name`='com_content');  
  501.  
  502. INSERT INTO  
  503. `my_new_database`.my_prefix_assets (  
  504. `parent_id`, `lft`, `rgt`, `level`, `name`, `title`, `rules`)  
  505. SELECT  
  506. @new_jos_assets_id_parent,  
  507. @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2,  
  508. @new_jos_assets_id_last_lft+1, 2,  
  509. CONCAT(  
  510. 'com_content.category.',  
  511. @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1  
  512. ),  
  513. CONCAT(`title`, ' :|joes|', `id`, '|: '),  
  514. CONCAT(  
  515. '{"core.create":[],"core.delete":[],',  
  516. '"core.edit":[],"core.edit.state":[],"core.edit.own":[]}'  
  517. )  
  518. FROM  
  519. `my_old_database`.jos_sections;  
  520.  
  521.  
  522. -- -----------------------------------------------------------------------------  
  523. -- Step 2) Import sections as new categories  
  524. -- - Import sections from J15 and insert as categories in J30  
  525.  
  526. SET @new_jos_categories_extension_alias=(  
  527. SELECT `alias`  
  528. FROM `my_new_database`.my_prefix_categories  
  529. WHERE `extension`='com_content'  
  530. ORDER BY `id` ASC LIMIT 0,1);  
  531. SET @new_jos_categories_id_last_lft=(  
  532. SELECT MAX(`lft`)  
  533. FROM `my_new_database`.my_prefix_categories);  
  534. SET @new_jos_admin_id=(  
  535. SELECT `created_user_id`  
  536. FROM `my_new_database`.my_prefix_categories  
  537. WHERE `id`='1');  
  538.  
  539. INSERT INTO  
  540. `my_new_database`.my_prefix_categories (  
  541. `parent_id`, `lft`, `rgt`, `level`, `path`, `extension`, `title`,  
  542. `alias`, `note`, `description`, `published`, `access`, `params`,  
  543. `metadata`, `created_user_id`, `created_time`, `language`  
  544. )  
  545. SELECT  
  546. 1, @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2,  
  547. @new_jos_categories_id_last_lft+1, 1, alias,  
  548. 'com_content', title, alias, CONCAT('Import from J15. PreviousID=', id),  
  549. description, published,  
  550. CASE  
  551. WHEN access=0 THEN 1  
  552. WHEN access=1 THEN 2  
  553. WHEN access=2 THEN 7  
  554. END AS access,  
  555. '{"target":"","image":""}', '{"page_title":"","author":"","robots":""}',  
  556. @new_jos_admin_id, NOW(), '*'  
  557. FROM  
  558. `my_old_database`.jos_sections;  
  559.  
  560.  
  561. -- -----------------------------------------------------------------------------  
  562. -- Step 3) Update foreign keys for J30: #__categories.asset_id  
  563.  
  564. UPDATE  
  565. `my_new_database`.my_prefix_categories a,  
  566. `my_new_database`.my_prefix_assets b  
  567. SET a.`asset_id`=b.`id` WHERE b.`name` LIKE 'com_content.category.%'  
  568. AND b.`title` LIKE '% :|joes|%'  
  569. AND SUBSTR(  
  570. b.`title`,  
  571. LOCATE(' :|joes|', b.`title`)+8,  
  572. (LOCATE('|: ', b.`title`)-(LOCATE(' :|joes|', b.`title`)+8))  
  573. ) = SUBSTR( a.`note`, LOCATE(' PreviousID=', a.`note`) + 12 );  
  574.  
  575.  
  576. -- -----------------------------------------------------------------------------  
  577. -- Step 4) Remove Scripting Notes from J30: #__assets.name  
  578.  
  579. UPDATE  
  580. `my_new_database`.my_prefix_assets b,  
  581. `my_new_database`.my_prefix_categories a  
  582. SET b.title=TRIM(SUBSTR(b.title, 1, LOCATE(' :|joes|', b.title)))  
  583. WHERE b.title LIKE '% :|joes|%' AND b.id=a.asset_id;  
  584.  
  585.  
  586. -- -----------------------------------------------------------------------------  
  587. -- Step 5) Import categories from J15 and insert as assets in J30  
  588.  
  589. SET @new_jos_assets_last_inc=(  
  590. SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastcatid  
  591. FROM `my_new_database`.my_prefix_assets  
  592. WHERE `name` LIKE '%.category.%'  
  593. ORDER BY lastcatid DESC LIMIT 0,1);  
  594. SET @new_jos_assets_id_last_lft=(  
  595. SELECT MAX(lft) FROM `my_new_database`.my_prefix_assets);  
  596.  
  597. INSERT INTO  
  598. `my_new_database`.my_prefix_assets (  
  599. parent_id, lft, rgt, `level`, `name`, title, rules)  
  600. SELECT  
  601. 0, @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2,  
  602. @new_jos_assets_id_last_lft+1, 3,  
  603. CONCAT(  
  604. 'com_content.category.',  
  605. @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1  
  606. ),  
  607. CONCAT(title, ' :|joes|', id, '|: '),  
  608. CONCAT(  
  609. '{"core.create":[],"core.delete":[],',  
  610. '"core.edit":[],"core.edit.state":[],"core.edit.own":[]}'  
  611. )  
  612. FROM  
  613. `my_old_database`.jos_categories  
  614. WHERE  
  615. `my_old_database`.jos_categories.section>0;  
  616.  
  617.  
  618. -- -----------------------------------------------------------------------------  
  619. -- Step 6) Insert categories of J15 as categories in J30  
  620.  
  621. SET @new_jos_categories_extension_alias=(  
  622. SELECT alias FROM `my_new_database`.my_prefix_categories  
  623. WHERE extension='com_content'  
  624. ORDER BY id ASC LIMIT 0,1);  
  625. SET @new_jos_categories_id_last_lft=(  
  626. SELECT MAX(lft)  
  627. FROM `my_new_database`.my_prefix_categories);  
  628. SET @new_jos_admin_id=(  
  629. SELECT `created_user_id`  
  630. FROM `my_new_database`.my_prefix_categories  
  631. WHERE `id`='1');  
  632.  
  633. INSERT INTO  
  634. `my_new_database`.my_prefix_categories (  
  635. parent_id, lft, rgt, `level`, path, extension, title, alias,  
  636. note, description, published, access, params, metadata, created_user_id,  
  637. created_time, `language`)  
  638. SELECT  
  639. 0, @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2,  
  640. @new_jos_categories_id_last_lft+1, 2, @new_jos_categories_extension_alias,  
  641. 'com_content', title, alias,  
  642. CONCAT('Import from J15. PreviousID=catid', id, '. SectionID=', section),  
  643. description, published,  
  644. CASE  
  645. WHEN access=0 THEN 1  
  646. WHEN access=1 THEN 2  
  647. WHEN access=2 THEN 7  
  648. END AS access,  
  649. '{"target":"","image":""}', '{"page_title":"","author":"","robots":""}',  
  650. @new_jos_admin_id, NOW(), '*'  
  651. FROM  
  652. `my_old_database`.jos_categories  
  653. WHERE  
  654. `my_old_database`.jos_categories.section>0;  
  655.  
  656.  
  657. -- -----------------------------------------------------------------------------  
  658. -- Step 7) Update foreign keys in J30: #__categories.asset_id  
  659.  
  660. UPDATE  
  661. `my_new_database`.my_prefix_categories a,  
  662. `my_new_database`.my_prefix_assets b  
  663. SET a.`asset_id`=b.`id` WHERE b.`name` LIKE 'com_content.category.%'  
  664. AND b.`title` LIKE '% :|joes|%'  
  665. AND SUBSTR(  
  666. b.title,  
  667. LOCATE(' :|joes|', b.title)+8,  
  668. (LOCATE('|: ', b.title)-(LOCATE(' :|joes|', b.title)+8))  
  669. ) = SUBSTR(  
  670. a.note,  
  671. LOCATE(' PreviousID=catid', a.note) + 17,  
  672. (LOCATE('. SectionID=', a.note)-(LOCATE(' PreviousID=catid', a.note) + 17))  
  673. );  
  674.  
  675.  
  676. -- -----------------------------------------------------------------------------  
  677. -- Step 8) Remove scripting notes in J30: #__assets.name  
  678.  
  679. UPDATE  
  680. `my_new_database`.my_prefix_assets b,  
  681. `my_new_database`.my_prefix_categories a  
  682. SET b.title=TRIM(SUBSTR(b.title, 1, LOCATE(' :|joes|', b.title)))  
  683. WHERE b.title LIKE '% :|joes|%' AND b.id=a.asset_id;  
  684.  
  685.  
  686. -- -----------------------------------------------------------------------------  
  687. -- Step 9) Correct category parent IDs based on sections  
  688.  
  689. UPDATE  
  690. `my_new_database`.my_prefix_categories a,  
  691. `my_new_database`.my_prefix_categories b  
  692. SET b.parent_id=a.id, b.path=CONCAT(a.alias, '/', b.alias)  
  693. WHERE SUBSTR(  
  694. b.note,  
  695. LOCATE('. SectionID=', b.note)+12  
  696. )=SUBSTR( a.note, LOCATE('. PreviousID=', a.note)+13)  
  697. AND SUBSTR( a.note, LOCATE('. PreviousID=', a.note)+13)<>'';  
  698.  
  699.  
  700. -- -----------------------------------------------------------------------------  
  701. -- Step 10) Correct asset parent IDs based on assets  
  702.  
  703. UPDATE  
  704. `my_new_database`.my_prefix_assets a,  
  705. `my_new_database`.my_prefix_assets b,  
  706. `my_new_database`.my_prefix_categories c,  
  707. `my_new_database`.my_prefix_categories d  
  708. SET a.parent_id=b.id WHERE a.id=c.asset_id AND c.parent_id=d.id  
  709. AND d.asset_id=b.id AND a.`level`=3;  
  710.  
  711.  
  712.  
  713. -- -----------------------------------------------------------------------------  
  714. -- II. 6. jos_components -> #__extensions  
  715. -- -----------------------------------------------------------------------------  
  716. --  
  717. -- The default tables are installed, see "III. third-party extensions" below  
  718. --  
  719.  
  720.  
  721. -- -----------------------------------------------------------------------------  
  722. -- II. 7. #__contact_details  
  723. -- -----------------------------------------------------------------------------  
  724.  
  725. -- For CONTACT DETAILS, we're going to do the following:  
  726. -- 1) Import contact categories as assets in J30  
  727. -- 2) Import contact categories as categories in J30  
  728. -- 3) Import all contacts using asset_id and cat_id  
  729. -- 4) Update foreign keys: #__categories.asset_id  
  730. -- 5) Remove migration notes [Optional Step]  
  731.  
  732. -- Affects: #__assets, #__categories, #__contact_details  
  733.  
  734.  
  735. -- -----------------------------------------------------------------------------  
  736. -- Step 1) Import contact categories as assets in J30  
  737.  
  738. SET @new_jos_assets_last_inc=(  
  739. SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastcatid  
  740. FROM `my_new_database`.my_prefix_assets  
  741. WHERE `name` LIKE '%.category.%'  
  742. ORDER BY lastcatid DESC LIMIT 0,1);  
  743. SET @new_jos_assets_last_inc=(  
  744. SELECT IFNULL(@new_jos_assets_last_inc, 0));  
  745. SET @new_jos_assets_id_last_lft=(  
  746. SELECT MAX(lft)  
  747. FROM `my_new_database`.my_prefix_assets);  
  748. SET @new_jos_contacts_parent_id=(  
  749. SELECT MIN(id)  
  750. FROM `my_new_database`.my_prefix_assets  
  751. WHERE `name`='com_contact');  
  752.  
  753. INSERT INTO  
  754. `my_new_database`.my_prefix_assets (  
  755. parent_id, lft, rgt, `level`, `name`, title, rules)  
  756. SELECT  
  757. @new_jos_contacts_parent_id,  
  758. @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2,  
  759. @new_jos_assets_id_last_lft+1, 2,  
  760. CONCAT(  
  761. 'com_contact.category.',  
  762. @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1  
  763. ),  
  764. CONCAT(title, ' :|joes|', id, '|: '),  
  765. CONCAT(  
  766. '{"core.create":[],"core.delete":[],',  
  767. '"core.edit":[],"core.edit.state":[],"core.edit.own":[]}'  
  768. )  
  769. FROM  
  770. `my_old_database`.jos_categories  
  771. WHERE  
  772. `my_old_database`.jos_categories.section='com_contact_details';  
  773.  
  774.  
  775. -- -----------------------------------------------------------------------------  
  776. -- Step 2) Import contact categories as categories in J30  
  777.  
  778. SET @new_jos_categories_extension_alias=(  
  779. SELECT alias  
  780. FROM `my_new_database`.my_prefix_categories  
  781. WHERE extension='com_content'  
  782. ORDER BY id ASC LIMIT 0,1);  
  783. SET @new_jos_categories_id_last_lft=(  
  784. SELECT MAX(lft)  
  785. FROM `my_new_database`.my_prefix_categories);  
  786. SET @new_jos_contacts_parent_id=(  
  787. SELECT MIN(id)  
  788. FROM `my_new_database`.my_prefix_categories  
  789. WHERE extension='system');  
  790. SET @new_jos_admin_id=(  
  791. SELECT `created_user_id`  
  792. FROM `my_new_database`.my_prefix_categories  
  793. WHERE `id`='1');  
  794.  
  795. INSERT INTO  
  796. `my_new_database`.my_prefix_categories (  
  797. parent_id, lft, rgt, `level`, path, extension, title, alias, note,  
  798. description, published, access, params, metadata, created_user_id,  
  799. created_time, `language`)  
  800. SELECT  
  801. @new_jos_contacts_parent_id,  
  802. @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2,  
  803. @new_jos_categories_id_last_lft+1, 1, alias,  
  804. 'com_contact', title, alias,  
  805. CONCAT('Import from J15. PreviousID=catid', id),  
  806. description, published,  
  807. CASE  
  808. WHEN access=0 THEN 1  
  809. WHEN access=1 THEN 2  
  810. WHEN access=2 THEN 7  
  811. END AS access,  
  812. '{"category_layout":"","image":""}', '{"author":"","robots":""}',  
  813. @new_jos_admin_id, NOW(), '*'  
  814. FROM  
  815. `my_old_database`.jos_categories  
  816. WHERE  
  817. `my_old_database`.jos_categories.section='com_contact_details';  
  818.  
  819.  
  820. -- -----------------------------------------------------------------------------  
  821. -- Step 3) Import all contacts using asset_id and cat_id  
  822.  
  823. SET @new_jos_admin_id=(  
  824. SELECT `created_user_id`  
  825. FROM `my_new_database`.my_prefix_categories  
  826. WHERE `id`='1');  
  827.  
  828. INSERT INTO  
  829. `my_new_database`.my_prefix_contact_details (  
  830. `name`, alias, con_position, address, suburb, state, country, postcode,  
  831. telephone, fax, misc, image, email_to, default_con, published,  
  832. checked_out, checked_out_time, ordering,  
  833. params,  
  834. user_id, catid,  
  835. access,  
  836. mobile, webpage, `language`, created, created_by, metadata )  
  837. SELECT  
  838. `name`, alias, con_position, address, suburb, state, country, postcode,  
  839. telephone, fax, CONCAT(misc, ' :|joes|', id, '|:'), image,  
  840. email_to, default_con, published, 0, 0, ordering,  
  841. IF(  
  842. params='',  
  843. '',  
  844. CONCAT(  
  845. '{"',  
  846. REPLACE(REPLACE(params, CHAR(10), '","'), '=', '":"'),  
  847. '"}'  
  848. )  
  849. ),  
  850. @admin_user_id_old_website, 4,  
  851. CASE  
  852. WHEN access=0 THEN 1  
  853. WHEN access=1 THEN 2  
  854. WHEN access=2 THEN 7  
  855. END AS access,  
  856. mobile, webpage, '*', NOW(), @new_jos_admin_id, '{"robots":"","rights":""}'  
  857. FROM  
  858. `my_old_database`.jos_contact_details;  
  859.  
  860.  
  861. -- -----------------------------------------------------------------------------  
  862. -- Step 4) Update foreign keys: #__categories.asset_id  
  863.  
  864. UPDATE  
  865. `my_new_database`.my_prefix_categories a,  
  866. `my_new_database`.my_prefix_assets b  
  867. SET a.asset_id=b.id WHERE b.`name` LIKE 'com_contact.category.%'  
  868. AND b.title LIKE '% :|joes|%'  
  869. AND SUBSTR(  
  870. b.title,  
  871. LOCATE(' :|joes|',b.title)+8,  
  872. (LOCATE('|: ',b.title)-(LOCATE(' :|joes|', b.title)+8))  
  873. ) = SUBSTR( a.note, LOCATE(' PreviousID=catid', a.note) + 17 );  
  874.  
  875.  
  876. -- -----------------------------------------------------------------------------  
  877. -- Step 5) Remove migration notes: #__assets.title & #__contact_details.misc  
  878.  
  879. UPDATE  
  880. `my_new_database`.my_prefix_assets b,  
  881. `my_new_database`.my_prefix_categories a  
  882. SET b.title=TRIM(SUBSTR(b.title, 1, LOCATE(' :|joes|', b.title)))  
  883. WHERE b.title LIKE '% :|joes|%' AND b.id=a.asset_id;  
  884.  
  885. UPDATE  
  886. `my_new_database`.my_prefix_assets b,  
  887. `my_new_database`.my_prefix_contact_details a  
  888. SET a.misc=TRIM(SUBSTR(a.misc, 1, LOCATE(' :|joes|', a.misc)))  
  889. WHERE a.misc LIKE '% :|joes|%';  
  890.  
  891.  
  892. -- -----------------------------------------------------------------------------  
  893. -- II. 8. #__content  
  894. -- -----------------------------------------------------------------------------  
  895.  
  896. -- For CONTENT/ARTICLES, we're going to do the following:  
  897. -- 1) Import articles from J15 and insert as assets in J30  
  898. -- 2) Import articles from J15 and insert as articles in J30  
  899. -- 3) Associate J15 categories and J30 assets  
  900.  
  901. -- Affects: #__assets, #__content  
  902.  
  903.  
  904. -- -----------------------------------------------------------------------------  
  905. -- Step 1) Import articles from J15 and insert as assets in J30  
  906.  
  907. SET @new_jos_assets_last_inc=(  
  908. SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastassetid  
  909. FROM `my_new_database`.my_prefix_assets  
  910. WHERE `name` LIKE 'com_content.article.%'  
  911. ORDER BY lastassetid DESC LIMIT 0,1);  
  912. SET @new_jos_assets_last_inc=(  
  913. SELECT IFNULL(@new_jos_assets_last_inc, 0));  
  914. SET @new_jos_assets_id_last_lft=(  
  915. SELECT MAX(lft) FROM `my_new_database`.my_prefix_assets);  
  916.  
  917. INSERT INTO  
  918. `my_new_database`.my_prefix_assets (  
  919. parent_id, lft, rgt, `level`, `name`, title, rules)  
  920. SELECT  
  921. 0, @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2,  
  922. @new_jos_assets_id_last_lft+1, 3,  
  923. CONCAT(  
  924. 'com_content.article.',  
  925. @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1  
  926. ),  
  927. CONCAT(title, ' :|joes|', id, '|:'),  
  928. '{"core.delete":[],"core.edit":[],"core.edit.state":[]}'  
  929. FROM  
  930. `my_old_database`.jos_content;  
  931.  
  932.  
  933. -- -----------------------------------------------------------------------------  
  934. -- Step 2) Import articles from J15 and insert as articles in J30  
  935.  
  936. INSERT INTO  
  937. `my_new_database`.my_prefix_content (  
  938. id, asset_id, title, alias, introtext, `fulltext`, state,  
  939. catid, created, created_by, created_by_alias, modified, modified_by,  
  940. publish_up, publish_down, images, urls,  
  941. attribs, version, ordering, metakey, metadesc,  
  942. access, hits, metadata, `language`)  
  943. SELECT  
  944. id, 0, title, alias, introtext, `fulltext`, state,  
  945. catid, created, created_by, created_by_alias, modified, modified_by,  
  946. publish_up, publish_down, images, urls,  
  947. IF(  
  948. attribs='',  
  949. '',  
  950. CONCAT(  
  951. '{"',  
  952. REPLACE(REPLACE(attribs, CHAR(10), '","'), '=', '":"'),  
  953. '"}'  
  954. )  
  955. ),  
  956. version, ordering, metakey, metadesc,  
  957. CASE  
  958. WHEN access=0 THEN 1  
  959. WHEN access=1 THEN 2  
  960. WHEN access=2 THEN 7  
  961. END AS access,  
  962. hits,  
  963. IF(  
  964. metadata='',  
  965. '',  
  966. CONCAT(  
  967. '{"',  
  968. REPLACE(REPLACE(metadata, CHAR(10), '","'), '=', '":"'),  
  969. '"}'  
  970. )  
  971. ), '*'  
  972. FROM  
  973. `my_old_database`.jos_content;  
  974.  
  975.  
  976. -- -----------------------------------------------------------------------------  
  977. -- Step 3) Associate J15 categories and J30 assets  
  978.  
  979. UPDATE  
  980. `my_new_database`.my_prefix_content a,  
  981. `my_old_database`.jos_content b,  
  982. `my_new_database`.my_prefix_categories c,  
  983. `my_old_database`.jos_categories d  
  984. SET a.catid=c.id  
  985. WHERE a.id=b.id AND c.alias=d.alias  
  986. AND b.catid=d.id AND c.extension='com_content';  
  987.  
  988.  
  989. UPDATE  
  990. `my_new_database`.my_prefix_content a,  
  991. `my_new_database`.my_prefix_assets e  
  992. SET e.title=a.title, a.asset_id=e.id  
  993. WHERE TRIM(CONCAT(a.title, ' :|joes|', a.id, '|:'))=TRIM(e.title);  
  994.  
  995.  
  996. UPDATE  
  997. `my_new_database`.my_prefix_assets a,  
  998. `my_new_database`.my_prefix_categories b,  
  999. `my_new_database`.my_prefix_content c  
  1000. SET a.parent_id=b.asset_id WHERE a.id=c.asset_id AND b.id=c.catid;  
  1001.  
  1002.  
  1003. -- -----------------------------------------------------------------------------  
  1004. -- II. 9. jos_content_frontpage  
  1005. -- -----------------------------------------------------------------------------  
  1006. -- As long as the Article ID was kept intact as per the previous step in this  
  1007. -- script, this should be an exact copy in J30.  
  1008.  
  1009. INSERT INTO `my_new_database`.my_prefix_content_frontpage  
  1010. (content_id, ordering)  
  1011. SELECT content_id, ordering  
  1012. FROM `my_old_database`.jos_content_frontpage;  
  1013.  
  1014.  
  1015. -- -----------------------------------------------------------------------------  
  1016. -- II. 10. jos_content_rating  
  1017. -- -----------------------------------------------------------------------------  
  1018. -- As long as the Article ID was kept intact as per the previous step in this  
  1019. -- script, this should be an exact copy in J30.  
  1020.  
  1021. INSERT INTO `my_new_database`.my_prefix_content_rating  
  1022. (content_id, rating_sum, rating_count, lastip)  
  1023. SELECT content_id, rating_sum, rating_count, lastip  
  1024. FROM `my_old_database`.jos_content_rating;  
  1025.  
  1026.  
  1027. -- -----------------------------------------------------------------------------  
  1028. -- II. 11. jos_core_log_searches -> #__core_log_searches  
  1029. -- -----------------------------------------------------------------------------  
  1030.  
  1031. INSERT INTO `my_new_database`.my_prefix_core_log_searches (search_term, hits)  
  1032. SELECT search_term, hits FROM `my_old_database`.jos_core_log_searches;  
  1033.  
  1034.  
  1035. -- -----------------------------------------------------------------------------  
  1036. -- II. 12. #__menu, #__menu_types  
  1037. -- -----------------------------------------------------------------------------  
  1038.  
  1039. -- NOTE: It is recommended you skip the menu migration and do this manually.  
  1040. -- -> Doing this manually takes me too long so I have added this section.  
  1041. -- -> Migrating menus with this script preserves SEF Joomla 1.5.x URLs.  
  1042. -- -> the Joomla Menu was the most complex section to migrate so be weary.  
  1043. -- -> Joomla 1.5 menus will be migrated as new menus in Joomla 3.0.x  
  1044. -- -> Archived Joomla 1.5 menu items will NOT be migrated.  
  1045.  
  1046. -- For MENUS, we're going to do the following:  
  1047. -- 1) Add menu types from J15 that do not exist in J30 (menutype).  
  1048. -- 2) Add menu items from J15 that do not exist in J30 (alias).  
  1049. -- 3) Re-associate correct parent ids and start rebuilding paths in J30  
  1050. -- 4) Complete hierarchical paths in J30  
  1051. -- 5) Correct internal Joomla links to sections  
  1052. -- 6) Correct internal Joomla links to categories  
  1053. -- 7) Update com_user (J15) as com_users (J30)  
  1054. -- 8) Update links containing view=frontpage (J15) to view=featured (J30)  
  1055.  
  1056. -- Affects: #__menu_types, #__menu  
  1057.  
  1058.  
  1059. -- -----------------------------------------------------------------------------  
  1060. -- Step 1) Add menu types from J15 that do not exist in J30 (menutype).  
  1061. -- mainmenu in J15 will be imported as mainmenu2 if mainmenu exists in J30.  
  1062. -- mainmenu2 in J15 will be imported as mainmenu3 if mainmenu2 exists.  
  1063.  
  1064. INSERT INTO `my_new_database`.my_prefix_menu_types  
  1065. (menutype, title, description)  
  1066. SELECT  
  1067. CASE  
  1068. WHEN a.menutype IN (  
  1069. SELECT DISTINCT  
  1070. b.menutype  
  1071. FROM  
  1072. `my_new_database`.my_prefix_menu_types b)  
  1073. THEN CONCAT(a.menutype, (  
  1074. SELECT DISTINCT  
  1075. (COUNT(b.menutype) + 1)  
  1076. FROM  
  1077. `my_new_database`.my_prefix_menu_types b  
  1078. WHERE  
  1079. b.menutype LIKE CONCAT(a.menutype, '%')  
  1080. )  
  1081. )  
  1082. ELSE a.menutype  
  1083. END AS menutype,  
  1084. CASE  
  1085. WHEN a.title IN (  
  1086. SELECT DISTINCT  
  1087. b.title  
  1088. FROM  
  1089. `my_new_database`.my_prefix_menu_types b)  
  1090. THEN CONCAT(a.title, ' ', (  
  1091. SELECT DISTINCT  
  1092. (COUNT(b.title) + 1)  
  1093. FROM  
  1094. `my_new_database`.my_prefix_menu_types b  
  1095. WHERE  
  1096. b.title LIKE CONCAT(a.title, '%')  
  1097. )  
  1098. )  
  1099. ELSE a.title  
  1100. END AS title,  
  1101. a.description  
  1102. FROM  
  1103. `my_old_database`.jos_menu_types a;  
  1104.  
  1105.  
  1106. -- -----------------------------------------------------------------------------  
  1107. -- Step 2) Add menu items from J15 that do not exist in J30 (alias).  
  1108. -- Menu items for components that do not exist in J30 will become "alias".  
  1109. -- Note that "com_user" in J15 is now "com_users" in J30.  
  1110. -- All imported menus from J15 will become new separate menus in J30.  
  1111. -- Any parent_ids=0 will become 1.  
  1112. -- Archived items (published=-2) will NOT be copied across.  
  1113. -- Home flag in J15 will not be migrated. Change this using the admin panel.  
  1114.  
  1115. SET @new_jos_menu_lft_last=(  
  1116. SELECT MAX(lft)  
  1117. FROM `my_new_database`.my_prefix_menu);  
  1118.  
  1119. INSERT INTO  
  1120. `my_new_database`.my_prefix_menu (  
  1121. menutype, title, alias, note, path, link,  
  1122. type, published, parent_id, `level`,  
  1123. component_id, browserNav, access, params, lft, rgt, home,  
  1124. `language`  
  1125. )  
  1126. SELECT  
  1127. (  
  1128. SELECT MAX(b.menutype)  
  1129. FROM `my_new_database`.my_prefix_menu_types b  
  1130. WHERE b.menutype LIKE CONCAT(a.menutype, '%')  
  1131. ),  
  1132. a.`name`,  
  1133. CASE  
  1134. WHEN a.`alias` IN (  
  1135. SELECT DISTINCT  
  1136. b.`alias`  
  1137. FROM  
  1138. `my_new_database`.my_prefix_menu b  
  1139. )  
  1140. THEN CONCAT(a.`alias`, (  
  1141. SELECT DISTINCT  
  1142. CASE  
  1143. WHEN COUNT(b.`alias`)>0 THEN a.`id`  
  1144. ELSE ''  
  1145. END AS myCount  
  1146. FROM  
  1147. `my_new_database`.my_prefix_menu b  
  1148. WHERE  
  1149. b.`alias` LIKE CONCAT(a.`alias`, '%')  
  1150. )  
  1151. )  
  1152. ELSE a.`alias`  
  1153. END AS alias,  
  1154. CONCAT(  
  1155. 'Imported from J15. PreviousID=',  
  1156. a.`id`,  
  1157. '. PreviousParentID=',  
  1158. a.parent  
  1159. ) AS note,  
  1160. a.alias as path, a.link,  
  1161. CASE  
  1162. WHEN SUBSTR(  
  1163. a.link,  
  1164. LOCATE('option=', a.link)+7,  
  1165. LOCATE('&', a.link)- (LOCATE('option=', a.link)+7))  
  1166. IN (  
  1167. SELECT DISTINCT `name`  
  1168. FROM `my_new_database`.my_prefix_extensions  
  1169. WHERE `type`='component'  
  1170. UNION ALL  
  1171. SELECT 'com_user'  
  1172. FROM `my_new_database`.my_prefix_extensions  
  1173. )  
  1174. THEN 'component'  
  1175. WHEN a.type='url'  
  1176. THEN 'url'  
  1177. ELSE 'alias'  
  1178. END AS type,  
  1179. a.published,  
  1180. CASE  
  1181. WHEN a.parent=0 THEN 1  
  1182. ELSE null  
  1183. END AS parent_id,  
  1184. a.sublevel+1,  
  1185. IFNULL((  
  1186. SELECT MIN(extension_id)  
  1187. FROM `my_new_database`.my_prefix_extensions  
  1188. WHERE `name`=SUBSTR(  
  1189. a.link,  
  1190. LOCATE('option=', a.link)+7,  
  1191. LOCATE('&', a.link)- (LOCATE('option=', a.link)+7)  
  1192. )  
  1193. ), '0') AS component_id,  
  1194. a.browserNav,  
  1195. CASE  
  1196. WHEN a.access=0 THEN 1  
  1197. WHEN a.access=1 THEN 2  
  1198. WHEN a.access=2 THEN 7  
  1199. END AS access,  
  1200. IF(a.params='', '', CONCAT(  
  1201. '{"',  
  1202. REPLACE(REPLACE(a.params, CHAR(10), '","'), '=', '":"'), '"}'  
  1203. )),  
  1204. (@new_jos_menu_lft_last:=@new_jos_menu_lft_last+2),  
  1205. (@new_jos_menu_lft_last+1), 0 as home, '*' as language  
  1206. FROM  
  1207. `my_old_database`.jos_menu a  
  1208. WHERE  
  1209. a.published>=0;  
  1210.  
  1211.  
  1212.  
  1213. -- -----------------------------------------------------------------------------  
  1214. -- Step 3) Re-associate correct parent ids and start rebuilding paths in J30  
  1215.  
  1216. UPDATE  
  1217. `my_new_database`.my_prefix_menu child,  
  1218. `my_new_database`.my_prefix_menu parent  
  1219. SET  
  1220. child.parent_id=parent.id,  
  1221. child.path=CONCAT(parent.path,'/', child.alias)  
  1222. WHERE  
  1223. TRIM(SUBSTR(  
  1224. child.note,  
  1225. LOCATE('PreviousParentID=', child.note)+17  
  1226. ))=SUBSTR(  
  1227. parent.note,  
  1228. LOCATE('PreviousID=',parent.note)+11,  
  1229. LOCATE(  
  1230. '.',  
  1231. parent.note, (  
  1232. LOCATE(  
  1233. 'PreviousID=',  
  1234. parent.note  
  1235. )+11  
  1236. )  
  1237. ) - (  
  1238. LOCATE(  
  1239. 'PreviousID=',  
  1240. parent.note  
  1241. )+11  
  1242. ))  
  1243. AND  
  1244. parent.note LIKE '%PreviousParentID=%'  
  1245. AND  
  1246. child.parent_id=0;  
  1247.  
  1248.  
  1249. -- -----------------------------------------------------------------------------  
  1250. -- Step 4) Complete hierarchical paths in J30  
  1251. -- applicable for menus up to 3 levels deep  
  1252. -- Any deeper will need to be corrected using the Joomla Admin Panel  
  1253.  
  1254. UPDATE  
  1255. `my_new_database`.my_prefix_menu child,  
  1256. `my_new_database`.my_prefix_menu parent,  
  1257. `my_new_database`.my_prefix_menu grandparent  
  1258. SET  
  1259. child.path=(  
  1260. CASE  
  1261. WHEN (TRIM(SUBSTR(  
  1262. grandparent.path,  
  1263. 1,  
  1264. LOCATE('/', grandparent.path)-1  
  1265. ))<>'')  
  1266. THEN CONCAT(  
  1267. TRIM(SUBSTR(  
  1268. grandparent.path,  
  1269. 1,  
  1270. LOCATE('/', grandparent.path)-1  
  1271. )),  
  1272. '/',  
  1273. CONCAT(TRIM(SUBSTR(  
  1274. parent.path,  
  1275. 1,  
  1276. LOCATE('/', parent.path)-1  
  1277. )), '/', child.path))  
  1278. ELSE  
  1279. (  
  1280. CASE  
  1281. WHEN LOCATE('/', child.path)>0  
  1282. THEN CONCAT(CONCAT(  
  1283. TRIM(SUBSTR(  
  1284. parent.path,  
  1285. 1,  
  1286. LOCATE('/', parent.path)-1  
  1287. )), '/', child.path))  
  1288. ELSE CONCAT(parent.path, '/', child.alias)  
  1289. END  
  1290. )  
  1291. END  
  1292. )  
  1293. WHERE  
  1294. TRIM(SUBSTR(child.path, 1, LOCATE('/', child.path)-1))=parent.alias  
  1295. AND  
  1296. TRIM(SUBSTR(parent.path, 1, LOCATE('/', parent.path)-1))=grandparent.alias  
  1297. AND  
  1298. child.note LIKE '%Imported from J15%'  
  1299. AND  
  1300. TRIM(SUBSTR(parent.path, 1, LOCATE('/', parent.path)-1)) <> '';  
  1301.  
  1302.  
  1303. -- -----------------------------------------------------------------------------  
  1304. -- Step 5) Correct internal Joomla links to sections (categories in J30)  
  1305.  
  1306. UPDATE  
  1307. `my_new_database`.my_prefix_menu a,  
  1308. `my_new_database`.my_prefix_categories b  
  1309. SET  
  1310. a.link=CONCAT(  
  1311. REPLACE(SUBSTR(  
  1312. a.link,  
  1313. 1,  
  1314. LOCATE(  
  1315. '&id=', a.link)+3),  
  1316. '&view=section',  
  1317. '&view=category'  
  1318. ),  
  1319. b.id  
  1320. )  
  1321. WHERE  
  1322. TRIM(SUBSTR(  
  1323. a.link,  
  1324. LOCATE('&id=', a.link)+4  
  1325. ))=TRIM(SUBSTR(b.note, LOCATE('PreviousID=', b.note)+11))  
  1326. AND  
  1327. LOCATE('?option=com_content', a.link)>0  
  1328. AND  
  1329. LOCATE('&id=', a.link)>0  
  1330. AND  
  1331. LOCATE('&view=section', a.link)>0  
  1332. AND  
  1333. a.note LIKE 'Imported from J15.%';  
  1334.  
  1335.  
  1336. -- -----------------------------------------------------------------------------  
  1337. -- Step 6) Correct internal Joomla links to categories  
  1338.  
  1339. UPDATE  
  1340. `my_new_database`.my_prefix_menu a,  
  1341. `my_new_database`.my_prefix_categories b  
  1342. SET  
  1343. a.link=CONCAT(TRIM(SUBSTR(a.link, 1, LOCATE('&id=', a.link)+3)), b.id)  
  1344. WHERE  
  1345. TRIM(SUBSTR(  
  1346. a.link,  
  1347. LOCATE('&id=', a.link)+4  
  1348. ))=SUBSTR(  
  1349. b.note,  
  1350. LOCATE('=catid', b.note)+6,  
  1351. LOCATE(  
  1352. '.',  
  1353. b.note,  
  1354. LOCATE('=catid', b.note)+6  
  1355. ) - (LOCATE('=catid', b.note)+6))  
  1356. AND  
  1357. LOCATE('?option=com_content', a.link)>0  
  1358. AND  
  1359. LOCATE('&id=', a.link)>0  
  1360. AND  
  1361. LOCATE('&view=category', a.link)>0  
  1362. AND  
  1363. a.note LIKE 'Imported from J15.%';  
  1364.  
  1365.  
  1366. -- -----------------------------------------------------------------------------  
  1367. -- Step 7) Update com_user (J15) as com_users (J30) (re-evaluate component_id)  
  1368.  
  1369. UPDATE `my_new_database`.my_prefix_menu a  
  1370. SET  
  1371. link=REPLACE(a.link, 'com_users', 'com_user'),  
  1372. component_id=(  
  1373. SELECT MIN(b.extension_id)  
  1374. FROM `my_new_database`.my_prefix_extensions b  
  1375. WHERE b.`name`='com_users' LIMIT 0,1)  
  1376. WHERE SUBSTR(  
  1377. a.link,  
  1378. LOCATE('option=', a.link)+7,  
  1379. LOCATE('&', a.link)- (LOCATE('option=', a.link)+7))='com_user';  
  1380.  
  1381.  
  1382. -- -----------------------------------------------------------------------------  
  1383. -- Step 8) Update links containing view=frontpage (J15) to view=featured (J30)  
  1384.  
  1385. UPDATE `my_new_database`.my_prefix_menu  
  1386. SET link=REPLACE(link, '&view=frontpage', '&view=featured')  
  1387. WHERE link LIKE '%&view=frontpage%';  
  1388.  
  1389.  
  1390.  
  1391. -- -----------------------------------------------------------------------------  
  1392. -- II. 13. jos_menu_types (this had to be done before the menus in II. 12.)  
  1393. -- -----------------------------------------------------------------------------  
  1394.  
  1395.  
  1396. -- -----------------------------------------------------------------------------  
  1397. -- II. 14. jos_messages  
  1398. -- -----------------------------------------------------------------------------  
  1399.  
  1400. INSERT INTO  
  1401. `my_new_database`.my_prefix_messages (  
  1402. message_id, user_id_from, user_id_to, folder_id, date_time, state,  
  1403. priority, `subject`, message)  
  1404. SELECT  
  1405. message_id, user_id_from, user_id_to, folder_id, date_time, state,  
  1406. priority, `subject`, message  
  1407. FROM  
  1408. `my_old_database`.jos_messages;  
  1409.  
  1410.  
  1411. -- -----------------------------------------------------------------------------  
  1412. -- II. 15. jos_messages_cfg  
  1413. -- -----------------------------------------------------------------------------  
  1414.  
  1415. INSERT INTO  
  1416. `my_new_database`.my_prefix_messages_cfg  
  1417. (user_id, cfg_name, cfg_value)  
  1418. SELECT  
  1419. user_id, cfg_name, cfg_value  
  1420. FROM  
  1421. `my_old_database`.jos_messages_cfg;  
  1422.  
  1423.  
  1424. -- -----------------------------------------------------------------------------  
  1425. -- II. 16. jos_modules -- not migrated  
  1426. -- -----------------------------------------------------------------------------  
  1427. --  
  1428. -- -----------------------------------------------------------------------------  
  1429. -- II. 17. jos_modules_menu -- not migrated  
  1430. -- -----------------------------------------------------------------------------  
  1431. --  
  1432. -- -----------------------------------------------------------------------------  
  1433. -- II. 18. jos_newsfeeds  
  1434. -- -----------------------------------------------------------------------------  
  1435.  
  1436. -- For NEWSFEEDS, we're going to do the following:  
  1437. -- 1) Import newsfeed categories from J15 and insert as assets in J30  
  1438. -- 2) Import newsfeed categories from J15 and insert as categories in J30  
  1439. -- 3) Update foreign keys between categories and assets  
  1440. -- 4) Insert newsfeeds from J15 as newsfeeds in J30  
  1441. -- 5) Associate J15 categories and newsfeeds  
  1442.  
  1443. -- Affects: #__newsfeeds  
  1444.  
  1445.  
  1446. -- -----------------------------------------------------------------------------  
  1447. -- Step 1) Import newsfeed categories from J15 and insert as assets in J30  
  1448.  
  1449. SET @new_jos_assets_last_inc=(  
  1450. SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastassetid  
  1451. FROM `my_new_database`.my_prefix_assets  
  1452. WHERE `name` LIKE '%.category.%'  
  1453. ORDER BY lastassetid DESC LIMIT 0,1);  
  1454. SET @new_jos_assets_last_inc=(  
  1455. SELECT IFNULL(@new_jos_assets_last_inc, 0));  
  1456. SET @new_jos_assets_id_last_lft=(  
  1457. SELECT MAX(lft)  
  1458. FROM `my_new_database`.my_prefix_assets);  
  1459. SET @new_jos_assets_parent_id=(  
  1460. SELECT MIN(id)  
  1461. FROM `my_new_database`.my_prefix_assets  
  1462. WHERE title='com_newsfeeds');  
  1463.  
  1464. INSERT INTO  
  1465. `my_new_database`.my_prefix_assets (  
  1466. parent_id, lft, rgt, `level`, `name`, title, rules)  
  1467. SELECT  
  1468. @new_jos_assets_parent_id,  
  1469. @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2,  
  1470. @new_jos_assets_id_last_lft+1, 1,  
  1471. CONCAT(  
  1472. 'com_newsfeeds.category.',  
  1473. @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1),  
  1474. CONCAT(title, ' :|joes|', id, '|:'),  
  1475. '{"core.create":[],"core.delete":[],"core.edit":[],"core.edit.state":[]}'  
  1476. FROM  
  1477. `my_old_database`.jos_categories  
  1478. WHERE  
  1479. section='com_newsfeeds';  
  1480.  
  1481.  
  1482. -- -----------------------------------------------------------------------------  
  1483. -- Step 2) Import newsfeed categories from J15 and insert as categories in J30  
  1484.  
  1485. SET @new_jos_categories_extension_alias=(  
  1486. SELECT alias FROM `my_new_database`.my_prefix_categories  
  1487. WHERE extension='com_newsfeeds'  
  1488. ORDER BY id ASC LIMIT 0,1);  
  1489. SET @new_jos_categories_id_last_lft=(  
  1490. SELECT MAX(lft)  
  1491. FROM `my_new_database`.my_prefix_categories);  
  1492. SET @new_jos_categories_parent_id=(  
  1493. SELECT MIN(id)  
  1494. FROM `my_new_database`.my_prefix_categories  
  1495. WHERE title='com_newsfeeds');  
  1496.  
  1497. INSERT INTO  
  1498. `my_new_database`.my_prefix_categories (  
  1499. parent_id, lft, rgt, `level`, path, extension, title, alias, note,  
  1500. description, published, access, params, metadata, created_user_id,  
  1501. created_time, `language`)  
  1502. SELECT  
  1503. 1, @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2,  
  1504. @new_jos_categories_id_last_lft+1, 1, alias,  
  1505. 'com_newsfeeds', title, alias,  
  1506. CONCAT('Import from J15. PreviousID=catid', id),  
  1507. description, published,  
  1508. CASE  
  1509. WHEN access=0 THEN 1  
  1510. WHEN access=1 THEN 2  
  1511. WHEN access=2 THEN 7  
  1512. END AS access,  
  1513. '{"target":"","image":""}', '{"page_title":"","author":"","robots":""}',  
  1514. 42, NOW(), '*'  
  1515. FROM  
  1516. `my_old_database`.jos_categories  
  1517. WHERE  
  1518. `my_old_database`.jos_categories.section='com_newsfeeds';  
  1519.  
  1520.  
  1521. -- -----------------------------------------------------------------------------  
  1522. -- Step 3) Update foreign keys between categories and assets  
  1523.  
  1524. UPDATE  
  1525. `my_new_database`.my_prefix_categories a,  
  1526. `my_new_database`.my_prefix_assets e  
  1527. SET e.title=a.title, a.asset_id=e.id  
  1528. WHERE TRIM(CONCAT(a.title, ' :|joes|', SUBSTR(  
  1529. a.note,  
  1530. LOCATE('. PreviousID=catid', a.note)+18  
  1531. ), '|:'))=TRIM(e.title);  
  1532.  
  1533.  
  1534. -- -----------------------------------------------------------------------------  
  1535. -- Step 4) Insert newsfeeds from J15 as newsfeeds in J30  
  1536.  
  1537. SET @new_jos_admin_id=(  
  1538. SELECT `created_user_id`  
  1539. FROM `my_new_database`.my_prefix_categories  
  1540. WHERE `id`='1');  
  1541.  
  1542.  
  1543. INSERT INTO  
  1544. `my_new_database`.my_prefix_newsfeeds (  
  1545. catid, `name`, alias, link, published, numarticles,  
  1546. cache_time, ordering, rtl,  
  1547. access, `language`,  
  1548. params,  
  1549. created, created_by, metadata )  
  1550. SELECT  
  1551. catid, `name`, alias, link, published, numarticles,  
  1552. cache_time, ordering, rtl, 1, '*',  
  1553. CONCAT(  
  1554. '{"show_feed_image":"",',  
  1555. '"show_feed_description":"",',  
  1556. '"show_item_description":"",',  
  1557. '"feed_character_count":"0"}'  
  1558. ),  
  1559. NOW(), @new_jos_admin_id, '{"robots":"","rights":""}'  
  1560. FROM  
  1561. `my_old_database`.jos_newsfeeds;  
  1562.  
  1563.  
  1564. -- -----------------------------------------------------------------------------  
  1565. -- Step 5) Associate J15 categories and newsfeeds  
  1566.  
  1567. UPDATE  
  1568. `my_new_database`.my_prefix_newsfeeds a,  
  1569. `my_new_database`.my_prefix_categories b  
  1570. SET a.catid=b.id  
  1571. WHERE b.note=CONCAT('Import from J15. PreviousID=catid', a.catid)  
  1572. AND b.extension='com_newsfeeds';  
  1573.  
  1574.  
  1575. -- -----------------------------------------------------------------------------  
  1576. -- II. 19. jos_plugins -> #__extensions -- not migrated  
  1577. -- -----------------------------------------------------------------------------  
  1578. --  
  1579. -- -----------------------------------------------------------------------------  
  1580. -- II. 20. jos_users -> #__users  
  1581. -- -----------------------------------------------------------------------------  
  1582.  
  1583. -- My favorite part of this script, migrate your users, passwords and all.  
  1584.  
  1585. -- For USERS, we're going to do the following:  
  1586. -- 1) Migrate all users except any matching the new admin in J30  
  1587. -- 2) Migrate user who's ID in J15 matches the new admin ID in J30  
  1588. -- 3) Recover odduser's new ID in J30 and re-associate to content  
  1589. -- 4) Recover groups used in J15 and apply correct groups to users in J30  
  1590.  
  1591. -- Affects: #__users, #__user_usergroup_map  
  1592.  
  1593.  
  1594. -- -----------------------------------------------------------------------------  
  1595. -- Step 1) Migrate all users except any matching the new admin in J30  
  1596.  
  1597. SET @new_jos_admin_id=(  
  1598. SELECT `id`  
  1599. FROM `my_new_database`.my_prefix_users  
  1600. ORDER BY registerDate ASC  
  1601. LIMIT 0,1);  
  1602. SET @new_jos_admin_email=(  
  1603. SELECT `email`  
  1604. FROM `my_new_database`.my_prefix_users  
  1605. ORDER BY registerDate ASC  
  1606. LIMIT 0,1);  
  1607.  
  1608. INSERT INTO  
  1609. `my_new_database`.my_prefix_users (  
  1610. id, `name`, username, email, `password`,  
  1611. block, sendEmail, registerDate, lastvisitDate, activation,  
  1612. params )  
  1613. SELECT  
  1614. id, `name`, username, email, `password`,  
  1615. block, sendEmail, registerDate, lastvisitDate, activation,  
  1616. IF(  
  1617. params='',  
  1618. '',  
  1619. CONCAT(  
  1620. '{"',  
  1621. REPLACE(REPLACE(params, CHAR(10), '","'), '=', '":"'),  
  1622. '"}'  
  1623. )  
  1624. )  
  1625. FROM  
  1626. `my_old_database`.jos_users  
  1627. WHERE  
  1628. `id` <> @new_jos_admin_id  
  1629. AND  
  1630. `email` <> @new_jos_admin_email;  
  1631.  
  1632.  
  1633. -- -----------------------------------------------------------------------------  
  1634. -- Step 2) Migrate user who's ID in J15 matches the new admin ID in J30  
  1635. -- - This user will take next available ID in the new database (J30).  
  1636.  
  1637. SET @new_jos_admin_id=(  
  1638. SELECT `created_user_id`  
  1639. FROM `my_new_database`.my_prefix_categories  
  1640. WHERE `id`='1');  
  1641. SET @J15_odduser_username=(  
  1642. SELECT `username`  
  1643. FROM `my_old_database`.jos_users  
  1644. WHERE `id` = @new_jos_admin_id);  
  1645.  
  1646. INSERT INTO  
  1647. `my_new_database`.my_prefix_users (  
  1648. `name`, username, email, `password`,  
  1649. block, sendEmail, registerDate, lastvisitDate, activation,  
  1650. params )  
  1651. SELECT  
  1652. `name`, username, email, `password`,  
  1653. block, sendEmail, registerDate, lastvisitDate, activation,  
  1654. IF(  
  1655. params='',  
  1656. '',  
  1657. CONCAT(  
  1658. '{"',  
  1659. REPLACE(REPLACE(params, CHAR(10), '","'), '=', '":"'),  
  1660. '"}'  
  1661. )  
  1662. )  
  1663. FROM  
  1664. `my_old_database`.jos_users  
  1665. WHERE  
  1666. id=@new_jos_admin_id;  
  1667.  
  1668.  
  1669. -- -----------------------------------------------------------------------------  
  1670. -- Step 3) Recover odduser's new ID in J30 and re-associate to content  
  1671.  
  1672. SET @new_jos_admin_id=(  
  1673. SELECT `created_user_id`  
  1674. FROM `my_new_database`.my_prefix_categories  
  1675. WHERE `id`='1');  
  1676. SET @J15_odduser_id=(  
  1677. SELECT `id`  
  1678. FROM `my_new_database`.my_prefix_users  
  1679. ORDER BY `id` DESC LIMIT 0,1);  
  1680. SET @J15_odduser_alias=(  
  1681. SELECT `name`  
  1682. FROM `my_new_database`.my_prefix_users  
  1683. ORDER BY `id` DESC LIMIT 0,1);  
  1684.  
  1685. UPDATE `my_new_database`.my_prefix_content  
  1686. SET created_by = @J15_odduser_id, created_by_alias = @J15_odduser_alias  
  1687. WHERE created_by = @new_jos_admin_id AND created_by_alias = @J15_odduser_alias;  
  1688.  
  1689.  
  1690. -- -----------------------------------------------------------------------------  
  1691. -- Step 4) Recover groups used in J15 and apply correct groups to users in J30  
  1692.  
  1693. INSERT INTO  
  1694. `my_new_database`.my_prefix_user_usergroup_map (  
  1695. user_id,  
  1696. group_id )  
  1697. SELECT  
  1698. a.aro_id AS user_id,  
  1699. CASE  
  1700. WHEN a.group_id=(  
  1701. SELECT id  
  1702. FROM `my_old_database`.jos_core_acl_aro_groups  
  1703. WHERE `name`='Super Administrator'  
  1704. LIMIT 0,1)  
  1705. THEN (  
  1706. SELECT id  
  1707. FROM `my_new_database`.my_prefix_usergroups  
  1708. WHERE title='Super Users'  
  1709. LIMIT 0,1)  
  1710. WHEN a.group_id=(  
  1711. SELECT id  
  1712. FROM `my_old_database`.jos_core_acl_aro_groups  
  1713. WHERE `name`='Administrator'  
  1714. LIMIT 0,1)  
  1715. THEN (  
  1716. SELECT id  
  1717. FROM `my_new_database`.my_prefix_usergroups  
  1718. WHERE title='Administrator'  
  1719. LIMIT 0,1)  
  1720. WHEN a.group_id=(  
  1721. SELECT id  
  1722. FROM `my_old_database`.jos_core_acl_aro_groups  
  1723. WHERE `name`='Manager'  
  1724. LIMIT 0,1)  
  1725. THEN (  
  1726. SELECT id  
  1727. FROM `my_new_database`.my_prefix_usergroups  
  1728. WHERE title='Manager'  
  1729. LIMIT 0,1)  
  1730. WHEN a.group_id=(  
  1731. SELECT id  
  1732. FROM `my_old_database`.jos_core_acl_aro_groups  
  1733. WHERE `name`='Public Backend'  
  1734. LIMIT 0,1)  
  1735. THEN (  
  1736. SELECT id  
  1737. FROM `my_new_database`.my_prefix_usergroups  
  1738. WHERE title='Public'  
  1739. LIMIT 0,1)  
  1740. WHEN a.group_id=(  
  1741. SELECT id  
  1742. FROM `my_old_database`.jos_core_acl_aro_groups  
  1743. WHERE `name`='Publisher'  
  1744. LIMIT 0,1)  
  1745. THEN (  
  1746. SELECT id  
  1747. FROM `my_new_database`.my_prefix_usergroups  
  1748. WHERE title='Publisher'  
  1749. LIMIT 0,1)  
  1750. WHEN a.group_id=(  
  1751. SELECT id  
  1752. FROM `my_old_database`.jos_core_acl_aro_groups  
  1753. WHERE `name`='Editor'  
  1754. LIMIT 0,1)  
  1755. THEN (  
  1756. SELECT id  
  1757. FROM `my_new_database`.my_prefix_usergroups  
  1758. WHERE title='Editor'  
  1759. LIMIT 0,1)  
  1760. WHEN a.group_id=(  
  1761. SELECT id  
  1762. FROM `my_old_database`.jos_core_acl_aro_groups  
  1763. WHERE `name`='Author'  
  1764. LIMIT 0,1)  
  1765. THEN (  
  1766. SELECT id  
  1767. FROM `my_new_database`.my_prefix_usergroups  
  1768. WHERE title='Author'  
  1769. LIMIT 0,1)  
  1770. WHEN a.group_id=(  
  1771. SELECT id  
  1772. FROM `my_old_database`.jos_core_acl_aro_groups  
  1773. WHERE `name`='Registered'  
  1774. LIMIT 0,1)  
  1775. THEN (  
  1776. SELECT id  
  1777. FROM `my_new_database`.my_prefix_usergroups  
  1778. WHERE title='Registered'  
  1779. LIMIT 0,1)  
  1780. WHEN a.group_id=(  
  1781. SELECT id  
  1782. FROM `my_old_database`.jos_core_acl_aro_groups  
  1783. WHERE `name`='Public Frontend'  
  1784. LIMIT 0,1)  
  1785. THEN (  
  1786. SELECT id  
  1787. FROM `my_new_database`.my_prefix_usergroups  
  1788. WHERE title='Public'  
  1789. LIMIT 0,1)  
  1790. END AS group_id  
  1791. FROM  
  1792. `my_old_database`.jos_core_acl_groups_aro_map a  
  1793. ORDER BY  
  1794. a.aro_id;  
  1795.  
  1796. -- -----------------------------------------------------------------------------  
  1797. -- Set system admins in J30 based on system admins in J15  
  1798.  
  1799. INSERT INTO  
  1800. `my_new_database`.my_prefix_user_usergroup_map (  
  1801. user_id,  
  1802. group_id )  
  1803. SELECT a.id, (  
  1804. SELECT id  
  1805. FROM `my_new_database`.my_prefix_usergroups  
  1806. WHERE title='Super Users'  
  1807. LIMIT 0,1)  
  1808. FROM `my_old_database`.jos_users a  
  1809. WHERE a.gid = (  
  1810. SELECT id  
  1811. FROM `my_old_database`.jos_core_acl_aro_groups  
  1812. WHERE `name`='Super Administrator'  
  1813. LIMIT 0,1)  
  1814. AND a.block=0;  
  1815.  
  1816.  
  1817. -- -----------------------------------------------------------------------------  
  1818. -- Set admins in J30 based on admins in J15  
  1819.  
  1820. INSERT INTO  
  1821. `my_new_database`.my_prefix_user_usergroup_map (  
  1822. user_id,  
  1823. group_id )  
  1824. SELECT a.id, (  
  1825. SELECT id  
  1826. FROM `my_new_database`.my_prefix_usergroups  
  1827. WHERE title='Administrator'  
  1828. LIMIT 0,1)  
  1829. FROM `my_old_database`.jos_users a  
  1830. WHERE a.gid = (  
  1831. SELECT id  
  1832. FROM `my_old_database`.jos_core_acl_aro_groups  
  1833. WHERE `name`='Administrator'  
  1834. LIMIT 0,1)  
  1835. AND a.block=0;  
  1836.  
  1837.  
  1838.  
  1839. -- -----------------------------------------------------------------------------  
  1840. -- II. 21. jos_weblinks  
  1841. -- -----------------------------------------------------------------------------  
  1842.  
  1843. -- For WEBLINKS, we're going to do the following:  
  1844. -- 1) Migrate all users except any matching the new admin in J30  
  1845. -- 2) Migrate previous J15 admin to new J30 system  
  1846. -- 3) Migrate user who's ID in J15 matches the new admin ID in J30  
  1847. -- 4) Recover odduser's new ID in J30 and re-associate to content  
  1848. -- 5) Recover groups used in J15 and apply correct groups to users in J30  
  1849.  
  1850. -- Affects: #__users, #__user_usergroup_map  
  1851.  
  1852.  
  1853. -- -----------------------------------------------------------------------------  
  1854. -- Step 1) Import weblinks categories from J15 and insert as assets in J30  
  1855.  
  1856. SET @new_jos_assets_last_inc=(  
  1857. SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastassetid  
  1858. FROM `my_new_database`.my_prefix_assets  
  1859. WHERE `name` LIKE '%.category.%'  
  1860. ORDER BY lastassetid DESC LIMIT 0,1);  
  1861. SET @new_jos_assets_last_inc=(  
  1862. SELECT IFNULL(@new_jos_assets_last_inc, 0));  
  1863. SET @new_jos_assets_id_last_lft=(  
  1864. SELECT MAX(lft)  
  1865. FROM `my_new_database`.my_prefix_assets);  
  1866. SET @new_jos_assets_parent_id=(  
  1867. SELECT MIN(id)  
  1868. FROM `my_new_database`.my_prefix_assets  
  1869. WHERE title='com_weblinks');  
  1870.  
  1871. INSERT INTO  
  1872. `my_new_database`.my_prefix_assets (  
  1873. parent_id, lft, rgt, `level`, `name`, title, rules)  
  1874. SELECT  
  1875. @new_jos_assets_parent_id,  
  1876. @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2,  
  1877. @new_jos_assets_id_last_lft+1, 1,  
  1878. CONCAT(  
  1879. 'com_weblinks.category.',  
  1880. @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1  
  1881. ),  
  1882. CONCAT(title, ' :|joes|', id, '|:'),  
  1883. '{"core.create":[],"core.delete":[],"core.edit":[],"core.edit.state":[]}'  
  1884. FROM  
  1885. `my_old_database`.jos_categories  
  1886. WHERE  
  1887. section='com_weblinks';  
  1888.  
  1889.  
  1890. -- -----------------------------------------------------------------------------  
  1891. -- Step 2) Import weblinks categories from J15 and insert as categories in J30  
  1892.  
  1893. SET @new_jos_categories_extension_alias=(  
  1894. SELECT alias  
  1895. FROM `my_new_database`.my_prefix_categories  
  1896. WHERE extension='com_weblinks'  
  1897. ORDER BY id ASC LIMIT 0,1);  
  1898. SET @new_jos_categories_id_last_lft=(  
  1899. SELECT MAX(lft)  
  1900. FROM `my_new_database`.my_prefix_categories);  
  1901. SET @new_jos_categories_parent_id=(  
  1902. SELECT MIN(id)  
  1903. FROM `my_new_database`.my_prefix_categories  
  1904. WHERE title='com_newsfeeds');  
  1905.  
  1906. INSERT INTO  
  1907. `my_new_database`.my_prefix_categories (  
  1908. parent_id, lft, rgt, `level`, path, extension, title, alias, note,  
  1909. description, published, access, params, metadata, created_user_id,  
  1910. created_time, `language`)  
  1911. SELECT  
  1912. 1, @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2,  
  1913. @new_jos_categories_id_last_lft+1, 1, alias,  
  1914. 'com_weblinks', title, alias,  
  1915. CONCAT('Import from J15. PreviousID=catid', id),  
  1916. description, published,  
  1917. CASE  
  1918. WHEN access=0 THEN 1  
  1919. WHEN access=1 THEN 2  
  1920. WHEN access=2 THEN 7  
  1921. END AS access,  
  1922. '{"target":"","image":""}', '{"page_title":"","author":"","robots":""}',  
  1923. 42, NOW(), '*'  
  1924. FROM  
  1925. `my_old_database`.jos_categories  
  1926. WHERE  
  1927. `my_old_database`.jos_categories.section='com_weblinks';  
  1928.  
  1929.  
  1930. -- -----------------------------------------------------------------------------  
  1931. -- Step 3) Associate categories and assets for weblinks  
  1932.  
  1933. UPDATE  
  1934. `my_new_database`.my_prefix_categories a,  
  1935. `my_new_database`.my_prefix_assets e  
  1936. SET e.title=a.title, a.asset_id=e.id  
  1937. WHERE TRIM(CONCAT(  
  1938. a.title,  
  1939. ' :|joes|',  
  1940. SUBSTR( a.note, LOCATE('. PreviousID=catid', a.note)+18), '|:'  
  1941. ))=TRIM(e.title);  
  1942.  
  1943.  
  1944. -- -----------------------------------------------------------------------------  
  1945. -- Step 4) Insert weblinks from J15 as weblinks in J30  
  1946.  
  1947. SET @new_jos_admin_id=(  
  1948. SELECT `created_user_id`  
  1949. FROM `my_new_database`.my_prefix_categories  
  1950. WHERE `id`='1');  
  1951.  
  1952. INSERT INTO  
  1953. `my_new_database`.my_prefix_weblinks (  
  1954. catid, title, alias, url, description,  
  1955. hits, state, ordering, access,  
  1956. params,  
  1957. `language`, created, created_by )  
  1958. SELECT  
  1959. catid, title, alias, url, description,  
  1960. hits, published, ordering, 1,  
  1961. IF(  
  1962. params='',  
  1963. '',  
  1964. CONCAT(  
  1965. '{"',  
  1966. REPLACE(REPLACE(params, CHAR(10), '","'), '=', '":"'),  
  1967. '"}'  
  1968. )  
  1969. ),  
  1970. '*', `date`, @new_jos_admin_id  
  1971. FROM  
  1972. `my_old_database`.jos_weblinks;  
  1973.  
  1974.  
  1975. -- -----------------------------------------------------------------------------  
  1976. -- Step 5) Associate J30 categories and weblinks  
  1977.  
  1978. UPDATE  
  1979. `my_new_database`.my_prefix_weblinks a,  
  1980. `my_new_database`.my_prefix_categories b  
  1981. SET a.catid=b.id  
  1982. WHERE b.note=CONCAT('Import from J15. PreviousID=catid', a.catid)  
  1983. AND b.extension='com_weblinks';  
  1984.  
  1985.  
  1986. -- -----------------------------------------------------------------------------  
  1987. -- III. Third-Party Extensions  
  1988. -- -----------------------------------------------------------------------------  
  1989.  
  1990. -- JCOMMENTS FOR J1.6+  
  1991. -- Search my website for "Migrate" for the final script to migrate JComments  
  1992.  
  1993. -- VIRTUEMART? (what a nightmare, major upgrade, 30FTE)  
  1994. -- ROKBOX TO YOOTHEME?  
  1995. -- JEVENTS?  
  1996.  
  1997. -- -----------------------------------------------------------------------------  
  1998. -- IV. Post-Installation  
  1999. -- -----------------------------------------------------------------------------  
  2000.  
  2001. -- For POST-INSTALL, after running this script:  
  2002. -- 1) Remove migration notes  
  2003. -- 2) Login to Joomla Admin Panel > Categories and click on "Rebuild".  
  2004. -- 3) Login to Joomla Admin Panel > Menus and click on "Rebuild".  
  2005.  
  2006.  
  2007. -- -----------------------------------------------------------------------------  
  2008. -- Remove Migration Notes from Categories  
  2009.  
  2010. UPDATE `my_new_database`.my_prefix_categories a  
  2011. SET a.`note`=SUBSTR(a.note, 1, LOCATE('Import from J15', a.note)-1)  
  2012. WHERE a.`note` LIKE 'Import from J15%';  
  2013.  
  2014.  
  2015. -- -----------------------------------------------------------------------------  
  2016. -- Remove Migration Notes from Menus  
  2017.  
  2018. UPDATE `my_new_database`.my_prefix_menu a  
  2019. SET a.`note`=SUBSTR(a.note, 1, LOCATE('Imported from J15', a.note)-1)  
  2020. WHERE a.`note` LIKE 'Imported from J15%';  
  2021.  
  2022.  
  2023. -- -----------------------------------------------------------------------------  
  2024. -- Don't forget to go CATEGORY MANAGER and click on the REBUILD icon  
  2025. -- Also double-check J1.5 administrators are correct in J2.5.  
  2026.  
  2027. -- The installer for Joomla 3.0 is a new system administrator.  
  2028.  
  2029. -- 1) Login to Joomla Admin Panel > Categories and click on "Rebuild".  
  2030. -- 2) Login to Joomla Admin Panel > Menus and click on "Rebuild".  
  2031. -- 3) Move menu item "Home" (or your default Home) to "Main Menu 2".  
  2032. -- -----------------------------------------------------------------------------

« DOWNLOAD »


Further amendments via the GUI

Login to your Joomla! admin panel:
  1. CATEGORIES
    1. Go into Content > Category Manager > Rebuild
    2. Organize the category structure.
  2. USERS
    1. Re-assign "Super Administrators" to "Super Users"
  3. MENUS
    1. Should work but if not click on "Rebuild"

Issues I ran into

  1. joomla Fatal error: Call to a member function getPath categories.php on line 435
I haven't found a way to automate this yet but you can fix all of these in one go by logging into your Joomla! 1.6.0 admin panel > content > category manager > rebuild (icon in top right).

« DOWNLOAD »

ChangeLog (for this SQL script):

  1. 2013-06-04: Fix for odd user (user in Joomla 1.5 which accidentally has ID of new admin in Joomla 3.0) - associates article content if ID and Username match.
  2. 2013-03-13: Performance tweaks. Moved to downloads section of website.
  3. 2012-11-19: Corrected user migration (new admin is determined by first user in new users table).
  4. 2012-11-15: Additional corrections for Menu migration.
  5. 2012-11-12: Corrected menu migration if menu items already exist in new site.
  6. 2012-11-08: Corrected some menu migration. J15 admins now admins in J30.
  7. 2012-11-02: Added menu migration to the script.
  8. 2012-11-02: Upgraded to account for pre-installed content.
  9. 2012-11-02: Upgraded to work with both Joomla v2.5.x or 3.0.x.
  10. 2012-07-25: Fixed bug in older versions of MySQL reserved word "name".
  11. 2012-07-24: Fixed bug of new admin not being allowed into admin panel.
  12. 2012-03-25: Changed instructions regarding changing table prefixes.
  13. 2012-03-21: Added "Update 2012" to explain compatibility with Joomla version 2.5.6.
  14. 2011-01-30: First draft of this script
Still To Do
  • Add intro script to automatically check I don't get the database names mixed up.
  • Get the menu and category rebuild scripts to run automatically.
  • Menu item issue: Confirm if J15 Sections not linking automatically (eg. "Joomla CMS")
  • Menu item issue: Weblinks component not linked to same category as in J15.
  • Menu item issue: re-examine why "Contact Us" link moves one less order than in J15.
  • Look to migrate categories, blog layouts, article settings to J30 equivalents.
  • Menu item issue: fix migrate user registration, user profile menu items.

« DOWNLOAD »



Article Comments (49)

Friday, 19th August 2016
0 Votes
Gravatar for Nicola
Nicola
Hi,
back to the 90's :-)

For those who encounters en error like:

[Err] 1062 - Duplicate entry for key 'idx_client_id_parent_id_alias_language' in prefix_menu table
(step II.12.2 line 1083

the error is caused by J15 with duplicate menu names/alias.
I had duplicates aliases placed in different menu groups, that is not possible in J2.5/3
- home
- home
- contact
- contact

So I renamed them to
- home1
- home2
- contact1
- contact2
and so on.

After that, Import was successfull.
Probably SEF is somewhat compromised by changing aliases, but menu structure is imported and navigation preserved.

Hope this helps.
Great script Joel! Thanks.

Wednesday, 8th June 2016
0 Votes
Gravatar for webtrebol
webtrebol
Thank you a lot Joel! It did its job migrating a Joomla 1.5 content into Joomla 3.5.1. I had some troubles because of some duplicated items. I would like to give an advice to all the persons that will try this script: CLEAN your old installation from ALL unpublished items and the script will work perfectly

Thursday, 18th December 2014
0 Votes
Gravatar for Joel
Joel
Interesting report LLiSEIL! I'll definitely have to review it when Joomla 3.5 comes out.

Error 1: is usually bypassed by simply removing the |joe| marker from all rows of that column. I do this when the client already has content.

Error 2: The menus as I recall were the most challenging part to migrate. I like that Joomla 1.6+ has a rebuild button in the admin interface which I do recommend.

Error 3: I could not recreate the error but it sounds as if it added all the articles and then conflicted with itself. Are the article IDs the same in both sites (they don't need to be but that would answer what the database system did with them).

Categories: another challenging part as 1.5 had sections. Again I recommend the rebuild button Joomla has for categories.

I can imagine Asset errors as my script had to copy the system Joomla was using. I didn't try copying the last phase that I thought the rebuild did. It's something I will consider in the next review of the script.

Did the weblinks not migrate or were there no weblinks in the old site? As I recall, there weren't many changes from Joomla 1.5 to 1.6 re weblinks. They ought to work the same way as articles.

Duplicates will definitely appear if the script is run several times. Unfortunately, I'm used to creating several copies of the clients Joomla database and merely modifying the script until I'm confident there are no more errors and that it runs in one go.

Thanks for your time and again for this report!

Tuesday, 16th December 2014
0 Votes
Gravatar for LLi
LLi
* Categories:
- duplicates, I suppose from the multiple times I launched the script.
- love the notes let by JMS in the 'Alias' field: 'Note : Import from J15. PreviousID=catid141. SectionID=46) '
- client used flexicontent in J 1.5 to includes subcategories; this structure is of course not preserved (all subcategories to the same level)

* Articles:
- all there; no duplicate.

Both assetweaver and assetdiagnostic show multiple Asset errors for Categoris and Articles.

* Contacts:
- all there
- duplicates

* Links
- not migrated

* Banners and Banner Categories
- are there
- duplicates that I can trash, but can't empty unless opened and saved.

Thank you very very much for sharing JMS.

Tuesday, 16th December 2014
0 Votes
Gravatar for LLi
LLi
SQL ERror 3
> -- Step 2) Import articles from J15 and insert as articles in J30 INSERT INTO `newbase_j25`.dv46z_content ( id, asset_id, title, alias, introtext, `fulltext`, state, catid, created, created_by, created_by_alias, modified, modified_by, publish_up, publish_down, images, urls, attribs, version, ordering, metakey, metadesc, access, hits, metadata, `language`) SELECT id, 0, title, alias, introtext, `fulltext`, state, catid, created, created_by, created_by_alias, modified, modified_by, publish_up, publish_down, images, urls, IF( attribs='', '', CONCAT( '{"', REPLACE(REPLACE(attribs, CHAR(10), '","'), '=', '":"'), '"}' ) ), version, ordering, metakey, metadesc, CASE [...]
> MySQL a répondu:
> #1062 - Duplicate entry '147' for key 'PRIMARY'

x3

Migrated with JMS:

1. Contents and Structure

* Extensions » Database check is fine.

* Section:
yeap.
[...]

Tuesday, 16th December 2014
0 Votes
Gravatar for LLi
LLi
I removed the banner part from the script (has no use for that site) and relaunched JMS:

SQL Error 2
> INSERT INTO `newbase_j25`.dv46z_menu ( menutype, title, alias, note, path, link, type, published, parent_id, `level`, component_id, browserNav, access, params, lft, rgt, home, `language` ) SELECT ( SELECT MAX(b.menutype) FROM `ausoleil_j25`.dv46z_menu_types b WHERE b.menutype LIKE CONCAT(a.menutype, '%') ), a.`name`, CASE WHEN a.`alias` IN ( SELECT DISTINCT b.`alias` FROM `ausoleil_j25`.dv46z_menu b ) THEN CONCAT(a.`alias`, ( SELECT DISTINCT CASE WHEN COUNT(b.`alias`)>0 THEN a.`id` ELSE '' END AS myCount FROM `ausoleil_j25`.dv46z_menu b WHERE b.`alias` LIKE CONCAT(a.`[...]
> MySQL a répondu:
> #1062 - Duplicate entry '0-1-activites-*' for key 'idx_client_id_parent_id_alias_language'

I just retried:
[...]

Tuesday, 16th December 2014
0 Votes
Gravatar for LLi
LLi
Hi, I runned JMS on a Joomla 1.5.26 associative website with a few hundred categories and articles, that uses Flexicontent for subcategories. Migrated to blank default Joomla 2.5.28.

I'd like to hear your advice about the way I handled the three SQL errors met (below) while running JMS, as well as about the database future consistency considering these three errors. I'll check it with assetweaver.php in a moment.

SQL error 1
> -- Step 5) Remove script markers from assets.
> UPDATE `newbase_j25`.dv46z_assets b,
> `ausoleil_j25`.dv46z_categories a SET b.`title` = TRIM( SUBSTR( b.`title` , 1, LOCATE( ' :|joes|', b.`title` ) ) ) ,
> b.`name` = CONCAT( 'com_banners.category.', a.`id` ) WHERE b.`title` LIKE '% :|joes|%' AND b.`id` = a.`asset_id` ;
> MySQL said:
> #1062 - Duplicate entry 'com_banners.category.10' for key 'idx_asset_name'

Migration stallled at this point.
[...]

Friday, 7th February 2014
0 Votes
Gravatar for Jessi
Jessi
Thank you very much for this informtions.I like it...Joomla Video player

Thursday, 18th July 2013
0 Votes
Gravatar for Joel
Joel
Hi dorakura,

The default value is set at the database level. So if you can access the structure of the Joomla2.5 #__content table, you can check what the default value is. Saying this, this is the default installation.

I can only suggest switching off the strict mode for this database. What is the storage engine you are using? For Joomla Sites my InnoDB is set to STRICT MODE = OFF.

I think you can check by typing the following to the database:
SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode; SELECT @@sql_mode;

MyISAM should be fine if thats causing it...

Otherwise it could be some syntax in the code but I've just run this again and it does what it was told to do.

:-)

Thursday, 18th July 2013
0 Votes
Gravatar for dorakura
dorakura
Oh, silly me! I thought the curly bracket syntax was part of SQL. Well, if metadata is assigned this string as its value, it's non-NULL, right? Why do I get the error message then? :sigh:

Thursday, 18th July 2013
1 Vote
Gravatar for Joel
Joel
Hi dorakura,

The curly bracket syntax is the way that Joomla 2.5 is storing parameters and if you look up PHP's function serialize (format for storing an array as a string), this may explain things.

The way I interpret it is that the first value is the parameter name and the second value is the data. Each parameter separated by commas and the overall fieldset surrounded by curly braces.

ie.
{"page_title":"","author":"","robots":""} page_title="" author="" robots=""

Hope that helps!

Wednesday, 17th July 2013
0 Votes
Gravatar for dorakura
dorakura
Thank you for your prompt reply. I think you misunderstood Step 2 for Part 2, perhaps. It's the second block of code. The relevant line is this:
'{"page_title":"","author":"","robots":""}' AS metadata,
I'm not familiar with this curly bracket syntax. How do I add a default value? Something like default:"" ?

Tuesday, 16th July 2013
0 Votes
Gravatar for Joel
Joel
Hi dorakura,

Thanks for your message. I get this error when I try to put a NULL value into a column that does not allow NULLs. Check your #__content table to see if a) the column "metadesc" allows NULLs, b) if the server is running in STRICT mode.

Note that I would NOT disable strict mode if it is running, not without testing every other website running off that server.

One fix is to specify a default value for that column (eg. " ") so set this to a space or 0.

Another fix is to change the SQL script a tad for the Metadesc part (Line 956):
INSERT INTO ... SELECT id, 0, title, alias, introtext, `fulltext`, state, catid, created, created_by, created_by_alias, modified, modified_by, publish_up, publish_down, images, urls, IF( attribs='', '', CONCAT( '{"', REPLACE(REPLACE(attribs, CHAR(10), '","'), '=', '":"'), '"}' ) ), version, ordering, metakey, metadesc, ...
to
INSERT INTO ... SELECT id, 0, title, alias, introtext, `fulltext`, state, catid, created, created_by, created_by_alias, modified, modified_by, publish_up, publish_down, images, urls, IF( attribs='', '', CONCAT( '{"', REPLACE(REPLACE(attribs, CHAR(10), '","'), '=', '":"'), '"}' ) ), version, ordering, metakey, IFNULL(metadesc, ''), ...

Hope that helps!

Joe

Monday, 15th July 2013
0 Votes
Gravatar for dorakura
dorakura
Thank you very much for your script. It worked great for one site, but am having trouble with another. In Step 2, I get this error:

#1364 - Field 'metadesc' doesn't have a default value

I'm migrating from 1.5 to 2.5. Any ideas? Thanks!

Tuesday, 2nd July 2013
0 Votes
Gravatar for Joel
Joel
Hi there Keith,

Thanks for the comment, that was one issue I didn't really get to test, so thanks for bringing it up. It's made me double-check the files available for download and I made the mistake of using the code required to display the SQL on this HTML page. So yes the &amp;lt; &amp;gt; signs need fixing.

As for the issue on different named groups, you can have custom ones, but then you need to add them to the new Joomla 2.5.x+ site before running this script... then...

Find the part:
WHEN a.group_id=( SELECT id FROM `my_old_database`.jos_core_acl_aro_groups WHERE `name`='Super Administrator' LIMIT 0,1) THEN ( SELECT id FROM `my_new_database`.my_prefix_usergroups WHERE title='Super Users' LIMIT 0,1)
Copy that and put it underneath it. Just change "Super Administrator" into the name of J15 group and "Super Users" into the name of J25 group.

(I used if...then...else rather than script autodiscovering the names because of all the languages in the world and this was easier for my client)

Hope that answers your questions!

I'll release an update shortly with the fix you mentioned.

Joe

Sunday, 30th June 2013
0 Votes
Gravatar for Keith
Keith
Joel,

Thanks for the great script. I had to change a couple of things to get it to work.

migrate_j15_to_j25_new_v1_7.sql had the old sql syntax of &lt;&gt; in the step to add users. changed those to , and that now works.

Our old J1.5 uses some additional usergroups that were added manually to the database, "Owners" and "Board".

It seems the script is was not updating the usertype in the newdatase.prefix_users. I tried adding usertype to the select and insert of users, and that leaves me with only some of the users correct. Not even all the "Registered" show up in the new db.

I also tried adding more sections to the add groups, that's not working either.

Any ideas?

Friday, 7th June 2013
0 Votes
Gravatar for Joel
Joel
Hi Drew,

I've got a silly question, are you sure the template you are using has been upgraded or designed for Joomla 2.5? As they are functionally different then in Joomla 1.5.

If it's a 3rd-party one then hope the suppliers have an updated version otherwise there are quite a few pages via google on upgrading a Joomla template from 1.5 to 1.6. I noticed even further changes to make if you want to make it compatible with Joomla 3.1.

I might add the extension manager > discover point you made but I think I wrote the above during Joomla version 1.6 and their discover button wasn't stable enough at the time to be considered reliable (still don't for non components).

Wednesday, 5th June 2013
0 Votes
Gravatar for drew
drew
Joel,

Thanks for the newly updated instructions. I was nearly successful in getting everything working. We had a custom built 1.5 Joomla template making it difficult to convert. I can attest that all the data looks to have come over. I would add to your article that if you are copying over other data folders from the old Joomla install that you will want to go to Extension Manager>Discover and install the old extensions once the data is copied into place. I found that copying the libraries folder into the new Joomla install will break it, so avoid copying in that folder (from my testing.) I'm going to go back and attempt this on a 2.5 Joomla now and see if I can get that template working. 3.1 is too new for it and I've attempted to make a bunch of corrections to the template code to fix it. I did however get the template to show up in the templates section, but no styles show up, and when attempting to install the template it starts to process and gets a server 500.

Wednesday, 5th June 2013
0 Votes
Gravatar for Joel
Joel
Hi Drew,

Good point. You need to copy your images folder from your old site over to your new one. You should find the missing images is because it is searching relative to the website you are on. If you have 3rd-party components then you should install these on the new site following the usual process.

I added some much needed instructions to this article following your comment yesterday as I get the question a lot "how do i use it?".

The script above only does the database (data and configuration). I advise installing the new Joomla because that will have the correct file/folder structure rather than trying to correct a Joomla 1.5 to fit in a later version. Your most important file being the "configuration.php" file in the root of your Joomla site and should never be copied to other sites (for example, I can copy the database and all files to a test system which holds a different configuration.php file and this will be an exact website copy).

Tuesday, 4th June 2013
0 Votes
Gravatar for drew
drew
Making a little progress here. I already had a copy of the live site running on this test server. So, I setup a second virtual host in apache, and pointed to my joomla3.1 install path which is running on the same server as the live site copy. I created the 2nd db so it was ready for joomla 3.1 to insatll. I edited the script so that it matches up with the 2 databases properly. I then ran through the joomla3.1 install and removed the install folder. I see articles from the old database. I notice that places where there is old articles that have content such as images or attachments are missing. How should I migrate in the rest of the old content to complete the conversion?

Thanks for the help Joel, your last comment got me moving forward faster than I had for about 2-3 hours the other day :)

-Drew

Tuesday, 4th June 2013
0 Votes
Gravatar for Joel
Joel
Hi Drew,

Yes you need to setup two versions of Joomla to use this script. Well one if you don't count your old Joomla 1.5 site. Personally, I'd have a copy of the old Joomla 1.5 site up and running. Search my site for "setup a copy of your Joomla site" if you're unsure on this.

The biggy in using this script is that you need to download and setup the latest Joomla (go through the Installation GUI until it deletes the Installation folder). For less risk and errors, don't include any sample data in the new site. It should be blank and running alongside your old site.

The migration script can then be used, you will need to rename the databases and respective table prefixes to use. Once this is done, run the script against the database and it will migrate the content, users and core Joomla objects.

Try this and if you get stuck, feel free to come back to this post.

Monday, 3rd June 2013
0 Votes
Gravatar for drew
drew
Please let me know where to begin with this, I've got a joomla 1.5 install and trying to migrate to 3.1.

Do I need to have a new mysql database with data in it, because I have a 1.5 joomla mysql db as my old db and a new mysql db? When I try "mysql shell> source .sql" it throws errors saying the tables don't exist in the new table. Also do I throw the new joomla files over the top of the old ones to complete the upgrade. I didn't see any mention of how to run the sql in the documentation. I was assuming I need to source it since it's a sql script. Let me know what I'm doing wrong. :-|

Monday, 3rd June 2013
0 Votes
Gravatar for drew
drew
I'm a bit confused here. Do I need to put the new joomla install files over the top of the old files? Do I need to have a new mysql database with data in it from the old install? When I tried sourcing the .sql migration script it threw errors because the tables don't exist in the new db. How do you run this .sql file? Thanks for any help you can provide!

-drew

Friday, 3rd May 2013
0 Votes
Gravatar for Joel
Joel
Hi Amilcar,

80,000 articles is guaranteed to slow down a site. I have had this with other migrations and we concluded that in addition to the number of articles added, Joomla 1.6+ decides that every article has to have an associated asset ("table: #__assets").

In some cases, adding table indexes may help (specifically on the assets table) but adding them to your content will slow any inserts/updates down (ie. "Times Read").

SHOW INDEXES FROM my_joomla_content;

If you have mySQL workbench (v5.x), enable the Joomla DEBUG mode and try using the database queries for some slow pages and use the EXPLAIN button (next to execute statements/run query).

Other than that, try the standard GZIP page compression techniques provided by Joomla and other third-party developers.

Thanks for your feedback!

Friday, 3rd May 2013
0 Votes
Gravatar for Amílcar
Amílcar
Hi Joel.

Thank you for this wonderful script! I managed to migrate a website with almost 80000 articles!

I had no problems with the process (MySQL Workbench showed some warnings but no errors) but the website is slower. Could you give me some hints about this?

Thanks.

Wednesday, 13th March 2013
0 Votes
Gravatar for roderick
roderick
Sorry about my last mail, I fixed it by flushing the trash and just imported categories and articles. Still a lot of work to do but your script helped for sure.
Thnx
Roderick

Wednesday, 13th March 2013
0 Votes
Gravatar for roderick
roderick
Hi,
Trying your script to migrate articles from 1.5 to 3.0.

The script stopr with this error:
#1062 - Duplicate entry '2' for key 'PRIMARY'

Catatgories are created. No articles are imported. I deleted all the sample articles and categories up front.

Have you got a suggestion?

Thnx
Roderick

Tuesday, 12th March 2013
0 Votes
Gravatar for Joel
Joel
Hi LLiSEIL,

That sounds great! I only wrote this script because jUpgrade didn't work for me.

The script above will not work on a 200K article. We have found ways of splitting the article content (assuming create function/proc privilege is not granted) is currently the only method that has worked. There's an update to the script above for where we improved performance (having to work with big migrations) which I'll upload as soon as. :-)

Would love to know the results and how this script stacks up. TBH there's a lot of work in upgrading a Joomla 1.5 site and the migration with my script is just a small part of it.

Thx for your message.

Tuesday, 12th March 2013
0 Votes
Gravatar for LLi
LLi
Hi,
Just came over this page Joel. Looking at your script and seeing how long you've been using, improving it and sharing it --thank you for that-- I can't wait to test it!
We used JUpgrade to migrate quite a few sites last year and each caused further work detecting and correcting errors in various tables (mostly Categories). Note that VirtueMart migration itself went fine.
Now I'm planning to migrate to Joomla! 2.5 one of the Joomla 1.5 website that we kept with both your script, J2XML and JUpgrade, and then compare the results. That sure will be teachful and might even be fun!
Please excuse the long message -
ps Can't believe it, already have an account on your site :-*

Monday, 28th January 2013
0 Votes
Gravatar for Joel
Joel
Hi Perina,

I haven't done too much with Joomla! v3.x but have you tried the menu and category "rebuild" buttons (using Joomla admin panel)? They help with the left/right parent structures.

Saturday, 26th January 2013
1 Vote
Gravatar for Perina
Perina
Hi, your scrip runned flawlessly, all articles imported - I can see them listed in backed of 3.0.2, however on backend open says : SQL=SELECT id FROM jos_assets WHERE alias = 'root'

same goes for all articles & categories

Saturday, 19th January 2013
0 Votes
Gravatar for Joel
Joel
Hi Zorro,

So are you saying that you have 30000 articles or that the script is creating 30000 articles? I'm curious there is a loop somewhere. If so, I'm thinking a JOIN in a statement is replicating entries.

Let me know. Cheers!

Wednesday, 16th January 2013
0 Votes
Gravatar for Zorro
Zorro
I got following error on migrating categories - first step of migration:
Column 'parent_id' cannot be null

Monday, 7th January 2013
0 Votes
Gravatar for Joel
Joel
Hi Marc,

Yes you need to create the assets along with the articles. Don't forget to associate the asset category and asset articles as well:

Taken from my script above:
-- ----------------------------------------------------------------------------- -- Step 1) Import articles from J15 and insert as assets in J30 SET @new_jos_assets_last_inc=( SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastassetid FROM my_new_database.my_prefix_assets WHERE `name` LIKE 'com_content.article.%' ORDER BY lastassetid DESC LIMIT 0,1); SET @new_jos_assets_last_inc=( SELECT IFNULL(@new_jos_assets_last_inc, 0)); SET @new_jos_assets_id_last_lft=( SELECT MAX(lft) FROM my_new_database.my_prefix_assets); INSERT INTO my_new_database.my_prefix_assets ( parent_id, lft, rgt, `level`, `name`, title, rules) SELECT 0, @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2, @new_jos_assets_id_last_lft+1, 3, CONCAT( 'com_content.article.', @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1 ), CONCAT(title, ' :|joes|', id, '|:'), '{"core.delete":[],"core.edit":[],"core.edit.state":[]}' FROM my_old_database.jos_content; -- ----------------------------------------------------------------------------- -- Step 2) Import articles from J15 and insert as articles in J30 INSERT INTO my_new_database.my_prefix_content ( id, asset_id, title, alias, introtext, `fulltext`, state, catid, created, created_by, created_by_alias, modified, modified_by, publish_up, publish_down, images, urls, attribs, version, ordering, metakey, metadesc, access, hits, metadata, `language`) SELECT id, 0, title, alias, introtext, `fulltext`, state, catid, created, created_by, created_by_alias, modified, modified_by, publish_up, publish_down, images, urls, IF( attribs='', '', CONCAT( '{"', REPLACE(REPLACE(attribs, CHAR(10), '","'), '=', '":"'), '"}' ) ), version, ordering, metakey, metadesc, CASE WHEN access=0 THEN 1 WHEN access=1 THEN 2 WHEN access=2 THEN 7 END AS access, hits, IF( metadata='', '', CONCAT( '{"', REPLACE(REPLACE(metadata, CHAR(10), '","'), '=', '":"'), '"}' ) ), '*' FROM my_old_database.jos_content;

Sunday, 6th January 2013
0 Votes
Gravatar for Marc
Marc
HEllo Joel. I am looking at your script and maybe you could help me. I want ot create 200 000 articles right in the _content table. However I must take into account the _asset table. I can not figure it out. What would be the script to do so: create 200 000 entries in _content while _asset is being update accordingly. I just need the title and alias created, the rest I fill it up using excel .

Monday, 5th November 2012
1 Vote
Gravatar for Joel
Joel
Hi Mykah,

The query you have posted indicates that you are on the same database but migrating the data from one table to another. I haven't tested this script if you have only one database available and would strongly urge you to use two separate databases. Saying that, in theory, it should still be possible.

I wrote the above script so you could run sections separately. Though I would recommend migrating articles and users together. Some sections like Menus can be skipped. Categories not though. The SQL you posted associates categories and articles. Doing this manually would involve opening both tables (content and categories) up and manually changing the catid of the J2.5 article to the corresponding category (J2.5 categories).

The only reason I think it's in a neverending loop is because you may have two aliases the same (two categories called the same) or two categories with the same ID (should be prevented by the system but it's possible).

Hope that makes sense.

Monday, 5th November 2012
1 Vote
Gravatar for Joel
Joel
Hi Mykah,

I've just migrated a site using the above script (Joomla 1.5.23 to Joomla 2.5.7). The site to migrate to had no sample data though.

I have another to do this afternoon but it will have content already in the upgrade version of the site so it will be a good test for conflicts.

Note that deleting a category using the Joomla admin panel doesn't necessarily clear this from the system as I don't see how Joomla clears it's assets table (from what I see, it doesn't).

I'll try to run a test migration with specific sample data pre-installed to match your error.

Sunday, 4th November 2012
0 Votes
Gravatar for Mykah
Mykah
Hi Joel,

It's me again. I used Migrator to go from 1.0 to 1.5 and I'm testing your script with MySQL Workbench 5.2 CE. It lasts for ever when it reaches the following:

UPDATE db.vlhcg_content a, db.jos_content b, db.vlhcg_categories c, db.jos_categories d SET a.catid=c.id WHERE a.id=b.id AND c.alias=d.alias AND b.catid=d.id AND c.extension='com_content'

May I skip this step for now and go back to it later?

Have a nice weekend. Thanks.

Friday, 2nd November 2012
0 Votes
Gravatar for Mykah
Mykah
Hi Joe, thank you for the reply.

Give me hint: it is better to use your script after cleaning entirely the new database or you suggest other approach?

I'll try to resolve the issues you pointed out.

Mykah

Friday, 2nd November 2012
1 Vote
Gravatar for Joel
Joel
Hi Mykah,

I have just completed and published an upgrade to the above script. The errors you mention are conflicts due to joomla assets/categories which already exist in your new system. I was under the impression that the updates to my above script accounted for this but every site is different i guess.

The above script updated 2012-11-02 16:30, has been tested on newly installed joomla 2.5.7 and joomla 3.0.1 with default data and brochure data already installed.

I will try to replicate the error you have been getting but i think you need to clear your assets table (which would mean clearing your site and starting again). If I solve it, I will post an update.

Joe

Friday, 2nd November 2012
0 Votes
Gravatar for Mykah
Mykah
Hi. It's me again. Justo to be clear, I would like to add something to my previous comment. The error appears to me at:
-- Step 5) Remove script markers from assets.
MySQL said: Documentation
#1062 - Duplicate entry 'com_banners.category.3' for key 'idx_asset_name'

I'm testing the import in two ways: emptying all tables of the new DB, and with the new DB with the sample DB provided by Joomla. This error appears in both.

Thank you for your time!

Friday, 2nd November 2012
0 Votes
Gravatar for Mykah
Mykah
Hi Joel!

I'm having the following error:

- ::::::::: REMOVE SCRIPTING NOTES: jos_assets.name
UPDATE joomla257.nzkwi_assets b,
joomla257.nzkwi_categories a SET b.title = TRIM( SUBSTR( b.title, 1, LOCATE( ' :|joes|', b.title ) ) ) ......
MySQL said: Documentation
#1062 - Duplicate entry 'com_content.category.19' for key 'idx_asset_name'

Do you have any sugestions?

Thanks for sharing the wonderful script!

Monday, 8th October 2012
0 Votes
Gravatar for Joel
Joel
Hi Chris,

That statement is only assigning a value to a variable. I've noticed in the excerpt you pasted that there seems to be confusion between backticks (`) and apostrophes ('). Note my syntax as I use backticks for reserved words (eg. fulltext is a MySQL reserved word but Joomla CMS decides to use it anyway for something unrelated - full article content -> this has to be escaped as such `fulltext`). Apostrophes are for values so your comment above should be:
-graf.m10tx_assets WHERE `name` LIKE '%.category.%' ORDER BY `lastcatid` DESC.
or equally
-graf.m10tx_assets WHERE name LIKE '%.category.%' ORDER BY lastcatid DESC

I had added these as some systems I was migrating to used an older version of MySQL and the script would halt on reserved words that weren't escaped with backticks.

Saturday, 29th September 2012
0 Votes
Gravatar for chris
chris
Hi, when executing the first SELECT I get the following error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-graf.m10tx_assets WHERE 'name' LIKE '%.category.%' ORDER BY 'lastcatid' DESC ' at line 2

Thursday, 19th July 2012
0 Votes
Gravatar for Joel
Joel
Sorry about that, everytime I update this page, Joomla CMS takes out all my "&lt;" "&gt;" symbols so I have to reload this script. I've now got several backups of this script stored in my "cloud" and have to remember to paste that into this article each time I update it. That line should have been
-- ::::::::: ASSOCIATE CATEGORY IDS: jos_banners.catid UPDATE mydb_upgrade.jos_categories a, mydb_upgrade.jos_banners b SET b.catid=a.id WHERE b.catid=SUBSTR( a.note, LOCATE('. PreviousID=catid', a.note)+18) AND SUBSTR( a.note, LOCATE('. PreviousID=', a.note)+13)<>'';

Thursday, 19th July 2012
1 Vote
Gravatar for E.
E.
I get an error near

-- ::::::::: ASSOCIATE CATEGORY IDS: jos_banners.catid
UPDATE mydb_upgrade.jos_categories a, mydb_upgrade.jos_banners b
SET b.catid=a.id WHERE b.catid=SUBSTR( a.note, LOCATE('. PreviousID=catid', a.note)+18)
AND SUBSTR( a.note, LOCATE('. PreviousID=', a.note)+13)''

Thursday, 21st June 2012
0 Votes
Gravatar for Joel
Joel
Additionally I would add that I still use this script for migrating to Joomla 2.5.6 websites and it still seems to work!!!

Thursday, 24th March 2011
1 Vote
Gravatar for Joel
Joel
Hiya Denis,

I like v1.6.1 but can't really vouch for it as I'm not running a live site through it yet.

For this script I just install a 1.6.1 and migrate the live site. I'm trying to add to it because just running the script only saves a small amount of time in my opinion.

Wednesday, 16th March 2011
0 Votes
Gravatar for Denis
Denis
Hi!

Thank you.

Is it right for Joomla 1.6.1 or i need to migrate to 1.6 first, then upgrade to 1.6.1 ?


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