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
|
<?php
//connect or create the database
try {
$db = new PDO('sqlite:'.ROOT.DS.'posts.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$config['db_version'] = $db->query("PRAGMA user_version")->fetch(PDO::FETCH_ASSOC)['user_version'];
} catch(PDOException $e) {
print 'Exception : '.$e->getMessage();
die('cannot connect to or open the database');
}
// first time setup
if($config['db_version'] == 0) {
try {
$db->exec("PRAGMA `user_version` = 1;
CREATE TABLE IF NOT EXISTS `posts` (
`id` INTEGER PRIMARY KEY NOT NULL,
`post_content` TEXT,
`post_timestamp` INTEGER
);");
$config['db_version'] = 1;
} catch(PDOException $e) {
print 'Exception : '.$e->getMessage();
die('cannot set up initial database table!');
}
}
// upgrade database to v2
if($config['db_version'] == 1) {
try {
$db->exec("PRAGMA `user_version` = 2;
ALTER TABLE `posts` ADD `post_thread` INTEGER;
ALTER TABLE `posts` ADD `post_edited` INTEGER;
ALTER TABLE `posts` ADD `post_deleted` INTEGER;
");
$config['db_version'] = 2;
} catch(PDOException $e) {
print 'Exception : '.$e->getMessage();
die('cannot upgrade database table to v2!');
}
}
// upgrade database to v3
if($config['db_version'] == 2) {
try {
$db->exec("PRAGMA `user_version` = 3;
ALTER TABLE `posts` ADD `post_guid` TEXT;
");
$config['db_version'] = 3;
} catch(PDOException $e) {
print 'Exception : '.$e->getMessage();
die('cannot upgrade database table to v3!');
}
}
// upgrade database to v4
if($config['db_version'] == 3) {
try {
$db->exec("PRAGMA `user_version` = 4;
CREATE TABLE `files` (
`id` INTEGER PRIMARY KEY NOT NULL,
`file_filename` TEXT NOT NULL,
`file_extension` TEXT,
`file_original` TEXT NOT NULL,
`file_mime_type` TEXT,
`file_size` INTEGER,
`file_hash` TEXT UNIQUE,
`file_hash_algo` TEXT,
`file_meta` TEXT DEFAULT '{}',
`file_dir` TEXT,
`file_subdir` TEXT,
`file_timestamp` INTEGER,
`file_deleted` INTEGER
);
CREATE TABLE `file_to_post` (
`file_id` INTEGER NOT NULL,
`post_id` INTEGER NOT NULL,
`deleted` INTEGER,
UNIQUE(`file_id`, `post_id`) ON CONFLICT IGNORE
);
CREATE INDEX `posts_timestamp` ON posts (`post_timestamp`);
CREATE INDEX `files_original` ON files (`file_original`);
CREATE INDEX `link_deleted` ON file_to_post (`deleted`);
CREATE UNIQUE INDEX `files_hashes` ON files (`file_hash`);
");
$config['db_version'] = 4;
} catch(PDOException $e) {
print 'Exception : '.$e->getMessage();
die('cannot upgrade database table to v4!');
}
}
// debug: get a list of post table columns
// var_dump($db->query("PRAGMA table_info(`posts`)")->fetchAll(PDO::FETCH_COLUMN, 1));
|
