gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/tkt1435.test (about)

     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