summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--application/controllers/tools.php9
-rw-r--r--application/migrations/014_deduplicate_file_storage.php92
-rw-r--r--application/migrations/015_actually_deduplicate_file_storage.php49
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";
}
}
generated by cgit v1.2.3-24-g4f1b (git 2.32.0) at 2024-11-18 21:04:23 +0100