# Example application: order/inventory database.
# This is intended as an example, not as anything useful.

use SQL::Interpolate FILTER => 1, qw(:all);
use DBIx::Interpolate;
use Data::Dumper;

my $dsn  = "dbi:SQLite:dbname=test.sqlt";
my $user = "";
my $pass = "";

unlink('test.sqlt');

my $dbh = &connect();
&create_db($dbh);
&fill_inventory($dbh);
&modify_inventory($dbh);
&display_inventory1($dbh, 0);
&display_inventory1($dbh, 1);


sub connect
{
    my $dbx = DBIx::Interpolate->connect(
        $dsn, $user, $pass,
        {RaiseError => 1, AutoCommit => 1}
    );
}

sub create_db
{
    my($dbx) = @_;
    $dbx->do( sql[
        CREATE TABLE customer(
            id      INTEGER PRIMARY KEY,
            name    STRING,
            company STRING  INTEGER,
            email   STRING
        )
    ] );

    $dbx->do( sql[
        CREATE TABLE inventory(
            partnum INTEGER PRIMARY KEY,
            desc    STRING,
            price   DOUBLE,
            stock   INTEGER
        )
    ] );
}

sub fill_inventory
{
    my($dbx) = @_;

    my $item = {
        desc  => 'thing',
        price => 1.00,
        stock => 10
    };
    for(my $idx = 0; $idx < 10; $idx++) {
        $$item{price}++;
        $$item{desc} = 'thing-' . chr(ord('a') + $idx);
        
        $dbx->do(sql[
            INSERT INTO inventory $item
        ]);
    }
}

sub modify_inventory
{
    my($dbx) = @_;

    my @others = ('thing-e', 'thing-g');
    $dbx->do( sql[
        UPDATE inventory
        SET {stock => 2*4}
        WHERE desc IN ['thing-a', @others]
    ]);
}

sub display_inventory1
{
    my($dbx, $show_all) = @_;
    my $price = 3.00;
    my $stocks = [0, 8];
    # Example: combining two sql fragments.
    my $items = $dbx->selectall_arrayref(
        sql[
            SELECT partnum, desc, price, stock
            FROM inventory
        ],
        $show_all
            ? ()
            : sql[WHERE price > $price AND stock IN $stocks]
    );

    print Dumper($items);
}