* my version of playonbsd.com data
       
   URI git clone git://git.codevoid.de/playonbsd-sdk.git
   DIR Log
   DIR Files
   DIR Refs
   DIR README
       ---
       PlayOnBSD.pm (2401B)
       ---
            1 package PlayOnBSD;
            2 use Dancer2;
            3 
            4 use DBI;
            5 use File::Spec;
            6 use File::Slurper qw/ read_text /;
            7 use Template;
            8 
            9 # debug
           10 use Data::Dumper;
           11 # /debug
           12 
           13 set 'database'     => File::Spec->catfile('playonbsd.sqlite');
           14 set 'show_errors' => 1;
           15 set 'startup_info' => 1;
           16 set 'warnings'     => 1;
           17 
           18 our $VERSION = '0.1';
           19 
           20 sub connect_db {
           21     my $dbh = DBI->connect("dbi:SQLite:dbname=".setting('database'))
           22         or die $DBI::errstr;
           23     print "Connection to database established\n";
           24     return $dbh;
           25 }
           26  
           27 sub init_db {
           28     my $db     = connect_db();
           29     my $schema = read_text('./schema.sql');
           30     $db->do($schema)
           31         or die $db->errstr;
           32     print "Database initialized\n";
           33 }
           34 
           35 get '/' => sub {
           36     my $db  = connect_db();
           37     my $game_sql = 'SELECT 
           38                     game.id         AS GameID,
           39                     game.name       AS GameName,
           40                     game.imgsrc     AS GameImageSrc,
           41                     engine.id       AS EngineID,
           42                     engine.name     AS EngineName,
           43                     engine.href     AS EngineLinkSrc,
           44                     game.comment    AS GameComment,
           45                     game.createdat  AS GameCreatedAt,
           46                     game.changedat  AS GameChangedAt
           47                 FROM game 
           48                 INNER JOIN engine ON
           49                     engine.id = game.engine_id
           50                 ORDER BY game.id DESC';
           51 
           52     my $store_sql = 'SELECT 
           53                     storeinfo.id        AS StoreInfoID,
           54                     storeinfo.game_id   AS StoreInfoGameID,
           55                     storeinfo.name      AS StoreInfoName,
           56                     storeinfo.href      AS StoreInfoLinkSrc,
           57                     storeinfo.info      AS StoreInfoInfo,
           58                     storeinfo.createdat AS StoreInfoCreatedAt,
           59                     storeinfo.changedat AS StoreInfoChangedAt
           60                 FROM storeinfo
           61                 ORDER BY storeinfo.id DESC';
           62 
           63     my $game_sth = $db->prepare($game_sql) or die $db->errstr;
           64     $game_sth->execute or die $game_sth->errstr;
           65     my $game_res = $game_sth->fetchall_hashref('GameID');
           66 
           67     my $store_sth = $db->prepare($store_sql) or die $db->errstr;
           68     $store_sth->execute or die $store_sth->errstr;
           69     my $store_res = $store_sth->fetchall_hashref('StoreInfoID');
           70 
           71     print Dumper($game_res);
           72     print Dumper($store_res);
           73 
           74     template 'index.tt', {
           75         game  => $game_res,
           76         store => $store_res
           77     };
           78 };
           79 
           80 init_db;
           81 true;