]>
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 | ||
bc1ee852 | 11 | class Database { |
14890de3 | 12 | var $handle; |
13 | ||
68857cea NL |
14 | function __construct() |
15 | { | |
16 | switch (STORAGE) { | |
17 | case 'sqlite': | |
18 | $db_path = 'sqlite:' . STORAGE_SQLITE; | |
19 | $this->handle = new PDO($db_path); | |
20 | break; | |
21 | case 'mysql': | |
22 | $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; | |
23 | $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); | |
24 | break; | |
25 | case 'postgres': | |
26 | $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB; | |
27 | $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); | |
28 | break; | |
29 | } | |
30 | ||
14890de3 | 31 | $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
580d60b9 | 32 | Tools::logm('storage type ' . STORAGE); |
14890de3 | 33 | } |
34 | ||
35 | private function getHandle() { | |
36 | return $this->handle; | |
37 | } | |
38 | ||
aa8c9f2a | 39 | public function isInstalled() { |
b916bcfc NL |
40 | $sql = "SELECT username FROM users"; |
41 | $query = $this->executeQuery($sql, array()); | |
42 | $hasAdmin = count($query->fetchAll()); | |
aa8c9f2a | 43 | |
b916bcfc | 44 | if ($hasAdmin == 0) |
aa8c9f2a NL |
45 | return FALSE; |
46 | ||
47 | return TRUE; | |
48 | } | |
49 | ||
50 | public function install($login, $password) { | |
b916bcfc NL |
51 | $sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)'; |
52 | $params = array($login, $password, $login, ' '); | |
53 | $query = $this->executeQuery($sql, $params); | |
54 | ||
55 | $sequence = ''; | |
56 | if (STORAGE == 'postgres') { | |
57 | $sequence = 'users_id_seq'; | |
58 | } | |
59 | ||
60 | $id_user = intval($this->getLastId($sequence)); | |
61 | ||
62 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
00dbaf90 | 63 | $params = array($id_user, 'pager', PAGINATION); |
b916bcfc NL |
64 | $query = $this->executeQuery($sql, $params); |
65 | ||
66 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
00dbaf90 NL |
67 | $params = array($id_user, 'language', LANG); |
68 | $query = $this->executeQuery($sql, $params); | |
69 | ||
70 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
71 | $params = array($id_user, 'theme', DEFAULT_THEME); | |
7ce7ec4c | 72 | $query = $this->executeQuery($sql, $params); |
aa8c9f2a | 73 | |
7ce7ec4c NL |
74 | return TRUE; |
75 | } | |
aa8c9f2a | 76 | |
5846b0f1 | 77 | public function getConfigUser($id) { |
7ce7ec4c NL |
78 | $sql = "SELECT * FROM users_config WHERE user_id = ?"; |
79 | $query = $this->executeQuery($sql, array($id)); | |
80 | $result = $query->fetchAll(); | |
81 | $user_config = array(); | |
82 | ||
83 | foreach ($result as $key => $value) { | |
84 | $user_config[$value['name']] = $value['value']; | |
aa8c9f2a NL |
85 | } |
86 | ||
7ce7ec4c | 87 | return $user_config; |
aa8c9f2a NL |
88 | } |
89 | ||
027b4e15 DS |
90 | public function userExists($username) { |
91 | $sql = "SELECT * FROM users WHERE username=?"; | |
92 | $query = $this->executeQuery($sql, array($username)); | |
93 | $login = $query->fetchAll(); | |
94 | if (isset($login[0])) { | |
95 | return true; | |
96 | } else { | |
97 | return false; | |
98 | } | |
99 | } | |
100 | ||
7ce7ec4c | 101 | public function login($username, $password) { |
8d3275be NL |
102 | $sql = "SELECT * FROM users WHERE username=? AND password=?"; |
103 | $query = $this->executeQuery($sql, array($username, $password)); | |
104 | $login = $query->fetchAll(); | |
aa8c9f2a | 105 | |
7ce7ec4c NL |
106 | $user = array(); |
107 | if (isset($login[0])) { | |
108 | $user['id'] = $login[0]['id']; | |
109 | $user['username'] = $login[0]['username']; | |
110 | $user['password'] = $login[0]['password']; | |
111 | $user['name'] = $login[0]['name']; | |
112 | $user['email'] = $login[0]['email']; | |
113 | $user['config'] = $this->getConfigUser($login[0]['id']); | |
114 | } | |
aa8c9f2a | 115 | |
7ce7ec4c | 116 | return $user; |
aa8c9f2a NL |
117 | } |
118 | ||
00dbaf90 | 119 | public function updatePassword($userId, $password) |
6499b26a | 120 | { |
8d3275be | 121 | $sql_update = "UPDATE users SET password=? WHERE id=?"; |
1e98ee1d NL |
122 | $params_update = array($password, $userId); |
123 | $query = $this->executeQuery($sql_update, $params_update); | |
00dbaf90 NL |
124 | } |
125 | ||
126 | public function updateUserConfig($userId, $key, $value) { | |
58ace494 NL |
127 | $config = $this->getConfigUser($userId); |
128 | ||
129 | if (!isset ($user_config[$key])) { | |
130 | $sql = "INSERT INTO users_config (`value`, `user_id`, `name`) VALUES (?, ?, ?)"; | |
131 | } | |
132 | else { | |
133 | $sql = "UPDATE users_config SET `value`=? WHERE `user_id`=? AND `name`=?"; | |
134 | } | |
135 | ||
136 | $params = array($value, $userId, $key); | |
137 | $query = $this->executeQuery($sql, $params); | |
6499b26a NL |
138 | } |
139 | ||
14890de3 | 140 | private function executeQuery($sql, $params) { |
141 | try | |
142 | { | |
143 | $query = $this->getHandle()->prepare($sql); | |
144 | $query->execute($params); | |
145 | return $query; | |
146 | } | |
147 | catch (Exception $e) | |
148 | { | |
eb1af592 | 149 | Tools::logm('execute query error : '.$e->getMessage()); |
bc1ee852 | 150 | return FALSE; |
14890de3 | 151 | } |
152 | } | |
153 | ||
8d3275be NL |
154 | public function retrieveAll($user_id) { |
155 | $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id"; | |
156 | $query = $this->executeQuery($sql, array($user_id)); | |
44e77bfa | 157 | $entries = $query->fetchAll(); |
158 | ||
159 | return $entries; | |
160 | } | |
161 | ||
8d3275be | 162 | public function retrieveOneById($id, $user_id) { |
14890de3 | 163 | $entry = NULL; |
8d3275be NL |
164 | $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; |
165 | $params = array(intval($id), $user_id); | |
14890de3 | 166 | $query = $this->executeQuery($sql, $params); |
167 | $entry = $query->fetchAll(); | |
168 | ||
0c2f4537 | 169 | return isset($entry[0]) ? $entry[0] : null; |
14890de3 | 170 | } |
171 | ||
8d3275be | 172 | public function getEntriesByView($view, $user_id, $limit = '') { |
14890de3 | 173 | switch ($_SESSION['sort']) |
174 | { | |
175 | case 'ia': | |
176 | $order = 'ORDER BY id'; | |
177 | break; | |
178 | case 'id': | |
179 | $order = 'ORDER BY id DESC'; | |
180 | break; | |
181 | case 'ta': | |
182 | $order = 'ORDER BY lower(title)'; | |
183 | break; | |
184 | case 'td': | |
185 | $order = 'ORDER BY lower(title) DESC'; | |
186 | break; | |
187 | default: | |
188 | $order = 'ORDER BY id'; | |
189 | break; | |
190 | } | |
191 | ||
192 | switch ($view) | |
193 | { | |
194 | case 'archive': | |
8d3275be | 195 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; |
bc1ee852 | 196 | $params = array($user_id, 1); |
14890de3 | 197 | break; |
198 | case 'fav' : | |
8d3275be | 199 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? " . $order; |
bc1ee852 | 200 | $params = array($user_id, 1); |
14890de3 | 201 | break; |
202 | default: | |
8d3275be NL |
203 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; |
204 | $params = array($user_id, 0); | |
14890de3 | 205 | break; |
206 | } | |
207 | ||
6a361945 NL |
208 | $sql .= ' ' . $limit; |
209 | ||
8d3275be NL |
210 | $query = $this->executeQuery($sql, $params); |
211 | $entries = $query->fetchAll(); | |
14890de3 | 212 | |
213 | return $entries; | |
214 | } | |
215 | ||
b58e261d NL |
216 | public function updateContent($id, $content, $user_id) { |
217 | $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?'; | |
218 | $params_action = array($content, $id, $user_id); | |
219 | $query = $this->executeQuery($sql_action, $params_action); | |
220 | return $query; | |
221 | } | |
222 | ||
8d3275be | 223 | public function add($url, $title, $content, $user_id) { |
8d3275be NL |
224 | $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)'; |
225 | $params_action = array($url, $title, $content, $user_id); | |
226 | $query = $this->executeQuery($sql_action, $params_action); | |
29c6fd46 | 227 | return $query; |
14890de3 | 228 | } |
229 | ||
8d3275be | 230 | public function deleteById($id, $user_id) { |
8d3275be NL |
231 | $sql_action = "DELETE FROM entries WHERE id=? AND user_id=?"; |
232 | $params_action = array($id, $user_id); | |
14890de3 | 233 | $query = $this->executeQuery($sql_action, $params_action); |
29c6fd46 | 234 | return $query; |
14890de3 | 235 | } |
236 | ||
8d3275be | 237 | public function favoriteById($id, $user_id) { |
bc1ee852 | 238 | $sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?"; |
8d3275be | 239 | $params_action = array($id, $user_id); |
14890de3 | 240 | $query = $this->executeQuery($sql_action, $params_action); |
241 | } | |
242 | ||
8d3275be | 243 | public function archiveById($id, $user_id) { |
bc1ee852 | 244 | $sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?"; |
8d3275be | 245 | $params_action = array($id, $user_id); |
14890de3 | 246 | $query = $this->executeQuery($sql_action, $params_action); |
247 | } | |
248 | ||
b916bcfc NL |
249 | public function getLastId($column = '') { |
250 | return $this->getHandle()->lastInsertId($column); | |
14890de3 | 251 | } |
5bea1a4d | 252 | |
2e2ebe5e NL |
253 | public function retrieveAllTags() { |
254 | $sql = "SELECT * FROM tags"; | |
255 | $query = $this->executeQuery($sql, array()); | |
256 | $tags = $query->fetchAll(); | |
257 | ||
258 | return $tags; | |
259 | } | |
260 | ||
4886ed6d NL |
261 | public function retrieveTag($id) { |
262 | $tag = NULL; | |
263 | $sql = "SELECT * FROM tags WHERE id=?"; | |
264 | $params = array(intval($id)); | |
265 | $query = $this->executeQuery($sql, $params); | |
266 | $tag = $query->fetchAll(); | |
267 | ||
268 | return isset($tag[0]) ? $tag[0] : null; | |
269 | } | |
270 | ||
271 | public function retrieveEntriesByTag($tag_id) { | |
272 | $sql = | |
273 | "SELECT * FROM entries | |
274 | LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id | |
275 | WHERE tags_entries.tag_id = ?"; | |
276 | $query = $this->executeQuery($sql, array($tag_id)); | |
277 | $entries = $query->fetchAll(); | |
278 | ||
279 | return $entries; | |
280 | } | |
281 | ||
5bea1a4d | 282 | public function retrieveTagsByEntry($entry_id) { |
2e2ebe5e | 283 | $sql = |
5bea1a4d NL |
284 | "SELECT * FROM tags |
285 | LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id | |
286 | WHERE tags_entries.entry_id = ?"; | |
2e2ebe5e NL |
287 | $query = $this->executeQuery($sql, array($entry_id)); |
288 | $tags = $query->fetchAll(); | |
5bea1a4d NL |
289 | |
290 | return $tags; | |
291 | } | |
c432fa16 NL |
292 | |
293 | public function removeTagForEntry($entry_id, $tag_id) { | |
294 | $sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?"; | |
295 | $params_action = array($tag_id, $entry_id); | |
296 | $query = $this->executeQuery($sql_action, $params_action); | |
297 | return $query; | |
298 | } | |
299 | ||
300 | public function retrieveTagByValue($value) { | |
301 | $tag = NULL; | |
302 | $sql = "SELECT * FROM tags WHERE value=?"; | |
303 | $params = array($value); | |
304 | $query = $this->executeQuery($sql, $params); | |
305 | $tag = $query->fetchAll(); | |
306 | ||
307 | return isset($tag[0]) ? $tag[0] : null; | |
308 | } | |
309 | ||
310 | public function createTag($value) { | |
311 | $sql_action = 'INSERT INTO tags ( value ) VALUES (?)'; | |
312 | $params_action = array($value); | |
313 | $query = $this->executeQuery($sql_action, $params_action); | |
314 | return $query; | |
315 | } | |
316 | ||
317 | public function setTagToEntry($tag_id, $entry_id) { | |
318 | $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)'; | |
319 | $params_action = array($tag_id, $entry_id); | |
320 | $query = $this->executeQuery($sql_action, $params_action); | |
321 | return $query; | |
322 | } | |
2cd263e0 | 323 | } |