1package sqlite
2
3var (
4 sqlCreateUserTable = `CREATE TABLE IF NOT EXISTS user (
5 id INTEGER PRIMARY KEY AUTOINCREMENT,
6 name TEXT NOT NULL,
7 login TEXT UNIQUE,
8 email TEXT UNIQUE,
9 password TEXT,
10 admin BOOLEAN NOT NULL,
11 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
12 updated_at DATETIME NOT NULL
13 );`
14
15 sqlCreatePublicKeyTable = `CREATE TABLE IF NOT EXISTS public_key (
16 id INTEGER PRIMARY KEY AUTOINCREMENT,
17 user_id INTEGER NOT NULL,
18 public_key TEXT NOT NULL,
19 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
20 updated_at DATETIME NOT NULL,
21 UNIQUE (user_id, public_key),
22 CONSTRAINT user_id_fk
23 FOREIGN KEY(user_id) REFERENCES user(id)
24 ON DELETE CASCADE
25 ON UPDATE CASCADE
26 );`
27
28 sqlCreateRepoTable = `CREATE TABLE IF NOT EXISTS repo (
29 id INTEGER PRIMARY KEY AUTOINCREMENT,
30 name TEXT NOT NULL UNIQUE,
31 project_name TEXT NOT NULL,
32 description TEXT NOT NULL,
33 private BOOLEAN NOT NULL,
34 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
35 updated_at DATETIME NOT NULL
36 );`
37
38 sqlCreateCollabTable = `CREATE TABLE IF NOT EXISTS collab (
39 id INTEGER PRIMARY KEY AUTOINCREMENT,
40 user_id INTEGER NOT NULL,
41 repo_id INTEGER NOT NULL,
42 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
43 updated_at DATETIME NOT NULL,
44 UNIQUE (user_id, repo_id),
45 CONSTRAINT user_id_fk
46 FOREIGN KEY(user_id) REFERENCES user(id)
47 ON DELETE CASCADE
48 ON UPDATE CASCADE,
49 CONSTRAINT repo_id_fk
50 FOREIGN KEY(repo_id) REFERENCES repo(id)
51 ON DELETE CASCADE
52 ON UPDATE CASCADE
53 );`
54
55 // User.
56 sqlInsertUser = `INSERT INTO user (name, login, email, password, admin, updated_at) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP);`
57 sqlDeleteUser = `DELETE FROM user WHERE id = ?;`
58 sqlSelectUser = `SELECT id, name, login, email, password, admin, created_at, updated_at FROM user WHERE id = ?;`
59 sqlSelectUserByLogin = `SELECT id, name, login, email, password, admin, created_at, updated_at FROM user WHERE login = ?;`
60 sqlSelectUserByEmail = `SELECT id, name, login, email, password, admin, created_at, updated_at FROM user WHERE email = ?;`
61 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 = ?;`
62 sqlUpdateUserName = `UPDATE user SET name = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
63 sqlUpdateUserLogin = `UPDATE user SET login = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
64 sqlUpdateUserEmail = `UPDATE user SET email = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
65 sqlUpdateUserPassword = `UPDATE user SET password = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
66 sqlUpdateUserAdmin = `UPDATE user SET admin = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
67 sqlCountUsers = `SELECT COUNT(*) FROM user;`
68
69 // Public Key.
70 sqlInsertPublicKey = `INSERT INTO public_key (user_id, public_key, updated_at) VALUES (?, ?, CURRENT_TIMESTAMP);`
71 sqlDeletePublicKey = `DELETE FROM public_key WHERE id = ?;`
72 sqlSelectUserPublicKeys = `SELECT id, user_id, public_key, created_at, updated_at FROM public_key WHERE user_id = ?;`
73
74 // Repo.
75 sqlInsertRepo = `INSERT INTO repo (name, project_name, description, private, updated_at) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP);`
76 sqlDeleteRepo = `DELETE FROM repo WHERE id = ?;`
77 sqlDeleteRepoWithName = `DELETE FROM repo WHERE name = ?;`
78 sqlSelectRepoByName = `SELECT id, name, project_name, description, private, created_at, updated_at FROM repo WHERE name = ?;`
79 sqlUpdateRepoProjectNameByName = `UPDATE repo SET project_name = ?, updated_at = CURRENT_TIMESTAMP WHERE name = ?;`
80 sqlUpdateRepoDescriptionByName = `UPDATE repo SET description = ?, updated_at = CURRENT_TIMESTAMP WHERE name = ?;`
81 sqlUpdateRepoPrivateByName = `UPDATE repo SET private = ?, updated_at = CURRENT_TIMESTAMP WHERE name = ?;`
82
83 // Collab.
84 sqlInsertCollab = `INSERT INTO collab (user_id, repo_id, updated_at) VALUES (?, ?, CURRENT_TIMESTAMP);`
85 sqlInsertCollabByName = `INSERT INTO collab (user_id, repo_id, updated_at) VALUES (?, (SELECT id FROM repo WHERE name = ?), CURRENT_TIMESTAMP);`
86 sqlDeleteCollab = `DELETE FROM collab WHERE user_id = ? AND repo_id = ?;`
87 sqlDeleteCollabByName = `DELETE FROM collab WHERE user_id = ? AND repo_id = (SELECT id FROM repo WHERE name = ?);`
88 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 = ?;`
89 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 = ?);`
90 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 = ?;`
91 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 = ?);`
92)