Return to tkt1435.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/tkt1435.test, revision 1.1.1.1

1.1       misho       1: # 2005 September 17
                      2: #
                      3: # The author disclaims copyright to this source code.  In place of
                      4: # a legal notice, here is a blessing:
                      5: #
                      6: #    May you do good and not evil.
                      7: #    May you find forgiveness for yourself and forgive others.
                      8: #    May you share freely, never taking more than you give.
                      9: #
                     10: #***********************************************************************
                     11: # This file implements regression tests for SQLite library.
                     12: #
                     13: # This file implements tests to verify that ticket #1435 has been
                     14: # fixed.  
                     15: #
                     16: #
                     17: # $Id: tkt1435.test,v 1.2 2006/01/17 09:35:02 danielk1977 Exp $
                     18: 
                     19: set testdir [file dirname $argv0]
                     20: source $testdir/tester.tcl
                     21: 
                     22: ifcapable !memorydb {
                     23:   finish_test
                     24:   return
                     25: }
                     26: 
                     27: # Construct the sample database.
                     28: #
                     29: do_test tkt1435-1.0 {
                     30:   sqlite3 db :memory:
                     31:   execsql {
                     32:     CREATE TABLE Instances(
                     33:        instanceId INTEGER PRIMARY KEY,
                     34:        troveName STR,
                     35:        versionId INT,
                     36:        flavorId INT,
                     37:        timeStamps STR,
                     38:        isPresent INT,
                     39:        pinned BOOLEAN
                     40:     );
                     41:     INSERT INTO "Instances"
                     42:        VALUES(1, 'libhello:runtime', 1, 1, 1126929880.094, 1, 1);
                     43:     INSERT INTO "Instances"
                     44:        VALUES(2, 'libhello:user', 1, 1, 1126929880.094, 1, 0);
                     45:     INSERT INTO "Instances"
                     46:        VALUES(3, 'libhello:script', 1, 1, 1126929880.094, 1, 0);
                     47:     INSERT INTO "Instances"
                     48:        VALUES(4, 'libhello', 1, 1, 1126929880.094, 1, 0);
                     49:     
                     50:     CREATE TABLE Versions(versionId INTEGER PRIMARY KEY,version STR UNIQUE);
                     51:     INSERT INTO "Versions" VALUES(0, NULL);
                     52:     INSERT INTO "Versions" VALUES(1, '/localhost@rpl:linux/0-1-1');
                     53:     
                     54:     CREATE TABLE Flavors(flavorId integer primary key, flavor str unique);
                     55:     INSERT INTO "Flavors" VALUES(0, NULL);
                     56:     INSERT INTO "Flavors" VALUES(1, '1#x86');
                     57:     
                     58:     CREATE TEMPORARY TABLE tlList (
                     59:        row INTEGER PRIMARY KEY,
                     60:        name STRING,
                     61:        version STRING,
                     62:        flavor STRING
                     63:     );
                     64:     
                     65:     INSERT INTO tlList 
                     66:       values(NULL, 'libhello:script', '/localhost@rpl:linux/0-1-1', '1#x86');
                     67:     INSERT INTO tlList 
                     68:       values(NULL, 'libhello:user', '/localhost@rpl:linux/0-1-1', '1#x86');
                     69:     INSERT INTO tlList 
                     70:       values(NULL, 'libhello:runtime', '/localhost@rpl:linux/0-1-1', '1#x86');
                     71:   }
                     72: } {}
                     73: 
                     74: # Run the query with an index
                     75: #
                     76: do_test tkt1435-1.1 {
                     77:   execsql {
                     78:     select row, pinned from tlList, Instances, Versions, Flavors
                     79:         where
                     80:             Instances.troveName = tlList.name
                     81:         and Versions.version = tlList.version
                     82:         and Instances.versionId = Versions.versionId
                     83:         and (    Flavors.flavor = tlList.flavor or Flavors.flavor is NULL
                     84:              and tlList.flavor = '')
                     85:         and Instances.flavorId = Flavors.flavorId
                     86:     order by row asc;
                     87:   }
                     88: } {1 0 2 0 3 1}
                     89: 
                     90: # Create a indices, analyze and rerun the query. 
                     91: # Verify that the results are the same
                     92: #
                     93: do_test tkt1435-1.2 {
                     94:   execsql {
                     95:     CREATE INDEX InstancesNameIdx ON Instances(troveName);
                     96:     CREATE UNIQUE INDEX InstancesIdx 
                     97:       ON Instances(troveName, versionId, flavorId);
                     98:     ANALYZE;
                     99:     select row, pinned from tlList, Instances, Versions, Flavors
                    100:         where
                    101:             Instances.troveName = tlList.name
                    102:         and Versions.version = tlList.version
                    103:         and Instances.versionId = Versions.versionId
                    104:         and (    Flavors.flavor = tlList.flavor or Flavors.flavor is NULL
                    105:              and tlList.flavor = '')
                    106:         and Instances.flavorId = Flavors.flavorId
                    107:     order by row asc;
                    108:   }
                    109: } {1 0 2 0 3 1}
                    110: 
                    111: finish_test