Merge branch 'pgsql_controllers'

* pgsql_controllers:
  Fix user/register mistaken query handling
  WIP: Cascading delete
  where_in for in array queries a proper count usage
  Fix timestamp adjusting for a list of arrays
  Correct unsupported open/close where query statements
  File controller uses query builder, except 2 queries
  Integrating query builder in models
  User controller queries built dynamically
  Query builder in user login and controller

6 files changed, 210 insertions, 196 deletions

diff --git a/application/controllers/file.php b/application/controllers/file.php
index e1b43d314..ddb7a38cf 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_in('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,13 @@ 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("(user = 0 AND 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 +1050,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 +1099,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');
 			}
 		}
 	}
diff --git a/application/controllers/user.php b/application/controllers/user.php
index 079f1665c..57c6498b1 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(array(
+				'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));
@@ -126,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");
 	}
@@ -141,11 +141,21 @@ 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
+		foreach ($query as & $record)
+		{
+			if ( ! empty($record['created']))
+			{
+				$record['created'] = strtotime($record['created']);
+			}
+		}
+		unset($record);
 
 		if (static_storage("response_type") == "json") {
 			return send_json_reply($query);
@@ -165,24 +175,25 @@ 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();
+		$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.");
 		}
 
 		$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");
 	}
@@ -194,12 +205,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;
 
@@ -247,20 +257,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);
@@ -319,27 +326,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"]);
@@ -381,15 +388,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);
@@ -508,9 +514,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');
 	}
 }
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");
diff --git a/application/models/mfile.php b/application/models/mfile.php
index 1f5409ec3..68f8ab299 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;
@@ -331,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;
@@ -362,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));
@@ -382,11 +391,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/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;
diff --git a/application/models/muser.php b/application/models/muser.php
index a1d8f18e5..ffcc5f6b3 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();
+		$query = $this->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);