Migrate Joomla! 1.5.x to 2.5.x+

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.

 -- IMPORTANT NOTES (to be included in SQL script):
 -- -- The script below is to migrate your existing Joomla! 1.5 database
 -- -- to an instance of Joomla! version 1.6.x or greater.
 -- -- You should have both sites up and running alongside each other.

 -- -- The instructions below uses 2 databases: LIVE and UPGRADE
 -- -- Do not run this if you only have 1 database available!

 -- -- References to J15 indicate a Joomla version 1.5.x instance.
 -- -- References to J30 indicate a Joomla version 1.6.x or greater.


 -- REQUIRED:
 -- -- A minimum of 2 databases.  The original and the one to migrate to.
 -- -- A login account (mysql user) with most privileges on BOTH databases.
 -- -- To change the database names used in the below script to the ones you use!!!


 -- SKIP MIGRATION:
 -- -- Each section was written so that it could be migrated separate to the rest.
 -- -- It is recommended to skip the menu migration and to do this manually.
 -- -- This script was initially written to only migrate articles and users.


 -- MINIMAL SETUP REQUIRED FOR USING THIS SCRIPT:
 -- -- Live = The live and original version of the website (Joomla! 1.5.x)
 -- -- Upgrade = Fresh install of Joomla! 1.6.x - 3.0.x (sample data optional).


 -- -----------------------------------------------------------------------------

 -- TABLE OF CONTENTS

 -- I. Help on using the queries below
 -- -- 1. replace all database names in the scripts below
 -- -- 2. checking before you commit changes

 -- II. Copy content from the old database to the new database and tweak:
 -- -- 1. setting some variables
 -- -- 2. jos_banner -> #__banners
 -- -- 3. jos_bannerclient -> #__banner_clients
 -- -- 4. jos_bannertrack -> #__banner_tracks
 -- -- 5. jos_categories -> #__categories
 -- -- 6. jos_components -> #__extensions
 -- -- 7. jos_contact_details -> #__contact_details
 -- -- 8. jos_content -> #__content
 -- -- 9. jos_content_frontpage -> #__content_frontpage
 -- -- 10. jos_content_rating -> #__content_rating
 -- -- 11. jos_core_log_items, jos_core_log_searches -> #__core_log_searches
 -- -- 12. jos_menu -> #__menu, #__menu_types
 -- -- 13. jos_menu_types -> merged with II.12.
 -- -- 14. jos_messages -> #__messages
 -- -- 15. jos_messages_cfg -> #__messages_cfg
 -- -- 16. jos_modules -> #__modules
 -- -- 17. jos_modules_menu -> #__modules_menu
 -- -- 18. jos_newsfeeds -> #__newsfeeds
 -- -- 19. jos_plugins -> #__extensions
 -- -- 20. jos_users, jos_core_acl_groups_aro_map -> #__user_usergroup_map, #__users
 -- -- 21. jos_weblinks -> #__weblinks

 -- III. Third-party Extensions
 -- -- JComments
 -- -- Others?

 -- IV. Post-Installation
 -- -- IMPORTANT: Category Rebuild
 -- -- IMPORTANT: Menu Rebuild


 -- READY?

 -- -----------------------------------------------------------------------------
 -- I. Help on using the queries below
 -- -----------------------------------------------------------------------------

 --       There are a few notes about running the scripts.

 --       A new table in Joomla 1.6+ is the `jos_assets` which wants to list
 --       each article, banner, category, contact, newsfeed, weblink.  This is
 --       why each snippet consists of more than one MySQL query.

 --       I have separated the queries so that they can be run individually
 --       rather than running this entire script in one go but do try to run the
 --       whole script as it maintains associations between users and content.

 --       Reminder:  backticks have been used on reserved words so that this SQL
 --       script runs on systems that do not have the most recent mySQL version
 --       installed.  These are NOT the same as apostrophes used in SQL to define
 --       non-numerical values.

 -- -----------------------------------------------------------------------------
 -- -- 1. replace all database names in the scripts below
 -- -----------------------------------------------------------------------------

 --       Yeh well you've been warned BACKUP BACKUP BACKUP!

 --       I have several databases and websites setup with exact copies of the
 --       data so I can risk this one not working.  You need as a minimum, at
 --       least 2 databases to use the queries in this script (Live and Upgrade).

 -- INSTRUCTIONS:

 --       #1 Replace "my_old_database.jos_" to the existing & current database
 --          including the table prefix (Joomla 1.5.x).

 --       #2 Replace "my_new_database" to the name of the new/upgrade database
 --          excluding the new table prefix (Joomla 1.6.x, 1.7.x, 2.5.x, 3.0.x).

 --       #3 Replace "my_new_database.my_prefix_" to the name of the new database
 --          including the new table prefix (Joomla 1.6.x, 1.7.x, 2.5.x, 3.0.x).

 --       Tip: I copy & paste this code into a text editor, then do
 --            "Replace All" with these 3 values, then copy & paste the resulting
 --            SQL into a query against my new database.
 --            - Replace Step #1: 58 Occurrences
 --            - Replace Step #2: 169 Occurrences
 --            - Replace Step #3: 167 Occurrences


 -- -----------------------------------------------------------------------------
 -- -- 2. checking before you commit changes
 -- -----------------------------------------------------------------------------

 --       If you select the part of each expression from the SELECT and run these
 --       to see what results are returned, it is reading only and not making any
 --       changes.  It will show you what data the query is planning to migrate.

 --       EXAMPLE:
 --              SET @exampleLocalVariable=1;
 --              INSERT INTO
 --                      `my_new_database`.my_prefix_sampletable
 --                      (`id`, `name`, `title`, `value`)
 --              SELECT
 --                      1, 'Joel Lipman', 'Webmaster', @exampleLocalVariable
 --              FROM
 --                      `my_old_database`.jos_sampletable
 --              WHERE
 --                      `my_old_database`.jos_sampletable.user_id=1;

 --       You could copy and paste this whole query and then remove everything
 --       from the "INSERT INTO" to just before the "SELECT" (as below):

 --              SET @exampleLocalVariable=1;
 --              SELECT
 --                      1, 'Joel Lipman', 'Webmaster', @exampleLocalVariable
 --              FROM
 --                      `my_old_database`.jos_sampletable
 --              WHERE
 --                      `my_old_database`.jos_sampletable.user_id=1;

 --       This would return the data to migrate but without making any changes.


 -- -----------------------------------------------------------------------------
 -- II. 1. Declare some variables
 -- -----------------------------------------------------------------------------

 --       A bit obsolete as we'll address each variable per section.

 --       NOTE: Because Joomla randomly selects an ID for the installing admin, I
 --       have changed the script to determine what this ID is by finding out what
 --       user_id is the first user created in the upgrade site.  This is the ID
 --       used when installing Joomla CMS.  It is important that the script below
 --       accounts for the fact that you may have a user with the same ID in your
 --       previous Joomla CMS and we would want to maintain any relationships and
 --       history of both entities.

 --       As a workaround, the installing admin ID will stay as is and will be
 --       a new user in your users table.  The user who originally was assigned
 --       that ID will be moved to the end of the list and any associated content
 --       and events will be re-linked to that user.


 -- -----------------------------------------------------------------------------
 -- II. 2. jos_banner to #__banners
 -- -----------------------------------------------------------------------------

 -- For BANNERS, we're going to do the following:
 --      1)  Create banner-category assets
 --      2)  Import banner categories
 --      3)  Migrate banners from J15 to J30
 --      4)  Update foreign keys
 --      5)  Remove notes used by script
 --      6)  Associate category IDs
 --      7)  Remove remaining migration notes [Optional Step]

 --      Affects:  #__assets, #__banners, #__categories
 --      * You need to run steps 1-7 for a full transfer of banners (does not
 --        include clients and tracks, run the remainder of this section for these).

 -- -----------------------------------------------------------------------------
 -- Step 1)  Create banner-category assets:
 --          - Import banner categories from J15 and insert as assets in J30


 SET @new_jos_assets_last_inc=(
         SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastcatid
         FROM `my_new_database`.my_prefix_assets
      WHERE `name` LIKE '%.category.%'
         ORDER BY `lastcatid` 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);
 SET @new_jos_banners_parent_id=(
         SELECT MIN(`id`)
      FROM `my_new_database`.my_prefix_assets
      WHERE `name`='com_banners');
 SET @new_jos_banners_parent_id = (
         SELECT IFNULL(@new_jos_banners_parent_id, 0));

 INSERT INTO
         `my_new_database`.my_prefix_assets (
         `parent_id`, `lft`, `rgt`, `level`, `name`, `title`, `rules`)
 SELECT
         @new_jos_banners_parent_id AS parent_id,
         @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2 AS lft,
         @new_jos_assets_id_last_lft + 1 AS rgt,
         2 AS level,
         CONCAT(
           'com_banners.category.',
           @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1
      ) AS name,
         CONCAT(`title`, ' :|joes|', `id`, '|: ') AS title,
         '{"core.create":[],"core.delete":[],"core.edit":[],"core.edit.state":[]}'
 FROM
         `my_old_database`.jos_categories
 WHERE
         `my_old_database`.jos_categories.section='com_banner';


 -- -----------------------------------------------------------------------------
 -- Step 2)  Import banner categories:
 --          - Import banner categories from J15 and insert as categories in J30


 SET @new_jos_categories_extension_alias=(
         SELECT `alias`
      FROM `my_new_database`.my_prefix_categories
         WHERE `extension`='com_content'
      ORDER BY `id` ASC
      LIMIT 0,1);
 SET @new_jos_categories_id_last_lft=(
         SELECT MAX(`lft`)
      FROM `my_new_database`.my_prefix_categories);
 SET @new_jos_banners_parent_id=(
         SELECT MIN(`id`)
      FROM `my_new_database`.my_prefix_categories
      WHERE `extension`='system');
 SET @new_jos_admin_id=(
         SELECT `created_user_id`
      FROM `my_new_database`.my_prefix_categories
      WHERE `id`='1');

 INSERT INTO
         `my_new_database`.my_prefix_categories (
         `parent_id`, `lft`, `rgt`, `level`, `path`, `extension`, `title`,
      `alias`, `note`, `description`, `published`, `access`, `params`,
      `metadata`, `created_user_id`, `created_time`, `language`)
 SELECT
         @new_jos_banners_parent_id AS parent_id,
         @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2 AS lft,
         @new_jos_categories_id_last_lft+1 AS rgt, 1 AS `level`,
         CAST(@new_jos_categories_extension_alias AS CHAR) AS path,
         'com_banners' AS extension, c.`title`, c.`alias`,
         CONCAT('Import from J15. PreviousID=catid', c.`id`) AS note,
         c.`description`, c.`published`,
         CASE
           WHEN c.`access`=0 THEN 1
           WHEN c.`access`=1 THEN 2
           WHEN c.`access`=2 THEN 7
      END AS access,
         '{"target":"","image":""}' AS params,
      '{"page_title":"","author":"","robots":""}' AS metadata,
         @new_jos_admin_id AS created_user_id, b.date AS created_time,
      '*' AS `language`
 FROM
         `my_old_database`.jos_categories c
         INNER JOIN `my_old_database`.jos_banner b ON b.catid=c.id
 WHERE
         c.section='com_banner';


 -- -----------------------------------------------------------------------------
 -- Step 3)  Migrate banners from J15 to J30:
 --          - Import banners from J15 and insert as banners in J30
 --          - Maintain asset_id and category_id
 --          - J30 Assigns ID of 42 to creator of vanilla (Joomla!) banners
 --          -> Need to use ID of creator of first category as creator of banners.

 SET @new_jos_admin_id=(
         SELECT `created_user_id`
      FROM `my_new_database`.my_prefix_categories
      WHERE `id`='1');

 INSERT INTO
         `my_new_database`.my_prefix_banners (
         `cid`, `type`, `name`, `alias`, `imptotal`, `impmade`, `clicks`,
      `clickurl`, `state`, `catid`, `description`, `custombannercode`, `sticky`,
      `ordering`, `params`, `track_clicks`, `track_impressions`, `publish_up`,
      `publish_down`, `created`, `language`)
 SELECT
         `cid`, `type`, `name`, `alias`, `imptotal`, `impmade`, `clicks`,
      `clickurl`, 1, `catid`, CONCAT(`description`, '(PreviousID=', `bid`, ')'),
      `custombannercode`, `sticky`, `ordering`,
      IF(
           `params`='',
           '',
           CONCAT(
                '{"',
                REPLACE(REPLACE(`params`, CHAR(10), '","'), '=', '":"'),
                '"}'
           )
      ),
         0, 0, `publish_up`, `publish_down`, `date`, '*'
 FROM
         `my_old_database`.jos_banner;


 -- -----------------------------------------------------------------------------
 -- Step 4)  Update foreign keys in J30:
 --          - Set categories asset_id to assets ID.


 UPDATE
      `my_new_database`.my_prefix_categories a,
      `my_new_database`.my_prefix_assets b
 SET a.`asset_id`=b.`id` WHERE b.`name` LIKE 'com_banners.category.%'
 AND b.`title` LIKE '% :|joes|%'
 AND SUBSTR(
      b.`title`,
      LOCATE(' :|joes|', b.`title`)+8,
      (LOCATE('|: ', b.`title`) - ( LOCATE(' :|joes|', b.`title`)+8))
     ) = SUBSTR( a.`note`, LOCATE(' PreviousID=catid', a.`note`) + 17 );


 -- -----------------------------------------------------------------------------
 -- Step 5)  Remove script markers from assets:
 --          - Remove everything after and including the string ":|joes|"


 UPDATE
      `my_new_database`.my_prefix_assets b,
      `my_new_database`.my_prefix_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`;


 -- -----------------------------------------------------------------------------
 -- Step 6)  Associate category IDs:
 --          - Determine IDs by script notes "PreviousID="


 UPDATE
      `my_new_database`.my_prefix_categories a,
      `my_new_database`.my_prefix_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)<>'';

 SET @new_jos_banners_parent_id=(
         SELECT MIN(`id`)
      FROM `my_new_database`.my_prefix_categories
      WHERE `extension`='com_banners');

 UPDATE
      `my_new_database`.my_prefix_banners a,
      `my_old_database`.jos_categories b
 SET a.`catid`=@new_jos_banners_parent_id
 WHERE a.`catid` NOT IN (SELECT `id` FROM `my_old_database`.jos_categories);


 -- -----------------------------------------------------------------------------
 -- Step 7)  Remove remaining migration notes [Optional Step]:
 --          - Remove notes of "Import from J15. PreviousID=..."
 --          * Comment this out with a preceding double-hyphen if you want to
 --            leave the notes in the system.  These say what the ID references
 --            were in the J15 system. I leave them in until satisfied the
 --            process worked.  You can use the Joomla admin panel to remove them
 --            later instead of running the below UPDATE.


 UPDATE `my_new_database`.my_prefix_categories
 SET `note`=''
 WHERE `note` LIKE 'Import from J15. Previous%';


 -- -----------------------------------------------------------------------------
 -- II. 3. jos_bannerclient to #__banner_clients
 -- -----------------------------------------------------------------------------

 -- For BANNER CLIENTS, we're going to do the following:
 --      1)  Import banner clients from J15 and insert into J30
 --      2)  Re-associate banner clients (in case J30 already includes new clients)

 --      Affects:  #__banners, #__banner_clients


 -- -----------------------------------------------------------------------------
 -- Step 1)  Import banner clients from J15 and insert into J30:
 --          - Assign a default state of published (state did not exist in J15)

 INSERT INTO `my_new_database`.my_prefix_banner_clients (
         `name`, `contact`, `email`, `extrainfo`, `state`, `checked_out`,
      `checked_out_time`, `purchase_type`, `track_clicks`, `track_impressions`)
 SELECT `name`, `contact`, `email`, `extrainfo`, 1, 0, 0, 0, 0, 0
 FROM `my_old_database`.jos_bannerclient;


 -- -----------------------------------------------------------------------------
 -- Step 2)  Re-associate banner clients (in case J30 already includes new clients)
 --          - Re-associate if description contains migration notes

 UPDATE `my_new_database`.my_prefix_banners a
 INNER JOIN `my_old_database`.jos_bannerclient c ON a.cid=c.cid
 INNER JOIN `my_new_database`.my_prefix_banner_clients b ON b.email=c.email
 SET a.cid=b.id
 WHERE a.description LIKE '%(PreviousID=%';


 -- -----------------------------------------------------------------------------
 -- II. 4. jos_bannertrack to #__banner_tracks
 -- -----------------------------------------------------------------------------

 -- For BANNER TRACKS, we're going to do the following:
 --      1)  Import banner tracks from J15 and insert into J30.
 --          & Re-associate banner track IDs to "new" IDs in J30.
 --      2)  Remove migration notes in J30 [Optional Step]

 --      Affects:  #__banner_tracks, #__banners


 -- -----------------------------------------------------------------------------
 -- Step 1)  Import banner tracks from J15 and insert into J30 & associate IDs.

 INSERT INTO `my_new_database`.my_prefix_banner_tracks
      (`track_date`, `track_type`, `banner_id`)
 SELECT a.`track_date`, a.`track_type`, b.`id`
 FROM `my_old_database`.jos_bannertrack a
 INNER JOIN `my_new_database`.my_prefix_banners b
 ON SUBSTR(
         b.`description`,
         LOCATE('(PreviousID=', b.`description`)+12,
         LOCATE(')', b.`description`) - (LOCATE('(PreviousID=', b.`description`)+12)
 )=a.banner_id;


 -- -----------------------------------------------------------------------------
 -- Step 2)  Remove migration notes in J30 [Optional Step]
 --          - Remove string "(PreviousID=" where it exists in descriptions

 UPDATE `my_new_database`.my_prefix_banners
 SET description=SUBSTR(description, 1, LOCATE('(PreviousID=', description)-1)
 WHERE description LIKE '%(PreviousID=%';


 -- -----------------------------------------------------------------------------
 -- II. 5. #__categories
 -- -----------------------------------------------------------------------------

 -- NOTE: Categories in Joomla 1.5 have no parent category and sections do not
 --       exist in Joomla 1.6.0 or greater

 -- For CATEGORIES, we're going to do the following:
 --        1) Import sections as new categories
 --        2) Import categories as sub-categories
 --        3) Update foreign keys for J30: #__categories.asset_id
 --        4) Remove Scripting Notes from J30: #__assets.name
 --        5) Import categories from J15 and insert as assets in J30
 --        6) Insert categories of J15 as categories in J30
 --        7) Update foreign keys in J30: #__categories.asset_id
 --        8) Remove scripting notes in J30: #__assets.name
 --        9) Correct category parent IDs based on sections
 --       10) Correct asset parent IDs based on assets

 --       note-to-self: don't import categories that are for others
 --                     (eg. com_banners - as these are migrated with banners)
 --                     J15 sections only exist for content?

 --      Affects:  #__assets, #__categories


 -- -----------------------------------------------------------------------------
 -- Step 1)  Import sections as new assets
 --          - Import sections from J15 and insert as assets in J30

 SET @new_jos_assets_last_inc=(
         SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastcatid
         FROM `my_new_database`.my_prefix_assets
      WHERE `name` LIKE '%.category.%'
         ORDER BY `lastcatid` 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);
 SET @new_jos_assets_id_parent=(
         SELECT MIN(`id`)
      FROM `my_new_database`.my_prefix_assets
      WHERE `name`='com_content');

 INSERT INTO
         `my_new_database`.my_prefix_assets (
         `parent_id`, `lft`, `rgt`, `level`, `name`, `title`, `rules`)
 SELECT
         @new_jos_assets_id_parent,
         @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2,
         @new_jos_assets_id_last_lft+1, 2,
         CONCAT(
           'com_content.category.',
           @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1
      ),
         CONCAT(`title`, ' :|joes|', `id`, '|: '),
         CONCAT(
           '{"core.create":[],"core.delete":[],',
           '"core.edit":[],"core.edit.state":[],"core.edit.own":[]}'
      )
 FROM
         `my_old_database`.jos_sections;


 -- -----------------------------------------------------------------------------
 -- Step 2)  Import sections as new categories
 --          - Import sections from J15 and insert as categories in J30

 SET @new_jos_categories_extension_alias=(
         SELECT `alias`
      FROM `my_new_database`.my_prefix_categories
         WHERE `extension`='com_content'
      ORDER BY `id` ASC LIMIT 0,1);
 SET @new_jos_categories_id_last_lft=(
         SELECT MAX(`lft`)
      FROM `my_new_database`.my_prefix_categories);
 SET @new_jos_admin_id=(
         SELECT `created_user_id`
      FROM `my_new_database`.my_prefix_categories
      WHERE `id`='1');

 INSERT INTO
         `my_new_database`.my_prefix_categories (
         `parent_id`, `lft`, `rgt`, `level`, `path`, `extension`, `title`,
      `alias`, `note`, `description`, `published`, `access`, `params`,
      `metadata`, `created_user_id`, `created_time`, `language`
         )
 SELECT
         1, @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2,
         @new_jos_categories_id_last_lft+1, 1, alias,
         'com_content', title, alias, CONCAT('Import from J15. PreviousID=', id),
         description, published,
         CASE
           WHEN access=0 THEN 1
           WHEN access=1 THEN 2
           WHEN access=2 THEN 7
      END AS access,
         '{"target":"","image":""}', '{"page_title":"","author":"","robots":""}',
         @new_jos_admin_id, NOW(), '*'
 FROM
         `my_old_database`.jos_sections;


 -- -----------------------------------------------------------------------------
 -- Step 3)  Update foreign keys for J30: #__categories.asset_id

 UPDATE
      `my_new_database`.my_prefix_categories a,
      `my_new_database`.my_prefix_assets b
 SET a.`asset_id`=b.`id` WHERE b.`name` LIKE 'com_content.category.%'
 AND b.`title` LIKE '% :|joes|%'
 AND SUBSTR(
      b.`title`,
      LOCATE(' :|joes|', b.`title`)+8,
      (LOCATE('|: ', b.`title`)-(LOCATE(' :|joes|', b.`title`)+8))
     ) = SUBSTR( a.`note`, LOCATE(' PreviousID=', a.`note`) + 12 );


 -- -----------------------------------------------------------------------------
 -- Step 4)  Remove Scripting Notes from J30: #__assets.name

 UPDATE
      `my_new_database`.my_prefix_assets b,
      `my_new_database`.my_prefix_categories a
 SET b.title=TRIM(SUBSTR(b.title, 1, LOCATE(' :|joes|', b.title)))
 WHERE b.title LIKE '% :|joes|%' AND b.id=a.asset_id;


 -- -----------------------------------------------------------------------------
 -- Step 5)  Import categories from J15 and insert as assets in J30

 SET @new_jos_assets_last_inc=(
         SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastcatid
         FROM `my_new_database`.my_prefix_assets
      WHERE `name` LIKE '%.category.%'
         ORDER BY lastcatid DESC LIMIT 0,1);
 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.category.',
           @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1
      ),
         CONCAT(title, ' :|joes|', id, '|: '),
      CONCAT(
           '{"core.create":[],"core.delete":[],',
           '"core.edit":[],"core.edit.state":[],"core.edit.own":[]}'
      )
 FROM
         `my_old_database`.jos_categories
 WHERE
         `my_old_database`.jos_categories.section>0;


 -- -----------------------------------------------------------------------------
 -- Step 6)  Insert categories of J15 as categories in J30

 SET @new_jos_categories_extension_alias=(
         SELECT alias FROM `my_new_database`.my_prefix_categories
         WHERE extension='com_content'
      ORDER BY id ASC LIMIT 0,1);
 SET @new_jos_categories_id_last_lft=(
         SELECT MAX(lft)
      FROM `my_new_database`.my_prefix_categories);
 SET @new_jos_admin_id=(
         SELECT `created_user_id`
      FROM `my_new_database`.my_prefix_categories
      WHERE `id`='1');

 INSERT INTO
         `my_new_database`.my_prefix_categories (
         parent_id, lft, rgt, `level`, path, extension, title, alias,
      note, description, published, access, params, metadata, created_user_id,
         created_time, `language`)
 SELECT
         0, @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2,
         @new_jos_categories_id_last_lft+1, 2, @new_jos_categories_extension_alias,
         'com_content', title, alias,
      CONCAT('Import from J15. PreviousID=catid', id, '. SectionID=', section),
         description, published,
         CASE
           WHEN access=0 THEN 1
           WHEN access=1 THEN 2
           WHEN access=2 THEN 7
      END AS access,
         '{"target":"","image":""}', '{"page_title":"","author":"","robots":""}',
         @new_jos_admin_id, NOW(), '*'
 FROM
         `my_old_database`.jos_categories
 WHERE
         `my_old_database`.jos_categories.section>0;


 -- -----------------------------------------------------------------------------
 -- Step 7)  Update foreign keys in J30: #__categories.asset_id

 UPDATE
      `my_new_database`.my_prefix_categories a,
      `my_new_database`.my_prefix_assets b
 SET a.`asset_id`=b.`id` WHERE b.`name` LIKE 'com_content.category.%'
 AND b.`title` LIKE '% :|joes|%'
 AND SUBSTR(
      b.title,
      LOCATE(' :|joes|', b.title)+8,
      (LOCATE('|: ', b.title)-(LOCATE(' :|joes|', b.title)+8))
     ) = SUBSTR(
      a.note,
      LOCATE(' PreviousID=catid', a.note) + 17,
      (LOCATE('. SectionID=', a.note)-(LOCATE(' PreviousID=catid', a.note) + 17))
     );


 -- -----------------------------------------------------------------------------
 -- Step 8)  Remove scripting notes in J30: #__assets.name

 UPDATE
      `my_new_database`.my_prefix_assets b,
      `my_new_database`.my_prefix_categories a
 SET b.title=TRIM(SUBSTR(b.title, 1, LOCATE(' :|joes|', b.title)))
 WHERE b.title LIKE '% :|joes|%' AND b.id=a.asset_id;


 -- -----------------------------------------------------------------------------
 -- Step 9)  Correct category parent IDs based on sections

 UPDATE
      `my_new_database`.my_prefix_categories a,
      `my_new_database`.my_prefix_categories b
 SET b.parent_id=a.id, b.path=CONCAT(a.alias, '/', b.alias)
 WHERE SUBSTR(
      b.note,
      LOCATE('. SectionID=', b.note)+12
       )=SUBSTR( a.note, LOCATE('. PreviousID=', a.note)+13)
 AND SUBSTR( a.note, LOCATE('. PreviousID=', a.note)+13)<>'';


 -- -----------------------------------------------------------------------------
 -- Step 10)  Correct asset parent IDs based on assets

 UPDATE
      `my_new_database`.my_prefix_assets a,
      `my_new_database`.my_prefix_assets b,
         `my_new_database`.my_prefix_categories c,
      `my_new_database`.my_prefix_categories d
 SET a.parent_id=b.id WHERE a.id=c.asset_id AND c.parent_id=d.id
 AND d.asset_id=b.id AND a.`level`=3;



 -- -----------------------------------------------------------------------------
 -- II. 6. jos_components -> #__extensions
 -- -----------------------------------------------------------------------------
 --
 -- The default tables are installed, see "III. third-party extensions" below
 --


 -- -----------------------------------------------------------------------------
 -- II. 7. #__contact_details
 -- -----------------------------------------------------------------------------

 -- For CONTACT DETAILS, we're going to do the following:
 --        1) Import contact categories as assets in J30
 --        2) Import contact categories as categories in J30
 --        3) Import all contacts using asset_id and cat_id
 --        4) Update foreign keys: #__categories.asset_id
 --        5) Remove migration notes [Optional Step]

 --      Affects:  #__assets, #__categories, #__contact_details


 -- -----------------------------------------------------------------------------
 -- Step 1) Import contact categories as assets in J30

 SET @new_jos_assets_last_inc=(
         SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastcatid
         FROM `my_new_database`.my_prefix_assets
      WHERE `name` LIKE '%.category.%'
         ORDER BY lastcatid 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);
 SET @new_jos_contacts_parent_id=(
         SELECT MIN(id)
      FROM `my_new_database`.my_prefix_assets
      WHERE `name`='com_contact');

 INSERT INTO
         `my_new_database`.my_prefix_assets (
         parent_id, lft, rgt, `level`, `name`, title, rules)
 SELECT
         @new_jos_contacts_parent_id,
      @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2,
         @new_jos_assets_id_last_lft+1, 2,
         CONCAT(
           'com_contact.category.',
           @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1
      ),
         CONCAT(title, ' :|joes|', id, '|: '),
      CONCAT(
           '{"core.create":[],"core.delete":[],',
           '"core.edit":[],"core.edit.state":[],"core.edit.own":[]}'
      )
 FROM
         `my_old_database`.jos_categories
 WHERE
         `my_old_database`.jos_categories.section='com_contact_details';


 -- -----------------------------------------------------------------------------
 -- Step 2) Import contact categories as categories in J30

 SET @new_jos_categories_extension_alias=(
         SELECT alias
      FROM `my_new_database`.my_prefix_categories
         WHERE extension='com_content'
      ORDER BY id ASC LIMIT 0,1);
 SET @new_jos_categories_id_last_lft=(
         SELECT MAX(lft)
      FROM `my_new_database`.my_prefix_categories);
 SET @new_jos_contacts_parent_id=(
         SELECT MIN(id)
      FROM `my_new_database`.my_prefix_categories
      WHERE extension='system');
 SET @new_jos_admin_id=(
         SELECT `created_user_id`
      FROM `my_new_database`.my_prefix_categories
      WHERE `id`='1');

 INSERT INTO
         `my_new_database`.my_prefix_categories (
         parent_id, lft, rgt, `level`, path, extension, title, alias, note,
         description, published, access, params, metadata, created_user_id,
         created_time, `language`)
 SELECT
         @new_jos_contacts_parent_id,
      @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2,
         @new_jos_categories_id_last_lft+1, 1, alias,
         'com_contact', title, alias,
      CONCAT('Import from J15. PreviousID=catid', id),
         description, published,
         CASE
           WHEN access=0 THEN 1
           WHEN access=1 THEN 2
           WHEN access=2 THEN 7
      END AS access,
         '{"category_layout":"","image":""}', '{"author":"","robots":""}',
         @new_jos_admin_id, NOW(), '*'
 FROM
         `my_old_database`.jos_categories
 WHERE
         `my_old_database`.jos_categories.section='com_contact_details';


 -- -----------------------------------------------------------------------------
 -- Step 3) Import all contacts using asset_id and cat_id

 SET @new_jos_admin_id=(
         SELECT `created_user_id`
      FROM `my_new_database`.my_prefix_categories
      WHERE `id`='1');

 INSERT INTO
         `my_new_database`.my_prefix_contact_details (
         `name`, alias, con_position, address, suburb, state, country, postcode,
         telephone, fax, misc, image, email_to, default_con, published,
         checked_out, checked_out_time, ordering,
         params,
         user_id, catid,
         access,
         mobile, webpage, `language`, created, created_by, metadata )
 SELECT
         `name`, alias, con_position, address, suburb, state, country, postcode,
         telephone, fax, CONCAT(misc, ' :|joes|', id, '|:'), image,
         email_to, default_con, published, 0, 0, ordering,
         IF(
           params='',
           '',
           CONCAT(
                '{"',
                REPLACE(REPLACE(params, CHAR(10), '","'), '=', '":"'),
                '"}'
           )
      ),
         @admin_user_id_old_website, 4,
         CASE
           WHEN access=0 THEN 1
           WHEN access=1 THEN 2
           WHEN access=2 THEN 7
      END AS access,
         mobile, webpage, '*', NOW(), @new_jos_admin_id, '{"robots":"","rights":""}'
 FROM
         `my_old_database`.jos_contact_details;


 -- -----------------------------------------------------------------------------
 -- Step 4) Update foreign keys: #__categories.asset_id

 UPDATE
      `my_new_database`.my_prefix_categories a,
      `my_new_database`.my_prefix_assets b
 SET a.asset_id=b.id WHERE b.`name` LIKE 'com_contact.category.%'
 AND b.title LIKE '% :|joes|%'
 AND SUBSTR(
      b.title,
      LOCATE(' :|joes|',b.title)+8,
      (LOCATE('|: ',b.title)-(LOCATE(' :|joes|', b.title)+8))
     ) = SUBSTR( a.note, LOCATE(' PreviousID=catid', a.note) + 17 );


 -- -----------------------------------------------------------------------------
 -- Step 5) Remove migration notes: #__assets.title & #__contact_details.misc

 UPDATE
      `my_new_database`.my_prefix_assets b,
      `my_new_database`.my_prefix_categories a
 SET b.title=TRIM(SUBSTR(b.title, 1, LOCATE(' :|joes|', b.title)))
 WHERE b.title LIKE '% :|joes|%' AND b.id=a.asset_id;

 UPDATE
      `my_new_database`.my_prefix_assets b,
      `my_new_database`.my_prefix_contact_details a
 SET a.misc=TRIM(SUBSTR(a.misc, 1, LOCATE(' :|joes|', a.misc)))
 WHERE a.misc LIKE '% :|joes|%';


 -- -----------------------------------------------------------------------------
 -- II. 8. #__content
 -- -----------------------------------------------------------------------------

 -- For CONTENT/ARTICLES, we're going to do the following:
 --      1) Import articles from J15 and insert as assets in J30
 --      2) Import articles from J15 and insert as articles in J30
 --      3) Associate J15 categories and J30 assets

 --      Affects:  #__assets, #__content


 -- -----------------------------------------------------------------------------
 -- 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;


 -- -----------------------------------------------------------------------------
 -- Step 3) Associate J15 categories and J30 assets

 UPDATE
      `my_new_database`.my_prefix_content a,
      `my_old_database`.jos_content b,
         `my_new_database`.my_prefix_categories c,
      `my_old_database`.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';


 UPDATE
      `my_new_database`.my_prefix_content a,
      `my_new_database`.my_prefix_assets e
 SET e.title=a.title, a.asset_id=e.id
 WHERE TRIM(CONCAT(a.title, ' :|joes|', a.id, '|:'))=TRIM(e.title);


 UPDATE
      `my_new_database`.my_prefix_assets a,
      `my_new_database`.my_prefix_categories b,
      `my_new_database`.my_prefix_content c
 SET a.parent_id=b.asset_id WHERE a.id=c.asset_id AND b.id=c.catid;


 -- -----------------------------------------------------------------------------
 -- II. 9. jos_content_frontpage
 -- -----------------------------------------------------------------------------
 -- As long as the Article ID was kept intact as per the previous step in this
 -- script, this should be an exact copy in J30.

 INSERT INTO `my_new_database`.my_prefix_content_frontpage
      (content_id, ordering)
 SELECT content_id, ordering
 FROM `my_old_database`.jos_content_frontpage;


 -- -----------------------------------------------------------------------------
 -- II. 10. jos_content_rating
 -- -----------------------------------------------------------------------------
 -- As long as the Article ID was kept intact as per the previous step in this
 -- script, this should be an exact copy in J30.

 INSERT INTO `my_new_database`.my_prefix_content_rating
      (content_id, rating_sum, rating_count, lastip)
 SELECT content_id, rating_sum, rating_count, lastip
 FROM `my_old_database`.jos_content_rating;


 -- -----------------------------------------------------------------------------
 -- II. 11. jos_core_log_searches -> #__core_log_searches
 -- -----------------------------------------------------------------------------

 INSERT INTO `my_new_database`.my_prefix_core_log_searches (search_term, hits)
 SELECT search_term, hits FROM `my_old_database`.jos_core_log_searches;


 -- -----------------------------------------------------------------------------
 -- II. 12. #__menu, #__menu_types
 -- -----------------------------------------------------------------------------

 -- NOTE: It is recommended you skip the menu migration and do this manually.
 --       -> Doing this manually takes me too long so I have added this section.
 --       -> Migrating menus with this script preserves SEF Joomla 1.5.x URLs.
 --       -> the Joomla Menu was the most complex section to migrate so be weary.
 --       -> Joomla 1.5 menus will be migrated as new menus in Joomla 3.0.x
 --       -> Archived Joomla 1.5 menu items will NOT be migrated.

 -- For MENUS, we're going to do the following:
 --       1) Add menu types from J15 that do not exist in J30 (menutype).
 --       2) Add menu items from J15 that do not exist in J30 (alias).
 --       3) Re-associate correct parent ids and start rebuilding paths in J30
 --       4) Complete hierarchical paths in J30
 --       5) Correct internal Joomla links to sections
 --       6) Correct internal Joomla links to categories
 --       7) Update com_user (J15) as com_users (J30)
 --       8) Update links containing view=frontpage (J15) to view=featured (J30)

 --      Affects:  #__menu_types, #__menu


 -- -----------------------------------------------------------------------------
 -- Step  1) Add menu types from J15 that do not exist in J30 (menutype).
 --       mainmenu in J15 will be imported as mainmenu2 if mainmenu exists in J30.
 --       mainmenu2 in J15 will be imported as mainmenu3 if mainmenu2 exists.

 INSERT INTO `my_new_database`.my_prefix_menu_types
         (menutype, title, description)
 SELECT
         CASE
                 WHEN a.menutype IN (
                         SELECT DISTINCT
                                 b.menutype
                         FROM
                                 `my_new_database`.my_prefix_menu_types b)
                 THEN CONCAT(a.menutype, (
                         SELECT DISTINCT
                                 (COUNT(b.menutype) + 1)
                         FROM
                                 `my_new_database`.my_prefix_menu_types b
                         WHERE
                                 b.menutype LIKE CONCAT(a.menutype, '%')
                         )
                 )
                 ELSE a.menutype
         END AS menutype,
         CASE
                 WHEN a.title IN (
                         SELECT DISTINCT
                                 b.title
                         FROM
                                 `my_new_database`.my_prefix_menu_types b)
                 THEN CONCAT(a.title, ' ', (
                         SELECT DISTINCT
                                 (COUNT(b.title) + 1)
                         FROM
                                 `my_new_database`.my_prefix_menu_types b
                         WHERE
                                 b.title LIKE CONCAT(a.title, '%')
                         )
                 )
                 ELSE a.title
         END AS title,
         a.description
 FROM
         `my_old_database`.jos_menu_types a;


 -- -----------------------------------------------------------------------------
 -- Step  2) Add menu items from J15 that do not exist in J30 (alias).
 --          Menu items for components that do not exist in J30 will become "alias".
 --          Note that "com_user" in J15 is now "com_users" in J30.
 --          All imported menus from J15 will become new separate menus in J30.
 --          Any parent_ids=0 will become 1.
 --          Archived items (published=-2) will NOT be copied across.
 --          Home flag in J15 will not be migrated. Change this using the admin panel.

 SET @new_jos_menu_lft_last=(
      SELECT MAX(lft)
      FROM `my_new_database`.my_prefix_menu);

 INSERT INTO
      `my_new_database`.my_prefix_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 `my_new_database`.my_prefix_menu_types b
      WHERE b.menutype LIKE CONCAT(a.menutype, '%')
      ),
      a.`name`,
      CASE
           WHEN a.`alias` IN (
                SELECT DISTINCT
                     b.`alias`
                FROM
                     `my_new_database`.my_prefix_menu b
           )
           THEN CONCAT(a.`alias`, (
                SELECT DISTINCT
                     CASE
                          WHEN COUNT(b.`alias`)>0 THEN a.`id`
                          ELSE ''
                     END AS myCount
                FROM
                     `my_new_database`.my_prefix_menu b
                WHERE
                     b.`alias` LIKE CONCAT(a.`alias`, '%')
                )
           )
           ELSE a.`alias`
      END AS alias,
      CONCAT(
           'Imported from J15. PreviousID=',
           a.`id`,
           '. PreviousParentID=',
           a.parent
      ) AS note,
      a.alias as path, a.link,
      CASE
           WHEN SUBSTR(
                a.link,
                LOCATE('option=', a.link)+7,
                LOCATE('&', a.link)- (LOCATE('option=', a.link)+7))
                IN (
                     SELECT DISTINCT `name`
                     FROM `my_new_database`.my_prefix_extensions
                     WHERE `type`='component'
                     UNION ALL
                     SELECT 'com_user'
                     FROM `my_new_database`.my_prefix_extensions
           )
           THEN 'component'
           WHEN a.type='url'
           THEN 'url'
           ELSE 'alias'
      END AS type,
      a.published,
      CASE
           WHEN a.parent=0 THEN 1
           ELSE null
      END AS parent_id,
      a.sublevel+1,
      IFNULL((
           SELECT MIN(extension_id)
           FROM `my_new_database`.my_prefix_extensions
           WHERE `name`=SUBSTR(
                a.link,
                LOCATE('option=', a.link)+7,
                LOCATE('&', a.link)- (LOCATE('option=', a.link)+7)
           )
      ), '0') AS component_id,
      a.browserNav,
      CASE
           WHEN a.access=0 THEN 1
           WHEN a.access=1 THEN 2
           WHEN a.access=2 THEN 7
      END AS access,
      IF(a.params='', '', CONCAT(
           '{"',
           REPLACE(REPLACE(a.params, CHAR(10), '","'), '=', '":"'), '"}'
      )),
      (@new_jos_menu_lft_last:=@new_jos_menu_lft_last+2),
      (@new_jos_menu_lft_last+1), 0 as home, '*' as language
 FROM
      `my_old_database`.jos_menu a
 WHERE
      a.published>=0;



 -- -----------------------------------------------------------------------------
 -- Step  3) Re-associate correct parent ids and start rebuilding paths in J30

 UPDATE
         `my_new_database`.my_prefix_menu child,
         `my_new_database`.my_prefix_menu parent
 SET
         child.parent_id=parent.id,
         child.path=CONCAT(parent.path,'/', child.alias)
 WHERE
         TRIM(SUBSTR(
           child.note,
           LOCATE('PreviousParentID=', child.note)+17
      ))=SUBSTR(
           parent.note,
           LOCATE('PreviousID=',parent.note)+11,
           LOCATE(
                '.',
                parent.note, (
                     LOCATE(
                          'PreviousID=',
                          parent.note
                     )+11
                )
      ) - (
           LOCATE(
                'PreviousID=',
                parent.note
           )+11
      ))
 AND
         parent.note LIKE '%PreviousParentID=%'
 AND
         child.parent_id=0;


 -- -----------------------------------------------------------------------------
 -- Step  4) Complete hierarchical paths in J30
 --         applicable for menus up to 3 levels deep
 --         Any deeper will need to be corrected using the Joomla Admin Panel

 UPDATE
         `my_new_database`.my_prefix_menu child,
         `my_new_database`.my_prefix_menu parent,
         `my_new_database`.my_prefix_menu grandparent
 SET
         child.path=(
           CASE
                 WHEN (TRIM(SUBSTR(
                grandparent.path,
                1,
                LOCATE('/', grandparent.path)-1
                ))<>'')
                 THEN CONCAT(
                TRIM(SUBSTR(
                     grandparent.path,
                     1,
                     LOCATE('/', grandparent.path)-1
                )),
                '/',
                CONCAT(TRIM(SUBSTR(
                     parent.path,
                     1,
                     LOCATE('/', parent.path)-1
                )), '/', child.path))
                 ELSE
                 (
                 CASE
                         WHEN LOCATE('/', child.path)>0
                     THEN CONCAT(CONCAT(
                          TRIM(SUBSTR(
                               parent.path,
                               1,
                               LOCATE('/', parent.path)-1
                          )), '/', child.path))
                         ELSE CONCAT(parent.path, '/', child.alias)
                 END
                 )
           END
         )
 WHERE
   TRIM(SUBSTR(child.path, 1, LOCATE('/', child.path)-1))=parent.alias
 AND
   TRIM(SUBSTR(parent.path, 1, LOCATE('/', parent.path)-1))=grandparent.alias
 AND
   child.note LIKE '%Imported from J15%'
 AND
   TRIM(SUBSTR(parent.path, 1, LOCATE('/', parent.path)-1)) <> '';


 -- -----------------------------------------------------------------------------
 -- Step  5) Correct internal Joomla links to sections (categories in J30)

 UPDATE
         `my_new_database`.my_prefix_menu a,
         `my_new_database`.my_prefix_categories b
 SET
         a.link=CONCAT(
           REPLACE(SUBSTR(
                a.link,
                1,
                LOCATE(
                     '&id=', a.link)+3),
                     '&view=section',
                     '&view=category'
                ),
           b.id
      )
 WHERE
         TRIM(SUBSTR(
           a.link,
           LOCATE('&id=', a.link)+4
      ))=TRIM(SUBSTR(b.note, LOCATE('PreviousID=', b.note)+11))
 AND
         LOCATE('?option=com_content', a.link)>0
 AND
         LOCATE('&id=', a.link)>0
 AND
         LOCATE('&view=section', a.link)>0
 AND
         a.note LIKE 'Imported from J15.%';


 -- -----------------------------------------------------------------------------
 -- Step  6) Correct internal Joomla links to categories

 UPDATE
         `my_new_database`.my_prefix_menu a,
         `my_new_database`.my_prefix_categories b
 SET
         a.link=CONCAT(TRIM(SUBSTR(a.link, 1, LOCATE('&id=', a.link)+3)), b.id)
 WHERE
         TRIM(SUBSTR(
           a.link,
           LOCATE('&id=', a.link)+4
      ))=SUBSTR(
           b.note,
           LOCATE('=catid', b.note)+6,
           LOCATE(
                '.',
                b.note,
                LOCATE('=catid', b.note)+6
           ) - (LOCATE('=catid', b.note)+6))
 AND
         LOCATE('?option=com_content', a.link)>0
 AND
         LOCATE('&id=', a.link)>0
 AND
         LOCATE('&view=category', a.link)>0
 AND
         a.note LIKE 'Imported from J15.%';


 -- -----------------------------------------------------------------------------
 -- Step  7) Update com_user (J15) as com_users (J30) (re-evaluate component_id)

 UPDATE `my_new_database`.my_prefix_menu a
 SET
      link=REPLACE(a.link, 'com_users', 'com_user'),
      component_id=(
           SELECT MIN(b.extension_id)
           FROM `my_new_database`.my_prefix_extensions b
           WHERE b.`name`='com_users' LIMIT 0,1)
 WHERE SUBSTR(
      a.link,
      LOCATE('option=', a.link)+7,
      LOCATE('&', a.link)- (LOCATE('option=', a.link)+7))='com_user';


 -- -----------------------------------------------------------------------------
 -- Step  8) Update links containing view=frontpage (J15) to view=featured (J30)

 UPDATE `my_new_database`.my_prefix_menu
 SET link=REPLACE(link, '&view=frontpage', '&view=featured')
 WHERE link LIKE '%&view=frontpage%';



 -- -----------------------------------------------------------------------------
 -- II. 13. jos_menu_types (this had to be done before the menus in II. 12.)
 -- -----------------------------------------------------------------------------


 -- -----------------------------------------------------------------------------
 -- II. 14. jos_messages
 -- -----------------------------------------------------------------------------

 INSERT INTO
         `my_new_database`.my_prefix_messages (
         message_id, user_id_from, user_id_to, folder_id, date_time, state,
         priority, `subject`, message)
 SELECT
         message_id, user_id_from, user_id_to, folder_id, date_time, state,
         priority, `subject`, message
 FROM
         `my_old_database`.jos_messages;


 -- -----------------------------------------------------------------------------
 -- II. 15. jos_messages_cfg
 -- -----------------------------------------------------------------------------

 INSERT INTO
         `my_new_database`.my_prefix_messages_cfg
      (user_id, cfg_name, cfg_value)
 SELECT
         user_id, cfg_name, cfg_value
 FROM
         `my_old_database`.jos_messages_cfg;


 -- -----------------------------------------------------------------------------
 -- II. 16. jos_modules -- not migrated
 -- -----------------------------------------------------------------------------
 --
 -- -----------------------------------------------------------------------------
 -- II. 17. jos_modules_menu -- not migrated
 -- -----------------------------------------------------------------------------
 --
 -- -----------------------------------------------------------------------------
 -- II. 18. jos_newsfeeds
 -- -----------------------------------------------------------------------------

 -- For NEWSFEEDS, we're going to do the following:
 --       1) Import newsfeed categories from J15 and insert as assets in J30
 --       2) Import newsfeed categories from J15 and insert as categories in J30
 --       3) Update foreign keys between categories and assets
 --       4) Insert newsfeeds from J15 as newsfeeds in J30
 --       5) Associate J15 categories and newsfeeds

 --      Affects:  #__newsfeeds


 -- -----------------------------------------------------------------------------
 -- Step  1) Import newsfeed categories 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 '%.category.%'
         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);
 SET @new_jos_assets_parent_id=(
         SELECT MIN(id)
      FROM `my_new_database`.my_prefix_assets
      WHERE title='com_newsfeeds');

 INSERT INTO
         `my_new_database`.my_prefix_assets (
         parent_id, lft, rgt, `level`, `name`, title, rules)
 SELECT
         @new_jos_assets_parent_id,
      @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2,
         @new_jos_assets_id_last_lft+1, 1,
         CONCAT(
           'com_newsfeeds.category.',
           @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1),
         CONCAT(title, ' :|joes|', id, '|:'),
      '{"core.create":[],"core.delete":[],"core.edit":[],"core.edit.state":[]}'
 FROM
         `my_old_database`.jos_categories
 WHERE
         section='com_newsfeeds';


 -- -----------------------------------------------------------------------------
 -- Step  2) Import newsfeed categories from J15 and insert as categories in J30

 SET @new_jos_categories_extension_alias=(
         SELECT alias FROM `my_new_database`.my_prefix_categories
         WHERE extension='com_newsfeeds'
      ORDER BY id ASC LIMIT 0,1);
 SET @new_jos_categories_id_last_lft=(
         SELECT MAX(lft)
      FROM `my_new_database`.my_prefix_categories);
 SET @new_jos_categories_parent_id=(
         SELECT MIN(id)
      FROM `my_new_database`.my_prefix_categories
      WHERE title='com_newsfeeds');

 INSERT INTO
         `my_new_database`.my_prefix_categories (
         parent_id, lft, rgt, `level`, path, extension, title, alias, note,
         description, published, access, params, metadata, created_user_id,
         created_time, `language`)
 SELECT
         1, @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2,
         @new_jos_categories_id_last_lft+1, 1, alias,
         'com_newsfeeds', title, alias,
      CONCAT('Import from J15. PreviousID=catid', id),
         description, published,
         CASE
           WHEN access=0 THEN 1
           WHEN access=1 THEN 2
           WHEN access=2 THEN 7
      END AS access,
         '{"target":"","image":""}', '{"page_title":"","author":"","robots":""}',
         42, NOW(), '*'
 FROM
         `my_old_database`.jos_categories
 WHERE
         `my_old_database`.jos_categories.section='com_newsfeeds';


 -- -----------------------------------------------------------------------------
 -- Step  3) Update foreign keys between categories and assets

 UPDATE
      `my_new_database`.my_prefix_categories a,
      `my_new_database`.my_prefix_assets e
 SET     e.title=a.title, a.asset_id=e.id
 WHERE TRIM(CONCAT(a.title, ' :|joes|', SUBSTR(
      a.note,
      LOCATE('. PreviousID=catid', a.note)+18
         ), '|:'))=TRIM(e.title);


 -- -----------------------------------------------------------------------------
 -- Step  4) Insert newsfeeds from J15 as newsfeeds in J30

 SET @new_jos_admin_id=(
         SELECT `created_user_id`
      FROM `my_new_database`.my_prefix_categories
      WHERE `id`='1');


 INSERT INTO
         `my_new_database`.my_prefix_newsfeeds (
         catid, `name`, alias, link, published, numarticles,
         cache_time, ordering, rtl,
         access, `language`,
         params,
         created, created_by, metadata )
 SELECT
         catid, `name`, alias, link, published, numarticles,
         cache_time, ordering, rtl, 1, '*',
         CONCAT(
           '{"show_feed_image":"",',
           '"show_feed_description":"",',
           '"show_item_description":"",',
           '"feed_character_count":"0"}'
      ),
         NOW(), @new_jos_admin_id, '{"robots":"","rights":""}'
 FROM
         `my_old_database`.jos_newsfeeds;


 -- -----------------------------------------------------------------------------
 -- Step  5) Associate J15 categories and newsfeeds

 UPDATE
      `my_new_database`.my_prefix_newsfeeds a,
      `my_new_database`.my_prefix_categories b
 SET a.catid=b.id
 WHERE b.note=CONCAT('Import from J15. PreviousID=catid', a.catid)
 AND b.extension='com_newsfeeds';


 -- -----------------------------------------------------------------------------
 -- II. 19. jos_plugins -> #__extensions -- not migrated
 -- -----------------------------------------------------------------------------
 --
 -- -----------------------------------------------------------------------------
 -- II. 20. jos_users -> #__users
 -- -----------------------------------------------------------------------------

 -- My favorite part of this script, migrate your users, passwords and all.

 -- For USERS, we're going to do the following:
 --       1) Migrate all users except any matching the new admin in J30
 --       2) Migrate user who's ID in J15 matches the new admin ID in J30
 --       3) Recover odduser's new ID in J30 and re-associate to content
 --       4) Recover groups used in J15 and apply correct groups to users in J30

 --      Affects:  #__users, #__user_usergroup_map


 -- -----------------------------------------------------------------------------
 -- Step  1) Migrate all users except any matching the new admin in J30

 SET @new_jos_admin_id=(
         SELECT `id`
      FROM `my_new_database`.my_prefix_users
      ORDER BY registerDate ASC
         LIMIT 0,1);
 SET @new_jos_admin_email=(
         SELECT `email`
      FROM `my_new_database`.my_prefix_users
      ORDER BY registerDate ASC
         LIMIT 0,1);

 INSERT INTO
         `my_new_database`.my_prefix_users (
         id, `name`, username, email, `password`,
         block, sendEmail, registerDate, lastvisitDate, activation,
         params )
 SELECT
         id, `name`, username, email, `password`,
         block, sendEmail, registerDate, lastvisitDate, activation,
         IF(
           params='',
           '',
           CONCAT(
                '{"',
                REPLACE(REPLACE(params, CHAR(10), '","'), '=', '":"'),
                '"}'
           )
      )
 FROM
         `my_old_database`.jos_users
 WHERE
         `id` <> @new_jos_admin_id
 AND
         `email` <> @new_jos_admin_email;


 -- -----------------------------------------------------------------------------
 -- Step  2) Migrate user who's ID in J15 matches the new admin ID in J30
 --      - This user will take next available ID in the new database (J30).

 SET @new_jos_admin_id=(
         SELECT `created_user_id`
      FROM `my_new_database`.my_prefix_categories
      WHERE `id`='1');
 SET @J15_odduser_username=(
         SELECT `username`
      FROM `my_old_database`.jos_users
      WHERE `id` = @new_jos_admin_id);

 INSERT INTO
         `my_new_database`.my_prefix_users (
         `name`, username, email, `password`,
         block, sendEmail, registerDate, lastvisitDate, activation,
         params )
 SELECT
         `name`, username, email, `password`,
         block, sendEmail, registerDate, lastvisitDate, activation,
         IF(
           params='',
           '',
           CONCAT(
                '{"',
                REPLACE(REPLACE(params, CHAR(10), '","'), '=', '":"'),
                '"}'
           )
      )
 FROM
         `my_old_database`.jos_users
 WHERE
         id=@new_jos_admin_id;


 -- -----------------------------------------------------------------------------
 -- Step  3) Recover odduser's new ID in J30 and re-associate to content

 SET @new_jos_admin_id=(
         SELECT `created_user_id`
      FROM `my_new_database`.my_prefix_categories
      WHERE `id`='1');
 SET @J15_odduser_id=(
         SELECT `id`
      FROM `my_new_database`.my_prefix_users
      ORDER BY `id` DESC LIMIT 0,1);
 SET @J15_odduser_alias=(
         SELECT `name`
      FROM `my_new_database`.my_prefix_users
      ORDER BY `id` DESC LIMIT 0,1);

 UPDATE `my_new_database`.my_prefix_content
 SET created_by = @J15_odduser_id, created_by_alias = @J15_odduser_alias
 WHERE created_by = @new_jos_admin_id AND created_by_alias = @J15_odduser_alias;


 -- -----------------------------------------------------------------------------
 -- Step  4) Recover groups used in J15 and apply correct groups to users in J30

 INSERT INTO
         `my_new_database`.my_prefix_user_usergroup_map (
                 user_id,
                 group_id )
 SELECT
         a.aro_id AS user_id,
         CASE
         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)
         WHEN a.group_id=(
           SELECT id
           FROM `my_old_database`.jos_core_acl_aro_groups
           WHERE `name`='Administrator'
           LIMIT 0,1)
           THEN (
                SELECT id
                FROM `my_new_database`.my_prefix_usergroups
                WHERE title='Administrator'
                LIMIT 0,1)
         WHEN a.group_id=(
           SELECT id
           FROM `my_old_database`.jos_core_acl_aro_groups
           WHERE `name`='Manager'
           LIMIT 0,1)
           THEN (
                SELECT id
                FROM `my_new_database`.my_prefix_usergroups
                WHERE title='Manager'
                LIMIT 0,1)
         WHEN a.group_id=(
           SELECT id
           FROM `my_old_database`.jos_core_acl_aro_groups
           WHERE `name`='Public Backend'
           LIMIT 0,1)
           THEN (
                SELECT id
                FROM `my_new_database`.my_prefix_usergroups
                WHERE title='Public'
                LIMIT 0,1)
         WHEN a.group_id=(
           SELECT id
           FROM `my_old_database`.jos_core_acl_aro_groups
           WHERE `name`='Publisher'
           LIMIT 0,1)
           THEN (
                SELECT id
                FROM `my_new_database`.my_prefix_usergroups
                WHERE title='Publisher'
                LIMIT 0,1)
         WHEN a.group_id=(
           SELECT id
           FROM `my_old_database`.jos_core_acl_aro_groups
           WHERE `name`='Editor'
           LIMIT 0,1)
           THEN (
                SELECT id
                FROM `my_new_database`.my_prefix_usergroups
                WHERE title='Editor'
                LIMIT 0,1)
         WHEN a.group_id=(
           SELECT id
           FROM `my_old_database`.jos_core_acl_aro_groups
           WHERE `name`='Author'
           LIMIT 0,1)
           THEN (
                SELECT id
                FROM `my_new_database`.my_prefix_usergroups
                WHERE title='Author'
                LIMIT 0,1)
         WHEN a.group_id=(
           SELECT id
           FROM `my_old_database`.jos_core_acl_aro_groups
           WHERE `name`='Registered'
           LIMIT 0,1)
           THEN (
                SELECT id
                FROM `my_new_database`.my_prefix_usergroups
                WHERE title='Registered'
                LIMIT 0,1)
         WHEN a.group_id=(
           SELECT id
           FROM `my_old_database`.jos_core_acl_aro_groups
           WHERE `name`='Public Frontend'
           LIMIT 0,1)
           THEN (
                SELECT id
                FROM `my_new_database`.my_prefix_usergroups
                WHERE title='Public'
                LIMIT 0,1)
         END AS group_id
 FROM
         `my_old_database`.jos_core_acl_groups_aro_map a
 ORDER BY
         a.aro_id;

 -- -----------------------------------------------------------------------------
 -- Set system admins in J30 based on system admins in J15

 INSERT INTO
         `my_new_database`.my_prefix_user_usergroup_map (
                 user_id,
                 group_id )
 SELECT     a.id, (
      SELECT id
      FROM `my_new_database`.my_prefix_usergroups
      WHERE title='Super Users'
      LIMIT 0,1)
 FROM     `my_old_database`.jos_users a
 WHERE     a.gid = (
      SELECT id
      FROM `my_old_database`.jos_core_acl_aro_groups
      WHERE `name`='Super Administrator'
      LIMIT 0,1)
 AND     a.block=0;


 -- -----------------------------------------------------------------------------
 -- Set admins in J30 based on admins in J15

 INSERT INTO
         `my_new_database`.my_prefix_user_usergroup_map (
                 user_id,
                 group_id )
 SELECT     a.id, (
      SELECT id
      FROM `my_new_database`.my_prefix_usergroups
      WHERE title='Administrator'
      LIMIT 0,1)
 FROM     `my_old_database`.jos_users a
 WHERE     a.gid = (
      SELECT id
      FROM `my_old_database`.jos_core_acl_aro_groups
      WHERE `name`='Administrator'
      LIMIT 0,1)
 AND     a.block=0;



 -- -----------------------------------------------------------------------------
 -- II. 21. jos_weblinks
 -- -----------------------------------------------------------------------------

 -- For WEBLINKS, we're going to do the following:
 --       1) Migrate all users except any matching the new admin in J30
 --       2) Migrate previous J15 admin to new J30 system
 --       3) Migrate user who's ID in J15 matches the new admin ID in J30
 --       4) Recover odduser's new ID in J30 and re-associate to content
 --       5) Recover groups used in J15 and apply correct groups to users in J30

 --      Affects:  #__users, #__user_usergroup_map


 -- -----------------------------------------------------------------------------
 -- Step  1) Import weblinks categories 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 '%.category.%'
         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);
 SET @new_jos_assets_parent_id=(
         SELECT MIN(id)
      FROM `my_new_database`.my_prefix_assets
      WHERE title='com_weblinks');

 INSERT INTO
         `my_new_database`.my_prefix_assets (
         parent_id, lft, rgt, `level`, `name`, title, rules)
 SELECT
         @new_jos_assets_parent_id,
      @new_jos_assets_id_last_lft:=@new_jos_assets_id_last_lft+2,
         @new_jos_assets_id_last_lft+1, 1,
         CONCAT(
           'com_weblinks.category.',
           @new_jos_assets_last_inc:=@new_jos_assets_last_inc+1
      ),
         CONCAT(title, ' :|joes|', id, '|:'),
      '{"core.create":[],"core.delete":[],"core.edit":[],"core.edit.state":[]}'
 FROM
         `my_old_database`.jos_categories
 WHERE
         section='com_weblinks';


 -- -----------------------------------------------------------------------------
 -- Step  2) Import weblinks categories from J15 and insert as categories in J30

 SET @new_jos_categories_extension_alias=(
         SELECT alias
      FROM `my_new_database`.my_prefix_categories
         WHERE extension='com_weblinks'
      ORDER BY id ASC LIMIT 0,1);
 SET @new_jos_categories_id_last_lft=(
         SELECT MAX(lft)
      FROM `my_new_database`.my_prefix_categories);
 SET @new_jos_categories_parent_id=(
         SELECT MIN(id)
      FROM `my_new_database`.my_prefix_categories
      WHERE title='com_newsfeeds');

 INSERT INTO
         `my_new_database`.my_prefix_categories (
         parent_id, lft, rgt, `level`, path, extension, title, alias, note,
         description, published, access, params, metadata, created_user_id,
         created_time, `language`)
 SELECT
         1, @new_jos_categories_id_last_lft:=@new_jos_categories_id_last_lft+2,
         @new_jos_categories_id_last_lft+1, 1, alias,
         'com_weblinks', title, alias,
      CONCAT('Import from J15. PreviousID=catid', id),
         description, published,
         CASE
           WHEN access=0 THEN 1
           WHEN access=1 THEN 2
           WHEN access=2 THEN 7
      END AS access,
         '{"target":"","image":""}', '{"page_title":"","author":"","robots":""}',
         42, NOW(), '*'
 FROM
         `my_old_database`.jos_categories
 WHERE
         `my_old_database`.jos_categories.section='com_weblinks';


 -- -----------------------------------------------------------------------------
 -- Step  3) Associate categories and assets for weblinks

 UPDATE
      `my_new_database`.my_prefix_categories a,
      `my_new_database`.my_prefix_assets e
 SET e.title=a.title, a.asset_id=e.id
 WHERE TRIM(CONCAT(
      a.title,
      ' :|joes|',
      SUBSTR( a.note, LOCATE('. PreviousID=catid', a.note)+18), '|:'
      ))=TRIM(e.title);


 -- -----------------------------------------------------------------------------
 -- Step  4) Insert weblinks from J15 as weblinks in J30

 SET @new_jos_admin_id=(
         SELECT `created_user_id`
      FROM `my_new_database`.my_prefix_categories
      WHERE `id`='1');

 INSERT INTO
         `my_new_database`.my_prefix_weblinks (
         catid, title, alias, url, description,
         hits, state, ordering, access,
         params,
         `language`, created, created_by )
 SELECT
         catid, title, alias, url, description,
         hits, published, ordering, 1,
         IF(
           params='',
           '',
           CONCAT(
                '{"',
                REPLACE(REPLACE(params, CHAR(10), '","'), '=', '":"'),
                '"}'
           )
      ),
         '*', `date`, @new_jos_admin_id
 FROM
         `my_old_database`.jos_weblinks;


 -- -----------------------------------------------------------------------------
 -- Step  5) Associate J30 categories and weblinks

 UPDATE
      `my_new_database`.my_prefix_weblinks a,
      `my_new_database`.my_prefix_categories b
 SET a.catid=b.id
 WHERE b.note=CONCAT('Import from J15. PreviousID=catid', a.catid)
 AND b.extension='com_weblinks';


 -- -----------------------------------------------------------------------------
 -- III. Third-Party Extensions
 -- -----------------------------------------------------------------------------

 -- JCOMMENTS FOR J1.6+
 -- Search my website for "Migrate" for the final script to migrate JComments

 -- VIRTUEMART? (what a nightmare, major upgrade, 30FTE)
 -- ROKBOX TO YOOTHEME?
 -- JEVENTS?

 -- -----------------------------------------------------------------------------
 -- IV. Post-Installation
 -- -----------------------------------------------------------------------------

 -- For POST-INSTALL, after running this script:
 --       1) Remove migration notes
 --       2) Login to Joomla Admin Panel > Categories and click on "Rebuild".
 --       3) Login to Joomla Admin Panel > Menus and click on "Rebuild".


 -- -----------------------------------------------------------------------------
 -- Remove Migration Notes from Categories

 UPDATE     `my_new_database`.my_prefix_categories a
 SET     a.`note`=SUBSTR(a.note, 1, LOCATE('Import from J15', a.note)-1)
 WHERE     a.`note` LIKE 'Import from J15%';


 -- -----------------------------------------------------------------------------
 -- Remove Migration Notes from Menus

 UPDATE     `my_new_database`.my_prefix_menu a
 SET     a.`note`=SUBSTR(a.note, 1, LOCATE('Imported from J15', a.note)-1)
 WHERE     a.`note` LIKE 'Imported from J15%';


 -- -----------------------------------------------------------------------------
 -- Don't forget to go CATEGORY MANAGER and click on the REBUILD icon
 -- Also double-check J1.5 administrators are correct in J2.5.

 -- The installer for Joomla 3.0 is a new system administrator.

 --       1) Login to Joomla Admin Panel > Categories and click on "Rebuild".
 --       2) Login to Joomla Admin Panel > Menus and click on "Rebuild".
 --       3) Move menu item "Home" (or your default Home) to "Main Menu 2".
 -- -----------------------------------------------------------------------------

« 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

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 »


Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.