· 8 years ago · Feb 14, 2018, 11:18 PM
1//
2// SQLite3 storage backend for Snippets system. This is quite
3// inefficient since it does locking on all database access, but it is
4// good enough for prototyping. I'm not even sure if I need the
5// locking, perhaps SQLite3 is more robustly built than I think, but
6// for now I'll just leave it this way.
7//
8// To speed up builds I had to install go-sqlite3:
9//
10// go install github.com/mattn/go-sqlite3
11//
12//
13package store
14
15import (
16 "database/sql"
17 "errors"
18 "log"
19 "os"
20 "sync"
21 "time"
22
23 "github.com/borud/snippets/model"
24 _ "github.com/mattn/go-sqlite3"
25)
26
27// Database schema using SQLite3 DDL.
28//
29const schema = `
30PRAGMA foreign_keys = ON;
31PRAGMA defer_foreign_keys = FALSE;
32
33CREATE TABLE IF NOT EXISTS organizations (
34 name VARCHAR(80) PRIMARY KEY NOT NULL,
35 description VARCHAR(255),
36 imageurl VARCHAR(255)
37);
38
39CREATE TABLE IF NOT EXISTS users (
40 username VARCHAR(128) PRIMARY KEY NOT NULL,
41 password VARCHAR(128) NOT NULL,
42 fullname VARCHAR(128) NOT NULL,
43 email VARCHAR(128) NOT NULL,
44 organization VARCHAR(128) NOT NULL,
45 imageurl VARCHAR(255) NOT NULL,
46
47 FOREIGN KEY(organization) REFERENCES organizations(name)
48);
49CREATE INDEX organization_idx ON users (organization);
50
51CREATE TABLE IF NOT EXISTS snippets (
52 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
53 username VARCHAR(255) NOT NULL,
54 ts timestamp NOT NULL,
55 content TEXT NOT NULL,
56
57 FOREIGN KEY(username) REFERENCES users(username)
58);
59CREATE INDEX snippets_user_idx ON snippets (username);
60
61CREATE TABLE IF NOT EXISTS follows (
62 username VARCHAR(255) NOT NULL,
63 follows VARCHAR(255) NOT NULL,
64 since INTEGER,
65
66 FOREIGN KEY(username) REFERENCES users(username),
67 FOREIGN KEY(follows) REFERENCES users(username)
68);
69
70INSERT INTO organizations (name, description, imageurl) VALUES('Telenor', 'Telenor is a digital company', '/static/img/c.png');
71INSERT INTO organizations (name, description, imageurl) VALUES('Other', 'Telenor is a digital company', '/static/img/c.png');
72
73/* password is 'secretive' */
74INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('borud', '$2a$08$xHrcnewvS.icboGjrLqH3e/6/vZgPAJp9/fOW1weaXZmreiGQlAfq', 'Bjørn Borud', 'borud@telenordigital.com', 'Telenor', 'https://avatars3.githubusercontent.com/u/1595');
75INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('stalehd', '', 'Ståle Holberg Dahl', 'stalehd@telenordigital.com', 'Telenor', 'https://media.licdn.com/mpr/mpr/shrinknp_200_200/p/3/000/090/163/20f1209.jpg');
76INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('tlan', '', 'Thomas Langås', 'tlan@telenordigital.com', 'Telenor', 'https://yt3.ggpht.com/-FtFMyyKtpiM/AAAAAAAAAAI/AAAAAAAAAAA/y-tBQ0qv4gE/s900-c-k-no-mo-rj-c0xffffff/photo.jpg');
77INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('hansj', '', 'Hans Jørgen Grimstad', 'hansj@telenordigital.com', 'Telenor', 'https://avatars3.githubusercontent.com/u/483862');
78INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('pkk', '', 'Per Kristian Kummermo', 'pkk@telenordigital.com', 'Telenor', 'https://media.licdn.com/mpr/mpr/shrinknp_200_200/p/2/005/030/2b7/164fcba.jpg');
79
80INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('a', '', 'Testuser A', 'a@example.com', 'Other', '/static/img/u.jpg');
81INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES ('b', '', 'Testuser B', 'b@example.com', 'Other', '/static/img/u.jpg');
82
83INSERT INTO snippets (username, ts, content) VALUES ('borud', date('now'), 'This is a test snippet');
84INSERT INTO snippets (username, ts, content) VALUES ('borud', date('now', '-1 day'), 'This is another test snippet');
85INSERT INTO snippets (username, ts, content) VALUES ('stalehd', date('now'), 'Foo bar baz');
86INSERT INTO snippets (username, ts, content) VALUES ('stalehd', date('now','-2 day'), 'The dingo ate my baby');
87INSERT INTO snippets (username, ts, content) VALUES ('pkk', date('now'), 'Some stuff');
88INSERT INTO snippets (username, ts, content) VALUES ('pkk', date('now', '-5 day'), 'Some more stuff');
89INSERT INTO snippets (username, ts, content) VALUES ('tlan', date('now'), 'Spent mucho dineros');
90INSERT INTO snippets (username, ts, content) VALUES ('tlan', date('now', '-9 day'), 'Blah di blah');
91
92INSERT INTO follows (username, follows, since) VALUES ('borud', 'stalehd', date('now'));
93INSERT INTO follows (username, follows, since) VALUES ('stalehd', 'borud', date('now'));
94INSERT INTO follows (username, follows, since) VALUES ('tlan', 'borud', date('now'));
95INSERT INTO follows (username, follows, since) VALUES ('pkk', 'borud', date('now'));
96`
97
98// Struct containing the storage state information.
99//
100type SqliteStore struct {
101 mutex *sync.Mutex
102 fileName string
103 db *sql.DB
104 isOpen bool
105}
106
107// Create an SqliteStore instance.
108//
109func CreateSqliteStore(dbFile string) Store {
110 ss := SqliteStore{
111 mutex: &sync.Mutex{},
112 fileName: dbFile,
113 isOpen: false,
114 }
115
116 return &ss
117}
118
119// Convert time to milliseconds since epoch.
120//
121func unixMilli(t time.Time) int64 {
122 return t.Round(time.Millisecond).UnixNano() / (int64(time.Millisecond) / int64(time.Nanosecond))
123}
124
125// Raise panic of the database is not open when we assume it is.
126//
127func (st *SqliteStore) ensureDbOpen() {
128 if !st.isOpen {
129 panic("Database not open")
130 }
131}
132
133// Initialize database instance.
134//
135func (st *SqliteStore) Init() (err error) {
136 st.mutex.Lock()
137 defer st.mutex.Unlock()
138
139 // Record if database existed prior to opening it.
140 var databaseFileExisted = false
141 if _, err := os.Stat(st.fileName); err == nil {
142 databaseFileExisted = true
143 }
144
145 db, err := sql.Open("sqlite3", st.fileName)
146 if err != nil {
147 return errors.New("Unable to open database in " + st.fileName)
148 }
149
150 st.db = db
151
152 // Ping the database to make sure it is there.
153 if err = st.db.Ping(); err != nil {
154 return errors.New("Failed to ping database right after opening")
155 }
156
157 // If the database did not exist and we just created it the schema has to be initialized.
158 if !databaseFileExisted {
159 _ = st.createSchema()
160 }
161
162 st.isOpen = true
163
164 return nil
165}
166
167// Create database schema
168//
169func (st *SqliteStore) createSchema() error {
170 log.Printf("Creating database schema")
171 if _, err := st.db.Exec(schema); err != nil {
172 panic("Unable to create schema: " + err.Error())
173 }
174
175 return nil
176}
177
178func (st *SqliteStore) CreateSnippet(snippet model.Snippet) (id int64, err error) {
179 st.mutex.Lock()
180 defer st.mutex.Unlock()
181 st.ensureDbOpen()
182
183 res, err := st.db.Exec("INSERT INTO snippets (username, ts, content) values(?, date('now'), ?)",
184 snippet.Username,
185 snippet.Content)
186
187 if err != nil {
188 return
189 }
190
191 id, err = res.LastInsertId()
192
193 return
194}
195
196func (st *SqliteStore) ReadSnippet(snippetId int64) (snippet model.Snippet, err error) {
197 st.mutex.Lock()
198 defer st.mutex.Unlock()
199
200 st.ensureDbOpen()
201
202 err = st.db.QueryRow("SELECT id, username, ts, strftime('%W', ts), content FROM snippets WHERE id = ?", snippetId).
203 Scan(&snippet.SnippetId,
204 &snippet.Username,
205 &snippet.Timestamp,
206 &snippet.Week,
207 &snippet.Content)
208 return
209}
210
211func (st *SqliteStore) UpdateSnippet(snippet model.Snippet) (err error) {
212 st.mutex.Lock()
213 defer st.mutex.Unlock()
214
215 st.ensureDbOpen()
216
217 _, err = st.db.Exec("UPDATE snippets SET username = ?, content = ? WHERE id = ?", snippet.Username, snippet.Content, snippet.SnippetId)
218
219 return
220}
221
222func (st *SqliteStore) DeleteSnippet(snippetId int64) (err error) {
223 st.mutex.Lock()
224 defer st.mutex.Unlock()
225
226 st.ensureDbOpen()
227
228 _, err = st.db.Exec("DELETE FROM snippets WHERE id = ?", snippetId)
229
230 return
231}
232
233func (st *SqliteStore) ListSnippetsMain(username string, offset int32, limit int32) (snippets []model.Snippet, err error) {
234 st.mutex.Lock()
235 defer st.mutex.Unlock()
236 st.ensureDbOpen()
237
238 // rows, err := st.db.Query("SELECT id, username, ts, strftime('%W', ts), content FROM snippets ORDER BY ts DESC LIMIT ?,?", offset, limit)
239
240 rows, err := st.db.Query("SELECT users.username, users.fullname, users.imageurl, snippets.id, snippets.ts, strftime('%W', snippets.ts), snippets.content "+
241 "FROM users, snippets "+
242 "WHERE snippets.username = users.username "+
243 "ORDER BY snippets.id DESC "+
244 "LIMIT ?,?",
245 offset,
246 limit)
247
248 if err != nil {
249 return
250 }
251 defer rows.Close()
252
253 for rows.Next() {
254 snippet := model.Snippet{}
255 user := model.User{}
256 err = rows.Scan(
257 &user.Username,
258 &user.Fullname,
259 &user.Imageurl,
260 &snippet.SnippetId,
261 &snippet.Timestamp,
262 &snippet.Week,
263 &snippet.Content,
264 )
265
266 snippet.Username = user.Username
267 snippet.User = &user
268 snippets = append(snippets, snippet)
269 }
270 return
271}
272
273func (st *SqliteStore) ListUserSnippets(username string, offset int32, limit int32) (snippets []model.Snippet, err error) {
274 st.mutex.Lock()
275 defer st.mutex.Unlock()
276 st.ensureDbOpen()
277
278 // rows, err := st.db.Query("SELECT id, username, ts, strftime('%W', ts), content FROM snippets WHERE username = ? ORDER BY ts DESC LIMIT ?,?", username, offset, limit)
279 rows, err := st.db.Query("SELECT users.username, users.fullname, users.imageurl, snippets.id, snippets.ts, strftime('%W', snippets.ts), snippets.content "+
280 "FROM users, snippets "+
281 "WHERE snippets.username = ? AND snippets.username = users.username "+
282 "ORDER BY snippets.id DESC "+
283 "LIMIT ?,?",
284 username,
285 offset,
286 limit)
287
288 if err != nil {
289 return
290 }
291 defer rows.Close()
292
293 for rows.Next() {
294 snippet := model.Snippet{}
295 user := model.User{}
296
297 err = rows.Scan(
298 &user.Username,
299 &user.Fullname,
300 &user.Imageurl,
301 &snippet.SnippetId,
302 &snippet.Timestamp,
303 &snippet.Week,
304 &snippet.Content,
305 )
306
307 snippet.Username = user.Username
308 snippet.User = &user
309 snippets = append(snippets, snippet)
310 }
311 return
312}
313
314func (st *SqliteStore) CreateUser(user model.User) (err error) {
315 st.mutex.Lock()
316 defer st.mutex.Unlock()
317
318 st.ensureDbOpen()
319
320 _, err = st.db.Exec("INSERT INTO users (username, password, fullname, email, organization, imageurl) VALUES (?,?,?,?,?,?)",
321 &user.Username,
322 &user.Password,
323 &user.Fullname,
324 &user.Email,
325 &user.Organization,
326 &user.Imageurl)
327
328 return
329}
330
331func (st *SqliteStore) ReadUser(username string) (user model.User, err error) {
332 st.mutex.Lock()
333 defer st.mutex.Unlock()
334
335 st.ensureDbOpen()
336
337 err = st.db.QueryRow("SELECT username, password, fullname, email, organization, imageurl FROM users WHERE username = ?", username).
338 Scan(&user.Username,
339 &user.Password,
340 &user.Fullname,
341 &user.Email,
342 &user.Organization,
343 &user.Imageurl)
344
345 return
346}
347
348func (st *SqliteStore) UpdateUser(user model.User) (err error) {
349 st.mutex.Lock()
350 defer st.mutex.Unlock()
351
352 st.ensureDbOpen()
353
354 // Note that organization, once set, cannot be changed via the normal API
355 _, err = st.db.Exec("UPDATE users SET password = ?, fullname = ?, email = ?, imageurl = ? WHERE username = ?",
356 user.Password,
357 user.Fullname,
358 user.Email,
359 user.Imageurl,
360 user.Username)
361
362 return
363}
364
365func (st *SqliteStore) DeleteUser(username string) (err error) {
366 st.mutex.Lock()
367 defer st.mutex.Unlock()
368
369 st.ensureDbOpen()
370
371 _, err = st.db.Exec("DELETE FROM users WHERE username = ?", username)
372
373 return
374}
375
376func (st *SqliteStore) FollowUser(username string, follow string) (err error) {
377 if username == follow {
378 return errors.New("Cannot follow self")
379 }
380
381 st.mutex.Lock()
382 defer st.mutex.Unlock()
383 st.ensureDbOpen()
384
385 _, err = st.db.Exec("INSERT INTO follows (username, follows, since) VALUES(?,?,date('now'))",
386 &username,
387 &follow)
388 return
389}
390
391func (st *SqliteStore) UnfollowUser(username string, follows string) (err error) {
392 st.mutex.Lock()
393 defer st.mutex.Unlock()
394 st.ensureDbOpen()
395
396 _, err = st.db.Exec("DELETE FROM follows WHERE username = ? AND follows = ?", username, follows)
397 return
398}
399
400func (st *SqliteStore) ListFollow(username string) (follows []string, followers []string, err error) {
401 st.mutex.Lock()
402 defer st.mutex.Unlock()
403 st.ensureDbOpen()
404
405 rows, err := st.db.Query("SELECT username, follows FROM follows WHERE username = ? OR follows = ?", username, username)
406 if err != nil {
407 return
408 }
409 defer rows.Close()
410
411 // Since we are querying both followers and whom the user follows,
412 // we need to sort out what's what and split the two cases into
413 // two different result arays.
414 for rows.Next() {
415 var first string
416 var second string
417 err = rows.Scan(&first, &second)
418 if err != nil {
419 return
420 }
421
422 if first == username {
423 follows = append(follows, first)
424 } else {
425 followers = append(followers, first)
426 }
427 }
428
429 return
430}
431
432func (st *SqliteStore) Close() (err error) {
433 st.mutex.Lock()
434 defer st.mutex.Unlock()
435
436 st.ensureDbOpen()
437
438 if err = st.db.Close(); err != nil {
439 return
440 }
441
442 st.isOpen = false
443 return
444}