From d52fe43782b23f2de62d0ccf0535586df0560b19 Mon Sep 17 00:00:00 2001 From: Florian Pritz Date: Wed, 5 Aug 2015 00:24:22 +0200 Subject: Fix missing prefix in migration 7 Signed-off-by: Florian Pritz --- application/migrations/007_repurpose_invitations.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'application') diff --git a/application/migrations/007_repurpose_invitations.php b/application/migrations/007_repurpose_invitations.php index ed9b136a0..ecdef305d 100644 --- a/application/migrations/007_repurpose_invitations.php +++ b/application/migrations/007_repurpose_invitations.php @@ -20,7 +20,7 @@ class Migration_Repurpose_invitations extends CI_Migration { '); $this->db->query(' - ALTER TABLE "'.$prefix.'invitations" RENAME TO "actions"; + ALTER TABLE "'.$prefix.'invitations" RENAME TO '.$prefix.'actions; '); } else { -- cgit v1.2.3-24-g4f1b From 49c8b94e5a2839da7fb9c370ce199b9c06b4a6cf Mon Sep 17 00:00:00 2001 From: Florian Pritz Date: Sat, 18 Jul 2015 16:25:34 +0200 Subject: Implement migration 14 for postgres Signed-off-by: Florian Pritz --- application/controllers/tools.php | 9 ++- .../migrations/014_deduplicate_file_storage.php | 92 ++++++++++++++++------ .../015_actually_deduplicate_file_storage.php | 49 ++++++------ 3 files changed, 101 insertions(+), 49 deletions(-) (limited to 'application') diff --git a/application/controllers/tools.php b/application/controllers/tools.php index f0d8ce6f9..664a9e324 100644 --- a/application/controllers/tools.php +++ b/application/controllers/tools.php @@ -59,9 +59,14 @@ class Tools extends MY_Controller { return; } - $this->db->query('SET FOREIGN_KEY_CHECKS = 0'); + + if ($this->db->dbdriver !== 'postgre') { + $this->db->query('SET FOREIGN_KEY_CHECKS = 0'); + } $this->db->query('DROP TABLE '.implode(", ", $tables_to_drop)); - $this->db->query('SET FOREIGN_KEY_CHECKS = 1'); + if ($this->db->dbdriver !== 'postgre') { + $this->db->query('SET FOREIGN_KEY_CHECKS = 1'); + } } function test() diff --git a/application/migrations/014_deduplicate_file_storage.php b/application/migrations/014_deduplicate_file_storage.php index 133fbbab7..96c041c27 100644 --- a/application/migrations/014_deduplicate_file_storage.php +++ b/application/migrations/014_deduplicate_file_storage.php @@ -8,7 +8,48 @@ class Migration_deduplicate_file_storage extends CI_Migration { $prefix = $this->db->dbprefix; if ($this->db->dbdriver == 'postgre') { - throw new \exceptions\ApiException("migration/postgres/not-implemented", "migration 14 not yet implemented for postgres"); + $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` ( @@ -52,38 +93,45 @@ class Migration_deduplicate_file_storage extends CI_Migration { GROUP BY x.hash ) AND `'.$prefix.'files`.id IS NULL - '); + '); + } - $chunk = 500; - $total = $this->db->count_all("file_storage"); + $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(); + 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"; - } + 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"); } + + $this->dbforge->drop_column("files", "hash"); + $this->dbforge->drop_column("files", "mimetype"); + $this->dbforge->drop_column("files", "filesize"); } public function down() diff --git a/application/migrations/015_actually_deduplicate_file_storage.php b/application/migrations/015_actually_deduplicate_file_storage.php index 2fea26dab..267afc1fe 100644 --- a/application/migrations/015_actually_deduplicate_file_storage.php +++ b/application/migrations/015_actually_deduplicate_file_storage.php @@ -8,8 +8,7 @@ class Migration_actually_deduplicate_file_storage extends CI_Migration { $prefix = $this->db->dbprefix; if ($this->db->dbdriver == 'postgre') { - // likely no need for this migration since 14 won't be buggy - throw new \exceptions\ApiException("migration/postgres/not-implemented", "migration 15 not yet implemented for postgres"); + // no need for this query since 14 is not buggy } else { // XXX: This query also exists in migration 14 $this->db->query(' @@ -25,32 +24,32 @@ class Migration_actually_deduplicate_file_storage extends CI_Migration { GROUP BY x.hash ) AND `'.$prefix.'files`.id IS NULL - '); - - $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; - } + '); + } + + $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"; - } + 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"; } } -- cgit v1.2.3-24-g4f1b From 731dbac7d399928f4e1137031689796b0721c75e Mon Sep 17 00:00:00 2001 From: Florian Pritz Date: Sun, 20 Sep 2015 14:57:59 +0200 Subject: postgres: Fix multiple queries using MySQL syntax Signed-off-by: Florian Pritz --- application/models/mmultipaste.php | 12 ++++++------ application/service/files.php | 10 +++++----- 2 files changed, 11 insertions(+), 11 deletions(-) (limited to 'application') diff --git a/application/models/mmultipaste.php b/application/models/mmultipaste.php index f80105ec9..0185e8c7f 100644 --- a/application/models/mmultipaste.php +++ b/application/models/mmultipaste.php @@ -65,7 +65,7 @@ class Mmultipaste extends CI_Model { $sql = ' SELECT url_id - FROM `'.$this->db->dbprefix.'multipaste` + FROM '.$this->db->dbprefix.'multipaste WHERE url_id = ? LIMIT 1'; $query = $this->db->query($sql, array($id)); @@ -124,7 +124,7 @@ class Mmultipaste extends CI_Model { { return $this->db->query(" SELECT user_id - FROM `".$this->db->dbprefix."multipaste` + FROM ".$this->db->dbprefix."multipaste WHERE url_id = ? ", array($id))->row_array()["user_id"]; } @@ -133,7 +133,7 @@ class Mmultipaste extends CI_Model { { return $this->db->query(" SELECT url_id, user_id, date - FROM `".$this->db->dbprefix."multipaste` + FROM ".$this->db->dbprefix."multipaste WHERE url_id = ? ", array($id))->row_array(); } @@ -144,8 +144,8 @@ class Mmultipaste extends CI_Model { $query = $this->db->query(" SELECT mfm.file_url_id - FROM `".$this->db->dbprefix."multipaste_file_map` mfm - JOIN `".$this->db->dbprefix."multipaste` m ON m.multipaste_id = mfm.multipaste_id + FROM ".$this->db->dbprefix."multipaste_file_map mfm + JOIN ".$this->db->dbprefix."multipaste m ON m.multipaste_id = mfm.multipaste_id WHERE m.url_id = ? ORDER BY mfm.sort_order ", array($url_id))->result_array(); @@ -162,7 +162,7 @@ class Mmultipaste extends CI_Model { { $query = $this->db->query(" SELECT multipaste_id - FROM `".$this->db->dbprefix."multipaste` + FROM ".$this->db->dbprefix."multipaste WHERE url_id = ? ", array($url_id)); diff --git a/application/service/files.php b/application/service/files.php index adc62c7dc..c65660b75 100644 --- a/application/service/files.php +++ b/application/service/files.php @@ -31,12 +31,12 @@ class files { } $total_size = $CI->db->query(" - SELECT coalesce(sum(filesize), 0) sum + SELECT coalesce(sum(sub.filesize), 0) sum FROM ( - SELECT DISTINCT `".$CI->db->dbprefix."file_storage`.id, filesize - FROM `".$CI->db->dbprefix."file_storage` - JOIN `".$CI->db->dbprefix."files` ON `".$CI->db->dbprefix."file_storage`.id = `".$CI->db->dbprefix."files`.file_storage_id - WHERE user = ? + SELECT DISTINCT fs.id, filesize + FROM ".$CI->db->dbprefix."file_storage fs + JOIN ".$CI->db->dbprefix."files f ON fs.id = f.file_storage_id + WHERE f.user = ? ) sub ", array($user))->row_array(); -- cgit v1.2.3-24-g4f1b From ffacb290fa8a30e6f3e507bf6842b281efbcd558 Mon Sep 17 00:00:00 2001 From: Florian Pritz Date: Sun, 20 Sep 2015 15:36:22 +0200 Subject: postgres: Adapt MySQL query for thumbnail history Signed-off-by: Florian Pritz --- application/controllers/file.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'application') diff --git a/application/controllers/file.php b/application/controllers/file.php index a78f89bb0..f484c5260 100644 --- a/application/controllers/file.php +++ b/application/controllers/file.php @@ -684,10 +684,10 @@ class File extends MY_Controller { ->from('files') ->join('file_storage', 'file_storage.id = files.file_storage_id') ->where(' - (user = '.$this->db->escape($user).') + (files.user = '.$this->db->escape($user).') AND ( - mimetype LIKE "image%" - OR mimetype IN ("application/pdf") + mimetype LIKE \'image%\' + OR mimetype IN (\'application/pdf\') )', null, false) ->order_by('date', 'desc') ->get()->result_array(); -- cgit v1.2.3-24-g4f1b From 2e627d7a88aed82363a7ad48dab21b2b55797452 Mon Sep 17 00:00:00 2001 From: Florian Pritz Date: Sun, 20 Sep 2015 15:46:09 +0200 Subject: postgres: Add missing prefix to index creation Signed-off-by: Florian Pritz --- application/migrations/001_add_files.php | 4 ++-- application/migrations/002_add_users.php | 4 ++-- application/migrations/003_add_referrers.php | 4 ++-- application/migrations/006_add_username_index.php | 4 ++-- application/migrations/007_repurpose_invitations.php | 2 +- application/migrations/009_add_apikeys.php | 2 +- application/migrations/012_add_constraints.php | 2 +- 7 files changed, 11 insertions(+), 11 deletions(-) (limited to 'application') diff --git a/application/migrations/001_add_files.php b/application/migrations/001_add_files.php index dd37d08c3..7b1398e1c 100644 --- a/application/migrations/001_add_files.php +++ b/application/migrations/001_add_files.php @@ -18,8 +18,8 @@ class Migration_Add_files extends CI_Migration { "mimetype" varchar(255) NOT NULL, PRIMARY KEY ("id") ); - CREATE INDEX "files_date_idx" ON '.$prefix.'files ("date"); - CREATE INDEX "files_hash_id_idx" ON '.$prefix.'files ("hash", "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(' diff --git a/application/migrations/002_add_users.php b/application/migrations/002_add_users.php index 5ccef6669..454618e48 100644 --- a/application/migrations/002_add_users.php +++ b/application/migrations/002_add_users.php @@ -26,12 +26,12 @@ class Migration_Add_users extends CI_Migration { "user_data" text NOT NULL, PRIMARY KEY ("session_id") ); - CREATE INDEX "ci_sessions_last_activity_idx" ON "'.$prefix.'ci_sessions" ("last_activity"); + 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 "user_idx" ON "'.$prefix.'files" ("user"); + CREATE INDEX "'.$prefix.'user_idx" ON "'.$prefix.'files" ("user"); '); } else { diff --git a/application/migrations/003_add_referrers.php b/application/migrations/003_add_referrers.php index c504b5539..e6da1c0dd 100644 --- a/application/migrations/003_add_referrers.php +++ b/application/migrations/003_add_referrers.php @@ -15,8 +15,8 @@ class Migration_Add_referrers extends CI_Migration { "date" integer NOT NULL, PRIMARY KEY ("key") ); - CREATE INDEX "invitations_user_idx" ON "'.$prefix.'invitations" ("user"); - CREATE INDEX "invitations_date_idx" ON "'.$prefix.'invitations" ("date"); + 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" diff --git a/application/migrations/006_add_username_index.php b/application/migrations/006_add_username_index.php index 0e6dc7650..1633a95a9 100644 --- a/application/migrations/006_add_username_index.php +++ b/application/migrations/006_add_username_index.php @@ -9,7 +9,7 @@ class Migration_Add_username_index extends CI_Migration { if ($this->db->dbdriver == 'postgre') { $this->db->query(' - CREATE UNIQUE INDEX "users_username_idx" ON "'.$prefix.'users" ("username") + CREATE UNIQUE INDEX "'.$prefix.'users_username_idx" ON "'.$prefix.'users" ("username") '); } else { $this->db->query(' @@ -24,7 +24,7 @@ class Migration_Add_username_index extends CI_Migration { $prefix = $this->db->dbprefix; if ($this->db->dbdriver == 'postgre') { - $this->db->query('DROP INDEX "users_username_idx"'); + $this->db->query('DROP INDEX "'.$prefix.'users_username_idx"'); } else { $this->db->query(" ALTER TABLE `'.$prefix.'users` diff --git a/application/migrations/007_repurpose_invitations.php b/application/migrations/007_repurpose_invitations.php index ecdef305d..0bc39c64b 100644 --- a/application/migrations/007_repurpose_invitations.php +++ b/application/migrations/007_repurpose_invitations.php @@ -12,7 +12,7 @@ class Migration_Repurpose_invitations extends CI_Migration { ALTER TABLE "'.$prefix.'invitations" ADD "action" character varying(255) NOT NULL, ADD "data" TEXT NULL; - CREATE INDEX "invitations_action_idx" ON '.$prefix.'invitations ("action"); + CREATE INDEX "'.$prefix.'invitations_action_idx" ON '.$prefix.'invitations ("action"); '); $this->db->query(' diff --git a/application/migrations/009_add_apikeys.php b/application/migrations/009_add_apikeys.php index a5af809fc..e9dba4e41 100644 --- a/application/migrations/009_add_apikeys.php +++ b/application/migrations/009_add_apikeys.php @@ -16,7 +16,7 @@ class Migration_Add_apikeys extends CI_Migration { "comment" varchar(255) NOT NULL, PRIMARY KEY ("key") ); - CREATE INDEX "apikeys_user_idx" ON "'.$prefix.'apikeys" ("user"); + CREATE INDEX "'.$prefix.'apikeys_user_idx" ON "'.$prefix.'apikeys" ("user"); '); } else { diff --git a/application/migrations/012_add_constraints.php b/application/migrations/012_add_constraints.php index 40a4540f6..a2569d54d 100644 --- a/application/migrations/012_add_constraints.php +++ b/application/migrations/012_add_constraints.php @@ -10,7 +10,7 @@ class Migration_add_constraints extends CI_Migration { 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 "users_referrer_idx" ON "'.$prefix.'users" ("referrer")'); + $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" -- cgit v1.2.3-24-g4f1b From e79c1c45dcd23dde1f4b072993829cc008f4858b Mon Sep 17 00:00:00 2001 From: Florian Pritz Date: Sun, 20 Sep 2015 16:01:28 +0200 Subject: Migration 16: Increase length of ci_session.ip_address for IPv6 Reported-by: Michael Mueller Signed-off-by: Florian Pritz --- application/config/migration.php | 2 +- application/migrations/016_allow_ipv6_storage.php | 27 +++++++++++++++++++++++ 2 files changed, 28 insertions(+), 1 deletion(-) create mode 100644 application/migrations/016_allow_ipv6_storage.php (limited to 'application') diff --git a/application/config/migration.php b/application/config/migration.php index 7a5b5f970..d5cce76f9 100644 --- a/application/config/migration.php +++ b/application/config/migration.php @@ -21,7 +21,7 @@ $config['migration_enabled'] = true; | be upgraded / downgraded to. | */ -$config['migration_version'] = 15; +$config['migration_version'] = 16; /* 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 @@ +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"); + } +} -- cgit v1.2.3-24-g4f1b