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 __construct()
{
parent::__construct();
parent::checkForTables('users');
parent::checkForColumns('user_token', 'users');
}
public function down()
{
parent::checkForColumns('active', 'users_to_marks');
$this->db->query("ALTER TABLE `users_to_marks` DROP COLUMN `active`");
}
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 __construct()
{
parent::__construct();
}
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`");
}
