💾 Archived View for gemini.rmf-dev.com › repo › Vaati › Gemigit › files › dc047a3d3b6ca69dde87dca8fa… captured on 2023-12-28 at 15:32:40. Gemini links have been rewritten to link to archived content
⬅️ Previous capture (2023-11-04)
-=-=-=-=-=-=-
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 ReadOnly bool
61 }
62
63 var unixTime string
64 var autoincrement string
65
66 var db *sql.DB
67
68 func Init(dbType string, path string, create bool) error {
69
70 isSqlite := dbType == "sqlite3" || dbType == "sqlite"
71 if isSqlite {
72 autoincrement = "AUTOINCREMENT"
73 } else {
74 autoincrement = "AUTO_INCREMENT"
75 }
76
77 if !create && isSqlite {
78 file, err := os.Open(path)
79 if os.IsNotExist(err) {
80 file, err := os.Create(path)
81 if err != nil {
82 return err
83 }
84 file.Close()
85 log.Println("Creating database " + path)
86 create = true
87 } else {
88 file.Close()
89 log.Println("Loading database " + path)
90 }
91 }
92
93 var err error
94 db, err = sql.Open(dbType, path)
95 if err != nil {
96 return err
97 }
98 err = db.Ping()
99 if err != nil {
100 return err
101 }
102 unixTime = "UNIX_TIMESTAMP()"
103 if isSqlite {
104 unixTime = "strftime('%s', 'now')"
105 }
106 if create {
107 return createTable(db)
108 }
109 return nil
110 }
111
112 func Close() error {
113 return db.Close()
114 }
115
116 func createTable(db *sql.DB) error {
117
118 userTable := `CREATE TABLE user (
119 userID INTEGER NOT NULL PRIMARY KEY ` + autoincrement + `,
120 name TEXT UNIQUE NOT NULL,
121 password TEXT NOT NULL,
122 description TEXT DEFAULT "" NOT NULL,
123 secret TEXT DEFAULT "" NOT NULL,
124 creation INTEGER NOT NULL,
125 securegit INTEGER DEFAULT 0 NOT NULL
126 );`
127
128 groupTable := `CREATE TABLE groups (
129 groupID INTEGER NOT NULL PRIMARY KEY ` + autoincrement + `,
130 owner INTEGER NOT NULL,
131 name TEXT UNIQUE NOT NULL,
132 description TEXT DEFAULT "",
133 creation INTEGER NOT NULL
134 );`
135
136 memberTable := `CREATE TABLE member (
137 groupID INTEGER NOT NULL,
138 userID INTEGER NOT NULL
139 );`
140
141 certificateTable := `CREATE TABLE certificate (
142 userID INTEGER NOT NULL,
143 hash TEXT UNIQUE NOT NULL,
144 creation INTEGER NOT NULL
145 );`
146
147 accessTable := `CREATE TABLE access (
148 repoID INTEGER NOT NULL,
149 groupID INTEGER,
150 userID INTEGER,
151 privilege INTEGER NOT NULL
152 );`
153
154 repoTable := `CREATE TABLE repo (
155 repoID INTEGER NOT NULL PRIMARY KEY ` + autoincrement + `,
156 userID INTEGER NOT NULL,
157 name TEXT NOT NULL,
158 description TEXT DEFAULT "",
159 creation INTEGER NOT NULL,
160 public INTEGER DEFAULT 0,
161 securegit INTEGER DEFAULT 0 NOT NULL
162 );`
163
164 tokenTable := `CREATE TABLE token (
165 tokenID INTEGER NOT NULL PRIMARY KEY ` + autoincrement + `,
166 userID INTEGER NOT NULL,
167 token TEXT NOT NULL,
168 hint TEXT NOT NULL,
169 expiration INTEGER NOT NULL,
170 readonly INTEGER DEFAULT 0 NOT NULL
171 );`
172
173 userConstraint := `CREATE UNIQUE INDEX username_upper ON user (
174 UPPER(name), UPPER(name)
175 );`
176
177 _, err := db.Exec(userTable)
178 if err != nil {
179 return err
180 }
181 log.Println("Users table created")
182
183 _, err = db.Exec(groupTable)
184 if err != nil {
185 return err
186 }
187 log.Println("Groups table created")
188
189 _, err = db.Exec(memberTable)
190 if err != nil {
191 return err
192 }
193 log.Println("Members table created")
194
195 _, err = db.Exec(certificateTable)
196 if err != nil {
197 return err
198 }
199 log.Println("Certificates table created")
200
201 _, err = db.Exec(accessTable)
202 if err != nil {
203 return err
204 }
205 log.Println("Access table created")
206
207 _, err = db.Exec(repoTable)
208 if err != nil {
209 return err
210 }
211 log.Println("Repositories table created")
212
213 _, err = db.Exec(tokenTable)
214 if err != nil {
215 return err
216 }
217 log.Println("Tokens table created")
218
219 _, err = db.Exec(userConstraint)
220 if err != nil {
221 return err
222 }
223 log.Println("Users constraint created")
224
225 return nil
226 }
227
228 func printOnSuccess(query string) {
229 res, err := db.Exec(query)
230 if err == nil {
231 if strings.Contains(query, "UPDATE") {
232 rows, err := res.RowsAffected()
233 if err == nil && rows > 0 {
234 log.Println(query)
235 }
236 } else {
237 log.Println(query)
238 }
239 }
240 }
241
242 /* add missing field to database */
243 func UpdateTable() {
244
245 /* user table */
246 printOnSuccess("CREATE TABLE user;")
247
248 printOnSuccess("ALTER TABLE user ADD " +
249 "userID INTEGER NOT NULL PRIMARY KEY " + autoincrement)
250 printOnSuccess("ALTER TABLE user ADD name TEXT UNIQUE NOT NULL;")
251 printOnSuccess("ALTER TABLE user ADD password TEXT UNIQUE NOT NULL;")
252
253 printOnSuccess(`ALTER TABLE user ADD
254 description TEXT DEFAULT "";`)
255 printOnSuccess(`UPDATE user SET description=""
256 WHERE description IS NULL;`)
257
258 printOnSuccess(`ALTER TABLE user ADD
259 secret TEXT DEFAULT "";`)
260 printOnSuccess(`UPDATE user SET secret=""
261 WHERE secret IS NULL;`)
262
263 printOnSuccess(`ALTER TABLE user ADD
264 creation INTEGER NOT NULL;`)
265 printOnSuccess(`UPDATE user SET creation=` + unixTime +
266 ` WHERE creation IS NULL;`)
267
268 printOnSuccess("ALTER TABLE user ADD securegit INTEGER")
269 printOnSuccess("UPDATE user SET securegit=0 WHERE securegit IS NULL;")
270
271 printOnSuccess(`CREATE UNIQUE INDEX username_upper ON user (
272 UPPER(name), UPPER(name));`)
273
274 /* groups table */
275 printOnSuccess("CREATE TABLE groups;")
276
277 printOnSuccess("ALTER TABLE groups ADD " +
278 "groupID INTEGER NOT NULL PRIMARY KEY " + autoincrement)
279 printOnSuccess("ALTER TABLE groups ADD owner INTEGER NOT NULL;")
280 printOnSuccess("ALTER TABLE groups ADD name TEXT UNIQUE NOT NULL;")
281
282 printOnSuccess(`ALTER TABLE groups ADD
283 description TEXT DEFAULT "";`)
284 printOnSuccess(`UPDATE groups SET description=""
285 WHERE description IS NULL;`)
286
287 printOnSuccess(`ALTER TABLE groups ADD
288 creation INTEGER NOT NULL;`)
289 printOnSuccess(`UPDATE groups SET creation=` + unixTime +
290 ` WHERE creation IS NULL;`)
291
292 /* member table */
293 printOnSuccess("CREATE TABLE member;")
294 printOnSuccess("ALTER TABLE member ADD groupID INTEGER NOT NULL;")
295 printOnSuccess("ALTER TABLE member ADD userID INTEGER NOT NULL;")
296
297 /* certificate table */
298 printOnSuccess("CREATE TABLE certificate;")
299 printOnSuccess("ALTER TABLE certificate ADD userID INTEGER NOT NULL;")
300 printOnSuccess("ALTER TABLE certificate ADD " +
301 "hash TEXT UNIQUE NOT NULL;")
302
303 printOnSuccess(`ALTER TABLE certificate ADD
304 creation INTEGER;`)
305 printOnSuccess(`UPDATE certificate SET creation=` + unixTime +
306 ` WHERE creation IS NULL;`)
307
308 /* access table */
309 printOnSuccess("CREATE TABLE access;")
310 printOnSuccess("ALTER TABLE access ADD repoID INTEGER NOT NULL;")
311 printOnSuccess("ALTER TABLE access ADD groupID INTEGER;")
312 printOnSuccess("ALTER TABLE access ADD userID INTEGER;")
313 printOnSuccess("ALTER TABLE access ADD privilege INTEGER NOT NULL;")
314
315 /* repo table */
316 printOnSuccess("CREATE TABLE repo;")
317 printOnSuccess("ALTER TABLE repo ADD " +
318 "repoID INTEGER NOT NULL PRIMARY KEY " + autoincrement)
319 printOnSuccess("ALTER TABLE repo ADD userID INTEGER NOT NULL;")
320 printOnSuccess("ALTER TABLE repo ADD name TEXT NOT NULL;")
321
322 printOnSuccess(`ALTER TABLE repo ADD
323 description TEXT DEFAULT "";`)
324 printOnSuccess(`UPDATE repo SET description=""
325 WHERE description IS NULL;`)
326
327 printOnSuccess("ALTER TABLE repo ADD creation INTEGER;")
328 printOnSuccess(`UPDATE repo SET creation=` + unixTime +
329 ` WHERE creation IS NULL;`)
330
331 printOnSuccess("ALTER TABLE repo ADD public INTEGER NOT NULL")
332 printOnSuccess("UPDATE repo SET public=0 WHERE public IS NULL;")
333
334 printOnSuccess(`ALTER TABLE repo ADD securegit INTEGER NOT NULL`)
335 printOnSuccess(`UPDATE repo SET securegit=0 WHERE securegit IS NULL;`)
336
337 /* token table */
338 printOnSuccess("CREATE TABLE token;")
339 printOnSuccess("ALTER TABLE token ADD " +
340 "tokenID INTEGER NOT NULL PRIMARY KEY " + autoincrement)
341 printOnSuccess("ALTER TABLE token ADD userID INTEGER NOT NULL;")
342 printOnSuccess("ALTER TABLE token ADD token TEXT NOT NULL;")
343 printOnSuccess("ALTER TABLE token ADD hint TEXT NOT NULL;")
344 printOnSuccess("ALTER TABLE token ADD expiration INTEGER NOT NULL;")
345 printOnSuccess("ALTER TABLE token ADD readonly " +
346 "INTEGER DEFAULT 0;")
347 printOnSuccess(`UPDATE token SET readonly=0 WHERE readonly IS NULL;`)
348
349 }
350