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
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
|
package main
func (app *App) createTables() error {
var err error
/**
* Copied from schema.sql
* TODO: Load this from the file itself.
*/
queries := []string{`
CREATE TABLE IF NOT EXISTS platform (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) UNIQUE NOT NULL,
url VARCHAR(255) UNIQUE NOT NULL
) CHARSET=utf8;
`,
`
CREATE TABLE IF NOT EXISTS language (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) UNIQUE NOT NULL
) CHARSET=utf8;
`,
`
CREATE TABLE IF NOT EXISTS update_period (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) UNIQUE NOT NULL
) CHARSET=utf8;
`,
`
CREATE TABLE IF NOT EXISTS owner (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
url VARCHAR(255) NOT NULL UNIQUE,
platform INT NOT NULL,
CONSTRAINT fk_owner_platform FOREIGN KEY (platform) REFERENCES platform(id)
) CHARSET=utf8;
`,
`
CREATE TABLE IF NOT EXISTS entry (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
synopsis VARCHAR(255) NOT NULL,
owner INT NOT NULL,
platform INT NOT NULL,
url VARCHAR(255) UNIQUE NOT NULL,
language INT NOT NULL,
stars VARCHAR(255) NOT NULL,
update_period INT NOT NULL,
created_at TIMESTAMP NOT NULL,
CONSTRAINT fk_entry_owner FOREIGN KEY (owner) REFERENCES owner(id),
CONSTRAINT fk_entry_platform FOREIGN KEY (platform) REFERENCES platform(id),
CONSTRAINT fk_entry_language FOREIGN KEY (language) REFERENCES language(id),
CONSTRAINT fk_entry_period FOREIGN KEY (update_period) REFERENCES update_period(id)
) CHARSET=utf8;
`,
`
CREATE OR REPLACE VIEW view_entry AS
SELECT
entry.id,
title,
synopsis,
owner.name,
platform.name AS platform,
platform.url AS platform_url,
CONCAT( platform.url, entry.url) AS url,
language.name AS language,
stars,
created_at
FROM entry
JOIN owner ON
entry.owner = owner.id
JOIN platform on
entry.platform = platform.id
JOIN language
ON entry.language = language.id
ORDER BY created_at DESC;
`,
}
for _, query := range queries {
_, err := app.DB.Exec(query)
if err != nil {
return err
}
}
/**
* Populates database with first entries.
* TODO: Make this customizable.
*/
init_platform_query := `
INSERT IGNORE INTO platform (id, name, url) VALUES (
NULL,
"Github",
"https://github.com/"
);
`
_, err = app.DB.Exec(init_platform_query)
if err != nil {
return err
}
init_language_query := `
INSERT IGNORE INTO language (name) VALUES (
"Go"
),(
"PHP"
), (
"Javascript"
), (
"CSS"
), (
"HTML"
), (
"Java"
), (
"SQL"
), (
"Python"
);
`
_, err = app.DB.Exec(init_language_query)
if err != nil {
return err
}
init_period_query := `
INSERT IGNORE INTO update_period (name) VALUES (
"Daily"
),(
"Weekly"
), (
"Monthly"
);
`
_, err = app.DB.Exec(init_period_query)
if err != nil {
return err
}
return nil
}
func (app *App) SaveEntries(entries []Entry) error {
query := `
INSERT IGNORE INTO entry (
id,
title,
synopsis,
owner,
platform,
url,
language,
stars,
update_period,
created_at
) VALUES (
NULL,
?,
?,
?,
?,
?,
?,
?,
?,
?
);
`
stmt, err := app.DB.Prepare(query)
if err != nil {
Warn(err, "SaveEntries: Preparing query failed")
return err
}
defer stmt.Close()
for _, e := range entries {
err = app.SaveOwner(*e.Owner)
if err != nil {
continue
}
// Populates the owner struct with the correct id
e.Owner, err = app.GetOwnerID(e.Owner)
if err != nil {
continue
}
_, err = stmt.Exec(e.Title, e.Synopsis, e.Owner.ID, e.Platform.ID, e.URL, e.Language.ID, e.Stars, e.UpdatePeriod.ID, app.Now)
if err != nil {
Warn(err, "SaveEntries: Statement execution failed")
return err
}
}
return nil
}
func (app *App) SaveOwner(owner Owner) error {
query := `
INSERT IGNORE owner (
id,
name,
url,
platform
) VALUES (
NULL,
?,
?,
?
);`
stmt, err := app.DB.Prepare(query)
if err != nil {
Warn(err, "SaveOwner: Preparing query failed")
return err
}
defer stmt.Close()
_, err = stmt.Exec(owner.Name, owner.URL, owner.Platform.ID)
if err != nil {
Warn(err, "SaveOwner: Statement execution failed")
return err
}
return nil
}
|
