/ Check-in [9c3f1b9a]
Login

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

Overview
Comment:Enhance error detection and fix other issues in unionvtab code.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | union-vtab
Files: files | file ages | folders
SHA3-256:9c3f1b9a82e500e015deb0cc669fbb32e7f0cdc69f926ceff383ab946f8d8d18
User & Date: dan 2017-07-17 20:25:21
Context
2017-07-17
21:03
Add test cases to test/unionvtab.test. Closed-Leaf check-in: f2c4a584 user: dan tags: union-vtab
20:25
Enhance error detection and fix other issues in unionvtab code. check-in: 9c3f1b9a user: dan tags: union-vtab
2017-07-15
20:48
Add the "unionvtab" virtual table extension in ext/misc/unionvtab.c. check-in: 62a86aa6 user: dan tags: union-vtab
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/misc/unionvtab.c.

6
7
8
9
10
11
12



































13
14
15
16
17
18
19
20
21
22











23
24
25
26
27
28
29
..
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
...
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
...
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
...
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
...
268
269
270
271
272
273
274

275
276
277




278
279
280
281
282
283
284
285
286



287
288
289


290
291
292
293
294
295
296

297
298
299
300
301
302
303
...
305
306
307
308
309
310
311

312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
...
444
445
446
447
448
449
450

451
452
453
454
455









456
457
458
459
460
461

462
463
464


465


466



467
468
469


470




471
472
473
474
475
476



477
478
479
480
481
482
483
484
485
486
487
...
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
...
517
518
519
520
521
522
523















524
525
526
527
528
529
530
...
552
553
554
555
556
557
558

559
560
561
562
563
564
565
566

567
568
569
570
571

572
573
574
575
576
577
578
579
580



581
582
583
584
585
586
587
...
619
620
621
622
623
624
625
626
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
**



































*/
#if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_UNIONVTAB)

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#include <assert.h>
#include <string.h>

#ifndef SQLITE_OMIT_VIRTUALTABLE












typedef struct UnionCsr UnionCsr;
typedef struct UnionTab UnionTab;
typedef struct UnionSrc UnionSrc;

/*
** Each source table (row returned by the initialization query) is 
** represented by an instance of the following structure stored in the
................................................................................
struct UnionSrc {
  char *zDb;                      /* Database containing source table */
  char *zTab;                     /* Source table name */
  sqlite3_int64 iMin;             /* Minimum rowid */
  sqlite3_int64 iMax;             /* Maximum rowid */
};

/*
** Virtual table cursor type for union vtab.
*/
struct UnionCsr {
  sqlite3_vtab_cursor base;       /* Base class - must be first */
  sqlite3_stmt *pStmt;            /* SQL statement to run */
};

/*
** Virtual table  type for union vtab.
*/
struct UnionTab {
  sqlite3_vtab base;              /* Base class - must be first */
  sqlite3 *db;                    /* Database handle */
  int nSrc;                       /* Number of elements in the aSrc[] array */
  UnionSrc *aSrc;                 /* Array of source tables, sorted by rowid */
};









