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
16 sqlCreateUserTable = `CREATE TABLE IF NOT EXISTS user (
17 id INTEGER PRIMARY KEY AUTOINCREMENT,
18 name TEXT NOT NULL,
19 login TEXT UNIQUE,
20 email TEXT UNIQUE,
21 password TEXT,
22 admin BOOLEAN NOT NULL,
23 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
24 updated_at DATETIME NOT NULL
25 );`
26
27 sqlCreatePublicKeyTable = `CREATE TABLE IF NOT EXISTS public_key (
28 id INTEGER PRIMARY KEY AUTOINCREMENT,
29 user_id INTEGER NOT NULL,
30 public_key TEXT NOT NULL,
31 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
32 updated_at DATETIME NOT NULL,
33 UNIQUE (user_id, public_key),
34 CONSTRAINT user_id_fk
35 FOREIGN KEY(user_id) REFERENCES user(id)
36 ON DELETE CASCADE
37 ON UPDATE CASCADE
38 );`
39
40 sqlCreateRepoTable = `CREATE TABLE IF NOT EXISTS repo (
41 id INTEGER PRIMARY KEY AUTOINCREMENT,
42 name TEXT NOT NULL UNIQUE,
43 project_name TEXT NOT NULL,
44 description TEXT NOT NULL,
45 private BOOLEAN NOT NULL,
46 create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
47 updated_at DATETIME NOT NULL,
48 );`
49
50 sqlCreateCollabTable = `CREATE TABLE IF NOT EXISTS collab (
51 id INTEGER PRIMARY KEY AUTOINCREMENT,
52 user_id INTEGER NOT NULL,
53 repo_id INTEGER NOT NULL,
54 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
55 updated_at DATETIME NOT NULL,
56 UNIQUE (user_id, repo_id),
57 CONSTRAINT user_id_fk
58 FOREIGN KEY(user_id) REFERENCES user(id)
59 ON DELETE CASCADE
60 ON UPDATE CASCADE,
61 CONSTRAINT repo_id_fk
62 FOREIGN KEY(repo_id) REFERENCES repo(id)
63 ON DELETE CASCADE
64 ON UPDATE CASCADE
65 );`
66
67 // Config.
68 sqlInsertConfig = `INSERT INTO config (name, host, port, anon_access, allow_keyless, updated_at) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP);`
69 sqlSelectConfig = `SELECT id, name, host, port, anon_access, allow_keyless, created_at, updated_at FROM config WHERE id = ?;`
70 sqlUpdateConfigName = `UPDATE config SET name = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
71 sqlUpdateConfigHost = `UPDATE config SET host = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
72 sqlUpdateConfigPort = `UPDATE config SET port = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
73 sqlUpdateConfigAnon = `UPDATE config SET anon_access = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
74 sqlUpdateConfigKeyless = `UPDATE config SET allow_keyless = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
75
76 // User.
77 sqlInsertUser = `INSERT INTO user (name, login, email, password, admin, updated_at) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP);`
78 sqlDeleteUser = `DELETE FROM user WHERE id = ?;`
79 sqlSelectUser = `SELECT id, name, login, email, password, admin, created_at, updated_at FROM user WHERE id = ?;`
80 sqlSelectUserByLogin = `SELECT id, name, login, email, password, admin, created_at, updated_at FROM user WHERE login = ?;`
81 sqlSelectUserByEmail = `SELECT id, name, login, email, password, admin, created_at, updated_at FROM user WHERE email = ?;`
82 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 = ?;`
83 sqlUpdateUserName = `UPDATE user SET name = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
84 sqlUpdateUserLogin = `UPDATE user SET login = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
85 sqlUpdateUserEmail = `UPDATE user SET email = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
86 sqlUpdateUserPassword = `UPDATE user SET password = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
87 sqlUpdateUserAdmin = `UPDATE user SET admin = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;`
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 sqlDeleteCollab = `DELETE FROM collab WHERE user_id = ? AND repo_id;`
106 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 = ?;`
107)