aboutsummaryrefslogtreecommitdiffhomepage
path: root/inc/store/sqlite.class.php
diff options
context:
space:
mode:
Diffstat (limited to 'inc/store/sqlite.class.php')
-rw-r--r--inc/store/sqlite.class.php144
1 files changed, 144 insertions, 0 deletions
diff --git a/inc/store/sqlite.class.php b/inc/store/sqlite.class.php
new file mode 100644
index 00000000..d5208a29
--- /dev/null
+++ b/inc/store/sqlite.class.php
@@ -0,0 +1,144 @@
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
11class 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 retrieveAll() {
42 $sql = "SELECT * FROM entries ORDER BY id";
43 $query = $this->executeQuery($sql, array());
44 $entries = $query->fetchAll();
45
46 return $entries;
47 }
48
49 public function retrieveOneById($id) {
50 parent::__construct();
51
52 $entry = NULL;
53 $sql = "SELECT * FROM entries WHERE id=?";
54 $params = array(intval($id));
55 $query = $this->executeQuery($sql, $params);
56 $entry = $query->fetchAll();
57
58 return $entry[0];
59 }
60
61 public function getEntriesByView($view) {
62 parent::__construct();
63
64 switch ($_SESSION['sort'])
65 {
66 case 'ia':
67 $order = 'ORDER BY id';
68 break;
69 case 'id':
70 $order = 'ORDER BY id DESC';
71 break;
72 case 'ta':
73 $order = 'ORDER BY lower(title)';
74 break;
75 case 'td':
76 $order = 'ORDER BY lower(title) DESC';
77 break;
78 default:
79 $order = 'ORDER BY id';
80 break;
81 }
82
83 switch ($view)
84 {
85 case 'archive':
86 $sql = "SELECT * FROM entries WHERE is_read=? " . $order;
87 $params = array(-1);
88 break;
89 case 'fav' :
90 $sql = "SELECT * FROM entries WHERE is_fav=? " . $order;
91 $params = array(-1);
92 break;
93 default:
94 $sql = "SELECT * FROM entries WHERE is_read=? " . $order;
95 $params = array(0);
96 break;
97 }
98
99 $query = $this->executeQuery($sql, $params);
100 $entries = $query->fetchAll();
101
102 return $entries;
103 }
104
105 public function add($url, $title, $content) {
106 parent::__construct();
107 $sql_action = 'INSERT INTO entries ( url, title, content ) VALUES (?, ?, ?)';
108 $params_action = array($url, $title, $content);
109 $query = $this->executeQuery($sql_action, $params_action);
110 }
111
112 public function deleteById($id) {
113 parent::__construct();
114 $sql_action = "DELETE FROM entries WHERE id=?";
115 $params_action = array($id);
116 $query = $this->executeQuery($sql_action, $params_action);
117 }
118
119 public function favoriteById($id) {
120 parent::__construct();
121 $sql_action = "UPDATE entries SET is_fav=~is_fav WHERE id=?";
122 $params_action = array($id);
123 $query = $this->executeQuery($sql_action, $params_action);
124 }
125
126 public function archiveById($id) {
127 parent::__construct();
128 $sql_action = "UPDATE entries SET is_read=~is_read WHERE id=?";
129 $params_action = array($id);
130 $query = $this->executeQuery($sql_action, $params_action);
131 }
132
133 public function getLastId() {
134 parent::__construct();
135 return $this->getHandle()->lastInsertId();
136 }
137
138 public function updateContentById($id) {
139 parent::__construct();
140 $sql_update = "UPDATE entries SET content=? WHERE id=?";
141 $params_update = array($content, $id);
142 $query = $this->executeQuery($sql_update, $params_update);
143 }
144}