SQLite

**     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).
**
** SWARMVTAB
**
**  LEGACY SYNTAX:
**
**   A "swarmvtab" virtual table is created similarly to a unionvtab table:
**
**     CREATE VIRTUAL TABLE <name>
**      USING swarmvtab(<sql-statement>, <callback>);
**
**   The difference is that for a swarmvtab table, the first column returned
**   by the <sql statement> must return a path or URI that can be used to open
**   the database file containing the source table.  The <callback> option
**   is optional.  If included, it is the name of an application-defined
**   SQL function that is invoked with the URI of the file, if the file
**   does not already exist on disk.
**   does not already exist on disk when required by swarmvtab.
**
**  NEW SYNTAX:
**
**   Using the new syntax, a swarmvtab table is created with:
**
**      CREATE VIRTUAL TABLE <name> USING swarmvtab(
**        <sql-statement> [, <options>]
**      );
**
**   where valid <options> are:
**
**      missing=<udf-function-name>
**      openclose=<udf-function-name>
**      maxopen=<integer>
**      <sql-parameter>=<text-value>
**
**   The <sql-statement> must return the same 4 columns as for a swarmvtab
**   table in legacy mode. However, it may also return a 5th column - the
**   "context" column. The text value returned in this column is not used
**   at all by the swarmvtab implementation, except that it is passed as
**   an additional argument to the two UDF functions that may be invoked
**   (see below).
**
**   The "missing" option, if present, specifies the name of an SQL UDF
**   function to be invoked if a database file is not already present on
**   disk when required by swarmvtab. If the <sql-statement> did not provide
**   a context column, it is invoked as:
**
**     SELECT <missing-udf>(<database filename/uri>);
**
**   Or, if there was a context column:
**
**     SELECT <missing-udf>(<database filename/uri>, <context>);
**
**   The "openclose" option may also specify a UDF function. This function
**   is invoked right before swarmvtab opens a database, and right after
**   it closes one. The first argument - or first two arguments, if
**   <sql-statement> supplied the context column - is the same as for
**   the "missing" UDF. Following this, the UDF is passed integer value
**   0 before a db is opened, and 1 right after it is closed. If both
**   a missing and openclose UDF is supplied, the application should expect
**   the following sequence of calls (for a single database):
**
**      SELECT <openclose-udf>(<db filename>, <context>, 0);
**      if( db not already on disk ){
**          SELECT <missing-udf>(<db filename>, <context>);
**      }
**      ... swarmvtab uses database ...
**      SELECT <openclose-udf>(<db filename>, <context>, 1);
**
**   The "maxopen" option is used to configure the maximum number of
**   database files swarmvtab will hold open simultaneously (default 9).
**
**   If an option name begins with a ":" character, then it is assumed
**   to be an SQL parameter. In this case, the specified text value is
**   bound to the same variable of the <sql-statement> before it is 
**   executed. It is an error of the named SQL parameter does not exist.
**   For example:
**
**     CREATE VIRTUAL TABLE swarm USING swarmvtab(
**       'SELECT :path || localfile, tbl, min, max FROM swarmdir',
**       :path='/home/user/databases/'
**       missing='missing_func'
**     );
*/

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

#ifndef SQLITE_OMIT_VIRTUALTABLE

/*
** Largest and smallest possible 64-bit signed integers. These macros
** copied from sqliteInt.h.
*/

  char *zDb;                      /* Database containing source table */
  char *zTab;                     /* Source table name */
  sqlite3_int64 iMin;             /* Minimum rowid */
  sqlite3_int64 iMax;             /* Maximum rowid */

  /* Fields used by swarmvtab only */
  char *zFile;                    /* Database file containing table zTab */
  char *zContext;                 /* Context string, if any */
  int nUser;                      /* Current number of users */
  sqlite3 *db;                    /* Database handle */
  UnionSrc *pNextClosable;        /* Next in list of closable sources */
};

/*
** Virtual table  type for union vtab.
*/
struct UnionTab {
  sqlite3_vtab base;              /* Base class - must be first */
  sqlite3 *db;                    /* Database handle */
  int bSwarm;                     /* 1 for "swarmvtab", 0 for "unionvtab" */
  int iPK;                        /* INTEGER PRIMARY KEY column, or -1 */
  int nSrc;                       /* Number of elements in the aSrc[] array */
  UnionSrc *aSrc;                 /* Array of source tables, sorted by rowid */

