SQL migration Joomla 3.0

gistfile1.sql

# Drop deprecated columns

ALTER TABLE `#__content` DROP `title_alias`;
ALTER TABLE `#__content` DROP `sectionid`;
ALTER TABLE `#__users` DROP `usertype`;
ALTER TABLE `#__session` DROP `usertype`;
ALTER TABLE `#__users` DROP KEY `whosonline`;

# Change tables for which we want to support foreign keys or transactions to InnoDB

ALTER TABLE `#__assets` ENGINE=InnoDB;
ALTER TABLE `#__banner_clients` ENGINE=InnoDB;
ALTER TABLE `#__banners` ENGINE=InnoDB;
ALTER TABLE `#__categories` ENGINE=InnoDB;
ALTER TABLE `#__contact_details` ENGINE=InnoDB;
ALTER TABLE `#__content` ENGINE=InnoDB;
ALTER TABLE `#__content_frontpage` ENGINE=InnoDB;
ALTER TABLE `#__content_rating` ENGINE=InnoDB;
ALTER TABLE `#__extensions` ENGINE=InnoDB;
ALTER TABLE `#__finder_filters` ENGINE=InnoDB;
ALTER TABLE `#__menu` ENGINE=InnoDB;
ALTER TABLE `#__messages` ENGINE=InnoDB;
ALTER TABLE `#__messages_cfg` ENGINE=InnoDB;
ALTER TABLE `#__modules` ENGINE=InnoDB;
ALTER TABLE `#__newsfeeds` ENGINE=InnoDB;
ALTER TABLE `#__user_notes` ENGINE=InnoDB;
ALTER TABLE `#__user_profiles` ENGINE=InnoDB;
ALTER TABLE `#__users` ENGINE=InnoDB;
ALTER TABLE `#__weblinks` ENGINE=InnoDB;

# These columns will never be negative
ALTER TABLE `#__assets` MODIFY `parent_id` INT unsigned NOT NULL;
ALTER TABLE `#__assets` MODIFY `lft` INT unsigned NOT NULL;
ALTER TABLE `#__assets` MODIFY `rgt` INT unsigned NOT NULL;

# Columns with boolean values should be TINYINT 
ALTER TABLE `#__languages` MODIFY `published` TINYINT unsigned NOT NULL;
ALTER TABLE `#__update_sites` MODIFY `enabled` TINYINT unsigned NOT NULL;

# Change the user ID to unsigned MEDIUMINT

## Deal with all the checked_out columns
ALTER TABLE `#__banner_clients` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__banners` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__categories` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__contact_details` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__content` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__extensions` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__finder_filters` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__menu` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__modules` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__newsfeeds` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__user_notes` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;
ALTER TABLE `#__weblinks` MODIFY `checked_out` MEDIUMINT unsigned DEFAULT NULL;

# Replace the current data to fit the schema
UPDATE #__banner_clients SET `checked_out` = NULL;
UPDATE #__banners SET `checked_out` = NULL;
UPDATE #__categories SET `checked_out` = NULL;
UPDATE #__contact_details SET `checked_out` = NULL;
UPDATE #__content SET `checked_out` = NULL;
UPDATE #__extensions SET `checked_out` = NULL;
UPDATE #__finder_filters SET `checked_out` = NULL;
UPDATE #__menu SET `checked_out` = NULL;
UPDATE #__modules SET `checked_out` = NULL;
UPDATE #__newsfeeds SET `checked_out` = NULL;
UPDATE #__user_notes SET `checked_out` = NULL;
UPDATE #__weblinks SET `checked_out` = NULL;

# Modify the created_by, modifief_by columns
ALTER TABLE `#__categories` MODIFY `created_user_id` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__categories` MODIFY `modified_user_id` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__contact_details` MODIFY `created_by` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__contact_details` MODIFY `modified_by` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__content` MODIFY `created_by` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__content` MODIFY `modified_by` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__finder_filters` MODIFY `created_by` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__finder_filters` MODIFY `modified_by` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__newsfeeds` MODIFY `created_by` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__newsfeeds` MODIFY `modified_by` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__user_notes` MODIFY `created_user_id` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__user_notes` MODIFY `modified_user_id` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__weblinks` MODIFY `created_by` MEDIUMINT unsigned NOT NULL;
ALTER TABLE `#__weblinks` MODIFY `modified_by` MEDIUMINT unsigned NOT NULL;

# Modify all other columns that are FK to the user id
ALTER TABLE `#__contact_details` MODIFY `user_id` MEDIUMINT unsigned;
ALTER TABLE `#__messages` MODIFY `user_id_from` MEDIUMINT unsigned;
ALTER TABLE `#__messages` MODIFY `user_id_to` MEDIUMINT unsigned;
ALTER TABLE `#__messages_cfg` MODIFY `user_id` MEDIUMINT unsigned;
ALTER TABLE `#__session` MODIFY `userid` MEDIUMINT unsigned;
ALTER TABLE `#__users` MODIFY `id` MEDIUMINT unsigned;
ALTER TABLE `#__user_profiles` MODIFY `user_id` MEDIUMINT unsigned;
ALTER TABLE `#__user_usergroup_map` MODIFY `user_id` MEDIUMINT unsigned;

# Modify the content ID to be an unsigned MEDIUMINT
ALTER TABLE `#__content` MODIFY `id` MEDIUMINT unsigned;
ALTER TABLE `#__content_frontpage` MODIFY `content_id` MEDIUMINT unsigned;
ALTER TABLE `#__content_rating` MODIFY `content_id` MEDIUMINT unsigned;

# Modify the extension ID to be an unsigned MEDIUMINT
ALTER TABLE `#__schemas` MODIFY `extension_id` MEDIUMINT unsigned;