]>
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 NL |
11 | class Database { |
12 | ||
13 | #postgresql | |
14 | public static $db_path = 'pgsql:host=localhost;dbname=poche'; | |
15 | public static $user = 'postgres'; | |
16 | public static $password = 'postgres'; | |
17 | #sqlite | |
18 | // public static $db_path = 'sqlite:./db/poche.sqlite'; | |
19 | // public static $user = ''; | |
20 | // public static $password = ''; | |
21 | #mysql | |
22 | // public static $db_path = 'mysql:host=localhost;dbname=poche'; | |
23 | // public static $user = 'root'; | |
24 | // public static $password = 'root'; | |
14890de3 | 25 | |
14890de3 | 26 | var $handle; |
27 | ||
28 | function __construct() { | |
bc1ee852 | 29 | $this->handle = new PDO(self::$db_path, self::$user, self::$password); |
14890de3 | 30 | $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
31 | } | |
32 | ||
33 | private function getHandle() { | |
34 | return $this->handle; | |
35 | } | |
36 | ||
aa8c9f2a | 37 | public function isInstalled() { |
7ce7ec4c NL |
38 | $sql = "SELECT username FROM users WHERE id=?"; |
39 | $query = $this->executeQuery($sql, array('1')); | |
40 | $hasAdmin = $query->fetchAll(); | |
aa8c9f2a | 41 | |
7ce7ec4c | 42 | if (count($hasAdmin) == 0) |
aa8c9f2a NL |
43 | return FALSE; |
44 | ||
45 | return TRUE; | |
46 | } | |
47 | ||
48 | public function install($login, $password) { | |
7ce7ec4c NL |
49 | $sql = 'INSERT INTO users ( username, password ) VALUES (?, ?)'; |
50 | $params = array($login, $password); | |
51 | $query = $this->executeQuery($sql, $params); | |
aa8c9f2a | 52 | |
7ce7ec4c NL |
53 | return TRUE; |
54 | } | |
aa8c9f2a | 55 | |
7ce7ec4c NL |
56 | private function getConfigUser($id) { |
57 | $sql = "SELECT * FROM users_config WHERE user_id = ?"; | |
58 | $query = $this->executeQuery($sql, array($id)); | |
59 | $result = $query->fetchAll(); | |
60 | $user_config = array(); | |
61 | ||
62 | foreach ($result as $key => $value) { | |
63 | $user_config[$value['name']] = $value['value']; | |
aa8c9f2a NL |
64 | } |
65 | ||
7ce7ec4c | 66 | return $user_config; |
aa8c9f2a NL |
67 | } |
68 | ||
7ce7ec4c | 69 | public function login($username, $password) { |
8d3275be NL |
70 | $sql = "SELECT * FROM users WHERE username=? AND password=?"; |
71 | $query = $this->executeQuery($sql, array($username, $password)); | |
72 | $login = $query->fetchAll(); | |
aa8c9f2a | 73 | |
7ce7ec4c NL |
74 | $user = array(); |
75 | if (isset($login[0])) { | |
76 | $user['id'] = $login[0]['id']; | |
77 | $user['username'] = $login[0]['username']; | |
78 | $user['password'] = $login[0]['password']; | |
79 | $user['name'] = $login[0]['name']; | |
80 | $user['email'] = $login[0]['email']; | |
81 | $user['config'] = $this->getConfigUser($login[0]['id']); | |
82 | } | |
aa8c9f2a | 83 | |
7ce7ec4c | 84 | return $user; |
aa8c9f2a NL |
85 | } |
86 | ||
7ce7ec4c | 87 | public function updatePassword($id, $password) |
6499b26a | 88 | { |
8d3275be NL |
89 | $sql_update = "UPDATE users SET password=? WHERE id=?"; |
90 | $params_update = array($password, $id); | |
91 | $query = $this->executeQuery($sql_update, $params_update); | |
6499b26a NL |
92 | } |
93 | ||
14890de3 | 94 | private function executeQuery($sql, $params) { |
95 | try | |
96 | { | |
97 | $query = $this->getHandle()->prepare($sql); | |
98 | $query->execute($params); | |
99 | return $query; | |
100 | } | |
101 | catch (Exception $e) | |
102 | { | |
eb1af592 | 103 | Tools::logm('execute query error : '.$e->getMessage()); |
bc1ee852 | 104 | return FALSE; |
14890de3 | 105 | } |
106 | } | |
107 | ||
8d3275be NL |
108 | public function retrieveAll($user_id) { |
109 | $sql = "SELECT * FROM entries WHERE user_id=? ORDER BY id"; | |
110 | $query = $this->executeQuery($sql, array($user_id)); | |
44e77bfa | 111 | $entries = $query->fetchAll(); |
112 | ||
113 | return $entries; | |
114 | } | |
115 | ||
8d3275be | 116 | public function retrieveOneById($id, $user_id) { |
14890de3 | 117 | $entry = NULL; |
8d3275be NL |
118 | $sql = "SELECT * FROM entries WHERE id=? AND user_id=?"; |
119 | $params = array(intval($id), $user_id); | |
14890de3 | 120 | $query = $this->executeQuery($sql, $params); |
121 | $entry = $query->fetchAll(); | |
122 | ||
123 | return $entry[0]; | |
124 | } | |
125 | ||
8d3275be | 126 | public function getEntriesByView($view, $user_id, $limit = '') { |
14890de3 | 127 | switch ($_SESSION['sort']) |
128 | { | |
129 | case 'ia': | |
130 | $order = 'ORDER BY id'; | |
131 | break; | |
132 | case 'id': | |
133 | $order = 'ORDER BY id DESC'; | |
134 | break; | |
135 | case 'ta': | |
136 | $order = 'ORDER BY lower(title)'; | |
137 | break; | |
138 | case 'td': | |
139 | $order = 'ORDER BY lower(title) DESC'; | |
140 | break; | |
141 | default: | |
142 | $order = 'ORDER BY id'; | |
143 | break; | |
144 | } | |
145 | ||
146 | switch ($view) | |
147 | { | |
148 | case 'archive': | |
8d3275be | 149 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; |
bc1ee852 | 150 | $params = array($user_id, 1); |
14890de3 | 151 | break; |
152 | case 'fav' : | |
8d3275be | 153 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? " . $order; |
bc1ee852 | 154 | $params = array($user_id, 1); |
14890de3 | 155 | break; |
156 | default: | |
8d3275be NL |
157 | $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; |
158 | $params = array($user_id, 0); | |
14890de3 | 159 | break; |
160 | } | |
161 | ||
6a361945 NL |
162 | $sql .= ' ' . $limit; |
163 | ||
8d3275be NL |
164 | $query = $this->executeQuery($sql, $params); |
165 | $entries = $query->fetchAll(); | |
14890de3 | 166 | |
167 | return $entries; | |
168 | } | |
169 | ||
8d3275be | 170 | public function add($url, $title, $content, $user_id) { |
8d3275be NL |
171 | $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)'; |
172 | $params_action = array($url, $title, $content, $user_id); | |
173 | $query = $this->executeQuery($sql_action, $params_action); | |
29c6fd46 | 174 | return $query; |
14890de3 | 175 | } |
176 | ||
8d3275be | 177 | public function deleteById($id, $user_id) { |
8d3275be NL |
178 | $sql_action = "DELETE FROM entries WHERE id=? AND user_id=?"; |
179 | $params_action = array($id, $user_id); | |
14890de3 | 180 | $query = $this->executeQuery($sql_action, $params_action); |
29c6fd46 | 181 | return $query; |
14890de3 | 182 | } |
183 | ||
8d3275be | 184 | public function favoriteById($id, $user_id) { |
bc1ee852 | 185 | $sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?"; |
8d3275be | 186 | $params_action = array($id, $user_id); |
14890de3 | 187 | $query = $this->executeQuery($sql_action, $params_action); |
188 | } | |
189 | ||
8d3275be | 190 | public function archiveById($id, $user_id) { |
bc1ee852 | 191 | $sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?"; |
8d3275be | 192 | $params_action = array($id, $user_id); |
14890de3 | 193 | $query = $this->executeQuery($sql_action, $params_action); |
194 | } | |
195 | ||
196 | public function getLastId() { | |
14890de3 | 197 | return $this->getHandle()->lastInsertId(); |
198 | } | |
2cd263e0 | 199 | } |