SQLite

Check-in [4f2ab4b632]
Login

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

Overview
Comment:http://www.sqlite.org/cvstrac/tktview?tn=2166,35

Calling UPDATE against an fts table in a UTF-16 database inserts corrupted data into the database. The UTF-8 data is being inserted directly. This appears to happen because sqlite3_ value_text() destructively coerces a value to UTF-8, and it's never converted back when updating the table. This works around the problem by rearranging things so that the update happens before the coercion. (CVS 3596)

Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4f2ab4b6320ffc621900049b41f50bc30d76d7f5
User & Date: shess 2007-01-19 22:59:57.000
Context
2007-01-22
13:02
Fix a pragma test so that it works in directories that have spaces in their names. (CVS 3597) (check-in: 071c957a5d user: drh tags: trunk)
2007-01-19
22:59
http://www.sqlite.org/cvstrac/tktview?tn=2166,35

Calling UPDATE against an fts table in a UTF-16 database inserts corrupted data into the database. The UTF-8 data is being inserted directly. This appears to happen because sqlite3_ value_text() destructively coerces a value to UTF-8, and it's never converted back when updating the table. This works around the problem by rearranging things so that the update happens before the coercion. (CVS 3596) (check-in: 4f2ab4b632 user: shess tags: trunk)

01:06
Make sure the IS NULL optimization introduced by check-in (3494) correctly handles a LEFT JOIN where the a term from the right table of the join uses an IS NULL constraint. Ticket #2177. This check-in also adds the new test cases that were suppose to have been added with (3494) but which were mistakenly omitted. (CVS 3595) (check-in: 335863e4d1 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/fts1/fts1.c.
︙︙
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095

3096
3097
3098
3099
3100
3101
3102
3103

static int index_update(fulltext_vtab *v, sqlite_int64 iRow,
                        sqlite3_value **pValues, fts1Hash *pTerms){
  /* Generate an empty doclist for each term that previously appeared in this
   * row. */
  int rc = deleteTerms(v, pTerms, iRow);
  if( rc!=SQLITE_OK ) return rc;

  /* Now add positions for terms which appear in the updated row. */
  rc = insertTerms(v, pTerms, iRow, pValues);
  if( rc!=SQLITE_OK ) return rc;


  return content_update(v, pValues, iRow);  /* execute an SQL UPDATE */
}

/* This function implements the xUpdate callback; it's the top-level entry
 * point for inserting, deleting or updating a row in a full-text table. */
