SQLite

Check-in [65d9c6fafb]
Login

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

Overview
Comment:Merge trunk changes with this branch.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | fts4-docid-range-constraints
Files: files | file ages | folders
SHA1: 65d9c6fafbc19d3582e07c31fd915a70e7fcfa8e
User & Date: dan 2013-10-01 20:10:26.229
Context
2013-10-02
08:04
Add a test to check that the new multi-token phrase optimization is actually helping. (check-in: bc3a2ed5fb user: dan tags: fts4-docid-range-constraints)
2013-10-01
20:10
Merge trunk changes with this branch. (check-in: 65d9c6fafb user: dan tags: fts4-docid-range-constraints)
20:02
Allow multi-token phrases to load doclists from the database incrementally. This allows queries that feature such phrases to benefit from the "docid<?" optimization. (check-in: ea543f081d user: dan tags: fts4-docid-range-constraints)
19:36
Fix comments in the fast_vacuum.c demonstration program. (check-in: e0db7b38e1 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/shell.c.
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
  int rc;
  int nResult;
  int i;
  const char *z;
  rc = sqlite3_prepare(p->db, zSelect, -1, &pSelect, 0);
  if( rc!=SQLITE_OK || !pSelect ){
    fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
    p->nErr++;
    return rc;
  }
  rc = sqlite3_step(pSelect);
  nResult = sqlite3_column_count(pSelect);
  while( rc==SQLITE_ROW ){
    if( zFirstRow ){
      fprintf(p->out, "%s", zFirstRow);







|







970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
  int rc;
  int nResult;
  int i;
  const char *z;
  rc = sqlite3_prepare(p->db, zSelect, -1, &pSelect, 0);
  if( rc!=SQLITE_OK || !pSelect ){
    fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
    if( (rc&0xff)!=SQLITE_CORRUPT ) p->nErr++;
    return rc;
  }
  rc = sqlite3_step(pSelect);
  nResult = sqlite3_column_count(pSelect);
  while( rc==SQLITE_ROW ){
    if( zFirstRow ){
      fprintf(p->out, "%s", zFirstRow);
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
      fprintf(p->out, ";\n");
    }    
    rc = sqlite3_step(pSelect);
  }
  rc = sqlite3_finalize(pSelect);
  if( rc!=SQLITE_OK ){
    fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
    p->nErr++;
  }
  return rc;
}

/*
** Allocate space and save off current error string.
*/







|







997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
      fprintf(p->out, ";\n");
    }    
    rc = sqlite3_step(pSelect);
  }
  rc = sqlite3_finalize(pSelect);
  if( rc!=SQLITE_OK ){
    fprintf(p->out, "/**** ERROR: (%d) %s *****/\n", rc, sqlite3_errmsg(p->db));
    if( (rc&0xff)!=SQLITE_CORRUPT ) p->nErr++;
  }
  return rc;
}

/*
** Allocate space and save off current error string.
*/
Changes to src/where.c.
6003
6004
6005
6006
6007
6008
6009
6010
6011
6012
6013
6014
6015
6016
6017
    }
  }
  WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
  pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;

  /* If the caller is an UPDATE or DELETE statement that is requesting
  ** to use a one-pass algorithm, determine if this is appropriate.
  ** The one-pass algorithm only works if the WHERE clause constraints
  ** the statement to update a single row.
  */
  assert( (wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || pWInfo->nLevel==1 );
  if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 
   && (pWInfo->a[0].pWLoop->wsFlags & WHERE_ONEROW)!=0 ){
    pWInfo->okOnePass = 1;
    pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY;







|







6003
6004
6005
6006
6007
6008
6009
6010
6011
6012
6013
6014
6015
6016
6017
    }
  }
  WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
  pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;

  /* If the caller is an UPDATE or DELETE statement that is requesting
  ** to use a one-pass algorithm, determine if this is appropriate.
  ** The one-pass algorithm only works if the WHERE clause constrains
  ** the statement to update a single row.
  */
  assert( (wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || pWInfo->nLevel==1 );
  if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 
   && (pWInfo->a[0].pWLoop->wsFlags & WHERE_ONEROW)!=0 ){
    pWInfo->okOnePass = 1;
    pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY;
Changes to test/amatch1.test.
31
32
33
34
35
36
37

38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61


62
63
64
65
66













67
68










69




70
















71
72

do_test amatch1-1.0 {
  db eval {
    CREATE VIRTUAL TABLE t1 USING fts4(words); --, tokenize porter);
  }
  fts_kjv_genesis
  db eval {

    CREATE VIRTUAL TABLE temp.t1aux USING fts4aux(main, t1);
    SELECT term FROM t1aux WHERE col=0 ORDER BY 1 LIMIT 5
  }
} {a abated abel abelmizraim abidah}
do_test amatch1-1.1 {
  db eval {
    SELECT term FROM t1aux WHERE term>'b' AND col=0 ORDER BY 1 LIMIT 5
  }
} {baalhanan babel back backward bad}
do_test amatch1-1.2 {
  db eval {
    SELECT term FROM t1aux WHERE term>'b' AND col=0 LIMIT 5
  }
} {baalhanan babel back backward bad}

