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(-) 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