· 8 years ago · Dec 01, 2017, 11:14 PM
1package west
2
3import "database/sql"
4
5// SchemaPostgres defines the schema for a postgres database
6const SchemaPostgres = `
7CREATE TABLE IF NOT EXISTS files (
8 id SERIAL PRIMARY KEY,
9 url TEXT NOT NULL,
10 sourceurl TEXT NOT NULL,
11 mediatype TEXT NOT NULL,
12 title TEXT NOT NULL,
13 description TEXT NOT NULL,
14 thumbnailurl TEXT NOT NULL,
15 odescription TEXT NOT NULL,
16 ouploaded TEXT NOT NULL,
17 oauthor TEXT NOT NULL,
18 uploaded INTEGER NOT NULL,
19 authorid INTEGER NOT NULL,
20 deleted INTEGER NOT NULL,
21 hash TEXT NOT NULL
22);
23
24CREATE TABLE IF NOT EXISTS users (
25 id SERIAL PRIMARY KEY,
26 username TEXT NOT NULL,
27 avatarurl TEXT NOT NULL,
28 joined TEXT NOT NULL,
29 salt TEXT NOT NULL,
30 passwordhash TEXT NOT NULL,
31 role INTEGER NOT NULL,
32 settings TEXT NOT NULL,
33 banned INTEGER NOT NULL
34);
35
36
37CREATE TABLE IF NOT EXISTS user_sessions (
38 id SERIAL PRIMARY KEY,
39 key TEXT NOT NULL,
40 expires INTEGER NOT NULL,
41 user_id INTEGER NOT NULL,
42 UNIQUE(key),
43 FOREIGN KEY(user_id) REFERENCES users(id)
44);
45
46CREATE TABLE IF NOT EXISTS collections (
47 id SERIAL PRIMARY KEY,
48 name TEXT NOT NULL,
49 description TEXT NOT NULL,
50 thumbnailurl TEXT NOT NULL,
51 author_id INT NOT NULL,
52 uploaded INTEGER NOT NULL
53);
54
55CREATE TABLE IF NOT EXISTS collection_files (
56 id SERIAL PRIMARY KEY,
57 file_id INTEGER NOT NULL,
58 collection_id INTEGER NOT NULL,
59 UNIQUE(file_id, collection_id),
60 FOREIGN KEY(file_id) REFERENCES files(id),
61 FOREIGN KEY(collection_id) REFERENCES collections(id)
62);
63
64CREATE TABLE IF NOT EXISTS tags (
65 id SERIAL PRIMARY KEY,
66 name TEXT NOT NULL,
67 UNIQUE(name)
68);
69
70CREATE TABLE IF NOT EXISTS file_tags (
71 id SERIAL PRIMARY KEY,
72 file_id INTEGER NOT NULL,
73 tag_id INTEGER NOT NULL,
74 UNIQUE(file_id, tag_id),
75 FOREIGN KEY(file_id) REFERENCES files(id),
76 FOREIGN KEY(tag_id) REFERENCES tags(id)
77);`
78
79// SchemaSqlite3 defines the schema for a sqlite3 database
80const SchemaSqlite3 = `
81CREATE TABLE IF NOT EXISTS files (
82 id INTEGER PRIMARY KEY AUTOINCREMENT,
83 url TEXT NOT NULL,
84 sourceurl TEXT NOT NULL,
85 mediatype TEXT NOT NULL,
86 title TEXT NOT NULL,
87 description TEXT NOT NULL,
88 thumbnailurl TEXT NOT NULL,
89 odescription TEXT NOT NULL,
90 ouploaded TEXT NOT NULL,
91 oauthor TEXT NOT NULL,
92 uploaded INTEGER NOT NULL,
93 authorid INTEGER NOT NULL,
94 deleted INTEGER NOT NULL,
95 hash TEXT NOT NULL
96);
97
98CREATE TABLE IF NOT EXISTS users (
99 id INTEGER PRIMARY KEY AUTOINCREMENT,
100 username TEXT NOT NULL,
101 avatarurl TEXT NOT NULL,
102 joined TEXT NOT NULL,
103 salt TEXT NOT NULL,
104 passwordhash TEXT NOT NULL,
105 role INTEGER NOT NULL,
106 settings TEXT NOT NULL,
107 banned INTEGER NOT NULL
108);
109
110
111CREATE TABLE IF NOT EXISTS user_sessions (
112 id INTEGER PRIMARY KEY AUTOINCREMENT,
113 key TEXT NOT NULL,
114 expires INTEGER NOT NULL,
115 user_id INTEGER NOT NULL,
116 UNIQUE(key),
117 FOREIGN KEY(user_id) REFERENCES users(id)
118);
119
120CREATE TABLE IF NOT EXISTS collections (
121 id INTEGER PRIMARY KEY AUTOINCREMENT,
122 name TEXT NOT NULL,
123 description TEXT NOT NULL,
124 thumbnailurl TEXT NOT NULL,
125 author_id INT NOT NULL,
126 uploaded INTEGER NOT NULL
127);
128
129CREATE TABLE IF NOT EXISTS collection_files (
130 id INTEGER PRIMARY KEY AUTOINCREMENT,
131 file_id INTEGER NOT NULL,
132 collection_id INTEGER NOT NULL,
133 UNIQUE(file_id, collection_id),
134 FOREIGN KEY(file_id) REFERENCES files(id),
135 FOREIGN KEY(collection_id) REFERENCES collections(id)
136);
137
138CREATE TABLE IF NOT EXISTS tags (
139 id INTEGER PRIMARY KEY AUTOINCREMENT,
140 name TEXT NOT NULL,
141 UNIQUE(name)
142);
143
144CREATE TABLE IF NOT EXISTS file_tags (
145 id INTEGER PRIMARY KEY AUTOINCREMENT,
146 file_id INTEGER NOT NULL,
147 tag_id INTEGER NOT NULL,
148 UNIQUE(file_id, tag_id),
149 FOREIGN KEY(file_id) REFERENCES files(id),
150 FOREIGN KEY(tag_id) REFERENCES tags(id)
151);`
152
153// SchemaMysql defines the schema for a mysql database
154const SchemaMysql = `
155CREATE TABLE IF NOT EXISTS files (
156 id INTEGER PRIMARY KEY AUTO_INCREMENT,
157 url TEXT NOT NULL,
158 sourceurl TEXT NOT NULL,
159 mediatype TEXT NOT NULL,
160 title TEXT NOT NULL,
161 description TEXT NOT NULL,
162 thumbnailurl TEXT NOT NULL,
163 odescription TEXT NOT NULL,
164 ouploaded TEXT NOT NULL,
165 oauthor TEXT NOT NULL,
166 uploaded INTEGER NOT NULL,
167 authorid INTEGER NOT NULL,
168 deleted INTEGER NOT NULL,
169 hash TEXT NOT NULL
170);
171
172CREATE TABLE IF NOT EXISTS users (
173 id INTEGER PRIMARY KEY AUTO_INCREMENT,
174 username TEXT NOT NULL,
175 avatarurl TEXT NOT NULL,
176 joined TEXT NOT NULL,
177 salt TEXT NOT NULL,
178 passwordhash TEXT NOT NULL,
179 role INTEGER NOT NULL,
180 settings TEXT NOT NULL,
181 banned INTEGER NOT NULL
182);
183
184
185CREATE TABLE IF NOT EXISTS user_sessions (
186 id INTEGER PRIMARY KEY AUTO_INCREMENT,
187 key TEXT NOT NULL,
188 expires INTEGER NOT NULL,
189 user_id INTEGER NOT NULL,
190 UNIQUE(key),
191 FOREIGN KEY(user_id) REFERENCES users(id)
192);
193
194CREATE TABLE IF NOT EXISTS collections (
195 id INTEGER PRIMARY KEY AUTO_INCREMENT,
196 name TEXT NOT NULL,
197 description TEXT NOT NULL,
198 thumbnailurl TEXT NOT NULL,
199 author_id INT NOT NULL,
200 uploaded INTEGER NOT NULL
201);
202
203CREATE TABLE IF NOT EXISTS collection_files (
204 id INTEGER PRIMARY KEY AUTO_INCREMENT,
205 file_id INTEGER NOT NULL,
206 collection_id INTEGER NOT NULL,
207 UNIQUE(file_id, collection_id),
208 FOREIGN KEY(file_id) REFERENCES files(id),
209 FOREIGN KEY(collection_id) REFERENCES collections(id)
210);
211
212CREATE TABLE IF NOT EXISTS tags (
213 id INTEGER PRIMARY KEY AUTO_INCREMENT,
214 name TEXT NOT NULL,
215 UNIQUE(name)
216);
217
218CREATE TABLE IF NOT EXISTS file_tags (
219 id INTEGER PRIMARY KEY AUTO_INCREMENT,
220 file_id INTEGER NOT NULL,
221 tag_id INTEGER NOT NULL,
222 UNIQUE(file_id, tag_id),
223 FOREIGN KEY(file_id) REFERENCES files(id),
224 FOREIGN KEY(tag_id) REFERENCES tags(id)
225);`
226
227// InitDB Initializes a database with the proper schema
228func InitDB(db *sql.DB, driver string) error {
229 // Initialize the database
230
231 var schema string
232
233 switch driver {
234 case "postgres":
235 schema = SchemaPostgres
236 case "sqlite3":
237 schema = SchemaSqlite3
238 case "mysql":
239 schema = SchemaMysql
240 default:
241 schema = SchemaPostgres
242 }
243
244 return nil
245}