SQLite

*/
#ifndef LARGEST_INT64
# define LARGEST_INT64  (0xffffffff|(((sqlite3_int64)0x7fffffff)<<32))
#endif
#ifndef SMALLEST_INT64
# define SMALLEST_INT64 (((sqlite3_int64)-1) - LARGEST_INT64)
#endif

#define SWARMVTAB_MAX_ATTACHED 9

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
** UnionTab.aSrc[] array.
*/
struct UnionSrc {
  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;                    /* File to ATTACH */
  int bAttached;                  /* True if currently attached */
  UnionSrc *pNextAttached;        /* Next in list of all attached 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 */
  sqlite3_stmt *pSourceStr;       /* Used by unionSourceToStr() */
  UnionSrc *aSrc;                 /* Array of source tables, sorted by rowid */

  /* Used by swarmvtab only */
  char *zSourceStr;               /* Expected unionSourceToStr() value */
  UnionSrc *pAttached;            /* First in list of attached sources */
  int nAttach;                    /* Current number of attached sources */
  int nMaxAttach;                 /* Maximum number of attached sources */
};

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

  /* Used by swarmvtab only */
  sqlite3_int64 iMaxRowid;        /* Last rowid to visit */
  int iTab;                       /* Index of table read by pStmt */
};

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

  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 ){
      *pzErr = sqlite3_mprintf("sql error: %s", sqlite3_errmsg(db));
      *pRc = rc;
    }
  }
  return pRet;
}

** 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;
}

static int unionDetachDatabase(UnionTab *pTab, char **pzErr){
  sqlite3_stmt *pStmt = 0;
  int rc = SQLITE_OK;
  UnionSrc **pp;
  assert( pTab->pAttached );

  pp = &pTab->pAttached;
  while( (*pp)->pNextAttached ){
    pp = &(*pp)->pNextAttached;
  }

  pStmt = unionPreparePrintf(&rc, pzErr, pTab->db, "DETACH %s", (*pp)->zDb);
  if( rc==SQLITE_OK ) sqlite3_step(pStmt);
  unionFinalize(&rc, pStmt);
  assert( rc==SQLITE_OK );
  if( rc==SQLITE_OK ){
    assert( (*pp)->bAttached && (*pp)->pNextAttached==0 );
    (*pp)->bAttached = 0;
    *pp = 0;
    pTab->nAttach--;
  }

  return rc;
}

/*
** xDisconnect method.
*/
static int unionDisconnect(sqlite3_vtab *pVtab){
  if( pVtab ){
    UnionTab *pTab = (UnionTab*)pVtab;
    int i;
    for(i=0; i<pTab->nSrc; i++){
      sqlite3_free(pTab->aSrc[i].zDb);
      sqlite3_free(pTab->aSrc[i].zTab);
      sqlite3_free(pTab->aSrc[i].zFile);
    }
    while( pTab->pAttached ){
      if( unionDetachDatabase(pTab, 0)!=SQLITE_OK ) break;
    }
    sqlite3_finalize(pTab->pSourceStr);
    sqlite3_free(pTab->zSourceStr);
    sqlite3_free(pTab->aSrc);
    sqlite3_free(pTab);
  }
  return SQLITE_OK;
}

/*
** Check that the table identified by pSrc is a rowid table. If not,
** return SQLITE_ERROR and set (*pzErr) to point to an English language
** error message. If the table is a rowid table and no error occurs,
** return SQLITE_OK and leave (*pzErr) unmodified.
*/
static int unionIsIntkeyTable(
  sqlite3 *db,                    /* Database handle */
  UnionSrc *pSrc,                 /* Source table to test */
  char **pzErr                    /* OUT: Error message */
){
  int bPk = 0;
  const char *zType = 0;
  int rc;

  sqlite3_table_column_metadata(
      db, pSrc->zDb, pSrc->zTab, "_rowid_", &zType, 0, 0, &bPk, 0
  );
  rc = sqlite3_errcode(db);
  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
    );
  }
  return rc;
}

