0 package db
1
2 import (
3 "database/sql"
4 "log"
5 "os"
6 "time"
7 "strings"
8
9 _ "github.com/mattn/go-sqlite3"
10 _ "modernc.org/sqlite"
11 _ "github.com/go-sql-driver/mysql"
12 )
13
14 type Repo struct {
15 ID int
16 UserID int
17 Username string
18 Name string
19 Date int
20 IsPublic bool
21 Description string
22 }
23
24 type User struct {
25 ID int
26 Name string
27 Description string
28 Registration int
29 Connection time.Time
30 Signature string
31 Secret string
32 SecureGit bool
33 }
34
35 type Group struct {
36 ID int
37 Name string
38 Description string
39 }
40
41 type Member struct {
42 Name string
43 UserID int
44 }
45
46 type Access struct {
47 RepoID int
48 GroupID int
49 UserID int
50 Name string
51 Privilege int
52 }
53
54 type Token struct {
55 Hint string
56 Expiration int64
57 ExpirationFormat string
58 UserID int
59 ID int
60 }
61
62 var unixTime string
63 var autoincrement string
64
65 var db *sql.DB
66
67 func Init(dbType string, path string, create bool) error {
68
69 isSqlite := dbType == "sqlite3" || dbType == "sqlite"
70 if isSqlite {
71 autoincrement = "AUTOINCREMENT"
72 } else {
73 autoincrement = "AUTO_INCREMENT"
74 }
75
76 if !create && isSqlite {
77 file, err := os.Open(path)
78 if os.IsNotExist(err) {
79 file, err := os.Create(path)
80 if err != nil {
81 return err
82 }
83 file.Close()
84 log.Println("Creating database " + path)
85 create = true
86 } else {
87 file.Close()
88 log.Println("Loading database " + path)
89 }
90 }
91
92 var err error
93 db, err = sql.Open(dbType, path)
94 if err != nil {
95 return err
96 }
97 err = db.Ping()
98 if err != nil {
99 return err
100 }
101 unixTime = "UNIX_TIMESTAMP()"
102 if isSqlite {
103 unixTime = "strftime('%s', 'now')"
104 }
105 if create {
106 return createTable(db)
107 }
108 return nil
109 }
110
111 func Close() error {
112 return db.Close()
113 }
114
115 func createTable(db *sql.DB) error {
116
117 userTable := `CREATE TABLE user (
118 userID INTEGER NOT NULL PRIMARY KEY ` + autoincrement + `,
119 name TEXT UNIQUE NOT NULL,
120 password TEXT NOT NULL,
121 description TEXT DEFAULT "" NOT NULL,
122 secret TEXT DEFAULT "" NOT NULL,
123 creation INTEGER NOT NULL,
124 securegit INTEGER DEFAULT 0 NOT NULL
125 );`
126
127 groupTable := `CREATE TABLE groups (
128 groupID INTEGER NOT NULL PRIMARY KEY ` + autoincrement + `,
129 owner INTEGER NOT NULL,
130 name TEXT UNIQUE NOT NULL,
131 description TEXT DEFAULT "",
132 creation INTEGER NOT NULL
133 );`
134
135 memberTable := `CREATE TABLE member (
136 groupID INTEGER NOT NULL,
137 userID INTEGER NOT NULL
138 );`
139
140 certificateTable := `CREATE TABLE certificate (
141 userID INTEGER NOT NULL,
142 hash TEXT UNIQUE NOT NULL,
143 creation INTEGER NOT NULL
144 );`
145
146 accessTable := `CREATE TABLE access (
147 repoID INTEGER NOT NULL,
148 groupID INTEGER,
149 userID INTEGER,
150 privilege INTEGER NOT NULL
151 );`
152
153 repoTable := `CREATE TABLE repo (
154 repoID INTEGER NOT NULL PRIMARY KEY ` + autoincrement + `,
155 userID INTEGER NOT NULL,
156 name TEXT NOT NULL,
157 description TEXT DEFAULT "",
158 creation INTEGER NOT NULL,
159 public INTEGER DEFAULT 0,
160 securegit INTEGER DEFAULT 0 NOT NULL
161 );`
162
163 tokenTable := `CREATE TABLE token (
164 tokenID INTEGER NOT NULL PRIMARY KEY ` + autoincrement + `,
165 userID INTEGER NOT NULL,
166 token TEXT NOT NULL,
167 hint TEXT NOT NULL,
168 expiration INTEGER NOT NULL
169 );`
170
171 userConstraint := `CREATE UNIQUE INDEX username_upper ON user (
172 UPPER(name), UPPER(name)
173 );`
174
175 _, err := db.Exec(userTable)
176 if err != nil {
177 return err
178 }
179 log.Println("Users table created")
180
181 _, err = db.Exec(groupTable)
182 if err != nil {
183 return err
184 }
185 log.Println("Groups table created")
186
187 _, err = db.Exec(memberTable)
188 if err != nil {
189 return err
190 }
191 log.Println("Members table created")
192
193 _, err = db.Exec(certificateTable)
194 if err != nil {
195 return err
196 }
197 log.Println("Certificates table created")
198
199 _, err = db.Exec(accessTable)
200 if err != nil {
201 return err
202 }
203 log.Println("Access table created")
204
205 _, err = db.Exec(repoTable)
206 if err != nil {
207 return err
208 }
209 log.Println("Repositories table created")
210
211 _, err = db.Exec(tokenTable)
212 if err != nil {
213 return err
214 }
215 log.Println("Tokens table created")
216
217 _, err = db.Exec(userConstraint)
218 if err != nil {
219 return err
220 }
221 log.Println("Users constraint created")
222
223 return nil
224 }
225
226 func printOnSuccess(query string) {
227 res, err := db.Exec(query)
228 if err == nil {
229 if strings.Contains(query, "UPDATE") {
230 rows, err := res.RowsAffected()
231 if err == nil && rows > 0 {
232 log.Println(query)
233 }
234 } else {
235 log.Println(query)
236 }
237 }
238 }
239
240 /* add missing field to database */
241 func UpdateTable() {
242
243 /* user table */
244 printOnSuccess("CREATE TABLE user;")
245
246 printOnSuccess("ALTER TABLE user ADD " +
247 "userID INTEGER NOT NULL PRIMARY KEY " + autoincrement)
248 printOnSuccess("ALTER TABLE user ADD name TEXT UNIQUE NOT NULL;")
249 printOnSuccess("ALTER TABLE user ADD password TEXT UNIQUE NOT NULL;")
250
251 printOnSuccess(`ALTER TABLE user ADD
252 description TEXT DEFAULT "";`)
253 printOnSuccess(`UPDATE user SET description=""
254 WHERE description IS NULL;`)
255
256 printOnSuccess(`ALTER TABLE user ADD
257 secret TEXT DEFAULT "";`)
258 printOnSuccess(`UPDATE user SET secret=""
259 WHERE secret IS NULL;`)
260
261 printOnSuccess(`ALTER TABLE user ADD
262 creation INTEGER NOT NULL;`)
263 printOnSuccess(`UPDATE user SET creation=` + unixTime +
264 ` WHERE creation IS NULL;`)
265
266 printOnSuccess("ALTER TABLE user ADD securegit INTEGER")
267 printOnSuccess("UPDATE user SET securegit=0 WHERE securegit IS NULL;")
268
269 printOnSuccess(`CREATE UNIQUE INDEX username_upper ON user (
270 UPPER(name), UPPER(name));`)
271
272 /* groups table */
273 printOnSuccess("CREATE TABLE groups;")
274
275 printOnSuccess("ALTER TABLE groups ADD " +
276 "groupID INTEGER NOT NULL PRIMARY KEY " + autoincrement)
277 printOnSuccess("ALTER TABLE groups ADD owner INTEGER NOT NULL;")
278 printOnSuccess("ALTER TABLE groups ADD name TEXT UNIQUE NOT NULL;")
279
280 printOnSuccess(`ALTER TABLE groups ADD
281 description TEXT DEFAULT "";`)
282 printOnSuccess(`UPDATE groups SET description=""
283 WHERE description IS NULL;`)
284
285 printOnSuccess(`ALTER TABLE groups ADD
286 creation INTEGER NOT NULL;`)
287 printOnSuccess(`UPDATE groups SET creation=` + unixTime +
288 ` WHERE creation IS NULL;`)
289
290 /* member table */
291 printOnSuccess("CREATE TABLE member;")
292 printOnSuccess("ALTER TABLE member ADD groupID INTEGER NOT NULL;")
293 printOnSuccess("ALTER TABLE member ADD userID INTEGER NOT NULL;")
294
295 /* certificate table */
296 printOnSuccess("CREATE TABLE certificate;")
297 printOnSuccess("ALTER TABLE certificate ADD userID INTEGER NOT NULL;")
298 printOnSuccess("ALTER TABLE certificate ADD " +
299 "hash TEXT UNIQUE NOT NULL;")
300
301 printOnSuccess(`ALTER TABLE certificate ADD
302 creation INTEGER;`)
303 printOnSuccess(`UPDATE certificate SET creation=` + unixTime +
304 ` WHERE creation IS NULL;`)
305
306 /* access table */
307 printOnSuccess("CREATE TABLE access;")
308 printOnSuccess("ALTER TABLE access ADD repoID INTEGER NOT NULL;")
309 printOnSuccess("ALTER TABLE access ADD groupID INTEGER;")
310 printOnSuccess("ALTER TABLE access ADD userID INTEGER;")
311 printOnSuccess("ALTER TABLE access ADD privilege INTEGER NOT NULL;")
312
313 /* repo table */
314 printOnSuccess("CREATE TABLE repo;")
315 printOnSuccess("ALTER TABLE repo ADD " +
316 "repoID INTEGER NOT NULL PRIMARY KEY " + autoincrement)
317 printOnSuccess("ALTER TABLE repo ADD userID INTEGER NOT NULL;")
318 printOnSuccess("ALTER TABLE repo ADD name TEXT NOT NULL;")
319
320 printOnSuccess(`ALTER TABLE repo ADD
321 description TEXT DEFAULT "";`)
322 printOnSuccess(`UPDATE repo SET description=""
323 WHERE description IS NULL;`)
324
325 printOnSuccess("ALTER TABLE repo ADD creation INTEGER;")
326 printOnSuccess(`UPDATE repo SET creation=` + unixTime +
327 ` WHERE creation IS NULL;`)
328
329 printOnSuccess("ALTER TABLE repo ADD public INTEGER NOT NULL")
330 printOnSuccess("UPDATE repo SET public=0 WHERE public IS NULL;")
331
332 printOnSuccess(`ALTER TABLE repo ADD securegit INTEGER NOT NULL`)
333 printOnSuccess(`UPDATE repo SET securegit=0 WHERE securegit IS NULL;`)
334
335 /* token table */
336 printOnSuccess("CREATE TABLE token;")
337 printOnSuccess("ALTER TABLE token ADD " +
338 "tokenID INTEGER NOT NULL PRIMARY KEY " + autoincrement)
339 printOnSuccess("ALTER TABLE token ADD userID INTEGER NOT NULL;")
340 printOnSuccess("ALTER TABLE token ADD token TEXT NOT NULL;")
341 printOnSuccess("ALTER TABLE token ADD hint TEXT NOT NULL;")
342 printOnSuccess("ALTER TABLE token ADD expiration INTEGER NOT NULL;")
343
344 }
345