]>
Commit | Line | Data |
---|---|---|
14890de3 | 1 | <?php |
2 | /** | |
3 | * poche, a read it later open source system | |
4 | * | |
5 | * @category poche | |
6 | * @author Nicolas Lœuillet <support@inthepoche.com> | |
7 | * @copyright 2013 | |
8 | * @license http://www.wtfpl.net/ see COPYING file | |
9 | */ | |
10 | ||
11 | class Sqlite extends Store { | |
12 | ||
13 | public static $db_path = 'sqlite:./db/poche.sqlite'; | |
14 | var $handle; | |
15 | ||
16 | function __construct() { | |
17 | parent::__construct(); | |
18 | ||
19 | $this->handle = new PDO(self::$db_path); | |
20 | $this->handle->exec('CREATE TABLE IF NOT EXISTS "entries" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , "title" VARCHAR, "url" VARCHAR UNIQUE , "is_read" INTEGER DEFAULT 0, "is_fav" INTEGER DEFAULT 0, "content" BLOB)'); | |
21 | $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); | |
22 | } | |
23 | ||
24 | private function getHandle() { | |
25 | return $this->handle; | |
26 | } | |
27 | ||
28 | private function executeQuery($sql, $params) { | |
29 | try | |
30 | { | |
31 | $query = $this->getHandle()->prepare($sql); | |
32 | $query->execute($params); | |
33 | return $query; | |
34 | } | |
35 | catch (Exception $e) | |
36 | { | |
37 | logm('execute query error : '.$e->getMessage()); | |
38 | } | |
39 | } | |
40 | ||
41 | public function retrieveOneById($id) { | |
42 | parent::__construct(); | |
43 | ||
44 | $entry = NULL; | |
45 | $sql = "SELECT * FROM entries WHERE id=?"; | |
46 | $params = array(intval($id)); | |
47 | $query = $this->executeQuery($sql, $params); | |
48 | $entry = $query->fetchAll(); | |
49 | ||
50 | return $entry[0]; | |
51 | } | |
52 | ||
53 | public function getEntriesByView($view) { | |
54 | parent::__construct(); | |
55 | ||
56 | switch ($_SESSION['sort']) | |
57 | { | |
58 | case 'ia': | |
59 | $order = 'ORDER BY id'; | |
60 | break; | |
61 | case 'id': | |
62 | $order = 'ORDER BY id DESC'; | |
63 | break; | |
64 | case 'ta': | |
65 | $order = 'ORDER BY lower(title)'; | |
66 | break; | |
67 | case 'td': | |
68 | $order = 'ORDER BY lower(title) DESC'; | |
69 | break; | |
70 | default: | |
71 | $order = 'ORDER BY id'; | |
72 | break; | |
73 | } | |
74 | ||
75 | switch ($view) | |
76 | { | |
77 | case 'archive': | |
78 | $sql = "SELECT * FROM entries WHERE is_read=? " . $order; | |
79 | $params = array(-1); | |
80 | break; | |
81 | case 'fav' : | |
82 | $sql = "SELECT * FROM entries WHERE is_fav=? " . $order; | |
83 | $params = array(-1); | |
84 | break; | |
85 | default: | |
86 | $sql = "SELECT * FROM entries WHERE is_read=? " . $order; | |
87 | $params = array(0); | |
88 | break; | |
89 | } | |
90 | ||
91 | $query = $this->executeQuery($sql, $params); | |
92 | $entries = $query->fetchAll(); | |
93 | ||
94 | return $entries; | |
95 | } | |
96 | ||
2cd263e0 | 97 | public function add($url, $title, $content) { |
14890de3 | 98 | parent::__construct(); |
99 | $sql_action = 'INSERT INTO entries ( url, title, content ) VALUES (?, ?, ?)'; | |
2cd263e0 | 100 | $params_action = array($url, $title, $content); |
14890de3 | 101 | $query = $this->executeQuery($sql_action, $params_action); |
102 | } | |
103 | ||
104 | public function deleteById($id) { | |
105 | parent::__construct(); | |
106 | $sql_action = "DELETE FROM entries WHERE id=?"; | |
107 | $params_action = array($id); | |
108 | $query = $this->executeQuery($sql_action, $params_action); | |
109 | } | |
110 | ||
111 | public function favoriteById($id) { | |
112 | parent::__construct(); | |
113 | $sql_action = "UPDATE entries SET is_fav=~is_fav WHERE id=?"; | |
114 | $params_action = array($id); | |
115 | $query = $this->executeQuery($sql_action, $params_action); | |
116 | } | |
117 | ||
118 | public function archiveById($id) { | |
119 | parent::__construct(); | |
120 | $sql_action = "UPDATE entries SET is_read=~is_read WHERE id=?"; | |
121 | $params_action = array($id); | |
122 | $query = $this->executeQuery($sql_action, $params_action); | |
123 | } | |
124 | ||
125 | public function getLastId() { | |
126 | parent::__construct(); | |
127 | return $this->getHandle()->lastInsertId(); | |
128 | } | |
129 | ||
130 | public function updateContentById($id) { | |
131 | parent::__construct(); | |
132 | $sql_update = "UPDATE entries SET content=? WHERE id=?"; | |
133 | $params_update = array($content, $id); | |
134 | $query = $this->executeQuery($sql_update, $params_update); | |
135 | } | |
2cd263e0 | 136 | } |