/*
** 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 */
  UnionTab *pTab,                 /* Virtual table object */
  UnionSrc *pSrc,                 /* Source table to test */

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



    int rc = unionIsIntkeyTable(pTab->db, pSrc, pzErr);




    if( rc==SQLITE_OK && pTab->pSourceStr==0 ){






      pTab->pSourceStr = unionPrepare(&rc, pTab->db, 
        "SELECT group_concat(quote(name) || '.' || quote(type)) "
        "FROM pragma_table_info(?, ?)", pzErr
      );
    }

    if( rc==SQLITE_OK ){
      sqlite3_bind_text(pTab->pSourceStr, 1, pSrc->zTab, -1, SQLITE_STATIC);
      sqlite3_bind_text(pTab->pSourceStr, 2, pSrc->zDb, -1, SQLITE_STATIC);
      if( SQLITE_ROW==sqlite3_step(pTab->pSourceStr) ){
        const char *z = (const char*)sqlite3_column_text(pTab->pSourceStr, 0);
        zRet = unionStrdup(&rc, z);
      }
      unionReset(&rc, pTab->pSourceStr, 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){



  int rc = SQLITE_OK;

  assert( *pzErr==0 );
  if( pTab->nSrc==0 ){
    *pzErr = sqlite3_mprintf("no source tables configured");
    rc = SQLITE_ERROR;
  }else{

    char *z0 = 0;
    int i;



    z0 = unionSourceToStr(&rc, pTab, &pTab->aSrc[0], pzErr);

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

    unionFinalize(&rc, pTab->pSourceStr);
    pTab->pSourceStr = 0;
    sqlite3_free(z0);
  }
  return rc;
}

static int unionAttachDatabase(UnionTab *pTab, int iSrc, char **pzErr){
  int rc = SQLITE_OK;
  UnionSrc *pSrc = &pTab->aSrc[iSrc];

  assert( pTab->bSwarm && iSrc<pTab->nSrc );
  if( pSrc->bAttached==0 ){
    sqlite3_stmt *pStmt;

    if( pTab->nAttach>=pTab->nMaxAttach ){
      rc = unionDetachDatabase(pTab, pzErr);
    }

    pStmt = unionPreparePrintf(
        &rc, pzErr, pTab->db, "ATTACH %Q AS %s", pSrc->zFile, pSrc->zDb
    );
    if( rc==SQLITE_OK ){
      sqlite3_step(pStmt);
      rc = sqlite3_finalize(pStmt);
    }
    if( rc!=SQLITE_OK ){
      *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db));
    }else{
      char *z = unionSourceToStr(&rc, pTab, pSrc, pzErr);
      if( rc==SQLITE_OK ){
        if( pTab->zSourceStr==0 ){
          pTab->zSourceStr = z;
        }else{
          if( sqlite3_stricmp(z, pTab->zSourceStr) ){
            *pzErr = sqlite3_mprintf("source table schema mismatch");
            rc = SQLITE_ERROR;
          }
          sqlite3_free(z);
        }
      }
    }

    pSrc->pNextAttached = pTab->pAttached;
    pSrc->bAttached = 1;
    pTab->pAttached = pSrc;
    pTab->nAttach++;
  }

  return rc;
}

/* 
** xConnect/xCreate method.
**
** The argv[] array contains the following:

  void *pAux,
  int argc, const char *const*argv,
  sqlite3_vtab **ppVtab,
  char **pzErr
){
  UnionTab *pTab = 0;
  int rc = SQLITE_OK;
  int bSwarm = (pAux==0 ? 0 : 1);
  const char *zVtab = (bSwarm ? "swarmvtab" : "unionvtab");
  const char *zName = argv[2];


  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 ){
    *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

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

      if( rc==SQLITE_OK ){
        pSrc = &pTab->aSrc[pTab->nSrc++];

        pSrc->zTab = unionStrdup(&rc, zTab);
        pSrc->iMin = iMin;
        pSrc->iMax = iMax;
        if( bSwarm ){
          pSrc->zFile = unionStrdup(&rc, zDb);
          pSrc->zDb = sqlite3_mprintf("swm_%s_%d", zName, pTab->nSrc);
          if( pSrc->zDb==0 ) rc = SQLITE_NOMEM;
        }else{
          pSrc->zDb = unionStrdup(&rc, zDb);
          pSrc->bAttached = 1;
        }
      }
    }
    unionFinalize(&rc, pStmt);
    pStmt = 0;

    /* 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->nMaxAttach = SWARMVTAB_MAX_ATTACHED;
      if( bSwarm ){
        rc = unionAttachDatabase(pTab, 0, pzErr);
      }else{
        rc = unionSourceCheck(pTab, pzErr);
      }
    }

    /* Compose a CREATE TABLE statement and pass it to declare_vtab() */
    if( rc==SQLITE_OK ){
      pStmt = unionPreparePrintf(&rc, pzErr, db, "SELECT "
          "'CREATE TABLE xyz('"
          "    || group_concat(quote(name) || ' ' || type, ', ')"

  return SQLITE_OK;
}


/*
** xNext
*/

static int doUnionNext(UnionCsr *pCsr){
  int rc = SQLITE_OK;
  assert( pCsr->pStmt );
  if( sqlite3_step(pCsr->pStmt)!=SQLITE_ROW ){
    UnionTab *pTab = (UnionTab*)pCsr->base.pVtab;
    rc = sqlite3_finalize(pCsr->pStmt);
    pCsr->pStmt = 0;
    if( rc==SQLITE_OK && pTab->bSwarm ){
      pCsr->iTab++;
      if( pCsr->iTab<pTab->nSrc ){
        UnionSrc *pSrc = &pTab->aSrc[pCsr->iTab];
        if( pCsr->iMaxRowid>=pSrc->iMin ){
          /* It is necessary to scan the next table. */
          rc = unionAttachDatabase(pTab, pCsr->iTab, &pTab->base.zErrMsg);
          pCsr->pStmt = unionPreparePrintf(&rc, &pTab->base.zErrMsg, pTab->db,
              "SELECT rowid, * FROM %Q.%Q %s %lld",
              pSrc->zDb, pSrc->zTab,
              (pSrc->iMax>pCsr->iMaxRowid ? "WHERE _rowid_ <=" : "-- "),
              pCsr->iMaxRowid
          );
          if( rc==SQLITE_OK ) rc = SQLITE_ROW;
        }
      }
    }
  }

  return rc;
}

static int unionNext(sqlite3_vtab_cursor *cur){
  int rc;
  do {
    rc = doUnionNext((UnionCsr*)cur);
  }while( rc==SQLITE_ROW );
  return rc;
}

/*
** xColumn
*/
static int unionColumn(

        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( pTab->bSwarm ){
      pCsr->iTab = i;
      pCsr->iMaxRowid = iMax;
      rc = unionAttachDatabase(pTab, i, &pTab->base.zErrMsg);
      break;
    }
  }


  if( zSql==0 ) return rc;
  pCsr->pStmt = unionPrepare(&rc, pTab->db, zSql, &pTab->base.zErrMsg);
  sqlite3_free(zSql);
  if( rc!=SQLITE_OK ) return rc;

    0,                            /* xRollback */
    0,                            /* xFindMethod */
    0,                            /* xRename */
    0,                            /* xSavepoint */
    0,                            /* xRelease */
    0                             /* xRollbackTo */
  };
  int rc;

  rc = sqlite3_create_module(db, "unionvtab", &unionModule, 0);
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_module(db, "swarmvtab", &unionModule, (void*)db);
  }
  return rc;
}