  /* Used by swarmvtab only */
  int bHasContext;                /* Has context strings */
  char *zSourceStr;               /* Expected unionSourceToStr() value */
  char *zNotFoundCallback;        /* UDF to invoke if file not found on open */
  sqlite3_stmt *pNotFound;        /* UDF to invoke if file not found on open */
  sqlite3_stmt *pOpenClose;       /* UDF to invoke on open and close */

  UnionSrc *pClosable;            /* First in list of closable sources */
  int nOpen;                      /* Current number of open sources */
  int nMaxOpen;                   /* Maximum number of open sources */
};

/*
** Virtual table cursor type for union vtab.

  if( *pRc==SQLITE_OK ){
    *pRc = rc;
    if( rc ){
      *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
    }
  }
}

/*
** If an "openclose" UDF was supplied when this virtual table was created,
** invoke it now. The first argument passed is the name of the database
** file for source pSrc. The second is integer value bClose.
**
** If successful, return SQLITE_OK. Otherwise an SQLite error code. In this
** case if argument pzErr is not NULL, also set (*pzErr) to an English
** language error message. The caller is responsible for eventually freeing 
** any error message using sqlite3_free().
*/
static int unionInvokeOpenClose(
  UnionTab *pTab, 
  UnionSrc *pSrc, 
  int bClose,
  char **pzErr
){
  int rc = SQLITE_OK;
  if( pTab->pOpenClose ){
    sqlite3_bind_text(pTab->pOpenClose, 1, pSrc->zFile, -1, SQLITE_STATIC);
    if( pTab->bHasContext ){
      sqlite3_bind_text(pTab->pOpenClose, 2, pSrc->zContext, -1, SQLITE_STATIC);
    }
    sqlite3_bind_int(pTab->pOpenClose, 2+pTab->bHasContext, bClose);
    sqlite3_step(pTab->pOpenClose);
    if( SQLITE_OK!=(rc = sqlite3_reset(pTab->pOpenClose)) ){
      if( pzErr ){
        *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db));
      }
    }
  }
  return rc;
}

/*
** This function is a no-op for unionvtab. For swarmvtab, it attempts to
** close open database files until at most nMax are open. An SQLite error
** code is returned if an error occurs, or SQLITE_OK otherwise.
*/
static void unionCloseSources(UnionTab *pTab, int nMax){
  while( pTab->pClosable && pTab->nOpen>nMax ){
    UnionSrc *p;
    UnionSrc **pp;
    for(pp=&pTab->pClosable; (*pp)->pNextClosable; pp=&(*pp)->pNextClosable);
    p = *pp;
    assert( (*pp)->db );
    sqlite3_close((*pp)->db);
    (*pp)->db = 0;
    assert( p->db );
    sqlite3_close(p->db);
    p->db = 0;
    *pp = 0;
    pTab->nOpen--;
    unionInvokeOpenClose(pTab, p, 1, 0);
  }
}

/*
** xDisconnect method.
*/
static int unionDisconnect(sqlite3_vtab *pVtab){
  if( pVtab ){
    UnionTab *pTab = (UnionTab*)pVtab;
    int i;
    for(i=0; i<pTab->nSrc; i++){
      UnionSrc *pSrc = &pTab->aSrc[i];
      if( pSrc->db ){
        unionInvokeOpenClose(pTab, pSrc, 1, 0);
      }
      sqlite3_free(pSrc->zDb);
      sqlite3_free(pSrc->zTab);
      sqlite3_free(pSrc->zFile);
      sqlite3_free(pSrc->zContext);
      sqlite3_close(pSrc->db);
    }
    sqlite3_free(pTab->zSourceStr);
    sqlite3_free(pTab->zNotFoundCallback);
    sqlite3_finalize(pTab->pNotFound);
    sqlite3_finalize(pTab->pOpenClose);
    sqlite3_free(pTab->zSourceStr);
    sqlite3_free(pTab->aSrc);
    sqlite3_free(pTab);
  }
  return SQLITE_OK;
}

