SQLite

Check-in [ad433ec2b6]
Login

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

Overview
Comment:Reindex tests added and bugs fixed. (CVS 2075)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ad433ec2b6bd34e33dfe119668f38fbb978e889d
User & Date: drh 2004-11-07 13:01:50.000
Context
2004-11-08
07:13
Auto-vacuum: Account for the page reserved for windows locking (PENDING_BYTE). (CVS 2076) (check-in: d633569869 user: danielk1977 tags: trunk)
2004-11-07
13:01
Reindex tests added and bugs fixed. (CVS 2075) (check-in: ad433ec2b6 user: drh tags: trunk)
2004-11-06
12:26
Auto-vacuum: Ensure pages to be removed by database truncation are in the journal file. Also fix an sqlite3pager_movepage() bug. (CVS 2074) (check-in: 081676e491 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.272 2004/11/06 00:02:48 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.273 2004/11/07 13:01:50 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
2314
2315
2316
2317
2318
2319
2320
2321

2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
      /* A named index with an explicit CREATE INDEX statement */
      zStmt = sqlite3MPrintf("CREATE%s INDEX %.*q",
        onError==OE_None ? "" : " UNIQUE",
        Addr(pEnd->z) - Addr(pName->z) + 1,
        pName->z);
    }else{
      /* An automatic index created by a PRIMARY KEY or UNIQUE constraint */
      zStmt = sqlite3MPrintf("");

    }

    /* Add an entry in sqlite_master for this index
    */
    sqlite3NestedParse(pParse, 
        "INSERT INTO %Q.%s VALUES('index',%Q,%Q,#0,'%s');",
        db->aDb[iDb].zName, SCHEMA_TABLE(iDb),
        pIndex->zName,
        pTab->zName,
        zStmt
    );
    sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
    sqliteFree(zStmt);







|
>





|







2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
      /* A named index with an explicit CREATE INDEX statement */
      zStmt = sqlite3MPrintf("CREATE%s INDEX %.*q",
        onError==OE_None ? "" : " UNIQUE",
        Addr(pEnd->z) - Addr(pName->z) + 1,
        pName->z);
    }else{
      /* An automatic index created by a PRIMARY KEY or UNIQUE constraint */
      /* zStmt = sqlite3MPrintf(""); */
      zStmt = 0;
    }

    /* Add an entry in sqlite_master for this index
    */
    sqlite3NestedParse(pParse, 
        "INSERT INTO %Q.%s VALUES('index',%Q,%Q,#0,%Q);",
        db->aDb[iDb].zName, SCHEMA_TABLE(iDb),
        pIndex->zName,
        pTab->zName,
        zStmt
    );
    sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
    sqliteFree(zStmt);
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
  const char *zDb;            /* Name of the database */
  Table *pTab;                /* A table in the database */
  Index *pIndex;              /* An index associated with pTab */
  int iDb;                    /* The database index number */
  sqlite3 *db = pParse->db;   /* The database connection */
  Token *pObjName;            /* Name of the table or index to be reindexed */

  if( pName1==0 ){
    reindexDatabases(pParse, 0);
    return;
  }else if( pName2==0 ){
    pColl = sqlite3FindCollSeq(db, db->enc, pName1->z, pName1->n, 0);
    if( pColl ){
      reindexDatabases(pParse, pColl);
      return;
    }
  }
  iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pObjName);







|


|







2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
  const char *zDb;            /* Name of the database */
  Table *pTab;                /* A table in the database */
  Index *pIndex;              /* An index associated with pTab */
  int iDb;                    /* The database index number */
  sqlite3 *db = pParse->db;   /* The database connection */
  Token *pObjName;            /* Name of the table or index to be reindexed */

  if( pName1==0 || pName1->z==0 ){
    reindexDatabases(pParse, 0);
    return;
  }else if( pName2==0 || pName2->z==0 ){
    pColl = sqlite3FindCollSeq(db, db->enc, pName1->z, pName1->n, 0);
    if( pColl ){
      reindexDatabases(pParse, pColl);
      return;
    }
  }
  iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pObjName);
Changes to test/index.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.34 2004/11/04 04:42:28 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 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.35 2004/11/07 13:01:50 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 {
82
83
84
85
86
87
88







89
90
91
92
93
94
95
    set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
    execsql $sql
  }
  execsql {SELECT name FROM sqlite_master 
           WHERE type='index' AND tbl_name='test1'
           ORDER BY name}
} $r









# Verify that all the indices go away when we drop the table.
#
do_test index-3.3 {
  execsql {DROP TABLE test1}
  execsql {SELECT name FROM sqlite_master 







>
>
>
>
>
>
>







82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
    set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
    execsql $sql
  }
  execsql {SELECT name FROM sqlite_master 
           WHERE type='index' AND tbl_name='test1'
           ORDER BY name}
} $r
integrity_check index-3.2.1
ifcapable {reindex} {
  do_test index-3.2.2 {
    execsql REINDEX
  } {}
}
integrity_check index-3.2.3


# Verify that all the indices go away when we drop the table.
#
do_test index-3.3 {
  execsql {DROP TABLE test1}
  execsql {SELECT name FROM sqlite_master 
650
651
652
653
654
655
656







657
658
659
660
661
662
663
    CREATE TABLE t7(
       a PRIMARY KEY ON CONFLICT FAIL, 
       UNIQUE(a) ON CONFLICT IGNORE
    );
  }
} {1 {conflicting ON CONFLICT clauses specified}}








