From 33f542487a89da4a8edad934b82aae7484f70ca3 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Thu, 18 Sep 2014 22:02:26 +0300
Subject: Migrations support for PostgreSQL

---
 application/migrations/001_add_files.php           | 30 ++++---
 application/migrations/002_add_users.php           | 93 +++++++++++++++-------
 application/migrations/003_add_referrers.php       | 64 ++++++++++-----
 application/migrations/004_add_filesize.php        | 35 +++++---
 application/migrations/005_drop_file_password.php  | 30 +++++--
 application/migrations/006_add_username_index.php  | 32 ++++++--
 .../migrations/007_repurpose_invitations.php       | 71 ++++++++++++-----
 application/migrations/008_add_profiles.php        | 60 ++++++++++----
 application/migrations/009_add_apikeys.php         | 36 ++++++---
 application/migrations/010_files_innodb.php        |  9 ++-
 .../migrations/011_apikeys_add_access_level.php    | 30 +++++--
 application/migrations/012_add_constraints.php     | 27 +++++--
 application/migrations/013_add_multipaste.php      | 77 +++++++++++++-----
 13 files changed, 426 insertions(+), 168 deletions(-)

(limited to 'application')

diff --git a/application/migrations/001_add_files.php b/application/migrations/001_add_files.php
index f1f16ea3a..c5e4e7dfe 100644
--- a/application/migrations/001_add_files.php
+++ b/application/migrations/001_add_files.php
@@ -5,19 +5,23 @@ class Migration_Add_files extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			CREATE TABLE IF NOT EXISTS `files` (
-			  `hash` varchar(32) CHARACTER SET ascii NOT NULL,
-			  `id` varchar(6) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
-			  `filename` varchar(256) COLLATE utf8_bin NOT NULL,
-			  `password` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
-			  `date` int(11) unsigned NOT NULL,
-			  `mimetype` varchar(255) CHARACTER SET ascii NOT NULL,
-			  PRIMARY KEY (`id`),
-			  KEY `date` (`date`),
-			  KEY `hash` (`hash`,`id`)
-			) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-		");
+		// Set database engine for MySQL drivers
+		if (strpos($this->db->dbdriver, 'mysql') !== FALSE)
+		{
+			$this->db->query('SET storage_engine=MYISAM');
+		}
+
+		$this->dbforge->add_field([
+			'hash'     => [ 'type' => 'varchar', 'constraint' => 32, 'null' => FALSE ],
+			'id'       => [ 'type' => 'varchar', 'constraint' => 6, 'null' => FALSE ],
+			'filename' => [ 'type' => 'varchar', 'constraint' => 256, 'null' => FALSE ],
+			'password' => [ 'type' => 'varchar', 'constraint' => 40, 'null' => TRUE ],
+			'date'     => [ 'type' => 'integer', 'unsigned' => TRUE, 'null' => FALSE ],
+			'mimetype' => [ 'type' => 'varchar', 'constraint' => 255, 'null' => FALSE ],
+		]);
+		$this->dbforge->add_key('id', TRUE);
+		$this->dbforge->add_key([ 'hash', 'id' ]);
+		$this->dbforge->create_table('files', TRUE);
 	}
 
 	public function down()
