Remove unneeded duplicates in file_storage

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

3 files changed, 80 insertions, 1 deletions

diff --git a/application/config/migration.php b/application/config/migration.php
index 952fbf056..7a5b5f970 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'] = 14;
+$config['migration_version'] = 15;
 
 
 /*
diff --git a/application/migrations/014_deduplicate_file_storage.php b/application/migrations/014_deduplicate_file_storage.php
index bc4171d1d..a1ea4fab4 100644
--- a/application/migrations/014_deduplicate_file_storage.php
+++ b/application/migrations/014_deduplicate_file_storage.php
@@ -38,6 +38,22 @@ class Migration_deduplicate_file_storage extends CI_Migration {
 				SET f.file_storage_id = fs.id
 			');
 
+			// XXX: This query also exists in migration 15
+			$this->db->query('
+				DELETE file_storage
+				FROM file_storage
+				LEFT OUTER JOIN files ON files.file_storage_id = file_storage.id
+				WHERE file_storage.id NOT IN (
+					SELECT min(x.id)
+					FROM (
+						SELECT fs.id, fs.hash
+						FROM file_storage fs
+					) x
+					GROUP BY x.hash
+				)
+				AND files.id IS NULL
+			');
+
 			$chunk = 500;
 			$total = $this->db->count_all("file_storage");
 
@@ -53,6 +69,8 @@ class Migration_deduplicate_file_storage extends CI_Migration {
 					$new = $this->mfile->file($data_id);
 					if (file_exists($old)) {
 						rename($old, $new);
+					} else {
+						echo "Warning: no file found for $data_id. Skipping...\n";
 					}
 				}
 			}
diff --git a/application/migrations/015_actually_deduplicate_file_storage.php b/application/migrations/015_actually_deduplicate_file_storage.php
new file mode 100644
index 000000000..8f2a51c74
--- /dev/null
+++ b/application/migrations/015_actually_deduplicate_file_storage.php
@@ -0,0 +1,61 @@
+<?php
+defined('BASEPATH') OR exit('No direct script access allowed');
+
+class Migration_actually_deduplicate_file_storage extends CI_Migration {
+
+	public function up()
+	{
+		$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");
+		} else {
+			// XXX: This query also exists in migration 14
+			$this->db->query('
+				DELETE file_storage
+				FROM file_storage
+				LEFT OUTER JOIN files ON files.file_storage_id = file_storage.id
+				WHERE file_storage.id NOT IN (
+					SELECT min(x.id)
+					FROM (
+						SELECT fs.id, fs.hash
+						FROM file_storage fs
+					) x
+					GROUP BY x.hash
+				)
+				AND 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;
+					}
+				}
+			}
+
+			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";
+			}
+		}
+	}
+
+	public function down()
+	{
+		throw new \exceptions\ApiException("migration/downgrade-not-supported", "downgrade not supported");
+	}
+}