view release on metacpan or  search on metacpan

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

###############################################################################
# Equality search via index (INT)
###############################################################################
$res = $db->execute("SELECT * FROM emp WHERE id = 5");

# ok 16
ok($res->{type} eq 'rows', "SELECT WHERE id=5 ok");

# ok 17
is(scalar @{$res->{data}}, 1, "WHERE id=5 returns 1 row");

# ok 18
is($res->{data}[0]{name}, 'Eve', "WHERE id=5 -> Eve");

# id not found
$res = $db->execute("SELECT * FROM emp WHERE id = 999");

# ok 19
is(scalar @{$res->{data}}, 0, "WHERE id=999 returns 0 rows");

# Equality on VARCHAR column
$res = $db->execute("SELECT * FROM emp WHERE dept = 'Engineering'");

# ok 20
is(scalar @{$res->{data}}, 5, "WHERE dept=Engineering returns 5 rows");

###############################################################################
# Range searches via index
###############################################################################
# id > 7  -> 8,9,10
$res = $db->execute("SELECT * FROM emp WHERE id > 7");

# ok 21
is(scalar @{$res->{data}}, 3, "WHERE id>7 returns 3 rows");

# id >= 8  -> 8,9,10
$res = $db->execute("SELECT * FROM emp WHERE id >= 8");

# ok 22
is(scalar @{$res->{data}}, 3, "WHERE id>=8 returns 3 rows");

# id < 3  -> 1,2
$res = $db->execute("SELECT * FROM emp WHERE id < 3");

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

# id <= 2  -> 1,2
$res = $db->execute("SELECT * FROM emp WHERE id <= 2");

# 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)
###############################################################################
# Change Bob's salary; old value must be gone from index, new added
$res = $db->execute("UPDATE emp SET salary=60000 WHERE id=2");

# ok 30
ok($res->{type} eq 'ok', "UPDATE salary for id=2");

# Old salary (55000.5) should no longer match
$res = $db->execute("SELECT * FROM emp WHERE salary >= 55000");
my @found_55 = grep { $_->{id}==2 } @{$res->{data}};

# ok 31
ok(scalar @found_55 == 1, "Bob still found via salary index after update");

# ok 32
is($res->{data}[0]{salary}+0 == 55000.5 ? 'old' : 'updated',
   'updated', "Salary index updated: old value gone");

# Directly verify new value reachable
$res = $db->execute("SELECT * FROM emp WHERE salary >= 59999 AND salary <= 60001");
# (no index path for AND range with two bounds, but verify via full scan)
my @bobs = grep { $_->{name} eq 'Bob' } @{$res->{data}};

# ok 33
ok(scalar @bobs == 1, "Bob found via salary=60000 after update");

###############################################################################
# Index maintained on DELETE
###############################################################################
$res = $db->execute("DELETE FROM emp WHERE id=10");

# ok 34
ok($res->{type} eq 'ok', "DELETE Jack (id=10)");

$res = $db->execute("SELECT * FROM emp WHERE id=10");

# ok 35
is(scalar @{$res->{data}}, 0, "id=10 not found after DELETE");

$res = $db->execute("SELECT * FROM emp WHERE id >= 1");

# ok 36
is(scalar @{$res->{data}}, 9, "9 rows remain after DELETE");

###############################################################################
# VACUUM rebuilds index
###############################################################################
# Insert some rows then delete to create holes
$db->execute("INSERT INTO emp (id,name,dept,salary) VALUES (11,'Tmp1','Test',1.0)");
$db->execute("INSERT INTO emp (id,name,dept,salary) VALUES (12,'Tmp2','Test',2.0)");
$db->execute("DELETE FROM emp WHERE id=11");