summaryrefslogtreecommitdiffstats
path: root/application/migrations
diff options
context:
space:
mode:
Diffstat (limited to 'application/migrations')
-rw-r--r--application/migrations/001_add_files.php4
-rw-r--r--application/migrations/002_add_users.php4
-rw-r--r--application/migrations/003_add_referrers.php4
-rw-r--r--application/migrations/006_add_username_index.php4
-rw-r--r--application/migrations/007_repurpose_invitations.php4
-rw-r--r--application/migrations/009_add_apikeys.php2
-rw-r--r--application/migrations/012_add_constraints.php2
-rw-r--r--application/migrations/014_deduplicate_file_storage.php92
-rw-r--r--application/migrations/015_actually_deduplicate_file_storage.php49
-rw-r--r--application/migrations/016_allow_ipv6_storage.php27
10 files changed, 133 insertions, 59 deletions
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 ed9b136a0..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('
@@ -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 {
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"
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";
}
}
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 @@
+<?php
+defined('BASEPATH') OR exit('No direct script access allowed');
+
+class Migration_allow_ipv6_storage extends CI_Migration {
+
+ public function up()
+ {
+ $prefix = $this->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");
+ }
+}
generated by cgit v1.2.3-24-g4f1b (git 2.32.0) at 2024-11-03 12:24:04 +0100