# Load the amatch extension
load_static_extension db amatch

do_test amatch1-2.0 {
  db eval {
    CREATE TABLE costs(iLang, cFrom, cTo, Cost);
    INSERT INTO costs VALUES(0, '', '?', 100);
    INSERT INTO costs VALUES(0, '?', '', 100);
    INSERT INTO costs VALUES(0, '?', '?', 150);


    CREATE VIRTUAL TABLE t2 USING approximate_match(
      vocabulary_table=t1aux,
      vocabulary_word=term,
      edit_distances=costs
    );













    SELECT word, distance FROM t2
     WHERE word MATCH 'josxph' AND distance<300;










  }




} {joseph 150}

















finish_test







>


















|
<




>
>





>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

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

do_test amatch1-1.0 {
  db eval {
    CREATE VIRTUAL TABLE t1 USING fts4(words); --, tokenize porter);
  }
  fts_kjv_genesis
  db eval {
    INSERT INTO t1(t1) VALUES('optimize');
    CREATE VIRTUAL TABLE temp.t1aux USING fts4aux(main, t1);
    SELECT term FROM t1aux WHERE col=0 ORDER BY 1 LIMIT 5
  }
} {a abated abel abelmizraim abidah}
do_test amatch1-1.1 {
  db eval {
    SELECT term FROM t1aux WHERE term>'b' AND col=0 ORDER BY 1 LIMIT 5
  }
} {baalhanan babel back backward bad}
do_test amatch1-1.2 {
  db eval {
    SELECT term FROM t1aux WHERE term>'b' AND col=0 LIMIT 5
  }
} {baalhanan babel back backward bad}

# Load the amatch extension
load_static_extension db amatch

do_execsql_test amatch1-2.0 {

    CREATE TABLE costs(iLang, cFrom, cTo, Cost);
    INSERT INTO costs VALUES(0, '', '?', 100);
    INSERT INTO costs VALUES(0, '?', '', 100);
    INSERT INTO costs VALUES(0, '?', '?', 150);
    CREATE TABLE vocab(w TEXT UNIQUE);
    INSERT OR IGNORE INTO vocab SELECT term FROM t1aux;
    CREATE VIRTUAL TABLE t2 USING approximate_match(
      vocabulary_table=t1aux,
      vocabulary_word=term,
      edit_distances=costs
    );
    CREATE VIRTUAL TABLE t3 USING approximate_match(
      vocabulary_table=vocab,
      vocabulary_word=w,
      edit_distances=costs
    );
    CREATE VIRTUAL TABLE t4 USING approximate_match(
        vocabulary_table=vtemp,
        vocabulary_word=w,
        edit_distances=costs
      );
} {}
puts "Query against fts4aux: [time {
  do_execsql_test amatch1-2.1 {
      SELECT word, distance FROM t2
       WHERE word MATCH 'josxph' AND distance<300;
  } {joseph 150}} 1]"
puts "Query against ordinary table: [time {
  do_execsql_test amatch1-2.2 {
      SELECT word, distance FROM t3
       WHERE word MATCH 'josxph' AND distance<300;
  } {joseph 150}} 1]"
