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