-
Notifications
You must be signed in to change notification settings - Fork 0
/
create_database.go
173 lines (144 loc) · 4.6 KB
/
create_database.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
package main
import (
"database/sql"
"log"
)
const DDL_TESTS_PROJECTS = `
CREATE TABLE IF NOT EXISTS test_projects (
project_id integer PRIMARY KEY AUTOINCREMENT,
project_name TEXT UNIQUE NOT NULL,
description TEXT NULL
)`
const DDL_TESTS_PROJECT_BRANCHES = `
CREATE TABLE IF NOT EXISTS project_branches (
branch_id INTEGER PRIMARY KEY AUTOINCREMENT,
parent_project_id INTEGER NOT NULL REFERENCES test_projects(project_id),
branch_name TEXT NOT NULL
)`
// alter table test_launches add column parent_branch_id INTEGER NULL REFERENCES project_branches(branch_id);
// INSERT INTO test_projects (project_name, description) VALUES ('Integration Tests', 'Burberry REST API integration tests')
// SELECT DISTINCT branch FROM test_launches;
// INSERT INTO project_branches (parent_project_id, branch_name) VALUES
// UPDATE test_launches SET parent_branch_id=(SELECT branch_id FROM project_branches WHERE branch_name='FB_BAU') WHERE branch = 'FB_BAU';
//CREATE TABLE IF NOT EXISTS test_launches_1 (
// launch_id integer PRIMARY KEY AUTOINCREMENT,
// parent_branch_id INTEGER REFERENCES project_branches(branch_id),
// label TEXT NULL,
// creation_date DATE NOT NULL,
// test_num INTEGER,
// failed_num INTEGER,
// skipped_num INTEGER,
// passed_num INTEGER
//)
// INSERT INTO test_launches_1 select launch_id, parent_branch_id, label, creation_date, test_num, failed_num, skipped_num, passed_num FROM test_launches;
// DROP TABLE test_launches;
// ALTER TABLE test_launches_1 RENAME TO test_launches;
const DDL_TESTS_LAUNCHES = `
CREATE TABLE IF NOT EXISTS test_launches (
launch_id integer PRIMARY KEY AUTOINCREMENT,
parent_branch_id INTEGER REFERENCES project_branches(branch_id),
label TEXT NULL,
creation_date DATE NOT NULL,
test_num INTEGER,
failed_num INTEGER,
skipped_num INTEGER,
passed_num INTEGER
)`
const DDL_TEST_SUITES = `
CREATE TABLE IF NOT EXISTS test_suites (
test_suite_id integer PRIMARY KEY AUTOINCREMENT,
name TEXT,
parent_launch_id INTEGER,
FOREIGN KEY(parent_launch_id) REFERENCES test_launches(launch_id)
)`
const DDL_TEST_CASES = `
CREATE TABLE IF NOT EXISTS test_cases (
test_case_id integer PRIMARY KEY AUTOINCREMENT,
md5_hash TEXT,
name TEXT,
package TEXT,
class_name TEXT,
status TEXT,
parent_launch_id INTEGER REFERENCES test_launches(launch_id) ON DELETE CASCADE
)`
const DDL_TEST_CASE_FAILURE = `
CREATE TABLE IF NOT EXISTS test_case_failures (
test_case_failure_id integer PRIMARY KEY AUTOINCREMENT,
failure_type TEXT NULL,
failure_message TEXT NULL,
failure_text TEXT NULL,
parent_test_case_id INTEGER REFERENCES test_cases(test_case_id) ON DELETE CASCADE
)`
const DDL_USERS = `
CREATE TABLE IF NOT EXISTS users (
user_id integer PRIMARY KEY AUTOINCREMENT,
login TEXT UNIQUE NOT NULL,
password TEXT,
is_active BOOLEAN DEFAULT 0,
first_name TEXT NULL,
last_name TEXT NULL
)`
const SQL_REMOVED_ORPHAN_TESTS = `
DELETE FROM test_cases WHERE parent_launch_id IN (
SELECT DISTINCT parent_launch_id
FROM test_cases LEFT JOIN test_launches
ON parent_launch_id=launch_id
WHERE launch_id is NULL
)
`
const SQL_REMOVED_ORPHAN_FAILURES = `
DELETE FROM test_case_failures WHERE parent_test_case_id IN (
SELECT DISTINCT parent_test_case_id
FROM test_case_failures LEFT JOIN test_cases
ON parent_test_case_id = test_case_id
WHERE test_case_id is NULL
)
`
const DDL_INDEX_TESTS_LAUNCHES_BRANCH = `
CREATE INDEX ind_test_launches_branch ON test_launches(branch)
`
const DDL_INDEX_TEST_CAST_PARENT_ID = `
CREATE INDEX ind_test_cases_prnt_id ON test_cases (parent_launch_id)
`
func createDbIfNotExists() {
database, operErr := OpenDbConnection()
if operErr != nil {
log.Println("Failed to create the handle")
}
defer database.Close()
if pingErr := database.Ping(); pingErr != nil {
log.Fatal("Failed to keep connection alive")
}
if _, err := database.Exec(DDL_TESTS_PROJECTS); err != nil {
log.Fatal(err)
}
if _, err := database.Exec(DDL_TESTS_PROJECT_BRANCHES); err != nil {
log.Fatal(err)
}
if _, err := database.Exec(DDL_TESTS_LAUNCHES); err != nil {
log.Fatal(err)
}
if _, err := database.Exec(DDL_TEST_CASES); err != nil {
log.Fatal(err)
}
if _, err := database.Exec(DDL_TEST_CASE_FAILURE); err != nil {
log.Fatal(err)
}
if _, err := database.Exec(DDL_USERS); err != nil {
log.Fatal(err)
}
initUsers(database)
}
func initUsers(database *sql.DB) {
row := database.QueryRow("SELECT count(*) FROM users")
var numberOfUsers int
if err := row.Scan(&numberOfUsers); err != nil {
log.Fatal(err)
}
if numberOfUsers > 0 {
return
}
if _, err := database.Exec("INSERT INTO users(login, password, is_active) VALUES('admin', 'admin', 1)"); err != nil {
log.Fatal(err)
}
}