DB-Handy

 view release on metacpan or  search on metacpan

lib/DB/Handy.pm  view on Meta::CPAN

Check parent-directory write permissions.

=item C<Cannot create database 'E<lt>nameE<gt>': E<lt>reasonE<gt>>

C<create_database> could not create the database subdirectory.
Check disk space and write permissions on C<base_dir>.

=item C<Cannot drop database 'E<lt>nameE<gt>': E<lt>reasonE<gt>>

C<drop_database> could not remove the database directory tree.
Check that no files are locked and that write permission is granted.

=item C<DB::Handy connect failed: E<lt>messageE<gt>>

The low-level C<connect> call failed.  C<$DB::Handy::errstr> contains
the underlying error set by the failing operation.

=item C<DB::Handy: E<lt>messageE<gt>>

A fatal internal error was raised directly via C<die>.
C<RaiseError> must be enabled (the default) for this message to propagate.

t/1002_index.t  view on Meta::CPAN

# ok 24
is(scalar @{$res->{data}}, 2, "WHERE id<=2 returns 2 rows");

# salary >= 80000 -> 80000, 90000, 95000 (3 rows)
$res = $db->execute("SELECT * FROM emp WHERE salary >= 80000");

# ok 25
is(scalar @{$res->{data}}, 3, "WHERE salary>=80000 returns 3 rows");

###############################################################################
# UNIQUE constraint: duplicate INSERT blocked
###############################################################################
$res = $db->execute("INSERT INTO emp (id,name,dept,salary) VALUES (1,'Dup','Test',1.0)");

# ok 26
ok($res->{type} eq 'error', "Duplicate INSERT blocked by UNIQUE");

# ok 27
ok($res->{message} =~ /UNIQUE/, "Error message mentions UNIQUE");

###############################################################################
# UNIQUE constraint: duplicate UPDATE blocked
###############################################################################
$res = $db->execute("UPDATE emp SET id=1 WHERE id=2");

# ok 28
ok($res->{type} eq 'error', "UPDATE to duplicate id=1 blocked");

# Non-duplicate update is allowed
$res = $db->execute("UPDATE emp SET id=2 WHERE id=2");

# ok 29
ok($res->{type} eq 'ok', "UPDATE id=2->2 (no change) allowed");

###############################################################################
# Index maintained on UPDATE (key change)
###############################################################################

t/1010_fixes.t  view on Meta::CPAN

###############################################################################
# Fix 1 -- CHECK constraint enforced on UPDATE
###############################################################################
$db->execute("CREATE TABLE ck (id INT NOT NULL, salary INT CHECK (salary >= 0), score INT CHECK (score <= 100))");
my $r = $db->execute("INSERT INTO ck (id,salary,score) VALUES (1,50000,80)");
# ok 1
ok($r->{type} eq 'ok', "Fix1: INSERT valid row ok");

$r = $db->execute("INSERT INTO ck (id,salary,score) VALUES (2,-1,50)");
# ok 2
ok($r->{type} eq 'error', "Fix1: INSERT negative salary blocked");

# UPDATE violation: salary goes negative
$r = $db->execute("UPDATE ck SET salary=-1 WHERE id=1");
# ok 3
ok($r->{type} eq 'error', "Fix1: UPDATE salary=-1 blocked by CHECK");
# ok 4
ok($r->{message} =~ /CHECK/, "Fix1: UPDATE error mentions CHECK");

# UPDATE violation: score exceeds limit
$r = $db->execute("UPDATE ck SET score=101 WHERE id=1");
# ok 5
ok($r->{type} eq 'error', "Fix1: UPDATE score=101 blocked by CHECK");

# UPDATE to boundary value: allowed
$r = $db->execute("UPDATE ck SET salary=0 WHERE id=1");
# ok 6
ok($r->{type} eq 'ok', "Fix1: UPDATE salary=0 (boundary) ok");
$r = $db->execute("SELECT salary FROM ck WHERE id=1");
# ok 7
is($r->{data}[0]{salary}+0, 0, "Fix1: salary=0 stored correctly");

# UPDATE to boundary score: allowed

t/1010_fixes.t  view on Meta::CPAN

ok($r->{type} eq 'ok', "Fix1: UPDATE score=100 ok");

# UPDATE valid positive salary
$r = $db->execute("UPDATE ck SET salary=99000 WHERE id=1");
# ok 10
ok($r->{type} eq 'ok', "Fix1: UPDATE salary=99000 ok");
$r = $db->execute("SELECT salary FROM ck WHERE id=1");
# ok 11
is($r->{data}[0]{salary}+0, 99000, "Fix1: salary=99000 verified");

# UPDATE only non-CHECK column: not blocked
$db->execute("CREATE TABLE ck2 (id INT NOT NULL, val INT CHECK (val >= 10), note VARCHAR(20))");
$db->execute("INSERT INTO ck2 (id,val,note) VALUES (1,50,'ok')");
$r = $db->execute("UPDATE ck2 SET note='updated' WHERE id=1");
# ok 12
ok($r->{type} eq 'ok', "Fix1: UPDATE non-CHECK column not blocked");
$r = $db->execute("UPDATE ck2 SET val=9 WHERE id=1");
# ok 13
ok($r->{type} eq 'error', "Fix1: UPDATE CHECK column to invalid value blocked");

###############################################################################
# Fix 2 -- Index used for AND range and BETWEEN
###############################################################################
$db->execute("CREATE TABLE rng (id INT, v FLOAT)");
$db->execute("CREATE INDEX idx_rng_id ON rng (id)");
$db->execute("CREATE INDEX idx_rng_v  ON rng (v)");
for my $i (1..50) {
    my $v = $i * 0.5;
    $db->execute("INSERT INTO rng (id,v) VALUES ($i,$v)");