diff --git a/application/migrations/002_add_users.php b/application/migrations/002_add_users.php
index 5675c77e9..d74402263 100644
--- a/application/migrations/002_add_users.php
+++ b/application/migrations/002_add_users.php
@@ -5,42 +5,77 @@ class Migration_Add_users extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			CREATE TABLE IF NOT EXISTS `users` (
-			  `id` int(8) UNSIGNED NOT NULL AUTO_INCREMENT,
-			  `username` varchar(32) COLLATE ascii_general_ci NOT NULL,
-			  `password` varchar(60) COLLATE ascii_general_ci NOT NULL,
-			  `email` varchar(255) COLLATE ascii_general_ci NOT NULL,
-			  PRIMARY KEY (`id`)
-			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				CREATE TABLE IF NOT EXISTS "users" (
+					"id" serial PRIMARY KEY,
+					"username" character varying(32) NOT NULL,
+					"password" character varying(60) NOT NULL,
+					"email" character varying(255) NOT NULL
+				)
+			');
 
-		$this->db->query("
-			CREATE TABLE IF NOT EXISTS `ci_sessions` (
-				`session_id` varchar(40) NOT NULL DEFAULT '0',
-				`ip_address` varchar(16) NOT NULL DEFAULT '0',
-				`user_agent` varchar(120) NOT NULL,
-				`last_activity` int(10) unsigned NOT NULL DEFAULT '0',
-				`user_data` text NOT NULL,
-				PRIMARY KEY (`session_id`),
-				KEY `last_activity_idx` (`last_activity`)
-			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-		");
+			$this->db->query('
+				CREATE TABLE IF NOT EXISTS "ci_sessions" (
+					"session_id" character varying(40) NOT NULL DEFAULT 0,
+					"ip_address" character varying(16) NOT NULL DEFAULT 0,
+					"user_agent" character varying(120) NOT NULL,
+					"last_activity" integer NOT NULL DEFAULT 0,
+					"user_data" text NOT NULL,
+					PRIMARY KEY ("session_id")
+				);
+				CREATE INDEX "ci_sessions_last_activity_idx" ON "ci_sessions" ("last_activity");
+			');
 
-		$this->db->query("
-			ALTER TABLE `files`
-			ADD `user` INT(8) UNSIGNED NOT NULL DEFAULT '0',
-			ADD INDEX (`user`)
-		");
+			$this->db->query('
+				ALTER TABLE "files" ADD "user" integer NOT NULL DEFAULT 0;
+				CREATE INDEX "user_idx" ON "files" ("user");
+			');
+		}
+		else
+		{
+			$this->db->query("
+				CREATE TABLE IF NOT EXISTS `users` (
+					`id` int(8) UNSIGNED NOT NULL AUTO_INCREMENT,
+					`username` varchar(32) COLLATE ascii_general_ci NOT NULL,
+					`password` varchar(60) COLLATE ascii_general_ci NOT NULL,
+					`email` varchar(255) COLLATE ascii_general_ci NOT NULL,
+					PRIMARY KEY (`id`)
+				) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+			");
+
+			$this->db->query("
+				CREATE TABLE IF NOT EXISTS `ci_sessions` (
+					`session_id` varchar(40) NOT NULL DEFAULT '0',
+					`ip_address` varchar(16) NOT NULL DEFAULT '0',
+					`user_agent` varchar(120) NOT NULL,
+					`last_activity` int(10) unsigned NOT NULL DEFAULT '0',
+					`user_data` text NOT NULL,
+					PRIMARY KEY (`session_id`),
+					KEY `last_activity_idx` (`last_activity`)
+				) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+			");
+
+			$this->db->query("
+				ALTER TABLE `files`
+				ADD `user` INT(8) UNSIGNED NOT NULL DEFAULT '0',
+				ADD INDEX (`user`)
+			");
+		}
 	}
 
 	public function down()
 	{
 		$this->dbforge->drop_table('users');
 		$this->dbforge->drop_table('ci_sessions');
-		$this->db->query("
-			ALTER TABLE `files`
-			DROP `user`
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('ALTER TABLE "files" DROP "user"');
+		}
+		else
+		{
+			$this->db->query('ALTER TABLE `files` DROP `user`');
+		}
 	}
 }
diff --git a/application/migrations/003_add_referrers.php b/application/migrations/003_add_referrers.php
index 524e92ff0..1f6b966e3 100644
--- a/application/migrations/003_add_referrers.php
+++ b/application/migrations/003_add_referrers.php
@@ -5,28 +5,56 @@ class Migration_Add_referrers extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			CREATE TABLE `invitations` (
-				`user` int(8) unsigned NOT NULL,
-				`key` varchar(16) CHARACTER SET ascii NOT NULL,
-				`date` int(11) unsigned NOT NULL,
-				PRIMARY KEY (`key`),
-		KEY `user` (`user`),
-		KEY `date` (`date`)
-			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
-		");
-		$this->db->query("
-			ALTER TABLE `users`
-			ADD `referrer` INT(8) UNSIGNED NOT NULL DEFAULT '0'
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				CREATE TABLE "invitations" (
+					"user" integer NOT NULL,
+					"key" character varying(16) NOT NULL,
+					"date" integer NOT NULL,
+					PRIMARY KEY ("key")
+				);
+				CREATE INDEX "invitations_user_idx" ON "invitations" ("user");
+				CREATE INDEX "invitations_date_idx" ON "invitations" ("date");
+			');
+			$this->db->query('
+				ALTER TABLE "users"
+				ADD "referrer" integer NOT NULL DEFAULT 0
+			');
+		}
+		else
+		{
+			$this->db->query("
+				CREATE TABLE `invitations` (
+					`user` int(8) unsigned NOT NULL,
+					`key` varchar(16) CHARACTER SET ascii NOT NULL,
+					`date` int(11) unsigned NOT NULL,
+					PRIMARY KEY (`key`),
+			KEY `user` (`user`),
+			KEY `date` (`date`)
+				) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
+			");
+			$this->db->query("
+				ALTER TABLE `users`
+				ADD `referrer` INT(8) UNSIGNED NOT NULL DEFAULT '0'
+			");
+		}
 	}
 
 	public function down()
 	{
-		$this->db->query("
-			ALTER TABLE `users`
-			DROP `referrer`
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				ALTER TABLE "users" DROP "referrer"
+			');
+		}
+		else
+		{
+			$this->db->query('
+				ALTER TABLE `users` DROP `referrer`
+			');
+		}
 		$this->dbforge->drop_table('invitations');
 
 	}
diff --git a/application/migrations/004_add_filesize.php b/application/migrations/004_add_filesize.php
index d7a70223d..7e5355c14 100644
--- a/application/migrations/004_add_filesize.php
+++ b/application/migrations/004_add_filesize.php
@@ -5,18 +5,35 @@ class Migration_Add_filesize extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			ALTER TABLE `files`
-			ADD `filesize` INT UNSIGNED NOT NULL
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				ALTER TABLE "files"
+				ADD "filesize" integer NOT NULL
+			');
+		}
+		else
+		{
+			$this->db->query("
+				ALTER TABLE `files`
+				ADD `filesize` INT UNSIGNED NOT NULL
+			");
+		}
 	}
 
 	public function down()
 	{
-		$this->db->query("
-			ALTER TABLE `files`
-			DROP `filesize`
-		");
-
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				ALTER TABLE "files" DROP "filesize"
+			');
+		}
+		else
+		{
+			$this->db->query('
+				ALTER TABLE `files` DROP `filesize`
+			');
+		}
 	}
 }
diff --git a/application/migrations/005_drop_file_password.php b/application/migrations/005_drop_file_password.php
index bf03490a8..2be20e550 100644
--- a/application/migrations/005_drop_file_password.php
+++ b/application/migrations/005_drop_file_password.php
@@ -5,17 +5,31 @@ class Migration_Drop_file_password extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			ALTER TABLE `files`
-			DROP `password`;
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('ALTER TABLE "files" DROP "password"');
+		}
+		else
+		{
+			$this->db->query("ALTER TABLE `files` DROP `password`;");
+		}
 	}
 
 	public function down()
 	{
-		$this->db->query("
-			ALTER TABLE `files`
-			ADD `password` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL;
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				ALTER TABLE "files"
+					ADD "password" character varying(40) DEFAULT NULL
+			');
+		}
+		else
+		{
+			$this->db->query("
+				ALTER TABLE `files`
+					ADD `password` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL;
+			");
+		}
 	}
 }
diff --git a/application/migrations/006_add_username_index.php b/application/migrations/006_add_username_index.php
index ea5e3ebc0..a7ad236f3 100644
--- a/application/migrations/006_add_username_index.php
+++ b/application/migrations/006_add_username_index.php
@@ -5,17 +5,33 @@ class Migration_Add_username_index extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			ALTER TABLE `users`
-			ADD UNIQUE `username` (`username`);
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				CREATE UNIQUE INDEX "users_username_idx" ON "users" ("username")
+			');
+		}
+		else
+		{
+			$this->db->query("
+				ALTER TABLE `users`
+				ADD UNIQUE `username` (`username`);
+			");
+		}
 	}
 
 	public function down()
 	{
-		$this->db->query("
-			ALTER TABLE `users`
-			DROP INDEX `username`;
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('DROP INDEX "users_username_idx"');
+		}
+		else
+		{
+			$this->db->query("
+				ALTER TABLE `users`
+				DROP INDEX `username`;
+			");
+		}
 	}
 }
diff --git a/application/migrations/007_repurpose_invitations.php b/application/migrations/007_repurpose_invitations.php
index d586c2829..8f83ff7c9 100644
--- a/application/migrations/007_repurpose_invitations.php
+++ b/application/migrations/007_repurpose_invitations.php
@@ -5,33 +5,62 @@ class Migration_Repurpose_invitations extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			ALTER TABLE `invitations`
-				ADD `action` VARCHAR(255) NOT NULL,
-				ADD `data` TEXT NULL,
-				ADD INDEX `action` (`action`);
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				ALTER TABLE "invitations"
+					ADD "action" character varying(255) NOT NULL,
+					ADD "data" TEXT NULL;
+				CREATE INDEX "invitations_action_idx" ON invitations ("action");
+			');
 
-		$this->db->query("
-			UPDATE `invitations` SET `action` = 'invitation' WHERE `action` = '';
-		");
+			$this->db->query('
+				UPDATE "invitations" SET "action" = \'invitation\' WHERE "action" = \'\'
+			');
 
-		$this->db->query("
-			ALTER TABLE `invitations` RENAME `actions`;
-		");
+			$this->db->query('
+				ALTER TABLE "invitations" RENAME TO "actions";
+			');
+		}
+		else
+		{
+			$this->db->query("
+				ALTER TABLE `invitations`
+					ADD `action` VARCHAR(255) NOT NULL,
+					ADD `data` TEXT NULL,
+					ADD INDEX `action` (`action`);
+			");
 
+			$this->db->query("
+				UPDATE `invitations` SET `action` = 'invitation' WHERE `action` = '';
+			");
+
+			$this->db->query("
+				ALTER TABLE `invitations` RENAME `actions`;
+			");
+		}
 	}
 
 	public function down()
 	{
-		$this->db->query("
-			ALTER TABLE `actions` RENAME `invitations`;
-		");
-
-		$this->db->query("
-			ALTER TABLE `invitations`
-				DROP `action`,
-				DROP `data`;
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('ALTER TABLE "actions" RENAME TO "invitations"');
+			$this->db->query('
+				ALTER TABLE "invitations"
+					DROP "action",
+					DROP "data";
+			');
+		}
+		else
+		{
+			$this->db->query('ALTER TABLE `actions` RENAME `invitations`');
+			$this->db->query('
+				ALTER TABLE `invitations`
+					DROP `action`,
+					DROP `data`;
+			');
+		}
+
 	}
 }
diff --git a/application/migrations/008_add_profiles.php b/application/migrations/008_add_profiles.php
index 3fea33c08..1e55c7d14 100644
--- a/application/migrations/008_add_profiles.php
+++ b/application/migrations/008_add_profiles.php
@@ -5,27 +5,55 @@ class Migration_Add_profiles extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			CREATE TABLE `profiles` (
-				`user` int(8) unsigned NOT NULL,
-				`upload_id_limits` varchar(255) COLLATE utf8_bin NOT NULL,
-				PRIMARY KEY (`user`)
-			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
-			");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				CREATE TABLE "profiles" (
+					"user" integer NOT NULL,
+					"upload_id_limits" varchar(255) NOT NULL,
+					PRIMARY KEY ("user")
+				)
+			');
 
-		$this->db->query("
-			ALTER TABLE `files` CHANGE `id` `id` VARCHAR( 255 );
-		");
+			$this->db->query('
+				ALTER TABLE "files" ALTER COLUMN "id" TYPE varchar(255);
+			');
+		}
+		else
+		{
+			$this->db->query("
+				CREATE TABLE `profiles` (
+					`user` int(8) unsigned NOT NULL,
+					`upload_id_limits` varchar(255) COLLATE utf8_bin NOT NULL,
+					PRIMARY KEY (`user`)
+				) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
+				");
 
+			$this->db->query("
+				ALTER TABLE `files` CHANGE `id` `id` VARCHAR( 255 );
+			");
+		}
 	}
 
 	public function down()
 	{
-		$this->db->query("
-			DROP TABLE `profiles`;
-		");
-		$this->db->query("
-			ALTER TABLE `files` CHANGE `id` `id` VARCHAR( 6 );
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				DROP TABLE "profiles";
+			');
+			$this->db->query('
+				ALTER TABLE "files" ALTER COLUMN "id" TYPE varchar(6);
+			');
+		}
+		else
+		{
+			$this->db->query("
+				DROP TABLE `profiles`;
+			");
+			$this->db->query("
+				ALTER TABLE `files` CHANGE `id` `id` VARCHAR( 6 );
+			");
+		}
 	}
 }
diff --git a/application/migrations/009_add_apikeys.php b/application/migrations/009_add_apikeys.php
index 8e88260a8..7b1b5aa58 100644
--- a/application/migrations/009_add_apikeys.php
+++ b/application/migrations/009_add_apikeys.php
@@ -5,16 +5,32 @@ class Migration_Add_apikeys extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			CREATE TABLE `apikeys` (
-				`key` varchar(64) COLLATE utf8_bin NOT NULL,
-				`user` int(8) unsigned NOT NULL,
-				`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-				`comment` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
-				PRIMARY KEY (`key`),
-				KEY `user` (`user`)
-			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				CREATE TABLE "apikeys" (
+					"key" varchar(64) NOT NULL,
+					"user" integer NOT NULL,
+					"created" integer NOT NULL,
+					"comment" varchar(255) NOT NULL,
+					PRIMARY KEY ("key")
+				);
+				CREATE INDEX "apikeys_user_idx" ON "apikeys" ("user");
+			');
+		}
+		else
+		{
+			$this->db->query("
+				CREATE TABLE `apikeys` (
+					`key` varchar(64) COLLATE utf8_bin NOT NULL,
+					`user` int(8) unsigned NOT NULL,
+					`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+					`comment` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
+					PRIMARY KEY (`key`),
+					KEY `user` (`user`)
+				) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
+			");
+		}
 	}
 
 	public function down()
diff --git a/application/migrations/010_files_innodb.php b/application/migrations/010_files_innodb.php
index b32f94724..f965344d2 100644
--- a/application/migrations/010_files_innodb.php
+++ b/application/migrations/010_files_innodb.php
@@ -5,9 +5,12 @@ class Migration_files_innodb extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			ALTER TABLE `files` ENGINE = InnoDB;
-		");
+		if ($this->db->dbdriver != 'postgre')
+		{
+			$this->db->query("
+				ALTER TABLE `files` ENGINE = InnoDB;
+			");
+		}
 	}
 
 	public function down()
diff --git a/application/migrations/011_apikeys_add_access_level.php b/application/migrations/011_apikeys_add_access_level.php
index e0f39317b..29a2f769b 100644
--- a/application/migrations/011_apikeys_add_access_level.php
+++ b/application/migrations/011_apikeys_add_access_level.php
@@ -5,15 +5,33 @@ class Migration_apikeys_add_access_level extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("
-			alter table `apikeys` add `access_level` varchar(255) default 'apikey';
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				alter table "apikeys" add "access_level" varchar(255) default \'apikey\'
+			');
+		}
+		else
+		{
+			$this->db->query("
+				alter table `apikeys` add `access_level` varchar(255) default 'apikey';
+			");
+		}
 	}
 
 	public function down()
 	{
-		$this->db->query("
-			alter table `apikeys` drop `access_level`;
-		");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				alter table "apikeys" drop "access_level"
+			');
+		}
+		else
+		{
+			$this->db->query('
+				alter table `apikeys` drop `access_level`
+			');
+		}
 	}
 }
diff --git a/application/migrations/012_add_constraints.php b/application/migrations/012_add_constraints.php
index 2b0764fb0..04c0fb9ca 100644
--- a/application/migrations/012_add_constraints.php
+++ b/application/migrations/012_add_constraints.php
@@ -5,12 +5,27 @@ class Migration_add_constraints extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query("ALTER TABLE `users` ADD INDEX(`referrer`);");
-		$this->db->query("ALTER TABLE `users` CHANGE `referrer` `referrer`
-			INT(8) UNSIGNED NULL;");
-		$this->db->query("UPDATE `users` SET `referrer` = NULL where `referrer` = 0;");
-		$this->db->query("ALTER TABLE `users` ADD FOREIGN KEY (`referrer`)
-			REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;");
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('ALTER TABLE "users" ALTER COLUMN "referrer" TYPE integer');
+			$this->db->query('ALTER TABLE "users" ALTER COLUMN "referrer" DROP NOT NULL');
+			$this->db->query('CREATE INDEX "users_referrer_idx" ON "users" ("referrer")');
+			$this->db->query('UPDATE "users" SET "referrer" = NULL where "referrer" = 0');
+			$this->db->query('
+				ALTER TABLE "users"
+					ADD CONSTRAINT "referrer_user_fkey" FOREIGN KEY ("referrer")
+						REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE RESTRICT
+			');
+		}
+		else
+		{
+			$this->db->query("ALTER TABLE `users` ADD INDEX(`referrer`);");
+			$this->db->query("ALTER TABLE `users` CHANGE `referrer` `referrer`
+				INT(8) UNSIGNED NULL;");
+			$this->db->query("UPDATE `users` SET `referrer` = NULL where `referrer` = 0;");
+			$this->db->query("ALTER TABLE `users` ADD FOREIGN KEY (`referrer`)
+				REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;");
+		}
 	}
 
 	public function down()
diff --git a/application/migrations/013_add_multipaste.php b/application/migrations/013_add_multipaste.php
index edb4a0748..3b97f6986 100644
--- a/application/migrations/013_add_multipaste.php
+++ b/application/migrations/013_add_multipaste.php
@@ -5,28 +5,63 @@ class Migration_add_multipaste extends CI_Migration {
 
 	public function up()
 	{
-		$this->db->query('
-		CREATE TABLE `multipaste` (
-		  `url_id` varchar(255) NOT NULL,
-		  `multipaste_id` int(11) NOT NULL AUTO_INCREMENT,
-		  `user_id` int(11) NOT NULL,
-		  `date` int(11) NOT NULL,
-		  PRIMARY KEY (`url_id`),
-		  UNIQUE KEY `multipaste_id` (`multipaste_id`),
-		  KEY `user_id` (`user_id`)
-		) ENGINE=InnoDB  DEFAULT CHARSET=utf8;');
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				CREATE TABLE "multipaste" (
+					"url_id" varchar(255) NOT NULL,
+					"multipaste_id" serial,
+					"user_id" integer NOT NULL,
+					"date" integer NOT NULL,
+					PRIMARY KEY ("url_id")
+				);
+				CREATE INDEX "multipaste_user_idx" ON "multipaste" ("user_id");
+				CREATE UNIQUE INDEX "multipaste_id_idx" ON "multipaste" ("multipaste_id");
+			');
 
-		$this->db->query('
-		CREATE TABLE `multipaste_file_map` (
-		  `multipaste_id` int(11) NOT NULL,
-		  `file_url_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
-		  `sort_order` int(10) unsigned NOT NULL AUTO_INCREMENT,
-		  PRIMARY KEY (`sort_order`),
-		  UNIQUE KEY `multipaste_id` (`multipaste_id`,`file_url_id`),
-		  KEY `multipaste_file_map_ibfk_2` (`file_url_id`),
-		  CONSTRAINT `multipaste_file_map_ibfk_1` FOREIGN KEY (`multipaste_id`) REFERENCES `multipaste` (`multipaste_id`) ON DELETE CASCADE ON UPDATE CASCADE,
-		  CONSTRAINT `multipaste_file_map_ibfk_2` FOREIGN KEY (`file_url_id`) REFERENCES `files` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-		) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;');
+			$this->db->query('
+				CREATE TABLE "multipaste_file_map" (
+					"multipaste_id" integer NOT NULL,
+					"file_url_id" varchar(255) NOT NULL,
+					"sort_order" serial,
+					PRIMARY KEY ("sort_order")
+				);
+				CREATE UNIQUE INDEX "multipaste_file_map_idx"
+					ON "multipaste_file_map" ("multipaste_id", "file_url_id");
+				CREATE INDEX "multipaste_file_map_file_idx" ON "multipaste_file_map" ("file_url_id");
+				ALTER TABLE "multipaste_file_map"
+					ADD CONSTRAINT "multipaste_file_map_id_fkey" FOREIGN KEY ("multipaste_id")
+						REFERENCES "multipaste"("multipaste_id") ON DELETE CASCADE ON UPDATE CASCADE;
+				ALTER TABLE "multipaste_file_map"
+					ADD CONSTRAINT "multipaste_file_map_file_id_fkey" FOREIGN KEY ("file_url_id")
+						REFERENCES "files"("id") ON DELETE CASCADE ON UPDATE CASCADE
+			');
+		}
+		else
+		{
+			$this->db->query('
+			CREATE TABLE `multipaste` (
+				`url_id` varchar(255) NOT NULL,
+				`multipaste_id` int(11) NOT NULL AUTO_INCREMENT,
+				`user_id` int(11) NOT NULL,
+				`date` int(11) NOT NULL,
+				PRIMARY KEY (`url_id`),
+				UNIQUE KEY `multipaste_id` (`multipaste_id`),
+				KEY `user_id` (`user_id`)
+			) ENGINE=InnoDB  DEFAULT CHARSET=utf8;');
+
+			$this->db->query('
+			CREATE TABLE `multipaste_file_map` (
+				`multipaste_id` int(11) NOT NULL,
+				`file_url_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
+				`sort_order` int(10) unsigned NOT NULL AUTO_INCREMENT,
+				PRIMARY KEY (`sort_order`),
+				UNIQUE KEY `multipaste_id` (`multipaste_id`,`file_url_id`),
+				KEY `multipaste_file_map_ibfk_2` (`file_url_id`),
+				CONSTRAINT `multipaste_file_map_ibfk_1` FOREIGN KEY (`multipaste_id`) REFERENCES `multipaste` (`multipaste_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+				CONSTRAINT `multipaste_file_map_ibfk_2` FOREIGN KEY (`file_url_id`) REFERENCES `files` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+			) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;');
+		}
 	}
 
 	public function down()
-- 
cgit v1.2.3-24-g4f1b


From 91bd244393ae40e302e3c1e0f7fedb01ddeee957 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Thu, 18 Sep 2014 22:05:08 +0300
Subject: Query builder in user login and controller

---
 application/controllers/user.php                 | 12 ++++++-----
 application/libraries/Duser/drivers/Duser_db.php | 27 +++++++++++-------------
 2 files changed, 19 insertions(+), 20 deletions(-)

(limited to 'application')

diff --git a/application/controllers/user.php b/application/controllers/user.php
index 079f1665c..45bd93816 100644
--- a/application/controllers/user.php
+++ b/application/controllers/user.php
@@ -102,11 +102,13 @@ class User extends MY_Controller {
 
 		$key = random_alphanum(32);
 
-		$this->db->query("
-			INSERT INTO `apikeys`
-			(`key`, `user`, `comment`, `access_level`)
-			VALUES (?, ?, ?, ?)
-		", array($key, $userid, $comment, $access_level));
+		$this->db->set([
+				'key'          => $key,
+				'user'         => $userid,
+				'comment'      => $comment,
+				'access_level' => $access_level
+			])
+			->insert('apikeys');
 
 		if (static_storage("response_type") == "json") {
 			return send_json_reply(array("new_key" => $key));
diff --git a/application/libraries/Duser/drivers/Duser_db.php b/application/libraries/Duser/drivers/Duser_db.php
index a58b5a298..258de1820 100644
--- a/application/libraries/Duser/drivers/Duser_db.php
+++ b/application/libraries/Duser/drivers/Duser_db.php
@@ -22,11 +22,10 @@ class Duser_db extends Duser_Driver {
 	{
 		$CI =& get_instance();
 
-		$query = $CI->db->query('
-			SELECT username, id, password
-			FROM `users`
-			WHERE `username` = ?
-			', array($username))->row_array();
+		$query = $CI->db->select('username, id, password')
+			->from('users')
+			->where('username', $username)
+			->get()->row_array();
 
 		if (empty($query)) {
 			return false;
@@ -46,11 +45,10 @@ class Duser_db extends Duser_Driver {
 	{
 		$CI =& get_instance();
 
-		$query = $CI->db->query("
-			SELECT id
-			FROM users
-			WHERE username = ?
-			", array($username));
+		$query = $CI->db->select('id')
+			->from('users')
+			->where('username', $username)
+			->get();
 
 		if ($query->num_rows() > 0) {
 			return true;
@@ -63,11 +61,10 @@ class Duser_db extends Duser_Driver {
 	{
 		$CI =& get_instance();
 
-		$query = $CI->db->query("
-			SELECT email
-			FROM users
-			WHERE id = ?
-			", array($userid))->row_array();
+		$query = $CI->db->select('email')
+			->from('users')
+			->where('id', $userid)
+			->get()->row_array();
 
 		if (empty($query)) {
 			show_error("Failed to get email address from db");
-- 
cgit v1.2.3-24-g4f1b


From fe7f15dbfb8020daf96110e86e359ec01558fcb8 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Thu, 18 Sep 2014 22:47:21 +0300
Subject: Correcting bracket style for 'if'

---
 application/migrations/001_add_files.php               |  3 +--
 application/migrations/002_add_users.php               | 16 ++++++----------
 application/migrations/003_add_referrers.php           | 13 +++++--------
 application/migrations/004_add_filesize.php            | 14 ++++----------
 application/migrations/005_drop_file_password.php      | 14 ++++----------
 application/migrations/006_add_username_index.php      | 14 ++++----------
 application/migrations/007_repurpose_invitations.php   | 15 +++++++--------
 application/migrations/008_add_profiles.php            | 18 ++++++++----------
 application/migrations/009_add_apikeys.php             |  9 ++++-----
 application/migrations/010_files_innodb.php            |  3 +--
 .../migrations/011_apikeys_add_access_level.php        | 14 ++++----------
 application/migrations/012_add_constraints.php         |  9 ++++-----
 application/migrations/013_add_multipaste.php          |  9 ++++-----
 13 files changed, 56 insertions(+), 95 deletions(-)

(limited to 'application')

diff --git a/application/migrations/001_add_files.php b/application/migrations/001_add_files.php
index c5e4e7dfe..8af202285 100644
--- a/application/migrations/001_add_files.php
+++ b/application/migrations/001_add_files.php
@@ -6,8 +6,7 @@ class Migration_Add_files extends CI_Migration {
 	public function up()
 	{
 		// Set database engine for MySQL drivers
-		if (strpos($this->db->dbdriver, 'mysql') !== FALSE)
-		{
+		if (strpos($this->db->dbdriver, 'mysql') !== FALSE) {
 			$this->db->query('SET storage_engine=MYISAM');
 		}
 
diff --git a/application/migrations/002_add_users.php b/application/migrations/002_add_users.php
index d74402263..322415d9b 100644
--- a/application/migrations/002_add_users.php
+++ b/application/migrations/002_add_users.php
@@ -5,8 +5,7 @@ class Migration_Add_users extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				CREATE TABLE IF NOT EXISTS "users" (
 					"id" serial PRIMARY KEY,
@@ -32,9 +31,9 @@ class Migration_Add_users extends CI_Migration {
 				ALTER TABLE "files" ADD "user" integer NOT NULL DEFAULT 0;
 				CREATE INDEX "user_idx" ON "files" ("user");
 			');
-		}
-		else
-		{
+
+		} else {
+
 			$this->db->query("
 				CREATE TABLE IF NOT EXISTS `users` (
 					`id` int(8) UNSIGNED NOT NULL AUTO_INCREMENT,
@@ -69,12 +68,9 @@ class Migration_Add_users extends CI_Migration {
 	{
 		$this->dbforge->drop_table('users');
 		$this->dbforge->drop_table('ci_sessions');
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('ALTER TABLE "files" DROP "user"');
-		}
-		else
-		{
+		} else {
 			$this->db->query('ALTER TABLE `files` DROP `user`');
 		}
 	}
diff --git a/application/migrations/003_add_referrers.php b/application/migrations/003_add_referrers.php
index 1f6b966e3..9ca167eab 100644
--- a/application/migrations/003_add_referrers.php
+++ b/application/migrations/003_add_referrers.php
@@ -5,8 +5,7 @@ class Migration_Add_referrers extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				CREATE TABLE "invitations" (
 					"user" integer NOT NULL,
@@ -21,9 +20,9 @@ class Migration_Add_referrers extends CI_Migration {
 				ALTER TABLE "users"
 				ADD "referrer" integer NOT NULL DEFAULT 0
 			');
-		}
-		else
-		{
+
+		} else {
+
 			$this->db->query("
 				CREATE TABLE `invitations` (
 					`user` int(8) unsigned NOT NULL,
@@ -48,9 +47,7 @@ class Migration_Add_referrers extends CI_Migration {
 			$this->db->query('
 				ALTER TABLE "users" DROP "referrer"
 			');
-		}
-		else
-		{
+		} else {
 			$this->db->query('
 				ALTER TABLE `users` DROP `referrer`
 			');
diff --git a/application/migrations/004_add_filesize.php b/application/migrations/004_add_filesize.php
index 7e5355c14..f2367937a 100644
--- a/application/migrations/004_add_filesize.php
+++ b/application/migrations/004_add_filesize.php
@@ -5,15 +5,12 @@ class Migration_Add_filesize extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				ALTER TABLE "files"
 				ADD "filesize" integer NOT NULL
 			');
-		}
-		else
-		{
+		} else {
 			$this->db->query("
 				ALTER TABLE `files`
 				ADD `filesize` INT UNSIGNED NOT NULL
@@ -23,14 +20,11 @@ class Migration_Add_filesize extends CI_Migration {
 
 	public function down()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				ALTER TABLE "files" DROP "filesize"
 			');
-		}
-		else
-		{
+		} else {
 			$this->db->query('
 				ALTER TABLE `files` DROP `filesize`
 			');
diff --git a/application/migrations/005_drop_file_password.php b/application/migrations/005_drop_file_password.php
index 2be20e550..e8b7f8952 100644
--- a/application/migrations/005_drop_file_password.php
+++ b/application/migrations/005_drop_file_password.php
@@ -5,27 +5,21 @@ class Migration_Drop_file_password extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('ALTER TABLE "files" DROP "password"');
-		}
-		else
-		{
+		} else {
 			$this->db->query("ALTER TABLE `files` DROP `password`;");
 		}
 	}
 
 	public function down()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				ALTER TABLE "files"
 					ADD "password" character varying(40) DEFAULT NULL
 			');
-		}
-		else
-		{
+		} else {
 			$this->db->query("
 				ALTER TABLE `files`
 					ADD `password` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL;
diff --git a/application/migrations/006_add_username_index.php b/application/migrations/006_add_username_index.php
index a7ad236f3..5b8c3584f 100644
--- a/application/migrations/006_add_username_index.php
+++ b/application/migrations/006_add_username_index.php
@@ -5,14 +5,11 @@ class Migration_Add_username_index extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				CREATE UNIQUE INDEX "users_username_idx" ON "users" ("username")
 			');
-		}
-		else
-		{
+		} else {
 			$this->db->query("
 				ALTER TABLE `users`
 				ADD UNIQUE `username` (`username`);
@@ -22,12 +19,9 @@ class Migration_Add_username_index extends CI_Migration {
 
 	public function down()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('DROP INDEX "users_username_idx"');
-		}
-		else
-		{
+		} else {
 			$this->db->query("
 				ALTER TABLE `users`
 				DROP INDEX `username`;
diff --git a/application/migrations/007_repurpose_invitations.php b/application/migrations/007_repurpose_invitations.php
index 8f83ff7c9..024b62984 100644
--- a/application/migrations/007_repurpose_invitations.php
+++ b/application/migrations/007_repurpose_invitations.php
@@ -5,8 +5,7 @@ class Migration_Repurpose_invitations extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				ALTER TABLE "invitations"
 					ADD "action" character varying(255) NOT NULL,
@@ -21,9 +20,9 @@ class Migration_Repurpose_invitations extends CI_Migration {
 			$this->db->query('
 				ALTER TABLE "invitations" RENAME TO "actions";
 			');
-		}
-		else
-		{
+
+		} else {
+
 			$this->db->query("
 				ALTER TABLE `invitations`
 					ADD `action` VARCHAR(255) NOT NULL,
@@ -51,9 +50,9 @@ class Migration_Repurpose_invitations extends CI_Migration {
 					DROP "action",
 					DROP "data";
 			');
-		}
-		else
-		{
+
+		} else {
+
 			$this->db->query('ALTER TABLE `actions` RENAME `invitations`');
 			$this->db->query('
 				ALTER TABLE `invitations`
diff --git a/application/migrations/008_add_profiles.php b/application/migrations/008_add_profiles.php
index 1e55c7d14..9958fb03e 100644
--- a/application/migrations/008_add_profiles.php
+++ b/application/migrations/008_add_profiles.php
@@ -5,8 +5,7 @@ class Migration_Add_profiles extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				CREATE TABLE "profiles" (
 					"user" integer NOT NULL,
@@ -18,9 +17,9 @@ class Migration_Add_profiles extends CI_Migration {
 			$this->db->query('
 				ALTER TABLE "files" ALTER COLUMN "id" TYPE varchar(255);
 			');
-		}
-		else
-		{
+
+		} else {
+
 			$this->db->query("
 				CREATE TABLE `profiles` (
 					`user` int(8) unsigned NOT NULL,
@@ -37,17 +36,16 @@ class Migration_Add_profiles extends CI_Migration {
 
 	public function down()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				DROP TABLE "profiles";
 			');
 			$this->db->query('
 				ALTER TABLE "files" ALTER COLUMN "id" TYPE varchar(6);
 			');
-		}
-		else
-		{
+
+		} else {
+
 			$this->db->query("
 				DROP TABLE `profiles`;
 			");
diff --git a/application/migrations/009_add_apikeys.php b/application/migrations/009_add_apikeys.php
index 7b1b5aa58..bdc4dd07d 100644
--- a/application/migrations/009_add_apikeys.php
+++ b/application/migrations/009_add_apikeys.php
@@ -5,8 +5,7 @@ class Migration_Add_apikeys extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				CREATE TABLE "apikeys" (
 					"key" varchar(64) NOT NULL,
@@ -17,9 +16,9 @@ class Migration_Add_apikeys extends CI_Migration {
 				);
 				CREATE INDEX "apikeys_user_idx" ON "apikeys" ("user");
 			');
-		}
-		else
-		{
+
+		} else {
+
 			$this->db->query("
 				CREATE TABLE `apikeys` (
 					`key` varchar(64) COLLATE utf8_bin NOT NULL,
diff --git a/application/migrations/010_files_innodb.php b/application/migrations/010_files_innodb.php
index f965344d2..318314bd3 100644
--- a/application/migrations/010_files_innodb.php
+++ b/application/migrations/010_files_innodb.php
@@ -5,8 +5,7 @@ class Migration_files_innodb extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver != 'postgre')
-		{
+		if ($this->db->dbdriver != 'postgre') {
 			$this->db->query("
 				ALTER TABLE `files` ENGINE = InnoDB;
 			");
diff --git a/application/migrations/011_apikeys_add_access_level.php b/application/migrations/011_apikeys_add_access_level.php
index 29a2f769b..2fdbc7271 100644
--- a/application/migrations/011_apikeys_add_access_level.php
+++ b/application/migrations/011_apikeys_add_access_level.php
@@ -5,14 +5,11 @@ class Migration_apikeys_add_access_level extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				alter table "apikeys" add "access_level" varchar(255) default \'apikey\'
 			');
-		}
-		else
-		{
+		} else {
 			$this->db->query("
 				alter table `apikeys` add `access_level` varchar(255) default 'apikey';
 			");
@@ -21,14 +18,11 @@ class Migration_apikeys_add_access_level extends CI_Migration {
 
 	public function down()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				alter table "apikeys" drop "access_level"
 			');
-		}
-		else
-		{
+		} else {
 			$this->db->query('
 				alter table `apikeys` drop `access_level`
 			');
diff --git a/application/migrations/012_add_constraints.php b/application/migrations/012_add_constraints.php
index 04c0fb9ca..1ed4abf08 100644
--- a/application/migrations/012_add_constraints.php
+++ b/application/migrations/012_add_constraints.php
@@ -5,8 +5,7 @@ class Migration_add_constraints extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('ALTER TABLE "users" ALTER COLUMN "referrer" TYPE integer');
 			$this->db->query('ALTER TABLE "users" ALTER COLUMN "referrer" DROP NOT NULL');
 			$this->db->query('CREATE INDEX "users_referrer_idx" ON "users" ("referrer")');
@@ -16,9 +15,9 @@ class Migration_add_constraints extends CI_Migration {
 					ADD CONSTRAINT "referrer_user_fkey" FOREIGN KEY ("referrer")
 						REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE RESTRICT
 			');
-		}
-		else
-		{
+
+		} else {
+
 			$this->db->query("ALTER TABLE `users` ADD INDEX(`referrer`);");
 			$this->db->query("ALTER TABLE `users` CHANGE `referrer` `referrer`
 				INT(8) UNSIGNED NULL;");
diff --git a/application/migrations/013_add_multipaste.php b/application/migrations/013_add_multipaste.php
index 3b97f6986..ef29effa9 100644
--- a/application/migrations/013_add_multipaste.php
+++ b/application/migrations/013_add_multipaste.php
@@ -5,8 +5,7 @@ class Migration_add_multipaste extends CI_Migration {
 
 	public function up()
 	{
-		if ($this->db->dbdriver == 'postgre')
-		{
+		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				CREATE TABLE "multipaste" (
 					"url_id" varchar(255) NOT NULL,
@@ -36,9 +35,9 @@ class Migration_add_multipaste extends CI_Migration {
 					ADD CONSTRAINT "multipaste_file_map_file_id_fkey" FOREIGN KEY ("file_url_id")
 						REFERENCES "files"("id") ON DELETE CASCADE ON UPDATE CASCADE
 			');
-		}
-		else
-		{
+
+		} else {
+
 			$this->db->query('
 			CREATE TABLE `multipaste` (
 				`url_id` varchar(255) NOT NULL,
-- 
cgit v1.2.3-24-g4f1b


From 17a84b1f27ff6b618778ef4e4378039f6ae266da Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 00:02:30 +0300
Subject: User controller queries built dynamically

---
 application/controllers/user.php | 146 +++++++++++++++++++--------------------
 1 file changed, 72 insertions(+), 74 deletions(-)

(limited to 'application')

diff --git a/application/controllers/user.php b/application/controllers/user.php
index 45bd93816..30e5b9fd7 100644
--- a/application/controllers/user.php
+++ b/application/controllers/user.php
@@ -102,12 +102,12 @@ class User extends MY_Controller {
 
 		$key = random_alphanum(32);
 
-		$this->db->set([
+		$this->db->set(array(
 				'key'          => $key,
 				'user'         => $userid,
 				'comment'      => $comment,
 				'access_level' => $access_level
-			])
+			))
 			->insert('apikeys');
 
 		if (static_storage("response_type") == "json") {
@@ -128,11 +128,9 @@ class User extends MY_Controller {
 		$userid = $this->muser->get_userid();
 		$key = $this->input->post("key");
 
-		$this->db->query("
-			DELETE FROM `apikeys`
-			WHERE `user` = ?
-			AND `key` = ?
-			", array($userid, $key));
+		$this->db->where('user', $userid)
+			->where('key', $key)
+			->delete('apikeys');
 
 		redirect("user/apikeys");
 	}
@@ -143,11 +141,17 @@ class User extends MY_Controller {
 
 		$userid = $this->muser->get_userid();
 
-		$query = $this->db->query("
-			SELECT `key`, UNIX_TIMESTAMP(`created`) `created`, `comment`, `access_level`
-			FROM `apikeys`
-			WHERE `user` = ? order by created desc
-			", array($userid))->result_array();
+		$query = $this->db->select('key, created, comment, access_level')
+			->from('apikeys')
+			->where('user', $userid)
+			->order_by('created', 'desc')
+			->get()->result_array();
+
+		// Convert timestamp to unix timestamp
+		if (isset($query['created']))
+		{
+			$query['created'] = strtotime($query['created']);
+		}
 
 		if (static_storage("response_type") == "json") {
 			return send_json_reply($query);
@@ -167,12 +171,11 @@ class User extends MY_Controller {
 
 		$userid = $this->muser->get_userid();
 
-		$query = $this->db->query("
-			SELECT count(*) count
-			FROM `actions`
-			WHERE `user` = ?
-			AND `action` = 'invitation'
-			", array($userid))->row_array();
+		$query = $this->db->select('user')
+			->from('action')
+			->where('user', $userid)
+			->where('action', 'invitation')
+			->count_all_results();
 
 		if ($query["count"] + 1 > 3) {
 			show_error("You can't create more invitation keys at this time.");
@@ -180,11 +183,13 @@ class User extends MY_Controller {
 
 		$key = random_alphanum(12, 16);
 
-		$this->db->query("
-			INSERT INTO `actions`
-			(`key`, `user`, `date`, `action`)
-			VALUES (?, ?, ?, 'invitation')
-		", array($key, $userid, time()));
+		$this->db->set(array(
+				'key'    => $key,
+				'user'   => $userid,
+				'date'   => time(),
+				'action' => 'invitation'
+			))
+			->insert('actions');
 
 		redirect("user/invite");
 	}
@@ -196,12 +201,11 @@ class User extends MY_Controller {
 
 		$userid = $this->muser->get_userid();
 
-		$query = $this->db->query("
-			SELECT `key`, `date`
-			FROM `actions`
-			WHERE `user` = ?
-			AND `action` = 'invitation'
-			", array($userid))->result_array();
+		$query = $this->db->select('key, date')
+			->from('actions')
+			->where('user', $userid)
+			->where('action', 'invitation')
+			->get()->result_array();
 
 		$this->data["query"] = $query;
 
@@ -249,20 +253,17 @@ class User extends MY_Controller {
 			}
 
 			if (empty($error)) {
-				$this->db->query("
-					INSERT INTO users
-					(`username`, `password`, `email`, `referrer`)
-					VALUES(?, ?, ?, ?)
-					", array(
-						$username,
-						$this->muser->hash_password($password),
-						$email,
-						$referrer
-					));
-				$this->db->query("
-					DELETE FROM actions
-					WHERE `key` = ?
-					", array($key));
+				$this->db->set(array(
+						'username' => $username,
+						'password' => $this->muser->hash_password($password),
+						'email'    => $email,
+						'referrer' => $referrer
+					))
+					->insert('users');
+
+				$this->db->where('key', $key)
+					->delete('actions');
+
 				$this->load->view('header', $this->data);
 				$this->load->view($this->var->view_dir.'registered', $this->data);
 				$this->load->view('footer', $this->data);
@@ -321,27 +322,27 @@ class User extends MY_Controller {
 			show_error("Invalid username");
 		}
 
-		$userinfo = $this->db->query("
-			SELECT id, email, username
-			FROM users
-			WHERE username = ?
-			", array($username))->row_array();
+		$userinfo = $this->db->select('id, email, username')
+			->from('users')
+			->where('username', $username)
+			->get()->row_array();
 
 		$this->load->library("email");
 
-		$this->db->query("
-			INSERT INTO `actions`
-			(`key`, `user`, `date`, `action`)
-			VALUES (?, ?, ?, 'passwordreset')
-			", array($key, $userinfo["id"], time()));
-
-		$admininfo = $this->db->query("
-			SELECT email
-			FROM users
-			WHERE referrer is null
-			ORDER BY id asc
-			LIMIT 1
-			")->row_array();
+		$this->db->set(array(
+				'key'    => $key,
+				'user'   => $userinfo['id'],
+				'date'   => time(),
+				'action' => 'passwordreset'
+			))
+			->insert('actions');
+
+		$admininfo = $this->db->select('email')
+			->from('users')
+			->where('referrer', NULL)
+			->order_by('id', 'asc')
+			->limit(1)
+			->get()->row_array();
 
 		$this->email->from($admininfo["email"]);
 		$this->email->to($userinfo["email"]);
@@ -383,15 +384,14 @@ class User extends MY_Controller {
 			}
 
 			if (empty($error)) {
-				$this->db->query("
-					UPDATE users
-					SET `password` = ?
-					WHERE `id` = ?
-					", array($this->muser->hash_password($password), $userid));
-				$this->db->query("
-					DELETE FROM actions
-					WHERE `key` = ?
-					", array($key));
+				$this->db->where('id', $userid)
+					->update('users', [
+						'password' => $this->muser->hash_password($password)
+					]);
+
+				$this->db->where($key, $key)
+					->delete('actions');
+
 				$this->load->view('header', $this->data);
 				$this->load->view($this->var->view_dir.'reset_password_success', $this->data);
 				$this->load->view('footer', $this->data);
@@ -510,9 +510,7 @@ class User extends MY_Controller {
 
 		$oldest_time = (time() - $this->config->item('actions_max_age'));
 
-		$this->db->query("
-			DELETE FROM actions
-			WHERE date < ?
-			", array($oldest_time));
+		$this->db->where('date <', $oldest_time)
+			->delete('actions');
 	}
 }
-- 
cgit v1.2.3-24-g4f1b


From bd4ecd3db3444cd1c67f5d2bb4bffc83564b8c50 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 00:42:45 +0300
Subject: Integrating query builder in models

---
 application/models/mfile.php | 52 ++++++++++++++++++++------------------------
 application/models/muser.php | 41 +++++++++++++++-------------------
 2 files changed, 42 insertions(+), 51 deletions(-)

(limited to 'application')

diff --git a/application/models/mfile.php b/application/models/mfile.php
index 1f5409ec3..1d4340410 100644
--- a/application/models/mfile.php
+++ b/application/models/mfile.php
@@ -49,12 +49,11 @@ class Mfile extends CI_Model {
 			return false;
 		}
 
-		$sql = '
-			SELECT id
-			FROM `files`
-			WHERE `id` = ?
-			LIMIT 1';
-		$query = $this->db->query($sql, array($id));
+		$query = $this->db->select('id')
+			->from('files')
+			->where('id', $id)
+			->limit(1)
+			->get();
 
 		if ($query->num_rows() == 1) {
 			return true;
@@ -70,12 +69,12 @@ class Mfile extends CI_Model {
 
 	function get_filedata($id)
 	{
-		$sql = '
-			SELECT id, hash, filename, mimetype, date, user, filesize
-			FROM `files`
-			WHERE `id` = ?
-			LIMIT 1';
-		$query = $this->db->query($sql, array($id));
+		$query = $this->db
+			->select('id, hash, filename, mimetype, date, user, filesize')
+			->from('files')
+			->where('id', $id)
+			->limit(1)
+			->get();
 
 		if ($query->num_rows() > 0) {
 			return $query->row_array();
@@ -234,11 +233,9 @@ class Mfile extends CI_Model {
 	{
 		$userid = $this->muser->get_userid();
 
-		$this->db->query("
-			UPDATE files
-			SET user = ?
-			WHERE id = ?
-			", array($userid, $id));
+		$this->db->set(array('user' => $userid ))
+			->where('id', $id)
+			->update('files');
 	}
 
 	// remove old/invalid/broken IDs
@@ -309,12 +306,11 @@ class Mfile extends CI_Model {
 
 	private function unused_file($hash)
 	{
-		$sql = '
-			SELECT id
-			FROM `files`
-			WHERE `hash` = ?
-			LIMIT 1';
-		$query = $this->db->query($sql, array($hash));
+		$query = $this->db->select('id')
+			->from('files')
+			->where('hash', $hash)
+			->limit(1)
+			->get();
 
 		if ($query->num_rows() == 0) {
 			return true;
@@ -382,11 +378,11 @@ class Mfile extends CI_Model {
 
 	public function get_owner($id)
 	{
-		return $this->db->query("
-			SELECT user
-			FROM files
-			WHERE id = ?
-			", array($id))->row_array()["user"];
+		return $this->db->select('user')
+			->from('files')
+			->where('id', $id)
+			->get()->row_array()
+			['user'];
 	}
 
 	public function get_lexers() {
diff --git a/application/models/muser.php b/application/models/muser.php
index a1d8f18e5..4f5931728 100644
--- a/application/models/muser.php
+++ b/application/models/muser.php
@@ -97,17 +97,16 @@ class Muser extends CI_Model {
 		// get rid of spaces and newlines
 		$apikey = trim($apikey);
 
-		$query = $this->db->query("
-			SELECT a.user userid, a.access_level
-			FROM apikeys a
-			WHERE a.key = ?
-			", array($apikey))->row_array();
+		$query = $this->db->select('user, access_level')
+			->from('apikeys')
+			->where('key', $apikey)
+			->get()->row_array();
 
-		if (isset($query["userid"])) {
+		if (isset($query["user"])) {
 			$this->session->set_userdata(array(
 				'logged_in' => true,
 				'username' => '',
-				'userid' => $query["userid"],
+				'userid' => $query["user"],
 				'access_level' => $query["access_level"],
 			));
 			return true;
@@ -205,12 +204,10 @@ class Muser extends CI_Model {
 
 	function get_action($action, $key)
 	{
-		$query = $this->db->query("
-			SELECT *
-			FROM actions
-			WHERE `key` = ?
-			AND `action` = ?
-			", array($key, $action))->row_array();
+		$db->from('actions')
+			->where('key', $key)
+			->where('action', $action)
+			->get()->row_array();
 
 		if (!isset($query["key"]) || $key != $query["key"]) {
 			show_error("Invalid action key");
@@ -228,11 +225,10 @@ class Muser extends CI_Model {
 			"upload_id_limits" => $this->default_upload_id_limits,
 		);
 
-		$query = $this->db->query("
-			SELECT ".implode(", ", array_keys($fields))."
-			FROM `profiles`
-			WHERE user = ?
-			", array($userid))->row_array();
+		$query = $this->db->select(implode(', ', array_keys($fields)))
+			->from('profiles')
+			->where('user', $userid)
+			->get()->row_array();
 
 		$extra_fields = array(
 			"username" => $this->get_username(),
@@ -262,11 +258,10 @@ class Muser extends CI_Model {
 	{
 		$userid = $this->get_userid();
 
-		$query = $this->db->query("
-			SELECT upload_id_limits
-			FROM `profiles`
-			WHERE user = ?
-			", array($userid))->row_array();
+		$query = $this->db->select('upload_id_limits')
+			->from('profiles')
+			->where('user', $userid)
+			->get()->row_array();
 
 		if (empty($query)) {
 			return explode("-", $this->default_upload_id_limits);
-- 
cgit v1.2.3-24-g4f1b


From ce4d7b166d6785d23bf47255467ef2fbfdcf4efb Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 15:35:19 +0300
Subject: File controller uses query builder, except 2 queries

---
 application/controllers/file.php | 68 +++++++++++++++++++++-------------------
 1 file changed, 36 insertions(+), 32 deletions(-)

(limited to 'application')

diff --git a/application/controllers/file.php b/application/controllers/file.php
index e1b43d314..d293310c5 100644
--- a/application/controllers/file.php
+++ b/application/controllers/file.php
@@ -537,13 +537,13 @@ class File extends MY_Controller {
 
 		$user = $this->muser->get_userid();
 
-		$query = $this->db->query("
-			SELECT `id`, `filename`, `mimetype`, `date`, `hash`, `filesize`
-			FROM files
-			WHERE user = ?
-			AND mimetype IN ('image/jpeg', 'image/png', 'image/gif')
-			ORDER BY date DESC
-			", array($user))->result_array();
+		$query = $this->db
+			->select('id, filename, mimetype, date, hash, filesize')
+			->from('files')
+			->where('user', $user)
+			->where('mimetype', array('image/jpeg', 'image/png', 'image/gif'))
+			->order_by('date', 'desc')
+			->get()->result_array();
 
 		foreach($query as $key => $item) {
 			if (!$this->mfile->valid_id($item["id"])) {
@@ -586,11 +586,10 @@ class File extends MY_Controller {
 
 		$order = is_cli_client() ? "ASC" : "DESC";
 
-		$items = $this->db->query("
-			SELECT ".implode(",", array_keys($fields))."
-			FROM files
-			WHERE user = ?
-			", array($user))->result_array();
+		$items = $this->db->select(implode(',', array_keys($fields)))
+			->from('files')
+			->where('user', $user)
+			->get()->result_array();
 
 		$query = $this->db->query("
 			SELECT m.url_id id, sum(f.filesize) filesize, m.date, '' hash, '' mimetype, concat(count(*), ' file(s)') filename
@@ -630,10 +629,9 @@ class File extends MY_Controller {
 		$total_size = $this->db->query("
 			SELECT sum(filesize) sum
 			FROM (
-				SELECT filesize
+				SELECT DISTINCT hash, filesize
 				FROM files
 				WHERE user = ?
-				GROUP BY hash
 			) sub
 			", array($user))->row_array();
 
@@ -1003,13 +1001,15 @@ class File extends MY_Controller {
 
 		$small_upload_size = $this->config->item('small_upload_size');
 
-		$query = $this->db->query('
-			SELECT hash, id, user
-			FROM files
-			WHERE date < ? OR (user = 0 AND date < ?)',
-				array($oldest_time, $oldest_session_time));
+		$query = $this->db->select('hash, id, user')
+			->from('files')
+			->where('date <', $oldest_time)
+			->or_where_open()
+			->where('user', 0)
+			->where('date <', $oldest_session_time)
+			->get()->result_array();
 
-		foreach($query->result_array() as $row) {
+		foreach($query as $row) {
 			$file = $this->mfile->file($row['hash']);
 			if (!file_exists($file)) {
 				$this->mfile->delete_id($row["id"]);
@@ -1052,7 +1052,11 @@ class File extends MY_Controller {
 					continue;
 				}
 
-				$query = $this->db->query("SELECT hash FROM files WHERE hash = ? LIMIT 1", array($file))->row_array();
+				$query = $this->db->select('hash')
+					->from('files')
+					->where('hash', $file)
+					->limit(1)
+					->get()->row_array();
 
 				if (empty($query)) {
 					unlink($upload_path."/".$dir."/".$file);
@@ -1097,23 +1101,23 @@ class File extends MY_Controller {
 		$total = $this->db->count_all("files");
 
 		for ($limit = 0; $limit < $total; $limit += $chunk) {
-			$query = $this->db->query("
-				SELECT hash
-				FROM files
-				GROUP BY hash
-				LIMIT $limit, $chunk
-				")->result_array();
+			$query = $this->db->select('hash')
+				->from('files')
+				->group_by('hash')
+				->limit($limit, $chunk)
+				->get()->result_array();
 
 			foreach ($query as $key => $item) {
 				$hash = $item["hash"];
 				$filesize = intval(filesize($this->mfile->file($hash)));
 				$mimetype = $this->mfile->mimetype($this->mfile->file($hash));
 
-				$this->db->query("
-					UPDATE files
-					SET filesize = ?, mimetype = ?
-					WHERE hash = ?
-					", array($filesize, $mimetype, $hash));
+				$this->db->where('hash', $hash)
+					->set(array(
+						'filesize' => $filesize,
+						'mimetype' => $mimetype,
+					))
+					->update('files');
 			}
 		}
 	}
-- 
cgit v1.2.3-24-g4f1b


From b8fa66e0d874bc5aa6ee719f6067b5365661833a Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 15:43:51 +0300
Subject: No column based encoding. Reverting dbforge migration

---
 application/migrations/001_add_files.php | 46 +++++++++++++++++++++-----------
 1 file changed, 31 insertions(+), 15 deletions(-)

(limited to 'application')

diff --git a/application/migrations/001_add_files.php b/application/migrations/001_add_files.php
index 8af202285..70efbb520 100644
--- a/application/migrations/001_add_files.php
+++ b/application/migrations/001_add_files.php
@@ -5,22 +5,38 @@ class Migration_Add_files extends CI_Migration {
 
 	public function up()
 	{
-		// Set database engine for MySQL drivers
-		if (strpos($this->db->dbdriver, 'mysql') !== FALSE) {
-			$this->db->query('SET storage_engine=MYISAM');
+		if ($this->db->dbdriver == 'postgre')
+		{
+			$this->db->query('
+				CREATE TABLE IF NOT EXISTS "files" (
+					"hash" varchar(32) NOT NULL,
+					"id" varchar(6) NOT NULL,
+					"filename" varchar(256) NOT NULL,
+					"password" varchar(40) DEFAULT NULL,
+					"date" integer NOT NULL,
+					"mimetype" varchar(255) NOT NULL,
+					PRIMARY KEY ("id")
+				);
+				CREATE INDEX "files_date_idx" ON files ("date");
+				CREATE INDEX "files_hash_id_idx" ON files ("hash", "id");
+			');
+		}
+		else
+		{
+			$this->db->query("
+				CREATE TABLE IF NOT EXISTS `files` (
+					`hash` varchar(32) CHARACTER SET ascii NOT NULL,
+					`id` varchar(6) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
+					`filename` varchar(256) COLLATE utf8_bin NOT NULL,
+					`password` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
+					`date` int(11) unsigned NOT NULL,
+					`mimetype` varchar(255) CHARACTER SET ascii NOT NULL,
+					PRIMARY KEY (`id`),
+					KEY `date` (`date`),
+					KEY `hash` (`hash`,`id`)
+				) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+			");
 		}
-
-		$this->dbforge->add_field([
-			'hash'     => [ 'type' => 'varchar', 'constraint' => 32, 'null' => FALSE ],
-			'id'       => [ 'type' => 'varchar', 'constraint' => 6, 'null' => FALSE ],
-			'filename' => [ 'type' => 'varchar', 'constraint' => 256, 'null' => FALSE ],
-			'password' => [ 'type' => 'varchar', 'constraint' => 40, 'null' => TRUE ],
-			'date'     => [ 'type' => 'integer', 'unsigned' => TRUE, 'null' => FALSE ],
-			'mimetype' => [ 'type' => 'varchar', 'constraint' => 255, 'null' => FALSE ],
-		]);
-		$this->dbforge->add_key('id', TRUE);
-		$this->dbforge->add_key([ 'hash', 'id' ]);
-		$this->dbforge->create_table('files', TRUE);
 	}
 
 	public function down()
-- 
cgit v1.2.3-24-g4f1b


From 701f6b08f7d983e364b3b97d7525d3e94f5b19d0 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 15:56:53 +0300
Subject: Correct unsupported open/close where query statements

---
 application/controllers/file.php | 4 +---
 1 file changed, 1 insertion(+), 3 deletions(-)

(limited to 'application')

diff --git a/application/controllers/file.php b/application/controllers/file.php
index d293310c5..96c3fb9c6 100644
--- a/application/controllers/file.php
+++ b/application/controllers/file.php
@@ -1004,9 +1004,7 @@ class File extends MY_Controller {
 		$query = $this->db->select('hash, id, user')
 			->from('files')
 			->where('date <', $oldest_time)
-			->or_where_open()
-			->where('user', 0)
-			->where('date <', $oldest_session_time)
+			->or_where("(user = 0 AND date < $oldest_session_time)")
 			->get()->result_array();
 
 		foreach($query as $row) {
-- 
cgit v1.2.3-24-g4f1b


From 5170adf992ba84fcced8bc271e603d1440f29509 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 16:06:47 +0300
Subject: apikeys.created should be timestamp with default now()

---
 application/migrations/009_add_apikeys.php | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'application')

diff --git a/application/migrations/009_add_apikeys.php b/application/migrations/009_add_apikeys.php
index bdc4dd07d..8f2882e49 100644
--- a/application/migrations/009_add_apikeys.php
+++ b/application/migrations/009_add_apikeys.php
@@ -10,7 +10,7 @@ class Migration_Add_apikeys extends CI_Migration {
 				CREATE TABLE "apikeys" (
 					"key" varchar(64) NOT NULL,
 					"user" integer NOT NULL,
-					"created" integer NOT NULL,
+					"created" timestamp WITH TIME ZONE NOT NULL DEFAULT NOW(),
 					"comment" varchar(255) NOT NULL,
 					PRIMARY KEY ("key")
 				);
-- 
cgit v1.2.3-24-g4f1b


From 1a007fc8e7bf71fc5e292929af00a15bcac6b2c2 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 17:00:19 +0300
Subject: Fix timestamp adjusting for a list of arrays

---
 application/controllers/user.php | 8 ++++++--
 1 file changed, 6 insertions(+), 2 deletions(-)

(limited to 'application')

diff --git a/application/controllers/user.php b/application/controllers/user.php
index 30e5b9fd7..a9e50d742 100644
--- a/application/controllers/user.php
+++ b/application/controllers/user.php
@@ -148,10 +148,14 @@ class User extends MY_Controller {
 			->get()->result_array();
 
 		// Convert timestamp to unix timestamp
-		if (isset($query['created']))
+		foreach ($query as & $record)
 		{
-			$query['created'] = strtotime($query['created']);
+			if ( ! empty($record['created']))
+			{
+				$record['created'] = strtotime($record['created']);
+			}
 		}
+		unset($record);
 
 		if (static_storage("response_type") == "json") {
 			return send_json_reply($query);
-- 
cgit v1.2.3-24-g4f1b


From 8082cc98e7ebc9a27dcf8a8a4e6ee24dc71af1f3 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 18:36:51 +0300
Subject: where_in for in array queries a proper count usage

---
 application/controllers/file.php | 2 +-
 application/controllers/user.php | 6 +++---
 2 files changed, 4 insertions(+), 4 deletions(-)

(limited to 'application')

diff --git a/application/controllers/file.php b/application/controllers/file.php
index 96c3fb9c6..ddb7a38cf 100644
--- a/application/controllers/file.php
+++ b/application/controllers/file.php
@@ -541,7 +541,7 @@ class File extends MY_Controller {
 			->select('id, filename, mimetype, date, hash, filesize')
 			->from('files')
 			->where('user', $user)
-			->where('mimetype', array('image/jpeg', 'image/png', 'image/gif'))
+			->where_in('mimetype', array('image/jpeg', 'image/png', 'image/gif'))
 			->order_by('date', 'desc')
 			->get()->result_array();
 
diff --git a/application/controllers/user.php b/application/controllers/user.php
index a9e50d742..57c6498b1 100644
--- a/application/controllers/user.php
+++ b/application/controllers/user.php
@@ -175,13 +175,13 @@ class User extends MY_Controller {
 
 		$userid = $this->muser->get_userid();
 
-		$query = $this->db->select('user')
-			->from('action')
+		$invitations = $this->db->select('user')
+			->from('actions')
 			->where('user', $userid)
 			->where('action', 'invitation')
 			->count_all_results();
 
-		if ($query["count"] + 1 > 3) {
+		if ($invitations + 1 > 3) {
 			show_error("You can't create more invitation keys at this time.");
 		}
 
-- 
cgit v1.2.3-24-g4f1b


From effdc57264f62c3422b91d295f29a8c4e77c3db5 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 18:37:40 +0300
Subject: WIP: Cascading delete

---
 application/models/mfile.php       | 41 +++++++++++++++++++++++++-------------
 application/models/mmultipaste.php | 19 ++++++++++++------
 2 files changed, 40 insertions(+), 20 deletions(-)

(limited to 'application')

diff --git a/application/models/mfile.php b/application/models/mfile.php
index 1d4340410..68f8ab299 100644
--- a/application/models/mfile.php
+++ b/application/models/mfile.php
@@ -327,13 +327,20 @@ class Mfile extends CI_Model {
 		// Note that this does not delete all relations in multipaste_file_map
 		// which is actually done by a SQL contraint.
 		// TODO: make it work properly without the constraint
-		$this->db->query('
-			DELETE m, mfm, f
-			FROM files f
-			LEFT JOIN multipaste_file_map mfm ON f.id = mfm.file_url_id
-			LEFT JOIN multipaste m ON mfm.multipaste_id = m.multipaste_id
-			WHERE f.id = ?
-			', array($id));
+		if (strpos($this->db->dbdriver, 'postgre') === FALSE) {
+			$this->db->query('
+				DELETE m, mfm, f
+				FROM files f
+				LEFT JOIN multipaste_file_map mfm ON f.id = mfm.file_url_id
+				LEFT JOIN multipaste m ON mfm.multipaste_id = m.multipaste_id
+				WHERE f.id = ?
+				', array($id));
+		} else {
+			// TODO.rafi: Deletes files + multipaste_file_map
+			//            but not a multipaste.
+			$this->db->where('id', $id)
+				->delete('files');
+		}
 
 		if ($this->id_exists($id))  {
 			return false;
@@ -358,13 +365,19 @@ class Mfile extends CI_Model {
 		// Note that this does not delete all relations in multipaste_file_map
 		// which is actually done by a SQL contraint.
 		// TODO: make it work properly without the constraint
-		$this->db->query('
-			DELETE m, mfm, f
-			FROM files f
-			LEFT JOIN multipaste_file_map mfm ON f.id = mfm.file_url_id
-			LEFT JOIN multipaste m ON mfm.multipaste_id = m.multipaste_id
-			WHERE f.hash = ?
-			', array($hash));
+		if (strpos($this->db->dbdriver, 'postgre') === FALSE) {
+			$this->db->query('
+				DELETE m, mfm, f
+				FROM files f
+				LEFT JOIN multipaste_file_map mfm ON f.id = mfm.file_url_id
+				LEFT JOIN multipaste m ON mfm.multipaste_id = m.multipaste_id
+				WHERE f.hash = ?
+				', array($hash));
+		} else {
+			// TODO.rafi: Test
+			$this->db->where('hash', $hash)
+				->delete('files');
+		}
 
 		if (file_exists($this->file($hash))) {
 			unlink($this->file($hash));
diff --git a/application/models/mmultipaste.php b/application/models/mmultipaste.php
index 723132a50..9b1a7b16e 100644
--- a/application/models/mmultipaste.php
+++ b/application/models/mmultipaste.php
@@ -90,12 +90,19 @@ class Mmultipaste extends CI_Model {
 
 	public function delete_id($id)
 	{
-		$this->db->query('
-			DELETE m, mfm
-			FROM multipaste m
-			LEFT JOIN multipaste_file_map mfm ON mfm.multipaste_id = m.multipaste_id
-			WHERE m.url_id = ?
-			', array($id));
+		if (strpos($this->db->dbdriver, 'postgre') === FALSE) {
+			$this->db->query('
+				DELETE m, mfm
+				FROM multipaste m
+				LEFT JOIN multipaste_file_map mfm ON mfm.multipaste_id = m.multipaste_id
+				WHERE m.url_id = ?
+				', array($id));
+		} else {
+			// TODO.rafi: Deletes multipaste + multipaste_file_map
+			//            but not files. Is it supposed to?
+			$this->db->where('url_id', $id)
+				->delete('multipaste');
+		}
 
 		if ($this->id_exists($id))  {
 			return false;
-- 
cgit v1.2.3-24-g4f1b


From 75b0a939c7ce24014a8db95a3355d2a7ffdfe3a9 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 18:39:28 +0300
Subject: Optimizing multipaste tables

---
 application/migrations/013_add_multipaste.php | 24 +++++++-----------------
 1 file changed, 7 insertions(+), 17 deletions(-)

(limited to 'application')

diff --git a/application/migrations/013_add_multipaste.php b/application/migrations/013_add_multipaste.php
index ef29effa9..96092b4ee 100644
--- a/application/migrations/013_add_multipaste.php
+++ b/application/migrations/013_add_multipaste.php
@@ -8,32 +8,22 @@ class Migration_add_multipaste extends CI_Migration {
 		if ($this->db->dbdriver == 'postgre') {
 			$this->db->query('
 				CREATE TABLE "multipaste" (
-					"url_id" varchar(255) NOT NULL,
-					"multipaste_id" serial,
+					"url_id" varchar(255) NOT NULL PRIMARY KEY,
+					"multipaste_id" serial UNIQUE,
 					"user_id" integer NOT NULL,
-					"date" integer NOT NULL,
-					PRIMARY KEY ("url_id")
+					"date" integer NOT NULL
 				);
 				CREATE INDEX "multipaste_user_idx" ON "multipaste" ("user_id");
-				CREATE UNIQUE INDEX "multipaste_id_idx" ON "multipaste" ("multipaste_id");
 			');
 
 			$this->db->query('
 				CREATE TABLE "multipaste_file_map" (
-					"multipaste_id" integer NOT NULL,
-					"file_url_id" varchar(255) NOT NULL,
-					"sort_order" serial,
-					PRIMARY KEY ("sort_order")
+					"multipaste_id" integer NOT NULL REFERENCES "multipaste" ("multipaste_id") ON DELETE CASCADE ON UPDATE CASCADE,
+					"file_url_id" varchar(255) NOT NULL REFERENCES "files"("id") ON DELETE CASCADE ON UPDATE CASCADE,
+					"sort_order" serial PRIMARY KEY,
+					UNIQUE ("multipaste_id", "file_url_id")
 				);
-				CREATE UNIQUE INDEX "multipaste_file_map_idx"
-					ON "multipaste_file_map" ("multipaste_id", "file_url_id");
 				CREATE INDEX "multipaste_file_map_file_idx" ON "multipaste_file_map" ("file_url_id");
-				ALTER TABLE "multipaste_file_map"
-					ADD CONSTRAINT "multipaste_file_map_id_fkey" FOREIGN KEY ("multipaste_id")
-						REFERENCES "multipaste"("multipaste_id") ON DELETE CASCADE ON UPDATE CASCADE;
-				ALTER TABLE "multipaste_file_map"
-					ADD CONSTRAINT "multipaste_file_map_file_id_fkey" FOREIGN KEY ("file_url_id")
-						REFERENCES "files"("id") ON DELETE CASCADE ON UPDATE CASCADE
 			');
 
 		} else {
-- 
cgit v1.2.3-24-g4f1b


From d98ba32e9717a5e325d439e785c967f8cc44d095 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 18:45:08 +0300
Subject: Fix user/register mistaken query handling

---
 application/models/muser.php | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'application')

diff --git a/application/models/muser.php b/application/models/muser.php
index 4f5931728..ffcc5f6b3 100644
--- a/application/models/muser.php
+++ b/application/models/muser.php
@@ -204,7 +204,7 @@ class Muser extends CI_Model {
 
 	function get_action($action, $key)
 	{
-		$db->from('actions')
+		$query = $this->db->from('actions')
 			->where('key', $key)
 			->where('action', $action)
 			->get()->row_array();
-- 
cgit v1.2.3-24-g4f1b


From 40fa09dcf09611afb34434a5c5c087b64f3fe8b6 Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Fri, 19 Sep 2014 19:31:11 +0300
Subject: Fixing multipaste delete queries

---
 application/models/mfile.php       | 56 +++++++++++++++++++++-----------------
 application/models/mmultipaste.php | 15 ++--------
 2 files changed, 33 insertions(+), 38 deletions(-)

(limited to 'application')

diff --git a/application/models/mfile.php b/application/models/mfile.php
index 68f8ab299..8dc4772d9 100644
--- a/application/models/mfile.php
+++ b/application/models/mfile.php
@@ -327,19 +327,17 @@ class Mfile extends CI_Model {
 		// Note that this does not delete all relations in multipaste_file_map
 		// which is actually done by a SQL contraint.
 		// TODO: make it work properly without the constraint
-		if (strpos($this->db->dbdriver, 'postgre') === FALSE) {
-			$this->db->query('
-				DELETE m, mfm, f
-				FROM files f
-				LEFT JOIN multipaste_file_map mfm ON f.id = mfm.file_url_id
-				LEFT JOIN multipaste m ON mfm.multipaste_id = m.multipaste_id
-				WHERE f.id = ?
-				', array($id));
-		} else {
-			// TODO.rafi: Deletes files + multipaste_file_map
-			//            but not a multipaste.
-			$this->db->where('id', $id)
-				->delete('files');
+		$map = $this->db->select('multipaste_id')
+			->from('multipaste_file_map')
+			->where('file_url_id', $id)
+			->get()->row_array();
+
+		$this->db->where('id', $id)
+			->delete('files');
+
+		if ( ! empty($map['multipaste_id'])) {
+			$this->db->where('multipaste_id', $map['multipaste_id'])
+				->delete('multipaste');
 		}
 
 		if ($this->id_exists($id))  {
@@ -365,18 +363,26 @@ class Mfile extends CI_Model {
 		// Note that this does not delete all relations in multipaste_file_map
 		// which is actually done by a SQL contraint.
 		// TODO: make it work properly without the constraint
-		if (strpos($this->db->dbdriver, 'postgre') === FALSE) {
-			$this->db->query('
-				DELETE m, mfm, f
-				FROM files f
-				LEFT JOIN multipaste_file_map mfm ON f.id = mfm.file_url_id
-				LEFT JOIN multipaste m ON mfm.multipaste_id = m.multipaste_id
-				WHERE f.hash = ?
-				', array($hash));
-		} else {
-			// TODO.rafi: Test
-			$this->db->where('hash', $hash)
-				->delete('files');
+		$file = $this->db->select('id')
+			->from('files')
+			->where('hash', $hash)
+			->get()->row_array();
+
+		if (empty($file['id'])) {
+			return false;
+		}
+
+		$map = $this->db->select('multipaste_id')
+			->from('multipaste_file_map')
+			->where('file_url_id', $file['id'])
+			->get()->row_array();
+
+		$this->db->where('hash', $hash)
+			->delete('files');
+
+		if ( ! empty($map['multipaste_id'])) {
+			$this->db->where('multipaste_id', $map['multipaste_id'])
+				->delete('multipaste');
 		}
 
 		if (file_exists($this->file($hash))) {
diff --git a/application/models/mmultipaste.php b/application/models/mmultipaste.php
index 9b1a7b16e..367e74787 100644
--- a/application/models/mmultipaste.php
+++ b/application/models/mmultipaste.php
@@ -90,19 +90,8 @@ class Mmultipaste extends CI_Model {
 
 	public function delete_id($id)
 	{
-		if (strpos($this->db->dbdriver, 'postgre') === FALSE) {
-			$this->db->query('
-				DELETE m, mfm
-				FROM multipaste m
-				LEFT JOIN multipaste_file_map mfm ON mfm.multipaste_id = m.multipaste_id
-				WHERE m.url_id = ?
-				', array($id));
-		} else {
-			// TODO.rafi: Deletes multipaste + multipaste_file_map
-			//            but not files. Is it supposed to?
-			$this->db->where('url_id', $id)
-				->delete('multipaste');
-		}
+		$this->db->where('url_id', $id)
+			->delete('multipaste');
 
 		if ($this->id_exists($id))  {
 			return false;
-- 
cgit v1.2.3-24-g4f1b


From 4edab80a15cad1a479d110f6b7e782e1b434763d Mon Sep 17 00:00:00 2001
From: Rafael Bodill <rafi@sortex.co.il>
Date: Sun, 28 Sep 2014 20:23:13 +0300
Subject: file/cron: Protecting identifiers

---
 application/controllers/file.php | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

(limited to 'application')

diff --git a/application/controllers/file.php b/application/controllers/file.php
index ddb7a38cf..329a0bdf7 100644
--- a/application/controllers/file.php
+++ b/application/controllers/file.php
@@ -1004,7 +1004,8 @@ class File extends MY_Controller {
 		$query = $this->db->select('hash, id, user')
 			->from('files')
 			->where('date <', $oldest_time)
-			->or_where("(user = 0 AND date < $oldest_session_time)")
+			->or_where('('.$this->db->_protect_identifiers('user').' = 0 AND '
+									.$this->db->_protect_identifiers('date')." < $oldest_session_time)")
 			->get()->result_array();
 
 		foreach($query as $row) {
-- 
cgit v1.2.3-24-g4f1b