From 33f542487a89da4a8edad934b82aae7484f70ca3 Mon Sep 17 00:00:00 2001 From: Rafael Bodill Date: Thu, 18 Sep 2014 22:02:26 +0300 Subject: Migrations support for PostgreSQL --- application/migrations/001_add_files.php | 30 ++++--- application/migrations/002_add_users.php | 93 +++++++++++++++------- application/migrations/003_add_referrers.php | 64 ++++++++++----- application/migrations/004_add_filesize.php | 35 +++++--- application/migrations/005_drop_file_password.php | 30 +++++-- application/migrations/006_add_username_index.php | 32 ++++++-- .../migrations/007_repurpose_invitations.php | 71 ++++++++++++----- application/migrations/008_add_profiles.php | 60 ++++++++++---- application/migrations/009_add_apikeys.php | 36 ++++++--- application/migrations/010_files_innodb.php | 9 ++- .../migrations/011_apikeys_add_access_level.php | 30 +++++-- application/migrations/012_add_constraints.php | 27 +++++-- application/migrations/013_add_multipaste.php | 77 +++++++++++++----- 13 files changed, 426 insertions(+), 168 deletions(-) diff --git a/application/migrations/001_add_files.php b/application/migrations/001_add_files.php index f1f16ea3a..c5e4e7dfe 100644 --- a/application/migrations/001_add_files.php +++ b/application/migrations/001_add_files.php @@ -5,19 +5,23 @@ class Migration_Add_files extends CI_Migration { public function up() { - $this->db->query(" - CREATE TABLE IF NOT EXISTS `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; - "); + // Set database engine for MySQL drivers + if (strpos($this->db->dbdriver, 'mysql') !== FALSE) + { + $this->db->query('SET storage_engine=MYISAM'); + } + + $this->dbforge->add_field([ + 'hash' => [ 'type' => 'varchar', 'constraint' => 32, 'null' => FALSE ], + 'id' => [ 'type' => 'varchar', 'constraint' => 6, 'null' => FALSE ], + 'filename' => [ 'type' => 'varchar', 'constraint' => 256, 'null' => FALSE ], + 'password' => [ 'type' => 'varchar', 'constraint' => 40, 'null' => TRUE ], + 'date' => [ 'type' => 'integer', 'unsigned' => TRUE, 'null' => FALSE ], + 'mimetype' => [ 'type' => 'varchar', 'constraint' => 255, 'null' => FALSE ], + ]); + $this->dbforge->add_key('id', TRUE); + $this->dbforge->add_key([ 'hash', 'id' ]); + $this->dbforge->create_table('files', TRUE); } public function down() diff --git a/application/migrations/002_add_users.php b/application/migrations/002_add_users.php index 5675c77e9..d74402263 100644 --- a/application/migrations/002_add_users.php +++ b/application/migrations/002_add_users.php @@ -5,42 +5,77 @@ class Migration_Add_users extends CI_Migration { public function up() { - $this->db->query(" - CREATE TABLE IF NOT EXISTS `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; - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + CREATE TABLE IF NOT EXISTS "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 `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(' + CREATE TABLE IF NOT EXISTS "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 "ci_sessions_last_activity_idx" ON "ci_sessions" ("last_activity"); + '); - $this->db->query(" - ALTER TABLE `files` - ADD `user` INT(8) UNSIGNED NOT NULL DEFAULT '0', - ADD INDEX (`user`) - "); + $this->db->query(' + ALTER TABLE "files" ADD "user" integer NOT NULL DEFAULT 0; + CREATE INDEX "user_idx" ON "files" ("user"); + '); + } + else + { + $this->db->query(" + CREATE TABLE IF NOT EXISTS `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', + `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 `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'); - $this->db->query(" - ALTER TABLE `files` - DROP `user` - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query('ALTER TABLE "files" DROP "user"'); + } + else + { + $this->db->query('ALTER TABLE `files` DROP `user`'); + } } } diff --git a/application/migrations/003_add_referrers.php b/application/migrations/003_add_referrers.php index 524e92ff0..1f6b966e3 100644 --- a/application/migrations/003_add_referrers.php +++ b/application/migrations/003_add_referrers.php @@ -5,28 +5,56 @@ class Migration_Add_referrers extends CI_Migration { public function up() { - $this->db->query(" - CREATE TABLE `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 `users` - ADD `referrer` INT(8) UNSIGNED NOT NULL DEFAULT '0' - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + CREATE TABLE "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"); + '); + $this->db->query(' + ALTER TABLE "users" + ADD "referrer" integer NOT NULL DEFAULT 0 + '); + } + else + { + $this->db->query(" + CREATE TABLE `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 `users` + ADD `referrer` INT(8) UNSIGNED NOT NULL DEFAULT '0' + "); + } } public function down() { - $this->db->query(" - ALTER TABLE `users` - DROP `referrer` - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + ALTER TABLE "users" DROP "referrer" + '); + } + else + { + $this->db->query(' + ALTER TABLE `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 d7a70223d..7e5355c14 100644 --- a/application/migrations/004_add_filesize.php +++ b/application/migrations/004_add_filesize.php @@ -5,18 +5,35 @@ class Migration_Add_filesize extends CI_Migration { public function up() { - $this->db->query(" - ALTER TABLE `files` - ADD `filesize` INT UNSIGNED NOT NULL - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + ALTER TABLE "files" + ADD "filesize" integer NOT NULL + '); + } + else + { + $this->db->query(" + ALTER TABLE `files` + ADD `filesize` INT UNSIGNED NOT NULL + "); + } } public function down() { - $this->db->query(" - ALTER TABLE `files` - DROP `filesize` - "); - + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + ALTER TABLE "files" DROP "filesize" + '); + } + else + { + $this->db->query(' + ALTER TABLE `files` DROP `filesize` + '); + } } } diff --git a/application/migrations/005_drop_file_password.php b/application/migrations/005_drop_file_password.php index bf03490a8..2be20e550 100644 --- a/application/migrations/005_drop_file_password.php +++ b/application/migrations/005_drop_file_password.php @@ -5,17 +5,31 @@ class Migration_Drop_file_password extends CI_Migration { public function up() { - $this->db->query(" - ALTER TABLE `files` - DROP `password`; - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query('ALTER TABLE "files" DROP "password"'); + } + else + { + $this->db->query("ALTER TABLE `files` DROP `password`;"); + } } public function down() { - $this->db->query(" - ALTER TABLE `files` - ADD `password` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL; - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + ALTER TABLE "files" + ADD "password" character varying(40) DEFAULT NULL + '); + } + else + { + $this->db->query(" + ALTER TABLE `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 ea5e3ebc0..a7ad236f3 100644 --- a/application/migrations/006_add_username_index.php +++ b/application/migrations/006_add_username_index.php @@ -5,17 +5,33 @@ class Migration_Add_username_index extends CI_Migration { public function up() { - $this->db->query(" - ALTER TABLE `users` - ADD UNIQUE `username` (`username`); - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + CREATE UNIQUE INDEX "users_username_idx" ON "users" ("username") + '); + } + else + { + $this->db->query(" + ALTER TABLE `users` + ADD UNIQUE `username` (`username`); + "); + } } public function down() { - $this->db->query(" - ALTER TABLE `users` - DROP INDEX `username`; - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query('DROP INDEX "users_username_idx"'); + } + else + { + $this->db->query(" + ALTER TABLE `users` + DROP INDEX `username`; + "); + } } } diff --git a/application/migrations/007_repurpose_invitations.php b/application/migrations/007_repurpose_invitations.php index d586c2829..8f83ff7c9 100644 --- a/application/migrations/007_repurpose_invitations.php +++ b/application/migrations/007_repurpose_invitations.php @@ -5,33 +5,62 @@ class Migration_Repurpose_invitations extends CI_Migration { public function up() { - $this->db->query(" - ALTER TABLE `invitations` - ADD `action` VARCHAR(255) NOT NULL, - ADD `data` TEXT NULL, - ADD INDEX `action` (`action`); - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + ALTER TABLE "invitations" + ADD "action" character varying(255) NOT NULL, + ADD "data" TEXT NULL; + CREATE INDEX "invitations_action_idx" ON invitations ("action"); + '); - $this->db->query(" - UPDATE `invitations` SET `action` = 'invitation' WHERE `action` = ''; - "); + $this->db->query(' + UPDATE "invitations" SET "action" = \'invitation\' WHERE "action" = \'\' + '); - $this->db->query(" - ALTER TABLE `invitations` RENAME `actions`; - "); + $this->db->query(' + ALTER TABLE "invitations" RENAME TO "actions"; + '); + } + else + { + $this->db->query(" + ALTER TABLE `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(" + ALTER TABLE `invitations` RENAME `actions`; + "); + } } public function down() { - $this->db->query(" - ALTER TABLE `actions` RENAME `invitations`; - "); - - $this->db->query(" - ALTER TABLE `invitations` - DROP `action`, - DROP `data`; - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query('ALTER TABLE "actions" RENAME TO "invitations"'); + $this->db->query(' + ALTER TABLE "invitations" + DROP "action", + DROP "data"; + '); + } + else + { + $this->db->query('ALTER TABLE `actions` RENAME `invitations`'); + $this->db->query(' + ALTER TABLE `invitations` + DROP `action`, + DROP `data`; + '); + } + } } diff --git a/application/migrations/008_add_profiles.php b/application/migrations/008_add_profiles.php index 3fea33c08..1e55c7d14 100644 --- a/application/migrations/008_add_profiles.php +++ b/application/migrations/008_add_profiles.php @@ -5,27 +5,55 @@ class Migration_Add_profiles extends CI_Migration { public function up() { - $this->db->query(" - CREATE TABLE `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 - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + CREATE TABLE "profiles" ( + "user" integer NOT NULL, + "upload_id_limits" varchar(255) NOT NULL, + PRIMARY KEY ("user") + ) + '); - $this->db->query(" - ALTER TABLE `files` CHANGE `id` `id` VARCHAR( 255 ); - "); + $this->db->query(' + ALTER TABLE "files" ALTER COLUMN "id" TYPE varchar(255); + '); + } + else + { + $this->db->query(" + CREATE TABLE `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 ); + "); + } } public function down() { - $this->db->query(" - DROP TABLE `profiles`; - "); - $this->db->query(" - ALTER TABLE `files` CHANGE `id` `id` VARCHAR( 6 ); - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + DROP TABLE "profiles"; + '); + $this->db->query(' + ALTER TABLE "files" ALTER COLUMN "id" TYPE varchar(6); + '); + } + else + { + $this->db->query(" + DROP TABLE `profiles`; + "); + $this->db->query(" + ALTER TABLE `files` CHANGE `id` `id` VARCHAR( 6 ); + "); + } } } diff --git a/application/migrations/009_add_apikeys.php b/application/migrations/009_add_apikeys.php index 8e88260a8..7b1b5aa58 100644 --- a/application/migrations/009_add_apikeys.php +++ b/application/migrations/009_add_apikeys.php @@ -5,16 +5,32 @@ class Migration_Add_apikeys extends CI_Migration { public function up() { - $this->db->query(" - CREATE TABLE `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 - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + CREATE TABLE "apikeys" ( + "key" varchar(64) NOT NULL, + "user" integer NOT NULL, + "created" integer NOT NULL, + "comment" varchar(255) NOT NULL, + PRIMARY KEY ("key") + ); + CREATE INDEX "apikeys_user_idx" ON "apikeys" ("user"); + '); + } + else + { + $this->db->query(" + CREATE TABLE `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() diff --git a/application/migrations/010_files_innodb.php b/application/migrations/010_files_innodb.php index b32f94724..f965344d2 100644 --- a/application/migrations/010_files_innodb.php +++ b/application/migrations/010_files_innodb.php @@ -5,9 +5,12 @@ class Migration_files_innodb extends CI_Migration { public function up() { - $this->db->query(" - ALTER TABLE `files` ENGINE = InnoDB; - "); + if ($this->db->dbdriver != 'postgre') + { + $this->db->query(" + ALTER TABLE `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 index e0f39317b..29a2f769b 100644 --- a/application/migrations/011_apikeys_add_access_level.php +++ b/application/migrations/011_apikeys_add_access_level.php @@ -5,15 +5,33 @@ class Migration_apikeys_add_access_level extends CI_Migration { public function up() { - $this->db->query(" - alter table `apikeys` add `access_level` varchar(255) default 'apikey'; - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + alter table "apikeys" add "access_level" varchar(255) default \'apikey\' + '); + } + else + { + $this->db->query(" + alter table `apikeys` add `access_level` varchar(255) default 'apikey'; + "); + } } public function down() { - $this->db->query(" - alter table `apikeys` drop `access_level`; - "); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + alter table "apikeys" drop "access_level" + '); + } + else + { + $this->db->query(' + alter table `apikeys` drop `access_level` + '); + } } } diff --git a/application/migrations/012_add_constraints.php b/application/migrations/012_add_constraints.php index 2b0764fb0..04c0fb9ca 100644 --- a/application/migrations/012_add_constraints.php +++ b/application/migrations/012_add_constraints.php @@ -5,12 +5,27 @@ class Migration_add_constraints extends CI_Migration { public function up() { - $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;"); + 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 "users" + ADD CONSTRAINT "referrer_user_fkey" FOREIGN KEY ("referrer") + REFERENCES "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;"); + } } public function down() diff --git a/application/migrations/013_add_multipaste.php b/application/migrations/013_add_multipaste.php index edb4a0748..3b97f6986 100644 --- a/application/migrations/013_add_multipaste.php +++ b/application/migrations/013_add_multipaste.php @@ -5,28 +5,63 @@ class Migration_add_multipaste extends CI_Migration { public function up() { - $this->db->query(' - CREATE TABLE `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;'); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + CREATE TABLE "multipaste" ( + "url_id" varchar(255) NOT NULL, + "multipaste_id" serial, + "user_id" integer NOT NULL, + "date" integer NOT NULL, + PRIMARY KEY ("url_id") + ); + CREATE INDEX "multipaste_user_idx" ON "multipaste" ("user_id"); + CREATE UNIQUE INDEX "multipaste_id_idx" ON "multipaste" ("multipaste_id"); + '); - $this->db->query(' - CREATE TABLE `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 - ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;'); + $this->db->query(' + CREATE TABLE "multipaste_file_map" ( + "multipaste_id" integer NOT NULL, + "file_url_id" varchar(255) NOT NULL, + "sort_order" serial, + PRIMARY KEY ("sort_order") + ); + CREATE UNIQUE INDEX "multipaste_file_map_idx" + ON "multipaste_file_map" ("multipaste_id", "file_url_id"); + CREATE INDEX "multipaste_file_map_file_idx" ON "multipaste_file_map" ("file_url_id"); + ALTER TABLE "multipaste_file_map" + ADD CONSTRAINT "multipaste_file_map_id_fkey" FOREIGN KEY ("multipaste_id") + REFERENCES "multipaste"("multipaste_id") ON DELETE CASCADE ON UPDATE CASCADE; + ALTER TABLE "multipaste_file_map" + ADD CONSTRAINT "multipaste_file_map_file_id_fkey" FOREIGN KEY ("file_url_id") + REFERENCES "files"("id") ON DELETE CASCADE ON UPDATE CASCADE + '); + } + else + { + $this->db->query(' + CREATE TABLE `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 `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 + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;'); + } } public function down() -- cgit v1.2.3-24-g4f1b From fe7f15dbfb8020daf96110e86e359ec01558fcb8 Mon Sep 17 00:00:00 2001 From: Rafael Bodill Date: Thu, 18 Sep 2014 22:47:21 +0300 Subject: Correcting bracket style for 'if' --- application/migrations/001_add_files.php | 3 +-- application/migrations/002_add_users.php | 16 ++++++---------- application/migrations/003_add_referrers.php | 13 +++++-------- application/migrations/004_add_filesize.php | 14 ++++---------- application/migrations/005_drop_file_password.php | 14 ++++---------- application/migrations/006_add_username_index.php | 14 ++++---------- application/migrations/007_repurpose_invitations.php | 15 +++++++-------- application/migrations/008_add_profiles.php | 18 ++++++++---------- application/migrations/009_add_apikeys.php | 9 ++++----- application/migrations/010_files_innodb.php | 3 +-- .../migrations/011_apikeys_add_access_level.php | 14 ++++---------- application/migrations/012_add_constraints.php | 9 ++++----- application/migrations/013_add_multipaste.php | 9 ++++----- 13 files changed, 56 insertions(+), 95 deletions(-) diff --git a/application/migrations/001_add_files.php b/application/migrations/001_add_files.php index c5e4e7dfe..8af202285 100644 --- a/application/migrations/001_add_files.php +++ b/application/migrations/001_add_files.php @@ -6,8 +6,7 @@ class Migration_Add_files extends CI_Migration { public function up() { // Set database engine for MySQL drivers - if (strpos($this->db->dbdriver, 'mysql') !== FALSE) - { + if (strpos($this->db->dbdriver, 'mysql') !== FALSE) { $this->db->query('SET storage_engine=MYISAM'); } diff --git a/application/migrations/002_add_users.php b/application/migrations/002_add_users.php index d74402263..322415d9b 100644 --- a/application/migrations/002_add_users.php +++ b/application/migrations/002_add_users.php @@ -5,8 +5,7 @@ class Migration_Add_users extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' CREATE TABLE IF NOT EXISTS "users" ( "id" serial PRIMARY KEY, @@ -32,9 +31,9 @@ class Migration_Add_users extends CI_Migration { ALTER TABLE "files" ADD "user" integer NOT NULL DEFAULT 0; CREATE INDEX "user_idx" ON "files" ("user"); '); - } - else - { + + } else { + $this->db->query(" CREATE TABLE IF NOT EXISTS `users` ( `id` int(8) UNSIGNED NOT NULL AUTO_INCREMENT, @@ -69,12 +68,9 @@ class Migration_Add_users extends CI_Migration { { $this->dbforge->drop_table('users'); $this->dbforge->drop_table('ci_sessions'); - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query('ALTER TABLE "files" DROP "user"'); - } - else - { + } else { $this->db->query('ALTER TABLE `files` DROP `user`'); } } diff --git a/application/migrations/003_add_referrers.php b/application/migrations/003_add_referrers.php index 1f6b966e3..9ca167eab 100644 --- a/application/migrations/003_add_referrers.php +++ b/application/migrations/003_add_referrers.php @@ -5,8 +5,7 @@ class Migration_Add_referrers extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' CREATE TABLE "invitations" ( "user" integer NOT NULL, @@ -21,9 +20,9 @@ class Migration_Add_referrers extends CI_Migration { ALTER TABLE "users" ADD "referrer" integer NOT NULL DEFAULT 0 '); - } - else - { + + } else { + $this->db->query(" CREATE TABLE `invitations` ( `user` int(8) unsigned NOT NULL, @@ -48,9 +47,7 @@ class Migration_Add_referrers extends CI_Migration { $this->db->query(' ALTER TABLE "users" DROP "referrer" '); - } - else - { + } else { $this->db->query(' ALTER TABLE `users` DROP `referrer` '); diff --git a/application/migrations/004_add_filesize.php b/application/migrations/004_add_filesize.php index 7e5355c14..f2367937a 100644 --- a/application/migrations/004_add_filesize.php +++ b/application/migrations/004_add_filesize.php @@ -5,15 +5,12 @@ class Migration_Add_filesize extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' ALTER TABLE "files" ADD "filesize" integer NOT NULL '); - } - else - { + } else { $this->db->query(" ALTER TABLE `files` ADD `filesize` INT UNSIGNED NOT NULL @@ -23,14 +20,11 @@ class Migration_Add_filesize extends CI_Migration { public function down() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' ALTER TABLE "files" DROP "filesize" '); - } - else - { + } else { $this->db->query(' ALTER TABLE `files` DROP `filesize` '); diff --git a/application/migrations/005_drop_file_password.php b/application/migrations/005_drop_file_password.php index 2be20e550..e8b7f8952 100644 --- a/application/migrations/005_drop_file_password.php +++ b/application/migrations/005_drop_file_password.php @@ -5,27 +5,21 @@ class Migration_Drop_file_password extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query('ALTER TABLE "files" DROP "password"'); - } - else - { + } else { $this->db->query("ALTER TABLE `files` DROP `password`;"); } } public function down() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' ALTER TABLE "files" ADD "password" character varying(40) DEFAULT NULL '); - } - else - { + } else { $this->db->query(" ALTER TABLE `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 a7ad236f3..5b8c3584f 100644 --- a/application/migrations/006_add_username_index.php +++ b/application/migrations/006_add_username_index.php @@ -5,14 +5,11 @@ class Migration_Add_username_index extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' CREATE UNIQUE INDEX "users_username_idx" ON "users" ("username") '); - } - else - { + } else { $this->db->query(" ALTER TABLE `users` ADD UNIQUE `username` (`username`); @@ -22,12 +19,9 @@ class Migration_Add_username_index extends CI_Migration { public function down() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query('DROP INDEX "users_username_idx"'); - } - else - { + } else { $this->db->query(" ALTER TABLE `users` DROP INDEX `username`; diff --git a/application/migrations/007_repurpose_invitations.php b/application/migrations/007_repurpose_invitations.php index 8f83ff7c9..024b62984 100644 --- a/application/migrations/007_repurpose_invitations.php +++ b/application/migrations/007_repurpose_invitations.php @@ -5,8 +5,7 @@ class Migration_Repurpose_invitations extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' ALTER TABLE "invitations" ADD "action" character varying(255) NOT NULL, @@ -21,9 +20,9 @@ class Migration_Repurpose_invitations extends CI_Migration { $this->db->query(' ALTER TABLE "invitations" RENAME TO "actions"; '); - } - else - { + + } else { + $this->db->query(" ALTER TABLE `invitations` ADD `action` VARCHAR(255) NOT NULL, @@ -51,9 +50,9 @@ class Migration_Repurpose_invitations extends CI_Migration { DROP "action", DROP "data"; '); - } - else - { + + } else { + $this->db->query('ALTER TABLE `actions` RENAME `invitations`'); $this->db->query(' ALTER TABLE `invitations` diff --git a/application/migrations/008_add_profiles.php b/application/migrations/008_add_profiles.php index 1e55c7d14..9958fb03e 100644 --- a/application/migrations/008_add_profiles.php +++ b/application/migrations/008_add_profiles.php @@ -5,8 +5,7 @@ class Migration_Add_profiles extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' CREATE TABLE "profiles" ( "user" integer NOT NULL, @@ -18,9 +17,9 @@ class Migration_Add_profiles extends CI_Migration { $this->db->query(' ALTER TABLE "files" ALTER COLUMN "id" TYPE varchar(255); '); - } - else - { + + } else { + $this->db->query(" CREATE TABLE `profiles` ( `user` int(8) unsigned NOT NULL, @@ -37,17 +36,16 @@ class Migration_Add_profiles extends CI_Migration { public function down() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' DROP TABLE "profiles"; '); $this->db->query(' ALTER TABLE "files" ALTER COLUMN "id" TYPE varchar(6); '); - } - else - { + + } else { + $this->db->query(" DROP TABLE `profiles`; "); diff --git a/application/migrations/009_add_apikeys.php b/application/migrations/009_add_apikeys.php index 7b1b5aa58..bdc4dd07d 100644 --- a/application/migrations/009_add_apikeys.php +++ b/application/migrations/009_add_apikeys.php @@ -5,8 +5,7 @@ class Migration_Add_apikeys extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' CREATE TABLE "apikeys" ( "key" varchar(64) NOT NULL, @@ -17,9 +16,9 @@ class Migration_Add_apikeys extends CI_Migration { ); CREATE INDEX "apikeys_user_idx" ON "apikeys" ("user"); '); - } - else - { + + } else { + $this->db->query(" CREATE TABLE `apikeys` ( `key` varchar(64) COLLATE utf8_bin NOT NULL, diff --git a/application/migrations/010_files_innodb.php b/application/migrations/010_files_innodb.php index f965344d2..318314bd3 100644 --- a/application/migrations/010_files_innodb.php +++ b/application/migrations/010_files_innodb.php @@ -5,8 +5,7 @@ class Migration_files_innodb extends CI_Migration { public function up() { - if ($this->db->dbdriver != 'postgre') - { + if ($this->db->dbdriver != 'postgre') { $this->db->query(" ALTER TABLE `files` ENGINE = InnoDB; "); diff --git a/application/migrations/011_apikeys_add_access_level.php b/application/migrations/011_apikeys_add_access_level.php index 29a2f769b..2fdbc7271 100644 --- a/application/migrations/011_apikeys_add_access_level.php +++ b/application/migrations/011_apikeys_add_access_level.php @@ -5,14 +5,11 @@ class Migration_apikeys_add_access_level extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' alter table "apikeys" add "access_level" varchar(255) default \'apikey\' '); - } - else - { + } else { $this->db->query(" alter table `apikeys` add `access_level` varchar(255) default 'apikey'; "); @@ -21,14 +18,11 @@ class Migration_apikeys_add_access_level extends CI_Migration { public function down() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' alter table "apikeys" drop "access_level" '); - } - else - { + } else { $this->db->query(' alter table `apikeys` drop `access_level` '); diff --git a/application/migrations/012_add_constraints.php b/application/migrations/012_add_constraints.php index 04c0fb9ca..1ed4abf08 100644 --- a/application/migrations/012_add_constraints.php +++ b/application/migrations/012_add_constraints.php @@ -5,8 +5,7 @@ class Migration_add_constraints extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + 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")'); @@ -16,9 +15,9 @@ class Migration_add_constraints extends CI_Migration { ADD CONSTRAINT "referrer_user_fkey" FOREIGN KEY ("referrer") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE RESTRICT '); - } - else - { + + } else { + $this->db->query("ALTER TABLE `users` ADD INDEX(`referrer`);"); $this->db->query("ALTER TABLE `users` CHANGE `referrer` `referrer` INT(8) UNSIGNED NULL;"); diff --git a/application/migrations/013_add_multipaste.php b/application/migrations/013_add_multipaste.php index 3b97f6986..ef29effa9 100644 --- a/application/migrations/013_add_multipaste.php +++ b/application/migrations/013_add_multipaste.php @@ -5,8 +5,7 @@ class Migration_add_multipaste extends CI_Migration { public function up() { - if ($this->db->dbdriver == 'postgre') - { + if ($this->db->dbdriver == 'postgre') { $this->db->query(' CREATE TABLE "multipaste" ( "url_id" varchar(255) NOT NULL, @@ -36,9 +35,9 @@ class Migration_add_multipaste extends CI_Migration { ADD CONSTRAINT "multipaste_file_map_file_id_fkey" FOREIGN KEY ("file_url_id") REFERENCES "files"("id") ON DELETE CASCADE ON UPDATE CASCADE '); - } - else - { + + } else { + $this->db->query(' CREATE TABLE `multipaste` ( `url_id` varchar(255) NOT NULL, -- cgit v1.2.3-24-g4f1b From b8fa66e0d874bc5aa6ee719f6067b5365661833a Mon Sep 17 00:00:00 2001 From: Rafael Bodill Date: Fri, 19 Sep 2014 15:43:51 +0300 Subject: No column based encoding. Reverting dbforge migration --- application/migrations/001_add_files.php | 46 +++++++++++++++++++++----------- 1 file changed, 31 insertions(+), 15 deletions(-) diff --git a/application/migrations/001_add_files.php b/application/migrations/001_add_files.php index 8af202285..70efbb520 100644 --- a/application/migrations/001_add_files.php +++ b/application/migrations/001_add_files.php @@ -5,22 +5,38 @@ class Migration_Add_files extends CI_Migration { public function up() { - // Set database engine for MySQL drivers - if (strpos($this->db->dbdriver, 'mysql') !== FALSE) { - $this->db->query('SET storage_engine=MYISAM'); + if ($this->db->dbdriver == 'postgre') + { + $this->db->query(' + CREATE TABLE IF NOT EXISTS "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 "files_date_idx" ON files ("date"); + CREATE INDEX "files_hash_id_idx" ON files ("hash", "id"); + '); + } + else + { + $this->db->query(" + CREATE TABLE IF NOT EXISTS `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; + "); } - - $this->dbforge->add_field([ - 'hash' => [ 'type' => 'varchar', 'constraint' => 32, 'null' => FALSE ], - 'id' => [ 'type' => 'varchar', 'constraint' => 6, 'null' => FALSE ], - 'filename' => [ 'type' => 'varchar', 'constraint' => 256, 'null' => FALSE ], - 'password' => [ 'type' => 'varchar', 'constraint' => 40, 'null' => TRUE ], - 'date' => [ 'type' => 'integer', 'unsigned' => TRUE, 'null' => FALSE ], - 'mimetype' => [ 'type' => 'varchar', 'constraint' => 255, 'null' => FALSE ], - ]); - $this->dbforge->add_key('id', TRUE); - $this->dbforge->add_key([ 'hash', 'id' ]); - $this->dbforge->create_table('files', TRUE); } public function down() -- cgit v1.2.3-24-g4f1b From 5170adf992ba84fcced8bc271e603d1440f29509 Mon Sep 17 00:00:00 2001 From: Rafael Bodill Date: Fri, 19 Sep 2014 16:06:47 +0300 Subject: apikeys.created should be timestamp with default now() --- application/migrations/009_add_apikeys.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/application/migrations/009_add_apikeys.php b/application/migrations/009_add_apikeys.php index bdc4dd07d..8f2882e49 100644 --- a/application/migrations/009_add_apikeys.php +++ b/application/migrations/009_add_apikeys.php @@ -10,7 +10,7 @@ class Migration_Add_apikeys extends CI_Migration { CREATE TABLE "apikeys" ( "key" varchar(64) NOT NULL, "user" integer NOT NULL, - "created" integer NOT NULL, + "created" timestamp WITH TIME ZONE NOT NULL DEFAULT NOW(), "comment" varchar(255) NOT NULL, PRIMARY KEY ("key") ); -- cgit v1.2.3-24-g4f1b From 75b0a939c7ce24014a8db95a3355d2a7ffdfe3a9 Mon Sep 17 00:00:00 2001 From: Rafael Bodill Date: Fri, 19 Sep 2014 18:39:28 +0300 Subject: Optimizing multipaste tables --- application/migrations/013_add_multipaste.php | 24 +++++++----------------- 1 file changed, 7 insertions(+), 17 deletions(-) diff --git a/application/migrations/013_add_multipaste.php b/application/migrations/013_add_multipaste.php index ef29effa9..96092b4ee 100644 --- a/application/migrations/013_add_multipaste.php +++ b/application/migrations/013_add_multipaste.php @@ -8,32 +8,22 @@ class Migration_add_multipaste extends CI_Migration { if ($this->db->dbdriver == 'postgre') { $this->db->query(' CREATE TABLE "multipaste" ( - "url_id" varchar(255) NOT NULL, - "multipaste_id" serial, + "url_id" varchar(255) NOT NULL PRIMARY KEY, + "multipaste_id" serial UNIQUE, "user_id" integer NOT NULL, - "date" integer NOT NULL, - PRIMARY KEY ("url_id") + "date" integer NOT NULL ); CREATE INDEX "multipaste_user_idx" ON "multipaste" ("user_id"); - CREATE UNIQUE INDEX "multipaste_id_idx" ON "multipaste" ("multipaste_id"); '); $this->db->query(' CREATE TABLE "multipaste_file_map" ( - "multipaste_id" integer NOT NULL, - "file_url_id" varchar(255) NOT NULL, - "sort_order" serial, - PRIMARY KEY ("sort_order") + "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, + "sort_order" serial PRIMARY KEY, + UNIQUE ("multipaste_id", "file_url_id") ); - CREATE UNIQUE INDEX "multipaste_file_map_idx" - ON "multipaste_file_map" ("multipaste_id", "file_url_id"); CREATE INDEX "multipaste_file_map_file_idx" ON "multipaste_file_map" ("file_url_id"); - ALTER TABLE "multipaste_file_map" - ADD CONSTRAINT "multipaste_file_map_id_fkey" FOREIGN KEY ("multipaste_id") - REFERENCES "multipaste"("multipaste_id") ON DELETE CASCADE ON UPDATE CASCADE; - ALTER TABLE "multipaste_file_map" - ADD CONSTRAINT "multipaste_file_map_file_id_fkey" FOREIGN KEY ("file_url_id") - REFERENCES "files"("id") ON DELETE CASCADE ON UPDATE CASCADE '); } else { -- cgit v1.2.3-24-g4f1b