/*

    sqlite3_free(z);
  }
  sqlite3_free(z0);

  return rc;
}


/*
** Try to open the swarmvtab database.  If initially unable, invoke the
** not-found callback UDF and then try again.
*/
static int unionOpenDatabaseInner(UnionTab *pTab, UnionSrc *pSrc, char **pzErr){
  static const int openFlags = SQLITE_OPEN_READONLY | SQLITE_OPEN_URI;
  int rc = SQLITE_OK;
  static const int openFlags = 
       SQLITE_OPEN_READONLY | SQLITE_OPEN_URI;
  int rc;

  rc = unionInvokeOpenClose(pTab, pSrc, 0, pzErr);
  if( rc!=SQLITE_OK ) return rc;

  rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0);
  if( rc==SQLITE_OK ) return rc;
  if( pTab->zNotFoundCallback ){
  if( pTab->pNotFound ){
    char *zSql = sqlite3_mprintf("SELECT \"%w\"(%Q);",
                    pTab->zNotFoundCallback, pSrc->zFile);
    sqlite3_close(pSrc->db);
    pSrc->db = 0;
    sqlite3_bind_text(pTab->pNotFound, 1, pSrc->zFile, -1, SQLITE_STATIC);
    if( zSql==0 ){
      *pzErr = sqlite3_mprintf("out of memory");
      return SQLITE_NOMEM;
    if( pTab->bHasContext ){
      sqlite3_bind_text(pTab->pNotFound, 2, pSrc->zContext, -1, SQLITE_STATIC);
    }
    sqlite3_step(pTab->pNotFound);
    if( SQLITE_OK!=(rc = sqlite3_reset(pTab->pNotFound)) ){
      *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db));
      return rc;
    }
    rc = sqlite3_exec(pTab->db, zSql, 0, 0, pzErr);
    sqlite3_free(zSql);
    if( rc ) return rc;
    rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0);
  }
  if( rc!=SQLITE_OK ){
    *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pSrc->db));
  }
  return rc;
}

    if( rc==SQLITE_OK ){
      pSrc->pNextClosable = pTab->pClosable;
      pTab->pClosable = pSrc;
      pTab->nOpen++;
    }else{
      sqlite3_close(pSrc->db);
      pSrc->db = 0;
      unionInvokeOpenClose(pTab, pSrc, 1, 0);
    }
  }

  return rc;
}

        pTab->pClosable = pSrc;
      }
      unionCloseSources(pTab, pTab->nMaxOpen);
    }
  }
  return rc;
}

/* 
** Return true if the argument is a space, tab, CR or LF character.
*/
static int union_isspace(char c){
  return (c==' ' || c=='\n' || c=='\r' || c=='\t');
}

/* 
** Return true if the argument is an alphanumeric character in the 
** ASCII range.
*/
static int union_isidchar(char c){
  return ((c>='a' && c<='z') || (c>='A' && c<'Z') || (c>='0' && c<='9'));
}

