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
|
<?php
namespace Micropoly;
use SQLite3;
class Schema
{
private SQLite3 $db;
/**
* @param SQLite3 $db
*/
public function __construct(SQLite3 $db) { $this->db = $db; }
private function getSchemaVersion(): int
{
$n = $this->db->querySingle("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'schema_meta'");
if ($n !== "schema_meta")
return 0;
return (int)$this->db->querySingle("SELECT value FROM schema_meta WHERE key = 'version'");
}
private function setSchemaVersion(int $v): void
{
(new DbQuery("REPLACE INTO schema_meta (key, value) VALUES ('version', :v)"))
->bind(":v", $v)
->exec($this->db);
}
public function migrate()
{
$version = $this->getSchemaVersion();
switch ($version) {
case 0:
$this->v1();
// fallthrough
case 1:
$this->v2();
$this->setSchemaVersion(2);
}
}
private function v1()
{
$this->db->exec("
CREATE TABLE schema_meta (
key VARCHAR(100) NOT NULL PRIMARY KEY,
value
) WITHOUT ROWID
");
$this->db->exec("
CREATE VIRTUAL TABLE note_contents USING fts4 (content TEXT)
");
$this->db->exec("
CREATE TABLE notes (
id VARCHAR(23) NOT NULL PRIMARY KEY,
content_row INT NOT NULL,
created_at BIGINT NOT NULL DEFAULT CURRENT_TIMESTAMP,
changed_at BIGINT NOT NULL DEFAULT CURRENT_TIMESTAMP,
trash INT NOT NULL DEFAULT 0
) WITHOUT ROWID
");
$this->db->exec("
CREATE TABLE tags (
note_id VARCHAR(23) NOT NULL REFERENCES notes (id) ON UPDATE CASCADE ON DELETE CASCADE,
tag TEXT NOT NULL,
PRIMARY KEY (note_id, tag)
) WITHOUT ROWID
");
$this->db->exec("CREATE INDEX tag ON tags (tag)");
$this->db->exec("
CREATE VIEW tagcloud AS
SELECT
tag,
COUNT(*) AS num
FROM tags
GROUP BY tag
");
}
private function v2()
{
$this->db->exec("
CREATE TABLE attachments (hash TEXT NOT NULL PRIMARY KEY) WITHOUT ROWID
");
$this->db->exec("
CREATE TABLE note_attachments (
id VARCHAR(23) NOT NULL PRIMARY KEY,
note_id VARCHAR(23) NOT NULL REFERENCES notes (id) ON UPDATE CASCADE ON DELETE CASCADE,
hash TEXT NOT NULL REFERENCES attachments (hash) ON UPDATE CASCADE ON DELETE CASCADE,
file_name TEXT NULL DEFAULT NULL,
mime TEXT NOT NULL DEFAULT 'application/octet-stream'
) WITHOUT ROWID;
");
$this->db->exec("CREATE INDEX note_id ON note_attachments (note_id)");
$this->db->exec("CREATE INDEX hash ON note_attachments (hash)");
}
}
|
