• Files
• Commits
• Log
• Graph
• Stats

use App::Class; # Import::Into

class App::DB {
    use Data::Printer;
    use DBIx::Simple;
    use File::Slurper 'read_text';

    field $cfg  :reader :param; # mandatory
    field $dbix :reader =
        DBIx::Simple->connect( 'dbi:SQLite:dbname='.$cfg->{dbname} );
	ADJUST { # p $cfg->{db_init}, as => "db_init file:";
	    my $dbi_trace_level = $ENV{DBI_TRACE} || 0;
		$dbix->dbh->trace('SQL|'.$dbi_trace_level);
		# init db if 1st run (creates db also if not exists)
		my $schema = read_text( $cfg->{db_init} );  # p $sql;
		my @schemata = split /\n\s*\n/, $schema;    # p @schema;
		$dbix->dbh->do($_) for @schemata; # is needed, sometimes fails to run directly
		# $dbix->dbh->do($schema);
	}

    # timestamp is GMT, needs to be converted to local timezone:
    my $fields = join ',', qw( id description comment filename date ),
        q!DATETIME(time, 'localtime') as time!;

    method save_document ($data) { # p $data; # return;
		my @cols = qw(id description comment filename date);
		my $flds = join ',', @cols;
		my $sql  = qq!INSERT INTO events($flds) VALUES(?,?,?,?,?) ON CONFLICT(id)
			 DO UPDATE SET description = ?, comment = ?, filename = ?, date = ?!; # p $sql;
		my @bind = ( @{$data}{@cols}, @{$data}{ @cols[1 .. $#cols] }); # omit 'id'
            # p @bind;
        my $result = do { # choice is to capture error, or just die with db error
            try {         #  since user probably cannot do anything about it
                $dbix->query( $sql, @bind ) or die $dbix->error;
				# record id = $data->{id} from record edit, or get last insert:
				my $id = $data->{id} || $dbix->last_insert_id(); # p $id;
                return { id => $id };
            }
            catch ($e) { # dsl->warning $e; # can't do it
                return { error => $e };
            }
        };
        return $result;
    }

    method get_all {
		$dbix->select( events => $fields, {}, { -asc => 'date' } )->hashes;
	}

	method total_count { $dbix->select( events => q!COUNT(*)! )->list }

 	method get_document ($id) {
		my $rec = $dbix->select( 'events', $fields, { id => $id } )->hashes; # p $rec;
		return $rec; # returns AoH for template
	}

    method find_user ($username) { # p $dbh->{Name}; p $username;
        my $user = $dbix->select('users', '*', { username => $username })->hash; # p $user;
        return $user;
    }

	method find_documents ($str) {
        # sqlite3 regexp is case-sensitive, force all fields to lower-case search:
        my @conditions = map { +( qq!LOWER($_)! => { -regexp => lc $str } ) }
            qw(description filename comment); # p @conditions;
		my %where = ( -or => \@conditions ); # p %where;
		my $res = $dbix->select( 'events', $fields, \%where, 
			{ -asc => 'date' } )->hashes; # p $res;
		return $res;
	}
}