Debian Codegraph @

www/unionvtab.html

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