public function down() { parent::checkForColumns(array('user_id', 'email', 'password', 'date_joined', 'status', 'last_updated'), 'users'); $this->db->query('ALTER TABLE `users` CHANGE COLUMN `user_id` `id` bigint(11) NOT NULL AUTO_INCREMENT'); $this->db->query('ALTER TABLE `users` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`)'); $this->db->query('ALTER TABLE `users` DROP COLUMN `last_updated`'); $this->db->query('ALTER TABLE `users` CHANGE COLUMN `email` `emailaddress` varchar(255) NOT NULL'); $this->db->query('ALTER TABLE `users` CHANGE COLUMN `password` `password` varchar(255) NOT NULL'); $this->db->query('ALTER TABLE `users` CHANGE COLUMN `date_joined` `datejoined` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP'); $this->db->query('ALTER TABLE `users` CHANGE COLUMN `status` `status` varchar(255) NOT NULL'); $this->db->query('ALTER TABLE `users` DROP INDEX `email`'); $this->db->query('ALTER TABLE `users` ADD UNIQUE `emailaddress`(emailaddress)'); }
public function down() { parent::checkForColumns('active', 'users_to_marks'); $this->db->query("ALTER TABLE `users_to_marks` DROP COLUMN `active`"); }
public function __construct() { parent::__construct(); parent::checkForTables('users'); parent::checkForColumns('user_token', 'users'); }
public function down() { parent::checkForColumns('admin', 'users'); $this->db->query("ALTER TABLE `users` DROP COLUMN `admin`"); }
public function down() { set_time_limit(0); // Check for all tables required for this migration parent::checkForTables(array('labels', 'marks', 'migrations', 'tags', 'users', 'user_marks_to_tags', 'users_to_marks')); // Check all columns need per table are found // marks, users and users_marks parent::checkForColumns(array('last_updated', 'url_key', 'mark_id', 'title', 'url', 'embed', 'created_on', 'embed_processed'), 'marks'); parent::checkForColumns(array('active', 'created_on'), 'users'); parent::checkForColumns(array('last_updated', 'users_to_mark_id', 'mark_id', 'user_id', 'label_id', 'notes', 'created_on', 'archived_on'), 'users_to_marks'); // Set default label/tags $default_labels = array('unlabeled', 'read', 'watch', 'listen', 'buy', 'eatdrink', 'do'); // Drop user_marks_to_tags $this->db->query("DROP TABLE IF EXISTS `user_marks_to_tags`"); // Drop tags $this->db->query("DROP TABLE IF EXISTS `tags`"); /* - Start users_marks transistion */ // Find any archived marks and save id for later use $archived = array(); $marks = $this->db->query("SELECT users_to_mark_id FROM `users_to_marks` WHERE archived_on IS NOT NULL"); if ($marks->num_rows() >= 1) { // Loop thru results foreach ($marks->result() as $mark) { array_push($archived, $mark->users_to_mark_id); } } // Change all date_archived back to 0000-00-00 00:00:00 like they were originally $res = $this->db->query("UPDATE `users_to_marks` SET archived_on = '0000-00-00 00:00:00' WHERE archived_on IS NULL"); // Revert users_marks table $this->db->query("RENAME TABLE `users_to_marks` TO `users_marks`"); $this->db->query("ALTER TABLE `users_marks` DROP FOREIGN KEY `FK_utm_mark_id`"); $this->db->query("ALTER TABLE `users_marks` DROP FOREIGN KEY `FK_utm_user_id`"); $this->db->query("ALTER TABLE `users_marks` DROP FOREIGN KEY `FK_utm_label_id`"); $this->db->query("ALTER TABLE `users_marks` DROP INDEX `mark_id`"); $this->db->query("ALTER TABLE `users_marks` DROP INDEX `label_id`"); $this->db->query("ALTER TABLE `users_marks` DROP INDEX `user_id`"); $this->db->query("ALTER TABLE `users_marks` DROP COLUMN `last_updated`"); $this->db->query("ALTER TABLE `users_marks` CHANGE COLUMN `users_to_mark_id` `id` int(11) NOT NULL AUTO_INCREMENT"); $this->db->query("ALTER TABLE `users_marks` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`)"); $this->db->query("ALTER TABLE `users_marks` CHANGE COLUMN `mark_id` `urlid` int(11) NOT NULL"); $this->db->query("ALTER TABLE `users_marks` CHANGE COLUMN `user_id` `userid` int(11) NOT NULL AFTER `id`"); $this->db->query("ALTER TABLE `users_marks` CHANGE COLUMN `label_id` `tags` text DEFAULT NULL"); $this->db->query("ALTER TABLE `users_marks` CHANGE COLUMN `notes` `note` text DEFAULT NULL"); $this->db->query("ALTER TABLE `users_marks` CHANGE COLUMN `created_on` `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP"); $this->db->query("ALTER TABLE `users_marks` CHANGE COLUMN `archived_on` `datearchived` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'"); $this->db->query("ALTER TABLE `users_marks` ADD COLUMN `status` varchar(255) DEFAULT NULL AFTER `dateadded`"); $this->db->query("ALTER TABLE `users_marks` ADD COLUMN `addedby` int(11) NOT NULL AFTER `tags`"); $this->db->query("ALTER TABLE `users_marks` ADD COLUMN `groups` int(11) NOT NULL AFTER `addedby`"); // Revert archived marks $res = $this->db->query("UPDATE `users_marks` SET status = NULL WHERE status IS NOT NULL"); if (!empty($archived)) { foreach ($archived as $id) { $res = $this->db->query("UPDATE `users_marks` SET status = 'archive' WHERE id = '" . $id . "'"); } } // Add tags back $marks = $this->db->query("SELECT id, tags FROM `users_marks`"); if ($marks->num_rows() >= 1) { // Loop thru results and update foreach ($marks->result() as $mark) { $label_id = $mark->tags - 1; $label = array_key_exists($label_id, $default_labels) ? $default_labels[$label_id] : 'unlabeled'; $res = $this->db->query("UPDATE `users_marks` SET tags = '" . $label . "' WHERE id = '" . $mark->id . "'"); } } /* - End users_marks transistion */ // Add groups_invites table back $this->db->query("\n CREATE TABLE IF NOT EXISTS `groups_invites` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `groupid` int(11) NOT NULL,\n `emailaddress` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,\n `invitedby` int(11) NOT NULL,\n `dateinvited` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=`InnoDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;\n "); // Add groups table back $this->db->query("\n CREATE TABLE IF NOT EXISTS `groups` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `createdby` int(11) NOT NULL,\n `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,\n `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `urlname` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `uid` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,\n `datecreated` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n PRIMARY KEY (`id`)\n ) ENGINE=`InnoDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;\n "); // Add users_groups table back $this->db->query("\n CREATE TABLE `users_groups` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `groupid` int(11) NOT NULL,\n `userid` int(11) NOT NULL,\n `datejoined` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=`InnoDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;\n "); // Revert marks table $this->db->query("ALTER TABLE `marks` DROP INDEX `url_key`"); $this->db->query("ALTER TABLE `marks` DROP COLUMN `last_updated`"); $this->db->query("ALTER TABLE `marks` DROP COLUMN `url_key`"); $this->db->query("ALTER TABLE `marks` CHANGE COLUMN `mark_id` `id` int(11) NOT NULL AUTO_INCREMENT"); $this->db->query("ALTER TABLE `marks` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`)"); $this->db->query("ALTER TABLE `marks` CHANGE COLUMN `title` `title` text NOT NULL"); $this->db->query("ALTER TABLE `marks` CHANGE COLUMN `url` `url` text NOT NULL"); $this->db->query("ALTER TABLE `marks` CHANGE COLUMN `embed` `oembed` text DEFAULT NULL"); $this->db->query("ALTER TABLE `marks` CHANGE COLUMN `created_on` `dateadded` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP"); $this->db->query("ALTER TABLE `marks` ADD COLUMN `recipe` text DEFAULT NULL AFTER `oembed`"); $this->db->query("ALTER TABLE `marks` DROP COLUMN `embed_processed`"); $res = $this->db->query("UPDATE `marks` SET oembed = 'None' WHERE oembed IS NULL"); // Move recipes back to the recipe column $marks = $this->db->query("SELECT id, oembed FROM `marks` WHERE oembed LIKE '%hrecipe%'"); if ($marks->num_rows() >= 1) { foreach ($marks->result() as $mark) { $res = $this->db->query("UPDATE `marks` SET recipe = '" . addslashes($mark->oembed) . "', oembed = NULL WHERE `id` = '" . $mark->id . "'"); } } // Revert user smart labels $this->db->query("\n CREATE TABLE IF NOT EXISTS `users_smartlabels` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `userid` int(11) NOT NULL,\n `domain` varchar(255) NOT NULL,\n `path` varchar(255) NOT NULL,\n `label` varchar(255) NOT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=`InnoDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;\n "); // Get all user smart labels from labels table and add back to users_smartlabels $labels = $this->db->query("SELECT smart_label_id, user_id, domain, path FROM `labels` WHERE smart_label_id IS NOT NULL AND user_id IS NOT NULL"); if ($labels->num_rows() >= 1) { foreach ($labels->result() as $label) { // Proceed only if domain is not empty $label_id = $label->smart_label_id - 1; $current_label = isset($default_labels[$label_id]) ? $default_labels[$label_id] : null; if (!empty($current_label)) { $res = $this->db->query("\n INSERT INTO `users_smartlabels`\n (`userid`, `domain`, `path`, `label`)\n VALUES ('" . $label->user_id . "', '" . $label->domain . "', '" . $label->path . "', '" . $current_label . "')\n "); } } } // Drop labels table $this->db->query("DROP TABLE IF EXISTS `labels`"); // Revert users table // Revert active to status, 0 = inactive, 1 = active $this->db->query("ALTER TABLE `users` CHANGE COLUMN `active` `status` varchar(25) NOT NULL DEFAULT 'inactive'"); $res = $this->db->query("UPDATE `users` SET status = 'inactive' WHERE status <> '1'"); $res = $this->db->query("UPDATE `users` SET status = 'active' WHERE status = '1'"); $this->db->query("ALTER TABLE `users` ADD COLUMN `salt` varchar(50) DEFAULT NULL COMMENT 'The salt used to generate password.' AFTER `password`"); $this->db->query("ALTER TABLE `users` CHANGE COLUMN `created_on` `date_joined` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'"); $this->db->query("ALTER TABLE `users` DROP INDEX `user_token`"); $this->db->query("ALTER TABLE `users` DROP COLUMN `user_token`"); }
public function down() { parent::checkForColumns('datearchived', 'users_marks'); $this->dbforge->drop_column('users_marks', 'datearchived'); }
public function down() { parent::checkForColumns('order', 'labels'); $this->db->query("ALTER TABLE `labels` DROP COLUMN `order`"); }