Documentation Source Text

Check-in [68debd1491]
Login

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

Overview
Comment:Change docs to talk about the fact that virtual tables and shared cache can be used together beginning with version 3.6.17.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 68debd1491f732ad302f67d0c6099d8c766905c4
User & Date: drh 2009-08-06 17:47:59
Context
2009-08-09
22:27
Fixes to tcl documentation: Add docs for "version" and "profile" methods, add a hyperlink to the existing "incrblob" documentation. check-in: 2eccbd9b76 user: dan tags: trunk
2009-08-06
17:47
Change docs to talk about the fact that virtual tables and shared cache can be used together beginning with version 3.6.17. check-in: 68debd1491 user: drh tags: trunk
2009-08-01
18:38
Updates in preparation for version 3.6.17. check-in: c5059a139a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

41
42
43
44
45
46
47


48
49
50
51
52
53
54
    hd_enable_main 1
  }
}

chng {2009 Aug 12 (3.6.17)} {
<li>Expose the [sqlite3_strnicmp()] interface for use by extensions and
    applications.


<li>Many code simplifications and obscure bug fixes in support of
    providing [test coverage | 100% branch test coverage].
}

chng {2009 June 27 (3.6.16)} {
<li>Fix a bug (ticket #3929) that occasionally causes INSERT or UPDATE
    operations to fail on an indexed table that has a self-modifying trigger.







>
>







41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
    hd_enable_main 1
  }
}

chng {2009 Aug 12 (3.6.17)} {
<li>Expose the [sqlite3_strnicmp()] interface for use by extensions and
    applications.
<li>Remove the restriction on [virtual tables] and [shared cache mode].
    Virtual tables and shared cache can now be used at the same time.
<li>Many code simplifications and obscure bug fixes in support of
    providing [test coverage | 100% branch test coverage].
}

