Merge postgresql support

Signed-off-by: Florian Pritz <bluewind@xinu.at>

2 files changed, 116 insertions, 110 deletions

diff --git a/application/controllers/file.php b/application/controllers/file.php
index a9c1ef12a..62cf342b1 100644
--- a/application/controllers/file.php
+++ b/application/controllers/file.php
@@ -540,13 +540,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"])) {
@@ -590,11 +590,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
@@ -634,10 +633,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();
 
@@ -1007,13 +1005,14 @@ 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('('.$this->db->_protect_identifiers('user').' = 0 AND '
+			             .$this->db->_protect_identifiers('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"]);
@@ -1056,7 +1055,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);
@@ -1101,23 +1104,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..b542f2b4c 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,20 @@ 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
+		// TODO: migrate database to integer timestamp and get rid of this
+		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 +174,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 +204,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 +256,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 +325,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 +387,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', array(
+						'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 +513,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');
 	}
 }