/ Check-in [233b3338]
Login

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

Overview
Comment:Add a prototype intarray($PTR,$N) table valued function.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | prototype-int-array
Files: files | file ages | folders
SHA1: 233b33382dc70de45f90b6dfdb5785f20b21489e
User & Date: drh 2016-06-29 05:00:30
Context
2016-06-29
05:08
Another test case for the intarray($PTR,$N) virtual table. check-in: 06e1fab7 user: drh tags: prototype-int-array
05:00
Add a prototype intarray($PTR,$N) table valued function. check-in: 233b3338 user: drh tags: prototype-int-array
2016-06-26
04:06
Prevent the WhereLoop.rSetup cost estimate from going negative on complex queries. check-in: f8105085 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to Makefile.in.

   412    412     $(TOP)/ext/session/test_session.c \
   413    413     $(TOP)/ext/rbu/test_rbu.c 
   414    414   
   415    415   # Statically linked extensions
   416    416   #
   417    417   TESTSRC += \
   418    418     $(TOP)/ext/misc/amatch.c \
          419  +  $(TOP)/ext/misc/array.c \
   419    420     $(TOP)/ext/misc/closure.c \
   420    421     $(TOP)/ext/misc/csv.c \
   421    422     $(TOP)/ext/misc/eval.c \
   422    423     $(TOP)/ext/misc/fileio.c \
   423    424     $(TOP)/ext/misc/fuzzer.c \
   424    425     $(TOP)/ext/fts5/fts5_tcl.c \
   425    426     $(TOP)/ext/fts5/fts5_test_mi.c \

