GopherProxy

	ifdbgames.sh - brcon2024-hackathons - Bitreichcon 2024 Hackathons
	git clone git://bitreich.org/brcon2024-hackathons git://enlrupgkhuxnvlhsf6lc3fz…
	Log
	Files
	Refs
	Tags
	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(rat…
	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(…
	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.fm…
	46 left outer join extreviews on extreviews.gameid = games.…
	47 left outer join reviews on reviews.id = extreviews.revie…
	48 left outer join ratings on ratings.gameid = games.id
	49 order by games.id, gamelinks.displayorder, extreviews.di…
	50 EOF
	51 printf "getrawdata: end\n" 1>&2
	52 }
	53
	54 # Sadly OpenBSD's awk has some problems with some multibyte characters i…
	55 # dataset Maybe the encoding in the database is wonky. I don't know. Let…
	56 # ignore it! And just use gawk. I want to see something in the end. I do…
	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…
	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,…
	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", rating…
	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"] \| …
	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("--------------------------------------------------------…
	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["s…
	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…
	213 if (links[i, "desc"])
	214 foldinto(links[i, "desc"], 80, " ", f…
	215 if (links[i, "fmtdesc"]) {
	216 temp = links[i, "fmtdesc"]
	217 if (links[i, "fmtdesclink"]) {
	218 if (!refs["set", links[i, "fmtde…
	219 refs["length"]++
	220 refs[refs["length"]] = l…
	221 refs["set", links[i, "fm…
	222 }
	223 temp = temp sprintf("[%s]", refs…
	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"]) >> …
	234 printf(" ") >> file
	235 if (reviews[i, "summary"])
	236 printf("%s ", reviews[i, "summary"]) >> …
	237 printf("<%s>\n", reviews[i, "url"]) >> file
	238 if (reviews[i, "review"])
	239 foldinto(reviews[i, "review"], 80, " > …
	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(se…
	262 }
	263 function flushmenu(file) {
	264 printf("") > file
	265 gphitem("0", sprintf("About \"%s\"", data[2]), "./" data[1] ".tx…
	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("h…
	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(se…
	345 }
	346 {
	347 gphitem("0", $2, "./" $1, "servere", "port", "index.gph")
	348 }
	349 '