From 6f1258fbf27b05092ed0712c7d20bafda42074ea Mon Sep 17 00:00:00 2001 From: Florian Pritz Date: Tue, 3 Feb 2015 23:30:13 +0100 Subject: Support database table prefixes This also cleans up some inconsistencies with quotes. Signed-off-by: Florian Pritz --- application/migrations/001_add_files.php | 14 +++--- application/migrations/002_add_users.php | 42 ++++++++--------- application/migrations/003_add_referrers.php | 30 +++++++------ application/migrations/004_add_filesize.php | 16 ++++--- application/migrations/005_drop_file_password.php | 12 +++-- application/migrations/006_add_username_index.php | 14 +++--- .../migrations/007_repurpose_invitations.php | 38 +++++++++------- application/migrations/008_add_profiles.php | 28 +++++++----- application/migrations/009_add_apikeys.php | 12 ++--- application/migrations/010_files_innodb.php | 8 ++-- .../migrations/011_apikeys_add_access_level.php | 16 ++++--- application/migrations/012_add_constraints.php | 28 ++++++------ application/migrations/013_add_multipaste.php | 22 ++++----- .../migrations/014_deduplicate_file_storage.php | 52 ++++++++++++++++++++++ 14 files changed, 212 insertions(+), 120 deletions(-) create mode 100644 application/migrations/014_deduplicate_file_storage.php (limited to 'application/migrations') diff --git a/application/migrations/001_add_files.php b/application/migrations/001_add_files.php index 30f567325..dd37d08c3 100644 --- a/application/migrations/001_add_files.php +++ b/application/migrations/001_add_files.php @@ -5,9 +5,11 @@ 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 "files" ( + CREATE TABLE IF NOT EXISTS "'.$prefix.'files" ( "hash" varchar(32) NOT NULL, "id" varchar(6) NOT NULL, "filename" varchar(256) NOT NULL, @@ -16,12 +18,12 @@ class Migration_Add_files extends CI_Migration { "mimetype" varchar(255) NOT NULL, PRIMARY KEY ("id") ); - CREATE INDEX "files_date_idx" ON files ("date"); - CREATE INDEX "files_hash_id_idx" ON files ("hash", "id"); + CREATE INDEX "files_date_idx" ON '.$prefix.'files ("date"); + CREATE INDEX "files_hash_id_idx" ON '.$prefix.'files ("hash", "id"); '); } else { - $this->db->query(" - CREATE TABLE IF NOT EXISTS `files` ( + $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, @@ -32,7 +34,7 @@ class Migration_Add_files extends CI_Migration { KEY `date` (`date`), KEY `hash` (`hash`,`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; - "); + '); } } diff --git a/application/migrations/002_add_users.php b/application/migrations/002_add_users.php index 322415d9b..5ccef6669 100644 --- a/application/migrations/002_add_users.php +++ b/application/migrations/002_add_users.php @@ -5,9 +5,11 @@ 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 "users" ( + CREATE TABLE IF NOT EXISTS "'.$prefix.'users" ( "id" serial PRIMARY KEY, "username" character varying(32) NOT NULL, "password" character varying(60) NOT NULL, @@ -16,7 +18,7 @@ class Migration_Add_users extends CI_Migration { '); $this->db->query(' - CREATE TABLE IF NOT EXISTS "ci_sessions" ( + 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, @@ -24,43 +26,43 @@ class Migration_Add_users extends CI_Migration { "user_data" text NOT NULL, PRIMARY KEY ("session_id") ); - CREATE INDEX "ci_sessions_last_activity_idx" ON "ci_sessions" ("last_activity"); + CREATE INDEX "ci_sessions_last_activity_idx" ON "'.$prefix.'ci_sessions" ("last_activity"); '); $this->db->query(' - ALTER TABLE "files" ADD "user" integer NOT NULL DEFAULT 0; - CREATE INDEX "user_idx" ON "files" ("user"); + ALTER TABLE "'.$prefix.'files" ADD "user" integer NOT NULL DEFAULT 0; + CREATE INDEX "user_idx" ON "'.$prefix.'files" ("user"); '); } else { - $this->db->query(" - CREATE TABLE IF NOT EXISTS `users` ( + $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 `ci_sessions` ( - `session_id` varchar(40) NOT NULL DEFAULT '0', - `ip_address` varchar(16) NOT NULL DEFAULT '0', + $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', + `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 `files` - ADD `user` INT(8) UNSIGNED NOT NULL DEFAULT '0', + $this->db->query(' + ALTER TABLE `'.$prefix.'files` + ADD `user` INT(8) UNSIGNED NOT NULL DEFAULT 0, ADD INDEX (`user`) - "); + '); } } @@ -69,9 +71,9 @@ class Migration_Add_users extends CI_Migration { $this->dbforge->drop_table('users'); $this->dbforge->drop_table('ci_sessions'); if ($this->db->dbdriver == 'postgre') { - $this->db->query('ALTER TABLE "files" DROP "user"'); + $this->db->query('ALTER TABLE "'.$prefix.'files" DROP "user"'); } else { - $this->db->query('ALTER TABLE `files` DROP `user`'); + $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 index e30f1faef..c504b5539 100644 --- a/application/migrations/003_add_referrers.php +++ b/application/migrations/003_add_referrers.php @@ -5,26 +5,28 @@ class Migration_Add_referrers extends CI_Migration { public function up() { + $prefix = $this->db->dbprefix; + if ($this->db->dbdriver == 'postgre') { $this->db->query(' - CREATE TABLE "invitations" ( + CREATE TABLE "'.$prefix.'invitations" ( "user" integer NOT NULL, "key" character varying(16) NOT NULL, "date" integer NOT NULL, PRIMARY KEY ("key") ); - CREATE INDEX "invitations_user_idx" ON "invitations" ("user"); - CREATE INDEX "invitations_date_idx" ON "invitations" ("date"); + CREATE INDEX "invitations_user_idx" ON "'.$prefix.'invitations" ("user"); + CREATE INDEX "invitations_date_idx" ON "'.$prefix.'invitations" ("date"); '); $this->db->query(' - ALTER TABLE "users" + ALTER TABLE "'.$prefix.'users" ADD "referrer" integer NOT NULL DEFAULT 0 '); } else { - $this->db->query(" - CREATE TABLE `invitations` ( + $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, @@ -32,23 +34,25 @@ class Migration_Add_referrers extends CI_Migration { KEY `user` (`user`), KEY `date` (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin - "); - $this->db->query(" - ALTER TABLE `users` - ADD `referrer` INT(8) UNSIGNED NOT NULL DEFAULT '0' - "); + '); + $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 "users" DROP "referrer" + ALTER TABLE "'.$prefix.'users" DROP "referrer" '); } else { $this->db->query(' - ALTER TABLE `users` DROP `referrer` + 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 index f2367937a..ca10e7dc3 100644 --- a/application/migrations/004_add_filesize.php +++ b/application/migrations/004_add_filesize.php @@ -5,28 +5,32 @@ class Migration_Add_filesize extends CI_Migration { public function up() { + $prefix = $this->db->dbprefix; + if ($this->db->dbdriver == 'postgre') { $this->db->query(' - ALTER TABLE "files" + ALTER TABLE "'.$prefix.'files" ADD "filesize" integer NOT NULL '); } else { - $this->db->query(" - ALTER TABLE `files` + $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 "files" DROP "filesize" + ALTER TABLE "'.$prefix.'files" DROP "filesize" '); } else { $this->db->query(' - ALTER TABLE `files` DROP `filesize` + ALTER TABLE `'.$prefix.'files` DROP `filesize` '); } } diff --git a/application/migrations/005_drop_file_password.php b/application/migrations/005_drop_file_password.php index e8b7f8952..1b3b5fc12 100644 --- a/application/migrations/005_drop_file_password.php +++ b/application/migrations/005_drop_file_password.php @@ -5,23 +5,27 @@ 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 "files" DROP "password"'); + $this->db->query('ALTER TABLE "'.$prefix.'files" DROP "password"'); } else { - $this->db->query("ALTER TABLE `files` DROP `password`;"); + $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 "files" + ALTER TABLE "'.$prefix.'files" ADD "password" character varying(40) DEFAULT NULL '); } else { $this->db->query(" - ALTER TABLE `files` + 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 index 5b8c3584f..0e6dc7650 100644 --- a/application/migrations/006_add_username_index.php +++ b/application/migrations/006_add_username_index.php @@ -5,25 +5,29 @@ 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 "users_username_idx" ON "users" ("username") + CREATE UNIQUE INDEX "users_username_idx" ON "'.$prefix.'users" ("username") '); } else { - $this->db->query(" - ALTER TABLE `users` + $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 "users_username_idx"'); } else { $this->db->query(" - ALTER TABLE `users` + ALTER TABLE `'.$prefix.'users` DROP INDEX `username`; "); } diff --git a/application/migrations/007_repurpose_invitations.php b/application/migrations/007_repurpose_invitations.php index fb40e8179..ed9b136a0 100644 --- a/application/migrations/007_repurpose_invitations.php +++ b/application/migrations/007_repurpose_invitations.php @@ -5,56 +5,60 @@ class Migration_Repurpose_invitations extends CI_Migration { public function up() { + $prefix = $this->db->dbprefix; + if ($this->db->dbdriver == 'postgre') { $this->db->query(' - ALTER TABLE "invitations" + ALTER TABLE "'.$prefix.'invitations" ADD "action" character varying(255) NOT NULL, ADD "data" TEXT NULL; - CREATE INDEX "invitations_action_idx" ON invitations ("action"); + CREATE INDEX "invitations_action_idx" ON '.$prefix.'invitations ("action"); '); $this->db->query(' - UPDATE "invitations" SET "action" = \'invitation\' WHERE "action" = \'\' + UPDATE "'.$prefix.'invitations" SET "action" = \'invitation\' WHERE "action" = \'\' '); $this->db->query(' - ALTER TABLE "invitations" RENAME TO "actions"; + ALTER TABLE "'.$prefix.'invitations" RENAME TO "actions"; '); } else { - $this->db->query(" - ALTER TABLE `invitations` + $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 `invitations` SET `action` = 'invitation' WHERE `action` = ''; - "); + $this->db->query(' + UPDATE `'.$prefix.'invitations` SET `action` = \'invitation\' WHERE `action` = \'\'; + '); - $this->db->query(" - ALTER TABLE `invitations` RENAME `actions`; - "); + $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 "actions" RENAME TO "invitations"'); + $this->db->query('ALTER TABLE "'.$prefix.'actions" RENAME TO "'.$prefix.'invitations"'); $this->db->query(' - ALTER TABLE "invitations" + ALTER TABLE "'.$prefix.'invitations" DROP "action", DROP "data"; '); } else { - $this->db->query('ALTER TABLE `actions` RENAME `invitations`'); + $this->db->query('ALTER TABLE `'.$prefix.'actions` RENAME `'.$prefix.'invitations`'); $this->db->query(' - ALTER TABLE `invitations` + ALTER TABLE `'.$prefix.'invitations` DROP `action`, DROP `data`; '); diff --git a/application/migrations/008_add_profiles.php b/application/migrations/008_add_profiles.php index 9958fb03e..4cdd14de0 100644 --- a/application/migrations/008_add_profiles.php +++ b/application/migrations/008_add_profiles.php @@ -5,9 +5,11 @@ class Migration_Add_profiles extends CI_Migration { public function up() { + $prefix = $this->db->dbprefix; + if ($this->db->dbdriver == 'postgre') { $this->db->query(' - CREATE TABLE "profiles" ( + CREATE TABLE "'.$prefix.'profiles" ( "user" integer NOT NULL, "upload_id_limits" varchar(255) NOT NULL, PRIMARY KEY ("user") @@ -15,42 +17,44 @@ class Migration_Add_profiles extends CI_Migration { '); $this->db->query(' - ALTER TABLE "files" ALTER COLUMN "id" TYPE varchar(255); + ALTER TABLE "'.$prefix.'files" ALTER COLUMN "id" TYPE varchar(255); '); } else { - $this->db->query(" - CREATE TABLE `profiles` ( + $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 `files` CHANGE `id` `id` VARCHAR( 255 ); - "); + $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 "profiles"; + DROP TABLE "'.$prefix.'profiles"; '); $this->db->query(' - ALTER TABLE "files" ALTER COLUMN "id" TYPE varchar(6); + ALTER TABLE "'.$prefix.'files" ALTER COLUMN "id" TYPE varchar(6); '); } else { $this->db->query(" - DROP TABLE `profiles`; + DROP TABLE `'.$prefix.'profiles`; "); $this->db->query(" - ALTER TABLE `files` CHANGE `id` `id` VARCHAR( 6 ); + 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 index 8f2882e49..a5af809fc 100644 --- a/application/migrations/009_add_apikeys.php +++ b/application/migrations/009_add_apikeys.php @@ -5,22 +5,24 @@ class Migration_Add_apikeys extends CI_Migration { public function up() { + $prefix = $this->db->dbprefix; + if ($this->db->dbdriver == 'postgre') { $this->db->query(' - CREATE TABLE "apikeys" ( + 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 "apikeys_user_idx" ON "apikeys" ("user"); + CREATE INDEX "apikeys_user_idx" ON "'.$prefix.'apikeys" ("user"); '); } else { - $this->db->query(" - CREATE TABLE `apikeys` ( + $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, @@ -28,7 +30,7 @@ class Migration_Add_apikeys extends CI_Migration { PRIMARY KEY (`key`), KEY `user` (`user`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin - "); + '); } } diff --git a/application/migrations/010_files_innodb.php b/application/migrations/010_files_innodb.php index 318314bd3..98f9dea31 100644 --- a/application/migrations/010_files_innodb.php +++ b/application/migrations/010_files_innodb.php @@ -5,10 +5,12 @@ class Migration_files_innodb extends CI_Migration { public function up() { + $prefix = $this->db->dbprefix; + if ($this->db->dbdriver != 'postgre') { - $this->db->query(" - ALTER TABLE `files` ENGINE = InnoDB; - "); + $this->db->query(' + ALTER TABLE `'.$prefix.'files` ENGINE = InnoDB; + '); } } diff --git a/application/migrations/011_apikeys_add_access_level.php b/application/migrations/011_apikeys_add_access_level.php index 2fdbc7271..14d0b03d3 100644 --- a/application/migrations/011_apikeys_add_access_level.php +++ b/application/migrations/011_apikeys_add_access_level.php @@ -5,26 +5,30 @@ 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 "apikeys" add "access_level" varchar(255) default \'apikey\' + alter table "'.$prefix.'apikeys" add "access_level" varchar(255) default \'apikey\' '); } else { - $this->db->query(" - alter table `apikeys` add `access_level` varchar(255) default 'apikey'; - "); + $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 "apikeys" drop "access_level" + alter table "'.$prefix.'apikeys" drop "access_level" '); } else { $this->db->query(' - alter table `apikeys` drop `access_level` + alter table `'.$prefix.'apikeys` drop `access_level` '); } } diff --git a/application/migrations/012_add_constraints.php b/application/migrations/012_add_constraints.php index f298ceb5f..40a4540f6 100644 --- a/application/migrations/012_add_constraints.php +++ b/application/migrations/012_add_constraints.php @@ -5,25 +5,27 @@ class Migration_add_constraints extends CI_Migration { public function up() { + $prefix = $this->db->dbprefix; + if ($this->db->dbdriver == 'postgre') { - $this->db->query('ALTER TABLE "users" ALTER COLUMN "referrer" TYPE integer'); - $this->db->query('ALTER TABLE "users" ALTER COLUMN "referrer" DROP NOT NULL'); - $this->db->query('CREATE INDEX "users_referrer_idx" ON "users" ("referrer")'); - $this->db->query('UPDATE "users" SET "referrer" = NULL where "referrer" = 0'); + $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 "users_referrer_idx" ON "'.$prefix.'users" ("referrer")'); + $this->db->query('UPDATE "'.$prefix.'users" SET "referrer" = NULL where "referrer" = 0'); $this->db->query(' - ALTER TABLE "users" - ADD CONSTRAINT "referrer_user_fkey" FOREIGN KEY ("referrer") - REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE RESTRICT + 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 `users` ADD INDEX(`referrer`);"); - $this->db->query("ALTER TABLE `users` CHANGE `referrer` `referrer` - INT(8) UNSIGNED NULL;"); - $this->db->query("UPDATE `users` SET `referrer` = NULL where `referrer` = 0;"); - $this->db->query("ALTER TABLE `users` ADD FOREIGN KEY (`referrer`) - REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;"); + $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;'); } } diff --git a/application/migrations/013_add_multipaste.php b/application/migrations/013_add_multipaste.php index 539e9d292..6dd9bcb7b 100644 --- a/application/migrations/013_add_multipaste.php +++ b/application/migrations/013_add_multipaste.php @@ -5,31 +5,33 @@ class Migration_add_multipaste extends CI_Migration { public function up() { + $prefix = $this->db->dbprefix; + if ($this->db->dbdriver == 'postgre') { $this->db->query(' - CREATE TABLE "multipaste" ( + 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 "multipaste_user_idx" ON "multipaste" ("user_id"); + CREATE INDEX "'.$prefix.'multipaste_user_idx" ON "'.$prefix.'multipaste" ("user_id"); '); $this->db->query(' - CREATE TABLE "multipaste_file_map" ( - "multipaste_id" integer NOT NULL REFERENCES "multipaste" ("multipaste_id") ON DELETE CASCADE ON UPDATE CASCADE, - "file_url_id" varchar(255) NOT NULL REFERENCES "files"("id") ON DELETE CASCADE ON UPDATE CASCADE, + 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 "multipaste_file_map_file_idx" ON "multipaste_file_map" ("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 `multipaste` ( + CREATE TABLE `'.$prefix.'multipaste` ( `url_id` varchar(255) NOT NULL, `multipaste_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, @@ -40,15 +42,15 @@ class Migration_add_multipaste extends CI_Migration { ) ENGINE=InnoDB DEFAULT CHARSET=utf8;'); $this->db->query(' - CREATE TABLE `multipaste_file_map` ( + 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 `multipaste_file_map_ibfk_1` FOREIGN KEY (`multipaste_id`) REFERENCES `multipaste` (`multipaste_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `multipaste_file_map_ibfk_2` FOREIGN KEY (`file_url_id`) REFERENCES `files` (`id`) ON DELETE CASCADE ON UPDATE CASCADE + 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 ;'); } } diff --git a/application/migrations/014_deduplicate_file_storage.php b/application/migrations/014_deduplicate_file_storage.php new file mode 100644 index 000000000..8f8f40430 --- /dev/null +++ b/application/migrations/014_deduplicate_file_storage.php @@ -0,0 +1,52 @@ +db->dbprefix; + + // FIXME: use prefix + + if ($this->db->dbdriver == 'postgre') { + throw new \exceptions\ApiException("migration/postgres/not-implemented", "migration 14 not implemented yet for postgres"); + } else { + $this->db->query(' + CREATE TABLE `file_storage` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `filesize` int(11) NOT NULL, + `mimetype` varchar(255) NOT NULL, + `hash` char(32) NOT NULL, + `hash_collision_counter` int(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `data_id` (`hash`, `hash_collision_counter`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + '); + $this->db->query(' + ALTER TABLE `files` + ADD `file_storage_id` INT NOT NULL, + ADD INDEX (`file_storage_id`), + ADD FOREIGN KEY (`file_storage_id`) REFERENCES `filebin_test`.`file_storage`(`id`) ON DELETE CASCADE ON UPDATE CASCADE; + '); + + $this->db->query(' + INSERT INTO file_storage (storage-id, filesize, mimetype) + SELECT hash, filesize, mimetype FROM files; + '); + + $this->db->query(' + UPDATE files f + JOIN file_storage fs ON fs.data_id = f.hash + SET f.file_storage_id = fs.id + '); + + $this->dbforge->drop_column("files", array("hash", "mimetype", "filesize")); + } + } + + public function down() + { + throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported"); + } +} -- cgit v1.2.3-24-g4f1b