ifdbgames.sh - brcon2024-hackathons - Bitreichcon 2024 Hackathons
HTML git clone git://bitreich.org/brcon2024-hackathons git://enlrupgkhuxnvlhsf6lc3fziv5h2hhfrinws65d7roiv6bfj7d652fid.onion/brcon2024-hackathons
DIR Log
DIR Files
DIR Refs
DIR Tags
DIR Submodules
---
ifdbgames.sh (9193B)
---
1 #!/bin/sh
2 getrawdata() {
3 printf "getrawdata: start\n" 1>&2
4 sqlite3 "$1" <<-EOF
5 .separator "\x1f" "\x1e"
6
7 with ratings as (
8 select gameid, avg(rating) rawavg, round(avg(rating) * 2) / 2 stars, count(*) cnt from reviews where special isnull group by gameid
9 )
10 select
11 games.id,
12 games.title,
13 games.sort_title,
14 games.author,
15 games.sort_author,
16 games.tags,
17 iif(time(games.published) = '00:00:00',strftime('%Y', games.published), date(games.published)),
18 games.license,
19 games.desc,
20 games.genre,
21 games.forgiveness,
22 games.language,
23 games.website,
24 games.downloadnotes,
25
26 gamelinks.title,
27 gamelinks.desc,
28 gamelinks.url,
29
30 filetypes.externid,
31 filetypes.fmtname,
32 filetypes.desc,
33
34 extreviews.url,
35 extreviews.sourcename,
36 extreviews.sourceurl,
37 reviews.summary,
38 reviews.review,
39 reviews.rating,
40
41 ratings.stars,
42 ratings.cnt
43 from games
44 left outer join gamelinks on gamelinks.gameid = games.id
45 left outer join filetypes on filetypes.id = gamelinks.fmtid
46 left outer join extreviews on extreviews.gameid = games.id
47 left outer join reviews on reviews.id = extreviews.reviewid
48 left outer join ratings on ratings.gameid = games.id
49 order by games.id, gamelinks.displayorder, extreviews.displayorder
50 EOF
51 printf "getrawdata: end\n" 1>&2
52 }
53
54 # Sadly OpenBSD's awk has some problems with some multibyte characters in the
55 # dataset Maybe the encoding in the database is wonky. I don't know. Let's
56 # ignore it! And just use gawk. I want to see something in the end. I doesn't
57 # have to be correct. :)
58 getrawdata "$1" | \
59 gawk -v"FS=\x1f" -v"RS=\x1e" '
60 function removehtmltags(s) {
61 gsub("<[^>]+>", "", s)
62 return s
63 }
64 # TODO Do it, future_pazz0!
65 function resolvehtmlentities(s) {
66 return s
67 }
68 function trim(s) {
69 gsub("^[ ]+", "", s)
70 gsub("[ ]+$", "", s)
71 return s
72 }
73 function foldinto(input, w, indentstr, file,
74 p, t, l, linel, line, n) {
75 l = length(input)
76 p = 1
77 line = indentstr
78 n = 0
79 while (p <= l) {
80 for (t = p; t <= l && !index(" \t\n", substr(input, t, 1)); t++);
81
82 # Doesnt fit in the current line
83 if (length(line) + t - p + 1 > w) {
84 print line >> file
85 line = indentstr
86 n++
87 }
88
89 # Doesnt fit anyways
90 if (t - p > w) {
91 print line >> file
92 } else {
93 if (line && line != indentstr)
94 line = line " "
95 line = line substr(input, p, t - p)
96 }
97
98 for (; t <= l && index(" \t\n", substr(input, t, 1)); t++) {
99 if (substr(input, t, 1) == "\n" && substr(input, t - 1, 1) == "\n") {
100 print line >> file
101 line = indentstr
102 print indentstr >> file
103 n++
104 }
105 }
106 p = t
107 }
108 if (line)
109 print line >> file
110 }
111 function flushdescription2(file,
112 i) {
113 cmd = "groff -Tutf8 -ms > " file
114
115 print ".nr LL 80n" | cmd
116 print ".nr LT 80n" | cmd
117
118 print ".SH\n" data[2] | cmd
119 print ".IP Author(s)\n" data[4] | cmd
120 if (data[10])
121 print ".IP Genre\n" data[10] | cmd
122 if (data[13])
123 print ".IP Website\n" data[13] | cmd
124 if (data[12])
125 print ".IP Language\n" data[12] | cmd
126 if (data[7])
127 print ".IP \"First Publication Date\"\n" data[7] | cmd
128 if (data[8])
129 print ".IP License\n" data[8] | cmd
130 if (data[11])
131 print ".IP \"Forgiveness Rating\"\n" data[11] | cmd
132 if (ratings["stars"])
133 printf(".IP Rating\n%s (based on %s rating%s)\n", ratings["stars"], ratings["count"], ratings["count"] > 1 ? "s" : "") | cmd
134
135 if (data[9]) {
136 print ".SH\nAbout the Story" | cmd
137 gsub("\n\n", "\n.LP\n", data[9])
138 print ".LP\n" data[9] | cmd
139 }
140
141 if (reviews["length"]) {
142 print ".SH\nEditorial Reviews" | cmd
143 for (i = 1; i <= reviews["length"]; i++) {
144 print ".SH\n" reviews[i, "sourcename"] | cmd
145 print ".IP URL\n" reviews[i, "url"] | cmd
146 if (reviews[i, "summary"])
147 print ".SH\n" reviews[i, "summary"] | cmd
148 if (reviews[i, "review"])
149 print ".IP\n" reviews[i, "review"] | cmd
150 }
151 }
152 if (links["length"]) {
153 print ".SH\nExternal Links" | cmd
154 if (data[14])
155 print ".LP\n" data[14] | cmd
156 for (i = 1; i <= links["length"]; i++) {
157 print ".SH\n" links[i, "title"] | cmd
158 print ".IP URL\n" links[i, "url"] | cmd
159 if (links[i, "desc"])
160 print ".IP\n" links[i, "desc"] | cmd
161 if (links[i, "fmtdesc"]) {
162 print ".IP\n" links[i, "fmtdesc"] | cmd
163 print ".IP\n" links[i, "fmtdesclink"] | cmd
164 }
165 }
166 }
167
168 close(cmd)
169 }
170 function keyvalue(key, value, file, w) {
171 if (length(key ": " value) <= w) {
172 printf("%s: %s\n", key, value) >> file
173 return
174 }
175 printf("%s:\n", key) >> file
176 foldinto(sprintf("%s\n", value), w, " ", file)
177 }
178 function flushdescription_(file,
179 i, refs, temp) {
180 # file = data[1] ".txt"
181 # file = "/dev/stdout"
182
183 printf("") > file
184 foldinto(sprintf("%s\n", data[2]), 80, "", file)
185 printf("--------------------------------------------------------------------------------\n") >> file
186
187 keyvalue("Author(s)", data[4], file, 80)
188 # foldinto(sprintf("Author(s): %s\n", data[4]), 80, " ", file)
189 if (data[10])
190 printf("Genre: %s\n", data[10]) >> file
191 if (data[13])
192 printf("Website: %s\n", data[13]) >> file
193 if (data[12])
194 printf("Language: %s\n", data[12]) >> file
195 if (data[7])
196 printf("First Publication Date: %s\n", data[7]) >> file
197 if (data[8])
198 printf("License: %s\n", data[8]) >> file
199 if (data[11])
200 printf("Forgiveness Rating: %s\n", data[11]) >> file
201 if (ratings["stars"])
202 printf("Rating: %s (based on %s rating%s)\n", ratings["stars"], ratings["count"], ratings["count"] > 1 ? "s" : "") >> file
203
204 if (data[9]) {
205 printf("\n\nABOUT THE STORY\n\n") >> file
206 foldinto(data[9], 80, " ", file)
207 }
208
209 if (links["length"]) {
210 printf("\n\nEXTERNAL LINKS\n") >> file
211 for (i = 1; i <= links["length"]; i++) {
212 printf("\n %s <%s>\n", links[i, "title"], links[i, "url"]) >> file
213 if (links[i, "desc"])
214 foldinto(links[i, "desc"], 80, " ", file)
215 if (links[i, "fmtdesc"]) {
216 temp = links[i, "fmtdesc"]
217 if (links[i, "fmtdesclink"]) {
218 if (!refs["set", links[i, "fmtdesclink"]]) {
219 refs["length"]++
220 refs[refs["length"]] = links[i, "fmtdesclink"]
221 refs["set", links[i, "fmtdesclink"]] = refs["length"]
222 }
223 temp = temp sprintf("[%s]", refs["set", links[i, "fmtdesclink"]])
224 }
225 foldinto(temp, 80, " ", file)
226 }
227 }
228 }
229
230 if (reviews["length"]) {
231 printf("\n\nEDITORIAL REVIEWS\n") >> file
232 for (i = 1; i <= reviews["length"]; i++) {
233 printf("\n %s\n", reviews[i, "sourcename"]) >> file
234 printf(" ") >> file
235 if (reviews[i, "summary"])
236 printf("%s ", reviews[i, "summary"]) >> file
237 printf("<%s>\n", reviews[i, "url"]) >> file
238 if (reviews[i, "review"])
239 foldinto(reviews[i, "review"], 80, " > ", file)
240 }
241 }
242
243 if (refs["length"]) {
244 printf("\n\nREFERENCES\n\n") >> file
245 for (i = 1; i <= refs["length"]; i++)
246 printf(" [%s] <%s>\n", i, refs[i]) >> file
247 }
248
249 close(file)
250 }
251 function gphescape(s, f) {
252 gsub(" ", " ", s)
253 gsub("\n", " ", s)
254 if (f)
255 gsub("^\\[", "[|&", s)
256 else
257 gsub("\\|", "\\|", s)
258 return s
259 }
260 function gphitem(type, text, selector, server, port, file) {
261 printf("[%c|%s|%s|%s|%s]\n", type, gphescape(text), gphescape(selector), server, port) >> file
262 }
263 function flushmenu(file) {
264 printf("") > file
265 gphitem("0", sprintf("About \"%s\"", data[2]), "./" data[1] ".txt", "server", "port", file)
266 foldinto(sprintf("%s\n", data[2]), 80, "", file)
267
268 close(file)
269 }
270 function getlink(s,
271 i) {
272 if (!match(s, /href="[^"]*"/))
273 return ""
274 return substr(s, RSTART + length("href=\""), RLENGTH - length("href=\"") - 1)
275 }
276 function flush(tuid) {
277 print tuid > "/dev/stderr"
278 flushdescription_(tuid ".txt")
279 # flushmenu(tuid ".gph")
280 printf("%s\t%s\n", tuid ".txt", data[2]) >> "index.txt"
281 }
282 BEGIN {
283 printf("") > "index.txt"
284 }
285 tuid && $1 != tuid {
286 flush(tuid)
287
288 delete links
289 delete reviews
290 delete ratings
291 delete data
292 }
293 $1 != tuid {
294 for (i = 1; i <= 14; i++) {
295 $i = removehtmltags($i)
296 $i = resolvehtmlentities($i)
297 data[i] = $i
298 }
299 ratings["stars"] = $27
300 ratings["count"] = $28
301 tuid = $1
302 }
303 $17 && !links["url", $17] {
304 links["length"]++
305 links[links["length"], "title"] = removehtmltags($15)
306 links[links["length"], "desc"] = removehtmltags($16)
307 links[links["length"], "url"] = removehtmltags($17)
308
309 links[links["length"], "externid"] = removehtmltags($18)
310 links[links["length"], "fmtname"] = removehtmltags($19)
311 links[links["length"], "fmtdesc"] = removehtmltags($20)
312 links[links["length"], "fmtdesclink"] = getlink($20)
313
314 links["url", $17]++
315 }
316 $21 && !reviews["url", $21] {
317 reviews["length"]++
318 reviews[reviews["length"], "url"] = removehtmltags($21)
319 reviews[reviews["length"], "sourcename"] = removehtmltags($22)
320 reviews[reviews["length"], "sourceurl"] = removehtmltags($23)
321 reviews[reviews["length"], "summary"] = removehtmltags($24)
322 reviews[reviews["length"], "review"] = removehtmltags($25)
323 reviews[reviews["length"], "rating"] = removehtmltags($26)
324
325 reviews["url", $21]++
326 }
327 END {
328 flush(tuid)
329 }
330 '
331
332 sort -t ' ' -k2 index.txt | \
333 gawk -v"FS=\t" '
334 function gphescape(s, f) {
335 gsub(" ", " ", s)
336 gsub("\n", " ", s)
337 if (f)
338 gsub("^\\[", "[|&", s)
339 else
340 gsub("\\|", "\\|", s)
341 return s
342 }
343 function gphitem(type, text, selector, server, port, file) {
344 printf("[%c|%s|%s|%s|%s]\n", type, gphescape(text), gphescape(selector), server, port) >> file
345 }
346 {
347 gphitem("0", $2, "./" $1, "servere", "port", "index.gph")
348 }
349 '