# 2005 September 17 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests to verify that ticket #1435 has been # fixed. # # # $Id: tkt1435.test,v 1.2 2006/01/17 09:35:02 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !memorydb { finish_test return } # Construct the sample database. # do_test tkt1435-1.0 { sqlite3 db :memory: execsql { CREATE TABLE Instances( instanceId INTEGER PRIMARY KEY, troveName STR, versionId INT, flavorId INT, timeStamps STR, isPresent INT, pinned BOOLEAN ); INSERT INTO "Instances" VALUES(1, 'libhello:runtime', 1, 1, 1126929880.094, 1, 1); INSERT INTO "Instances" VALUES(2, 'libhello:user', 1, 1, 1126929880.094, 1, 0); INSERT INTO "Instances" VALUES(3, 'libhello:script', 1, 1, 1126929880.094, 1, 0); INSERT INTO "Instances" VALUES(4, 'libhello', 1, 1, 1126929880.094, 1, 0); CREATE TABLE Versions(versionId INTEGER PRIMARY KEY,version STR UNIQUE); INSERT INTO "Versions" VALUES(0, NULL); INSERT INTO "Versions" VALUES(1, '/localhost@rpl:linux/0-1-1'); CREATE TABLE Flavors(flavorId integer primary key, flavor str unique); INSERT INTO "Flavors" VALUES(0, NULL); INSERT INTO "Flavors" VALUES(1, '1#x86'); CREATE TEMPORARY TABLE tlList ( row INTEGER PRIMARY KEY, name STRING, version STRING, flavor STRING ); INSERT INTO tlList values(NULL, 'libhello:script', '/localhost@rpl:linux/0-1-1', '1#x86'); INSERT INTO tlList values(NULL, 'libhello:user', '/localhost@rpl:linux/0-1-1', '1#x86'); INSERT INTO tlList values(NULL, 'libhello:runtime', '/localhost@rpl:linux/0-1-1', '1#x86'); } } {} # Run the query with an index # do_test tkt1435-1.1 { execsql { select row, pinned from tlList, Instances, Versions, Flavors where Instances.troveName = tlList.name and Versions.version = tlList.version and Instances.versionId = Versions.versionId and ( Flavors.flavor = tlList.flavor or Flavors.flavor is NULL and tlList.flavor = '') and Instances.flavorId = Flavors.flavorId order by row asc; } } {1 0 2 0 3 1} # Create a indices, analyze and rerun the query. # Verify that the results are the same # do_test tkt1435-1.2 { execsql { CREATE INDEX InstancesNameIdx ON Instances(troveName); CREATE UNIQUE INDEX InstancesIdx ON Instances(troveName, versionId, flavorId); ANALYZE; select row, pinned from tlList, Instances, Versions, Flavors where Instances.troveName = tlList.name and Versions.version = tlList.version and Instances.versionId = Versions.versionId and ( Flavors.flavor = tlList.flavor or Flavors.flavor is NULL and tlList.flavor = '') and Instances.flavorId = Flavors.flavorId order by row asc; } } {1 0 2 0 3 1} finish_test