t* my version of playonbsd.com data
URI git clone git://git.codevoid.de/playonbsd-sdk.git
DIR Log
DIR Files
DIR Refs
DIR README
---
DIR commit 61f9af1e89f97344c8b5654698b85d4cb631868f
DIR parent 58a23140ef8801ee93933802af7312c9d106d416
URI Author: Stefan Hagen <sh+git[at]codevoid[dot]de>
Date: Mon, 11 May 2020 13:39:41 +0200
bring store<>game mapping table back
Diffstat:
M lib/PlayOnBSD.pm | 26 ++++++++++++++++----------
M playonbsd.sqlite | 0
M schema.sql | 22 ++++++++++++++++++----
3 files changed, 34 insertions(+), 14 deletions(-)
---
DIR diff --git a/lib/PlayOnBSD.pm b/lib/PlayOnBSD.pm
t@@ -36,19 +36,25 @@ get '/' => sub {
my $db = connect_db();
my $sql = 'SELECT
- game.id AS GameID,
- game.name AS GameName,
- game.imgsrc AS ImgSrc,
- engine.name AS EngineName,
- engine.name AS EngineID,
- storelist.id AS StoreID,
- game.comment AS GameComment,
- game.createdat AS GameCreatedAt,
- game.changedat AS GameChangedAt
+ game.id AS GameID,
+ game.name AS GameName,
+ game.imgsrc AS GameImageSrc,
+ engine.id AS EngineID,
+ engine.name AS EngineName,
+ engine.href AS EngineLinkSrc,
+ store.name AS StoreName,
+ store.href AS StoreLink,
+ game.comment AS GameComment,
+ game.createdat AS GameCreatedAt,
+ game.changedat AS GameChangedAt
FROM
game
INNER JOIN engine ON
engine.id = game.engine_id
+ INNER JOIN gamestore_map ON
+ gamestore_map.game_id = game.id
+ LEFT OUTER JOIN store ON
+ gamestore_map.store_id = store.id
ORDER BY game.id DESC';
my $sth = $db->prepare($sql)
t@@ -57,7 +63,7 @@ get '/' => sub {
$sth->execute
or die $sth->errstr;
- my $res = $sth->fetchall_hashref('game_id');
+ my $res = $sth->fetchall_hashref('GameID');
print Dumper($res);
DIR diff --git a/playonbsd.sqlite b/playonbsd.sqlite
Binary files differ.
DIR diff --git a/schema.sql b/schema.sql
t@@ -1,14 +1,20 @@
+-- game list to be shown on the index
+-- each game can have one engine (game 1:1 engine)
+-- each game can have N storesources (game 1:N store)
+-- but each store entry is specific to one game (store 1:1 game)
+
+-- list of games
create table if not exists game (
id integer primary key autoincrement,
name string not null,
imgsrc string,
engine_id integer REFERENCES engine(id),
- storelist_id integer REFERENCES storelist(game_id),
comment string,
createdat integer not null,
changedat integer not null
);
+-- list of engines
create table if not exists engine (
id integer primary key autoincrement,
name string not null,
t@@ -19,11 +25,19 @@ create table if not exists engine (
changedat integer not null
);
-create table if not exists storelist (
- id integer primary key autoincrement,
+-- mapping table to map game entries with store entries
+create table if not exists gamestore_map (
game_id integer REFERENCES game(id),
+ store_id integer REFERENCES store(id),
+ createdat integer not null,
+ changedat integer not null
+);
+
+-- store entries
+create table if not exists store (
+ id integer primary key autoincrement,
name string not null,
- homepage string,
+ href string,
info string,
createdat integer not null,
changedat integer not null