/*
** If *pRc is other than SQLITE_OK when this function is called, it
** always returns NULL. Otherwise, it attempts to allocate and return
** a pointer to nByte bytes of zeroed memory. If the memory allocation
** is attempted but fails, NULL is returned and *pRc is set to 
** SQLITE_NOMEM.
................................................................................
        z[iOut++] = z[iIn++];
      }
    }
    z[iOut] = '\0';
  }
}














static sqlite3_stmt *unionPrepare(
  int *pRc, 

  sqlite3 *db, 
  const char *zSql, 
  char **pzErr

){
  sqlite3_stmt *pRet = 0;
  if( *pRc==SQLITE_OK ){
    int rc = sqlite3_prepare_v2(db, zSql, -1, &pRet, 0);
    if( rc!=SQLITE_OK ){
      *pzErr = sqlite3_mprintf("sql error: %s", sqlite3_errmsg(db));
      *pRc = rc;
    }
  }
  return pRet;
}








static void unionReset(int *pRc, sqlite3_stmt *pStmt, char **pzErr){
  int rc = sqlite3_reset(pStmt);
  if( *pRc==SQLITE_OK ){
    *pRc = rc;
    if( rc && pzErr ){
      *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(sqlite3_db_handle(pStmt)));
    }
  }
}






static void unionFinalize(int *pRc, sqlite3_stmt *pStmt){
  int rc = sqlite3_finalize(pStmt);
  if( *pRc==SQLITE_OK ) *pRc = rc;
}

/*
** xDisconnect method.
................................................................................
    }
    sqlite3_free(pTab->aSrc);
    sqlite3_free(pTab);
  }
  return SQLITE_OK;
}


















static char *unionSourceToStr(
  int *pRc,


  UnionSrc *pSrc, 
  sqlite3_stmt *pStmt,
  char **pzErr

){
  char *zRet = 0;
  if( *pRc==SQLITE_OK ){
    sqlite3_bind_text(pStmt, 1, pSrc->zTab, -1, SQLITE_STATIC);
    sqlite3_bind_text(pStmt, 2, pSrc->zDb, -1, SQLITE_STATIC);
    if( SQLITE_ROW==sqlite3_step(pStmt) ){
      zRet = unionStrdup(pRc, (const char*)sqlite3_column_text(pStmt, 0));
    }


    unionReset(pRc, pStmt, pzErr);
    if( *pRc==SQLITE_OK && zRet==0 ){


      *pRc = SQLITE_ERROR;
      *pzErr = sqlite3_mprintf("no such table: %s%s%s",
          (pSrc->zDb ? pSrc->zDb : ""),
          (pSrc->zDb ? "." : ""),
          pSrc->zTab
      );
    }
  }












  return zRet;
}









static int unionSourceCheck(UnionTab *pTab, char **pzErr){
  const char *zSql = 
      "SELECT group_concat(quote(name) || '.' || quote(type)) "
      "FROM pragma_table_info(?, ?)";
  int rc = SQLITE_OK;

  if( pTab->nSrc==0 ){
................................................................................
  }else{
    sqlite3_stmt *pStmt = 0;
    char *z0 = 0;
    int i;

    pStmt = unionPrepare(&rc, pTab->db, zSql, pzErr);
    if( rc==SQLITE_OK ){
      z0 = unionSourceToStr(&rc, &pTab->aSrc[0], pStmt, pzErr);
    }
    for(i=1; i<pTab->nSrc; i++){
      char *z = unionSourceToStr(&rc, &pTab->aSrc[i], pStmt, pzErr);
      if( rc==SQLITE_OK && sqlite3_stricmp(z, z0) ){
        *pzErr = sqlite3_mprintf("source table schema mismatch");
        rc = SQLITE_ERROR;
      }
      sqlite3_free(z);
    }

................................................................................
    rc = SQLITE_ERROR;
  }else if( argc!=4 ){
    *pzErr = sqlite3_mprintf("wrong number of arguments for unionvtab");
    rc = SQLITE_ERROR;
  }else{
    int nAlloc = 0;               /* Allocated size of pTab->aSrc[] */
    sqlite3_stmt *pStmt = 0;      /* Argument statement */

    char *zSql1 = unionStrdup(&rc, argv[3]);
    char *zSql2 = 0;





    if( zSql1 ){
      unionDequote(zSql1);
      zSql2 = sqlite3_mprintf("SELECT * FROM (%s) ORDER BY 3", zSql1);
      sqlite3_free(zSql1);
      zSql1 = 0;
    }
    if( zSql2==0 ){
      rc = SQLITE_NOMEM;
    }



    pTab = unionMalloc(&rc, sizeof(UnionTab));
    pStmt = unionPrepare(&rc, db, zSql2, pzErr);



    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
      const char *zDb = (const char*)sqlite3_column_text(pStmt, 0);
      const char *zTab = (const char*)sqlite3_column_text(pStmt, 1);
      sqlite3_int64 iMin = sqlite3_column_int64(pStmt, 2);
      sqlite3_int64 iMax = sqlite3_column_int64(pStmt, 3);
      UnionSrc *pSrc;


      if( nAlloc<=pTab->nSrc ){
        int nNew = nAlloc ? nAlloc*2 : 8;
        UnionSrc *aNew = (UnionSrc*)sqlite3_realloc(
            pTab->aSrc, nNew*sizeof(UnionSrc)
        );
        if( aNew==0 ){
          rc = SQLITE_NOMEM;
................................................................................
        }else{
          memset(&aNew[pTab->nSrc], 0, (nNew-pTab->nSrc)*sizeof(UnionSrc));
          pTab->aSrc = aNew;
          nAlloc = nNew;
        }
      }


      if( iMax<iMin || (pTab->nSrc>0 && iMin<=pTab->aSrc[pTab->nSrc-1].iMax) ){
        *pzErr = sqlite3_mprintf("rowid range mismatch error");
        rc = SQLITE_ERROR;
      }

      pSrc = &pTab->aSrc[pTab->nSrc++];
      pSrc->zDb = unionStrdup(&rc, zDb);
      pSrc->zTab = unionStrdup(&rc, zTab);
      pSrc->iMin = iMin;
      pSrc->iMax = iMax;
    }
    unionFinalize(&rc, pStmt);
    pStmt = 0;
    sqlite3_free(zSql1);
    sqlite3_free(zSql2);
    zSql1 = 0;
    zSql2 = 0;

    /* Verify that all source tables exist and have compatible schemas. */
    if( rc==SQLITE_OK ){
      pTab->db = db;
      rc = unionSourceCheck(pTab, pzErr);
    }

    /* Compose a CREATE TABLE statement and pass it to declare_vtab() */
    if( rc==SQLITE_OK ){
      zSql1 = sqlite3_mprintf("SELECT "
          "'CREATE TABLE xyz('"
          "    || group_concat(quote(name) || ' ' || type, ', ')"
          "    || ')'"
          "FROM pragma_table_info(%Q, ?)", 
          pTab->aSrc[0].zTab
      );
      if( zSql1==0 ) rc = SQLITE_NOMEM;
    }
    pStmt = unionPrepare(&rc, db, zSql1, pzErr);
    if( rc==SQLITE_OK ){
      sqlite3_bind_text(pStmt, 1, pTab->aSrc[0].zDb, -1, SQLITE_STATIC);
      if( SQLITE_ROW==sqlite3_step(pStmt) ){
        const char *zDecl = (const char*)sqlite3_column_text(pStmt, 0);
        rc = sqlite3_declare_vtab(db, zDecl);
      }
    }

    unionFinalize(&rc, pStmt);
    sqlite3_free(zSql1);
  }

  if( rc!=SQLITE_OK ){
    unionDisconnect((sqlite3_vtab*)pTab);
    pTab = 0;
  }

