-
Notifications
You must be signed in to change notification settings - Fork 0
/
CommentMan.php
386 lines (337 loc) · 11 KB
/
CommentMan.php
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
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
<?php
/**
* @file comments.php
*
* @brief Comments manager without using any public identification
* service, and storing & retrieving the data using SQLite3
*
* @author J. A. Corbal <jacorbal@gmail.com>
*
* @date Created: Sat 07 Mar 2020 15:08 UTC
* @date Updated: Sun 08 Mar 2020 15:18 UTC
*
* @version 0.1.1
*
* @copyright (c) 2020, J. A. Corbal
*
* @note Licensed as BSD 3-clause
* <https://opensource.org/licenses/BSD-3-Clause>
*/
include_once('Comment.php');
/**
* @class CommentMan
*
* @brief Comments manager using database in SQLite3
*
* @see Comment
*/
/* Interface:
*
* is_empty()
* Test if the database is empty or not
*
* length()
* Return the number of registers in the database
*
* create()
* Create a new empty structure for the DB, an empty table
*
* add(comment)
* Add a new record to the database
*
* rem_by_id(id)
* Remove a comment by identifier `id`
*
* rem_by_name(name)
* Remove all registers with username `name`
*
* rem_newer_than(s)
* Remove all registers newer than `s`, e.g.: `rem_older("1 day")`
*
* rem_older_than(s)
* Remove all registers older than `s`, e.g.: `rem_older("1 year")`
*
* fetch_by_id(id)
* Retrieve a unique comment corresponding to the unique `id`
*
* fetch_by_post(pid)
* Retrieve an array of comments for that particular post `pid`
*
* fetch_thread(pid)
* Retrieve a multidimensional array of the comments thread for
* the post `pid` sorted hierarchically, as a tree
*/
class CommentMan extends SQLite3 {
/**
* @brief Default constructor.
* Start a new database connection
*
* @param database Path to the database
* @param create_file Create the file (if possible) if it doesn't exist
*
* @note If @e database doesn't exist, create it if @e create is set
* to @c true, or exit
*/
public function __construct($database, $create_file=false) {
if (!is_file($database)) {
trigger_error("CommentMan::ctor: no such database: $database");
if ($create_file) {
if (fopen($database, "w") and !is_file($database)) {
trigger_error("CommentMan::ctor: couldn't create file: $database");
die("Cannot create database file (check permissions)");
}
} else {// if no file, and not wanting to create it... die
die("Cannot work without a database");
}
}
// Start DB connection
$this->open($database);
// If database is empty, create a new schema
if ($this->is_empty()) {
if (!$this->create()) {
trigger_error("CommentMan:ctor: couldn't create database table");
die();
}
}
}
/**
* @brief Default destructor.
* Close database connection
*/
public function __destruct() {
$this->close();
}
/**
* @brief Count the number of registers stored in the database
*
* @return Number of registers in the database
*/
public function length() {
$sql = <<<EOQ
SELECT COUNT(*)
FROM comments;
EOQ;
return $this->querySingle($sql);
}
/**
* @brief Test if the database lacks an structure
*
* @return @c true if empty, or otherwise
*/
public function is_empty() {
$sql = <<<EOQ
SELECT COUNT(name) FROM sqlite_master
WHERE type = 'table'
AND NAME = 'comments';
EOQ;
return $this->querySingle($sql) === 0;
}
/**
* @brief Create a new structure for the database
*
* @return @c true if successfully created, or otherwise
*/
public function create() {
$sql = <<<EOQ
CREATE TABLE comments
(id INTEGER PRIMARY KEY AUTOINCREMENT,
parent_id INTEGER NOT_NULL DEFAULT 0,
post_id INTEGER NOT_NULL,
username NVARCHAR(80),
message TEXT NOT_NULL,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
ip VARCHAR(50),
is_deleted BOOLEAN NOT_NULL DEFAULT 0,
is_hidden BOOLEAN NOT_NULL DEFAULT 0);
EOQ;
return $this->query($sql);
}
/**
* @brief Add a new comment to the database
*
* @param comment_obj Comment object to add
*
* @return @c true if the insertion was successful, or otherwise
*
* @note This is the only piece of code that takes input query
* values from outside the class, so there's the STMT
* (statement) method to prevent SQL injection, although
* another test should be made from the PHP side when getting
* the data, just in case.
*/
public function add($comment_obj) {
$sql = <<<EOQ
INSERT INTO comments
(parent_id, post_id, username, message, ip)
VALUES
(:parent_id, :post_id, :username, :message, :ip);
EOQ;
$stmt = $this->prepare($sql);
$stmt->bindValue(':parent_id', $comment_obj->parent_id, SQLITE3_INTEGER);
$stmt->bindValue(':post_id', $comment_obj->post_id, SQLITE3_INTEGER);
$stmt->bindValue(':username', $comment_obj->username, SQLITE3_TEXT);
$stmt->bindValue(':message', $comment_obj->message, SQLITE3_TEXT);
$stmt->bindValue(':ip', $comment_obj->ip, SQLITE3_TEXT);
return $stmt->execute();
}
/**
* @brief Remove comment from the database by identifier
*
* @param comment_id Identifier of the comment to remove
*
* @return @c true if remove was successful, or otherwise
*/
public function rem_by_id($comment_id) {
$sql = <<<EOQ
DELETE FROM comments
WHERE id="$comment_id";
EOQ;
return $this->query($sql);
}
/**
* @brief Remove all comments posted by the same user
*
* @param username Username to delete all comments from
*
* @return @c true if remove was successful, or otherwise
*/
public function rem_by_name($username) {
$sql = <<<EOQ
DELETE FROM comments
WHERE username="$username";
EOQ;
return $this->query($sql);
}
/**
* @brief Remove all comments posted newer than a time mark
*
* @param newer_than Newness of the registers to delete
*
* @return @c true if remove was successful, or otherwise
*
* @note The newness must be introduced in a format that SQL knows,
* such as "1 day", "6 months", "3 years", "20 minutes",...
*/
public function rem_newer_than($newer_than="1 day") {
$sql = <<<EOQ
DELETE FROM comments
WHERE timestamp >= datetime('now','-$newer_than');
EOQ;
return $this->query($sql);
}
/**
* @brief Remove all comments posted older than a time mark
*
* @param older_than Oldness of the registers to delete
*
* @return @c true if remove was successful, or otherwise
*
* @note The oldness must be introduced in a format that SQL knows,
* such as "1 day", "6 months", "3 years", "20 minutes",...
*/
public function rem_older_than($older_than="6 months") {
$sql = <<<EOQ
DELETE FROM comments
WHERE timestamp <= datetime('now','-$older_than');
EOQ;
return $this->query($sql);
}
/**
* @brief Retrieve a comment from the database by identifier
*
* @param id Identifier of the comment to retrieve
*
* @return Comment object
*
* @see Comment
*/
public function fetch_by_id($id) {
$sql = <<<EOQ
SELECT * FROM comments
WHERE id = $id;
EOQ;
$comment = new Comment();
$row = $this->query($sql)->fetchArray(SQLITE3_ASSOC);
if (isset($row['id'])) {
$comment->id = $row['id'];
$comment->parent_id = $row['parent_id'];
$comment->post_id= $row['post_id'];
$comment->username = $row['username'];
$comment->message = $row['message'];
$comment->timestamp = $row['timestamp'];
$comment->ip = $row['ip'];
$comment->is_deleted = $row['is_deleted'];
$comment->is_hidden = $row['is_hidden'];
} else {
return null;
}
return $comment;
}
/**
* @brief Retrieve all comments relative to a single post
*
* @param post_id Identifier of the post those comments belong
* @param limit Maximum number of comments to retrieve
*
* @return Array of comment objects for that post
*
* @note The query will be performed sorted by date and reply,
earliest first
*/
public function fetch_by_post($post_id, $limit=100) {
$sql = <<<EOQ
SELECT * FROM comments
WHERE post_id = $post_id
ORDER BY timestamp ASC, parent_id ASC
LIMIT $limit;
EOQ;
$comments = array();
$query_res = $this->query($sql);
while ($row = $query_res->fetchArray(SQLITE3_ASSOC)) {
$comment = $this->fetch_by_id($row['id']);
array_push($comments, $comment);
}
return $comments;
}
/**
* @brief Retrieve the post thread sorted hierarchically as an array
*
* @param post_id Identifier of the post to retrieve the thread from
* @param limit Maximum number of comments to retrieve
*
* @return Multidimensional array (tree) of comments and replies
*
* @see build_thread
*/
public function fetch_thread($post_id, $limit=100) {
$comments = $this->fetch_by_post($post_id);
$thread = $this->build_thread($comments);
return $thread;
}
/**
* @brief Generate the thread array (tree-like) where the comments
* are sorted hierarchically, i.e., tree branches are the
* children comments associated to a parent comment (replies)
*
* @param comments Array of comment objects
* @param parent_id Identifier of the comment to get the thread of
*
* @return Tree-like array ordered by hierarchy parent/children
*/
private function build_thread(array $comments, $parent_id=0) {
$thread = array();
foreach ($comments as $comment) {
$comment_arr = $comment->toArray();
if ($comment_arr['parent_id'] == $parent_id) {
$children = $this->build_thread($comments,
$comment_arr['id']);
if ($children) {
$comment_arr['children'] = $children;
}
$thread[$comment_arr['id']] = $comment_arr;
unset($comment_arr);
}
}
return $thread;
}
}