4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
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.
11 *************************************************************************
13 ** This file demonstrates how to create a table-valued-function using
14 ** a virtual table. This demo implements the generate_series() function
15 ** which gives similar results to the eponymous function in PostgreSQL.
18 ** SELECT * FROM generate_series(0,100,5);
20 ** The query above returns integers from 0 through 100 counting by steps
23 ** SELECT * FROM generate_series(0,100);
25 ** Integers from 0 through 100 with a step size of 1.
27 ** SELECT * FROM generate_series(20) LIMIT 10;
29 ** Integers 20 through 29.
33 ** The generate_series "function" is really a virtual table with the
36 ** CREATE TABLE generate_series(
43 ** Function arguments in queries against this virtual table are translated
44 ** into equality constraints against successive hidden columns. In other
45 ** words, the following pairs of queries are equivalent to each other:
47 ** SELECT * FROM generate_series(0,100,5);
48 ** SELECT * FROM generate_series WHERE start=0 AND stop=100 AND step=5;
50 ** SELECT * FROM generate_series(0,100);
51 ** SELECT * FROM generate_series WHERE start=0 AND stop=100;
53 ** SELECT * FROM generate_series(20) LIMIT 10;
54 ** SELECT * FROM generate_series WHERE start=20 LIMIT 10;
56 ** The generate_series virtual table implementation leaves the xCreate method
57 ** set to NULL. This means that it is not possible to do a CREATE VIRTUAL
58 ** TABLE command with "generate_series" as the USING argument. Instead, there
59 ** is a single generate_series virtual table that is always available without
60 ** having to be created first.
62 ** The xBestIndex method looks for equality constraints against the hidden
63 ** start, stop, and step columns, and if present, it uses those constraints
64 ** to bound the sequence of generated values. If the equality constraints
65 ** are missing, it uses 0 for start, 4294967295 for stop, and 1 for step.
66 ** xBestIndex returns a small cost when both start and stop are available,
67 ** and a very large cost if either start or stop are unavailable. This
68 ** encourages the query planner to order joins such that the bounds of the
69 ** series are well-defined.
71 #include "sqlite3ext.h"
72 SQLITE_EXTENSION_INIT1
76 #ifndef SQLITE_OMIT_VIRTUALTABLE
79 /* series_cursor is a subclass of sqlite3_vtab_cursor which will
80 ** serve as the underlying representation of a cursor that scans
81 ** over rows of the result
83 typedef struct series_cursor series_cursor
;
84 struct series_cursor
{
85 sqlite3_vtab_cursor base
; /* Base class - must be first */
86 int isDesc
; /* True to count down rather than up */
87 sqlite3_int64 iRowid
; /* The rowid */
88 sqlite3_int64 iValue
; /* Current value ("value") */
89 sqlite3_int64 mnValue
; /* Mimimum value ("start") */
90 sqlite3_int64 mxValue
; /* Maximum value ("stop") */
91 sqlite3_int64 iStep
; /* Increment ("step") */
95 ** The seriesConnect() method is invoked to create a new
96 ** series_vtab that describes the generate_series virtual table.
98 ** Think of this routine as the constructor for series_vtab objects.
100 ** All this routine needs to do is:
102 ** (1) Allocate the series_vtab object and initialize all fields.
104 ** (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
105 ** result set of queries against generate_series will look like.
107 static int seriesConnect(
110 int argc
, const char *const*argv
,
111 sqlite3_vtab
**ppVtab
,
118 #define SERIES_COLUMN_VALUE 0
119 #define SERIES_COLUMN_START 1
120 #define SERIES_COLUMN_STOP 2
121 #define SERIES_COLUMN_STEP 3
123 rc
= sqlite3_declare_vtab(db
,
124 "CREATE TABLE x(value,start hidden,stop hidden,step hidden)");
126 pNew
= *ppVtab
= sqlite3_malloc( sizeof(*pNew
) );
127 if( pNew
==0 ) return SQLITE_NOMEM
;
128 memset(pNew
, 0, sizeof(*pNew
));
134 ** This method is the destructor for series_cursor objects.
136 static int seriesDisconnect(sqlite3_vtab
*pVtab
){
142 ** Constructor for a new series_cursor object.
144 static int seriesOpen(sqlite3_vtab
*p
, sqlite3_vtab_cursor
**ppCursor
){
146 pCur
= sqlite3_malloc( sizeof(*pCur
) );
147 if( pCur
==0 ) return SQLITE_NOMEM
;
148 memset(pCur
, 0, sizeof(*pCur
));
149 *ppCursor
= &pCur
->base
;
154 ** Destructor for a series_cursor.
156 static int seriesClose(sqlite3_vtab_cursor
*cur
){
163 ** Advance a series_cursor to its next row of output.
165 static int seriesNext(sqlite3_vtab_cursor
*cur
){
166 series_cursor
*pCur
= (series_cursor
*)cur
;
168 pCur
->iValue
-= pCur
->iStep
;
170 pCur
->iValue
+= pCur
->iStep
;
177 ** Return values of columns for the row at which the series_cursor
178 ** is currently pointing.
180 static int seriesColumn(
181 sqlite3_vtab_cursor
*cur
, /* The cursor */
182 sqlite3_context
*ctx
, /* First argument to sqlite3_result_...() */
183 int i
/* Which column to return */
185 series_cursor
*pCur
= (series_cursor
*)cur
;
188 case SERIES_COLUMN_START
: x
= pCur
->mnValue
; break;
189 case SERIES_COLUMN_STOP
: x
= pCur
->mxValue
; break;
190 case SERIES_COLUMN_STEP
: x
= pCur
->iStep
; break;
191 default: x
= pCur
->iValue
; break;
193 sqlite3_result_int64(ctx
, x
);
198 ** Return the rowid for the current row. In this implementation, the
199 ** first row returned is assigned rowid value 1, and each subsequent
200 ** row a value 1 more than that of the previous.
202 static int seriesRowid(sqlite3_vtab_cursor
*cur
, sqlite_int64
*pRowid
){
203 series_cursor
*pCur
= (series_cursor
*)cur
;
204 *pRowid
= pCur
->iRowid
;
209 ** Return TRUE if the cursor has been moved off of the last
212 static int seriesEof(sqlite3_vtab_cursor
*cur
){
213 series_cursor
*pCur
= (series_cursor
*)cur
;
215 return pCur
->iValue
< pCur
->mnValue
;
217 return pCur
->iValue
> pCur
->mxValue
;
221 /* True to cause run-time checking of the start=, stop=, and/or step=
222 ** parameters. The only reason to do this is for testing the
223 ** constraint checking logic for virtual tables in the SQLite core.
225 #ifndef SQLITE_SERIES_CONSTRAINT_VERIFY
226 # define SQLITE_SERIES_CONSTRAINT_VERIFY 0
230 ** This method is called to "rewind" the series_cursor object back
231 ** to the first row of output. This method is always called at least
232 ** once prior to any call to seriesColumn() or seriesRowid() or
235 ** The query plan selected by seriesBestIndex is passed in the idxNum
236 ** parameter. (idxStr is not used in this implementation.) idxNum
237 ** is a bitmask showing which constraints are available:
243 ** Also, if bit 8 is set, that means that the series should be output
244 ** in descending order rather than in ascending order.
246 ** This routine should initialize the cursor and position it so that it
247 ** is pointing at the first row, or pointing off the end of the table
248 ** (so that seriesEof() will return true) if the table is empty.
250 static int seriesFilter(
251 sqlite3_vtab_cursor
*pVtabCursor
,
252 int idxNum
, const char *idxStr
,
253 int argc
, sqlite3_value
**argv
255 series_cursor
*pCur
= (series_cursor
*)pVtabCursor
;
258 pCur
->mnValue
= sqlite3_value_int64(argv
[i
++]);
263 pCur
->mxValue
= sqlite3_value_int64(argv
[i
++]);
265 pCur
->mxValue
= 0xffffffff;
268 pCur
->iStep
= sqlite3_value_int64(argv
[i
++]);
269 if( pCur
->iStep
<1 ) pCur
->iStep
= 1;
273 for(i
=0; i
<argc
; i
++){
274 if( sqlite3_value_type(argv
[i
])==SQLITE_NULL
){
275 /* If any of the constraints have a NULL value, then return no rows.
276 ** See ticket https://www.sqlite.org/src/info/fac496b61722daf2 */
284 pCur
->iValue
= pCur
->mxValue
;
286 pCur
->iValue
-= (pCur
->mxValue
- pCur
->mnValue
)%pCur
->iStep
;
290 pCur
->iValue
= pCur
->mnValue
;
297 ** SQLite will invoke this method one or more times while planning a query
298 ** that uses the generate_series virtual table. This routine needs to create
299 ** a query plan for each invocation and compute an estimated cost for that
302 ** In this implementation idxNum is used to represent the
303 ** query plan. idxStr is unused.
305 ** The query plan is represented by bits in idxNum:
307 ** (1) start = $value -- constraint exists
308 ** (2) stop = $value -- constraint exists
309 ** (4) step = $value -- constraint exists
310 ** (8) output in descending order
312 static int seriesBestIndex(
314 sqlite3_index_info
*pIdxInfo
316 int i
; /* Loop over constraints */
317 int idxNum
= 0; /* The query plan bitmask */
318 int startIdx
= -1; /* Index of the start= constraint, or -1 if none */
319 int stopIdx
= -1; /* Index of the stop= constraint, or -1 if none */
320 int stepIdx
= -1; /* Index of the step= constraint, or -1 if none */
321 int nArg
= 0; /* Number of arguments that seriesFilter() expects */
323 const struct sqlite3_index_constraint
*pConstraint
;
324 pConstraint
= pIdxInfo
->aConstraint
;
325 for(i
=0; i
<pIdxInfo
->nConstraint
; i
++, pConstraint
++){
326 if( pConstraint
->usable
==0 ) continue;
327 if( pConstraint
->op
!=SQLITE_INDEX_CONSTRAINT_EQ
) continue;
328 switch( pConstraint
->iColumn
){
329 case SERIES_COLUMN_START
:
333 case SERIES_COLUMN_STOP
:
337 case SERIES_COLUMN_STEP
:
344 pIdxInfo
->aConstraintUsage
[startIdx
].argvIndex
= ++nArg
;
345 pIdxInfo
->aConstraintUsage
[startIdx
].omit
= !SQLITE_SERIES_CONSTRAINT_VERIFY
;
348 pIdxInfo
->aConstraintUsage
[stopIdx
].argvIndex
= ++nArg
;
349 pIdxInfo
->aConstraintUsage
[stopIdx
].omit
= !SQLITE_SERIES_CONSTRAINT_VERIFY
;
352 pIdxInfo
->aConstraintUsage
[stepIdx
].argvIndex
= ++nArg
;
353 pIdxInfo
->aConstraintUsage
[stepIdx
].omit
= !SQLITE_SERIES_CONSTRAINT_VERIFY
;
355 if( (idxNum
& 3)==3 ){
356 /* Both start= and stop= boundaries are available. This is the
357 ** the preferred case */
358 pIdxInfo
->estimatedCost
= (double)(2 - ((idxNum
&4)!=0));
359 pIdxInfo
->estimatedRows
= 1000;
360 if( pIdxInfo
->nOrderBy
==1 ){
361 if( pIdxInfo
->aOrderBy
[0].desc
) idxNum
|= 8;
362 pIdxInfo
->orderByConsumed
= 1;
365 /* If either boundary is missing, we have to generate a huge span
366 ** of numbers. Make this case very expensive so that the query
367 ** planner will work hard to avoid it. */
368 pIdxInfo
->estimatedCost
= (double)2147483647;
369 pIdxInfo
->estimatedRows
= 2147483647;
371 pIdxInfo
->idxNum
= idxNum
;
376 ** This following structure defines all the methods for the
377 ** generate_series virtual table.
379 static sqlite3_module seriesModule
= {
382 seriesConnect
, /* xConnect */
383 seriesBestIndex
, /* xBestIndex */
384 seriesDisconnect
, /* xDisconnect */
386 seriesOpen
, /* xOpen - open a cursor */
387 seriesClose
, /* xClose - close a cursor */
388 seriesFilter
, /* xFilter - configure scan constraints */
389 seriesNext
, /* xNext - advance a cursor */
390 seriesEof
, /* xEof - check for end of scan */
391 seriesColumn
, /* xColumn - read data */
392 seriesRowid
, /* xRowid - read data */
402 #endif /* SQLITE_OMIT_VIRTUALTABLE */
405 __declspec(dllexport
)
407 int sqlite3_series_init(
410 const sqlite3_api_routines
*pApi
413 SQLITE_EXTENSION_INIT2(pApi
);
414 #ifndef SQLITE_OMIT_VIRTUALTABLE
415 if( sqlite3_libversion_number()<3008012 ){
416 *pzErrMsg
= sqlite3_mprintf(
417 "generate_series() requires SQLite 3.8.12 or later");
420 rc
= sqlite3_create_module(db
, "generate_series", &seriesModule
, 0);