]>
Commit | Line | Data |
---|---|---|
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 Database { | |
12 | var $handle; | |
13 | ||
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 | ||
31 | $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); | |
32 | Tools::logm('storage type ' . STORAGE); | |
33 | } | |
34 | ||
35 | private function getHandle() { | |
36 | return $this->handle; | |
37 | } | |
38 | ||
39 | public function isInstalled() { | |
40 | $sql = "SELECT username FROM users"; | |
41 | $query = $this->executeQuery($sql, array()); | |
42 | $hasAdmin = count($query->fetchAll()); | |
43 | ||
44 | if ($hasAdmin == 0) | |
45 | return FALSE; | |
46 | ||
47 | return TRUE; | |
48 | } | |
49 | ||
50 | public function install($login, $password) { | |
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 (?, ?, ?)'; | |
63 | $params = array($id_user, 'pager', '10'); | |
64 | $query = $this->executeQuery($sql, $params); | |
65 | ||
66 | $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)'; | |
67 | $params = array($id_user, 'language', 'en_EN.UTF8'); | |
68 | $query = $this->executeQuery($sql, $params); | |
69 | ||
70 | return TRUE; | |
71 | } | |
72 | ||
73 | private function getConfigUser($id) { | |
74 | $sql = "SELECT * FROM users_config WHERE user_id = ?"; | |
75 | $query = $this->executeQuery($sql, array($id)); | |
76 | $result = $query->fetchAll(); | |
77 | $user_config = array(); | |
78 | ||
79 | foreach ($result as $key => $value) { | |
80 | $user_config[$value['name']] = $value['value']; | |
81 | } | |
82 | ||
83 | return $user_config; | |
84 | } | |
85 | ||
86 | public function login($username, $password) { | |
87 | $sql = "SELECT * FROM users WHERE username=? AND password=?"; | |
88 | $query = $this->executeQuery($sql, array($username, $password)); | |
89 | $login = $query->fetchAll(); | |
90 | ||
91 | $user = array(); | |
92 | if (isset($login[0])) { | |
93 | $user['id'] = $login[0]['id']; | |
94 | $user['username'] = $login[0]['username']; | |
95 | $user['password'] = $login[0]['password']; | |
96 | $user['name'] = $login[0]['name']; | |
97 | $user['email'] = $login[0]['email']; | |
98 | $user['config'] = $this->getConfigUser($login[0]['id']); | |
99 | } | |
100 | ||
101 | return $user; | |
102 | } | |
103 | ||
104 | public function updatePassword($id, $password) | |
105 | { | |
106 | $sql_update = "UPDATE users SET password=? WHERE id=?"; | |
107 | $params_update = array($password, $id); | |
108 | $query = $this->executeQuery($sql_update, $params_update); | |
109 | } | |
110 | ||
111 | private function executeQuery($sql, $params) { | |
112 | try | |
113 | { | |
114 | $query = $this->getHandle()->prepare($sql); | |
115 | $query->execute($params); | |
116 | return $query; | |
117 | } | |
118 | catch (Exception $e) | |
119 | { | |
120 | Tools::logm('execute query error : '.$e->getMessage()); | |
121 | return FALSE; | |
122 | } | |
123 | } | |
124 | ||
125 | public function retrieveAll($user_id) { | |
126 | $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id"; | |
127 | $query = $this->executeQuery($sql, array($user_id)); | |
128 | $entries = $query->fetchAll(); | |
129 | ||
130 | return $entries; | |
131 | } | |
132 | ||
133 | public function retrieveOneById($id, $user_id) { | |
134 | $entry = NULL; | |
135 | $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; | |
136 | $params = array(intval($id), $user_id); | |
137 | $query = $this->executeQuery($sql, $params); | |
138 | $entry = $query->fetchAll(); | |
139 | ||
140 | return $entry[0]; | |
141 | } | |
142 | ||
143 | public function getEntriesByView($view, $user_id, $limit = '') { | |
144 | switch ($_SESSION['sort']) | |
145 | { | |
146 | case 'ia': | |
147 | $order = 'ORDER BY id'; | |
148 | break; | |
149 | case 'id': | |
150 | $order = 'ORDER BY id DESC'; | |
151 | break; | |
152 | case 'ta': | |
153 | $order = 'ORDER BY lower(title)'; | |
154 | break; | |
155 | case 'td': | |
156 | $order = 'ORDER BY lower(title) DESC'; | |
157 | break; | |
158 | default: | |
159 | $order = 'ORDER BY id'; | |
160 | break; | |
161 | } | |
162 | ||
163 | switch ($view) | |
164 | { | |
165 | case 'archive': | |
166 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; | |
167 | $params = array($user_id, 1); | |
168 | break; | |
169 | case 'fav' : | |
170 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? " . $order; | |
171 | $params = array($user_id, 1); | |
172 | break; | |
173 | default: | |
174 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; | |
175 | $params = array($user_id, 0); | |
176 | break; | |
177 | } | |
178 | ||
179 | $sql .= ' ' . $limit; | |
180 | ||
181 | $query = $this->executeQuery($sql, $params); | |
182 | $entries = $query->fetchAll(); | |
183 | ||
184 | return $entries; | |
185 | } | |
186 | ||
187 | public function updateContent($id, $content, $user_id) { | |
188 | $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?'; | |
189 | $params_action = array($content, $id, $user_id); | |
190 | $query = $this->executeQuery($sql_action, $params_action); | |
191 | return $query; | |
192 | } | |
193 | ||
194 | public function add($url, $title, $content, $user_id) { | |
195 | $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)'; | |
196 | $params_action = array($url, $title, $content, $user_id); | |
197 | $query = $this->executeQuery($sql_action, $params_action); | |
198 | return $query; | |
199 | } | |
200 | ||
201 | public function deleteById($id, $user_id) { | |
202 | $sql_action = "DELETE FROM entries WHERE id=? AND user_id=?"; | |
203 | $params_action = array($id, $user_id); | |
204 | $query = $this->executeQuery($sql_action, $params_action); | |
205 | return $query; | |
206 | } | |
207 | ||
208 | public function favoriteById($id, $user_id) { | |
209 | $sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?"; | |
210 | $params_action = array($id, $user_id); | |
211 | $query = $this->executeQuery($sql_action, $params_action); | |
212 | } | |
213 | ||
214 | public function archiveById($id, $user_id) { | |
215 | $sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?"; | |
216 | $params_action = array($id, $user_id); | |
217 | $query = $this->executeQuery($sql_action, $params_action); | |
218 | } | |
219 | ||
220 | public function getLastId($column = '') { | |
221 | return $this->getHandle()->lastInsertId($column); | |
222 | } | |
223 | } |