puts "Temp table initialized from fts4aux: [time {
  do_execsql_test amatch1-2.3a {
      CREATE TEMP TABLE vtemp(w TEXT UNIQUE);
      INSERT OR IGNORE INTO vtemp SELECT term FROM t1aux;
  } {}} 1]"
puts "Query against temp table: [time {
  do_execsql_test amatch1-2.3b {
      SELECT word, distance FROM t4
       WHERE word MATCH 'josxph' AND distance<300;
  } {joseph 150}} 1]"
do_execsql_test amatch1-2.11 {
    SELECT word, distance FROM t2
     WHERE word MATCH 'joxxph' AND distance<=300;
} {joseph 300}
do_execsql_test amatch1-2.12 {
    SELECT word, distance FROM t3
     WHERE word MATCH 'joxxph' AND distance<=300;
} {joseph 300}
do_execsql_test amatch1-2.21 {
    SELECT word, distance FROM t2
     WHERE word MATCH 'joxxph' AND distance<300;
} {}
do_execsql_test amatch1-2.22 {
    SELECT word, distance FROM t3
     WHERE word MATCH 'joxxph' AND distance<300;
} {}

finish_test
Added tool/fast_vacuum.c.




















































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
/*
** 2013-10-01
**
** 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 program implements a high-speed version of the VACUUM command.
** It repacks an SQLite database to remove as much unused space as
** possible and to relocate content sequentially in the file.
**
** This program runs faster and uses less temporary disk space than the
** built-in VACUUM command.  On the other hand, this program has a number
** of important restrictions relative to the built-in VACUUM command.
**
**  (1)  The caller must ensure that no other processes are accessing the
**       database file while the vacuum is taking place.  The usual SQLite
**       file locking is insufficient for this.  The caller must use
**       external means to make sure only this one routine is reading and
**       writing the database.
**
**  (2)  Database reconfiguration such as page size or auto_vacuum changes
**       are not supported by this utility.
**
**  (3)  The database file might be renamed if a power loss or crash
**       occurs at just the wrong moment.  Recovery must be prepared to
**       to deal with the possibly changed filename.
**
** This program is intended as a *Demonstration Only*.  The intent of this
** program is to provide example code that application developers can use
** when creating similar functionality in their applications.
**
** To compile this program:
**
**     cc fast_vacuum.c sqlite3.c
**
** Add whatever linker options are required.  (Example: "-ldl -lpthread").
** Then to run the program:
**
**    ./a.out file-to-vacuum
**
*/
#include "sqlite3.h"
#include <stdio.h>
#include <stdlib.h>

/*
** Finalize a prepared statement.  If an error has occurred, print the
** error message and exit.
*/
static void vacuumFinalize(sqlite3_stmt *pStmt){
  sqlite3 *db = sqlite3_db_handle(pStmt);
  int rc = sqlite3_finalize(pStmt);
  if( rc ){
    fprintf(stderr, "finalize error: %s\n", sqlite3_errmsg(db));
    exit(1);
  }
}

/*
** Execute zSql on database db. The SQL text is printed to standard
** output.  If an error occurs, print an error message and exit the
** process.
*/
static void execSql(sqlite3 *db, const char *zSql){
  sqlite3_stmt *pStmt;
  if( !zSql ){
    fprintf(stderr, "out of memory!\n");
    exit(1);
  }
  printf("%s;\n", zSql);
  if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){
    fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
    exit(1);
  }
  sqlite3_step(pStmt);
  vacuumFinalize(pStmt);
}

/*
** Execute zSql on database db. The zSql statement returns exactly
** one column. Execute this return value as SQL on the same database.
**
** The zSql statement is printed on standard output prior to being
** run.  If any errors occur, an error is printed and the process
** exits.
*/
static void execExecSql(sqlite3 *db, const char *zSql){
  sqlite3_stmt *pStmt;
  int rc;

  printf("%s;\n", zSql);
  rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
  if( rc!=SQLITE_OK ){
    fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
    exit(1);
  }
  while( SQLITE_ROW==sqlite3_step(pStmt) ){
    execSql(db, (char*)sqlite3_column_text(pStmt, 0));
  }
  vacuumFinalize(pStmt);
}


