SQLite3 interface

00-database.t

#!/usr/bin/env perl6
use v6.c;
use lib '../lib';
use SQLite3;
use Test;

plan 10;

ok (if 'test.db'.IO.e {'test.db'.IO.unlink}
    else {True};), 'delete test.db if it exists';

ok (if 'test.csv'.IO.e {'test.csv'.IO.unlink}
    else {True};), 'delete test.db if it exists';

ok (my $testdb = DB.new: filepath => 'test.db'), 'create DB object';

ok (unless 'test.db'.IO.e {
        $testdb.cmd: 'CREATE TABLE table1 (col1 text primary key, col2 text);';
        $testdb.cmd: "INSERT INTO table1(col1) values('perl6');";
        $testdb.cmd: "INSERT INTO table1(col1) values('camelia');";
    }), 'create new database';

ok (if 'test.db'.IO.e {
        $testdb.cmd: "UPDATE table1 SET col2='is fun' WHERE col1='perl6';";
        $testdb.cmd: "UPDATE table1 SET col2='is cool' WHERE col1='camelia';";
    }), 'populate data base';

ok (my $res = $testdb.cmd-rec: "SELECT col2 FROM table1 WHERE col1='perl6';"
        if 'test.db'.IO.e;), 'get a value from the database';

ok ($res ~~ 'is fun'), 'value is correct';

ok (if 'test.db'.IO.e {
        $testdb.cmd: ".mode csv", ".output 'test.csv'", "select * from table1";
    }), 'write a csv of the database';

ok (if 'test.db'.IO.e {'test.db'.IO.unlink}
    else {True};), 'delete test.db if it exists';

ok (if 'test.csv'.IO.e {'test.csv'.IO.unlink}
    else {True};), 'delete test.db if it exists';

SQLite3.pm6

use v6.c;
unit module SQLite3:ver<0.0.1>:auth<github:ryn1x>;

class DB is export {
    has $.filepath;

    method cmd(*@command) {
        run 'sqlite3', $!filepath, |@command;
    }

    method !proc(*@cmd) {
        my $proc = Proc::Async.new(|@cmd, :w);
        my $out = Channel.new;
        $proc.Supply.tap( -> $str { $out.send($str) } );
        my $promise = $proc.start;
        return {proc => $proc, out => $out, prom => $promise};
    }

    method cmd-rec(Str $command) {
        my $p = self!proc: 'sqlite3', $!filepath, $command;
        if $p{'prom'}.result {
            $p{'out'}.close;
            return $p{'out'}.list.join.trim;
        }
        else {
            die 'external call to sqlite the did not return successfully';
        }
    }
}