Added ext/misc/array.c.

            1  +/*
            2  +** 2016-06-29
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +**
           13  +** This file demonstrates how to create a table-valued-function that
           14  +** returns the values in a C-language array.
           15  +** Examples:
           16  +**
           17  +**      SELECT * FROM intarray($ptr,5)
           18  +**
           19  +** The query above returns 5 integers contained in a C-language array
           20  +** at the address $ptr.  $ptr is a pointer to the array of integers that
           21  +** has been cast to an integer.
           22  +**
           23  +** HOW IT WORKS
           24  +**
           25  +** The intarray "function" is really a virtual table with the
           26  +** following schema:
           27  +**
           28  +**     CREATE FUNCTION intarray(
           29  +**       value,
           30  +**       pointer HIDDEN,
           31  +**       count HIDDEN
           32  +**     );
           33  +*/
           34  +#include "sqlite3ext.h"
           35  +SQLITE_EXTENSION_INIT1
           36  +#include <assert.h>
           37  +#include <string.h>
           38  +
           39  +#ifndef SQLITE_OMIT_VIRTUALTABLE
           40  +
           41  +
           42  +/* intarray_cursor is a subclass of sqlite3_vtab_cursor which will
           43  +** serve as the underlying representation of a cursor that scans
           44  +** over rows of the result
           45  +*/
           46  +typedef struct intarray_cursor intarray_cursor;
           47  +struct intarray_cursor {
           48  +  sqlite3_vtab_cursor base;  /* Base class - must be first */
           49  +  int isDesc;                /* True to count down rather than up */
           50  +  sqlite3_int64 iRowid;      /* The rowid */
           51  +  sqlite3_int64 iPtr;        /* Pointer to array of integers */
           52  +  sqlite3_int64 iCnt;        /* Number of integers in the array */
           53  +};
           54  +
           55  +/*
           56  +** The intarrayConnect() method is invoked to create a new
           57  +** intarray_vtab that describes the intarray virtual table.
           58  +**
           59  +** Think of this routine as the constructor for intarray_vtab objects.
           60  +**
           61  +** All this routine needs to do is:
           62  +**
           63  +**    (1) Allocate the intarray_vtab object and initialize all fields.
           64  +**
           65  +**    (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
           66  +**        result set of queries against intarray will look like.
           67  +*/
           68  +static int intarrayConnect(
           69  +  sqlite3 *db,
           70  +  void *pAux,
           71  +  int argc, const char *const*argv,
           72  +  sqlite3_vtab **ppVtab,
           73  +  char **pzErr
           74  +){
           75  +  sqlite3_vtab *pNew;
           76  +  int rc;
           77  +
           78  +/* Column numbers */
           79  +#define INTARRAY_COLUMN_VALUE   0
           80  +#define INTARRAY_COLUMN_POINTER 1
           81  +#define INTARRAY_COLUMN_COUNT   2
           82  +
           83  +  rc = sqlite3_declare_vtab(db,
           84  +     "CREATE TABLE x(value,pointer hidden,count hidden)");
           85  +  if( rc==SQLITE_OK ){
           86  +    pNew = *ppVtab = sqlite3_malloc( sizeof(*pNew) );
           87  +    if( pNew==0 ) return SQLITE_NOMEM;
           88  +    memset(pNew, 0, sizeof(*pNew));
           89  +  }
           90  +  return rc;
           91  +}
           92  +
           93  +/*
           94  +** This method is the destructor for intarray_cursor objects.
           95  +*/
           96  +static int intarrayDisconnect(sqlite3_vtab *pVtab){
           97  +  sqlite3_free(pVtab);
           98  +  return SQLITE_OK;
           99  +}
          100  +
          101  +/*
          102  +** Constructor for a new intarray_cursor object.
          103  +*/
          104  +static int intarrayOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
          105  +  intarray_cursor *pCur;
          106  +  pCur = sqlite3_malloc( sizeof(*pCur) );
          107  +  if( pCur==0 ) return SQLITE_NOMEM;
          108  +  memset(pCur, 0, sizeof(*pCur));
          109  +  *ppCursor = &pCur->base;
          110  +  return SQLITE_OK;
          111  +}
          112  +
          113  +/*
          114  +** Destructor for a intarray_cursor.
          115  +*/
          116  +static int intarrayClose(sqlite3_vtab_cursor *cur){
          117  +  sqlite3_free(cur);
          118  +  return SQLITE_OK;
          119  +}
          120  +
          121  +
          122  +/*
          123  +** Advance a intarray_cursor to its next row of output.
          124  +*/
          125  +static int intarrayNext(sqlite3_vtab_cursor *cur){
          126  +  intarray_cursor *pCur = (intarray_cursor*)cur;
          127  +  pCur->iRowid++;
          128  +  return SQLITE_OK;
          129  +}
          130  +
          131  +/*
          132  +** Return values of columns for the row at which the intarray_cursor
          133  +** is currently pointing.
          134  +*/
          135  +static int intarrayColumn(
          136  +  sqlite3_vtab_cursor *cur,   /* The cursor */
          137  +  sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
          138  +  int i                       /* Which column to return */
          139  +){
          140  +  intarray_cursor *pCur = (intarray_cursor*)cur;
          141  +  sqlite3_int64 x = 0;
          142  +  switch( i ){
          143  +    case INTARRAY_COLUMN_POINTER:   x = pCur->iPtr;   break;
          144  +    case INTARRAY_COLUMN_COUNT:     x = pCur->iCnt;   break;
          145  +    default: {
          146  +      int *p = (int*)pCur->iPtr;
          147  +      x = (int)p[pCur->iRowid-1];
          148  +      break;
          149  +    }
          150  +  }
          151  +  sqlite3_result_int64(ctx, x);
          152  +  return SQLITE_OK;
          153  +}
          154  +
          155  +/*
          156  +** Return the rowid for the current row.  In this implementation, the
          157  +** rowid is the same as the output value.
          158  +*/
          159  +static int intarrayRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          160  +  intarray_cursor *pCur = (intarray_cursor*)cur;
          161  +  *pRowid = pCur->iRowid;
          162  +  return SQLITE_OK;
          163  +}
          164  +
          165  +/*
          166  +** Return TRUE if the cursor has been moved off of the last
          167  +** row of output.
          168  +*/
          169  +static int intarrayEof(sqlite3_vtab_cursor *cur){
          170  +  intarray_cursor *pCur = (intarray_cursor*)cur;
          171  +  return pCur->iRowid>=pCur->iCnt;
          172  +}
          173  +
          174  +/*
          175  +** This method is called to "rewind" the intarray_cursor object back
          176  +** to the first row of output.
          177  +*/
          178  +static int intarrayFilter(
          179  +  sqlite3_vtab_cursor *pVtabCursor, 
          180  +  int idxNum, const char *idxStr,
          181  +  int argc, sqlite3_value **argv
          182  +){
          183  +  intarray_cursor *pCur = (intarray_cursor *)pVtabCursor;
          184  +  int i = 0;
          185  +  if( idxNum ){
          186  +    pCur->iPtr = sqlite3_value_int64(argv[0]);
          187  +    pCur->iCnt = sqlite3_value_int64(argv[1]);
          188  +  }else{
          189  +    pCur->iPtr = 0;
          190  +    pCur->iCnt = 0;
          191  +  }
          192  +  pCur->iRowid = 1;
          193  +  return SQLITE_OK;
          194  +}
          195  +
          196  +/*
          197  +** SQLite will invoke this method one or more times while planning a query
          198  +** that uses the intarray virtual table.  This routine needs to create
          199  +** a query plan for each invocation and compute an estimated cost for that
          200  +** plan.
          201  +**
          202  +** In this implementation idxNum is used to represent the
          203  +** query plan.  idxStr is unused.
          204  +**
          205  +** idxNum is 1 if the pointer= and count= constraints exist and is 0 otherwise.
          206  +** If idxNum is 0, then intarray becomes an empty table.
          207  +*/
          208  +static int intarrayBestIndex(
          209  +  sqlite3_vtab *tab,
          210  +  sqlite3_index_info *pIdxInfo
          211  +){
          212  +  int i;                 /* Loop over constraints */
          213  +  int idxNum = 0;        /* The query plan bitmask */
          214  +  int ptrIdx = -1;       /* Index of the pointer= constraint, or -1 if none */
          215  +  int cntIdx = -1;       /* Index of the count= constraint, or -1 if none */
          216  +  int nArg = 0;          /* Number of arguments that intarrayFilter() expects */
          217  +
          218  +  const struct sqlite3_index_constraint *pConstraint;
          219  +  pConstraint = pIdxInfo->aConstraint;
          220  +  for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
          221  +    if( pConstraint->usable==0 ) continue;
          222  +    if( pConstraint->op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue;
          223  +    switch( pConstraint->iColumn ){
          224  +      case INTARRAY_COLUMN_POINTER:
          225  +        ptrIdx = i;
          226  +        break;
          227  +      case INTARRAY_COLUMN_COUNT:
          228  +        cntIdx = i;
          229  +        break;
          230  +    }
          231  +  }
          232  +  if( ptrIdx>=0 && cntIdx>=0 ){
          233  +    pIdxInfo->aConstraintUsage[ptrIdx].argvIndex = 1;
          234  +    pIdxInfo->aConstraintUsage[ptrIdx].omit = 1;
          235  +    pIdxInfo->aConstraintUsage[cntIdx].argvIndex = 2;
          236  +    pIdxInfo->aConstraintUsage[cntIdx].omit = 1;
          237  +    pIdxInfo->estimatedCost = (double)1;
          238  +    pIdxInfo->estimatedRows = (double)100;
          239  +    pIdxInfo->idxNum = 1;
          240  +  }else{
          241  +    pIdxInfo->estimatedCost = (double)2147483647;
          242  +    pIdxInfo->estimatedRows = (double)2147483647;
          243  +    pIdxInfo->idxNum = 0;
          244  +  }
          245  +  return SQLITE_OK;
          246  +}
          247  +
          248  +/*
          249  +** This following structure defines all the methods for the 
          250  +** intarray virtual table.
          251  +*/
          252  +static sqlite3_module intarrayModule = {
          253  +  0,                         /* iVersion */
          254  +  0,                         /* xCreate */
          255  +  intarrayConnect,           /* xConnect */
          256  +  intarrayBestIndex,         /* xBestIndex */
          257  +  intarrayDisconnect,        /* xDisconnect */
          258  +  0,                         /* xDestroy */
          259  +  intarrayOpen,              /* xOpen - open a cursor */
          260  +  intarrayClose,             /* xClose - close a cursor */
          261  +  intarrayFilter,            /* xFilter - configure scan constraints */
          262  +  intarrayNext,              /* xNext - advance a cursor */
          263  +  intarrayEof,               /* xEof - check for end of scan */
          264  +  intarrayColumn,            /* xColumn - read data */
          265  +  intarrayRowid,             /* xRowid - read data */
          266  +  0,                         /* xUpdate */
          267  +  0,                         /* xBegin */
          268  +  0,                         /* xSync */
          269  +  0,                         /* xCommit */
          270  +  0,                         /* xRollback */
          271  +  0,                         /* xFindMethod */
          272  +  0,                         /* xRename */
          273  +};
          274  +
          275  +#endif /* SQLITE_OMIT_VIRTUALTABLE */
          276  +
          277  +#ifdef _WIN32
          278  +__declspec(dllexport)
          279  +#endif
          280  +int sqlite3_array_init(
          281  +  sqlite3 *db, 
          282  +  char **pzErrMsg, 
          283  +  const sqlite3_api_routines *pApi
          284  +){
          285  +  int rc = SQLITE_OK;
          286  +  SQLITE_EXTENSION_INIT2(pApi);
          287  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          288  +  if( sqlite3_libversion_number()<3008012 ){
          289  +    *pzErrMsg = sqlite3_mprintf(
          290  +        "intarray() requires SQLite 3.8.12 or later");
          291  +    return SQLITE_ERROR;
          292  +  }
          293  +  rc = sqlite3_create_module(db, "intarray", &intarrayModule, 0);
          294  +#endif
          295  +  return rc;
          296  +}