#endif /* SQLITE_OMIT_VIRTUALTABLE */

#ifdef _WIN32
__declspec(dllexport)
#endif

# 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 swarmvtab

ifcapable !vtab {
  finish_test
  return
}

load_static_extension db unionvtab

do_execsql_test 1.0 {
  CREATE TABLE t0(a INTEGER PRIMARY KEY, b TEXT);
  WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<400) 
  INSERT INTO t0 SELECT i, hex(randomblob(50)) FROM s;

  CREATE TABLE dir(f, t, imin, imax);
}

do_test 1.1 {
  for {set i 0} {$i < 40} {incr i} {
    set iMin [expr $i*10 + 1]
    set iMax [expr $iMin+9]

    forcedelete "test.db$i"
    execsql [subst {
      ATTACH 'test.db$i' AS aux;
      CREATE TABLE aux.t$i (a INTEGER PRIMARY KEY, b TEXT);
      INSERT INTO aux.t$i SELECT * FROM t0 WHERE a BETWEEN $iMin AND $iMax;
      DETACH aux;
      INSERT INTO dir VALUES('test.db$i', 't$i', $iMin, $iMax);
    }]
  }

  execsql {
    CREATE VIRTUAL TABLE temp.s1 USING swarmvtab('SELECT * FROM dir');
    SELECT count(*) FROM pragma_database_list;
  }
} {3}

do_execsql_test 1.2 { 
  DROP TABLE s1; 
  SELECT name FROM pragma_database_list;
} {main temp}

do_execsql_test 1.3 {
  CREATE VIRTUAL TABLE temp.s1 USING swarmvtab('SELECT * FROM dir');
  SELECT count(*) FROM s1 WHERE rowid<50;
} {49}

proc do_compare_test {tn where} {
  set sql [subst {
    SELECT (
        SELECT group_concat(a || ',' || b, ',') FROM t0 WHERE $where
    )==(
        SELECT group_concat(a || ',' || b, ',') FROM s1 WHERE $where
    )
  }]

  uplevel [list do_execsql_test $tn $sql 1]
}

do_compare_test 1.4 "rowid = 55"
do_compare_test 1.5 "rowid BETWEEN 20 AND 100"
do_compare_test 1.6 "rowid > 350"
do_compare_test 1.7 "rowid >= 350"

# The following both each an assert() in SQLite:
#
#do_compare_test 1.8 "rowid >= 200"
#do_test 1.x { db close } {}
#
do_execsql_test 1.x { DROP TABLE s1 }


finish_test