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 c112345fcf267fb462af1bdf949d7991bd14d3ac
DIR parent cadbf9b962b1e557b18525a8f3e119daf14a214b
URI Author: Stefan Hagen <sh+git[at]codevoid[dot]de>
Date: Mon, 11 May 2020 20:26:03 +0200
add store query
Diffstat:
M lib/PlayOnBSD.pm | 41 +++++++++++++++++--------------
M playonbsd.sqlite | 0
M schema.sql | 11 ++---------
M views/index.tt | 28 ++++++++++++++--------------
4 files changed, 39 insertions(+), 41 deletions(-)
---
DIR diff --git a/lib/PlayOnBSD.pm b/lib/PlayOnBSD.pm
t@@ -34,41 +34,46 @@ sub init_db {
get '/' => sub {
my $db = connect_db();
-
- my $sql = 'SELECT
+ my $game_sql = 'SELECT
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
+ 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)
- or die $db->errstr;
-
- $sth->execute
- or die $sth->errstr;
+ my $store_sql = 'SELECT
+ storeinfo.id AS StoreInfoID,
+ storeinfo.game_id AS StoreInfoGameID,
+ storeinfo.name AS StoreInfoName,
+ storeinfo.href AS StoreInfoLinkSrc,
+ storeinfo.info AS StoreInfoInfo,
+ storeinfo.createdat AS StoreInfoCreatedAt,
+ storeinfo.changedat AS StoreInfoChangedAt
+ FROM storeinfo
+ ORDER BY storeinfo.id DESC';
+
+ my $game_sth = $db->prepare($game_sql) or die $db->errstr;
+ $game_sth->execute or die $game_sth->errstr;
+ my $game_res = $game_sth->fetchall_hashref('GameID');
- my $res = $sth->fetchall_hashref('GameID');
+ my $store_sth = $db->prepare($store_sql) or die $db->errstr;
+ $store_sth->execute or die $store_sth->errstr;
+ my $store_res = $store_sth->fetchall_hashref('StoreInfoID');
- print Dumper($res);
+ print Dumper($game_res);
+ print Dumper($store_res);
template 'index.tt', {
- games => $res
+ game => $game_res,
+ store => $store_res
};
};
DIR diff --git a/playonbsd.sqlite b/playonbsd.sqlite
Binary files differ.
DIR diff --git a/schema.sql b/schema.sql
t@@ -25,17 +25,10 @@ create table if not exists engine (
changedat integer not null
);
--- 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 (
+create table if not exists storeinfo (
id integer primary key autoincrement,
+ game_id integer REFERENCES game(id),
name string not null,
href string,
info string,
DIR diff --git a/views/index.tt b/views/index.tt
t@@ -4,7 +4,7 @@
<h1>Welcome to playonbsd.com</h1>
<h2>The OpenBSD Gaming Community</h2>
</div>
-[% IF games.size %]
+[% IF game.size %]
<table>
<tr>
<th>
t@@ -32,40 +32,40 @@
ChangedAt
</th>
</tr>
- [% FOREACH entry IN games.keys.nsort %]
+ [% FOREACH entry IN game.keys.nsort %]
<tr>
<td>
- [% games.$entry.game_id| html %]
+ [% game.$entry.GameID| html %]
</td>
<td>
- [% games.$entry.title| html %]
+ [% game.$entry.GameName| html %]
</td>
<td>
- <img width=64 height=64 src="[% games.$entry.image | html %]">
+ <img width=64 height=64 src="[% game.$entry.GameImageSrc | html %]">
</td>
<td>
- [% games.$entry.engine | html %]
+ <a href="[% game.$entry.EngineLinkSrc | html %]">[% game.$entry.EngineName | html %]</a>
</td>
<td>
- [% FOREACH id IN games.$entry.stores %]
- [% games.$entry.stores.$id.name | html %]
- [% END %]
- [% games.$entry.stores | html %]
+ [% FOREACH id IN store.keys.nsort %]
+
+ <a href="[% store.$id.StoreInfoLinkSrc | html %]">[% store.$id.StoreInfoName | html %]</a><br>
+ [% END %]
</td>
<td>
- [% games.$entry.comment | html %]
+ [% game.$entry.GameComment| html %]
</td>
<td>
- [% games.$entry.createdat | html %]
+ [% game.$entry.GameCreatedAt | html %]
</td>
<td>
- [% games.$entry.changedat | html %]
+ [% game.$entry.GameChangedAt | html %]
</td>
</tr>
[% END %]
</table>
[% ELSE %]
- No games here so far<br>
+ No game here so far<br>
[% END %]
</div>
</div>