/*
** This function is called to handle all arguments following the first 
** (the SQL statement) passed to a swarmvtab (not unionvtab) CREATE 
** VIRTUAL TABLE statement. It may bind parameters to the SQL statement 
** or configure members of the UnionTab object passed as the second
** argument.
**
** Refer to header comments at the top of this file for a description
** of the arguments parsed.
**
** This function is a no-op if *pRc is other than SQLITE_OK when it is
** called. Otherwise, if an error occurs, *pRc is set to an SQLite error
** code. In this case *pzErr may be set to point to a buffer containing
** an English language error message. It is the responsibility of the 
** caller to eventually free the buffer using sqlite3_free().
*/
static void unionConfigureVtab(
  int *pRc,                       /* IN/OUT: Error code */
  UnionTab *pTab,                 /* Table to configure */
  sqlite3_stmt *pStmt,            /* SQL statement to find sources */
  int nArg,                       /* Number of entries in azArg[] array */
  const char * const *azArg,      /* Array of arguments to consider */
  char **pzErr                    /* OUT: Error message */
){
  int rc = *pRc;
  int i;
  if( rc==SQLITE_OK ){
    pTab->bHasContext = (sqlite3_column_count(pStmt)>4);
  }
  for(i=0; rc==SQLITE_OK && i<nArg; i++){
    char *zArg = unionStrdup(&rc, azArg[i]);
    if( zArg ){
      int nOpt = 0;               /* Size of option name in bytes */
      char *zOpt;                 /* Pointer to option name */
      char *zVal;                 /* Pointer to value */

      unionDequote(zArg);
      zOpt = zArg;
      while( union_isspace(*zOpt) ) zOpt++;
      zVal = zOpt;
      if( *zVal==':' ) zVal++;
      while( union_isidchar(*zVal) ) zVal++;
      nOpt = zVal-zOpt;

      while( union_isspace(*zVal) ) zVal++;
      if( *zVal=='=' ){
        zOpt[nOpt] = '\0';
        zVal++;
        while( union_isspace(*zVal) ) zVal++;
        zVal = unionStrdup(&rc, zVal);
        if( zVal ){
          unionDequote(zVal);
          if( zOpt[0]==':' ){
            /* A value to bind to the SQL statement */
            int iParam = sqlite3_bind_parameter_index(pStmt, zOpt);
            if( iParam==0 ){
              *pzErr = sqlite3_mprintf(
                  "swarmvtab: no such SQL parameter: %s", zOpt
              );
              rc = SQLITE_ERROR;
            }else{
              rc = sqlite3_bind_text(pStmt, iParam, zVal, -1, SQLITE_TRANSIENT);
            }
          }else if( nOpt==7 && 0==sqlite3_strnicmp(zOpt, "maxopen", 7) ){
            pTab->nMaxOpen = atoi(zVal);
            if( pTab->nMaxOpen<=0 ){
              *pzErr = sqlite3_mprintf("swarmvtab: illegal maxopen value");
              rc = SQLITE_ERROR;
            }
          }else if( nOpt==7 && 0==sqlite3_strnicmp(zOpt, "missing", 7) ){
            if( pTab->pNotFound ){
              *pzErr = sqlite3_mprintf(
                  "swarmvtab: duplicate \"missing\" option");
              rc = SQLITE_ERROR;
            }else{
              pTab->pNotFound = unionPreparePrintf(&rc, pzErr, pTab->db,
                  "SELECT \"%w\"(?%s)", zVal, pTab->bHasContext ? ",?" : ""
              );
            }
          }else if( nOpt==9 && 0==sqlite3_strnicmp(zOpt, "openclose", 9) ){
            if( pTab->pOpenClose ){
              *pzErr = sqlite3_mprintf(
                  "swarmvtab: duplicate \"openclose\" option");
              rc = SQLITE_ERROR;
            }else{
              pTab->pOpenClose = unionPreparePrintf(&rc, pzErr, pTab->db,
                  "SELECT \"%w\"(?,?%s)", zVal, pTab->bHasContext ? ",?" : ""
              );
            }
          }else{
            *pzErr = sqlite3_mprintf("swarmvtab: unrecognized option: %s",zOpt);
            rc = SQLITE_ERROR;
          }
          sqlite3_free(zVal);
        }
      }else{
        if( i==0 && nArg==1 ){
          pTab->pNotFound = unionPreparePrintf(&rc, pzErr, pTab->db,
              "SELECT \"%w\"(?)", zArg
          );
        }else{
          *pzErr = sqlite3_mprintf( "swarmvtab: parse error: %s", azArg[i]);
          rc = SQLITE_ERROR;
        }
      }
      sqlite3_free(zArg);
    }
  }
  *pRc = rc;
}