# Drop index with a quoted name.  Ticket #695.
#
do_test index-20.1 {
  execsql {
    CREATE INDEX "t6i2" ON t6(c);
    DROP INDEX "t6i2";
  }







>
>
>
>
>
>
>







657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
    CREATE TABLE t7(
       a PRIMARY KEY ON CONFLICT FAIL, 
       UNIQUE(a) ON CONFLICT IGNORE
    );
  }
} {1 {conflicting ON CONFLICT clauses specified}}

ifcapable {reindex} {
  do_test index-19.7 {
    execsql REINDEX
  } {}
}
integrity_check index-19.8

# Drop index with a quoted name.  Ticket #695.
#
do_test index-20.1 {
  execsql {
    CREATE INDEX "t6i2" ON t6(c);
    DROP INDEX "t6i2";
  }
Changes to test/insert.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.19 2004/11/03 16:27:02 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.20 2004/11/07 13:01:50 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
156
157
158
159
160
161
162





163
164
165
166
167
168
169
do_test insert-3.3 {
  execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}
  execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.4 {
  execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44}
} {22 -4.44 hi abc-123 wham}





integrity_check insert-3.5

# Test of expressions in the VALUES clause
#
do_test insert-4.1 {
  execsql {
    CREATE TABLE t3(a,b,c);







>
>
>
>
>







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
do_test insert-3.3 {
  execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}
  execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.4 {
  execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44}
} {22 -4.44 hi abc-123 wham}
ifcapable {reindex} {
  do_test insert-3.5 {
    execsql REINDEX
  } {}
}
integrity_check insert-3.5

# Test of expressions in the VALUES clause
#
do_test insert-4.1 {
  execsql {
    CREATE TABLE t3(a,b,c);
289
290
291
292
293
294
295





296
297
298
299
  }
} {2 4}
do_test insert-6.4 {
  execsql {
    SELECT * FROM t1 WHERE b=3;
  }
} {}






integrity_check insert-99.0

finish_test







>
>
>
>
>




294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
  }
} {2 4}
do_test insert-6.4 {
  execsql {
    SELECT * FROM t1 WHERE b=3;
  }
} {}
ifcapable {reindex} {
  do_test insert-6.7 {
    execsql REINDEX
  } {}
}

integrity_check insert-99.0

finish_test
Changes to test/reindex.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2004 November 5
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# This file implements tests for the REINDEX command.
#
# $Id: reindex.test,v 1.1 2004/11/05 23:46:15 drh Exp $

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

# There is nothing to test if REINDEX is disable for this build.
#
ifcapable {!reindex} {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2004 November 5
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# This file implements tests for the REINDEX command.
#
# $Id: reindex.test,v 1.2 2004/11/07 13:01:50 drh Exp $

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

# There is nothing to test if REINDEX is disable for this build.
#
ifcapable {!reindex} {
58
59
60
61
62
63
64
65














































































66
  }
} {}
do_test reindex-1.9 {
  catchsql {
    REINDEX bogus
  }
} {1 {unable to identify the object to be reindexed}}















































































finish_test








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

58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
  }
} {}
do_test reindex-1.9 {
  catchsql {
    REINDEX bogus
  }
} {1 {unable to identify the object to be reindexed}}

# Set up a table for testing that includes several different collating
# sequences including some that we can modify.
#
do_test reindex-2.1 {
  proc c1 {a b} {
    return [expr {-[string compare $a $b]}]
  }
  proc c2 {a b} {
    return [expr {-[string compare [string tolower $a] [string tolower $b]]}]
  }
  db collate c1 c1
  db collate c2 c2
  execsql {
    CREATE TABLE t2(
      a TEXT PRIMARY KEY COLLATE c1,
      b TEXT UNIQUE COLLATE c2,
      c TEXT COLLATE nocase,
      d TEST COLLATE binary
    );
    INSERT INTO t2 VALUES('abc','abc','abc','abc');
    INSERT INTO t2 VALUES('ABCD','ABCD','ABCD','ABCD');
    INSERT INTO t2 VALUES('bcd','bcd','bcd','bcd');
    INSERT INTO t2 VALUES('BCDE','BCDE','BCDE','BCDE');
    SELECT a FROM t2 ORDER BY a;
  }
} {bcd abc BCDE ABCD}
do_test reindex-2.2 {
  execsql {
    SELECT b FROM t2 ORDER BY b;
  }
} {BCDE bcd ABCD abc}
do_test reindex-2.3 {
  execsql {
    SELECT c FROM t2 ORDER BY c;
  }
} {abc ABCD bcd BCDE}
do_test reindex-2.4 {
  execsql {
    SELECT d FROM t2 ORDER BY d;
  }
} {ABCD BCDE abc bcd}

# Change a collating sequence function.  Verify that REINDEX rebuilds
# the index.
#
do_test reindex-2.5 {
  proc c1 {a b} {
    return [string compare $a $b]
  }
  execsql {
    SELECT a FROM t2 ORDER BY a;
  }
} {bcd abc BCDE ABCD}
ifcapable {integrityck} {
  do_test reindex-2.5.1 {
    string equal ok [execsql {PRAGMA integrity_check}]
  } {0}
}
do_test reindex-2.6 {
  execsql {
    REINDEX c2;
    SELECT a FROM t2 ORDER BY a;
  }
} {bcd abc BCDE ABCD}
do_test reindex-2.7 {
  execsql {
    REINDEX t1;
    SELECT a FROM t2 ORDER BY a;
  }
} {bcd abc BCDE ABCD}
do_test reindex-2.8 {
  execsql {
    REINDEX c1;
    SELECT a FROM t2 ORDER BY a;
  }
} {ABCD BCDE abc bcd}
integrity_check reindex-2.8.1

finish_test