From bd4ecd3db3444cd1c67f5d2bb4bffc83564b8c50 Mon Sep 17 00:00:00 2001 From: Rafael Bodill Date: Fri, 19 Sep 2014 00:42:45 +0300 Subject: Integrating query builder in models --- application/models/mfile.php | 52 ++++++++++++++++++++------------------------ 1 file changed, 24 insertions(+), 28 deletions(-) (limited to 'application/models/mfile.php') 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() { -- cgit v1.2.3-24-g4f1b From effdc57264f62c3422b91d295f29a8c4e77c3db5 Mon Sep 17 00:00:00 2001 From: Rafael Bodill Date: Fri, 19 Sep 2014 18:37:40 +0300 Subject: WIP: Cascading delete --- application/models/mfile.php | 41 +++++++++++++++++++++++++++-------------- 1 file changed, 27 insertions(+), 14 deletions(-) (limited to 'application/models/mfile.php') 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)); -- cgit v1.2.3-24-g4f1b From 40fa09dcf09611afb34434a5c5c087b64f3fe8b6 Mon Sep 17 00:00:00 2001 From: Rafael Bodill Date: Fri, 19 Sep 2014 19:31:11 +0300 Subject: Fixing multipaste delete queries --- application/models/mfile.php | 56 ++++++++++++++++++++++++-------------------- 1 file changed, 31 insertions(+), 25 deletions(-) (limited to 'application/models/mfile.php') 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))) { -- cgit v1.2.3-24-g4f1b From 1abe7372404a9d65f3b59eda2d83e628267b366d Mon Sep 17 00:00:00 2001 From: Florian Pritz Date: Sun, 19 Oct 2014 23:01:51 +0200 Subject: Clean up the postgres changes Style cleanup and some regression fixes Signed-off-by: Florian Pritz --- application/models/mfile.php | 16 ++++++++++------ 1 file changed, 10 insertions(+), 6 deletions(-) (limited to 'application/models/mfile.php') diff --git a/application/models/mfile.php b/application/models/mfile.php index be315b9e6..539092887 100644 --- a/application/models/mfile.php +++ b/application/models/mfile.php @@ -219,15 +219,17 @@ class Mfile extends CI_Model { // which is actually done by a SQL contraint. // TODO: make it work properly without the constraint $map = $this->db->select('multipaste_id') + ->distinct() ->from('multipaste_file_map') ->where('file_url_id', $id) - ->get()->row_array(); + ->get()->result_array(); $this->db->where('id', $id) ->delete('files'); - if ( ! empty($map['multipaste_id'])) { - $this->db->where('multipaste_id', $map['multipaste_id']) + foreach ($map as $entry) { + assert(!empty($entry['multipaste_id'])); + $this->db->where('multipaste_id', $entry['multipaste_id']) ->delete('multipaste'); } @@ -264,15 +266,17 @@ class Mfile extends CI_Model { } $map = $this->db->select('multipaste_id') + ->distinct() ->from('multipaste_file_map') ->where('file_url_id', $file['id']) - ->get()->row_array(); + ->get()->result_array(); $this->db->where('hash', $hash) ->delete('files'); - if ( ! empty($map['multipaste_id'])) { - $this->db->where('multipaste_id', $map['multipaste_id']) + foreach ($map as $entry) { + assert(!empty($entry['multipaste_id'])); + $this->db->where('multipaste_id', $entry['multipaste_id']) ->delete('multipaste'); } -- cgit v1.2.3-24-g4f1b