💾 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

View Raw

More Information

⬅️ Previous capture (2023-11-04)

-=-=-=-=-=-=-

Go Back

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