Documentation Source Text

Check-in [af4e59995a]
Login

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

Overview
Comment:Update the virtual table documentation to describe writable WITHOUT ROWID virtual tables. Also update the change log.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: af4e59995a671d857bfb0348f7211e5df85159da5ad82d3da5980bc2aad085c9
User & Date: drh 2017-08-11 01:21:41
Context
2017-08-12
16:44
Add a description of new fts5vocab table type "instance" to the the fts5 documentation. check-in: 2ba9202168 user: dan tags: trunk
2017-08-11
01:21
Update the virtual table documentation to describe writable WITHOUT ROWID virtual tables. Also update the change log. check-in: af4e59995a user: drh tags: trunk
00:54
Fix a typo in the fileformat document check-in: 85bec545b9 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

23
24
25
26
27
28
29


30
31
32
33
34
35
36

chng {2017-11-01 (3.21.0)} {
<li> Take advantage of the atomic-write capabilities of the 
     [https://en.wikipedia.org/wiki/F2FS|F2FS filesystem] when available, for
     greatly reduced transaction overhead.  This currently requires the
     [SQLITE_ENABLE_BATCH_ATOMIC_WRITE] compile-time option.
<li> Allow [ATTACH] and [DETACH] commands to work inside of a transaction.


<li> Query planner enhancements:
<ol type="a">
<li> Enhanced the [LIKE optimization] so that it works with an ESCAPE clause.
</ol>
<li> Miscellaneous [microoptimizations] reduce CPU usage by about 0.4%.
<li> Bug fixes:
<ol type="a">







>
>







23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

chng {2017-11-01 (3.21.0)} {
<li> Take advantage of the atomic-write capabilities of the 
     [https://en.wikipedia.org/wiki/F2FS|F2FS filesystem] when available, for
     greatly reduced transaction overhead.  This currently requires the
     [SQLITE_ENABLE_BATCH_ATOMIC_WRITE] compile-time option.
<li> Allow [ATTACH] and [DETACH] commands to work inside of a transaction.
<li> Allow [WITHOUT ROWID virtual tables] to be writable if the PRIMARY KEY
     contains exactly one column.
<li> Query planner enhancements:
<ol type="a">
<li> Enhanced the [LIKE optimization] so that it works with an ESCAPE clause.
</ol>
<li> Miscellaneous [microoptimizations] reduce CPU usage by about 0.4%.
<li> Bug fixes:
<ol type="a">

Changes to pages/vtab.in.

509
510
511
512
513
514
515
516

517
518
519
520
521
522
523
...
529
530
531
532
533
534
535
536
537
538
539
540






541
542
543
544
545
546
547
....
1108
1109
1110
1111
1112
1113
1114
1115

1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130


1131
1132

1133
1134
1135


1136
1137

1138
1139
1140
1141
1142

1143
1144
1145
1146
1147
1148
1149

<p>Arguments on the virtual table name are matched to [hidden columns]
in order.  The number of arguments can be less than the
number of hidden columns, in which case the latter hidden columns are
unconstrained.  However, an error results if there are more arguments
than there are hidden columns in the virtual table.

<tcl>hd_fragment worid {WITHOUT ROWID virtual tables}</tcl>

<h3> WITHOUT ROWID Virtual Tables </h3>

<p>Beginning with SQLite [version 3.14.0] ([dateof:3.14]), 
the CREATE TABLE statement that
is passed into [sqlite3_declare_vtab()] may contain a [WITHOUT ROWID] clause.
This is useful for cases where the virtual table rows 
cannot easily be mapped into unique integers.  A CREATE TABLE
................................................................................
virtual table.  Enforcement is the responsibility of the underlying
virtual table implementation.  But SQLite does assume that the PRIMARY KEY
constraint is valid - that the identified columns really are UNIQUE and
NOT NULL - and it uses that assumption to optimize queries against the
virtual table.

<p>The rowid column is not accessible on a
WITHOUT ROWID virtual table (of course).  Furthermore, since the
[xUpdate] method depends on having a valid rowid, the [xUpdate] method 
must be NULL for a WITHOUT ROWID virtual table.  That in turn means that
WITHOUT ROWID virtual tables must be read-only.








<tcl>############################################################# xConnect
hd_fragment xconnect {sqlite3_module.xConnect} {xConnect}</tcl>
<h2>The xConnect Method</h2>

<codeblock>
  int (*xConnect)(sqlite3*, void *pAux,
................................................................................
into the virtual table. If argv[1] is an SQL NULL, then the implementation 
must choose a rowid for the newly inserted row. Subsequent argv[] 
entries contain values of the columns of the virtual table, in the 
order that the columns were declared. The number of columns will
match the table declaration that the [xConnect] or [xCreate] method made 
using the [sqlite3_declare_vtab()] call.  All hidden columns are included.

<p>When doing an insert without a rowid (argc>1, argv[1] is an SQL NULL), the 

implementation must set *pRowid to the rowid of the newly inserted row; 
this will become the value returned by the [sqlite3_last_insert_rowid()]
function. Setting this value in all the other cases is a harmless no-op;
the SQLite engine ignores the *pRowid return value if argc==1 or 
argv[1] is not an SQL NULL.

<p>Each call to xUpdate will fall into one of cases shown below.
Not that references to <b>argv&#91;i&#93</b> mean the SQL value
held within the argv&#91;i&#93; object, not the argv&#91;i&#93;
object itself.

<blockquote>
<dl>
<dt><b>argc = 1 <br> argv[0] &ne; NULL</b>
<dd><p>The single row with rowid equal to argv[0] is deleted. No insert occurs.



<dt><b>argc &gt; 1 <br> argv[0] = NULL</b>

<dd><p>A new row is inserted with a rowid argv[1] and column values in
       argv[2] and following.  If argv[1] is an SQL NULL,
       the a new unique rowid is generated automatically.



<dt><b>argc &gt; 1 <br> argv[0] &ne; NULL <br> argv[0] = argv[1]</b>

<dd><p>The row with rowid argv[0] is updated with new values 
       in argv[2] and following parameters.

<dt><b>argc &gt; 1 <br> argv[0] &ne; NULL <br> argv[0] &ne; argv[1]</b>
<dd><p> The row with rowid argv[0] is updated with rowid argv[1] 

and new values in argv[2] and following parameters. This will occur 
when an SQL statement updates a rowid, as in the statement:
<blockquote>
   [UPDATE] table SET rowid=rowid+1 WHERE ...; 
</blockquote>
</dl>
</blockquote>







|
>







 







|
<
<
<

>
>
>
>
>
>







 







|
>
|













|
>
>


>
|
|
|
>
>


>
|
|


|
>







509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
...
530
531
532
533
534
535
536
537



538
539
540
541
542
543
544
545
546
547
548
549
550
551
....
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161

<p>Arguments on the virtual table name are matched to [hidden columns]
in order.  The number of arguments can be less than the
number of hidden columns, in which case the latter hidden columns are
unconstrained.  However, an error results if there are more arguments
than there are hidden columns in the virtual table.

<tcl>hd_fragment worid {WITHOUT ROWID virtual tables} \
{WITHOUT ROWID virtual table}</tcl>
<h3> WITHOUT ROWID Virtual Tables </h3>

<p>Beginning with SQLite [version 3.14.0] ([dateof:3.14]), 
the CREATE TABLE statement that
is passed into [sqlite3_declare_vtab()] may contain a [WITHOUT ROWID] clause.
This is useful for cases where the virtual table rows 
cannot easily be mapped into unique integers.  A CREATE TABLE
................................................................................
virtual table.  Enforcement is the responsibility of the underlying
virtual table implementation.  But SQLite does assume that the PRIMARY KEY
constraint is valid - that the identified columns really are UNIQUE and
NOT NULL - and it uses that assumption to optimize queries against the
virtual table.

<p>The rowid column is not accessible on a
WITHOUT ROWID virtual table (of course).




<p>The [xUpdate] method was originally designed around having a
[ROWID] as a single value.  The [xUpdate] method has been expanded to
accommodate an arbitrary PRIMARY KEY in place of the ROWID, but the
PRIMARY KEY must still be only one column.  For this reason, SQLite
will reject any WITHOUT ROWID virtual table that has more than one
PRIMARY KEY column and a non-NULL xUpdate method.

<tcl>############################################################# xConnect
hd_fragment xconnect {sqlite3_module.xConnect} {xConnect}</tcl>
<h2>The xConnect Method</h2>

<codeblock>
  int (*xConnect)(sqlite3*, void *pAux,
................................................................................
into the virtual table. If argv[1] is an SQL NULL, then the implementation 
must choose a rowid for the newly inserted row. Subsequent argv[] 
entries contain values of the columns of the virtual table, in the 
order that the columns were declared. The number of columns will
match the table declaration that the [xConnect] or [xCreate] method made 
using the [sqlite3_declare_vtab()] call.  All hidden columns are included.

<p>When doing an insert without a rowid (argc>1, argv[1] is an SQL NULL),
on a virtual table that uses ROWID (but not on a [WITHOUT ROWID virtual table],
the implementation must set *pRowid to the rowid of the newly inserted row; 
this will become the value returned by the [sqlite3_last_insert_rowid()]
function. Setting this value in all the other cases is a harmless no-op;
the SQLite engine ignores the *pRowid return value if argc==1 or 
argv[1] is not an SQL NULL.

<p>Each call to xUpdate will fall into one of cases shown below.
Not that references to <b>argv&#91;i&#93</b> mean the SQL value
held within the argv&#91;i&#93; object, not the argv&#91;i&#93;
object itself.

<blockquote>
<dl>
<dt><b>argc = 1 <br> argv[0] &ne; NULL</b>
<dd><p>
The single row with rowid or PRIMARY KEY equal to argv[0] is deleted. 
No insert occurs.

<dt><b>argc &gt; 1 <br> argv[0] = NULL</b>
<dd><p>
A new row is inserted with column values taken from
argv[2] and following.  In a rowid virtual table, if argv[1] is an SQL NULL,
then a new unique rowid is generated automatically.  The argv[1] will be NULL
for a [WITHOUT ROWID virtual table], in which case the implementation should
take the PRIMARY KEY value from the appropiate column in argv[2] and following.

<dt><b>argc &gt; 1 <br> argv[0] &ne; NULL <br> argv[0] = argv[1]</b>
<dd><p>
The row with rowid or PRIMARY KEY argv[0] is updated with new values 
in argv[2] and following parameters.

<dt><b>argc &gt; 1 <br> argv[0] &ne; NULL <br> argv[0] &ne; argv[1]</b>
<dd><p> The row with rowid or PRIMARY KEY argv[0] is updated with 
the rowid or PRIMARY KEY in argv[1] 
and new values in argv[2] and following parameters. This will occur 
when an SQL statement updates a rowid, as in the statement:
<blockquote>
   [UPDATE] table SET rowid=rowid+1 WHERE ...; 
</blockquote>
</dl>
</blockquote>