/* 
** xConnect/xCreate method.
**
** The argv[] array contains the following:
**
**   argv[0]   -> module name  ("unionvtab" or "swarmvtab")

  int bSwarm = (pAux==0 ? 0 : 1);
  const char *zVtab = (bSwarm ? "swarmvtab" : "unionvtab");

  if( sqlite3_stricmp("temp", argv[1]) ){
    /* unionvtab tables may only be created in the temp schema */
    *pzErr = sqlite3_mprintf("%s tables must be created in TEMP schema", zVtab);
    rc = SQLITE_ERROR;
  }else if( argc!=4 && argc!=5 ){
  }else if( argc<4 || (argc>4 && bSwarm==0) ){
    *pzErr = sqlite3_mprintf("wrong number of arguments for %s", zVtab);
    rc = SQLITE_ERROR;
  }else{
    int nAlloc = 0;               /* Allocated size of pTab->aSrc[] */
    sqlite3_stmt *pStmt = 0;      /* Argument 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.  */
    unionDequote(zArg);
    pStmt = unionPreparePrintf(&rc, pzErr, db, 
        "SELECT * FROM (%z) ORDER BY 3", zArg
    );

    /* Allocate the UnionTab structure */
    pTab = unionMalloc(&rc, sizeof(UnionTab));
    if( pTab ){
      assert( rc==SQLITE_OK );
      pTab->db = db;
      pTab->bSwarm = bSwarm;
      pTab->nMaxOpen = SWARMVTAB_MAX_OPEN;
    }

    /* Parse other CVT arguments, if any */
    if( bSwarm ){
      unionConfigureVtab(&rc, pTab, pStmt, argc-4, &argv[4], pzErr);
    }

    /* 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);

        pSrc->iMin = iMin;
        pSrc->iMax = iMax;
        if( bSwarm ){
          pSrc->zFile = unionStrdup(&rc, zDb);
        }else{
          pSrc->zDb = unionStrdup(&rc, zDb);
        }
        if( pTab->bHasContext ){
          const char *zContext = (const char*)sqlite3_column_text(pStmt, 4);
          pSrc->zContext = unionStrdup(&rc, zContext);
        }
      }
    }
    unionFinalize(&rc, pStmt, pzErr);
    pStmt = 0;

    /* Capture the not-found callback UDF name */
    if( rc==SQLITE_OK && argc>=5 ){
      pTab->zNotFoundCallback = unionStrdup(&rc, argv[4]);
      unionDequote(pTab->zNotFoundCallback);
    }

    /* It is an error if the SELECT statement returned zero rows. If only
    ** because there is no way to determine the schema of the virtual 
    ** table in this case.  */
    if( rc==SQLITE_OK && pTab->nSrc==0 ){
      *pzErr = sqlite3_mprintf("no source tables configured");
      rc = SQLITE_ERROR;
    }

    /* For unionvtab, verify that all source tables exist and have 
    ** compatible schemas. For swarmvtab, attach the first database and
    ** check that the first table is a rowid table only.  */
    if( rc==SQLITE_OK ){
      pTab->db = db;
      pTab->bSwarm = bSwarm;
      pTab->nMaxOpen = SWARMVTAB_MAX_OPEN;
      if( bSwarm ){
        rc = unionOpenDatabase(pTab, 0, pzErr);
      }else{
        rc = unionSourceCheck(pTab, pzErr);
      }
    }

do_catchsql_test 3.1 {
  CREATE VIRTUAL TABLE temp.xyz USING swarmvtab(
    'VALUES
        ("test.db1", "t1", 1, 10),
        ("test.db2", "t1", 11, 20)
    ', 'fetch_db_no_such_function'
  );
} {1 {no such function: fetch_db_no_such_function}}
} {1 {sql error: no such function: fetch_db_no_such_function}}

