SQLite

Check-in [4924fbb244]
Login

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

Overview
Comment:Open a savepoint within the FTS3 optimize() function.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4924fbb244bd1b7103e29e045812cb1c4d2d81c8
User & Date: dan 2009-12-01 13:48:14.000
Context
2009-12-01
13:57
Changes to the TCL interface header to allow it to be compiled independently from the amalgamation. (check-in: 58113932d9 user: drh tags: trunk)
13:48
Open a savepoint within the FTS3 optimize() function. (check-in: 4924fbb244 user: dan tags: trunk)
12:00
Fix a segfault that can occur when querying an empty FTS3 table. Also restore the rowid/docid conflict handling to work as it did in version 3.6.20. (check-in: c022f66b5a user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/fts3/fts3_write.c.
2151
2152
2153
2154
2155
2156
2157



2158
2159
2160






2161
2162
2163
2164
2165

/* 
** Flush any data in the pending-terms hash table to disk. If successful,
** merge all segments in the database (including the new segment, if 
** there was any data to flush) into a single segment. 
*/
int sqlite3Fts3Optimize(Fts3Table *p){



  int rc = sqlite3Fts3PendingTermsFlush(p);
  if( rc==SQLITE_OK ){
    rc = fts3SegmentMerge(p, -1);






  }
  return rc;
}

#endif







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





2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174

/* 
** Flush any data in the pending-terms hash table to disk. If successful,
** merge all segments in the database (including the new segment, if 
** there was any data to flush) into a single segment. 
*/
int sqlite3Fts3Optimize(Fts3Table *p){
  int rc;
  rc = sqlite3_exec(p->db, "SAVEPOINT fts3", 0, 0, 0);
  if( rc==SQLITE_OK ){
    rc = sqlite3Fts3PendingTermsFlush(p);
    if( rc==SQLITE_OK ){
      rc = fts3SegmentMerge(p, -1);
    }
    if( rc==SQLITE_OK ){
      rc = sqlite3_exec(p->db, "RELEASE fts3", 0, 0, 0);
    }else{
      sqlite3_exec(p->db, "ROLLBACK TO fts3 ; RELEASE fts3", 0, 0, 0);
    }
  }
  return rc;
}

#endif
Changes to test/e_fts3.test.
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
read_test  1.2.1.9 { SELECT docid, * FROM pages } {}

do_error_test fts3-1.2.1.10 {
  INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body');
} {SQL logic error or missing database}

# Test the optimize() function example:
if 0 {
ddl_test   1.2.2.1 { CREATE VIRTUAL TABLE docs USING fts3 }
write_test 1.2.2.2 docs_content {
  INSERT INTO docs VALUES('Others translate the first clause as');
}
write_test 1.2.2.3 docs_content {
  INSERT INTO docs VALUES('"which is for Solomon," meaning that');
}
write_test 1.2.2.4 docs_content {
  INSERT INTO docs VALUES('the book is dedicated to Solomon.');
}
read_test  1.2.2.5 { SELECT count(*) FROM docs_segdir } {3}
set DO_MALLOC_TEST 1
write_test 1.2.2.6 docs_segdir {
  INSERT INTO docs 
  SELECT * FROM (SELECT optimize(docs) FROM docs LIMIT 1) WHERE 0;
}
read_test  1.2.2.7 { SELECT count(*) FROM docs_segdir } {1}
}


##########################################################################
# Test the examples in section 1.3 (querying FTS3 tables)
#
ddl_test   1.3.1.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) }
read_test  1.3.1.2 { 
  SELECT * FROM mail WHERE rowid = 15;                -- Fast. Rowid lookup.







<











|

<



<
>







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
read_test  1.2.1.9 { SELECT docid, * FROM pages } {}

do_error_test fts3-1.2.1.10 {
  INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body');
} {SQL logic error or missing database}

# Test the optimize() function example:

ddl_test   1.2.2.1 { CREATE VIRTUAL TABLE docs USING fts3 }
write_test 1.2.2.2 docs_content {
  INSERT INTO docs VALUES('Others translate the first clause as');
}
write_test 1.2.2.3 docs_content {
  INSERT INTO docs VALUES('"which is for Solomon," meaning that');
}
write_test 1.2.2.4 docs_content {
  INSERT INTO docs VALUES('the book is dedicated to Solomon.');
}
read_test  1.2.2.5 { SELECT count(*) FROM docs_segdir } {3}
#set DO_MALLOC_TEST 1
write_test 1.2.2.6 docs_segdir {

  SELECT * FROM (SELECT optimize(docs) FROM docs LIMIT 1) WHERE 0;
}
read_test  1.2.2.7 { SELECT count(*) FROM docs_segdir } {1}

ddl_test   1.2.2.8 { DROP TABLE docs }

##########################################################################
# Test the examples in section 1.3 (querying FTS3 tables)
#
ddl_test   1.3.1.1 { CREATE VIRTUAL TABLE mail USING fts3(subject, body) }
read_test  1.3.1.2 { 
  SELECT * FROM mail WHERE rowid = 15;                -- Fast. Rowid lookup.
269
270
271
272
273
274
275

276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295



296

297
298
299
300
301
302
303
read_test  1.4.1.15 { 
  SELECT * FROM docs WHERE docs MATCH '"linux applications"'
} [concat $R(8)]
read_test  1.4.1.16 { 
  SELECT * FROM docs WHERE docs MATCH '"lin* app*"'
} [concat $R(8) $R(9)]
ddl_test   1.4.1.17 { DROP TABLE docs }


ddl_test   1.4.2.1 { CREATE VIRTUAL TABLE docs USING fts3() }
write_test 1.4.2.2 docs_content { 
  INSERT INTO docs VALUES(
  'SQLite is an ACID compliant embedded relational database management system')
}
foreach {tn query hit} {
3 {SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database'} 1
4 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite'} 1
5 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite'} 0
6 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"'} 1
7 {SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite'} 1
8 {SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational'} 1
9 {SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational'} 0
} {
  set res [db eval {SELECT * FROM docs WHERE $hit}]
  read_test 1.4.2.$tn $query $res
}
ddl_test 1.4.2.10 { DROP TABLE docs }




unset R



# TODO: Change numbering after here...

##########################################################################
# Test the example in section 5 (custom tokenizers).
#







>




















>
>
>
|
>







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
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
read_test  1.4.1.15 { 
  SELECT * FROM docs WHERE docs MATCH '"linux applications"'
} [concat $R(8)]
read_test  1.4.1.16 { 
  SELECT * FROM docs WHERE docs MATCH '"lin* app*"'
} [concat $R(8) $R(9)]
ddl_test   1.4.1.17 { DROP TABLE docs }
unset R

ddl_test   1.4.2.1 { CREATE VIRTUAL TABLE docs USING fts3() }
write_test 1.4.2.2 docs_content { 
  INSERT INTO docs VALUES(
  'SQLite is an ACID compliant embedded relational database management system')
}
foreach {tn query hit} {
3 {SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database'} 1
4 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite'} 1
5 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite'} 0
6 {SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"'} 1
7 {SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite'} 1
8 {SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational'} 1
9 {SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational'} 0
} {
  set res [db eval {SELECT * FROM docs WHERE $hit}]
  read_test 1.4.2.$tn $query $res
}
ddl_test 1.4.2.10 { DROP TABLE docs }

##########################################################################
# Test the example in section 3.1 (set operators with enhanced syntax).
#
set sqlite_fts3_enable_parentheses 1
ddl_test 1.5.1.1 { CREATE VIRTUAL TABLE docs USING fts3() }


# TODO: Change numbering after here...

##########################################################################
# Test the example in section 5 (custom tokenizers).
#