diff options
19 files changed, 601 insertions, 366 deletions
diff --git a/application/controllers/file.php b/application/controllers/file.php index a9c1ef12a..b81900af2 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..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/migrations/001_add_files.php b/application/migrations/001_add_files.php index f1f16ea3a..70efbb520 100644 --- a/application/migrations/001_add_files.php +++ b/application/migrations/001_add_files.php @@ -5,19 +5,38 @@ 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; - "); + 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; + "); + } } public function down() diff --git a/application/migrations/002_add_users.php b/application/migrations/002_add_users.php index 5675c77e9..322415d9b 100644 --- a/application/migrations/002_add_users.php +++ b/application/migrations/002_add_users.php @@ -5,42 +5,73 @@ 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; - "); - - $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`) - "); + 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" 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" 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..9ca167eab 100644 --- a/application/migrations/003_add_referrers.php +++ b/application/migrations/003_add_referrers.php @@ -5,28 +5,53 @@ 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..f2367937a 100644 --- a/application/migrations/004_add_filesize.php +++ b/application/migrations/004_add_filesize.php @@ -5,18 +5,29 @@ 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..e8b7f8952 100644 --- a/application/migrations/005_drop_file_password.php +++ b/application/migrations/005_drop_file_password.php @@ -5,17 +5,25 @@ 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..5b8c3584f 100644 --- a/application/migrations/006_add_username_index.php +++ b/application/migrations/006_add_username_index.php @@ -5,17 +5,27 @@ 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..024b62984 100644 --- a/application/migrations/007_repurpose_invitations.php +++ b/application/migrations/007_repurpose_invitations.php @@ -5,33 +5,61 @@ 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..9958fb03e 100644 --- a/application/migrations/008_add_profiles.php +++ b/application/migrations/008_add_profiles.php @@ -5,27 +5,53 @@ 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" ALTER COLUMN "id" TYPE varchar(255); + '); - $this->db->query(" - ALTER TABLE `files` CHANGE `id` `id` 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..8f2882e49 100644 --- a/application/migrations/009_add_apikeys.php +++ b/application/migrations/009_add_apikeys.php @@ -5,16 +5,31 @@ 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" timestamp WITH TIME ZONE NOT NULL DEFAULT NOW(), + "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..318314bd3 100644 --- a/application/migrations/010_files_innodb.php +++ b/application/migrations/010_files_innodb.php @@ -5,9 +5,11 @@ 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..2fdbc7271 100644 --- a/application/migrations/011_apikeys_add_access_level.php +++ b/application/migrations/011_apikeys_add_access_level.php @@ -5,15 +5,27 @@ 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..1ed4abf08 100644 --- a/application/migrations/012_add_constraints.php +++ b/application/migrations/012_add_constraints.php @@ -5,12 +5,26 @@ 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..96092b4ee 100644 --- a/application/migrations/013_add_multipaste.php +++ b/application/migrations/013_add_multipaste.php @@ -5,28 +5,52 @@ 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 PRIMARY KEY, + "multipaste_id" serial UNIQUE, + "user_id" integer NOT NULL, + "date" integer NOT NULL + ); + CREATE INDEX "multipaste_user_idx" ON "multipaste" ("user_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 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 INDEX "multipaste_file_map_file_idx" ON "multipaste_file_map" ("file_url_id"); + '); + + } 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() diff --git a/application/models/mfile.php b/application/models/mfile.php index f23855bfd..be315b9e6 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(); @@ -125,11 +124,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 @@ -200,12 +197,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; @@ -222,13 +218,18 @@ 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)); + $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)) { return false; @@ -253,13 +254,27 @@ 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)); + $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))) { unlink($this->file($hash)); @@ -273,11 +288,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..367e74787 100644 --- a/application/models/mmultipaste.php +++ b/application/models/mmultipaste.php @@ -90,12 +90,8 @@ 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)); + $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); |