Merge branch 'postgres' into dev

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();