................................................................................
  int argc, sqlite3_value **argv
){
  UnionTab *pTab = (UnionTab*)(pVtabCursor->pVtab);
  UnionCsr *pCsr = (UnionCsr*)pVtabCursor;
  int rc = SQLITE_OK;
  int i;
  char *zSql = 0;


  int bMinValid = 0;
  int bMaxValid = 0;
  sqlite3_int64 iMin;
  sqlite3_int64 iMax;










  if( idxNum==SQLITE_INDEX_CONSTRAINT_EQ ){
    assert( argc==1 );
    iMin = iMax = sqlite3_value_int64(argv[0]);
    bMinValid = bMaxValid = 1;
  }else{

    if( idxNum & SQLITE_INDEX_CONSTRAINT_LE ){
      assert( argc>=1 );
      iMax = sqlite3_value_int64(argv[0]);


      bMaxValid = 1;


    }



    if( idxNum & SQLITE_INDEX_CONSTRAINT_GE ){
      assert( argc>=1 );
      iMin = sqlite3_value_int64(argv[argc-1]);


      bMinValid = 1;




    }
  }


  sqlite3_finalize(pCsr->pStmt);
  pCsr->pStmt = 0;




  for(i=0; i<pTab->nSrc; i++){
    UnionSrc *pSrc = &pTab->aSrc[i];
    if( (bMinValid && iMin>pSrc->iMax) || (bMaxValid && iMax<pSrc->iMin) ){
      continue;
    }

    zSql = sqlite3_mprintf("%z%sSELECT rowid, * FROM %s%q%s%Q"
        , zSql
        , (zSql ? " UNION ALL " : "")
        , (pSrc->zDb ? "'" : "")
................................................................................
    );
    if( zSql==0 ){
      rc = SQLITE_NOMEM;
      break;
    }

    if( zSql ){
      if( bMinValid && bMaxValid && iMin==iMax ){
        zSql = sqlite3_mprintf("%z WHERE rowid=%lld", zSql, iMin);
      }else{
        const char *zWhere = "WHERE";
        if( bMinValid && iMin>pSrc->iMin ){
          zSql = sqlite3_mprintf("%z WHERE rowid>=%lld", zSql, iMin);
          zWhere = "AND";
        }
        if( bMaxValid && iMax<pSrc->iMax ){
          zSql = sqlite3_mprintf("%z %s rowid<=%lld", zSql, zWhere, iMax);
        }
      }
    }
  }

  if( rc==SQLITE_OK ){
................................................................................

  if( rc!=SQLITE_OK ) return rc;
  return unionNext(pVtabCursor);
}

/*
** xBestIndex.















*/
static int unionBestIndex(
  sqlite3_vtab *tab,
  sqlite3_index_info *pIdxInfo
){
  int iEq = -1;
  int iLt = -1;
................................................................................

  if( iEq>=0 ){
    pIdxInfo->estimatedRows = 1;
    pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;
    pIdxInfo->estimatedCost = 3.0;
    pIdxInfo->idxNum = SQLITE_INDEX_CONSTRAINT_EQ;
    pIdxInfo->aConstraintUsage[iEq].argvIndex = 1;

  }else{
    int iCons = 1;
    int idxNum = 0;
    sqlite3_int64 nRow = 1000000;
    if( iLt>=0 ){
      nRow = nRow / 2;
      pIdxInfo->aConstraintUsage[iLt].argvIndex = iCons++;
      idxNum |= SQLITE_INDEX_CONSTRAINT_LE;

    }
    if( iGt>=0 ){
      nRow = nRow / 2;
      pIdxInfo->aConstraintUsage[iGt].argvIndex = iCons++;
      idxNum |= SQLITE_INDEX_CONSTRAINT_GE;

    }
    pIdxInfo->estimatedRows = nRow;
    pIdxInfo->estimatedCost = 3.0 * (double)nRow;
    pIdxInfo->idxNum = idxNum;
  }

  return SQLITE_OK;
}




static int createUnionVtab(sqlite3 *db){
  static sqlite3_module unionModule = {
    0,                            /* iVersion */
    unionConnect,
    unionConnect,
    unionBestIndex,               /* xBestIndex - query planner */
    unionDisconnect, 
................................................................................
  SQLITE_EXTENSION_INIT2(pApi);
#ifndef SQLITE_OMIT_VIRTUALTABLE
  rc = createUnionVtab(db);
#endif
  return rc;
}

#endif /* !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_UNIONVTAB) */







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

<








>
>
>
>
>
>
>
>
>
>
>







 







<
<
<
<
<
<
<
<









>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>

<
>
|
|
<
>












>
>
>
>
>
>
>










>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

<
>
>
|

<
>



|
|
<
<
|
>
>
|
|
>
>
|
|





|
>
>
>
>
>
>
>
>
>
>
>
>



>
>
>
>
>
>
>
>







 







|


|







 







>
|
<

>
>
>
>
|
|
|
|
|

|


>
>
>

<

>
>







>







 







>













|
|
|
|









|






|

|









|







 







>

<
<
|
|
>
>
>
>
>
>
>
>
>




<

>
|


>
>
|
>
>
|
>
>
>
|


>
>
|
>
>
>
>



<


>
>
>



|







 







|



|



|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







|
>




|
>









>
>
>







 







<
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
..
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
...
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
239
240
...
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273

274
275
276
277

278
279
280
281
282
283


284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
...
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
...
378
379
380
381
382
383
384
385
386

387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404

405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
...
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
...
564
565
566
567
568
569
570
571
572


573
574
575
576
577
578
579
580
581
582
583
584
585
586
587

588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614

615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
...
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
...
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
...
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
...
783
784
785
786
787
788
789

**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
**
** This file contains the implementation of the "unionvtab" virtual
** table. This module provides read-only access to multiple tables, 
** possibly in multiple database files, via a single database object.
** The source tables must have the following characteristics:
**
**   * They must all be rowid tables (not VIRTUAL or WITHOUT ROWID
**     tables or views).
**
**   * Each table must have the same set of columns, declared in
**     the same order and with the same declared types.
**
**   * The tables must not feature a user-defined column named "_rowid_".
**
**   * Each table must contain a distinct range of rowid values.
**
** A "unionvtab" virtual table is created as follows:
**
**   CREATE VIRTUAL TABLE <name> USING unionvtab(<sql statement>);
**
** The implementation evalutes <sql statement> whenever a unionvtab virtual
** table is created or opened. It should return one row for each source
** database table. The four columns required of each row are:
**
**   1. The name of the database containing the table ("main" or "temp" or
**      the name of an attached database). Or NULL to indicate that all
**      databases should be searched for the table in the usual fashion.
**
**   2. The name of the database table.
**
**   3. The smallest rowid in the range of rowids that may be stored in the
**      database table (an integer).
**
**   4. The largest rowid in the range of rowids that may be stored in the
**      database table (an integer).
**
*/


#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#include <assert.h>
#include <string.h>

#ifndef SQLITE_OMIT_VIRTUALTABLE

/*
** Largest and smallest possible 64-bit signed integers. These macros
** copied from sqliteInt.h.
*/
#ifndef LARGEST_INT64
# define LARGEST_INT64  (0xffffffff|(((sqlite3_int64)0x7fffffff)<<32))
#endif
#ifndef SMALLEST_INT64
# define SMALLEST_INT64 (((sqlite3_int64)-1) - LARGEST_INT64)
#endif

typedef struct UnionCsr UnionCsr;
typedef struct UnionTab UnionTab;
typedef struct UnionSrc UnionSrc;

/*
** Each source table (row returned by the initialization query) is 
** represented by an instance of the following structure stored in the
................................................................................
struct UnionSrc {
  char *zDb;                      /* Database containing source table */
  char *zTab;                     /* Source table name */
  sqlite3_int64 iMin;             /* Minimum rowid */
  sqlite3_int64 iMax;             /* Maximum rowid */
};









/*
** Virtual table  type for union vtab.
*/
struct UnionTab {
  sqlite3_vtab base;              /* Base class - must be first */
  sqlite3 *db;                    /* Database handle */
  int nSrc;                       /* Number of elements in the aSrc[] array */
  UnionSrc *aSrc;                 /* Array of source tables, sorted by rowid */
};

/*
** Virtual table cursor type for union vtab.
*/
struct UnionCsr {
  sqlite3_vtab_cursor base;       /* Base class - must be first */
  sqlite3_stmt *pStmt;            /* SQL statement to run */
};

/*
** If *pRc is other than SQLITE_OK when this function is called, it
** always returns NULL. Otherwise, it attempts to allocate and return
** a pointer to nByte bytes of zeroed memory. If the memory allocation
** is attempted but fails, NULL is returned and *pRc is set to 
** SQLITE_NOMEM.
................................................................................
        z[iOut++] = z[iIn++];
      }
    }
    z[iOut] = '\0';
  }
}

/*
** This function is a no-op if *pRc is set to other than SQLITE_OK when it
** is called. NULL is returned in this case.
**
** Otherwise, the SQL statement passed as the third argument is prepared
** against the database handle passed as the second. If the statement is
** successfully prepared, a pointer to the new statement handle is 
** returned. It is the responsibility of the caller to eventually free the
** statement by calling sqlite3_finalize(). Alternatively, if statement
** compilation fails, NULL is returned, *pRc is set to an SQLite error
** code and *pzErr may be set to an error message buffer allocated by
** sqlite3_malloc().
*/
static sqlite3_stmt *unionPrepare(

  int *pRc,                       /* IN/OUT: Error code */
  sqlite3 *db,                    /* Database handle */
  const char *zSql,               /* SQL statement to prepare */

  char **pzErr                    /* OUT: Error message */
){
  sqlite3_stmt *pRet = 0;
  if( *pRc==SQLITE_OK ){
    int rc = sqlite3_prepare_v2(db, zSql, -1, &pRet, 0);
    if( rc!=SQLITE_OK ){
      *pzErr = sqlite3_mprintf("sql error: %s", sqlite3_errmsg(db));
      *pRc = rc;
    }
  }
  return pRet;
}

/*
** Call sqlite3_reset() on SQL statement pStmt. If *pRc is set to 
** SQLITE_OK when this function is called, then it is set to the
** value returned by sqlite3_reset() before this function exits.
** In this case, *pzErr may be set to point to an error message
** buffer allocated by sqlite3_malloc().
*/
static void unionReset(int *pRc, sqlite3_stmt *pStmt, char **pzErr){
  int rc = sqlite3_reset(pStmt);
  if( *pRc==SQLITE_OK ){
    *pRc = rc;
    if( rc && pzErr ){
      *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(sqlite3_db_handle(pStmt)));
    }
  }
}

