Documentation Source Text

Check-in [6b8138c898]
Login

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

Overview
Comment:Update the documentation for the LIKE/GLOB/REGEXP enhancement to virtual tables.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6b8138c8981b915b34172902b0a1e466dfe0d0cc
User & Date: drh 2015-11-25 18:42:34
Context
2015-11-30
19:36
Add information about the sqlite3_index_info.colUsed field. check-in: 43d4cb5785 user: drh tags: trunk
2015-11-25
18:42
Update the documentation for the LIKE/GLOB/REGEXP enhancement to virtual tables. check-in: 6b8138c898 user: drh tags: trunk
2015-11-13
18:14
Update the PRAGMA cache_spill documentation to show the new functionality. check-in: 65c5ce0f21 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

18
19
20
21
22
23
24

25
26
27
28
29
30

31
32
33
34
35
36
37
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2016-00-00 (3.10.0)} {
<p><b>General improvements:</b>

<li>Enhance the [PRAGMA cache_spill] statement to accept a 32-bit integer
    parameter which is the threshold below which cache spilling is prohibited.
<li>On unix, if a symlink to a database file is opened, then the corresponding
    journal files are based on the actual filename, not the symlink name.
<li>Added the "--transaction" option to [sqldiff].
<li>Added the [sqlite3_db_cacheflush()] interface.

<li>Many small performance optimizations.
<p><b>Portability enhancements:</b>
<li>Work around a sign-exension bug in the optimizer of the HP C compiler on HP/UX.
<p><b>Enhancements to makefiles:</b>
<li>Added the --enable-editline option to the various autoconf-generated configure
    scripts.
<li>Omit all use of "awk" in the makefiles,







>






>







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2016-00-00 (3.10.0)} {
<p><b>General improvements:</b>
<li>Added support for [LIKE], [GLOB], and [REGEXP] operators on [virtual tables].
<li>Enhance the [PRAGMA cache_spill] statement to accept a 32-bit integer
    parameter which is the threshold below which cache spilling is prohibited.
<li>On unix, if a symlink to a database file is opened, then the corresponding
    journal files are based on the actual filename, not the symlink name.
<li>Added the "--transaction" option to [sqldiff].
<li>Added the [sqlite3_db_cacheflush()] interface.
<li>Added the [sqlite3_strlike()] interface.
<li>Many small performance optimizations.
<p><b>Portability enhancements:</b>
<li>Work around a sign-exension bug in the optimizer of the HP C compiler on HP/UX.
<p><b>Enhancements to makefiles:</b>
<li>Added the --enable-editline option to the various autoconf-generated configure
    scripts.
<li>Omit all use of "awk" in the makefiles,

Changes to pages/lang.in.

1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964


1965
1966
1967
1968
1969


1970
1971
1972
1973
1974
1975
1976
<p>^The maximum parameter number is set at compile-time by
the [SQLITE_MAX_VARIABLE_NUMBER] macro.  ^(An individual [database connection]
D can reduce its maximum parameter number below the compile-time maximum
using the [sqlite3_limit](D, [SQLITE_LIMIT_VARIABLE_NUMBER],...) interface.)^
</p>

<tcl>hd_fragment like LIKE ESCAPE</tcl>
<h3>The LIKE and GLOB operators</h3>
<p>^The LIKE operator does a pattern matching comparison. ^The operand
to the right of the LIKE operator contains the pattern and the left hand
operand contains the string to match against the pattern.

<tcl>hd_puts "^A percent symbol (\"%\") in the LIKE pattern matches any
sequence of zero or more characters in the string.  ^An underscore
(\"_\") in the LIKE pattern matches any single character in the
string.  ^(Any other character matches itself or its lower/upper case
equivalent (i.e. case-insensitive matching).)^  (A bug: ^SQLite only


understands upper/lower case for ASCII characters by default.  ^The
LIKE operator is case sensitive by default for unicode characters that are
beyond the ASCII range.  For example,
^the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>
is TRUE but <b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE.)<p>"</tcl>



<p>^If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. ^This character may be used in the LIKE pattern
to include literal percent or underscore characters. ^The escape
character followed by a percent symbol (%), underscore (_), or a second
instance of the escape character itself matches a







|








|
>
>




|
>
>







1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
<p>^The maximum parameter number is set at compile-time by
the [SQLITE_MAX_VARIABLE_NUMBER] macro.  ^(An individual [database connection]
D can reduce its maximum parameter number below the compile-time maximum
using the [sqlite3_limit](D, [SQLITE_LIMIT_VARIABLE_NUMBER],...) interface.)^
</p>

<tcl>hd_fragment like LIKE ESCAPE</tcl>
<h3>The LIKE, GLOB, and REGEXP operators</h3>
<p>^The LIKE operator does a pattern matching comparison. ^The operand
to the right of the LIKE operator contains the pattern and the left hand
operand contains the string to match against the pattern.

<tcl>hd_puts "^A percent symbol (\"%\") in the LIKE pattern matches any
sequence of zero or more characters in the string.  ^An underscore
(\"_\") in the LIKE pattern matches any single character in the
string.  ^(Any other character matches itself or its lower/upper case
equivalent (i.e. case-insensitive matching).)^

<u>Important Note:</u> ^SQLite only
understands upper/lower case for ASCII characters by default.  ^The
LIKE operator is case sensitive by default for unicode characters that are
beyond the ASCII range.  For example,
^the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>
is TRUE but <b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE."</tcl>
The ICU extension to SQLite includes an enhanced version of the
LIKE operator that does case folding across all unicode characters.</p>

<p>^If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. ^This character may be used in the LIKE pattern
to include literal percent or underscore characters. ^The escape
character followed by a percent symbol (%), underscore (_), or a second
instance of the escape character itself matches a

Changes to pages/pragma.in.

305
306
307
308
309
310
311
312



313
314
315
316
317
318
319
    ^When case_sensitive_like is disabled, the default LIKE behavior is
    expressed.  ^(When case_sensitive_like is enabled, case becomes
    significant.  So, for example,
    <b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.)^</p>

    <p>^This pragma uses [sqlite3_create_function()] to overload the
    LIKE and GLOB functions, which may override previous implementations
    of LIKE and GLOB registered by the application.</p>



}

Pragma cell_size_check {
    <p>^(<b>PRAGMA cell_size_check
       <br>PRAGMA cell_size_check = </b><i>boolean</i><b>;</b></p>
    <p>The cell_size_check pragma enables or disables additional sanity
    checking on database b-tree pages as they are initially read from disk.)^







|
>
>
>







305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
    ^When case_sensitive_like is disabled, the default LIKE behavior is
    expressed.  ^(When case_sensitive_like is enabled, case becomes
    significant.  So, for example,
    <b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.)^</p>

    <p>^This pragma uses [sqlite3_create_function()] to overload the
    LIKE and GLOB functions, which may override previous implementations
    of LIKE and GLOB registered by the application.  ^(This pragma
    only changes the behavior of the SQL [LIKE] operator.  It does not
    change the behavior of the [sqlite3_strlike()] C-language interface,
    which is always case insensitive.)^</p>
}

Pragma cell_size_check {
    <p>^(<b>PRAGMA cell_size_check
       <br>PRAGMA cell_size_check = </b><i>boolean</i><b>;</b></p>
    <p>The cell_size_check pragma enables or disables additional sanity
    checking on database b-tree pages as they are initially read from disk.)^

Changes to pages/vtab.in.

617
618
619
620
621
622
623
624
625
626
627
628
629



630
631
632
633
634
635
636
637
3.9.0 - perhaps using a call to [sqlite3_version()]. The result of attempting 
to access these fields in an sqlite3_index_info structure created by an 
older version of SQLite are undefined.

<p>In addition, there are some defined constants:

<blockquote><pre>
  #define SQLITE_INDEX_CONSTRAINT_EQ    2
  #define SQLITE_INDEX_CONSTRAINT_GT    4
  #define SQLITE_INDEX_CONSTRAINT_LE    8
  #define SQLITE_INDEX_CONSTRAINT_LT    16
  #define SQLITE_INDEX_CONSTRAINT_GE    32
  #define SQLITE_INDEX_CONSTRAINT_MATCH 64



  #define SQLITE_INDEX_SCAN_UNIQUE      1     /* Scan visits at most 1 row */
</pre></blockquote>

<p>The SQLite core calls the xBestIndex method when it is compiling a query
that involves a virtual table. In other words, SQLite calls this method 
when it is running [sqlite3_prepare()] or the equivalent. 
By calling this method, the 
SQLite core is saying to the virtual table that it needs to access 







|
|
|
|
|
|
>
>
>
|







617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
3.9.0 - perhaps using a call to [sqlite3_version()]. The result of attempting 
to access these fields in an sqlite3_index_info structure created by an 
older version of SQLite are undefined.

<p>In addition, there are some defined constants:

<blockquote><pre>
  #define SQLITE_INDEX_CONSTRAINT_EQ      2
  #define SQLITE_INDEX_CONSTRAINT_GT      4
  #define SQLITE_INDEX_CONSTRAINT_LE      8
  #define SQLITE_INDEX_CONSTRAINT_LT     16
  #define SQLITE_INDEX_CONSTRAINT_GE     32
  #define SQLITE_INDEX_CONSTRAINT_MATCH  64
  #define SQLITE_INDEX_CONSTRAINT_LIKE   65     /* 3.10.0 and later only */
  #define SQLITE_INDEX_CONSTRAINT_GLOB   66     /* 3.10.0 and later only */
  #define SQLITE_INDEX_CONSTRAINT_REGEXP 67     /* 3.10.0 and later only */
  #define SQLITE_INDEX_SCAN_UNIQUE        1     /* Scan visits at most 1 row */
</pre></blockquote>

<p>The SQLite core calls the xBestIndex method when it is compiling a query
that involves a virtual table. In other words, SQLite calls this method 
when it is running [sqlite3_prepare()] or the equivalent. 
By calling this method, the 
SQLite core is saying to the virtual table that it needs to access