tmont/genreviews-revisited
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
master
genreviews-revisited/web/index.html

886 lines
27 KiB
HTML
Raw Permalink Normal View History

initial git import with web ui
2021-09-13 21:39:30 +00:00
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>genreviews</title>
added favicon and meta stuff
2021-09-14 04:54:15 +00:00
<link rel="shortcut icon" href="./favicon.png" type="image/png" />
<meta name="viewport" content="width=device-width,initial-scale=1">
<meta name="description" content="Sega Genesis reviews compilation in a SQLite database"/>
<meta property="og:description" content="Sega Genesis reviews compilation in a SQLite database"/>
<meta property="og:title" content="genreviews"/>
initial git import with web ui
2021-09-13 21:39:30 +00:00
<link rel="stylesheet" href="./genreviews.css" />
<link rel="stylesheet" href="./fontawesome.css" />
<link rel="stylesheet" href="./solid.css" />
</head>
<body>
<div class="wrapper">
<div class="header">
<h1><a href="?">genreviews</a></h1>
<div class="main-menu">
<ul>
<li>Shift+click to stack filters</li>
<li>
<a id="clear-filters-link" href="?">
<i class="fas fa-filter"></i>
Clear filters
</a>
</li>
<li>
<a id="last-query-link" href="#">
<i class="fas fa-database"></i>
Last query
(<span class="query-row-count"></span> rows, <span class="query-time"></span>ms)
</a>
</li>
<li>
<a href="./genreviews.sqlite" title="937,984 bytes" target="_self">
<i class="fas fa-download"></i>
Download SQLite db (916KB)
</a>
</li>
<li>
<a href="#" title="Query console" id="query-console-modal-link">
<i class="fas fa-code"></i>
Console
</a>
</li>
<li><a href="#" title="info" id="info-modal-link"><i class="fas fa-question-circle"></i></a></li>
</ul>
</div>
</div>
<div id="data">
<table id="main">
<thead>
<tr>
<th>#</th>
<th data-sort="game"><a href="?sort=game&amp;dir=asc" data-qs-clear="false">Game <i class="fas fa-sort"></i></a></th>
<th data-sort="system"><a href="?sort=system&amp;dir=asc" data-qs-clear="false">System <i class="fas fa-sort"></i></a></th>
<th data-sort="publisher"><a href="?sort=publisher&amp;dir=asc" data-qs-clear="false">Publisher <i class="fas fa-sort"></i></a></th>
<th data-sort="developer"><a href="?sort=developer&amp;dir=asc" data-qs-clear="false">Developer <i class="fas fa-sort"></i></a></th>
<th data-sort="region"><a href="?sort=region&amp;dir=asc" data-qs-clear="false">Region <i class="fas fa-sort"></i></a></th>
<th data-sort="genre"><a href="?sort=genre&amp;dir=asc" data-qs-clear="false">Genre <i class="fas fa-sort"></i></a></th>
<th data-sort="theme"><a href="?sort=theme&amp;dir=asc" data-qs-clear="false">Theme <i class="fas fa-sort"></i></a></th>
<th data-sort="viewpoint"><a href="?sort=viewpoint&amp;dir=asc" data-qs-clear="false">Viewpoint <i class="fas fa-sort"></i></a></th>
<th data-sort="grade"><a href="?sort=grade&amp;dir=asc" data-qs-clear="false">Grade <i class="fas fa-sort"></i></a></th>
<th data-sort="ascore" title="Average score"><a href="?sort=ascore&amp;dir=desc" data-qs-clear="false">aScore <i class="fas fa-sort"></i></a></th>
<th data-sort="nscore" title="Normalized score (aScore * 1.25^log(numReviews))"><a href="?sort=nscore&amp;dir=desc" data-qs-clear="false">nScore <i class="fas fa-sort"></i></a></th>
<th data-sort="reviews"><a href="?sort=reviews&amp;dir=desc" data-qs-clear="false">Reviews <i class="fas fa-sort"></i></a></th>
</tr>
</thead>
<tbody></tbody>
</table>
<div id="load-more-container"></div>
</div>
</div>
<div class="modal-container" id="query-modal">
<div class="modal">
<pre class="sql"><code class="query-text"></code></pre>
</div>
</div>
<div class="modal-container" id="game-detail-modal">
<div class="modal">
<div class="info-line game-detail-header">
<div class="game-name"></div>
<div><span class="system"></span> [<span class="region"></span>]</div>
</div>
<div class="info-line">
<div class="inline-list">
<div class="release-date"></div>
<div class="release-country"></div>
</div>
<div class="inline-list">
<div class="genre"></div>
<div class="theme"></div>
<div class="viewpoint"></div>
<div><span class="num-players"></span>&ndash;player</div>
</div>
</div>
<div class="info-line">
<div>published by <span class="publisher"></span></div>
<div>developed by <span class="developer"></span></div>
</div>
<p class="game-blurb"></p>
<hr />
<ol class="game-review-list"></ol>
</div>
</div>
<div class="modal-container" id="info-modal">
<div class="modal">
<p>
This is a database of reviews of Genesis games (and a smattering of other systems).
It was originally compiled in late 2007. So you can assume that pretty much every
link to a review is completely broken.
</p>
<p>
Originally this was a very simple PHP/MySQL website that allowed updating and adding
new reviews/games/systems/etc. It was long forgotten until I rediscovered it while
doing some spring cleaning. I wanted to resurrect the original site, but the security
was pretty atrocious and I can't really justify putting something that like that
on the web in 2021. So I converted it to a SQLite database and made a new, read-only
UI. And deleted the MD5-hashed passwords from the database.
</p>
<p>
This is probably also full of bugs since I mashed the whole thing together in a
weekend and tried to just make it a single HTML page.
</p>
<hr />
<p>
There is no longer a server: everything here is client-side.
It uses <a target="_blank" href="https://sql.js.org/">sql.js</a> (via WASM via Emscripten) to query
the SQLite database client-side. I used
<a target="_blank" href="https://gist.github.com/esperlu/943776#file-mysql2sqlite-sh">this script</a>
to convert the old MySQL dump to SQLite, which, incredibly, worked on the first try.
</p>
</div>
</div>
<div class="modal-container" id="query-console-modal">
<div class="modal">
<p>
Run arbitrary SQLite queries (destructive queries do not persist beyond a page refresh).
Press <tt>Ctrl+Enter</tt> or click <em><strong>Run</strong></em> to run the query.
</p>
<hr />
<p>Sample queries (<a href="./genreviews-erd.png" target="_blank">schema diagram</a>):</p>
<ul>
<li><a href="#" class="query-console-sample" data-query-slug="highestRatedGenres">Highest rated game for each genre</a></li>
<li><a href="#" class="query-console-sample" data-query-slug="gamesWithMostReviews">Games with most reviews</a></li>
<li><a href="#" class="query-console-sample" data-query-slug="systemsWithMostReviews">Systems with most reviews</a></li>
<li><a href="#" class="query-console-sample" data-query-slug="mostReviewedDomains">Domains with most reviews</a></li>
added releaseYears canned query
2021-09-14 05:18:11 +00:00
<li><a href="#" class="query-console-sample" data-query-slug="releaseYears">Games per year</a></li>
initial git import with web ui
2021-09-13 21:39:30 +00:00
</ul>
<hr />
<textarea spellcheck="false" class="sql" id="query-console-query" placeholder="select * from games" style=""></textarea>
<div style="text-align: center">
<button id="query-console-run">Run</button>
</div>
added releaseYears canned query
2021-09-14 05:18:11 +00:00
initial git import with web ui
2021-09-13 21:39:30 +00:00
<div id="query-console-result-container" style="display: none">
<hr />
<div class="query-console-elapsed"></div>
<div class="query-console-result"></div>
</div>
</div>
</div>
<script src="./sql-wasm.js"></script>
<script>
(async () => {
const config = {
locateFile: filename => `./${filename}`
};
const fetchDb = fetch('./genreviews.sqlite').then(res => res.arrayBuffer());
const initSql = window.initSqlJs(config);
const [ SQL, buffer ] = await Promise.all([ initSql, fetchDb ]);
const db = window.db = new SQL.Database(new Uint8Array(buffer));
const colMap = {
system: 's.SystemID',
viewpoint: 'v.ViewpointID',
publisher: 'p.PublisherID',
developer: 'd.DeveloperID',
region: 'r.RegionName',
genre: 'ge.GenreID',
theme: 't.ThemeID',
};
const sortColMap = {
game: 'g.GameName',
system: 's.SystemName',
viewpoint: 'v.Viewpoint',
publisher: 'p.PublisherName',
developer: 'd.DeveloperName',
region: 'r.RegionName',
genre: 'ge.GenreName',
theme: 't.ThemeName',
grade: 'gr.GradeName',
ascore: 'scores.AverageScore',
nscore: 'scores.NormalizedScore',
reviews: 'scores.NumReviews',
};
const buildQuery = (filters, sort) => {
const where = [];
const order = [];
const values = [];
sort = sort || {
col: 'nscore',
dir: 'desc',
};
const col = sortColMap[sort.col];
if (col) {
order.push(col + ((sort.dir || '').toLowerCase() === 'desc' ? ' DESC' : ''));
if (sort.col !== 'game') {
if (sort.col !== 'nscore') {
order.push('scores.NormalizedScore DESC');
}
order.push('g.GameName');
}
} else {
order.push('scores.NormalizedScore DESC', 'g.GameName');
}
if (filters && Object.keys(filters).length) {
Object.keys(filters).forEach((key) => {
const col = colMap[key];
if (col) {
where.push(`${col} = ?`);
values.push(filters[key]);
}
});
}
if (where.length) {
where[0] = `WHERE ${where[0]}`;
}
const query = `
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)
${where.join(' AND ')}
ORDER BY ${order.join(', ')}`;
return [ query, values ];
};
const table = document.querySelector('#main');
const emptyMainTable = () => {
const bodyRows = table.querySelectorAll('tbody tr');
for (const row of bodyRows) {
row.parentElement.removeChild(row);
}
}
const execMerge = (query, values, store = true) => {
const start = Date.now();
const res = db.exec(query, values);
const elapsed = Date.now() - start;
const firstRes = res[0];
const cols = firstRes.columns;
if (store) {
const temp = values.concat([]);
document.querySelector('.query-time').textContent = elapsed.toString();
document.querySelector('.query-row-count').textContent = firstRes.values.length;
document.querySelector('.query-text').textContent = query.trim()
.replace(/\n\n/g, '\n')
.replace(/\?/g, () => {
return temp.shift().toString();
});
}
return firstRes.values.map((values) => {
const row = {};
cols.forEach((col, i) => {
row[col] = values[i];
});
return row;
});
};
const pushStateLinkHandler = (e) => {
e.preventDefault();
const url = new URL(window.location.href);
if (!e.shiftKey && e.target.getAttribute('data-qs-clear') !== 'false') {
url.search = '';
}
const params = new URL(e.target.href).searchParams;
for (const [ key, value ] of params.entries()) {
url.searchParams.set(key, value);
}
window.history.pushState({}, '', url.href);
filterFromCurrentUrl(defaultRowLimit);
};
const loadIntoMainTable = (rows, sort, totalRows = null, append = false) => {
if (!append) {
emptyMainTable();
}
const cell = (text, cls, title, linkInfo) => {
if (title && typeof(title) === 'object') {
linkInfo = title;
title = null;
}
const cell = document.createElement('td');
const wrapper = document.createElement('span');
const classes = (cls || '').split(' ').filter(Boolean);
if (classes.length) {
if (classes.indexOf('number') !== -1) {
cell.classList.add('number');
classes.splice(classes.indexOf('number'), 1);
} else if (classes.indexOf('centered') !== -1) {
cell.classList.add('centered');
classes.splice(classes.indexOf('centered'), 1);
}
if (classes.length) {
wrapper.classList.add(...classes);
}
}
if (!linkInfo) {
wrapper.appendChild(document.createTextNode(text));
} else {
const link = document.createElement('a');
if (linkInfo.onclick) {
link.href = '#';
link.addEventListener('click', (e) => {
e.stopImmediatePropagation();
e.preventDefault();
linkInfo.onclick(e);
});
} else {
link.href = '?' + Object.keys(linkInfo)
.map(key => `${key}=${encodeURIComponent(linkInfo[key])}`)
.join('&');
}
link.appendChild(document.createTextNode(text));
wrapper.appendChild(link);
}
if (title) {
cell.setAttribute('title', title);
}
cell.appendChild(wrapper);
return cell;
};
const body = table.querySelector('tbody');
const startRow = body.querySelectorAll('tr').length;
rows.forEach((row, i) => {
const tr = document.createElement('tr');
const append = el => tr.appendChild(el);
append(cell((startRow + i + 1) + '.', 'number'));
append(cell(row.GameName, 'game overflow', row.GameName, {
onclick: (e) => {
// fetch reviews
const query = `SELECT Link, Score FROM reviews WHERE GameID = ? ORDER BY Score DESC, Link`;
const reviews = execMerge(query, [ row.GameID ], false);
const modal = document.querySelector('#game-detail-modal');
const na = txt => txt || 'n/a';
modal.querySelector('.game-name').textContent = row.GameName;
modal.querySelector('.system').textContent = row.SystemName;
modal.querySelector('.region').textContent = na(row.RegionName);
modal.querySelector('.release-date').textContent = na(row.ReleaseDate);
modal.querySelector('.release-country').textContent = na(row.ReleaseCountry);
modal.querySelector('.num-players').textContent = na(row.NumPlayers);
modal.querySelector('.publisher').textContent = na(row.PublisherName);
modal.querySelector('.developer').textContent = na(row.DeveloperName);
modal.querySelector('.genre').textContent = na(row.GenreName);
modal.querySelector('.theme').textContent = na(row.ThemeName);
modal.querySelector('.viewpoint').textContent = na(row.Viewpoint);
modal.querySelector('.game-blurb').textContent = na(row.Blurb);
const reviewList = modal.querySelector('.game-review-list');
reviewList.innerHTML = '';
reviews.forEach((review) => {
const item = document.createElement('li');
item.appendChild(document.createTextNode(review.Score.toString() + ' ' +String.fromCharCode(0x2014) + ' '));
if (/^https?:\/\//.test(review.Link)) {
const link = document.createElement('a');
link.setAttribute('target', '_blank');
link.setAttribute('href', review.Link);
link.appendChild(document.createTextNode(review.Link));
item.appendChild(link);
} else {
item.appendChild(document.createTextNode(review.Link));
}
reviewList.appendChild(item);
});
showModal('game-detail-modal');
}
}));
append(cell(row.SystemAbbr, 'system', row.SystemName, { system: row.SystemID }));
append(cell(row.PublisherName, 'publisher overflow', row.PublisherName, { publisher: row.PublisherID }));
append(cell(row.DeveloperName, 'developer overflow', row.DeveloperName, { developer: row.DeveloperID }));
append(cell(row.RegionName, null, null, { region: row.RegionName } ));
append(cell(row.GenreAbbr, 'genre overflow', row.GenreName, { genre: row.GenreID }));
append(cell(row.ThemeName, 'theme overflow', row.ThemeName, { theme: row.ThemeID }));
append(cell(row.Viewpoint, 'viewpoint overflow', row.Viewpoint, { viewpoint: row.ViewpointID }));
append(cell(row.GradeName, 'centered mono'));
append(cell(row.AverageScore.toFixed(2), 'number'));
append(cell(row.NormalizedScore.toFixed(2), 'number'));
append(cell(row.NumReviews, 'number'));
body.appendChild(tr);
});
if (!append) {
// update header links/icons for sorting
const headers = table.querySelectorAll('thead th[data-sort]');
headers.forEach((th) => {
const col = th.getAttribute('data-sort');
if (!col) {
return;
}
const isActiveCol = col === sort.col
const icon = th.querySelector('.fas');
if (isActiveCol) {
const anchor = th.querySelector('a');
const href = anchor.getAttribute('href');
const p = new URLSearchParams(href);
const defaultDir = p.get('dir');
// flip the dir
p.set('dir', defaultDir === 'asc' ? 'desc' : 'asc');
anchor.setAttribute('href', `?${p.toString()}`);
// set the icon class
const cls = sort.dir === 'asc' ? 'up' : 'down';
icon.className = `fas fa-sort-${cls}`;
} else {
icon.className = `fas fa-sort`;
}
});
}
const loadMoreBtn = document.querySelector('#load-more-btn');
if (loadMoreBtn) {
loadMoreBtn.parentNode.removeChild(loadMoreBtn);
}
if (!append && totalRows && totalRows > rows.length) {
const diff = totalRows - rows.length;
const btn = document.createElement('button');
btn.textContent = `Load ${diff} more game${diff === 1 ? '' : 's'}`;
btn.id = 'load-more-btn';
btn.addEventListener('click', () => {
btn.parentNode.removeChild(btn);
filterFromCurrentUrl(null, rows.length);
});
document.querySelector('#load-more-container').appendChild(btn);
}
};
const filterFromCurrentUrl = (limit, offset) => {
const params = new URL(window.location.href).searchParams;
const filters = {};
const sort = {
col: 'nscore',
dir: 'desc',
};
for (const [key, value] of params.entries()) {
if (key === 'sort') {
sort.col = value;
} else if (key === 'dir') {
sort.dir = value.toLowerCase() === 'desc' ? 'desc' : 'asc';
} else {
filters[key] = value;
}
}
const [query, values] = buildQuery(filters, sort);
let limitedQuery = query;
if (limit) {
limitedQuery += `\nLIMIT ${limit}`;
} else if (offset) {
limitedQuery += `\nLIMIT -1 OFFSET ${offset}`;
}
const gameData = execMerge(limitedQuery, values);
let totalRows = 0;
if (limit) {
const countRes = execMerge(`SELECT COUNT(*) AS count FROM (${query})`, null, false);
totalRows = countRes[0].count;
}
loadIntoMainTable(gameData, sort, totalRows, !limit);
};
const showModal = (name) => {
hideModals();
document.querySelector(`#${name}`).style.display = 'flex';
};
const hideModals = () => {
document.querySelectorAll('.modal-container').forEach((modal) => {
modal.style.display = 'none';
});
};
const defaultRowLimit = 100;
window.addEventListener('popstate', () => {
filterFromCurrentUrl(defaultRowLimit);
});
document.querySelector('#last-query-link').addEventListener('click', (e) => {
e.stopImmediatePropagation();
e.preventDefault();
showModal('query-modal');
});
document.querySelector('#info-modal-link').addEventListener('click', (e) => {
e.stopImmediatePropagation();
e.preventDefault();
showModal('info-modal');
});
document.querySelector('#query-console-modal-link').addEventListener('click', (e) => {
e.stopImmediatePropagation();
e.preventDefault();
showModal('query-console-modal');
});
const consoleRunBtn = document.querySelector('#query-console-run');
const consoleQueryTxt = document.querySelector('#query-console-query');
const runConsoleQuery = () => {
consoleRunBtn.disabled = true;
const resultContainer = document.querySelector('#query-console-result-container');
const resultArea = resultContainer.querySelector('.query-console-result');
if (!resultArea) {
throw new Error('no result area wtf');
}
resultContainer.style.display = 'block';
resultArea.innerHTML = 'Running query...';
const query = consoleQueryTxt.value.trim();
const showError = (err) => {
const p = document.createElement('p');
p.appendChild(document.createTextNode(err.message));
resultArea.innerHTML = '';
resultArea.appendChild(p);
};
setTimeout(() => {
const start = Date.now();
let numRows = -1;
try {
if (!query) {
throw new Error('No query provided');
}
const res = db.exec(query);
const firstRes = res[0];
if (!firstRes) {
throw new Error(`No result was returned?`);
}
resultArea.innerHTML = '';
const cols = firstRes.columns;
const tbl = document.createElement('table');
const thead = document.createElement('thead');
const headRow = document.createElement('tr');
const th = document.createElement('th');
headRow.appendChild(th);
cols.forEach((col) => {
const th = document.createElement('th');
th.appendChild(document.createTextNode(col));
headRow.appendChild(th);
});
thead.appendChild(headRow);
tbl.appendChild(thead);
numRows = firstRes.values.length;
const tbody = document.createElement('tbody');
firstRes.values.forEach((row, i) => {
const tr = document.createElement('tr');
const td = document.createElement('td');
td.className = 'row-number';
td.appendChild(document.createTextNode(i + 1));
tr.appendChild(td);
row.forEach((value) => {
const td = document.createElement('td');
if (typeof (value) === 'number') {
td.className = 'number';
} else if (value === null) {
td.className = 'null';
}
td.appendChild(document.createTextNode(value));
tr.appendChild(td);
});
tbody.appendChild(tr);
});
tbl.appendChild(tbody);
resultArea.appendChild(tbl);
} catch (e) {
showError(e);
} finally {
const elapsed = Date.now() - start;
resultContainer.querySelector('.query-console-elapsed').innerHTML =
(numRows >= 0 ?
`fetched ${numRows} row${numRows === 1 ? '' : 's'} in ` :
'query completed in ') +
`${elapsed}ms`;
consoleRunBtn.disabled = false;
}
}, 1);
};
consoleRunBtn.addEventListener('click', runConsoleQuery);
consoleQueryTxt.addEventListener('keyup', (e) => {
if (e.key === 'Enter' && e.ctrlKey) {
runConsoleQuery();
}
});
document.querySelectorAll('.query-console-sample').forEach((a) => {
const slug = a.getAttribute('data-query-slug');
const sampleQueries = {
highestRatedGenres: `
select
GameName,
GenreName,
AverageScore
from (
with gamesWithScores as (
select
g.GameID,
AVG(rev.Score) as AverageScore
FROM games g
INNER JOIN reviews rev
on g.GameID = rev.GameID
GROUP BY 1
)
select
g.GameID,
g.GameName,
ge.GenreName,
scores.AverageScore,
rank() over (partition by g.GenreID order by scores.AverageScore DESC, g.GameName) as rank
FROM games g
inner join genres ge
on ge.GenreID = g.GenreID
inner join gamesWithScores scores
on scores.GameID = g.GameID
)
where rank = 1
order by AverageScore DESC`,
gamesWithMostReviews: `
select
g.GameName,
AVG(r.Score) as AverageScore,
COUNT(*) as NumReviews
from games g
inner join reviews r on r.GameID = g.GameID
group by 1
order by 3 desc, 2 desc, 1
limit 50`,
systemsWithMostReviews: `
select
s.SystemName,
AVG(r.Score) as AverageScore,
COUNT(distinct g.GameID) as NumGames,
COUNT(*) as NumReviews
from systems s
inner join games g on g.SystemID = s.SystemID
inner join reviews r on r.GameID = g.GameID
group by 1
order by 4 desc, 3 desc, 2 desc, 1`,
mostReviewedDomains: `
select
substring(Link, 8, instr(substring(Link, 8), '/') - 1) as Domain,
count(distinct GameID) as NumGames,
count(*) as NumReviews
from reviews
where Link like 'http://%'
group by 1
union all
select
substring(Link, 9, instr(substring(Link, 9), '/') - 1) as Domain,
count(distinct GameID) as NumGames,
count(*) as NumReviews
from reviews
where Link like 'https://%'
group by 1
order by 3 desc, 2 desc, 1`,
added releaseYears canned query
2021-09-14 05:18:11 +00:00
releaseYears: `
with releases as (
select
GameID,
(ReleaseDate || '-01-01') as ReleaseDate
from games
where ReleaseDate like '____'
union all
select
GameID,
(ReleaseDate || '-01') as ReleaseDate
from games
where ReleaseDate like '____-__'
union all
select
GameID,
ReleaseDate
from games
where ReleaseDate like '____-__-__'
)
select
strftime('%Y', r.ReleaseDate) as Year,
count(distinct r.GameID) as NumGames,
avg(rev.Score) as AverageScore
from releases r
inner join games g
on g.GameID = r.GameID
inner join reviews rev
on rev.GameID = g.GameID
group by 1
order by 1`,
initial git import with web ui
2021-09-13 21:39:30 +00:00
};
const query = sampleQueries[slug];
if (!query) {
return;
}
a.addEventListener('click', (e) => {
e.stopImmediatePropagation();
e.preventDefault();
consoleQueryTxt.value = query.trim();
});
});
document.addEventListener('keyup', (e) => {
if (e.key === 'Escape') {
hideModals();
}
});
document.querySelectorAll('.modal-container').forEach((modalContainer) => {
addEventListener('click', (e) => {
if (e.target === modalContainer) {
hideModals();
}
});
});
document.addEventListener('click', (e) => {
const findNode = (node, predicate) => {
if (!node) {
return null;
}
if (predicate(node)) {
return node;
}
return findNode(node.parentNode, predicate);
};
const anchor = findNode(e.target, node => node.nodeName.toLowerCase() === 'a');
if (!anchor) {
return;
}
if (anchor.getAttribute('target')) {
return;
}
if (anchor.getAttribute('href') === '#') {
e.preventDefault();
return;
}
pushStateLinkHandler(e);
});
filterFromCurrentUrl(defaultRowLimit);
})();
</script>
</body>
</html>
Reference in New Issue Copy Permalink