1 /*
2 ** 2017 July 15
3 **
4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
6 **
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
10 **
11 *************************************************************************
12 **
13 ** This file contains the implementation of the "unionvtab" virtual
14 ** table. This module provides read-only access to multiple tables,
15 ** possibly in multiple database files, via a single database object.
16 ** The source tables must have the following characteristics:
17 **
18 ** * They must all be rowid tables (not VIRTUAL or WITHOUT ROWID
19 ** tables or views).
20 **
21 ** * Each table must have the same set of columns, declared in
22 ** the same order and with the same declared types.
23 **
24 ** * The tables must not feature a user-defined column named "_rowid_".
25 **
26 ** * Each table must contain a distinct range of rowid values.
27 **
28 ** A "unionvtab" virtual table is created as follows:
29 **
30 ** CREATE VIRTUAL TABLE <name> USING unionvtab(<sql statement>);
31 **
32 ** The implementation evalutes <sql statement> whenever a unionvtab virtual
33 ** table is created or opened. It should return one row for each source
34 ** database table. The four columns required of each row are:
35 **
36 ** 1. The name of the database containing the table ("main" or "temp" or
37 ** the name of an attached database). Or NULL to indicate that all
38 ** databases should be searched for the table in the usual fashion.
39 **
40 ** 2. The name of the database table.
41 **
42 ** 3. The smallest rowid in the range of rowids that may be stored in the
43 ** database table (an integer).
44 **
45 ** 4. The largest rowid in the range of rowids that may be stored in the
46 ** database table (an integer).
47 **
48 */
49
50 #include "sqlite3ext.h"
51 SQLITE_EXTENSION_INIT1
52 #include <assert.h>
53 #include <string.h>
54
55 #ifndef SQLITE_OMIT_VIRTUALTABLE
56
57 /*
58 ** Largest and smallest possible 64-bit signed integers. These macros
59 ** copied from sqliteInt.h.
60 */
61 #ifndef LARGEST_INT64
62 # define LARGEST_INT64 (0xffffffff|(((sqlite3_int64)0x7fffffff)<<32))
63 #endif
64 #ifndef SMALLEST_INT64
65 # define SMALLEST_INT64 (((sqlite3_int64)-1) - LARGEST_INT64)
66 #endif
67
68 typedef struct UnionCsr UnionCsr;
69 typedef struct UnionTab UnionTab;
70 typedef struct UnionSrc UnionSrc;
71
72 /*
73 ** Each source table (row returned by the initialization query) is
74 ** represented by an instance of the following structure stored in the
75 ** UnionTab.aSrc[] array.
76 */
77 struct UnionSrc {
78 char *zDb; /* Database containing source table */
79 char *zTab; /* Source table name */
80 sqlite3_int64 iMin; /* Minimum rowid */
81 sqlite3_int64 iMax; /* Maximum rowid */
82 };
83
84 /*
85 ** Virtual table type for union vtab.
86 */
87 struct UnionTab {
88 sqlite3_vtab base; /* Base class - must be first */
89 sqlite3 *db; /* Database handle */
90 int iPK; /* INTEGER PRIMARY KEY column, or -1 */
91 int nSrc; /* Number of elements in the aSrc[] array */
92 UnionSrc *aSrc; /* Array of source tables, sorted by rowid */
93 };
94
95 /*
96 ** Virtual table cursor type for union vtab.
97 */
98 struct UnionCsr {
99 sqlite3_vtab_cursor base; /* Base class - must be first */
100 sqlite3_stmt *pStmt; /* SQL statement to run */
101 };
102
103 /*
104 ** If *pRc is other than SQLITE_OK when this function is called, it
105 ** always returns NULL. Otherwise, it attempts to allocate and return
106 ** a pointer to nByte bytes of zeroed memory. If the memory allocation
107 ** is attempted but fails, NULL is returned and *pRc is set to
108 ** SQLITE_NOMEM.
109 */
unionMalloc(int * pRc,int nByte)110 static void *unionMalloc(int *pRc, int nByte){
111 void *pRet;
112 assert( nByte>0 );
113 if( *pRc==SQLITE_OK ){
114 pRet = sqlite3_malloc(nByte);
115 if( pRet ){
116 memset(pRet, 0, nByte);
117 }else{
118 *pRc = SQLITE_NOMEM;
119 }
120 }else{
121 pRet = 0;
122 }
123 return pRet;
124 }
125
126 /*
127 ** If *pRc is other than SQLITE_OK when this function is called, it
128 ** always returns NULL. Otherwise, it attempts to allocate and return
129 ** a copy of the nul-terminated string passed as the second argument.
130 ** If the allocation is attempted but fails, NULL is returned and *pRc is
131 ** set to SQLITE_NOMEM.
132 */
unionStrdup(int * pRc,const char * zIn)133 static char *unionStrdup(int *pRc, const char *zIn){
134 char *zRet = 0;
135 if( zIn ){
136 int nByte = (int)strlen(zIn) + 1;
137 zRet = unionMalloc(pRc, nByte);
138 if( zRet ){
139 memcpy(zRet, zIn, nByte);
140 }
141 }
142 return zRet;
143 }
144
145 /*
146 ** If the first character of the string passed as the only argument to this
147 ** function is one of the 4 that may be used as an open quote character
148 ** in SQL, this function assumes that the input is a well-formed quoted SQL
149 ** string. In this case the string is dequoted in place.
150 **
151 ** If the first character of the input is not an open quote, then this
152 ** function is a no-op.
153 */
unionDequote(char * z)154 static void unionDequote(char *z){
155 if( z ){
156 char q = z[0];
157
158 /* Set stack variable q to the close-quote character */
159 if( q=='[' || q=='\'' || q=='"' || q=='`' ){
160 int iIn = 1;
161 int iOut = 0;
162 if( q=='[' ) q = ']';
163 while( z[iIn] ){
164 if( z[iIn]==q ){
165 if( z[iIn+1]!=q ){
166 /* Character iIn was the close quote. */
167 iIn++;
168 break;
169 }else{
170 /* Character iIn and iIn+1 form an escaped quote character. Skip
171 ** the input cursor past both and copy a single quote character
172 ** to the output buffer. */
173 iIn += 2;
174 z[iOut++] = q;
175 }
176 }else{
177 z[iOut++] = z[iIn++];
178 }
179 }
180 z[iOut] = '\0';
181 }
182 }
183 }
184
185 /*
186 ** This function is a no-op if *pRc is set to other than SQLITE_OK when it
187 ** is called. NULL is returned in this case.
188 **
189 ** Otherwise, the SQL statement passed as the third argument is prepared
190 ** against the database handle passed as the second. If the statement is
191 ** successfully prepared, a pointer to the new statement handle is
192 ** returned. It is the responsibility of the caller to eventually free the
193 ** statement by calling sqlite3_finalize(). Alternatively, if statement
194 ** compilation fails, NULL is returned, *pRc is set to an SQLite error
195 ** code and *pzErr may be set to an error message buffer allocated by
196 ** sqlite3_malloc().
197 */
unionPrepare(int * pRc,sqlite3 * db,const char * zSql,char ** pzErr)198 static sqlite3_stmt *unionPrepare(
199 int *pRc, /* IN/OUT: Error code */
200 sqlite3 *db, /* Database handle */
201 const char *zSql, /* SQL statement to prepare */
202 char **pzErr /* OUT: Error message */
203 ){
204 sqlite3_stmt *pRet = 0;
205 if( *pRc==SQLITE_OK ){
206 int rc = sqlite3_prepare_v2(db, zSql, -1, &pRet, 0);
207 if( rc!=SQLITE_OK ){
208 *pzErr = sqlite3_mprintf("sql error: %s", sqlite3_errmsg(db));
209 *pRc = rc;
210 }
211 }
212 return pRet;
213 }
214
215 /*
216 ** Like unionPrepare(), except prepare the results of vprintf(zFmt, ...)
217 ** instead of a constant SQL string.
218 */
unionPreparePrintf(int * pRc,char ** pzErr,sqlite3 * db,const char * zFmt,...)219 static sqlite3_stmt *unionPreparePrintf(
220 int *pRc, /* IN/OUT: Error code */
221 char **pzErr, /* OUT: Error message */
222 sqlite3 *db, /* Database handle */
223 const char *zFmt, /* printf() format string */
224 ... /* Trailing printf args */
225 ){
226 sqlite3_stmt *pRet = 0;
227 char *zSql;
228 va_list ap;
229 va_start(ap, zFmt);
230
231 zSql = sqlite3_vmprintf(zFmt, ap);
232 if( *pRc==SQLITE_OK ){
233 if( zSql==0 ){
234 *pRc = SQLITE_NOMEM;
235 }else{
236 pRet = unionPrepare(pRc, db, zSql, pzErr);
237 }
238 }
239 sqlite3_free(zSql);
240
241 va_end(ap);
242 return pRet;
243 }
244
245
246 /*
247 ** Call sqlite3_reset() on SQL statement pStmt. If *pRc is set to
248 ** SQLITE_OK when this function is called, then it is set to the
249 ** value returned by sqlite3_reset() before this function exits.
250 ** In this case, *pzErr may be set to point to an error message
251 ** buffer allocated by sqlite3_malloc().
252 */
unionReset(int * pRc,sqlite3_stmt * pStmt,char ** pzErr)253 static void unionReset(int *pRc, sqlite3_stmt *pStmt, char **pzErr){
254 int rc = sqlite3_reset(pStmt);
255 if( *pRc==SQLITE_OK ){
256 *pRc = rc;
257 if( rc ){
258 *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(sqlite3_db_handle(pStmt)));
259 }
260 }
261 }
262
263 /*
264 ** Call sqlite3_finalize() on SQL statement pStmt. If *pRc is set to
265 ** SQLITE_OK when this function is called, then it is set to the
266 ** value returned by sqlite3_finalize() before this function exits.
267 */
unionFinalize(int * pRc,sqlite3_stmt * pStmt)268 static void unionFinalize(int *pRc, sqlite3_stmt *pStmt){
269 int rc = sqlite3_finalize(pStmt);
270 if( *pRc==SQLITE_OK ) *pRc = rc;
271 }
272
273 /*
274 ** xDisconnect method.
275 */
unionDisconnect(sqlite3_vtab * pVtab)276 static int unionDisconnect(sqlite3_vtab *pVtab){
277 if( pVtab ){
278 UnionTab *pTab = (UnionTab*)pVtab;
279 int i;
280 for(i=0; i<pTab->nSrc; i++){
281 sqlite3_free(pTab->aSrc[i].zDb);
282 sqlite3_free(pTab->aSrc[i].zTab);
283 }
284 sqlite3_free(pTab->aSrc);
285 sqlite3_free(pTab);
286 }
287 return SQLITE_OK;
288 }
289
290 /*
291 ** This function is a no-op if *pRc is other than SQLITE_OK when it is
292 ** called. In this case it returns NULL.
293 **
294 ** Otherwise, this function checks that the source table passed as the
295 ** second argument (a) exists, (b) is not a view and (c) has a column
296 ** named "_rowid_" of type "integer" that is the primary key.
297 ** If this is not the case, *pRc is set to SQLITE_ERROR and NULL is
298 ** returned.
299 **
300 ** Finally, if the source table passes the checks above, a nul-terminated
301 ** string describing the column names and types belonging to the source
302 ** table is returned. Tables with the same set of column names and types
303 ** cause this function to return identical strings. Is is the responsibility
304 ** of the caller to free the returned string using sqlite3_free() when
305 ** it is no longer required.
306 */
unionSourceToStr(int * pRc,sqlite3 * db,UnionSrc * pSrc,sqlite3_stmt * pStmt,char ** pzErr)307 static char *unionSourceToStr(
308 int *pRc, /* IN/OUT: Error code */
309 sqlite3 *db, /* Database handle */
310 UnionSrc *pSrc, /* Source table to test */
311 sqlite3_stmt *pStmt,
312 char **pzErr /* OUT: Error message */
313 ){
314 char *zRet = 0;
315 if( *pRc==SQLITE_OK ){
316 int bPk = 0;
317 const char *zType = 0;
318 int rc;
319
320 sqlite3_table_column_metadata(
321 db, pSrc->zDb, pSrc->zTab, "_rowid_", &zType, 0, 0, &bPk, 0
322 );
323 rc = sqlite3_errcode(db);
324 if( rc==SQLITE_ERROR
325 || (rc==SQLITE_OK && (!bPk || sqlite3_stricmp("integer", zType)))
326 ){
327 rc = SQLITE_ERROR;
328 *pzErr = sqlite3_mprintf("no such rowid table: %s%s%s",
329 (pSrc->zDb ? pSrc->zDb : ""),
330 (pSrc->zDb ? "." : ""),
331 pSrc->zTab
332 );
333 }
334
335 if( rc==SQLITE_OK ){
336 sqlite3_bind_text(pStmt, 1, pSrc->zTab, -1, SQLITE_STATIC);
337 sqlite3_bind_text(pStmt, 2, pSrc->zDb, -1, SQLITE_STATIC);
338 if( SQLITE_ROW==sqlite3_step(pStmt) ){
339 zRet = unionStrdup(&rc, (const char*)sqlite3_column_text(pStmt, 0));
340 }
341 unionReset(&rc, pStmt, pzErr);
342 }
343
344 *pRc = rc;
345 }
346
347 return zRet;
348 }
349
350 /*
351 ** Check that all configured source tables exist and have the same column
352 ** names and datatypes. If this is not the case, or if some other error
353 ** occurs, return an SQLite error code. In this case *pzErr may be set
354 ** to point to an error message buffer allocated by sqlite3_mprintf().
355 ** Or, if no problems regarding the source tables are detected and no
356 ** other error occurs, SQLITE_OK is returned.
357 */
unionSourceCheck(UnionTab * pTab,char ** pzErr)358 static int unionSourceCheck(UnionTab *pTab, char **pzErr){
359 const char *zSql =
360 "SELECT group_concat(quote(name) || '.' || quote(type)) "
361 "FROM pragma_table_info(?, ?)";
362 int rc = SQLITE_OK;
363
364 if( pTab->nSrc==0 ){
365 *pzErr = sqlite3_mprintf("no source tables configured");
366 rc = SQLITE_ERROR;
367 }else{
368 sqlite3_stmt *pStmt = 0;
369 char *z0 = 0;
370 int i;
371
372 pStmt = unionPrepare(&rc, pTab->db, zSql, pzErr);
373 if( rc==SQLITE_OK ){
374 z0 = unionSourceToStr(&rc, pTab->db, &pTab->aSrc[0], pStmt, pzErr);
375 }
376 for(i=1; i<pTab->nSrc; i++){
377 char *z = unionSourceToStr(&rc, pTab->db, &pTab->aSrc[i], pStmt, pzErr);
378 if( rc==SQLITE_OK && sqlite3_stricmp(z, z0) ){
379 *pzErr = sqlite3_mprintf("source table schema mismatch");
380 rc = SQLITE_ERROR;
381 }
382 sqlite3_free(z);
383 }
384
385 unionFinalize(&rc, pStmt);
386 sqlite3_free(z0);
387 }
388 return rc;
389 }
390
391 /*
392 ** xConnect/xCreate method.
393 **
394 ** The argv[] array contains the following:
395 **
396 ** argv[0] -> module name ("unionvtab")
397 ** argv[1] -> database name
398 ** argv[2] -> table name
399 ** argv[3] -> SQL statement
400 */
unionConnect(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVtab,char ** pzErr)401 static int unionConnect(
402 sqlite3 *db,
403 void *pAux,
404 int argc, const char *const*argv,
405 sqlite3_vtab **ppVtab,
406 char **pzErr
407 ){
408 UnionTab *pTab = 0;
409 int rc = SQLITE_OK;
410
411 (void)pAux; /* Suppress harmless 'unused parameter' warning */
412 if( sqlite3_stricmp("temp", argv[1]) ){
413 /* unionvtab tables may only be created in the temp schema */
414 *pzErr = sqlite3_mprintf("unionvtab tables must be created in TEMP schema");
415 rc = SQLITE_ERROR;
416 }else if( argc!=4 ){
417 *pzErr = sqlite3_mprintf("wrong number of arguments for unionvtab");
418 rc = SQLITE_ERROR;
419 }else{
420 int nAlloc = 0; /* Allocated size of pTab->aSrc[] */
421 sqlite3_stmt *pStmt = 0; /* Argument statement */
422 char *zArg = unionStrdup(&rc, argv[3]); /* Copy of argument to CVT */
423
424 /* Prepare the SQL statement. Instead of executing it directly, sort
425 ** the results by the "minimum rowid" field. This makes it easier to
426 ** check that there are no rowid range overlaps between source tables
427 ** and that the UnionTab.aSrc[] array is always sorted by rowid. */
428 unionDequote(zArg);
429 pStmt = unionPreparePrintf(&rc, pzErr, db,
430 "SELECT * FROM (%z) ORDER BY 3", zArg
431 );
432
433 /* Allocate the UnionTab structure */
434 pTab = unionMalloc(&rc, sizeof(UnionTab));
435
436 /* Iterate through the rows returned by the SQL statement specified
437 ** as an argument to the CREATE VIRTUAL TABLE statement. */
438 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
439 const char *zDb = (const char*)sqlite3_column_text(pStmt, 0);
440 const char *zTab = (const char*)sqlite3_column_text(pStmt, 1);
441 sqlite3_int64 iMin = sqlite3_column_int64(pStmt, 2);
442 sqlite3_int64 iMax = sqlite3_column_int64(pStmt, 3);
443 UnionSrc *pSrc;
444
445 /* Grow the pTab->aSrc[] array if required. */
446 if( nAlloc<=pTab->nSrc ){
447 int nNew = nAlloc ? nAlloc*2 : 8;
448 UnionSrc *aNew = (UnionSrc*)sqlite3_realloc(
449 pTab->aSrc, nNew*sizeof(UnionSrc)
450 );
451 if( aNew==0 ){
452 rc = SQLITE_NOMEM;
453 break;
454 }else{
455 memset(&aNew[pTab->nSrc], 0, (nNew-pTab->nSrc)*sizeof(UnionSrc));
456 pTab->aSrc = aNew;
457 nAlloc = nNew;
458 }
459 }
460
461 /* Check for problems with the specified range of rowids */
462 if( iMax<iMin || (pTab->nSrc>0 && iMin<=pTab->aSrc[pTab->nSrc-1].iMax) ){
463 *pzErr = sqlite3_mprintf("rowid range mismatch error");
464 rc = SQLITE_ERROR;
465 }
466
467 pSrc = &pTab->aSrc[pTab->nSrc++];
468 pSrc->zDb = unionStrdup(&rc, zDb);
469 pSrc->zTab = unionStrdup(&rc, zTab);
470 pSrc->iMin = iMin;
471 pSrc->iMax = iMax;
472 }
473 unionFinalize(&rc, pStmt);
474 pStmt = 0;
475
476 /* Verify that all source tables exist and have compatible schemas. */
477 if( rc==SQLITE_OK ){
478 pTab->db = db;
479 rc = unionSourceCheck(pTab, pzErr);
480 }
481
482 /* Compose a CREATE TABLE statement and pass it to declare_vtab() */
483 if( rc==SQLITE_OK ){
484 pStmt = unionPreparePrintf(&rc, pzErr, db, "SELECT "
485 "'CREATE TABLE xyz('"
486 " || group_concat(quote(name) || ' ' || type, ', ')"
487 " || ')',"
488 "max((cid+1) * (type='INTEGER' COLLATE nocase AND pk=1))-1 "
489 "FROM pragma_table_info(%Q, ?)",
490 pTab->aSrc[0].zTab, pTab->aSrc[0].zDb
491 );
492 }
493 if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
494 const char *zDecl = (const char*)sqlite3_column_text(pStmt, 0);
495 rc = sqlite3_declare_vtab(db, zDecl);
496 pTab->iPK = sqlite3_column_int(pStmt, 1);
497 }
498
499 unionFinalize(&rc, pStmt);
500 }
501
502 if( rc!=SQLITE_OK ){
503 unionDisconnect((sqlite3_vtab*)pTab);
504 pTab = 0;
505 }
506
507 *ppVtab = (sqlite3_vtab*)pTab;
508 return rc;
509 }
510
511
512 /*
513 ** xOpen
514 */
unionOpen(sqlite3_vtab * p,sqlite3_vtab_cursor ** ppCursor)515 static int unionOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
516 UnionCsr *pCsr;
517 int rc = SQLITE_OK;
518 (void)p; /* Suppress harmless warning */
519 pCsr = (UnionCsr*)unionMalloc(&rc, sizeof(UnionCsr));
520 *ppCursor = &pCsr->base;
521 return rc;
522 }
523
524 /*
525 ** xClose
526 */
unionClose(sqlite3_vtab_cursor * cur)527 static int unionClose(sqlite3_vtab_cursor *cur){
528 UnionCsr *pCsr = (UnionCsr*)cur;
529 sqlite3_finalize(pCsr->pStmt);
530 sqlite3_free(pCsr);
531 return SQLITE_OK;
532 }
533
534
535 /*
536 ** xNext
537 */
unionNext(sqlite3_vtab_cursor * cur)538 static int unionNext(sqlite3_vtab_cursor *cur){
539 UnionCsr *pCsr = (UnionCsr*)cur;
540 int rc;
541 assert( pCsr->pStmt );
542 if( sqlite3_step(pCsr->pStmt)!=SQLITE_ROW ){
543 rc = sqlite3_finalize(pCsr->pStmt);
544 pCsr->pStmt = 0;
545 }else{
546 rc = SQLITE_OK;
547 }
548 return rc;
549 }
550
551 /*
552 ** xColumn
553 */
unionColumn(sqlite3_vtab_cursor * cur,sqlite3_context * ctx,int i)554 static int unionColumn(
555 sqlite3_vtab_cursor *cur,
556 sqlite3_context *ctx,
557 int i
558 ){
559 UnionCsr *pCsr = (UnionCsr*)cur;
560 sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pStmt, i+1));
561 return SQLITE_OK;
562 }
563
564 /*
565 ** xRowid
566 */
unionRowid(sqlite3_vtab_cursor * cur,sqlite_int64 * pRowid)567 static int unionRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
568 UnionCsr *pCsr = (UnionCsr*)cur;
569 *pRowid = sqlite3_column_int64(pCsr->pStmt, 0);
570 return SQLITE_OK;
571 }
572
573 /*
574 ** xEof
575 */
unionEof(sqlite3_vtab_cursor * cur)576 static int unionEof(sqlite3_vtab_cursor *cur){
577 UnionCsr *pCsr = (UnionCsr*)cur;
578 return pCsr->pStmt==0;
579 }
580
581 /*
582 ** xFilter
583 */
unionFilter(sqlite3_vtab_cursor * pVtabCursor,int idxNum,const char * idxStr,int argc,sqlite3_value ** argv)584 static int unionFilter(
585 sqlite3_vtab_cursor *pVtabCursor,
586 int idxNum, const char *idxStr,
587 int argc, sqlite3_value **argv
588 ){
589 UnionTab *pTab = (UnionTab*)(pVtabCursor->pVtab);
590 UnionCsr *pCsr = (UnionCsr*)pVtabCursor;
591 int rc = SQLITE_OK;
592 int i;
593 char *zSql = 0;
594 int bZero = 0;
595
596 sqlite3_int64 iMin = SMALLEST_INT64;
597 sqlite3_int64 iMax = LARGEST_INT64;
598
599 assert( idxNum==0
600 || idxNum==SQLITE_INDEX_CONSTRAINT_EQ
601 || idxNum==SQLITE_INDEX_CONSTRAINT_LE
602 || idxNum==SQLITE_INDEX_CONSTRAINT_GE
603 || idxNum==SQLITE_INDEX_CONSTRAINT_LT
604 || idxNum==SQLITE_INDEX_CONSTRAINT_GT
605 || idxNum==(SQLITE_INDEX_CONSTRAINT_GE|SQLITE_INDEX_CONSTRAINT_LE)
606 );
607
608 (void)idxStr; /* Suppress harmless warning */
609
610 if( idxNum==SQLITE_INDEX_CONSTRAINT_EQ ){
611 assert( argc==1 );
612 iMin = iMax = sqlite3_value_int64(argv[0]);
613 }else{
614
615 if( idxNum & (SQLITE_INDEX_CONSTRAINT_LE|SQLITE_INDEX_CONSTRAINT_LT) ){
616 assert( argc>=1 );
617 iMax = sqlite3_value_int64(argv[0]);
618 if( idxNum & SQLITE_INDEX_CONSTRAINT_LT ){
619 if( iMax==SMALLEST_INT64 ){
620 bZero = 1;
621 }else{
622 iMax--;
623 }
624 }
625 }
626
627 if( idxNum & (SQLITE_INDEX_CONSTRAINT_GE|SQLITE_INDEX_CONSTRAINT_GT) ){
628 assert( argc>=1 );
629 iMin = sqlite3_value_int64(argv[argc-1]);
630 if( idxNum & SQLITE_INDEX_CONSTRAINT_GT ){
631 if( iMin==LARGEST_INT64 ){
632 bZero = 1;
633 }else{
634 iMin++;
635 }
636 }
637 }
638 }
639
640 sqlite3_finalize(pCsr->pStmt);
641 pCsr->pStmt = 0;
642 if( bZero ){
643 return SQLITE_OK;
644 }
645
646 for(i=0; i<pTab->nSrc; i++){
647 UnionSrc *pSrc = &pTab->aSrc[i];
648 if( iMin>pSrc->iMax || iMax<pSrc->iMin ){
649 continue;
650 }
651
652 zSql = sqlite3_mprintf("%z%sSELECT rowid, * FROM %s%q%s%Q"
653 , zSql
654 , (zSql ? " UNION ALL " : "")
655 , (pSrc->zDb ? "'" : "")
656 , (pSrc->zDb ? pSrc->zDb : "")
657 , (pSrc->zDb ? "'." : "")
658 , pSrc->zTab
659 );
660 if( zSql==0 ){
661 rc = SQLITE_NOMEM;
662 break;
663 }
664
665 if( iMin==iMax ){
666 zSql = sqlite3_mprintf("%z WHERE rowid=%lld", zSql, iMin);
667 }else{
668 const char *zWhere = "WHERE";
669 if( iMin!=SMALLEST_INT64 && iMin>pSrc->iMin ){
670 zSql = sqlite3_mprintf("%z WHERE rowid>=%lld", zSql, iMin);
671 zWhere = "AND";
672 }
673 if( iMax!=LARGEST_INT64 && iMax<pSrc->iMax ){
674 zSql = sqlite3_mprintf("%z %s rowid<=%lld", zSql, zWhere, iMax);
675 }
676 }
677 }
678
679
680 if( zSql==0 ) return rc;
681 pCsr->pStmt = unionPrepare(&rc, pTab->db, zSql, &pTab->base.zErrMsg);
682 sqlite3_free(zSql);
683 if( rc!=SQLITE_OK ) return rc;
684 return unionNext(pVtabCursor);
685 }
686
687 /*
688 ** xBestIndex.
689 **
690 ** This implementation searches for constraints on the rowid field. EQ,
691 ** LE, LT, GE and GT are handled.
692 **
693 ** If there is an EQ comparison, then idxNum is set to INDEX_CONSTRAINT_EQ.
694 ** In this case the only argument passed to xFilter is the rhs of the ==
695 ** operator.
696 **
697 ** Otherwise, if an LE or LT constraint is found, then the INDEX_CONSTRAINT_LE
698 ** or INDEX_CONSTRAINT_LT (but not both) bit is set in idxNum. The first
699 ** argument to xFilter is the rhs of the <= or < operator. Similarly, if
700 ** an GE or GT constraint is found, then the INDEX_CONSTRAINT_GE or
701 ** INDEX_CONSTRAINT_GT bit is set in idxNum. The rhs of the >= or > operator
702 ** is passed as either the first or second argument to xFilter, depending
703 ** on whether or not there is also a LT|LE constraint.
704 */
unionBestIndex(sqlite3_vtab * tab,sqlite3_index_info * pIdxInfo)705 static int unionBestIndex(
706 sqlite3_vtab *tab,
707 sqlite3_index_info *pIdxInfo
708 ){
709 UnionTab *pTab = (UnionTab*)tab;
710 int iEq = -1;
711 int iLt = -1;
712 int iGt = -1;
713 int i;
714
715 for(i=0; i<pIdxInfo->nConstraint; i++){
716 struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i];
717 if( p->usable && (p->iColumn<0 || p->iColumn==pTab->iPK) ){
718 switch( p->op ){
719 case SQLITE_INDEX_CONSTRAINT_EQ:
720 iEq = i;
721 break;
722 case SQLITE_INDEX_CONSTRAINT_LE:
723 case SQLITE_INDEX_CONSTRAINT_LT:
724 iLt = i;
725 break;
726 case SQLITE_INDEX_CONSTRAINT_GE:
727 case SQLITE_INDEX_CONSTRAINT_GT:
728 iGt = i;
729 break;
730 }
731 }
732 }
733
734 if( iEq>=0 ){
735 pIdxInfo->estimatedRows = 1;
736 pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;
737 pIdxInfo->estimatedCost = 3.0;
738 pIdxInfo->idxNum = SQLITE_INDEX_CONSTRAINT_EQ;
739 pIdxInfo->aConstraintUsage[iEq].argvIndex = 1;
740 pIdxInfo->aConstraintUsage[iEq].omit = 1;
741 }else{
742 int iCons = 1;
743 int idxNum = 0;
744 sqlite3_int64 nRow = 1000000;
745 if( iLt>=0 ){
746 nRow = nRow / 2;
747 pIdxInfo->aConstraintUsage[iLt].argvIndex = iCons++;
748 pIdxInfo->aConstraintUsage[iLt].omit = 1;
749 idxNum |= pIdxInfo->aConstraint[iLt].op;
750 }
751 if( iGt>=0 ){
752 nRow = nRow / 2;
753 pIdxInfo->aConstraintUsage[iGt].argvIndex = iCons++;
754 pIdxInfo->aConstraintUsage[iGt].omit = 1;
755 idxNum |= pIdxInfo->aConstraint[iGt].op;
756 }
757 pIdxInfo->estimatedRows = nRow;
758 pIdxInfo->estimatedCost = 3.0 * (double)nRow;
759 pIdxInfo->idxNum = idxNum;
760 }
761
762 return SQLITE_OK;
763 }
764
765 /*
766 ** Register the unionvtab virtual table module with database handle db.
767 */
createUnionVtab(sqlite3 * db)768 static int createUnionVtab(sqlite3 *db){
769 static sqlite3_module unionModule = {
770 0, /* iVersion */
771 unionConnect,
772 unionConnect,
773 unionBestIndex, /* xBestIndex - query planner */
774 unionDisconnect,
775 unionDisconnect,
776 unionOpen, /* xOpen - open a cursor */
777 unionClose, /* xClose - close a cursor */
778 unionFilter, /* xFilter - configure scan constraints */
779 unionNext, /* xNext - advance a cursor */
780 unionEof, /* xEof - check for end of scan */
781 unionColumn, /* xColumn - read data */
782 unionRowid, /* xRowid - read data */
783 0, /* xUpdate */
784 0, /* xBegin */
785 0, /* xSync */
786 0, /* xCommit */
787 0, /* xRollback */
788 0, /* xFindMethod */
789 0, /* xRename */
790 0, /* xSavepoint */
791 0, /* xRelease */
792 0 /* xRollbackTo */
793 };
794
795 return sqlite3_create_module(db, "unionvtab", &unionModule, 0);
796 }
797
798 #endif /* SQLITE_OMIT_VIRTUALTABLE */
799
800 #ifdef _WIN32
801 __declspec(dllexport)
802 #endif
sqlite3_unionvtab_init(sqlite3 * db,char ** pzErrMsg,const sqlite3_api_routines * pApi)803 int sqlite3_unionvtab_init(
804 sqlite3 *db,
805 char **pzErrMsg,
806 const sqlite3_api_routines *pApi
807 ){
808 int rc = SQLITE_OK;
809 SQLITE_EXTENSION_INIT2(pApi);
810 (void)pzErrMsg; /* Suppress harmless warning */
811 #ifndef SQLITE_OMIT_VIRTUALTABLE
812 rc = createUnionVtab(db);
813 #endif
814 return rc;
815 }
816