1package sqlite
2
3var (
4 sqlCreateConfigTable = `CREATE TABLE IF NOT EXISTS config (
5 id INTEGER PRIMARY KEY AUTOINCREMENT,
6 name TEXT NOT NULL,
7 host TEXT NOT NULL,
8 port INTEGER NOT NULL,
9 anon_access TEXT NOT NULL,
10 allow_keyless BOOLEAN NOT NULL,
11 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
12 updated_at DATETIME NOT NULL
13 );`
14
15 sqlCreateUserTable = `CREATE TABLE IF NOT EXISTS user (
16 id INTEGER PRIMARY KEY AUTOINCREMENT,
17 name TEXT NOT NULL,
18 login TEXT UNIQUE,
19 email TEXT UNIQUE,
20 password TEXT,
21 admin BOOLEAN NOT NULL,
22 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
23 updated_at DATETIME NOT NULL
24 );`
25
26 sqlCreatePublicKeyTable = `CREATE TABLE IF NOT EXISTS public_key (
27 id INTEGER PRIMARY KEY AUTOINCREMENT,
28 user_id INTEGER NOT NULL,
29 public_key TEXT NOT NULL,
30 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
31 updated_at DATETIME NOT NULL,
32 UNIQUE (user_id, public_key),
33 CONSTRAINT user_id_fk
34 FOREIGN KEY(user_id) REFERENCES user(id)
35 ON DELETE CASCADE
36 ON UPDATE CASCADE
37 );`
38
39 sqlCreateRepoTable = `CREATE TABLE IF NOT EXISTS repo (
40 id INTEGER PRIMARY KEY AUTOINCREMENT,
41 name TEXT NOT NULL UNIQUE,
42 project_name TEXT NOT NULL,
43 description TEXT NOT NULL,
44 private BOOLEAN NOT NULL,
45 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
46 updated_at DATETIME NOT NULL
47 );`
48
49 sqlCreateCollabTable = `CREATE TABLE IF NOT EXISTS collab (
50 id INTEGER PRIMARY KEY AUTOINCREMENT,
51 user_id INTEGER NOT NULL,
52 repo_id INTEGER NOT NULL,
53 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
54 updated_at DATETIME NOT NULL,
55 UNIQUE (user_id, repo_id),
56 CONSTRAINT user_id_fk
57 FOREIGN KEY(user_id) REFERENCES user(id)
58 ON DELETE CASCADE
59 ON UPDATE CASCADE,
60 CONSTRAINT repo_id_fk
61 FOREIGN KEY(repo_id) REFERENCES repo(id)
62 ON DELETE CASCADE
63 ON UPDATE CASCADE
64 );`
65
66 // Config.
67 sqlInsertConfig = `INSERT INTO config (name, host, port, anon_access, allow_keyless, updated_at) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP);`
68 sqlSelectConfig = `SELECT id, name, host, port, anon_access, allow_keyless, created_at, updated_at FROM config WHERE id = ?;`
69 sqlUpdateConfigName = `UPDATE config SET name = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
70 sqlUpdateConfigHost = `UPDATE config SET host = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
71 sqlUpdateConfigPort = `UPDATE config SET port = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
72 sqlUpdateConfigAnon = `UPDATE config SET anon_access = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
73 sqlUpdateConfigKeyless = `UPDATE config SET allow_keyless = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
74
75 // User.
76 sqlInsertUser = `INSERT INTO user (name, login, email, password, admin, updated_at) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP);`
77 sqlDeleteUser = `DELETE FROM user WHERE id = ?;`
78 sqlSelectUser = `SELECT id, name, login, email, password, admin, created_at, updated_at FROM user WHERE id = ?;`
79 sqlSelectUserByLogin = `SELECT id, name, login, email, password, admin, created_at, updated_at FROM user WHERE login = ?;`
80 sqlSelectUserByEmail = `SELECT id, name, login, email, password, admin, created_at, updated_at FROM user WHERE email = ?;`
81 sqlSelectUserByPublicKey = `SELECT u.id, u.name, u.login, u.email, u.password, u.admin, u.created_at, u.updated_at FROM user u INNER JOIN public_key pk ON u.id = pk.user_id WHERE pk.public_key = ?;`
82 sqlUpdateUserName = `UPDATE user SET name = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
83 sqlUpdateUserLogin = `UPDATE user SET login = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
84 sqlUpdateUserEmail = `UPDATE user SET email = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
85 sqlUpdateUserPassword = `UPDATE user SET password = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
86 sqlUpdateUserAdmin = `UPDATE user SET admin = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
87 sqlCountUsers = `SELECT COUNT(*) FROM user;`
88
89 // Public Key.
90 sqlInsertPublicKey = `INSERT INTO public_key (user_id, public_key, updated_at) VALUES (?, ?, CURRENT_TIMESTAMP);`
91 sqlDeletePublicKey = `DELETE FROM public_key WHERE id = ?;`
92 sqlSelectUserPublicKeys = `SELECT id, user_id, public_key, created_at, updated_at FROM public_key WHERE user_id = ?;`
93
94 // Repo.
95 sqlInsertRepo = `INSERT INTO repo (name, project_name, description, private, updated_at) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP);`
96 sqlDeleteRepo = `DELETE FROM repo WHERE id = ?;`
97 sqlDeleteRepoWithName = `DELETE FROM repo WHERE name = ?;`
98 sqlSelectRepoByName = `SELECT id, name, project_name, description, private, created_at, updated_at FROM repo WHERE name = ?;`
99 sqlUpdateRepoProjectNameByName = `UPDATE repo SET project_name = ?, updated_at = CURRENT_TIMESTAMP WHERE name = ?;`
100 sqlUpdateRepoDescriptionByName = `UPDATE repo SET description = ?, updated_at = CURRENT_TIMESTAMP WHERE name = ?;`
101 sqlUpdateRepoPrivateByName = `UPDATE repo SET private = ?, updated_at = CURRENT_TIMESTAMP WHERE name = ?;`
102
103 // Collab.
104 sqlInsertCollab = `INSERT INTO collab (user_id, repo_id, updated_at) VALUES (?, ?, CURRENT_TIMESTAMP);`
105 sqlInsertCollabByName = `INSERT INTO collab (user_id, repo_id, updated_at) VALUES (?, (SELECT id FROM repo WHERE name = ?), CURRENT_TIMESTAMP);`
106 sqlDeleteCollab = `DELETE FROM collab WHERE user_id = ? AND repo_id = ?;`
107 sqlDeleteCollabByName = `DELETE FROM collab WHERE user_id = ? AND repo_id = (SELECT id FROM repo WHERE name = ?);`
108 sqlSelectRepoCollabs = `SELECT user.id, user.name, user.login, user.email, user.admin, user.created_at, user.updated_at FROM user INNER JOIN collab ON user.id = collab.user_id WHERE collab.repo_id = ?;`
109 sqlSelectRepoCollabsByName = `SELECT user.id, user.name, user.login, user.email, user.admin, user.created_at, user.updated_at FROM user INNER JOIN collab ON user.id = collab.user_id WHERE collab.repo_id = (SELECT id FROM repo WHERE name = ?);`
110 sqlSelectRepoPublicKeys = `SELECT public_key.id, public_key.user_id, public_key.public_key, public_key.created_at, public_key.updated_at FROM public_key INNER JOIN collab ON public_key.user_id = collab.user_id WHERE collab.repo_id = ?;`
111 sqlSelectRepoPublicKeysByName = `SELECT public_key.id, public_key.user_id, public_key.public_key, public_key.created_at, public_key.updated_at FROM public_key INNER JOIN collab ON public_key.user_id = collab.user_id WHERE collab.repo_id = (SELECT id FROM repo WHERE name = ?);`
112)