# genreviews-revisited

## Set up

1. Download docker/docker-compose in your favorite way
1. `docker-compose up`
1. Connect to db
    - user: `root`
    - password: `password`
    - host: `localhost` (or `127.0.0.1`)
    - port: `19306`
    - database: `genreviews`

This will run all sql/gz files in `./initdb.d/`, which will initialize the
`genreviews` database.

A sqlite database has been generated as well. Access it with `sqlite3 ./web/genreviews.sqlite`.
This database has foreign keys.

## Web UI
Run `npm install` and then `npm start` to start the web UI. It uses
[sql.js](https://sql.js.org/) to query the SQLite database client side.
Visit http://localhost:11234/ to view the UI.

![genreviews web ui](./genreviews-web.png)

### Sample query
```mysql
WITH scores AS (
	SELECT
		g.GameID,
		AVG(rev.Score) AS AverageScore,
		COUNT(rev.ReviewID) AS NumReviews,
		(POWER(1.25, LOG10(COUNT(rev.ReviewID))) * AVG(rev.Score)) AS NormalizedScore
	FROM games g
	INNER JOIN reviews rev
		ON g.GameID = rev.GameID
	GROUP BY 1
)
SELECT
	scores.NormalizedScore,
	scores.AverageScore,
	scores.NumReviews,
	g.GameID,
	g.GameName,
	g.ReleaseDate,
	g.ReleaseCountry,
	g.NumPlayers,
	ge.GenreID,
	ge.GenreName,
	ge.GenreAbbr,
	s.SystemID,
	s.SystemName,
	s.SystemAbbr,
	t.ThemeID,
	t.ThemeName,
	r.RegionName,
	d.DeveloperID,
	d.DeveloperName,
	p.PublisherID,
	p.PublisherName,
	v.ViewpointID,
	v.Viewpoint,
	gr.GradeName,
	g.Blurb
FROM scores
INNER JOIN games g
	ON g.GameID = scores.GameID
LEFT OUTER JOIN genres ge
	ON ge.GenreID = g.GenreID
LEFT OUTER JOIN systems s
	ON s.SystemID = g.SystemID
LEFT OUTER JOIN themes t
	ON g.ThemeID = t.ThemeID
LEFT OUTER JOIN regions r
	ON g.RegionID = r.RegionID
LEFT OUTER JOIN developers d
	ON g.DeveloperID = d.DeveloperID
LEFT OUTER JOIN publishers p
	ON g.PublisherID = p.PublisherID
LEFT OUTER JOIN viewpoints v
	ON g.ViewpointID = v.ViewpointID
LEFT OUTER JOIN grades gr
	ON gr.LowerBound <= ROUND(scores.AverageScore)
	AND gr.UpperBound >= ROUND(scores.AverageScore)
ORDER BY scores.NormalizedScore DESC, g.GameName
LIMIT 100
```