chng {2009 June 27 (3.6.16)} {
<li>Fix a bug (ticket #3929) that occasionally causes INSERT or UPDATE
    operations to fail on an indexed table that has a self-modifying trigger.

Changes to pages/sharedcache.in.

1
2
3
4
5
6
7
8
9
...
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
<title>SQLite Shared-Cache Mode</title>
<tcl>hd_keywords {SQLite Shared-Cache Mode}</tcl>

<tcl>
proc HEADING {level title} {
  global pnum
  incr pnum($level)
  foreach i [array names pnum] {
    if {$i>$level} {set pnum($i) 0}
................................................................................
And a connection could only share cache with another connection in the
same thread.
These restrictions were dropped beginning with SQLite version 3.5.0.
</p>

<tcl>HEADING 1 {Shared Cache And Virtual Tables}</tcl>



<p>Shared cache mode cannot be used together with virtual tables.
The reason for this is that virtual tables often make a copy of
the ["sqlite3*"] database handle that was used to originally open
the virtual table.  The virtual table might use this handle to 
prepare statements for recursive access to the database.  But
a prepared statement only works for the database connection on
which it was originally created.  If a virtual table is part of
a shared cache, it might be invoked by multiple database connections
but its prepared statements will only work on one of those 
connections.  To avoid problems sorting all of this out, and to
simplify the implementation of virtual tables, SQLite includes
checks that prohibit shared cache and virtual tables from being
used at the same time.</p>

<tcl>HEADING 1 {Enabling Shared-Cache Mode}</tcl>

<p>Shared-cache mode is enabled on a per-process basis. Using the C 
interface, the following API can be used to enable or disable shared-cache
mode for the calling thread:
</p>

|







 







>
>
|

<
<
|
<
<
<
<
<
<
<
<







1
2
3
4
5
6
7
8
9
...
184
185
186
187
188
189
190
191
192
193
194


195








196
197
198
199
200
201
202
<title>SQLite Shared-Cache Mode</title>
<tcl>hd_keywords {SQLite Shared-Cache Mode} {shared cache mode}</tcl>

<tcl>
proc HEADING {level title} {
  global pnum
  incr pnum($level)
  foreach i [array names pnum] {
    if {$i>$level} {set pnum($i) 0}
................................................................................
And a connection could only share cache with another connection in the
same thread.
These restrictions were dropped beginning with SQLite version 3.5.0.
</p>

<tcl>HEADING 1 {Shared Cache And Virtual Tables}</tcl>

<p>
In older versions of SQLite,
shared cache mode could not be used together with virtual tables.
The reason for this is that virtual tables often make a copy of


This restriction was removed in SQLite [version 3.6.17].









<tcl>HEADING 1 {Enabling Shared-Cache Mode}</tcl>

<p>Shared-cache mode is enabled on a per-process basis. Using the C 
interface, the following API can be used to enable or disable shared-cache
mode for the calling thread:
</p>

Changes to pages/vtab.in.

23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
...
229
230
231
232
233
234
235

236
237
238
239
240


241
242
243
244
245
246
247
<li> One cannot create a trigger on a virtual table.
<li> One cannot create additional indices on a virtual table. 
     (Virtual tables can have indices but that must be built into
     the virtual table implementation.  Indices cannot be added
     separately using [CREATE INDEX] statements.)
<li> One cannot run [ALTER TABLE | ALTER TABLE ... ADD COLUMN]
     commands against a virtual table.
<li> Virtual tables cannot be used in a database that makes use of
     the [shared cache] feature. 
</ul>

<p>Particular virtual table implementations might impose additional
constraints. For example, some virtual implementations might provide
read-only tables. Or some virtual table implementations might allow
[INSERT] or [DELETE] but not [UPDATE].  Or some virtual table implementations
might limit the kinds of UPDATEs that can be made.
................................................................................

<p>The rest of the module structure consists of methods used to implement
various features of the virtual table. Details on what each of these 
methods do are provided in the sequel.

<h3>1.3 Virtual Tables And Shared Cache</h3>


<p>The virtual table mechanism assumes that each [database connection] keeps 
its own copy of the database schema. Hence, the virtual table mechanism
cannot be used in a database that has [shared cache] enabled. 
The [sqlite3_create_module()] interface will return an error if 
the [shared cache] feature is enabled.



<h3>1.4 Creating New Virtual Table Implementations</h3>

<p>Follow these steps to create your own virtual table:

<p>
<ol>







<
<







 







>
|

|
|
<
>
>







23
24
25
26
27
28
29


30
31
32
33
34
35
36
...
227
228
229
230
231
232
233
234
235
236
237
238

239
240
241
242
243
244
245
246
247
<li> One cannot create a trigger on a virtual table.
<li> One cannot create additional indices on a virtual table. 
     (Virtual tables can have indices but that must be built into
     the virtual table implementation.  Indices cannot be added
     separately using [CREATE INDEX] statements.)
<li> One cannot run [ALTER TABLE | ALTER TABLE ... ADD COLUMN]
     commands against a virtual table.


</ul>

<p>Particular virtual table implementations might impose additional
constraints. For example, some virtual implementations might provide
read-only tables. Or some virtual table implementations might allow
[INSERT] or [DELETE] but not [UPDATE].  Or some virtual table implementations
might limit the kinds of UPDATEs that can be made.
................................................................................

<p>The rest of the module structure consists of methods used to implement
various features of the virtual table. Details on what each of these 
methods do are provided in the sequel.

<h3>1.3 Virtual Tables And Shared Cache</h3>

<p>Prior to SQLite [version 3.6.17], the virtual table mechanism assumes 
that each [database connection] kept
its own copy of the database schema. Hence, the virtual table mechanism
could not be used in a database that has [shared cache mode] enabled. 
The [sqlite3_create_module()] interface would return an error if 

[shared cache mode] is enabled.  That restriction was relaxed
beginning with SQLite [version 3.6.17].

<h3>1.4 Creating New Virtual Table Implementations</h3>

<p>Follow these steps to create your own virtual table:

<p>
<ol>