/
schema.go
116 lines (97 loc) · 3.12 KB
/
schema.go
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
package sqlstore
import (
// "database/sql"
"fmt"
)
func (ss *SQLStore) checkSchema() error {
ver, err := ss.schemaVersion()
if err != nil {
return err
}
ss.DebugLog.Printf("Existing schema version: %d\n", ver)
if ver == 7 {
return nil // up to date.
}
// auto schema management currently only for sqlite.
if ss.driverName != "sqlite3" {
return fmt.Errorf("Missing Schema.")
}
if ver != 0 {
return fmt.Errorf("No Schema upgrade path (from ver %d)", ver)
}
// TODO: handle schema upgrades for data-in-the-wild!
stmts := []string{
`CREATE TABLE publication (
id INTEGER PRIMARY KEY,
code TEXT NOT NULL,
name TEXT NOT NULL DEFAULT '',
domain TEXT NOT NULL DEFAULT '')`,
`CREATE TABLE article (
id INTEGER PRIMARY KEY,
added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
canonical_url TEXT NOT NULL,
headline TEXT NOT NULL,
content TEXT NOT NULL DEFAULT '',
published TIMESTAMP DEFAULT NULL,
updated TIMESTAMP DEFAULT NULL,
publication_id INTEGER NOT NULL,
section TEXT NOT NULL DEFAULT '',
extra TEXT NOT NULL DEFAULT '',
FOREIGN KEY(publication_id) REFERENCES publication(id) )`,
`CREATE TABLE author (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
rel_link TEXT NOT NULL DEFAULT '',
email TEXT NOT NULL DEFAULT '',
twitter TEXT NOT NULL DEFAULT '' )`,
`CREATE TABLE author_attr (
id INTEGER PRIMARY KEY,
author_id INT NOT NULL,
article_id INT NOT NULL,
FOREIGN KEY(author_id) REFERENCES author(id) ON DELETE CASCADE,
FOREIGN KEY(article_id) REFERENCES article(id) ON DELETE CASCADE )`,
`CREATE INDEX author_attr_artid ON author_attr(article_id)`,
`CREATE INDEX author_attr_authorid ON author_attr(author_id)`,
`CREATE TABLE article_tag (
id INTEGER PRIMARY KEY,
article_id INTEGER NOT NULL,
tag TEXT NOT NULL,
FOREIGN KEY(article_id) REFERENCES article(id) ON DELETE CASCADE )`,
`CREATE INDEX article_tag_artid ON article_tag(article_id)`,
`CREATE TABLE article_url (
id INTEGER PRIMARY KEY,
article_id INTEGER NOT NULL,
url TEXT NOT NULL,
FOREIGN KEY(article_id) REFERENCES article(id) ON DELETE CASCADE )`,
`CREATE INDEX article_url_artid ON article_url(article_id)`,
`CREATE INDEX article_url_url ON article_url(url)`,
`CREATE TABLE article_keyword (
id INTEGER PRIMARY KEY,
article_id INTEGER NOT NULL,
name TEXT NOT NULL,
url TEXT NOT NULL,
FOREIGN KEY(article_id) REFERENCES article(id) ON DELETE CASCADE )`,
`CREATE INDEX article_keyword_artid ON article_keyword(article_id)`,
`CREATE TABLE version (ver INTEGER NOT NULL)`,
`CREATE TABLE settings (n TEXT, v TEXT NOT NULL)`,
`INSERT INTO version (ver) VALUES (7)`,
}
for _, stmt := range stmts {
_, err := ss.db.Exec(stmt)
//ss.DebugLog.Printf("%s (%v)\n", stmt, err)
if err != nil {
return err
}
}
return nil
}
func (ss *SQLStore) schemaVersion() (int, error) {
var v int
err := ss.db.QueryRow(`SELECT MAX(ver) FROM version`).Scan(&v)
if err != nil {
// should distinguish between missing version table and other errors,
// but hey.
return 0, nil
}
return v, nil
}