summaryrefslogtreecommitdiffstats
path: root/application
diff options
context:
space:
mode:
Diffstat (limited to 'application')
-rw-r--r--application/config/migration.php2
-rw-r--r--application/controllers/file.php6
-rw-r--r--application/controllers/tools.php9
-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
-rw-r--r--application/models/mmultipaste.php12
-rw-r--r--application/service/files.php10
15 files changed, 155 insertions, 76 deletions
diff --git a/application/config/migration.php b/application/config/migration.php
index 7a5b5f970..d5cce76f9 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'] = 15;
+$config['migration_version'] = 16;
/*
diff --git a/application/controllers/file.php b/application/controllers/file.php
index a78f89bb0..f484c5260 100644
--- a/application/controllers/file.php
+++ b/application/controllers/file.php
@@ -684,10 +684,10 @@ class File extends MY_Controller {
->from('files')
->join('file_storage', 'file_storage.id = files.file_storage_id')
->where('
- (user = '.$this->db->escape($user).')
+ (files.user = '.$this->db->escape($user).')
AND (
- mimetype LIKE "image%"
- OR mimetype IN ("application/pdf")
+ mimetype LIKE \'image%\'
+ OR mimetype IN (\'application/pdf\')
)', null, false)
->order_by('date', 'desc')
->get()->result_array();
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/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");
+ }
+}
diff --git a/application/models/mmultipaste.php b/application/models/mmultipaste.php
index f80105ec9..0185e8c7f 100644
--- a/application/models/mmultipaste.php
+++ b/application/models/mmultipaste.php
@@ -65,7 +65,7 @@ class Mmultipaste extends CI_Model {
$sql = '
SELECT url_id
- FROM `'.$this->db->dbprefix.'multipaste`
+ FROM '.$this->db->dbprefix.'multipaste
WHERE url_id = ?
LIMIT 1';
$query = $this->db->query($sql, array($id));
@@ -124,7 +124,7 @@ class Mmultipaste extends CI_Model {
{
return $this->db->query("
SELECT user_id
- FROM `".$this->db->dbprefix."multipaste`
+ FROM ".$this->db->dbprefix."multipaste
WHERE url_id = ?
", array($id))->row_array()["user_id"];
}
@@ -133,7 +133,7 @@ class Mmultipaste extends CI_Model {
{
return $this->db->query("
SELECT url_id, user_id, date
- FROM `".$this->db->dbprefix."multipaste`
+ FROM ".$this->db->dbprefix."multipaste
WHERE url_id = ?
", array($id))->row_array();
}
@@ -144,8 +144,8 @@ class Mmultipaste extends CI_Model {
$query = $this->db->query("
SELECT mfm.file_url_id
- FROM `".$this->db->dbprefix."multipaste_file_map` mfm
- JOIN `".$this->db->dbprefix."multipaste` m ON m.multipaste_id = mfm.multipaste_id
+ FROM ".$this->db->dbprefix."multipaste_file_map mfm
+ JOIN ".$this->db->dbprefix."multipaste m ON m.multipaste_id = mfm.multipaste_id
WHERE m.url_id = ?
ORDER BY mfm.sort_order
", array($url_id))->result_array();
@@ -162,7 +162,7 @@ class Mmultipaste extends CI_Model {
{
$query = $this->db->query("
SELECT multipaste_id
- FROM `".$this->db->dbprefix."multipaste`
+ FROM ".$this->db->dbprefix."multipaste
WHERE url_id = ?
", array($url_id));
diff --git a/application/service/files.php b/application/service/files.php
index adc62c7dc..c65660b75 100644
--- a/application/service/files.php
+++ b/application/service/files.php
@@ -31,12 +31,12 @@ class files {
}
$total_size = $CI->db->query("
- SELECT coalesce(sum(filesize), 0) sum
+ SELECT coalesce(sum(sub.filesize), 0) sum
FROM (
- SELECT DISTINCT `".$CI->db->dbprefix."file_storage`.id, filesize
- FROM `".$CI->db->dbprefix."file_storage`
- JOIN `".$CI->db->dbprefix."files` ON `".$CI->db->dbprefix."file_storage`.id = `".$CI->db->dbprefix."files`.file_storage_id
- WHERE user = ?
+ SELECT DISTINCT fs.id, filesize
+ FROM ".$CI->db->dbprefix."file_storage fs
+ JOIN ".$CI->db->dbprefix."files f ON fs.id = f.file_storage_id
+ WHERE f.user = ?
) sub
", array($user))->row_array();
generated by cgit v1.2.3-24-g4f1b (git 2.32.0) at 2024-11-09 18:54:46 +0100