static int fulltextUpdate(sqlite3_vtab *pVtab, int nArg, sqlite3_value **ppArg,
                   sqlite_int64 *pRowid){
  fulltext_vtab *v = (fulltext_vtab *) pVtab;








<
|


>
|








3085
3086
3087
3088
3089
3090
3091

3092
3093
3094
3095
3096
3097
3098
3099
3100
3101
3102
3103

static int index_update(fulltext_vtab *v, sqlite_int64 iRow,
                        sqlite3_value **pValues, fts1Hash *pTerms){
  /* Generate an empty doclist for each term that previously appeared in this
   * row. */
  int rc = deleteTerms(v, pTerms, iRow);
  if( rc!=SQLITE_OK ) return rc;


  rc = content_update(v, pValues, iRow);  /* execute an SQL UPDATE */
  if( rc!=SQLITE_OK ) return rc;

  /* Now add positions for terms which appear in the updated row. */
  return insertTerms(v, pTerms, iRow, pValues);
}

/* This function implements the xUpdate callback; it's the top-level entry
 * point for inserting, deleting or updating a row in a full-text table. */
static int fulltextUpdate(sqlite3_vtab *pVtab, int nArg, sqlite3_value **ppArg,
                   sqlite_int64 *pRowid){
  fulltext_vtab *v = (fulltext_vtab *) pVtab;
︙︙
Changes to ext/fts2/fts2.c.
︙︙
3606
3607
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617

3618
3619
3620
3621
3622
3623
3624
3625

 * %_term table. */
static int index_update(fulltext_vtab *v, sqlite_int64 iRow,
                        sqlite3_value **pValues, fts2Hash *pTerms){
  /* Generate an empty doclist for each term that previously appeared in this
   * row. */
  int rc = deleteTerms(v, pTerms, iRow);
  if( rc!=SQLITE_OK ) return rc;

  /* Now add positions for terms which appear in the updated row. */
  rc = insertTerms(v, pTerms, iRow, pValues);
  if( rc!=SQLITE_OK ) return rc;


  return content_update(v, pValues, iRow);  /* execute an SQL UPDATE */
}

/*******************************************************************/
/* InteriorWriter is used to collect terms and block references into
** interior nodes in %_segments.  See commentary at top of file for
** format.
*/









<
|


>
|








3606
3607
3608
3609
3610
3611
3612
3613

3614
3615
3616
3617
3618
3619
3620
3621
3622
3623
3624
3625

 * %_term table. */
static int index_update(fulltext_vtab *v, sqlite_int64 iRow,
                        sqlite3_value **pValues, fts2Hash *pTerms){
  /* Generate an empty doclist for each term that previously appeared in this
   * row. */
  int rc = deleteTerms(v, pTerms, iRow);
  if( rc!=SQLITE_OK ) return rc;


  rc = content_update(v, pValues, iRow);  /* execute an SQL UPDATE */
  if( rc!=SQLITE_OK ) return rc;

  /* Now add positions for terms which appear in the updated row. */
  return insertTerms(v, pTerms, iRow, pValues);
}

/*******************************************************************/
/* InteriorWriter is used to collect terms and block references into
** interior nodes in %_segments.  See commentary at top of file for
** format.
*/
︙︙
Added test/fts1i.test. 




































































































































































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

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

# 2007 January 17
#
# The author disclaims copyright to this source code.
#
#*************************************************************************
# This file implements regression tests for SQLite fts1 library.  The
# focus here is testing handling of UPDATE when using UTF-16-encoded
# databases.
#
# $Id: fts1i.test,v 1.1 2007/01/19 22:59:57 shess Exp $
#

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

# Return the UTF-16 representation of the supplied UTF-8 string $str.
# If $nt is true, append two 0x00 bytes as a nul terminator.
# NOTE(shess) Copied from capi3.test.
proc utf16 {str {nt 1}} {
  set r [encoding convertto unicode $str]
  if {$nt} {
    append r "\x00\x00"
  }
  return $r
}

db eval {
  PRAGMA encoding = "UTF-16le";
  CREATE VIRTUAL TABLE t1 USING fts1(content);
}

do_test fts1i-1.0 {
  execsql {PRAGMA encoding}
} {UTF-16le}

do_test fts1i-1.1 {
  execsql {INSERT INTO t1 (rowid, content) VALUES(1, 'one')}
  execsql {SELECT content FROM t1 WHERE rowid = 1}
} {one}

do_test fts1i-1.2 {
  set sql "INSERT INTO t1 (rowid, content) VALUES(2, 'two')"
  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  execsql {SELECT content FROM t1 WHERE rowid = 2}
} {two}

do_test fts1i-1.3 {
  set sql "INSERT INTO t1 (rowid, content) VALUES(3, 'three')"
  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  set sql "UPDATE t1 SET content = 'trois' WHERE rowid = 3"
  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  execsql {SELECT content FROM t1 WHERE rowid = 3}
} {trois}

do_test fts1i-1.4 {
  set sql16 [utf16 {INSERT INTO t1 (rowid, content) VALUES(4, 'four')}]
  set STMT [sqlite3_prepare16 $DB $sql16 -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  execsql {SELECT content FROM t1 WHERE rowid = 4}
} {four}

do_test fts1i-1.5 {
  set sql16 [utf16 {INSERT INTO t1 (rowid, content) VALUES(5, 'five')}]
  set STMT [sqlite3_prepare16 $DB $sql16 -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  set sql "UPDATE t1 SET content = 'cinq' WHERE rowid = 5"
  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  execsql {SELECT content FROM t1 WHERE rowid = 5}
} {cinq}

finish_test
Added test/fts2i.test. 




































































































































































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

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

# 2007 January 17
#
# The author disclaims copyright to this source code.
#
#*************************************************************************
# This file implements regression tests for SQLite fts2 library.  The
# focus here is testing handling of UPDATE when using UTF-16-encoded
# databases.
#
# $Id: fts2i.test,v 1.1 2007/01/19 22:59:57 shess Exp $
#

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

# Return the UTF-16 representation of the supplied UTF-8 string $str.
# If $nt is true, append two 0x00 bytes as a nul terminator.
# NOTE(shess) Copied from capi3.test.
proc utf16 {str {nt 1}} {
  set r [encoding convertto unicode $str]
  if {$nt} {
    append r "\x00\x00"
  }
  return $r
}

db eval {
  PRAGMA encoding = "UTF-16le";
  CREATE VIRTUAL TABLE t1 USING fts2(content);
}

do_test fts2i-1.0 {
  execsql {PRAGMA encoding}
} {UTF-16le}

do_test fts2i-1.1 {
  execsql {INSERT INTO t1 (rowid, content) VALUES(1, 'one')}
  execsql {SELECT content FROM t1 WHERE rowid = 1}
} {one}

do_test fts2i-1.2 {
  set sql "INSERT INTO t1 (rowid, content) VALUES(2, 'two')"
  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  execsql {SELECT content FROM t1 WHERE rowid = 2}
} {two}

do_test fts2i-1.3 {
  set sql "INSERT INTO t1 (rowid, content) VALUES(3, 'three')"
  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  set sql "UPDATE t1 SET content = 'trois' WHERE rowid = 3"
  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  execsql {SELECT content FROM t1 WHERE rowid = 3}
} {trois}

do_test fts2i-1.4 {
  set sql16 [utf16 {INSERT INTO t1 (rowid, content) VALUES(4, 'four')}]
  set STMT [sqlite3_prepare16 $DB $sql16 -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  execsql {SELECT content FROM t1 WHERE rowid = 4}
} {four}

do_test fts2i-1.5 {
  set sql16 [utf16 {INSERT INTO t1 (rowid, content) VALUES(5, 'five')}]
  set STMT [sqlite3_prepare16 $DB $sql16 -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  set sql "UPDATE t1 SET content = 'cinq' WHERE rowid = 5"
  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
  sqlite3_step $STMT
  sqlite3_finalize $STMT
  execsql {SELECT content FROM t1 WHERE rowid = 5}
} {cinq}

finish_test