1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
| <?php
class Tagebuch_Db
{
/**
* @var PDO
*/
public $db = null;
protected $config = null;
public function __construct()
{
$this->config = Tagebuch_Config::get();
$this->prepare();
$this->loadFiles();
}
/**
* Returns a post object from the given file.
*
* @param string $file File name without path
*
* @return Tagebuch_Post or boolean false if not found
*/
public function getPost($file)
{
$stmt = $this->db->prepare('SELECT * FROM posts WHERE file = ?');
$stmt->execute(array($file));
return $stmt->fetchObject('Tagebuch_Post');
}
public function getPosts($tag = null, $limit = null)
{
if ($tag === null) {
$tag = '%';
} else {
$tag = '%,' . $tag . ',%';
}
$stmt = $this->db->prepare(
'SELECT * FROM posts'
. ' WHERE tags LIKE ?'
. ' ORDER BY modified DESC'
. ($limit !== null ? ' LIMIT ' . $limit : '')
);
$stmt->execute(array($tag));
return $stmt->fetchAll(PDO::FETCH_CLASS, 'Tagebuch_Post');
}
/**
* Fetches the next and previous posts from the database.
*
* @param string $file Filename to fetch
* @param string $tag Tag name that determines the file list
* - may be null for no tag
*
* @return array Array with prev and next keys, post objects
* as value. NULL when empty
*/
public function getPrevNext($file, $tag = null)
{
if ($tag === null) {
$tag = '%';
} else {
$tag = '%,' . $tag . ',%';
}
$qfile = $this->db->quote($file);
$qtag = $this->db->quote($tag);
list($date) = $this->db->query(
'SELECT modified FROM posts WHERE file LIKE '
. $qfile
. ' LIMIT 1'
)->fetch();
$qdate = $this->db->quote($date);
$prev = $this->db
->query('SELECT * FROM posts'
. ' WHERE modified < ' . $qdate
. ' AND tags LIKE ' . $qtag
. ' ORDER BY modified DESC LIMIT 1'
)->fetchObject('Tagebuch_Post');
$next = $this->db
->query('SELECT * FROM posts'
. ' WHERE modified > ' . $qdate
. ' AND tags LIKE ' . $qtag
. ' ORDER BY modified ASC LIMIT 1'
)->fetchObject('Tagebuch_Post');
return array(
'prev' => $prev,
'next' => $next
);
}
/**
* Returns an array of all tags
*/
public function getTags()
{
$stmt = $this->db->query('SELECT tags FROM posts');
$tags = array();
while ($row = $stmt->fetch(PDO::FETCH_OBJ, PDO::FETCH_ORI_NEXT)) {
$rtags = array_flip(explode(',', substr($row->tags, 1, -1)));
$tags = array_merge($tags, $rtags);
}
unset($tags['']);
return array_keys($tags);
}
/**
* Prepares the table structure
*
* @return void
*/
protected function prepare()
{
$this->db = new PDO('sqlite::memory:');
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->db->exec("CREATE TABLE posts(
title TEXT,
file TEXT,
size INTEGER,
created DATE,
modified DATE,
tags TEXT,
license TEXT
)");
}
/**
* Loads raw files into database
*
* @return void
*/
protected function loadFiles()
{
$query = $this->db->prepare(
'insert into posts (title,file,size,created,modified,tags,license)'
. ' VALUES(:title,:file,:size,:created,:modified,:tags,:license)'
);
$query->bindParam(':title', $title);
$query->bindParam(':file', $filename);
$query->bindParam(':size', $size);
$query->bindParam(':created', $crdate);
$query->bindParam(':modified', $mdate);
$query->bindParam(':tags', $tags);
$query->bindParam(':license', $license);
foreach ($this->getRawFiles() as $file) {
$hp = new Tagebuch_HtmlPost($file);
$crdate = $hp->getCreationDate();
$mdate = $hp->getModificationDate();
$tags = ',' . implode(
',',
$hp->getTags()
) . ',';
$title = $hp->getTitle();
$filename = basename($file);
$size = filesize($file);
$license = $hp->getLicense();
$query->execute();
}
}
/**
* Returns an array full of raw filename paths
*
* @return array Array of raw file names
*/
protected function getRawFiles()
{
return glob($this->config->raw . '*.htm');
}
}
?> |
