1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/tkt1435.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,111 @@
1.4 +# 2005 September 17
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# This file implements tests to verify that ticket #1435 has been
1.17 +# fixed.
1.18 +#
1.19 +#
1.20 +# $Id: tkt1435.test,v 1.2 2006/01/17 09:35:02 danielk1977 Exp $
1.21 +
1.22 +set testdir [file dirname $argv0]
1.23 +source $testdir/tester.tcl
1.24 +
1.25 +ifcapable !memorydb {
1.26 + finish_test
1.27 + return
1.28 +}
1.29 +
1.30 +# Construct the sample database.
1.31 +#
1.32 +do_test tkt1435-1.0 {
1.33 + sqlite3 db :memory:
1.34 + execsql {
1.35 + CREATE TABLE Instances(
1.36 + instanceId INTEGER PRIMARY KEY,
1.37 + troveName STR,
1.38 + versionId INT,
1.39 + flavorId INT,
1.40 + timeStamps STR,
1.41 + isPresent INT,
1.42 + pinned BOOLEAN
1.43 + );
1.44 + INSERT INTO "Instances"
1.45 + VALUES(1, 'libhello:runtime', 1, 1, 1126929880.094, 1, 1);
1.46 + INSERT INTO "Instances"
1.47 + VALUES(2, 'libhello:user', 1, 1, 1126929880.094, 1, 0);
1.48 + INSERT INTO "Instances"
1.49 + VALUES(3, 'libhello:script', 1, 1, 1126929880.094, 1, 0);
1.50 + INSERT INTO "Instances"
1.51 + VALUES(4, 'libhello', 1, 1, 1126929880.094, 1, 0);
1.52 +
1.53 + CREATE TABLE Versions(versionId INTEGER PRIMARY KEY,version STR UNIQUE);
1.54 + INSERT INTO "Versions" VALUES(0, NULL);
1.55 + INSERT INTO "Versions" VALUES(1, '/localhost@rpl:linux/0-1-1');
1.56 +
1.57 + CREATE TABLE Flavors(flavorId integer primary key, flavor str unique);
1.58 + INSERT INTO "Flavors" VALUES(0, NULL);
1.59 + INSERT INTO "Flavors" VALUES(1, '1#x86');
1.60 +
1.61 + CREATE TEMPORARY TABLE tlList (
1.62 + row INTEGER PRIMARY KEY,
1.63 + name STRING,
1.64 + version STRING,
1.65 + flavor STRING
1.66 + );
1.67 +
1.68 + INSERT INTO tlList
1.69 + values(NULL, 'libhello:script', '/localhost@rpl:linux/0-1-1', '1#x86');
1.70 + INSERT INTO tlList
1.71 + values(NULL, 'libhello:user', '/localhost@rpl:linux/0-1-1', '1#x86');
1.72 + INSERT INTO tlList
1.73 + values(NULL, 'libhello:runtime', '/localhost@rpl:linux/0-1-1', '1#x86');
1.74 + }
1.75 +} {}
1.76 +
1.77 +# Run the query with an index
1.78 +#
1.79 +do_test tkt1435-1.1 {
1.80 + execsql {
1.81 + select row, pinned from tlList, Instances, Versions, Flavors
1.82 + where
1.83 + Instances.troveName = tlList.name
1.84 + and Versions.version = tlList.version
1.85 + and Instances.versionId = Versions.versionId
1.86 + and ( Flavors.flavor = tlList.flavor or Flavors.flavor is NULL
1.87 + and tlList.flavor = '')
1.88 + and Instances.flavorId = Flavors.flavorId
1.89 + order by row asc;
1.90 + }
1.91 +} {1 0 2 0 3 1}
1.92 +
1.93 +# Create a indices, analyze and rerun the query.
1.94 +# Verify that the results are the same
1.95 +#
1.96 +do_test tkt1435-1.2 {
1.97 + execsql {
1.98 + CREATE INDEX InstancesNameIdx ON Instances(troveName);
1.99 + CREATE UNIQUE INDEX InstancesIdx
1.100 + ON Instances(troveName, versionId, flavorId);
1.101 + ANALYZE;
1.102 + select row, pinned from tlList, Instances, Versions, Flavors
1.103 + where
1.104 + Instances.troveName = tlList.name
1.105 + and Versions.version = tlList.version
1.106 + and Instances.versionId = Versions.versionId
1.107 + and ( Flavors.flavor = tlList.flavor or Flavors.flavor is NULL
1.108 + and tlList.flavor = '')
1.109 + and Instances.flavorId = Flavors.flavorId
1.110 + order by row asc;
1.111 + }
1.112 +} {1 0 2 0 3 1}
1.113 +
1.114 +finish_test