do_catchsql_test 3.2 {
  CREATE VIRTUAL TABLE temp.xyz USING swarmvtab(
    'VALUES
        ("test.db1", "t1", 1, 10),
        ("test.db2", "t1", 11, 20)
    ', 'fetch_db'

#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is the "swarmvtab" extension
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix swarmvtab
set testprefix swarmvtab2
do_not_use_codec

ifcapable !vtab {
  finish_test
  return
}

# 2017-07-15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    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 implements regression tests for SQLite library.  The
# focus of this file is the "swarmvtab" extension
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix swarmvtab3
do_not_use_codec

ifcapable !vtab {
  finish_test
  return
}

load_static_extension db unionvtab

set nFile $sqlite_open_file_count

do_execsql_test 1.0 {
  CREATE TEMP TABLE swarm(id, tbl, minval, maxval);
}

# Set up 100 databases with filenames "remote_test.dbN", where N is between
# 0 and 99.
do_test 1.1 {
  for {set i 0} {$i < 100} {incr i} {
    set file remote_test.db$i
    forcedelete $file
    forcedelete test.db$i
    sqlite3 rrr $file
    rrr eval {
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
      INSERT INTO t1 VALUES($i, $i);
    }
    rrr close
    db eval {
      INSERT INTO swarm VALUES($i, 't1', $i, $i);
    }
    set ::dbcache(test.db$i) 0
  }
} {}

proc missing_db {filename} {
  set remote "remote_$filename"
  forcedelete $filename
  file copy $remote $filename
}
db func missing_db missing_db

proc openclose_db {filename bClose} {
  if {$bClose} {
    incr ::dbcache($filename) -1
  } else {
    incr ::dbcache($filename) 1
  }
  if {$::dbcache($filename)==0} {
    forcedelete $filename
  }
}
db func openclose_db openclose_db

proc check_dbcache {} {
  set n 0
  for {set i 0} {$i<100} {incr i} {
    set exists [file exists test.db$i]
    if {$exists!=($::dbcache(test.db$i)!=0)} {
      error "inconsistent ::dbcache and disk"
    }
    incr n $exists
  }
  return $n
}

foreach {tn nMaxOpen cvt} {
  1 5 {
    CREATE VIRTUAL TABLE temp.s USING swarmvtab(
        'SELECT :prefix || id, tbl, minval, minval FROM swarm',
        :prefix='test.db',
        missing=missing_db,
        openclose=openclose_db,
        maxopen=5
    )
  }

  2 3 {
    CREATE VIRTUAL TABLE temp.s USING swarmvtab(
        'SELECT :prefix || id, tbl, minval, minval FROM swarm',
        :prefix='test.db',
        missing =       'missing_db',
        openclose=[openclose_db],
        maxopen = 3
    )
  }

  3 1 {
    CREATE VIRTUAL TABLE temp.s USING swarmvtab(
        'SELECT :prefix||''.''||:suffix||id, tbl, minval, minval FROM swarm',
        :prefix=test, :suffix=db,
        missing =       'missing_db',
        openclose=[openclose_db],
        maxopen = 1
    )
  }

} {
  execsql { DROP TABLE IF EXISTS s }
  
  do_execsql_test 1.$tn.1 $cvt

  do_execsql_test 1.$tn.2 {
    SELECT b FROM s WHERE a<10;
  } {0 1 2 3 4 5 6 7 8 9}

  do_test 1.$tn.3 { check_dbcache } $nMaxOpen

  do_execsql_test 1.$tn.4 {
    SELECT b FROM s WHERE (b%10)=0;
  } {0 10 20 30 40 50 60 70 80 90}

  do_test 1.$tn.5 { check_dbcache } $nMaxOpen
}

execsql { DROP TABLE IF EXISTS s }
for {set i 0} {$i < 100} {incr i} {
  forcedelete remote_test.db$i
}

#----------------------------------------------------------------------------
#
do_execsql_test 2.0 {
  DROP TABLE IF EXISTS swarm;
  CREATE TEMP TABLE swarm(file, tbl, minval, maxval, ctx);
}

catch { array unset ::dbcache }

# Set up 100 databases with filenames "remote_test.dbN", where N is a
# random integer between 0 and 1,000,000
# 0 and 99.
do_test 2.1 {
  for {set i 0} {$i < 100} {incr i} {
    while 1 {
      set ctx [expr abs(int(rand() *1000000))]
      if {[info exists ::dbcache($ctx)]==0} break
    }

    set file test_remote.db$ctx
    forcedelete $file
    forcedelete test.db$i
    sqlite3 rrr $file
    rrr eval {
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
      INSERT INTO t1 VALUES($i, $i);
    }
    rrr close
    db eval {
      INSERT INTO swarm VALUES('test.db' || $i, 't1', $i, $i, $file)
    }
    set ::dbcache(test.db$i) 0
  }
} {}

proc missing_db {filename ctx} {
  file copy $ctx $filename
}
db func missing_db missing_db

proc openclose_db {filename ctx bClose} {
  if {$bClose} {
    incr ::dbcache($filename) -1
  } else {
    incr ::dbcache($filename) 1
  }
  if {$::dbcache($filename)==0} {
    forcedelete $filename
  }
}
db func openclose_db openclose_db

proc check_dbcache {} {
  set n 0
  foreach k [array names ::dbcache] {
    set exists [file exists $k]
    if {$exists!=($::dbcache($k)!=0)} {
      error "inconsistent ::dbcache and disk ($k)"
    }
    incr n $exists
  }
  return $n
}

foreach {tn nMaxOpen cvt} {
  2 5 {
    CREATE VIRTUAL TABLE temp.s USING swarmvtab(
        'SELECT file, tbl, minval, minval, ctx FROM swarm',
        missing=missing_db,
        openclose=openclose_db,
        maxopen=5
    )
  }
} {
  execsql { DROP TABLE IF EXISTS s }
  
  do_execsql_test 1.$tn.1 $cvt

  do_execsql_test 1.$tn.2 {
    SELECT b FROM s WHERE a<10;
  } {0 1 2 3 4 5 6 7 8 9}

  do_test 1.$tn.3 { check_dbcache } $nMaxOpen

  do_execsql_test 1.$tn.4 {
    SELECT b FROM s WHERE (b%10)=0;
  } {0 10 20 30 40 50 60 70 80 90}

  do_test 1.$tn.5 { check_dbcache } $nMaxOpen
}

db close
forcedelete {*}[glob test_remote.db*]

finish_test