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     ! 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