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 84424a4d0971bf785c6b1a20d1c04162bae8787f
DIR parent 8f09aeed439bb67e86cd302972faea2423c4b5da
URI Author: Stefan Hagen <sh+git[at]codevoid[dot]de>
Date: Sun, 10 May 2020 22:52:49 +0200
db layout change
Diffstat:
M lib/PlayOnBSD.pm | 33 +++++++++++++++++++++++++++++--
M playonbsd.sqlite | 0
M schema.sql | 38 ++++++++++++-------------------
M views/index.tt | 11 ++++++++++-
4 files changed, 56 insertions(+), 26 deletions(-)
---
DIR diff --git a/lib/PlayOnBSD.pm b/lib/PlayOnBSD.pm
t@@ -35,7 +35,32 @@ sub init_db {
get '/' => sub {
my $db = connect_db();
- my $sql = 'SELECT game_id, title, image, engine, comment, createdat, changedat FROM games ORDER BY game_id DESC';
+# SELECT
+# trackid,
+# tracks.name AS track,
+# albums.title AS album,
+# artists.name AS artist
+# FROM
+# tracks
+# INNER JOIN albums ON albums.albumid = tracks.albumid
+# INNER JOIN artists ON artists.artistid = albums.artistid;
+
+
+ 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
+ FROM
+ game
+ INNER JOIN engine ON
+ engine.id = game.engine_id
+ ORDER BY game.id DESC';
my $sth = $db->prepare($sql)
or die $db->errstr;
t@@ -43,8 +68,12 @@ get '/' => sub {
$sth->execute
or die $sth->errstr;
+ my $res = $sth->fetchall_hashref('game_id');
+
+ print Dumper($res);
+
template 'index.tt', {
- games => $sth->fetchall_hashref('game_id')
+ games => $res
};
};
DIR diff --git a/playonbsd.sqlite b/playonbsd.sqlite
Binary files differ.
DIR diff --git a/schema.sql b/schema.sql
t@@ -1,38 +1,30 @@
-create table if not exists games (
- game_id integer primary key autoincrement,
- title string not null,
- image string,
- engine integer,
- store_list integer,
+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,
- script string,
createdat integer not null,
changedat integer not null
);
-create table if not exists engines (
- engine_id integer primary key autoincrement,
+create table if not exists engine (
+ id integer primary key autoincrement,
name string not null,
- homepage string,
- portname string,
- description string,
- script string
+ href string,
+ port string,
+ descr string,
createdat integer not null,
changedat integer not null
);
-create table if not exists stores (
- store_id integer primary key autoincrement,
+create table if not exists storelist (
+ id integer primary key autoincrement,
+ game_id integer REFERENCES game(id),
name string not null,
homepage string,
- description string
+ info string,
createdat integer not null,
changedat integer not null
);
-
-create table if not exists store_map (
- game_id integer,
- store_id integer,
- priceinfo string,
- comment string
-);
DIR diff --git a/views/index.tt b/views/index.tt
t@@ -20,6 +20,9 @@
Engine
</th>
<th>
+ Storelist
+ </th>
+ <th>
Comment
</th>
<th>
t@@ -38,12 +41,18 @@
[% games.$entry.title| html %]
</td>
<td>
- [% games.$entry.image | html %]
+ <img width=64 height=64 src="[% games.$entry.image | html %]">
</td>
<td>
[% games.$entry.engine | html %]
</td>
<td>
+ [% FOREACH id IN games.$entry.stores %]
+ [% games.$entry.stores.$id.name | html %]
+ [% END %]
+ [% games.$entry.stores | html %]
+ </td>
+ <td>
[% games.$entry.comment | html %]
</td>
<td>