Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enhance the "swarmvtab" extension. See header comments in ext/misc/unionvtab.c for details. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
01c173651ab22b7b0c139eded6f2ad85 |
| User & Date: | dan 2017-12-15 20:21:17 |
Context
|
2017-12-16
| ||
| 04:37 | Add unnecessary initializations to some local variables in the rtree module to suppress false-positive compiler warnings coming out of MSVC. check-in: 64487d65 user: drh tags: trunk | |
|
2017-12-15
| ||
| 20:21 | Enhance the "swarmvtab" extension. See header comments in ext/misc/unionvtab.c for details. check-in: 01c17365 user: dan tags: trunk | |
| 12:22 | In the LEMON parser generator, provide reduce actions with access to the lookahead token. check-in: 42af190f user: drh tags: trunk | |
Changes
Changes to ext/misc/unionvtab.c.
51 51 ** 3. The smallest rowid in the range of rowids that may be stored in the 52 52 ** database table (an integer). 53 53 ** 54 54 ** 4. The largest rowid in the range of rowids that may be stored in the 55 55 ** database table (an integer). 56 56 ** 57 57 ** SWARMVTAB 58 +** 59 +** LEGACY SYNTAX: 58 60 ** 59 61 ** A "swarmvtab" virtual table is created similarly to a unionvtab table: 60 62 ** 61 63 ** CREATE VIRTUAL TABLE <name> 62 64 ** USING swarmvtab(<sql-statement>, <callback>); 63 65 ** 64 66 ** The difference is that for a swarmvtab table, the first column returned 65 67 ** by the <sql statement> must return a path or URI that can be used to open 66 68 ** the database file containing the source table. The <callback> option 67 69 ** is optional. If included, it is the name of an application-defined 68 70 ** SQL function that is invoked with the URI of the file, if the file 69 -** does not already exist on disk. 71 +** does not already exist on disk when required by swarmvtab. 72 +** 73 +** NEW SYNTAX: 74 +** 75 +** Using the new syntax, a swarmvtab table is created with: 76 +** 77 +** CREATE VIRTUAL TABLE <name> USING swarmvtab( 78 +** <sql-statement> [, <options>] 79 +** ); 80 +** 81 +** where valid <options> are: 82 +** 83 +** missing=<udf-function-name> 84 +** openclose=<udf-function-name> 85 +** maxopen=<integer> 86 +** <sql-parameter>=<text-value> 87 +** 88 +** The <sql-statement> must return the same 4 columns as for a swarmvtab 89 +** table in legacy mode. However, it may also return a 5th column - the 90 +** "context" column. The text value returned in this column is not used 91 +** at all by the swarmvtab implementation, except that it is passed as 92 +** an additional argument to the two UDF functions that may be invoked 93 +** (see below). 94 +** 95 +** The "missing" option, if present, specifies the name of an SQL UDF 96 +** function to be invoked if a database file is not already present on 97 +** disk when required by swarmvtab. If the <sql-statement> did not provide 98 +** a context column, it is invoked as: 99 +** 100 +** SELECT <missing-udf>(<database filename/uri>); 101 +** 102 +** Or, if there was a context column: 103 +** 104 +** SELECT <missing-udf>(<database filename/uri>, <context>); 105 +** 106 +** The "openclose" option may also specify a UDF function. This function 107 +** is invoked right before swarmvtab opens a database, and right after 108 +** it closes one. The first argument - or first two arguments, if 109 +** <sql-statement> supplied the context column - is the same as for 110 +** the "missing" UDF. Following this, the UDF is passed integer value 111 +** 0 before a db is opened, and 1 right after it is closed. If both 112 +** a missing and openclose UDF is supplied, the application should expect 113 +** the following sequence of calls (for a single database): 114 +** 115 +** SELECT <openclose-udf>(<db filename>, <context>, 0); 116 +** if( db not already on disk ){ 117 +** SELECT <missing-udf>(<db filename>, <context>); 118 +** } 119 +** ... swarmvtab uses database ... 120 +** SELECT <openclose-udf>(<db filename>, <context>, 1); 121 +** 122 +** The "maxopen" option is used to configure the maximum number of 123 +** database files swarmvtab will hold open simultaneously (default 9). 124 +** 125 +** If an option name begins with a ":" character, then it is assumed 126 +** to be an SQL parameter. In this case, the specified text value is 127 +** bound to the same variable of the <sql-statement> before it is 128 +** executed. It is an error of the named SQL parameter does not exist. 129 +** For example: 130 +** 131 +** CREATE VIRTUAL TABLE swarm USING swarmvtab( 132 +** 'SELECT :path || localfile, tbl, min, max FROM swarmdir', 133 +** :path='/home/user/databases/' 134 +** missing='missing_func' 135 +** ); 70 136 */ 71 137 72 138 #include "sqlite3ext.h" 73 139 SQLITE_EXTENSION_INIT1 74 140 #include <assert.h> 75 141 #include <string.h> 142 +#include <stdlib.h> 76 143 77 144 #ifndef SQLITE_OMIT_VIRTUALTABLE 78 145 79 146 /* 80 147 ** Largest and smallest possible 64-bit signed integers. These macros 81 148 ** copied from sqliteInt.h. 82 149 */ ................................................................................ 124 191 char *zDb; /* Database containing source table */ 125 192 char *zTab; /* Source table name */ 126 193 sqlite3_int64 iMin; /* Minimum rowid */ 127 194 sqlite3_int64 iMax; /* Maximum rowid */ 128 195 129 196 /* Fields used by swarmvtab only */ 130 197 char *zFile; /* Database file containing table zTab */ 198 + char *zContext; /* Context string, if any */ 131 199 int nUser; /* Current number of users */ 132 200 sqlite3 *db; /* Database handle */ 133 201 UnionSrc *pNextClosable; /* Next in list of closable sources */ 134 202 }; 135 203 136 204 /* 137 205 ** Virtual table type for union vtab. ................................................................................ 141 209 sqlite3 *db; /* Database handle */ 142 210 int bSwarm; /* 1 for "swarmvtab", 0 for "unionvtab" */ 143 211 int iPK; /* INTEGER PRIMARY KEY column, or -1 */ 144 212 int nSrc; /* Number of elements in the aSrc[] array */ 145 213 UnionSrc *aSrc; /* Array of source tables, sorted by rowid */ 146 214 147 215 /* Used by swarmvtab only */ 216 + int bHasContext; /* Has context strings */ 148 217 char *zSourceStr; /* Expected unionSourceToStr() value */ 149 - char *zNotFoundCallback; /* UDF to invoke if file not found on open */ 218 + sqlite3_stmt *pNotFound; /* UDF to invoke if file not found on open */ 219 + sqlite3_stmt *pOpenClose; /* UDF to invoke on open and close */ 220 + 150 221 UnionSrc *pClosable; /* First in list of closable sources */ 151 222 int nOpen; /* Current number of open sources */ 152 223 int nMaxOpen; /* Maximum number of open sources */ 153 224 }; 154 225 155 226 /* 156 227 ** Virtual table cursor type for union vtab. ................................................................................ 346 417 if( *pRc==SQLITE_OK ){ 347 418 *pRc = rc; 348 419 if( rc ){ 349 420 *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db)); 350 421 } 351 422 } 352 423 } 424 + 425 +/* 426 +** If an "openclose" UDF was supplied when this virtual table was created, 427 +** invoke it now. The first argument passed is the name of the database 428 +** file for source pSrc. The second is integer value bClose. 429 +** 430 +** If successful, return SQLITE_OK. Otherwise an SQLite error code. In this 431 +** case if argument pzErr is not NULL, also set (*pzErr) to an English 432 +** language error message. The caller is responsible for eventually freeing 433 +** any error message using sqlite3_free(). 434 +*/ 435 +static int unionInvokeOpenClose( 436 + UnionTab *pTab, 437 + UnionSrc *pSrc, 438 + int bClose, 439 + char **pzErr 440 +){ 441 + int rc = SQLITE_OK; 442 + if( pTab->pOpenClose ){ 443 + sqlite3_bind_text(pTab->pOpenClose, 1, pSrc->zFile, -1, SQLITE_STATIC); 444 + if( pTab->bHasContext ){ 445 + sqlite3_bind_text(pTab->pOpenClose, 2, pSrc->zContext, -1, SQLITE_STATIC); 446 + } 447 + sqlite3_bind_int(pTab->pOpenClose, 2+pTab->bHasContext, bClose); 448 + sqlite3_step(pTab->pOpenClose); 449 + if( SQLITE_OK!=(rc = sqlite3_reset(pTab->pOpenClose)) ){ 450 + if( pzErr ){ 451 + *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db)); 452 + } 453 + } 454 + } 455 + return rc; 456 +} 353 457 354 458 /* 355 459 ** This function is a no-op for unionvtab. For swarmvtab, it attempts to 356 460 ** close open database files until at most nMax are open. An SQLite error 357 461 ** code is returned if an error occurs, or SQLITE_OK otherwise. 358 462 */ 359 463 static void unionCloseSources(UnionTab *pTab, int nMax){ 360 464 while( pTab->pClosable && pTab->nOpen>nMax ){ 465 + UnionSrc *p; 361 466 UnionSrc **pp; 362 467 for(pp=&pTab->pClosable; (*pp)->pNextClosable; pp=&(*pp)->pNextClosable); 363 - assert( (*pp)->db ); 364 - sqlite3_close((*pp)->db); 365 - (*pp)->db = 0; 468 + p = *pp; 469 + assert( p->db ); 470 + sqlite3_close(p->db); 471 + p->db = 0; 366 472 *pp = 0; 367 473 pTab->nOpen--; 474 + unionInvokeOpenClose(pTab, p, 1, 0); 368 475 } 369 476 } 370 477 371 478 /* 372 479 ** xDisconnect method. 373 480 */ 374 481 static int unionDisconnect(sqlite3_vtab *pVtab){ 375 482 if( pVtab ){ 376 483 UnionTab *pTab = (UnionTab*)pVtab; 377 484 int i; 378 485 for(i=0; i<pTab->nSrc; i++){ 379 486 UnionSrc *pSrc = &pTab->aSrc[i]; 487 + if( pSrc->db ){ 488 + unionInvokeOpenClose(pTab, pSrc, 1, 0); 489 + } 380 490 sqlite3_free(pSrc->zDb); 381 491 sqlite3_free(pSrc->zTab); 382 492 sqlite3_free(pSrc->zFile); 493 + sqlite3_free(pSrc->zContext); 383 494 sqlite3_close(pSrc->db); 384 495 } 496 + sqlite3_finalize(pTab->pNotFound); 497 + sqlite3_finalize(pTab->pOpenClose); 385 498 sqlite3_free(pTab->zSourceStr); 386 - sqlite3_free(pTab->zNotFoundCallback); 387 499 sqlite3_free(pTab->aSrc); 388 500 sqlite3_free(pTab); 389 501 } 390 502 return SQLITE_OK; 391 503 } 392 504 393 505 /* ................................................................................ 492 604 sqlite3_free(z); 493 605 } 494 606 sqlite3_free(z0); 495 607 496 608 return rc; 497 609 } 498 610 499 - 500 611 /* 501 612 ** Try to open the swarmvtab database. If initially unable, invoke the 502 613 ** not-found callback UDF and then try again. 503 614 */ 504 615 static int unionOpenDatabaseInner(UnionTab *pTab, UnionSrc *pSrc, char **pzErr){ 505 - int rc = SQLITE_OK; 506 - static const int openFlags = 507 - SQLITE_OPEN_READONLY | SQLITE_OPEN_URI; 616 + static const int openFlags = SQLITE_OPEN_READONLY | SQLITE_OPEN_URI; 617 + int rc; 618 + 619 + rc = unionInvokeOpenClose(pTab, pSrc, 0, pzErr); 620 + if( rc!=SQLITE_OK ) return rc; 621 + 508 622 rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0); 509 623 if( rc==SQLITE_OK ) return rc; 510 - if( pTab->zNotFoundCallback ){ 511 - char *zSql = sqlite3_mprintf("SELECT \"%w\"(%Q);", 512 - pTab->zNotFoundCallback, pSrc->zFile); 624 + if( pTab->pNotFound ){ 513 625 sqlite3_close(pSrc->db); 514 626 pSrc->db = 0; 515 - if( zSql==0 ){ 516 - *pzErr = sqlite3_mprintf("out of memory"); 517 - return SQLITE_NOMEM; 627 + sqlite3_bind_text(pTab->pNotFound, 1, pSrc->zFile, -1, SQLITE_STATIC); 628 + if( pTab->bHasContext ){ 629 + sqlite3_bind_text(pTab->pNotFound, 2, pSrc->zContext, -1, SQLITE_STATIC); 518 630 } 519 - rc = sqlite3_exec(pTab->db, zSql, 0, 0, pzErr); 520 - sqlite3_free(zSql); 521 - if( rc ) return rc; 631 + sqlite3_step(pTab->pNotFound); 632 + if( SQLITE_OK!=(rc = sqlite3_reset(pTab->pNotFound)) ){ 633 + *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db)); 634 + return rc; 635 + } 522 636 rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0); 523 637 } 524 638 if( rc!=SQLITE_OK ){ 525 639 *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pSrc->db)); 526 640 } 527 641 return rc; 528 642 } ................................................................................ 568 682 if( rc==SQLITE_OK ){ 569 683 pSrc->pNextClosable = pTab->pClosable; 570 684 pTab->pClosable = pSrc; 571 685 pTab->nOpen++; 572 686 }else{ 573 687 sqlite3_close(pSrc->db); 574 688 pSrc->db = 0; 689 + unionInvokeOpenClose(pTab, pSrc, 1, 0); 575 690 } 576 691 } 577 692 578 693 return rc; 579 694 } 580 695 581 696 ................................................................................ 622 737 pTab->pClosable = pSrc; 623 738 } 624 739 unionCloseSources(pTab, pTab->nMaxOpen); 625 740 } 626 741 } 627 742 return rc; 628 743 } 744 + 745 +/* 746 +** Return true if the argument is a space, tab, CR or LF character. 747 +*/ 748 +static int union_isspace(char c){ 749 + return (c==' ' || c=='\n' || c=='\r' || c=='\t'); 750 +} 751 + 752 +/* 753 +** Return true if the argument is an alphanumeric character in the 754 +** ASCII range. 755 +*/ 756 +static int union_isidchar(char c){ 757 + return ((c>='a' && c<='z') || (c>='A' && c<'Z') || (c>='0' && c<='9')); 758 +} 759 + 760 +/* 761 +** This function is called to handle all arguments following the first 762 +** (the SQL statement) passed to a swarmvtab (not unionvtab) CREATE 763 +** VIRTUAL TABLE statement. It may bind parameters to the SQL statement 764 +** or configure members of the UnionTab object passed as the second 765 +** argument. 766 +** 767 +** Refer to header comments at the top of this file for a description 768 +** of the arguments parsed. 769 +** 770 +** This function is a no-op if *pRc is other than SQLITE_OK when it is 771 +** called. Otherwise, if an error occurs, *pRc is set to an SQLite error 772 +** code. In this case *pzErr may be set to point to a buffer containing 773 +** an English language error message. It is the responsibility of the 774 +** caller to eventually free the buffer using sqlite3_free(). 775 +*/ 776 +static void unionConfigureVtab( 777 + int *pRc, /* IN/OUT: Error code */ 778 + UnionTab *pTab, /* Table to configure */ 779 + sqlite3_stmt *pStmt, /* SQL statement to find sources */ 780 + int nArg, /* Number of entries in azArg[] array */ 781 + const char * const *azArg, /* Array of arguments to consider */ 782 + char **pzErr /* OUT: Error message */ 783 +){ 784 + int rc = *pRc; 785 + int i; 786 + if( rc==SQLITE_OK ){ 787 + pTab->bHasContext = (sqlite3_column_count(pStmt)>4); 788 + } 789 + for(i=0; rc==SQLITE_OK && i<nArg; i++){ 790 + char *zArg = unionStrdup(&rc, azArg[i]); 791 + if( zArg ){ 792 + int nOpt = 0; /* Size of option name in bytes */ 793 + char *zOpt; /* Pointer to option name */ 794 + char *zVal; /* Pointer to value */ 795 + 796 + unionDequote(zArg); 797 + zOpt = zArg; 798 + while( union_isspace(*zOpt) ) zOpt++; 799 + zVal = zOpt; 800 + if( *zVal==':' ) zVal++; 801 + while( union_isidchar(*zVal) ) zVal++; 802 + nOpt = zVal-zOpt; 803 + 804 + while( union_isspace(*zVal) ) zVal++; 805 + if( *zVal=='=' ){ 806 + zOpt[nOpt] = '\0'; 807 + zVal++; 808 + while( union_isspace(*zVal) ) zVal++; 809 + zVal = unionStrdup(&rc, zVal); 810 + if( zVal ){ 811 + unionDequote(zVal); 812 + if( zOpt[0]==':' ){ 813 + /* A value to bind to the SQL statement */ 814 + int iParam = sqlite3_bind_parameter_index(pStmt, zOpt); 815 + if( iParam==0 ){ 816 + *pzErr = sqlite3_mprintf( 817 + "swarmvtab: no such SQL parameter: %s", zOpt 818 + ); 819 + rc = SQLITE_ERROR; 820 + }else{ 821 + rc = sqlite3_bind_text(pStmt, iParam, zVal, -1, SQLITE_TRANSIENT); 822 + } 823 + }else if( nOpt==7 && 0==sqlite3_strnicmp(zOpt, "maxopen", 7) ){ 824 + pTab->nMaxOpen = atoi(zVal); 825 + if( pTab->nMaxOpen<=0 ){ 826 + *pzErr = sqlite3_mprintf("swarmvtab: illegal maxopen value"); 827 + rc = SQLITE_ERROR; 828 + } 829 + }else if( nOpt==7 && 0==sqlite3_strnicmp(zOpt, "missing", 7) ){ 830 + if( pTab->pNotFound ){ 831 + *pzErr = sqlite3_mprintf( 832 + "swarmvtab: duplicate \"missing\" option"); 833 + rc = SQLITE_ERROR; 834 + }else{ 835 + pTab->pNotFound = unionPreparePrintf(&rc, pzErr, pTab->db, 836 + "SELECT \"%w\"(?%s)", zVal, pTab->bHasContext ? ",?" : "" 837 + ); 838 + } 839 + }else if( nOpt==9 && 0==sqlite3_strnicmp(zOpt, "openclose", 9) ){ 840 + if( pTab->pOpenClose ){ 841 + *pzErr = sqlite3_mprintf( 842 + "swarmvtab: duplicate \"openclose\" option"); 843 + rc = SQLITE_ERROR; 844 + }else{ 845 + pTab->pOpenClose = unionPreparePrintf(&rc, pzErr, pTab->db, 846 + "SELECT \"%w\"(?,?%s)", zVal, pTab->bHasContext ? ",?" : "" 847 + ); 848 + } 849 + }else{ 850 + *pzErr = sqlite3_mprintf("swarmvtab: unrecognized option: %s",zOpt); 851 + rc = SQLITE_ERROR; 852 + } 853 + sqlite3_free(zVal); 854 + } 855 + }else{ 856 + if( i==0 && nArg==1 ){ 857 + pTab->pNotFound = unionPreparePrintf(&rc, pzErr, pTab->db, 858 + "SELECT \"%w\"(?)", zArg 859 + ); 860 + }else{ 861 + *pzErr = sqlite3_mprintf( "swarmvtab: parse error: %s", azArg[i]); 862 + rc = SQLITE_ERROR; 863 + } 864 + } 865 + sqlite3_free(zArg); 866 + } 867 + } 868 + *pRc = rc; 869 +} 629 870 630 871 /* 631 872 ** xConnect/xCreate method. 632 873 ** 633 874 ** The argv[] array contains the following: 634 875 ** 635 876 ** argv[0] -> module name ("unionvtab" or "swarmvtab") ................................................................................ 650 891 int bSwarm = (pAux==0 ? 0 : 1); 651 892 const char *zVtab = (bSwarm ? "swarmvtab" : "unionvtab"); 652 893 653 894 if( sqlite3_stricmp("temp", argv[1]) ){ 654 895 /* unionvtab tables may only be created in the temp schema */ 655 896 *pzErr = sqlite3_mprintf("%s tables must be created in TEMP schema", zVtab); 656 897 rc = SQLITE_ERROR; 657 - }else if( argc!=4 && argc!=5 ){ 898 + }else if( argc<4 || (argc>4 && bSwarm==0) ){ 658 899 *pzErr = sqlite3_mprintf("wrong number of arguments for %s", zVtab); 659 900 rc = SQLITE_ERROR; 660 901 }else{ 661 902 int nAlloc = 0; /* Allocated size of pTab->aSrc[] */ 662 903 sqlite3_stmt *pStmt = 0; /* Argument statement */ 663 904 char *zArg = unionStrdup(&rc, argv[3]); /* Copy of argument to CVT */ 664 905 ................................................................................ 669 910 unionDequote(zArg); 670 911 pStmt = unionPreparePrintf(&rc, pzErr, db, 671 912 "SELECT * FROM (%z) ORDER BY 3", zArg 672 913 ); 673 914 674 915 /* Allocate the UnionTab structure */ 675 916 pTab = unionMalloc(&rc, sizeof(UnionTab)); 917 + if( pTab ){ 918 + assert( rc==SQLITE_OK ); 919 + pTab->db = db; 920 + pTab->bSwarm = bSwarm; 921 + pTab->nMaxOpen = SWARMVTAB_MAX_OPEN; 922 + } 923 + 924 + /* Parse other CVT arguments, if any */ 925 + if( bSwarm ){ 926 + unionConfigureVtab(&rc, pTab, pStmt, argc-4, &argv[4], pzErr); 927 + } 676 928 677 929 /* Iterate through the rows returned by the SQL statement specified 678 930 ** as an argument to the CREATE VIRTUAL TABLE statement. */ 679 931 while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ 680 932 const char *zDb = (const char*)sqlite3_column_text(pStmt, 0); 681 933 const char *zTab = (const char*)sqlite3_column_text(pStmt, 1); 682 934 sqlite3_int64 iMin = sqlite3_column_int64(pStmt, 2); ................................................................................ 711 963 pSrc->iMin = iMin; 712 964 pSrc->iMax = iMax; 713 965 if( bSwarm ){ 714 966 pSrc->zFile = unionStrdup(&rc, zDb); 715 967 }else{ 716 968 pSrc->zDb = unionStrdup(&rc, zDb); 717 969 } 970 + if( pTab->bHasContext ){ 971 + const char *zContext = (const char*)sqlite3_column_text(pStmt, 4); 972 + pSrc->zContext = unionStrdup(&rc, zContext); 973 + } 718 974 } 719 975 } 720 976 unionFinalize(&rc, pStmt, pzErr); 721 977 pStmt = 0; 722 978 723 - /* Capture the not-found callback UDF name */ 724 - if( rc==SQLITE_OK && argc>=5 ){ 725 - pTab->zNotFoundCallback = unionStrdup(&rc, argv[4]); 726 - unionDequote(pTab->zNotFoundCallback); 727 - } 728 - 729 979 /* It is an error if the SELECT statement returned zero rows. If only 730 980 ** because there is no way to determine the schema of the virtual 731 981 ** table in this case. */ 732 982 if( rc==SQLITE_OK && pTab->nSrc==0 ){ 733 983 *pzErr = sqlite3_mprintf("no source tables configured"); 734 984 rc = SQLITE_ERROR; 735 985 } 736 986 737 987 /* For unionvtab, verify that all source tables exist and have 738 988 ** compatible schemas. For swarmvtab, attach the first database and 739 989 ** check that the first table is a rowid table only. */ 740 990 if( rc==SQLITE_OK ){ 741 - pTab->db = db; 742 - pTab->bSwarm = bSwarm; 743 - pTab->nMaxOpen = SWARMVTAB_MAX_OPEN; 744 991 if( bSwarm ){ 745 992 rc = unionOpenDatabase(pTab, 0, pzErr); 746 993 }else{ 747 994 rc = unionSourceCheck(pTab, pzErr); 748 995 } 749 996 } 750 997
Changes to test/swarmvtab.test.
209 209 do_catchsql_test 3.1 { 210 210 CREATE VIRTUAL TABLE temp.xyz USING swarmvtab( 211 211 'VALUES 212 212 ("test.db1", "t1", 1, 10), 213 213 ("test.db2", "t1", 11, 20) 214 214 ', 'fetch_db_no_such_function' 215 215 ); 216 -} {1 {no such function: fetch_db_no_such_function}} 216 +} {1 {sql error: no such function: fetch_db_no_such_function}} 217 217 218 218 do_catchsql_test 3.2 { 219 219 CREATE VIRTUAL TABLE temp.xyz USING swarmvtab( 220 220 'VALUES 221 221 ("test.db1", "t1", 1, 10), 222 222 ("test.db2", "t1", 11, 20) 223 223 ', 'fetch_db'
Changes to test/swarmvtab2.test.
10 10 #*********************************************************************** 11 11 # This file implements regression tests for SQLite library. The 12 12 # focus of this file is the "swarmvtab" extension 13 13 # 14 14 15 15 set testdir [file dirname $argv0] 16 16 source $testdir/tester.tcl 17 -set testprefix swarmvtab 17 +set testprefix swarmvtab2 18 18 do_not_use_codec 19 19 20 20 ifcapable !vtab { 21 21 finish_test 22 22 return 23 23 } 24 24
Added test/swarmvtab3.test.
1 +# 2017-07-15 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# This file implements regression tests for SQLite library. The 12 +# focus of this file is the "swarmvtab" extension 13 +# 14 + 15 +set testdir [file dirname $argv0] 16 +source $testdir/tester.tcl 17 +set testprefix swarmvtab3 18 +do_not_use_codec 19 + 20 +ifcapable !vtab { 21 + finish_test 22 + return 23 +} 24 + 25 +load_static_extension db unionvtab 26 + 27 +set nFile $sqlite_open_file_count 28 + 29 +do_execsql_test 1.0 { 30 + CREATE TEMP TABLE swarm(id, tbl, minval, maxval); 31 +} 32 + 33 +# Set up 100 databases with filenames "remote_test.dbN", where N is between 34 +# 0 and 99. 35 +do_test 1.1 { 36 + for {set i 0} {$i < 100} {incr i} { 37 + set file remote_test.db$i 38 + forcedelete $file 39 + forcedelete test.db$i 40 + sqlite3 rrr $file 41 + rrr eval { 42 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 43 + INSERT INTO t1 VALUES($i, $i); 44 + } 45 + rrr close 46 + db eval { 47 + INSERT INTO swarm VALUES($i, 't1', $i, $i); 48 + } 49 + set ::dbcache(test.db$i) 0 50 + } 51 +} {} 52 + 53 +proc missing_db {filename} { 54 + set remote "remote_$filename" 55 + forcedelete $filename 56 + file copy $remote $filename 57 +} 58 +db func missing_db missing_db 59 + 60 +proc openclose_db {filename bClose} { 61 + if {$bClose} { 62 + incr ::dbcache($filename) -1 63 + } else { 64 + incr ::dbcache($filename) 1 65 + } 66 + if {$::dbcache($filename)==0} { 67 + forcedelete $filename 68 + } 69 +} 70 +db func openclose_db openclose_db 71 + 72 +proc check_dbcache {} { 73 + set n 0 74 + for {set i 0} {$i<100} {incr i} { 75 + set exists [file exists test.db$i] 76 + if {$exists!=($::dbcache(test.db$i)!=0)} { 77 + error "inconsistent ::dbcache and disk" 78 + } 79 + incr n $exists 80 + } 81 + return $n 82 +} 83 + 84 +foreach {tn nMaxOpen cvt} { 85 + 1 5 { 86 + CREATE VIRTUAL TABLE temp.s USING swarmvtab( 87 + 'SELECT :prefix || id, tbl, minval, minval FROM swarm', 88 + :prefix='test.db', 89 + missing=missing_db, 90 + openclose=openclose_db, 91 + maxopen=5 92 + ) 93 + } 94 + 95 + 2 3 { 96 + CREATE VIRTUAL TABLE temp.s USING swarmvtab( 97 + 'SELECT :prefix || id, tbl, minval, minval FROM swarm', 98 + :prefix='test.db', 99 + missing = 'missing_db', 100 + openclose=[openclose_db], 101 + maxopen = 3 102 + ) 103 + } 104 + 105 + 3 1 { 106 + CREATE VIRTUAL TABLE temp.s USING swarmvtab( 107 + 'SELECT :prefix||''.''||:suffix||id, tbl, minval, minval FROM swarm', 108 + :prefix=test, :suffix=db, 109 + missing = 'missing_db', 110 + openclose=[openclose_db], 111 + maxopen = 1 112 + ) 113 + } 114 + 115 +} { 116 + execsql { DROP TABLE IF EXISTS s } 117 + 118 + do_execsql_test 1.$tn.1 $cvt 119 + 120 + do_execsql_test 1.$tn.2 { 121 + SELECT b FROM s WHERE a<10; 122 + } {0 1 2 3 4 5 6 7 8 9} 123 + 124 + do_test 1.$tn.3 { check_dbcache } $nMaxOpen 125 + 126 + do_execsql_test 1.$tn.4 { 127 + SELECT b FROM s WHERE (b%10)=0; 128 + } {0 10 20 30 40 50 60 70 80 90} 129 + 130 + do_test 1.$tn.5 { check_dbcache } $nMaxOpen 131 +} 132 + 133 +execsql { DROP TABLE IF EXISTS s } 134 +for {set i 0} {$i < 100} {incr i} { 135 + forcedelete remote_test.db$i 136 +} 137 + 138 +#---------------------------------------------------------------------------- 139 +# 140 +do_execsql_test 2.0 { 141 + DROP TABLE IF EXISTS swarm; 142 + CREATE TEMP TABLE swarm(file, tbl, minval, maxval, ctx); 143 +} 144 + 145 +catch { array unset ::dbcache } 146 + 147 +# Set up 100 databases with filenames "remote_test.dbN", where N is a 148 +# random integer between 0 and 1,000,000 149 +# 0 and 99. 150 +do_test 2.1 { 151 + for {set i 0} {$i < 100} {incr i} { 152 + while 1 { 153 + set ctx [expr abs(int(rand() *1000000))] 154 + if {[info exists ::dbcache($ctx)]==0} break 155 + } 156 + 157 + set file test_remote.db$ctx 158 + forcedelete $file 159 + forcedelete test.db$i 160 + sqlite3 rrr $file 161 + rrr eval { 162 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 163 + INSERT INTO t1 VALUES($i, $i); 164 + } 165 + rrr close 166 + db eval { 167 + INSERT INTO swarm VALUES('test.db' || $i, 't1', $i, $i, $file) 168 + } 169 + set ::dbcache(test.db$i) 0 170 + } 171 +} {} 172 + 173 +proc missing_db {filename ctx} { 174 + file copy $ctx $filename 175 +} 176 +db func missing_db missing_db 177 + 178 +proc openclose_db {filename ctx bClose} { 179 + if {$bClose} { 180 + incr ::dbcache($filename) -1 181 + } else { 182 + incr ::dbcache($filename) 1 183 + } 184 + if {$::dbcache($filename)==0} { 185 + forcedelete $filename 186 + } 187 +} 188 +db func openclose_db openclose_db 189 + 190 +proc check_dbcache {} { 191 + set n 0 192 + foreach k [array names ::dbcache] { 193 + set exists [file exists $k] 194 + if {$exists!=($::dbcache($k)!=0)} { 195 + error "inconsistent ::dbcache and disk ($k)" 196 + } 197 + incr n $exists 198 + } 199 + return $n 200 +} 201 + 202 +foreach {tn nMaxOpen cvt} { 203 + 2 5 { 204 + CREATE VIRTUAL TABLE temp.s USING swarmvtab( 205 + 'SELECT file, tbl, minval, minval, ctx FROM swarm', 206 + missing=missing_db, 207 + openclose=openclose_db, 208 + maxopen=5 209 + ) 210 + } 211 +} { 212 + execsql { DROP TABLE IF EXISTS s } 213 + 214 + do_execsql_test 1.$tn.1 $cvt 215 + 216 + do_execsql_test 1.$tn.2 { 217 + SELECT b FROM s WHERE a<10; 218 + } {0 1 2 3 4 5 6 7 8 9} 219 + 220 + do_test 1.$tn.3 { check_dbcache } $nMaxOpen 221 + 222 + do_execsql_test 1.$tn.4 { 223 + SELECT b FROM s WHERE (b%10)=0; 224 + } {0 10 20 30 40 50 60 70 80 90} 225 + 226 + do_test 1.$tn.5 { check_dbcache } $nMaxOpen 227 +} 228 + 229 +db close 230 +forcedelete {*}[glob test_remote.db*] 231 + 232 +finish_test 233 +