Implement migration 14 for postgres

Signed-off-by: Florian Pritz <bluewind@xinu.at>

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";
 		}
 	}