Changes to src/test1.c.

  3236   3236     if( rc!=SQLITE_OK ){
  3237   3237       return TCL_ERROR;
  3238   3238     }
  3239   3239   
  3240   3240     return TCL_OK;
  3241   3241   }
  3242   3242   
         3243  +
         3244  +/*
         3245  +** Usage:   sqlite3_bind_intarray  STMT N INT  ...
         3246  +**
         3247  +** Create a C-language array of integers from the arguments.  Bind a pointer
         3248  +** to this array to the NAME parameter of STMT.
         3249  +*/
         3250  +static int test_bind_intarray(
         3251  +  void * clientData,
         3252  +  Tcl_Interp *interp,
         3253  +  int objc,
         3254  +  Tcl_Obj *CONST objv[]
         3255  +){
         3256  +  sqlite3_stmt *pStmt;
         3257  +  int idx;
         3258  +  int i;
         3259  +  static int *p = 0;
         3260  +
         3261  +  sqlite3_free(p);
         3262  +  p = 0;
         3263  +  if( objc<4 ){
         3264  +    Tcl_AppendResult(interp, "wrong # args: should be \"",
         3265  +        Tcl_GetStringFromObj(objv[0], 0), " STMT NAME INT...", 0);
         3266  +    return TCL_ERROR;
         3267  +  }
         3268  +
         3269  +  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
         3270  +  if( Tcl_GetIntFromObj(interp, objv[2], &idx) ) return TCL_ERROR;
         3271  +  p = sqlite3_malloc( sizeof(int)*(objc-3) );
         3272  +  if( p==0 ) return TCL_ERROR;
         3273  +  for(i=0; i<objc-3; i++){
         3274  +    if( Tcl_GetIntFromObj(interp, objv[3+i], &p[i]) ){
         3275  +      sqlite3_free(p);
         3276  +      return TCL_ERROR;
         3277  +    }
         3278  +  }  
         3279  +  sqlite3_bind_int64(pStmt, idx, (sqlite3_int64)p);
         3280  +  return TCL_OK;
         3281  +}
         3282  +
  3243   3283   
  3244   3284   /*
  3245   3285   ** Usage:   sqlite3_bind_int64  STMT N VALUE
  3246   3286   **
  3247   3287   ** Test the sqlite3_bind_int64 interface.  STMT is a prepared statement.
  3248   3288   ** N is the index of a wildcard in the prepared statement.  This command
  3249   3289   ** binds a 64-bit integer VALUE to that wildcard.
................................................................................
  6579   6619   static int tclLoadStaticExtensionCmd(
  6580   6620     void * clientData,
  6581   6621     Tcl_Interp *interp,
  6582   6622     int objc,
  6583   6623     Tcl_Obj *CONST objv[]
  6584   6624   ){
  6585   6625     extern int sqlite3_amatch_init(sqlite3*,char**,const sqlite3_api_routines*);
         6626  +  extern int sqlite3_array_init(sqlite3*,char**,const sqlite3_api_routines*);
  6586   6627     extern int sqlite3_closure_init(sqlite3*,char**,const sqlite3_api_routines*);
  6587   6628     extern int sqlite3_csv_init(sqlite3*,char**,const sqlite3_api_routines*);
  6588   6629     extern int sqlite3_eval_init(sqlite3*,char**,const sqlite3_api_routines*);
  6589   6630     extern int sqlite3_fileio_init(sqlite3*,char**,const sqlite3_api_routines*);
  6590   6631     extern int sqlite3_fuzzer_init(sqlite3*,char**,const sqlite3_api_routines*);
  6591   6632     extern int sqlite3_ieee_init(sqlite3*,char**,const sqlite3_api_routines*);
  6592   6633     extern int sqlite3_nextchar_init(sqlite3*,char**,const sqlite3_api_routines*);
................................................................................
  6597   6638     extern int sqlite3_totype_init(sqlite3*,char**,const sqlite3_api_routines*);
  6598   6639     extern int sqlite3_wholenumber_init(sqlite3*,char**,const sqlite3_api_routines*);
  6599   6640     static const struct {
  6600   6641       const char *zExtName;
  6601   6642       int (*pInit)(sqlite3*,char**,const sqlite3_api_routines*);
  6602   6643     } aExtension[] = {
  6603   6644       { "amatch",                sqlite3_amatch_init               },
         6645  +    { "array",                 sqlite3_array_init                },
  6604   6646       { "closure",               sqlite3_closure_init              },
  6605   6647       { "csv",                   sqlite3_csv_init                  },
  6606   6648       { "eval",                  sqlite3_eval_init                 },
  6607   6649       { "fileio",                sqlite3_fileio_init               },
  6608   6650       { "fuzzer",                sqlite3_fuzzer_init               },
  6609   6651       { "ieee754",               sqlite3_ieee_init                 },
  6610   6652       { "nextchar",              sqlite3_nextchar_init             },
................................................................................
  7092   7134        void *clientData;
  7093   7135     } aObjCmd[] = {
  7094   7136        { "sqlite3_db_config",             test_sqlite3_db_config, 0 },
  7095   7137        { "bad_behavior",                  test_bad_behavior,  (void*)&iZero },
  7096   7138        { "register_dbstat_vtab",          test_register_dbstat_vtab  },
  7097   7139        { "sqlite3_connection_pointer",    get_sqlite_pointer, 0 },
  7098   7140        { "sqlite3_bind_int",              test_bind_int,      0 },
         7141  +     { "sqlite3_bind_intarray",         test_bind_intarray, 0 },
  7099   7142        { "sqlite3_bind_zeroblob",         test_bind_zeroblob, 0 },
  7100   7143        { "sqlite3_bind_zeroblob64",       test_bind_zeroblob64, 0 },
  7101   7144        { "sqlite3_bind_int64",            test_bind_int64,    0 },
  7102   7145        { "sqlite3_bind_double",           test_bind_double,   0 },
  7103   7146        { "sqlite3_bind_null",             test_bind_null     ,0 },
  7104   7147        { "sqlite3_bind_text",             test_bind_text     ,0 },
  7105   7148        { "sqlite3_bind_text16",           test_bind_text16   ,0 },

Changes to test/tabfunc01.test.

    18     18   set testprefix tabfunc01
    19     19   
    20     20   ifcapable !vtab {
    21     21     finish_test
    22     22     return
    23     23   }
    24     24   load_static_extension db series
           25  +load_static_extension db array
    25     26   
    26     27   do_execsql_test tabfunc01-1.1 {
    27     28     SELECT *, '|' FROM generate_series WHERE start=1 AND stop=9 AND step=2;
    28     29   } {1 | 3 | 5 | 7 | 9 |}
    29     30   do_execsql_test tabfunc01-1.2 {
    30     31     SELECT *, '|' FROM generate_series LIMIT 5;
    31     32   } {0 | 1 | 2 | 3 | 4 |}
................................................................................
   131    132   # by virtual tables unless omit was set.
   132    133   #
   133    134   do_execsql_test tabfunc01-500 {
   134    135     SELECT * FROM generate_series WHERE start IN (1,7) AND stop=20 AND step=10
   135    136     ORDER BY +1;
   136    137   } {1 7 11 17}
   137    138   
          139  +
          140  +do_test tabfunc01-600 {
          141  +  set TAIL {}
          142  +  set VM [sqlite3_prepare db {SELECT * FROM intarray(?2,?3)} -1 TAIL]
          143  +  set TAIL
          144  +} {}
          145  +do_test tabfunc01-610 {
          146  +  sqlite3_bind_intarray $VM 2 11 22 33 44 55
          147  +  sqlite3_bind_int $VM 3 4
          148  +  sqlite3_step $VM
          149  +} SQLITE_ROW
          150  +do_test tabfunc01-620 {
          151  +  sqlite3_column_int $VM 0
          152  +} 11
          153  +do_test tabfunc01-621 {
          154  +  sqlite3_step $VM
          155  +  sqlite3_column_int $VM 0
          156  +} 22
          157  +sqlite3_finalize $VM
          158  +catch {sqlite3_bind_intarray}
          159  +
   138    160   finish_test