/*
** Call sqlite3_finalize() on SQL statement pStmt. If *pRc is set to 
** SQLITE_OK when this function is called, then it is set to the
** value returned by sqlite3_finalize() before this function exits.
*/
static void unionFinalize(int *pRc, sqlite3_stmt *pStmt){
  int rc = sqlite3_finalize(pStmt);
  if( *pRc==SQLITE_OK ) *pRc = rc;
}

/*
** xDisconnect method.
................................................................................
    }
    sqlite3_free(pTab->aSrc);
    sqlite3_free(pTab);
  }
  return SQLITE_OK;
}

/*
** This function is a no-op if *pRc is other than SQLITE_OK when it is
** called. In this case it returns NULL.
**
** Otherwise, this function checks that the source table passed as the
** second argument (a) exists, (b) is not a view and (c) has a column 
** named "_rowid_" of type "integer" that is the primary key.
** If this is not the case, *pRc is set to SQLITE_ERROR and NULL is
** returned.
**
** Finally, if the source table passes the checks above, a nul-terminated
** string describing the column names and types belonging to the source
** table is returned. Tables with the same set of column names and types 
** cause this function to return identical strings. Is is the responsibility
** of the caller to free the returned string using sqlite3_free() when
** it is no longer required.
*/
static char *unionSourceToStr(

  int *pRc,                       /* IN/OUT: Error code */
  sqlite3 *db,                    /* Database handle */
  UnionSrc *pSrc,                 /* Source table to test */
  sqlite3_stmt *pStmt,

  char **pzErr                    /* OUT: Error message */
){
  char *zRet = 0;
  if( *pRc==SQLITE_OK ){
    int bPk = 0;
    const char *zType = 0;



    int rc = sqlite3_table_column_metadata(
        db, pSrc->zDb, pSrc->zTab, "_rowid_", &zType, 0, 0, &bPk, 0
    );
    if( rc==SQLITE_ERROR 
     || (rc==SQLITE_OK && (!bPk || sqlite3_stricmp("integer", zType)))
    ){
      rc = SQLITE_ERROR;
      *pzErr = sqlite3_mprintf("no such rowid table: %s%s%s",
          (pSrc->zDb ? pSrc->zDb : ""),
          (pSrc->zDb ? "." : ""),
          pSrc->zTab
      );
    }

    if( rc==SQLITE_OK ){
      sqlite3_bind_text(pStmt, 1, pSrc->zTab, -1, SQLITE_STATIC);
      sqlite3_bind_text(pStmt, 2, pSrc->zDb, -1, SQLITE_STATIC);
      if( SQLITE_ROW==sqlite3_step(pStmt) ){
        zRet = unionStrdup(&rc, (const char*)sqlite3_column_text(pStmt, 0));
      }
      unionReset(&rc, pStmt, pzErr);
    }

    *pRc = rc;
  }

  return zRet;
}

