forked from reinbach/drone
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.go
203 lines (180 loc) · 4.7 KB
/
schema.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
package schema
import (
"database/sql"
)
// SQL statement to create the User Table.
var userTableStmt = `
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT
,email VARCHAR(255) UNIQUE
,password VARCHAR(255)
,token VARCHAR(255) UNIQUE
,name VARCHAR(255)
,gravatar VARCHAR(255)
,created TIMESTAMP
,updated TIMESTAMP
,admin BOOLEAN
,github_login VARCHAR(255)
,github_token VARCHAR(255)
,bitbucket_login VARCHAR(255)
,bitbucket_token VARCHAR(255)
,bitbucket_secret VARCHAR(255)
);
`
// SQL statement to create the Team Table.
var teamTableStmt = `
CREATE TABLE teams (
id INTEGER PRIMARY KEY AUTOINCREMENT
,slug VARCHAR(255) UNIQUE
,name VARCHAR(255)
,email VARCHAR(255)
,gravatar VARCHAR(255)
,created TIMESTAMP
,updated TIMESTAMP
);
`
// SQL statement to create the Member Table.
var memberTableStmt = `
CREATE TABLE members (
id INTEGER PRIMARY KEY AUTOINCREMENT
,team_id INTEGER
,user_id INTEGER
,role INTEGER
);
`
// SQL statement to create the Repo Table.
var repoTableStmt = `
CREATE TABLE repos (
id INTEGER PRIMARY KEY AUTOINCREMENT
,slug VARCHAR(1024) UNIQUE
,host VARCHAR(255)
,owner VARCHAR(255)
,name VARCHAR(255)
,private BOOLEAN
,disabled BOOLEAN
,disabled_pr BOOLEAN
,priveleged BOOLEAN
,timeout INTEGER
,scm VARCHAR(25)
,url VARCHAR(1024)
,username VARCHAR(255)
,password VARCHAR(255)
,public_key VARCHAR(1024)
,private_key VARCHAR(1024)
,params VARCHAR(2000)
,created TIMESTAMP
,updated TIMESTAMP
,user_id INTEGER
,team_id INTEGER
);
`
// SQL statement to create the Commit Table.
var commitTableStmt = `
CREATE TABLE commits (
id INTEGER PRIMARY KEY AUTOINCREMENT
,repo_id INTEGER
,status VARCHAR(255)
,started TIMESTAMP
,finished TIMESTAMP
,duration INTEGER
,attempts INTEGER
,hash VARCHAR(255)
,branch VARCHAR(255)
,pull_request VARCHAR(255)
,author VARCHAR(255)
,gravatar VARCHAR(255)
,timestamp VARCHAR(255)
,message VARCHAR(255)
,created TIMESTAMP
,updated TIMESTAMP
);
`
// SQL statement to create the Build Table.
var buildTableStmt = `
CREATE TABLE builds (
id INTEGER PRIMARY KEY AUTOINCREMENT
,commit_id INTEGER
,slug VARCHAR(255)
,status VARCHAR(255)
,started TIMESTAMP
,finished TIMESTAMP
,duration INTEGER
,created TIMESTAMP
,updated TIMESTAMP
,stdout BLOB
);
`
// SQL statement to create the Settings
var settingsTableStmt = `
CREATE TABLE settings (
id INTEGER PRIMARY KEY
,github_key VARCHAR(255)
,github_secret VARCHAR(255)
,bitbucket_key VARCHAR(255)
,bitbucket_secret VARCHAR(255)
,smtp_server VARCHAR(1024)
,smtp_port VARCHAR(5)
,smtp_address VARCHAR(1024)
,smtp_username VARCHAR(1024)
,smtp_password VARCHAR(1024)
,hostname VARCHAR(1024)
,scheme VARCHAR(5)
,open_invitations BOOLEAN
);
`
var memberUniqueIndex = `
CREATE UNIQUE INDEX member_uix ON members (team_id, user_id);
`
var memberTeamIndex = `
CREATE INDEX member_team_ix ON members (team_id);
`
var memberUserIndex = `
CREATE INDEX member_user_ix ON members (user_id);
`
var commitUniqueIndex = `
CREATE UNIQUE INDEX commits_uix ON commits (repo_id, hash, branch);
`
var commitRepoIndex = `
CREATE INDEX commits_repo_ix ON commits (repo_id);
`
var commitBranchIndex = `
CREATE INDEX commits_repo_ix ON commits (repo_id, branch);
`
var repoTeamIndex = `
CREATE INDEX repo_team_ix ON repos (team_id);
`
var repoUserIndex = `
CREATE INDEX repo_user_ix ON repos (user_id);
`
var buildCommitIndex = `
CREATE INDEX builds_commit_ix ON builds (commit_id);
`
var buildSlugIndex = `
CREATE INDEX builds_commit_slug_ix ON builds (commit_id, slug);
`
// Load will apply the DDL commands to
// the provided database.
func Load(db *sql.DB) error {
// created tables
db.Exec(userTableStmt)
db.Exec(teamTableStmt)
db.Exec(memberTableStmt)
db.Exec(repoTableStmt)
db.Exec(commitTableStmt)
db.Exec(buildTableStmt)
db.Exec(settingsTableStmt)
db.Exec(memberUniqueIndex)
db.Exec(memberTeamIndex)
db.Exec(memberUserIndex)
db.Exec(commitUniqueIndex)
db.Exec(commitRepoIndex)
db.Exec(commitBranchIndex)
db.Exec(repoTeamIndex)
db.Exec(repoUserIndex)
db.Exec(buildCommitIndex)
db.Exec(buildSlugIndex)
// migrations for backward compatibility
db.Exec("ALTER TABLE settings ADD COLUMN open_invitations BOOLEAN")
db.Exec("UPDATE settings SET open_invitations=0 WHERE open_invitations IS NULL")
return nil
}