From 2b3a08e3dd5bf936d6d76a5f5282933e3ca6aeea Mon Sep 17 00:00:00 2001 From: Marien Fressinaud Date: Wed, 24 Oct 2012 00:15:30 +0200 Subject: Passage à du stockage en base de données MySQL MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- app/configuration/application.ini | 8 +- app/controllers/configureController.php | 2 - app/controllers/entryController.php | 15 +-- app/controllers/feedController.php | 123 ++++++++++-------------- app/controllers/indexController.php | 14 +-- app/models/Category.php | 107 +++++++++++++-------- app/models/Entry.php | 164 +++++++++++++++++++++++--------- app/models/Feed.php | 117 ++++++++++++----------- app/views/configure/flux.phtml | 2 +- 9 files changed, 314 insertions(+), 238 deletions(-) diff --git a/app/configuration/application.ini b/app/configuration/application.ini index 4196c1a70..93895ad0e 100644 --- a/app/configuration/application.ini +++ b/app/configuration/application.ini @@ -9,7 +9,7 @@ language = "fr" cache_enabled = false ; n'influe pas sur le cache de SimplePie [db] -host = "" -user = "" -password = "" -base = "" +host = "localhost" +user = "bd" +password = "bede" +base = "marienfr_rss" diff --git a/app/controllers/configureController.php b/app/controllers/configureController.php index 00bc571da..b382b2794 100755 --- a/app/controllers/configureController.php +++ b/app/controllers/configureController.php @@ -32,8 +32,6 @@ class configureController extends ActionController { $catDAO->addCategory ($values); } - $catDAO->save (); - } $this->view->categories = $catDAO->listCategories (); diff --git a/app/controllers/entryController.php b/app/controllers/entryController.php index a99038068..753994a2b 100755 --- a/app/controllers/entryController.php +++ b/app/controllers/entryController.php @@ -24,19 +24,14 @@ class entryController extends ActionController { $is_read = false; } + $values = array ( + 'is_read' => $is_read, + ); + $entryDAO = new EntryDAO (); if ($id == false) { - $entries = $entryDAO->listEntries ('not_read'); + $entryDAO->updateEntries ($values); } else { - $entry = $entryDAO->searchById ($id); - $entries = $entry !== false ? array ($entry) : array (); - } - - foreach ($entries as $entry) { - $values = array ( - 'is_read' => $is_read, - ); - $entryDAO->updateEntry ($entry->id (), $values); } } diff --git a/app/controllers/feedController.php b/app/controllers/feedController.php index 789d2a901..6dad1ac08 100755 --- a/app/controllers/feedController.php +++ b/app/controllers/feedController.php @@ -8,29 +8,24 @@ class feedController extends ActionController { try { $feed = new Feed ($url); $feed->load (); - $entries = $feed->entries (false); - $feed_entries = array (); + $entries = $feed->entries (); - if ($entries !== false) { - $entryDAO = new EntryDAO (); - - foreach ($entries as $entry) { - $values = array ( - 'id' => $entry->id (), - 'guid' => $entry->guid (), - 'title' => $entry->title (), - 'author' => $entry->author (), - 'content' => $entry->content (), - 'link' => $entry->link (), - 'date' => $entry->date (true), - 'is_read' => $entry->isRead (), - 'is_favorite' => $entry->isFavorite (), - 'feed' => $feed->id () - ); - $entryDAO->addEntry ($values); - - $feed_entries[] = $entry->id (); - } + $entryDAO = new EntryDAO (); + + foreach ($entries as $entry) { + $values = array ( + 'id' => $entry->id (), + 'guid' => $entry->guid (), + 'title' => $entry->title (), + 'author' => $entry->author (), + 'content' => $entry->content (), + 'link' => $entry->link (), + 'date' => $entry->date (true), + 'is_read' => $entry->isRead (), + 'is_favorite' => $entry->isFavorite (), + 'id_feed' => $feed->id () + ); + $entryDAO->addEntry ($values); } $feedDAO = new FeedDAO (); @@ -38,7 +33,6 @@ class feedController extends ActionController { 'id' => $feed->id (), 'url' => $feed->url (), 'category' => $feed->category (), - 'entries' => $feed_entries, 'name' => $feed->name (), 'website' => $feed->website (), 'description' => $feed->description (), @@ -60,37 +54,25 @@ class feedController extends ActionController { foreach ($feeds as $feed) { $feed->load (); - $entries = $feed->entries (false); - $feed_entries = $feed->entries (); + $entries = $feed->entries (); + + foreach ($entries as $entry) { + $values = array ( + 'id' => $entry->id (), + 'guid' => $entry->guid (), + 'title' => $entry->title (), + 'author' => $entry->author (), + 'content' => $entry->content (), + 'link' => $entry->link (), + 'date' => $entry->date (true), + 'is_read' => $entry->isRead (), + 'is_favorite' => $entry->isFavorite (), + 'id_feed' => $feed->id () + ); + $entryDAO->addEntry ($values); - if ($entries !== false) { - foreach ($entries as $entry) { - if (!in_array ($entry->id (), $feed_entries)) { - $values = array ( - 'id' => $entry->id (), - 'guid' => $entry->guid (), - 'title' => $entry->title (), - 'author' => $entry->author (), - 'content' => $entry->content (), - 'link' => $entry->link (), - 'date' => $entry->date (true), - 'is_read' => $entry->isRead (), - 'is_favorite' => $entry->isFavorite (), - 'feed' => $feed->id () - ); - $entryDAO->addEntry ($values); - - $feed_entries[] = $entry->id (); - } - - // TODO gérer suppression des articles trop vieux (à paramétrer) - } + // TODO gérer suppression des articles trop vieux (à paramétrer) } - - $values = array ( - 'entries' => $feed_entries - ); - $feedDAO->updateFeed ($values); } Request::forward (array (), true); @@ -111,33 +93,27 @@ class feedController extends ActionController { 'color' => $cat->color () ); $catDAO->addCategory ($values); - $catDAO->save (); } foreach ($feeds as $feed) { $feed->load (); - $entries = $feed->entries (false); - $feed_entries = array (); + $entries = $feed->entries (); // Chargement du flux - if ($entries !== false) { - foreach ($entries as $entry) { - $values = array ( - 'id' => $entry->id (), - 'guid' => $entry->guid (), - 'title' => $entry->title (), - 'author' => $entry->author (), - 'content' => $entry->content (), - 'link' => $entry->link (), - 'date' => $entry->date (true), - 'is_read' => $entry->isRead (), - 'is_favorite' => $entry->isFavorite (), - 'feed' => $feed->id () - ); - $entryDAO->addEntry ($values); - - $feed_entries[] = $entry->id (); - } + foreach ($entries as $entry) { + $values = array ( + 'id' => $entry->id (), + 'guid' => $entry->guid (), + 'title' => $entry->title (), + 'author' => $entry->author (), + 'content' => $entry->content (), + 'link' => $entry->link (), + 'date' => $entry->date (true), + 'is_read' => $entry->isRead (), + 'is_favorite' => $entry->isFavorite (), + 'id_feed' => $feed->id () + ); + $entryDAO->addEntry ($values); } // Enregistrement du flux @@ -145,7 +121,6 @@ class feedController extends ActionController { 'id' => $feed->id (), 'url' => $feed->url (), 'category' => $feed->category (), - 'entries' => $feed_entries, 'name' => $feed->name (), 'website' => $feed->website (), 'description' => $feed->description (), diff --git a/app/controllers/indexController.php b/app/controllers/indexController.php index b8908dee3..e7e3797ef 100755 --- a/app/controllers/indexController.php +++ b/app/controllers/indexController.php @@ -7,25 +7,19 @@ class indexController extends ActionController { $mode = Session::param ('mode', $this->view->conf->defaultView ()); $get = Request::param ('get'); + $order = $this->view->conf->sortOrder (); // Récupère les flux par catégorie, favoris ou tous if ($get == 'favoris') { - $entries = $entryDAO->listFavorites ($mode); + $entries = $entryDAO->listFavorites ($mode, $order); } elseif ($get != false) { - $entries = $entryDAO->listByCategory ($get, $mode); + $entries = $entryDAO->listByCategory ($get, $mode, $order); } // Cas où on ne choisie ni catégorie ni les favoris // ou si la catégorie ne correspond à aucune if (!isset ($entries)) { - $entries = $entryDAO->listEntries ($mode); - } - - // Tri par date - if ($this->view->conf->sortOrder () == 'high_to_low') { - usort ($entries, 'sortReverseEntriesByDate'); - } else { - usort ($entries, 'sortEntriesByDate'); + $entries = $entryDAO->listEntries ($mode, $order); } // Gestion pagination diff --git a/app/models/Category.php b/app/models/Category.php index 5b5d45b15..e0b8f564d 100755 --- a/app/models/Category.php +++ b/app/models/Category.php @@ -1,7 +1,7 @@ name . Configuration::selApplication ()); + if (!$this->id) { + return small_hash ($this->name . Configuration::selApplication ()); + } else { + return $this->id; + } } public function name () { return $this->name; @@ -20,6 +24,9 @@ class Category extends Model { return $this->color; } + public function _id ($value) { + $this->id = $value; + } public function _name ($value) { $this->name = $value; } @@ -32,64 +39,86 @@ class Category extends Model { } } -class CategoryDAO extends Model_array { - public function __construct () { - parent::__construct (PUBLIC_PATH . '/data/db/Categories.array.php'); - } - - public function addCategory ($values) { - $id = $values['id']; - unset ($values['id']); - - if (!isset ($this->array[$id])) { - $this->array[$id] = array (); - - foreach ($values as $key => $value) { - $this->array[$id][$key] = $value; - } +class CategoryDAO extends Model_pdo { + public function addCategory ($valuesTmp) { + $sql = 'INSERT INTO category (id, name, color) VALUES(?, ?, ?)'; + $stm = $this->bd->prepare ($sql); + + $values = array ( + $valuesTmp['id'], + $valuesTmp['name'], + $valuesTmp['color'], + ); + + if ($stm && $stm->execute ($values)) { + return true; } else { return false; } } - public function updateCategory ($id, $values) { - foreach ($values as $key => $value) { - $this->array[$id][$key] = $value; + public function updateCategory ($id, $valuesTmp) { + $sql = 'UPDATE category SET name=?, color=? WHERE id=?'; + $stm = $this->bd->prepare ($sql); + + $values = array ( + $valuesTmp['name'], + $valuesTmp['color'], + $id + ); + + if ($stm && $stm->execute ($values)) { + return true; + } else { + return false; } } public function deleteCategory ($id) { - if (isset ($this->array[$id])) { - unset ($this->array[$id]); + $sql = 'DELETE FROM category WHERE id=?'; + $stm = $this->bd->prepare ($sql); + + $values = array ($id); + + if ($stm && $stm->execute ($values)) { + return true; + } else { + return false; } } public function searchById ($id) { - $list = HelperCategory::daoToCategory ($this->array); + $sql = 'SELECT * FROM category WHERE id=?'; + $stm = $this->bd->prepare ($sql); - if (isset ($list[$id])) { - return $list[$id]; + $values = array ($id); + + $stm->execute ($values); + $res = $stm->fetchAll (PDO::FETCH_ASSOC); + $cat = HelperCategory::daoToCategory ($res); + + if (isset ($cat[0])) { + return $cat[0]; } else { return false; } } public function listCategories () { - $list = $this->array; - - if (!is_array ($list)) { - $list = array (); - } - - return HelperCategory::daoToCategory ($list); + $sql = 'SELECT * FROM category'; + $stm = $this->bd->prepare ($sql); + $stm->execute (); + + return HelperCategory::daoToCategory ($stm->fetchAll (PDO::FETCH_ASSOC)); } public function count () { - return count ($this->array); - } - - public function save () { - $this->writeFile ($this->array); + $sql = 'SELECT COUNT (*) AS count FROM category'; + $stm = $this->bd->prepare ($sql); + $stm->execute (); + $res = $stm->fetchAll (PDO::FETCH_ASSOC); + + return $res[0]['count']; } } @@ -102,10 +131,12 @@ class HelperCategory { } foreach ($listDAO as $key => $dao) { - $list[$key] = new Category ( + $cat = new Category ( $dao['name'], $dao['color'] ); + $cat->_id ($dao['id']); + $list[$key] = $cat; } return $list; diff --git a/app/models/Entry.php b/app/models/Entry.php index dbbdc1362..437aa8050 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -93,88 +93,160 @@ class Entry extends Model { } } -class EntryDAO extends Model_array { - public function __construct () { - parent::__construct (PUBLIC_PATH . '/data/db/Entries.array.php'); +class EntryDAO extends Model_pdo { + public function addEntry ($valuesTmp) { + $sql = 'INSERT INTO entry (id, guid, title, author, content, link, date, is_read, is_favorite, id_feed) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'; + $stm = $this->bd->prepare ($sql); + + $values = array ( + $valuesTmp['id'], + $valuesTmp['guid'], + $valuesTmp['title'], + $valuesTmp['author'], + $valuesTmp['content'], + $valuesTmp['link'], + $valuesTmp['date'], + $valuesTmp['is_read'], + $valuesTmp['is_favorite'], + $valuesTmp['id_feed'], + ); + + if ($stm && $stm->execute ($values)) { + return true; + } else { + return false; + } } - public function addEntry ($values) { - $id = $values['id']; - unset ($values['id']); - - if (!isset ($this->array[$id])) { - $this->array[$id] = array (); + public function updateEntry ($id, $valuesTmp) { + $set = ''; + foreach ($valuesTmp as $key => $v) { + $set .= $key . '=?, '; + } + $set = substr ($set, 0, -2); - foreach ($values as $key => $value) { - $this->array[$id][$key] = $value; - } + $sql = 'UPDATE entry SET ' . $set . ' WHERE id=?'; + $stm = $this->bd->prepare ($sql); - $this->writeFile ($this->array); + foreach ($valuesTmp as $v) { + $values[] = $v; + } + $values[] = $id; + + if ($stm && $stm->execute ($values)) { + return true; } else { return false; } } - public function updateEntry ($id, $values) { - foreach ($values as $key => $value) { - $this->array[$id][$key] = $value; + public function updateEntries ($valuesTmp) { + $set = ''; + foreach ($valuesTmp as $key => $v) { + $set .= $key . '=?, '; } + $set = substr ($set, 0, -2); + + $sql = 'UPDATE entry SET ' . $set; + $stm = $this->bd->prepare ($sql); - $this->writeFile($this->array); + foreach ($valuesTmp as $v) { + $values[] = $v; + } + + if ($stm && $stm->execute ($values)) { + return true; + } else { + return false; + } } public function searchById ($id) { - $list = HelperEntry::daoToEntry ($this->array); + $sql = 'SELECT * FROM entry WHERE id=?'; + $stm = $this->bd->prepare ($sql); + + $values = array ($id); + + $stm->execute ($values); + $res = $stm->fetchAll (PDO::FETCH_ASSOC); + $entry = HelperEntry::daoToEntry ($res); - if (isset ($list[$id])) { - return $list[$id]; + if (isset ($entry[0])) { + return $entry[0]; } else { return false; } } - public function listEntries ($mode) { - $list = $this->array; + public function listEntries ($mode, $order = 'high_to_low') { + $where = ''; + if ($mode == 'not_read') { + $where = ' WHERE is_read=0'; + } - if (!is_array ($list)) { - $list = array (); + if ($order == 'low_to_high') { + $order = ' DESC'; + } else { + $order = ''; } - return HelperEntry::daoToEntry ($list, $mode); + $sql = 'SELECT * FROM entry' . $where . ' ORDER BY date' . $order; + $stm = $this->bd->prepare ($sql); + $stm->execute (); + + return HelperEntry::daoToEntry ($stm->fetchAll (PDO::FETCH_ASSOC)); } - public function listFavorites ($mode) { - $list = $this->array; + public function listFavorites ($mode, $order = 'high_to_low') { + $where = ' WHERE is_favorite=1'; + if ($mode == 'not_read') { + $where .= ' AND is_read=0'; + } - if (!is_array ($list)) { - $list = array (); + if ($order == 'low_to_high') { + $order = ' DESC'; + } else { + $order = ''; } - return HelperEntry::daoToEntry ($list, $mode, true); + $sql = 'SELECT * FROM entry' . $where . ' ORDER BY date' . $order; + $stm = $this->bd->prepare ($sql); + + $stm->execute (); + + return HelperEntry::daoToEntry ($stm->fetchAll (PDO::FETCH_ASSOC)); } - public function listByCategory ($cat, $mode) { - $feedDAO = new FeedDAO (); - $feeds = $feedDAO->listByCategory ($cat); + public function listByCategory ($cat, $mode, $order = 'high_to_low') { + $where = ' WHERE category=?'; + if ($mode == 'not_read') { + $where .= ' AND is_read=0'; + } - $list = array (); - foreach ($feeds as $feed) { - foreach ($feed->entries () as $id) { - if (isset ($this->array[$id])) { - $list[$id] = $this->array[$id]; - } - } + if ($order == 'low_to_high') { + $order = ' DESC'; + } else { + $order = ''; } - return HelperEntry::daoToEntry ($list, $mode); - } - - public function listNotReadEntries () { + $sql = 'SELECT * FROM entry e INNER JOIN feed f ON e.id_feed = f.id' . $where . ' ORDER BY date' . $order; + + $stm = $this->bd->prepare ($sql); + + $values = array ($cat); + + $stm->execute ($values); + return HelperEntry::daoToEntry ($stm->fetchAll (PDO::FETCH_ASSOC)); } public function count () { - return count ($this->array); + $sql = 'SELECT COUNT (*) AS count FROM entry'; + $stm = $this->bd->prepare ($sql); + $stm->execute (); + $res = $stm->fetchAll (PDO::FETCH_ASSOC); + + return $res[0]['count']; } } @@ -190,7 +262,7 @@ class HelperEntry { if (($mode != 'not_read' || !$dao['is_read']) && ($favorite == false || $dao['is_favorite'])) { $list[$key] = new Entry ( - $dao['feed'], + $dao['id_feed'], $dao['guid'], $dao['title'], $dao['author'], diff --git a/app/models/Feed.php b/app/models/Feed.php index 90656de15..8a3f826bd 100644 --- a/app/models/Feed.php +++ b/app/models/Feed.php @@ -3,7 +3,6 @@ class Feed extends Model { private $url; private $category = ''; - private $entries_list = array (); private $entries = null; private $name = ''; private $website = ''; @@ -22,13 +21,11 @@ class Feed extends Model { public function category () { return $this->category; } - public function entries ($list = true) { - if ($list) { - return $this->entries_list; - } elseif (!is_null ($this->entries)) { + public function entries () { + if (!is_null ($this->entries)) { return $this->entries; } else { - return false; + return array (); } } public function name () { @@ -51,13 +48,6 @@ class Feed extends Model { public function _category ($value) { $this->category = $value; } - public function _entries ($value) { - if (!is_array ($value)) { - $value = array ($value); - } - - $this->entries_list = $value; - } public function _name ($value) { $this->name = $value; } @@ -109,70 +99,92 @@ class Feed extends Model { } } -class FeedDAO extends Model_array { - public function __construct () { - parent::__construct (PUBLIC_PATH . '/data/db/Feeds.array.php'); - } - - public function addFeed ($values) { - $id = $values['id']; - unset ($values['id']); - - if (!isset ($this->array[$id])) { - $this->array[$id] = array (); - - foreach ($values as $key => $value) { - $this->array[$id][$key] = $value; - } - - $this->writeFile ($this->array); +class FeedDAO extends Model_pdo { + public function addFeed ($valuesTmp) { + $sql = 'INSERT INTO feed (id, url, category, name, website, description) VALUES(?, ?, ?, ?, ?, ?)'; + $stm = $this->bd->prepare ($sql); + + $values = array ( + $valuesTmp['id'], + $valuesTmp['url'], + $valuesTmp['category'], + $valuesTmp['name'], + $valuesTmp['website'], + $valuesTmp['description'], + ); + + if ($stm && $stm->execute ($values)) { + return true; } else { return false; } } - public function updateFeed ($id, $values) { - foreach ($values as $key => $value) { - $this->array[$id][$key] = $value; + public function updateFeed ($id, $valuesTmp) { + $set = ''; + foreach ($valuesTmp as $key => $v) { + $set .= $key . '=?, '; } + $set = substr ($set, 0, -2); - $this->writeFile($this->array); + $sql = 'UPDATE feed SET ' . $set . ' WHERE id=?'; + $stm = $this->bd->prepare ($sql); + + $values = array_merge ( + $valuesTmp, + array ($id) + ); + + if ($stm && $stm->execute ($values)) { + return true; + } else { + return false; + } } public function searchById ($id) { - $list = HelperFeed::daoToFeed ($this->array); + $sql = 'SELECT * FROM feed WHERE id=?'; + $stm = $this->bd->prepare ($sql); + + $values = array ($id); - if (isset ($list[$id])) { - return $list[$id]; + $stm->execute ($values); + $res = $stm->fetchAll (PDO::FETCH_ASSOC); + $feed = HelperFeed::daoToFeed ($res); + + if (isset ($feed[0])) { + return $feed[0]; } else { return false; } } public function listFeeds () { - $list = $this->array; - - if (!is_array ($list)) { - $list = array (); - } - - return HelperFeed::daoToFeed ($list); + $sql = 'SELECT * FROM feed'; + $stm = $this->bd->prepare ($sql); + $stm->execute (); + + return HelperFeed::daoToFeed ($stm->fetchAll (PDO::FETCH_ASSOC)); } public function listByCategory ($cat) { - $list = array (); + $sql = 'SELECT * FROM feed WHERE category=?'; + $stm = $this->bd->prepare ($sql); - foreach ($this->array as $key => $feed) { - if ($feed['category'] == $cat) { - $list[$key] = $feed; - } - } + $values = array ($cat); - return HelperFeed::daoToFeed ($list); + $stm->execute ($values); + + return HelperFeed::daoToFeed ($stm->fetchAll (PDO::FETCH_ASSOC)); } public function count () { - return count ($this->array); + $sql = 'SELECT COUNT (*) AS count FROM feed'; + $stm = $this->bd->prepare ($sql); + $stm->execute (); + $res = $stm->fetchAll (PDO::FETCH_ASSOC); + + return $res[0]['count']; } } @@ -187,7 +199,6 @@ class HelperFeed { foreach ($listDAO as $key => $dao) { $list[$key] = new Feed ($dao['url']); $list[$key]->_category ($dao['category']); - $list[$key]->_entries ($dao['entries']); $list[$key]->_name ($dao['name']); $list[$key]->_website ($dao['website']); $list[$key]->_description ($dao['description']); diff --git a/app/views/configure/flux.phtml b/app/views/configure/flux.phtml index 34255bc59..d21230895 100644 --- a/app/views/configure/flux.phtml +++ b/app/views/configure/flux.phtml @@ -23,7 +23,7 @@ flux->website (); ?> - flux->entries ()); ?> + Coming soon categories)) { ?> -- cgit v1.2.3