#!/usr/bin/perl
#  $HeadURL$ $LastChangedRevision$
use strict;
use warnings;
my($app_svnid) = '$HeadURL$ $LastChangedRevision$';  ## no critic (RequireInterpolationOf
use lib substr(`ade-config ade_share_prefix`,0,-1) . '/include';  ## no critic (ProhibitBacktickOperators)
use ADE;
use DBI;

sub ade513_stdlibs_ade_sql
{
    my($errstack_ref) = @_;
    my($count, @sql_value, $dbh, $result, @results, $number, $dbfile);

    print "connecting to in memory database ...\n";
    $dbfile = ':memory:';
    #$dbfile = '/tmp/xxx.sqlite';
    $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile",'','');

    print "testing ade_execute_sql() ...\n";
    ADE::execute_sql($errstack_ref, $dbh, 'CREATE TABLE numbers (number INT);');
    ADE::execute_sql($errstack_ref, $dbh, 'INSERT INTO numbers VALUES (42);');
    ADE::execute_sql($errstack_ref, $dbh, 'INSERT INTO numbers VALUES (24);');

    #  This should outout: 2
    print "testing ade_select_sql_count() ...\n";
    ADE::select_sql_count($errstack_ref, $dbh, \$count, 'SELECT COUNT(*) FROM numbers;');
    print "count is $count\n";

    #  This should output: 24, 42
    print "testing ade_select_sql() ...\n";
    ADE::select_sql($errstack_ref, $dbh, \@results, 'SELECT number FROM numbers ORDER BY number;');
    foreach my $result (@results) {
        ($number) = @{$result};
        print "number is $number\n";
    }

    print "testing ade_execute_sql_qm() ...\n";
    @sql_value = (84);
    ADE::execute_sql_qm($errstack_ref, $dbh, 'INSERT INTO numbers VALUES (?);', \@sql_value);
    @sql_value = (48);
    ADE::execute_sql_qm($errstack_ref, $dbh, 'INSERT INTO numbers VALUES (?);', \@sql_value);

    #  This should output: 1
    print "testing ade_select_sql_count_qm() ...\n";
    @sql_value = (84);
    ADE::select_sql_count_qm($errstack_ref, $dbh, \$count, 'SELECT COUNT(*) FROM numbers where number = ?;', \@sql_value);
    print "count is $count\n";

    #  This should output: 84
    @sql_value = (84);
    print "testing ade_select_sql_qm() ...\n";
    ADE::select_sql_qm($errstack_ref, $dbh, \@results, 'SELECT number FROM numbers WHERE number = ? ORDER BY number;', \@sql_value);
    foreach my $result (@results) {
        ($number) = @{$result};
        print "number is $number\n";
    }

    undef $dbh;
    return $ADE::OK;
}

ADE::main(\&ade513_stdlibs_ade_sql);