SQLite

Check-in [2e5786d101]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment::-) (CVS 83)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2e5786d10148872db47d99e39c3f54597ad777c8
User & Date: drh 2000-06-08 16:54:40.000
Context
2000-06-08
19:38
:-) (CVS 84) (check-in: 57dce04add user: drh tags: trunk)
16:54
:-) (CVS 83) (check-in: 2e5786d101 user: drh tags: trunk)
16:26
:-) (CVS 82) (check-in: 33355b2d8d user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/index.test.
︙︙
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.3 2000/06/03 18:06:53 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {








|








19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.4 2000/06/08 16:54:40 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
︙︙
252
253
254
255
256
257
258
259































260

  execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
  execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
} {tab1}
do_test index-9.2 {
  execsql {CREATE INDEX idx1 ON tab1(a)}
  execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
} {idx1 tab1}
































finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291

  execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
  execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
} {tab1}
do_test index-9.2 {
  execsql {CREATE INDEX idx1 ON tab1(a)}
  execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
} {idx1 tab1}

# Allow more than one entry with the same key.
#
do_test index-10.0 {
  execsql {
    CREATE TABLE t1(a int, b int);
    CREATE INDEX i1 ON t1(a);
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(2,4);
    INSERT INTO t1 VALUES(3,8);
    INSERT INTO t1 VALUES(1,12);
    SELECT b FROM t1 WHERE a=1 ORDER BY b;
  }
} {2 12}
do_test index-10.1 {
  execsql {
    SELECT b FROM t1 WHERE a=2 ORDER BY b;
  }
} {4}
do_test index-10.2 {
  execsql {
    DELETE FROM t1 WHERE b=12;
    SELECT b FROM t1 WHERE a=1 ORDER BY b;
  }
} {2}
do_test index-10.2 {
  execsql {
    DELETE FROM t1 WHERE b=2;
    SELECT b FROM t1 WHERE a=1 ORDER BY b;
  }
} {}

finish_test
Changes to test/select2.test.
︙︙
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select2.test,v 1.5 2000/06/05 18:54:47 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}








|








19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select2.test,v 1.6 2000/06/08 16:54:40 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}
︙︙
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102








103
104
105
106
107
108
109
110
111
112
113

  execsql {SELECT count(*) FROM tbl2}
} {30000}
do_test select2-2.2 {
  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
} {29500}

do_test select2-3.1 {
  execsql {SELECT f1 FROM tbl2 WHERE f2==1000}
} {500}

do_test select2-3.2a {
  execsql {CREATE INDEX idx1 ON tbl2(f2)}
} {}

do_test select2-3.2b {
  execsql {SELECT f1 FROM tbl2 WHERE f2==1000}
} {500}









# Make sure queries run faster with an index than without
#
do_test select2-3.3 {
  set t1 [lindex [time {execsql {SELECT f1 from tbl2 WHERE f2==2000}} 1] 0]
  execsql {DROP INDEX idx1}
  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2==2000}} 1] 0]
  expr {$t1*25 < $t2}
} {1}

finish_test








|







|

>
>
>
>
>
>
>
>












86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121

  execsql {SELECT count(*) FROM tbl2}
} {30000}
do_test select2-2.2 {
  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
} {29500}

do_test select2-3.1 {
  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
} {500}

do_test select2-3.2a {
  execsql {CREATE INDEX idx1 ON tbl2(f2)}
} {}

do_test select2-3.2b {
  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
} {500}
do_test select2-3.2c {
  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
} {500}
do_test select2-3.2d {
  set t1 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}} 1] 0]
  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2=1000}} 1] 0]
  expr {$t1*0.9<$t2 && $t2*0.9<$t1}
} {1}

# Make sure queries run faster with an index than without
#
do_test select2-3.3 {
  set t1 [lindex [time {execsql {SELECT f1 from tbl2 WHERE f2==2000}} 1] 0]
  execsql {DROP INDEX idx1}
  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2==2000}} 1] 0]
  expr {$t1*25 < $t2}
} {1}

finish_test