#!/usr/bin/perl
use strict;
use warnings;
use Test::More;
use SQL::Abstract::Query;
use Try::Tiny;
try {
require DBI;
require DBD::SQLite;
}
catch {
plan skip_all => 'DBI and DBD::SQLite must be installed to run these tests';
};
unlink('test.db') if -e 'test.db';
my $dbh = DBI->connect('dbi:SQLite:dbname=test.db', '', '', {RaiseError=>1, AutoCommit=>1});
my $query = SQL::Abstract::Query->new( $dbh );
$dbh->do(q[
CREATE TABLE users (
user_id INTEGER,
email TEXT,
is_admin INTEGER DEFAULT 0
)
]);
{
my $insert = $query->insert('users', [qw( user_id email )]);
my $sth = $dbh->prepare( $insert->sql() );
foreach my $user_id (1..100) {
$sth->execute(
$insert->values({ user_id => $user_id, email => 'user' . $user_id . '@example.com' }),
);
}
is(
count(),
100,
'100 users inserted',
);
}
{
is( count({is_admin=>1}), 0, 'no admins yet' );
my $update = $query->update('users', ['is_admin'], {user_id=>{'<=', 'max_user_id'}});
my $sth = $dbh->prepare( $update->sql() );
$sth->execute( $update->values({is_admin=>1, max_user_id=>10}) );
is( count({is_admin=>1}), 10, '10 users updated to be admins' );
$sth->execute( $update->values({is_admin=>1, max_user_id=>30}) );
is( count({is_admin=>1}), 30, '20 more (30 total) users updated to be admins' );
}
{
my $delete = $query->delete('users', {email=>'email'});
my $sth = $dbh->prepare( $delete->sql() );
$sth->execute( $delete->values({email => 'user1@example.com'}) );
is( count(), 99, 'deleted one user' );
$sth->execute( $delete->values({email => 'user11@example.com'}) );
$sth->execute( $delete->values({email => 'user31@example.com'}) );
is( count(), 97, 'delete 2 more users' );
is( count({is_admin=>1}), 28, '2 of those deleted were admins' );
is( count({is_admin=>0}), 69, '1 of those deleted were not admins' );
}
done_testing;
sub count {
my ($where) = @_;
$where ||= {};
my ($sql, @bind_values) = $query->select(
[\'COUNT(*)'],
'users',
$where,
);
return ( $dbh->selectrow_array( $sql, undef, @bind_values ) )[0];
}