/*
** Check that all configured source tables exist and have the same column
** names and datatypes. If this is not the case, or if some other error
** occurs, return an SQLite error code. In this case *pzErr may be set
** to point to an error message buffer allocated by sqlite3_mprintf().
** Or, if no problems regarding the source tables are detected and no
** other error occurs, SQLITE_OK is returned.
*/
static int unionSourceCheck(UnionTab *pTab, char **pzErr){
  const char *zSql = 
      "SELECT group_concat(quote(name) || '.' || quote(type)) "
      "FROM pragma_table_info(?, ?)";
  int rc = SQLITE_OK;

  if( pTab->nSrc==0 ){
................................................................................
  }else{
    sqlite3_stmt *pStmt = 0;
    char *z0 = 0;
    int i;

    pStmt = unionPrepare(&rc, pTab->db, zSql, pzErr);
    if( rc==SQLITE_OK ){
      z0 = unionSourceToStr(&rc, pTab->db, &pTab->aSrc[0], pStmt, pzErr);
    }
    for(i=1; i<pTab->nSrc; i++){
      char *z = unionSourceToStr(&rc, pTab->db, &pTab->aSrc[i], pStmt, pzErr);
      if( rc==SQLITE_OK && sqlite3_stricmp(z, z0) ){
        *pzErr = sqlite3_mprintf("source table schema mismatch");
        rc = SQLITE_ERROR;
      }
      sqlite3_free(z);
    }

................................................................................
    rc = SQLITE_ERROR;
  }else if( argc!=4 ){
    *pzErr = sqlite3_mprintf("wrong number of arguments for unionvtab");
    rc = SQLITE_ERROR;
  }else{
    int nAlloc = 0;               /* Allocated size of pTab->aSrc[] */
    sqlite3_stmt *pStmt = 0;      /* Argument statement */
    char *zSql = 0;               /* SQL statement */
    char *zArg = unionStrdup(&rc, argv[3]);      /* Copy of argument to CVT */


    /* Prepare the SQL statement. Instead of executing it directly, sort
    ** the results by the "minimum rowid" field. This makes it easier to
    ** check that there are no rowid range overlaps between source tables 
    ** and that the UnionTab.aSrc[] array is always sorted by rowid.  */
    if( zArg ){
      unionDequote(zArg);
      zSql = sqlite3_mprintf("SELECT * FROM (%s) ORDER BY 3", zArg);
      sqlite3_free(zArg);
      zArg = 0;
    }
    if( zSql==0 ){
      rc = SQLITE_NOMEM;
    }
    pStmt = unionPrepare(&rc, db, zSql, pzErr);

    /* Allocate the UnionTab structure */
    pTab = unionMalloc(&rc, sizeof(UnionTab));


    /* Iterate through the rows returned by the SQL statement specified
    ** as an argument to the CREATE VIRTUAL TABLE statement. */
    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
      const char *zDb = (const char*)sqlite3_column_text(pStmt, 0);
      const char *zTab = (const char*)sqlite3_column_text(pStmt, 1);
      sqlite3_int64 iMin = sqlite3_column_int64(pStmt, 2);
      sqlite3_int64 iMax = sqlite3_column_int64(pStmt, 3);
      UnionSrc *pSrc;

      /* Grow the pTab->aSrc[] array if required. */
      if( nAlloc<=pTab->nSrc ){
        int nNew = nAlloc ? nAlloc*2 : 8;
        UnionSrc *aNew = (UnionSrc*)sqlite3_realloc(
            pTab->aSrc, nNew*sizeof(UnionSrc)
        );
        if( aNew==0 ){
          rc = SQLITE_NOMEM;
................................................................................
        }else{
          memset(&aNew[pTab->nSrc], 0, (nNew-pTab->nSrc)*sizeof(UnionSrc));
          pTab->aSrc = aNew;
          nAlloc = nNew;
        }
      }

      /* Check for problems with the specified range of rowids */
      if( iMax<iMin || (pTab->nSrc>0 && iMin<=pTab->aSrc[pTab->nSrc-1].iMax) ){
        *pzErr = sqlite3_mprintf("rowid range mismatch error");
        rc = SQLITE_ERROR;
      }

      pSrc = &pTab->aSrc[pTab->nSrc++];
      pSrc->zDb = unionStrdup(&rc, zDb);
      pSrc->zTab = unionStrdup(&rc, zTab);
      pSrc->iMin = iMin;
      pSrc->iMax = iMax;
    }
    unionFinalize(&rc, pStmt);
    pStmt = 0;
    sqlite3_free(zArg);
    sqlite3_free(zSql);
    zArg = 0;
    zSql = 0;

    /* Verify that all source tables exist and have compatible schemas. */
    if( rc==SQLITE_OK ){
      pTab->db = db;
      rc = unionSourceCheck(pTab, pzErr);
    }

    /* Compose a CREATE TABLE statement and pass it to declare_vtab() */
    if( rc==SQLITE_OK ){
      zSql = sqlite3_mprintf("SELECT "
          "'CREATE TABLE xyz('"
          "    || group_concat(quote(name) || ' ' || type, ', ')"
          "    || ')'"
          "FROM pragma_table_info(%Q, ?)", 
          pTab->aSrc[0].zTab
      );
      if( zSql==0 ) rc = SQLITE_NOMEM;
    }
    pStmt = unionPrepare(&rc, db, zSql, pzErr);
    if( rc==SQLITE_OK ){
      sqlite3_bind_text(pStmt, 1, pTab->aSrc[0].zDb, -1, SQLITE_STATIC);
      if( SQLITE_ROW==sqlite3_step(pStmt) ){
        const char *zDecl = (const char*)sqlite3_column_text(pStmt, 0);
        rc = sqlite3_declare_vtab(db, zDecl);
      }
    }

    unionFinalize(&rc, pStmt);
    sqlite3_free(zSql);
  }

  if( rc!=SQLITE_OK ){
    unionDisconnect((sqlite3_vtab*)pTab);
    pTab = 0;
  }