int main(int argc, char **argv){
  sqlite3 *db;                 /* Connection to the database file */
  int rc;                      /* Return code from SQLite interface calls */
  sqlite3_uint64 r;            /* A random number */
  const char *zDbToVacuum;     /* Database to be vacuumed */
  char *zBackupDb;             /* Backup copy of the original database */
  char *zTempDb;               /* Temporary database */
  char *zSql;                  /* An SQL statement */

  if( argc!=2 ){
    fprintf(stderr, "Usage: %s DATABASE\n", argv[0]);
    return 1;
  }

  /* Identify the database file to be vacuumed and open it.
  */
  zDbToVacuum = argv[1];
  printf("-- open database file \"%s\"\n", zDbToVacuum);
  rc = sqlite3_open(zDbToVacuum, &db);
  if( rc ){
    fprintf(stderr, "%s: %s\n", zDbToVacuum, sqlite3_errstr(rc));
    return 1;
  }

  /* Create names for two other files.  zTempDb will be a new database
  ** into which we construct a vacuumed copy of zDbToVacuum.  zBackupDb
  ** will be a new name for zDbToVacuum after it is vacuumed.
  */
  sqlite3_randomness(sizeof(r), &r);
  zTempDb = sqlite3_mprintf("%s-vacuum-%016llx", zDbToVacuum, r);
  zBackupDb = sqlite3_mprintf("%s-backup-%016llx", zDbToVacuum, r);

  /* Attach the zTempDb database to the database connection.
  */
  zSql = sqlite3_mprintf("ATTACH '%q' AS vacuum_db;", zTempDb);
  execSql(db, zSql);
  sqlite3_free(zSql);

  /* TODO:
  ** Set the page_size and auto_vacuum mode for zTempDb here, if desired.
  */

  /* The vacuum will occur inside of a transaction.  Set writable_schema
  ** to ON so that we can directly update the sqlite_master table in the
  ** zTempDb database.
  */
  execSql(db, "PRAGMA writable_schema=ON");
  execSql(db, "BEGIN");


  /* Query the schema of the main database. Create a mirror schema
  ** in the temporary database.
  */
  execExecSql(db, 
      "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) "
      "  FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"
      "   AND rootpage>0"
  );
  execExecSql(db,
      "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)"
      "  FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %'"
  );
  execExecSql(db,
      "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) "
      "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'"
  );

  /* Loop through the tables in the main database. For each, do
  ** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
  ** the contents to the temporary database.
  */
  execExecSql(db,
      "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
      "|| ' SELECT * FROM main.' || quote(name) "
      "FROM main.sqlite_master "
      "WHERE type = 'table' AND name!='sqlite_sequence' "
      "  AND rootpage>0"
  );

  /* Copy over the sequence table
  */
  execExecSql(db,
      "SELECT 'DELETE FROM vacuum_db.' || quote(name) "
      "FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence'"
  );
  execExecSql(db,
      "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
      "|| ' SELECT * FROM main.' || quote(name) "
      "FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence'"
  );

  /* Copy the triggers, views, and virtual tables from the main database
  ** over to the temporary database.  None of these objects has any
  ** associated storage, so all we have to do is copy their entries
  ** from the SQLITE_MASTER table.
  */
  execSql(db,
      "INSERT INTO vacuum_db.sqlite_master "
      "  SELECT type, name, tbl_name, rootpage, sql"
      "    FROM main.sqlite_master"
      "   WHERE type='view' OR type='trigger'"
      "      OR (type='table' AND rootpage=0)"
  );

  /* Commit the transaction and close the database
  */
  execSql(db, "COMMIT");
  printf("-- close database\n");
  sqlite3_close(db);


  /* At this point, zDbToVacuum is unchanged.  zTempDb contains a
  ** vacuumed copy of zDbToVacuum.  Rearrange filenames so that
  ** zTempDb becomes thenew zDbToVacuum.
  */
  printf("-- rename \"%s\" to \"%s\"\n", zDbToVacuum, zBackupDb);
  rename(zDbToVacuum, zBackupDb);
  printf("-- rename \"%s\" to \"%s\"\n", zTempDb, zDbToVacuum);
  rename(zTempDb, zDbToVacuum);

  /* Release allocated memory */
  sqlite3_free(zTempDb);
  sqlite3_free(zBackupDb);
  return 0;
}