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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
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
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>The UNION Virtual Table</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
The UNION Virtual Table
</div>
</div>
<ol>
<li value="100"><p>
The UNION virtual table (hereafter: "union-vtab")
is a <a href="vtab.html">virtual table</a> that makes multiple independent
<a href="rowidtable.html">rowid tables</a> tables look like a single large table.
</p></li><li value="110"><p>
The tables that participate in a union-vtab can be in the same
database file, or they can be in separate databases files that
are <a href="lang_attach.html">ATTACH</a>-ed to the same database connection.
</p></li><li value="120"><p>
The union-vtab is not built into SQLite.
Union-vtab is a <a href="loadext.html">loadable extension</a>.
The source code for union-vtab is contained in a single file located at
<a href="https://sqlite.org/src/file/ext/misc/unionvtab.c">ext/misc/unionvtab.c</a>
in the SQLite source tree.
</p></li><li value="200"><p>
A new union-vtab instance is created as follows:
</p><blockquote>
<b>CREATE VIRTUAL TABLE temp.</b><i>tabname</i> <b>USING unionvtab(</b><i>query</i><b>);</b>
</blockquote>
</li><li value="210"><p>
Every union-vtab must be in the TEMP namespace. Hence, the "<b>temp.</b>"
prior to <i>tabname</i> is required. Only the union-vtab itself is required
to be in the TEMP namespace - the individual tables that are being unioned
can be any <a href="lang_attach.html">ATTACH</a>-ed database.
</p></li><li value="220"><p>
The <i>query</i> in the CREATE VIRTUAL TABLE statement for a union-vtab
must be a well-formed SQL query that returns four columns and an
arbitrary number of rows. Each row in the result of the <i>query</i>
represents a single table that is to participate in the union.
</p><ol>
<li value="221'">
The first column is the schema name for the database that contains
the tables. Examples: "main", "zone512".
</li><li value="222'">
The second column is the name of the table.
</li><li value="223'">
The third column is the minimum value for any rowid in the table.
</li><li value="224'">
The fourth column is the maximum value of any rowid in the table.
</li></ol>
</li><li value="230"><p>
The <i>query</i> for the CREATE VIRTUAL TABLE statement of a union-vtab
can be either a <a href="lang_select.html">SELECT</a> statement or a <a href="lang_select.html#values">VALUES clause</a>.
</p></li><li value="240"><p>
The <i>query</i> is run once when the CREATE VIRTUAL TABLE statement is
first encountered and the results of that one run are used for all subsequent
access to the union-vtab. If the results of <i>query</i> change, then
the union-vtab should be <a href="lang_droptable.html">DROP</a>-ed and recreated in order
to cause the <i>query</i> to be run again.
</p></li><li value="250"><p>
There must be no overlap in the bands of rowids for the various tables
in a union-vtab.
</p></li><li value="260"><p>
All tables that participate in a union-vtab must have identical
CREATE TABLE definitions, except that the names of the tables can be different.
</p></li><li value="270"><p>
All tables that participate in a union-vtab must be <a href="rowidtable.html">rowid tables</a>.
</p></li><li value="280"><p>
The column names and definitions for <i>tabname</i> will be the same as
the underlying tables. An application can access <i>tabname</i> just like
it was one of the real underlying tables.
</p></li><li value="290"><p>
No table in a union-vtab may contain entries that are outside of the
rowid bounds established by the <i>query</i> in the CREATE VIRTUAL TABLE
statement.
</p></li><li value="300"><p>
The union-vtab shall optimize access to the underlying real tables
when the constraints on the query are among forms shown below.
Other kinds of constraints may be optimized in the future, but only
these constraints are optimized in the initial implementation.
</p><ul>
<li> <b>rowid=$id</b>
</li><li> <b>rowid IN</b> <i>query-or-list</i>
</li><li> <b>rowid BETWEEN $lwr AND $upr</b>
</li></ul>
<p>
Other kinds of constraints may be used and will work, but other
constraints will be checked individually for each row and will not
be optimized (at least not initially).
All constraint checking is completely automatic regardless of whether
or not optimization occurs. The optimization referred to in this bullet point
is a performance consideration only. The same result is obtained
regardless of whether or not the query is optimized.
</p></li><li value="310"><p>
The union-vtab is read-only. Support for writing may be added at a later
time, but writing is not a part of the initial implementation.
</p></li><li value="320"><p>
<i>Nota bene:</i>
The <a href="c3ref/blob_open.html">sqlite3_blob_open()</a> interface does <u>not</u> work for a union-vtab.
BLOB content must be read from the union-vtab using ordinary SQL statements.
</p></li></ol>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/unionvtab.in?m=46e606d9d8">2022-01-08 05:02:57</a> UTC </small></i></p>
|