................................................................................
  int argc, sqlite3_value **argv
){
  UnionTab *pTab = (UnionTab*)(pVtabCursor->pVtab);
  UnionCsr *pCsr = (UnionCsr*)pVtabCursor;
  int rc = SQLITE_OK;
  int i;
  char *zSql = 0;
  int bZero = 0;



  sqlite3_int64 iMin = SMALLEST_INT64;
  sqlite3_int64 iMax = LARGEST_INT64;

  assert( idxNum==0 
       || idxNum==SQLITE_INDEX_CONSTRAINT_EQ
       || idxNum==SQLITE_INDEX_CONSTRAINT_LE
       || idxNum==SQLITE_INDEX_CONSTRAINT_GE
       || idxNum==SQLITE_INDEX_CONSTRAINT_LT
       || idxNum==SQLITE_INDEX_CONSTRAINT_GT
       || idxNum==(SQLITE_INDEX_CONSTRAINT_GE|SQLITE_INDEX_CONSTRAINT_LE)
  );

  if( idxNum==SQLITE_INDEX_CONSTRAINT_EQ ){
    assert( argc==1 );
    iMin = iMax = sqlite3_value_int64(argv[0]);

  }else{

    if( idxNum & (SQLITE_INDEX_CONSTRAINT_LE|SQLITE_INDEX_CONSTRAINT_LT) ){
      assert( argc>=1 );
      iMax = sqlite3_value_int64(argv[0]);
      if( idxNum & SQLITE_INDEX_CONSTRAINT_LT ){
        if( iMax==SMALLEST_INT64 ){
          bZero = 1;
        }else{
          iMax--;
        }
      }
    }

    if( idxNum & (SQLITE_INDEX_CONSTRAINT_GE|SQLITE_INDEX_CONSTRAINT_GT) ){
      assert( argc>=1 );
      iMin = sqlite3_value_int64(argv[argc-1]);
      if( idxNum & SQLITE_INDEX_CONSTRAINT_GT ){
        if( iMin==LARGEST_INT64 ){
          bZero = 1;
        }else{
          iMin++;
        }
      }
    }
  }


  sqlite3_finalize(pCsr->pStmt);
  pCsr->pStmt = 0;
  if( bZero ){
    return SQLITE_OK;
  }

  for(i=0; i<pTab->nSrc; i++){
    UnionSrc *pSrc = &pTab->aSrc[i];
    if( iMin>pSrc->iMax || iMax<pSrc->iMin ){
      continue;
    }

    zSql = sqlite3_mprintf("%z%sSELECT rowid, * FROM %s%q%s%Q"
        , zSql
        , (zSql ? " UNION ALL " : "")
        , (pSrc->zDb ? "'" : "")
................................................................................
    );
    if( zSql==0 ){
      rc = SQLITE_NOMEM;
      break;
    }

    if( zSql ){
      if( iMin==iMax ){
        zSql = sqlite3_mprintf("%z WHERE rowid=%lld", zSql, iMin);
      }else{
        const char *zWhere = "WHERE";
        if( iMin!=SMALLEST_INT64 && iMin>pSrc->iMin ){
          zSql = sqlite3_mprintf("%z WHERE rowid>=%lld", zSql, iMin);
          zWhere = "AND";
        }
        if( iMax!=LARGEST_INT64 && iMax<pSrc->iMax ){
          zSql = sqlite3_mprintf("%z %s rowid<=%lld", zSql, zWhere, iMax);
        }
      }
    }
  }

  if( rc==SQLITE_OK ){
................................................................................

  if( rc!=SQLITE_OK ) return rc;
  return unionNext(pVtabCursor);
}

/*
** xBestIndex.
**
** This implementation searches for constraints on the rowid field. EQ, 
** LE, LT, GE and GT are handled.
**
** If there is an EQ comparison, then idxNum is set to INDEX_CONSTRAINT_EQ.
** In this case the only argument passed to xFilter is the rhs of the ==
** operator.
**
** Otherwise, if an LE or LT constraint is found, then the INDEX_CONSTRAINT_LE
** or INDEX_CONSTRAINT_LT (but not both) bit is set in idxNum. The first
** argument to xFilter is the rhs of the <= or < operator.  Similarly, if 
** an GE or GT constraint is found, then the INDEX_CONSTRAINT_GE or
** INDEX_CONSTRAINT_GT bit is set in idxNum. The rhs of the >= or > operator
** is passed as either the first or second argument to xFilter, depending
** on whether or not there is also a LT|LE constraint.
*/
static int unionBestIndex(
  sqlite3_vtab *tab,
  sqlite3_index_info *pIdxInfo
){
  int iEq = -1;
  int iLt = -1;
................................................................................

  if( iEq>=0 ){
    pIdxInfo->estimatedRows = 1;
    pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;
    pIdxInfo->estimatedCost = 3.0;
    pIdxInfo->idxNum = SQLITE_INDEX_CONSTRAINT_EQ;
    pIdxInfo->aConstraintUsage[iEq].argvIndex = 1;
    pIdxInfo->aConstraintUsage[iEq].omit = 1;
  }else{
    int iCons = 1;
    int idxNum = 0;
    sqlite3_int64 nRow = 1000000;
    if( iLt>=0 ){
      nRow = nRow / 2;
      pIdxInfo->aConstraintUsage[iLt].argvIndex = iCons++;
      pIdxInfo->aConstraintUsage[iLt].omit = 1;
      idxNum |= pIdxInfo->aConstraint[iLt].op;
    }
    if( iGt>=0 ){
      nRow = nRow / 2;
      pIdxInfo->aConstraintUsage[iGt].argvIndex = iCons++;
      pIdxInfo->aConstraintUsage[iGt].omit = 1;
      idxNum |= pIdxInfo->aConstraint[iGt].op;
    }
    pIdxInfo->estimatedRows = nRow;
    pIdxInfo->estimatedCost = 3.0 * (double)nRow;
    pIdxInfo->idxNum = idxNum;
  }

  return SQLITE_OK;
}

/*
** Register the unionvtab virtual table module with database handle db.
*/
static int createUnionVtab(sqlite3 *db){
  static sqlite3_module unionModule = {
    0,                            /* iVersion */
    unionConnect,
    unionConnect,
    unionBestIndex,               /* xBestIndex - query planner */
    unionDisconnect, 
................................................................................
  SQLITE_EXTENSION_INIT2(pApi);
#ifndef SQLITE_OMIT_VIRTUALTABLE
  rc = createUnionVtab(db);
#endif
  return rc;
}


Changes to test/unionvtab.test.

94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
} {1 {unionvtab tables must be created in TEMP schema}}
do_catchsql_test 2.1.3 {
  CREATE VIRTUAL TABLE aux.u1 USING unionvtab("VALUES('', 't1', 1, 100)");
} {1 {unionvtab tables must be created in TEMP schema}}

do_catchsql_test 2.2.1 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 't555', 1, 100)");
} {1 {no such table: t555}}
do_catchsql_test 2.2.2 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('aux', 't555', 1, 100)");
} {1 {no such table: aux.t555}}
do_catchsql_test 2.2.3 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('xua', 't555', 1, 100)");
} {1 {unknown database 'xua'}}

do_execsql_test 2.4.0 {
  CREATE TABLE x1(a BLOB, b);
  CREATE TABLE x2(a BLOB, b);
  CREATE TEMP TABLE x3(a BLOB, b);

  CREATE TABLE aux.y1(one, two, three INTEGER PRIMARY KEY);







|


|


|







94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
} {1 {unionvtab tables must be created in TEMP schema}}
do_catchsql_test 2.1.3 {
  CREATE VIRTUAL TABLE aux.u1 USING unionvtab("VALUES('', 't1', 1, 100)");
} {1 {unionvtab tables must be created in TEMP schema}}

do_catchsql_test 2.2.1 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 't555', 1, 100)");
} {1 {no such rowid table: t555}}
do_catchsql_test 2.2.2 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('aux', 't555', 1, 100)");
} {1 {no such rowid table: aux.t555}}
do_catchsql_test 2.2.3 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('xua', 't555', 1, 100)");
} {1 {no such rowid table: xua.t555}}

do_execsql_test 2.4.0 {
  CREATE TABLE x1(a BLOB, b);
  CREATE TABLE x2(a BLOB, b);
  CREATE TEMP TABLE x3(a BLOB, b);

  CREATE TABLE aux.y1(one, two, three INTEGER PRIMARY KEY);