diff options
Diffstat (limited to 'www/unionvtab.html')
-rw-r--r-- | www/unionvtab.html | 238 |
1 files changed, 238 insertions, 0 deletions
diff --git a/www/unionvtab.html b/www/unionvtab.html new file mode 100644 index 0000000..74a6be5 --- /dev/null +++ b/www/unionvtab.html @@ -0,0 +1,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> + |