diff options
Diffstat (limited to 'application/migrations')
21 files changed, 1015 insertions, 0 deletions
diff --git a/application/migrations/001_add_files.php b/application/migrations/001_add_files.php new file mode 100644 index 000000000..7b1398e1c --- /dev/null +++ b/application/migrations/001_add_files.php @@ -0,0 +1,45 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_Add_files extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + CREATE TABLE IF NOT EXISTS "'.$prefix.'files" ( + "hash" varchar(32) NOT NULL, + "id" varchar(6) NOT NULL, + "filename" varchar(256) NOT NULL, + "password" varchar(40) DEFAULT NULL, + "date" integer NOT NULL, + "mimetype" varchar(255) NOT NULL, + PRIMARY KEY ("id") + ); + CREATE INDEX "'.$prefix.'files_date_idx" ON '.$prefix.'files ("date"); + CREATE INDEX "'.$prefix.'files_hash_id_idx" ON '.$prefix.'files ("hash", "id"); + '); + } else { + $this->db->query(' + CREATE TABLE IF NOT EXISTS `'.$prefix.'files` ( + `hash` varchar(32) CHARACTER SET ascii NOT NULL, + `id` varchar(6) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `filename` varchar(256) COLLATE utf8_bin NOT NULL, + `password` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, + `date` int(11) unsigned NOT NULL, + `mimetype` varchar(255) CHARACTER SET ascii NOT NULL, + PRIMARY KEY (`id`), + KEY `date` (`date`), + KEY `hash` (`hash`,`id`) + ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; + '); + } + } + + public function down() + { + $this->dbforge->drop_table('files'); + } +} diff --git a/application/migrations/002_add_users.php b/application/migrations/002_add_users.php new file mode 100644 index 000000000..454618e48 --- /dev/null +++ b/application/migrations/002_add_users.php @@ -0,0 +1,79 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_Add_users extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + CREATE TABLE IF NOT EXISTS "'.$prefix.'users" ( + "id" serial PRIMARY KEY, + "username" character varying(32) NOT NULL, + "password" character varying(60) NOT NULL, + "email" character varying(255) NOT NULL + ) + '); + + $this->db->query(' + CREATE TABLE IF NOT EXISTS "'.$prefix.'ci_sessions" ( + "session_id" character varying(40) NOT NULL DEFAULT 0, + "ip_address" character varying(16) NOT NULL DEFAULT 0, + "user_agent" character varying(120) NOT NULL, + "last_activity" integer NOT NULL DEFAULT 0, + "user_data" text NOT NULL, + PRIMARY KEY ("session_id") + ); + CREATE INDEX "'.$prefix.'ci_sessions_last_activity_idx" ON "'.$prefix.'ci_sessions" ("last_activity"); + '); + + $this->db->query(' + ALTER TABLE "'.$prefix.'files" ADD "user" integer NOT NULL DEFAULT 0; + CREATE INDEX "'.$prefix.'user_idx" ON "'.$prefix.'files" ("user"); + '); + + } else { + + $this->db->query(' + CREATE TABLE IF NOT EXISTS `'.$prefix.'users` ( + `id` int(8) UNSIGNED NOT NULL AUTO_INCREMENT, + `username` varchar(32) COLLATE ascii_general_ci NOT NULL, + `password` varchar(60) COLLATE ascii_general_ci NOT NULL, + `email` varchar(255) COLLATE ascii_general_ci NOT NULL, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + '); + + $this->db->query(' + CREATE TABLE IF NOT EXISTS `'.$prefix.'ci_sessions` ( + `session_id` varchar(40) NOT NULL DEFAULT 0, + `ip_address` varchar(16) NOT NULL DEFAULT 0, + `user_agent` varchar(120) NOT NULL, + `last_activity` int(10) unsigned NOT NULL DEFAULT 0, + `user_data` text NOT NULL, + PRIMARY KEY (`session_id`), + KEY `last_activity_idx` (`last_activity`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + '); + + $this->db->query(' + ALTER TABLE `'.$prefix.'files` + ADD `user` INT(8) UNSIGNED NOT NULL DEFAULT 0, + ADD INDEX (`user`) + '); + } + } + + public function down() + { + $this->dbforge->drop_table('users'); + $this->dbforge->drop_table('ci_sessions'); + if ($this->db->dbdriver == 'postgre') { + $this->db->query('ALTER TABLE "'.$prefix.'files" DROP "user"'); + } else { + $this->db->query('ALTER TABLE `'.$prefix.'files` DROP `user`'); + } + } +} diff --git a/application/migrations/003_add_referrers.php b/application/migrations/003_add_referrers.php new file mode 100644 index 000000000..e6da1c0dd --- /dev/null +++ b/application/migrations/003_add_referrers.php @@ -0,0 +1,61 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_Add_referrers extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + CREATE TABLE "'.$prefix.'invitations" ( + "user" integer NOT NULL, + "key" character varying(16) NOT NULL, + "date" integer NOT NULL, + PRIMARY KEY ("key") + ); + CREATE INDEX "'.$prefix.'invitations_user_idx" ON "'.$prefix.'invitations" ("user"); + CREATE INDEX "'.$prefix.'invitations_date_idx" ON "'.$prefix.'invitations" ("date"); + '); + $this->db->query(' + ALTER TABLE "'.$prefix.'users" + ADD "referrer" integer NOT NULL DEFAULT 0 + '); + + } else { + + $this->db->query(' + CREATE TABLE `'.$prefix.'invitations` ( + `user` int(8) unsigned NOT NULL, + `key` varchar(16) CHARACTER SET ascii NOT NULL, + `date` int(11) unsigned NOT NULL, + PRIMARY KEY (`key`), + KEY `user` (`user`), + KEY `date` (`date`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin + '); + $this->db->query(' + ALTER TABLE `'.$prefix.'users` + ADD `referrer` INT(8) UNSIGNED NOT NULL DEFAULT 0 + '); + } + } + + public function down() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'users" DROP "referrer" + '); + } else { + $this->db->query(' + ALTER TABLE `'.$prefix.'users` DROP `referrer` + '); + } + $this->dbforge->drop_table('invitations'); + + } +} diff --git a/application/migrations/004_add_filesize.php b/application/migrations/004_add_filesize.php new file mode 100644 index 000000000..ca10e7dc3 --- /dev/null +++ b/application/migrations/004_add_filesize.php @@ -0,0 +1,37 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_Add_filesize extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'files" + ADD "filesize" integer NOT NULL + '); + } else { + $this->db->query(' + ALTER TABLE `'.$prefix.'files` + ADD `filesize` INT UNSIGNED NOT NULL + '); + } + } + + public function down() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'files" DROP "filesize" + '); + } else { + $this->db->query(' + ALTER TABLE `'.$prefix.'files` DROP `filesize` + '); + } + } +} diff --git a/application/migrations/005_drop_file_password.php b/application/migrations/005_drop_file_password.php new file mode 100644 index 000000000..1b3b5fc12 --- /dev/null +++ b/application/migrations/005_drop_file_password.php @@ -0,0 +1,33 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_Drop_file_password extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query('ALTER TABLE "'.$prefix.'files" DROP "password"'); + } else { + $this->db->query('ALTER TABLE `'.$prefix.'files` DROP `password`;'); + } + } + + public function down() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'files" + ADD "password" character varying(40) DEFAULT NULL + '); + } else { + $this->db->query(" + ALTER TABLE `'.$prefix.'files` + ADD `password` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL; + "); + } + } +} diff --git a/application/migrations/006_add_username_index.php b/application/migrations/006_add_username_index.php new file mode 100644 index 000000000..1633a95a9 --- /dev/null +++ b/application/migrations/006_add_username_index.php @@ -0,0 +1,35 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_Add_username_index extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + CREATE UNIQUE INDEX "'.$prefix.'users_username_idx" ON "'.$prefix.'users" ("username") + '); + } else { + $this->db->query(' + ALTER TABLE `'.$prefix.'users` + ADD UNIQUE `username` (`username`); + '); + } + } + + public function down() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query('DROP INDEX "'.$prefix.'users_username_idx"'); + } else { + $this->db->query(" + ALTER TABLE `'.$prefix.'users` + DROP INDEX `username`; + "); + } + } +} diff --git a/application/migrations/007_repurpose_invitations.php b/application/migrations/007_repurpose_invitations.php new file mode 100644 index 000000000..0bc39c64b --- /dev/null +++ b/application/migrations/007_repurpose_invitations.php @@ -0,0 +1,68 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_Repurpose_invitations extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'invitations" + ADD "action" character varying(255) NOT NULL, + ADD "data" TEXT NULL; + CREATE INDEX "'.$prefix.'invitations_action_idx" ON '.$prefix.'invitations ("action"); + '); + + $this->db->query(' + UPDATE "'.$prefix.'invitations" SET "action" = \'invitation\' WHERE "action" = \'\' + '); + + $this->db->query(' + ALTER TABLE "'.$prefix.'invitations" RENAME TO '.$prefix.'actions; + '); + + } else { + + $this->db->query(' + ALTER TABLE `'.$prefix.'invitations` + ADD `action` VARCHAR(255) NOT NULL, + ADD `data` TEXT NULL, + ADD INDEX `action` (`action`); + '); + + $this->db->query(' + UPDATE `'.$prefix.'invitations` SET `action` = \'invitation\' WHERE `action` = \'\'; + '); + + $this->db->query(' + ALTER TABLE `'.$prefix.'invitations` RENAME `'.$prefix.'actions`; + '); + } + } + + public function down() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query('ALTER TABLE "'.$prefix.'actions" RENAME TO "'.$prefix.'invitations"'); + $this->db->query(' + ALTER TABLE "'.$prefix.'invitations" + DROP "action", + DROP "data"; + '); + + } else { + + $this->db->query('ALTER TABLE `'.$prefix.'actions` RENAME `'.$prefix.'invitations`'); + $this->db->query(' + ALTER TABLE `'.$prefix.'invitations` + DROP `action`, + DROP `data`; + '); + } + + } +} diff --git a/application/migrations/008_add_profiles.php b/application/migrations/008_add_profiles.php new file mode 100644 index 000000000..4cdd14de0 --- /dev/null +++ b/application/migrations/008_add_profiles.php @@ -0,0 +1,61 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_Add_profiles extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + CREATE TABLE "'.$prefix.'profiles" ( + "user" integer NOT NULL, + "upload_id_limits" varchar(255) NOT NULL, + PRIMARY KEY ("user") + ) + '); + + $this->db->query(' + ALTER TABLE "'.$prefix.'files" ALTER COLUMN "id" TYPE varchar(255); + '); + + } else { + + $this->db->query(' + CREATE TABLE `'.$prefix.'profiles` ( + `user` int(8) unsigned NOT NULL, + `upload_id_limits` varchar(255) COLLATE utf8_bin NOT NULL, + PRIMARY KEY (`user`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin + '); + + $this->db->query(' + ALTER TABLE `'.$prefix.'files` CHANGE `id` `id` VARCHAR( 255 ); + '); + } + } + + public function down() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + DROP TABLE "'.$prefix.'profiles"; + '); + $this->db->query(' + ALTER TABLE "'.$prefix.'files" ALTER COLUMN "id" TYPE varchar(6); + '); + + } else { + + $this->db->query(" + DROP TABLE `'.$prefix.'profiles`; + "); + $this->db->query(" + ALTER TABLE `'.$prefix.'files` CHANGE `id` `id` VARCHAR( 6 ); + "); + } + } +} diff --git a/application/migrations/009_add_apikeys.php b/application/migrations/009_add_apikeys.php new file mode 100644 index 000000000..e9dba4e41 --- /dev/null +++ b/application/migrations/009_add_apikeys.php @@ -0,0 +1,41 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_Add_apikeys extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + CREATE TABLE "'.$prefix.'apikeys" ( + "key" varchar(64) NOT NULL, + "user" integer NOT NULL, + "created" timestamp WITH TIME ZONE NOT NULL DEFAULT NOW(), + "comment" varchar(255) NOT NULL, + PRIMARY KEY ("key") + ); + CREATE INDEX "'.$prefix.'apikeys_user_idx" ON "'.$prefix.'apikeys" ("user"); + '); + + } else { + + $this->db->query(' + CREATE TABLE `'.$prefix.'apikeys` ( + `key` varchar(64) COLLATE utf8_bin NOT NULL, + `user` int(8) unsigned NOT NULL, + `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `comment` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + PRIMARY KEY (`key`), + KEY `user` (`user`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin + '); + } + } + + public function down() + { + $this->dbforge->drop_table('apikeys'); + } +} diff --git a/application/migrations/010_files_innodb.php b/application/migrations/010_files_innodb.php new file mode 100644 index 000000000..98f9dea31 --- /dev/null +++ b/application/migrations/010_files_innodb.php @@ -0,0 +1,20 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_files_innodb extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver != 'postgre') { + $this->db->query(' + ALTER TABLE `'.$prefix.'files` ENGINE = InnoDB; + '); + } + } + + public function down() + { + } +} diff --git a/application/migrations/011_apikeys_add_access_level.php b/application/migrations/011_apikeys_add_access_level.php new file mode 100644 index 000000000..14d0b03d3 --- /dev/null +++ b/application/migrations/011_apikeys_add_access_level.php @@ -0,0 +1,35 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_apikeys_add_access_level extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + alter table "'.$prefix.'apikeys" add "access_level" varchar(255) default \'apikey\' + '); + } else { + $this->db->query(' + alter table `'.$prefix.'apikeys` add `access_level` varchar(255) default \'apikey\'; + '); + } + } + + public function down() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + alter table "'.$prefix.'apikeys" drop "access_level" + '); + } else { + $this->db->query(' + alter table `'.$prefix.'apikeys` drop `access_level` + '); + } + } +} diff --git a/application/migrations/012_add_constraints.php b/application/migrations/012_add_constraints.php new file mode 100644 index 000000000..a2569d54d --- /dev/null +++ b/application/migrations/012_add_constraints.php @@ -0,0 +1,36 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_add_constraints extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query('ALTER TABLE "'.$prefix.'users" ALTER COLUMN "referrer" TYPE integer'); + $this->db->query('ALTER TABLE "'.$prefix.'users" ALTER COLUMN "referrer" DROP NOT NULL'); + $this->db->query('CREATE INDEX "'.$prefix.'users_referrer_idx" ON "'.$prefix.'users" ("referrer")'); + $this->db->query('UPDATE "'.$prefix.'users" SET "referrer" = NULL where "referrer" = 0'); + $this->db->query(' + ALTER TABLE "'.$prefix.'users" + ADD CONSTRAINT "'.$prefix.'referrer_user_fkey" FOREIGN KEY ("referrer") + REFERENCES "'.$prefix.'users"("id") ON DELETE RESTRICT ON UPDATE RESTRICT + '); + + } else { + + $this->db->query('ALTER TABLE `'.$prefix.'users` ADD INDEX(`referrer`);'); + $this->db->query('ALTER TABLE `'.$prefix.'users` CHANGE `referrer` `referrer` + INT(8) UNSIGNED NULL;'); + $this->db->query('UPDATE `'.$prefix.'users` SET `referrer` = NULL where `referrer` = 0;'); + $this->db->query('ALTER TABLE `'.$prefix.'users` ADD FOREIGN KEY (`referrer`) + REFERENCES `'.$prefix.'users`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;'); + } + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} diff --git a/application/migrations/013_add_multipaste.php b/application/migrations/013_add_multipaste.php new file mode 100644 index 000000000..6dd9bcb7b --- /dev/null +++ b/application/migrations/013_add_multipaste.php @@ -0,0 +1,62 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_add_multipaste extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + CREATE TABLE "'.$prefix.'multipaste" ( + "url_id" varchar(255) NOT NULL PRIMARY KEY, + "multipaste_id" serial UNIQUE, + "user_id" integer NOT NULL, + "date" integer NOT NULL + ); + CREATE INDEX "'.$prefix.'multipaste_user_idx" ON "'.$prefix.'multipaste" ("user_id"); + '); + + $this->db->query(' + CREATE TABLE "'.$prefix.'multipaste_file_map" ( + "multipaste_id" integer NOT NULL REFERENCES "'.$prefix.'multipaste" ("multipaste_id") ON DELETE CASCADE ON UPDATE CASCADE, + "file_url_id" varchar(255) NOT NULL REFERENCES "'.$prefix.'files"("id") ON DELETE CASCADE ON UPDATE CASCADE, + "sort_order" serial PRIMARY KEY, + UNIQUE ("multipaste_id", "file_url_id") + ); + CREATE INDEX "'.$prefix.'multipaste_file_map_file_idx" ON "'.$prefix.'multipaste_file_map" ("file_url_id"); + '); + + } else { + + $this->db->query(' + CREATE TABLE `'.$prefix.'multipaste` ( + `url_id` varchar(255) NOT NULL, + `multipaste_id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` int(11) NOT NULL, + `date` int(11) NOT NULL, + PRIMARY KEY (`url_id`), + UNIQUE KEY `multipaste_id` (`multipaste_id`), + KEY `user_id` (`user_id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8;'); + + $this->db->query(' + CREATE TABLE `'.$prefix.'multipaste_file_map` ( + `multipaste_id` int(11) NOT NULL, + `file_url_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, + `sort_order` int(10) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`sort_order`), + UNIQUE KEY `multipaste_id` (`multipaste_id`,`file_url_id`), + KEY `multipaste_file_map_ibfk_2` (`file_url_id`), + CONSTRAINT `'.$prefix.'multipaste_file_map_ibfk_1` FOREIGN KEY (`multipaste_id`) REFERENCES `'.$prefix.'multipaste` (`multipaste_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `'.$prefix.'multipaste_file_map_ibfk_2` FOREIGN KEY (`file_url_id`) REFERENCES `'.$prefix.'files` (`id`) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;'); + } + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} diff --git a/application/migrations/014_deduplicate_file_storage.php b/application/migrations/014_deduplicate_file_storage.php new file mode 100644 index 000000000..bd96c56d8 --- /dev/null +++ b/application/migrations/014_deduplicate_file_storage.php @@ -0,0 +1,137 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_deduplicate_file_storage extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + CREATE TABLE "'.$prefix.'file_storage" ( + "id" serial NOT NULL, + "filesize" integer NOT NULL, + "mimetype" varchar(255) NOT NULL, + "hash" char(32) NOT NULL, + "date" integer NOT NULL, + PRIMARY KEY ("id"), + UNIQUE ("id", "hash") + ); + '); + $this->db->query(' + ALTER TABLE "'.$prefix.'files" + ADD "file_storage_id" integer NULL; + CREATE INDEX "'.$prefix.'files_file_storage_id_idx" ON "'.$prefix.'files" ("file_storage_id"); + '); + + $this->db->query(' + INSERT INTO "'.$prefix.'file_storage" (filesize, mimetype, hash, date) + SELECT filesize, mimetype, hash, date FROM "'.$prefix.'files"; + '); + + $this->db->query(' + UPDATE "'.$prefix.'files" f + SET file_storage_id = fs.id + FROM "'.$prefix.'file_storage" fs + WHERE fs.hash = f.hash + '); + + // remove file_storage rows that are not referenced by files.id + // AND that are duplicates when grouped by hash + $this->db->query(' + DELETE + FROM "'.$prefix.'file_storage" fs + USING "'.$prefix.'file_storage" fs2 + WHERE fs.hash = fs2.hash + AND fs.id > fs2.id + AND fs.id NOT IN ( + SELECT file_storage_id + FROM "'.$prefix.'files" f + ); + '); + } else { + $this->db->query(' + CREATE TABLE `'.$prefix.'file_storage` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `filesize` int(11) NOT NULL, + `mimetype` varchar(255) NOT NULL, + `hash` char(32) NOT NULL, + `date` int(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `data_id` (`id`, `hash`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + '); + $this->db->query(' + ALTER TABLE `'.$prefix.'files` + ADD `file_storage_id` INT NOT NULL, + ADD INDEX (`file_storage_id`); + '); + + $this->db->query(' + INSERT INTO `'.$prefix.'file_storage` (id, filesize, mimetype, hash, date) + SELECT NULL, filesize, mimetype, hash, date FROM `'.$prefix.'files`; + '); + + $this->db->query(' + UPDATE `'.$prefix.'files` f + JOIN `'.$prefix.'file_storage` fs ON fs.hash = f.hash + SET f.file_storage_id = fs.id + '); + + // XXX: This query also exists in migration 15 + $this->db->query(' + DELETE fs + FROM `'.$prefix.'file_storage` fs, `'.$prefix.'file_storage` fs2 + WHERE fs.hash = fs2.hash + AND fs.id > fs2.id + AND fs.id NOT IN ( + SELECT file_storage_id + FROM `'.$prefix.'files` f + ); + '); + } + + $chunk = 500; + $total = $this->db->count_all("file_storage"); + + for ($limit = 0; $limit < $total; $limit += $chunk) { + $query = $this->db->select('hash, id') + ->from('file_storage') + ->limit($chunk, $limit) + ->get()->result_array(); + + foreach ($query as $key => $item) { + $old = $this->mfile->file($item["hash"]); + $data_id = $item["hash"].'-'.$item["id"]; + $new = $this->mfile->file($data_id); + if (file_exists($old)) { + rename($old, $new); + } else { + echo "Warning: no file found for $data_id. Skipping...\n"; + } + } + } + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'files" + ADD FOREIGN KEY ("file_storage_id") REFERENCES "'.$prefix.'file_storage"("id") ON DELETE CASCADE ON UPDATE CASCADE; + '); + } else { + $this->db->query(' + ALTER TABLE `'.$prefix.'files` + ADD FOREIGN KEY (`file_storage_id`) REFERENCES `'.$prefix.'file_storage`(`id`) ON DELETE CASCADE ON UPDATE CASCADE; + '); + } + + $this->dbforge->drop_column("files", "hash"); + $this->dbforge->drop_column("files", "mimetype"); + $this->dbforge->drop_column("files", "filesize"); + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} diff --git a/application/migrations/015_actually_deduplicate_file_storage.php b/application/migrations/015_actually_deduplicate_file_storage.php new file mode 100644 index 000000000..76ff2d6b9 --- /dev/null +++ b/application/migrations/015_actually_deduplicate_file_storage.php @@ -0,0 +1,56 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_actually_deduplicate_file_storage extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + // no need for this query since 14 is not buggy + } else { + // XXX: This query also exists in migration 14 + $this->db->query(' + DELETE fs + FROM `'.$prefix.'file_storage` fs, `'.$prefix.'file_storage` fs2 + WHERE fs.hash = fs2.hash + AND fs.id > fs2.id + AND fs.id NOT IN ( + SELECT file_storage_id + FROM `'.$prefix.'files` f + ); + '); + } + + $chunk = 500; + $total = $this->db->count_all("file_storage"); + $consistent = true; + + for ($limit = 0; $limit < $total; $limit += $chunk) { + $query = $this->db->select('hash, id') + ->from('file_storage') + ->limit($chunk, $limit) + ->get()->result_array(); + + foreach ($query as $key => $item) { + $data_id = $item["hash"].'-'.$item["id"]; + $file = $this->mfile->file($data_id); + if (!file_exists($file)) { + echo "Warning: no file found for $data_id\n"; + $consistent = false; + } + } + } + + if (!$consistent) { + echo "Your database is not consistent with your file system.\n"; + echo "Please report this as it is most likely a bug.\n"; + } + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} diff --git a/application/migrations/016_allow_ipv6_storage.php b/application/migrations/016_allow_ipv6_storage.php new file mode 100644 index 000000000..726a18601 --- /dev/null +++ b/application/migrations/016_allow_ipv6_storage.php @@ -0,0 +1,27 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_allow_ipv6_storage extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'ci_sessions" + ALTER COLUMN "ip_address" type varchar(39); + '); + } else { + $this->db->query(' + ALTER TABLE `'.$prefix.'ci_sessions` + CHANGE `ip_address` `ip_address` varchar(39); + '); + } + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} diff --git a/application/migrations/017_increase_password_length.php b/application/migrations/017_increase_password_length.php new file mode 100644 index 000000000..9d12d3f52 --- /dev/null +++ b/application/migrations/017_increase_password_length.php @@ -0,0 +1,27 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_increase_password_length extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'users" + ALTER COLUMN "password" type varchar(255); + '); + } else { + $this->db->query(' + ALTER TABLE `'.$prefix.'users` + CHANGE `password` `password` varchar(255); + '); + } + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} diff --git a/application/migrations/018_allow_null_values_userinfo.php b/application/migrations/018_allow_null_values_userinfo.php new file mode 100644 index 000000000..1497dd0d4 --- /dev/null +++ b/application/migrations/018_allow_null_values_userinfo.php @@ -0,0 +1,31 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_allow_null_values_userinfo extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'users" + ALTER COLUMN "username" DROP NOT NULL, + ALTER COLUMN "password" DROP NOT NULL, + ALTER COLUMN "email" DROP NOT NULL; + '); + } else { + $this->db->query(' + ALTER TABLE `'.$prefix.'users` + CHANGE `username` `username` varchar(32) NULL, + CHANGE `password` `password` varchar(255) NULL, + CHANGE `email` `email` varchar(255) NULL; + '); + } + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} diff --git a/application/migrations/019_change_filesize_type.php b/application/migrations/019_change_filesize_type.php new file mode 100644 index 000000000..33abf89ed --- /dev/null +++ b/application/migrations/019_change_filesize_type.php @@ -0,0 +1,51 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_change_filesize_type extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'file_storage" + ALTER "filesize" TYPE bigint; + '); + } else { + $this->db->query(' + ALTER TABLE `'.$prefix.'file_storage` + MODIFY `filesize` bigint; + '); + } + + $chunk = 500; + + $this->db->where('filesize', 2147483647); + $total = $this->db->count_all_results("file_storage"); + + for ($limit = 0; $limit < $total; $limit += $chunk) { + $query = $this->db->select('hash, id') + ->from('file_storage') + ->where('filesize', 2147483647) + ->limit($chunk, $limit) + ->get()->result_array(); + + foreach ($query as $key => $item) { + $data_id = $item["hash"].'-'.$item['id']; + $filesize = filesize($this->mfile->file($data_id)); + + $this->db->where('id', $item['id']) + ->set(array( + 'filesize' => $filesize, + )) + ->update('file_storage'); + } + } + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} diff --git a/application/migrations/020_update_session_table.php b/application/migrations/020_update_session_table.php new file mode 100644 index 000000000..94a240def --- /dev/null +++ b/application/migrations/020_update_session_table.php @@ -0,0 +1,45 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_update_session_table extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + $this->db->query(' + ALTER TABLE "'.$prefix.'ci_sessions" + DROP COLUMN "user_agent"; + '); + $this->db->query(' + ALTER TABLE "'.$prefix.'ci_sessions" + RENAME COLUMN "session_id" TO "id"; + '); + $this->db->query(' + ALTER TABLE "'.$prefix.'ci_sessions" + RENAME COLUMN "last_activity" TO "timestamp"; + '); + $this->db->query(' + ALTER TABLE "'.$prefix.'ci_sessions" + RENAME COLUMN "user_data" TO "data"; + '); + $this->db->query(' + ALTER TABLE "'.$prefix.'ci_sessions" ALTER COLUMN id SET DATA TYPE varchar(128); + '); + } else { + $this->db->query(' + ALTER TABLE `'.$prefix.'ci_sessions` + DROP `user_agent`, + CHANGE `session_id` `id` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, + CHANGE `last_activity` `timestamp` INT(10) UNSIGNED NOT NULL DEFAULT 0, + CHANGE `user_data` `data` BLOB NOT NULL; + '); + } + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} diff --git a/application/migrations/021_change_charset.php b/application/migrations/021_change_charset.php new file mode 100644 index 000000000..475732ed5 --- /dev/null +++ b/application/migrations/021_change_charset.php @@ -0,0 +1,28 @@ +<?php +defined('BASEPATH') OR exit('No direct script access allowed'); + +class Migration_change_charset extends CI_Migration { + + public function up() + { + $prefix = $this->db->dbprefix; + + if ($this->db->dbdriver == 'postgre') { + # nothing to do + } else { + $this->db->query('SET FOREIGN_KEY_CHECKS = 0'); + foreach ([ + ['apikeys', 'comment', 'VARCHAR(255)'], + ['files', 'filename', 'VARCHAR(256)'], + ] as $col) { + $this->db->query('ALTER TABLE `'.$prefix.$col[0].'` CHANGE `'.$col[1].'` `'.$col[1].'` '.$col[2].' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;'); + } + $this->db->query('SET FOREIGN_KEY_CHECKS = 1'); + } + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} |