[prev in list] [next in list] [prev in thread] [next in thread]
List: sqlite-dev
Subject: Re: [sqlite-dev] Enhancment to SQLITE_FCNTL_PRAGMA
From: David Jones <jonesd () columbus ! rr ! com>
Date: 2019-12-18 9:11:21
Message-ID: 4B7C76D6-4B66-4E58-B41C-109143C550E0 () columbus ! rr ! com
[Download RAW message or body]
> On Dec 7, 2019, at 9:35 AM, David Jones <jonesd@columbus.rr.com> wrote:
>
> I use SQLite with a custom VFS. The recent changes to PRAGMA processing are cool, \
> but the VFS only being able to to return a single string is kind of limiting. I've \
> been experimenting with a modified pragma module that allows the VFS to return \
> either a table or a string.
Here's the patch I made for pragma.c
*** pragma.c Fri Oct 11 18:26:26 2019
--- pragma2.c Tue Dec 17 21:08:15 2019
***************
*** 1,3 ****
--- 1,4 ----
+ #pragma module PRAGMA2 "3300100"
/*
** 2003 April 6
**
***************
*** 293,298 ****
--- 294,554 ----
}
}
return lwr>upr ? 0 : &aPragmaName[mid];
+ #ifdef SQLITE_VFS_TABLE_PRAGMA
+ }
+ /*
+ ** Helper functions for VFS table return from FCNTL_PRAGMA. The VFS shall
+ ** return row data in aFcntl[0] as a pointer to a string with format
+ ** "fmt,V1V2[...]", where:
+ ** fmt Format string consisting of characters 'i', 's', 'l', 'f', 'b'.
+ ** Length of this string determines number of columns (n), in row.
+ ** The characters indicate the data for the corresponding column is to
+ ** be saved as integer, string, int64, double, or blob, respectively.
+ **
+ ** Vi Encoded value for i'th column (1..n), which is of format:
+ ** nnnD nnn is a numeric value, terminated by 'D'.
+ ** nnnHtext nnn is numeric value indicating length of text
+ ** following 'H'. 0H encodes a zero-length string
+ ** while -1H encodes a NULL value.
+ ** nnnXhexdigits nnn is numeric value indicating number of hex
+ ** digits following 'X'.
+ **
+ ** Note that the encoding and the save format may disagree, a 'D' value field
+ ** will be saved as a string if the format character is 's'.
+ **
+ ** Row string examples:
+ ** "sss,4Hname5Hvalue9Himmutable" 3 strings: 'name', 'value', 'immutable'
+ ** "isl,20D7Hall.dat2098895D" 20, 'all.dat', 2098895
+ ** "ssf,5Htemp-1H8.772D" 'temp', NULL, 8.772
+ **
+ **
+ ** Functions:
+ ** parseVFSrow() Make array of VFSpragmaField structs from string
+ ** returned by VFS.
+ **
+ ** convertVFSpragmaField() Decode field and save in Vdbe register.
+ **
+ ** returnTableFromVFS() Build table in Vdbe supplied by caller.
+ **
+ */
+ struct VFSpragmaField {
+ int encoding; /* Now to interpret str: 0-number, 1-string, 2-blob */
+ ptrdiff_t length; /* Length of str before decoding */
+ char *str; /* points into aFcntl[0] */
+ };
+ static struct VFSpragmaField *parseVFSrow(char *vlist)
+ {
+ int count, col;
+ size_t len, slen;
+ struct VFSpragmaField *fld;
+ char *vstr, *cp;
+ char delimiter;
+ /*
+ * Determine length of fmt portion of string, which equals the number of
+ * columns (col_count).
+ */
+ fld = 0;
+ for ( count = 0; vlist[count] != ','; count++ ) {
+ if ( vlist[count] == 0 ) return 0; /* No comma in string! */
+ }
+ vlist[count] = 0; /* don't care if we trash string */
+ /*
+ * Allocate array of field structures, 1 for each column plus 1 for format,
+ * then initialize element [0] to point to the (now terminated) format string.
+ */
+ fld = sqlite3_malloc((count+1)*sizeof(struct VFSpragmaField));
+ if ( !fld ) return fld;
+ fld[0].length = count;
+ fld[0].str = vlist;
+ /*
+ * Set fld[1..count] descriptors to point to values parsed from remainder
+ * of input string.
+ */
+ vstr = &vlist[count+1]; /* Initial value string starts after comma */
+ for ( col = 1; col <= count; col++ ) {
+ fld[col].encoding = -1; /* unknown or missing */
+ fld[col].length = 0;
+ fld[col].str = 0;
+ /*
+ * Extract string for current column, initializing fld[col].
+ * vstr will be updated to point to next value to process.
+ */
+ for ( cp=vstr; *cp; cp++ ) {
+ if ( *cp == 'D' ) {
+ /* Digits */
+ *cp = 0;
+ fld[col].encoding = 0;
+ fld[col].length = cp - vstr;
+ fld[col].str = vstr;
+ vstr = cp+1;
+ break;
+ } else if ( (*cp == 'H') || (*cp == 'X') ) {
+ /* Text (hollerith) or binary (hex-encoded) */
+ fld[col].encoding = (*cp == 'H') ? 1 : 2;
+ fld[col].length = atoll(vstr);
+ fld[col].str = vstr;
+ *cp = 0;
+ if ( fld[col].length > 0 ) {
+ slen = strnlen(cp+1,fld[col].length);
+ if ( slen < fld[col].length ) {
+ }
+ if ( slen > 0 ) memmove ( vstr, cp+1, slen );
+ vstr[slen] = 0;
+ } else if ( fld[col].length < 0 ) {
+ slen = 0;
+ }
+ vstr = cp+1+slen;
+ }
+ }
+ }
+ return fld;
+ }
+
+ /*
+ ** Return value is 1 for success, 0 for failure.
+ */
+ static int convertVFSpragmaField(Vdbe *v, int iDest,
+ struct VFSpragmaField *fld, char out_type)
+ {
+ int value, status;
+ i64 val64;
+ double fval;
+ /*
+ * Preliminary processing of input data: encoding -1 always set destination
+ * register to a blob. Hex string is decoded to binary bytes.
+ */
+ if ( fld->encoding < 0 ) {
+ sqlite3VdbeAddOp2(v, OP_Null, 0, iDest);
+ return 1;
+ }
+ if ( (fld->encoding == 2) && (fld->length > 0) ) {
+ char *cp, c;
+ int i, n = fld->length/2;
+ cp = fld->str;
+ for ( i = 0; i < n; i++ ) {
+ c = (sqlite3HexToInt(cp[0])<<4) | sqlite3HexToInt(cp[1]);
+ fld->str[i] = c;
+ cp += 2;
+ }
+ fld->length = n;
+ }
+ /*
+ * Set register with field's value based on output data type specified by
+ * caller.
+ */
+ switch ( out_type ) {
+ case 'i': /* Integer */
+ value = sqlite3Atoi(fld->str);
+ sqlite3VdbeAddOp2(v, OP_Integer, value, iDest);
+ break;
+
+ case 's': /* String */
+ if ( fld->length < 0 ) { /* interpret negative length as NULL */
+ sqlite3VdbeAddOp2(v, OP_Null, 0, iDest);
+ } else {
+ sqlite3VdbeAddOp4(v, OP_String8, 0, iDest, 0, fld->str, 0);
+ }
+ break;
+ case 'l': /* Int64 */
+ status = sqlite3Atoi64(fld->str, &val64, fld->length, SQLITE_UTF8);
+ if ( status == 0 ) {
+ sqlite3VdbeAddOp4Dup8(v, OP_Int64, 0, iDest, 0,
+ (const u8*)&val64, P4_INT64);
+ }
+ break;
+ case 'b': /* Blob */
+ sqlite3VdbeAddOp4(v, OP_Blob, fld->length, iDest, 0, fld->str, 0);
+ break;
+ case 'f': /* Double (floating point) */
+ status = sqlite3AtoF(fld->str, &fval, fld->length, SQLITE_UTF8);
+ if ( status > 0 ) {
+ sqlite3VdbeAddOp4Dup8(v, OP_Int64, 0, iDest, 0,
+ (const u8*)&fval, P4_REAL);
+ }
+ break;
+ default: /* Bugcheck, unknown */
+ sqlite3VdbeAddOp2(v, OP_Null, 0, iDest);
+ return 0;
+ }
+ return 1;
+ }
+
+ static void returnTableFromVFS( Parse *pParse,
+ Vdbe *v, sqlite3 *db, const char *zDb, char *aFcntl[4])
+ {
+ struct VFSpragmaField *cval;
+ char *dt, *cp, delimiter;
+ int col_count, col, value, rc;
+ /*
+ * Split string initially returned by VFS into column names and set the
+ * column names.
+ */
+ cval = parseVFSrow ( aFcntl[0] );
+ if ( !cval ) return;
+ col_count = cval[0].length;
+
+ if ( pParse->nMem <= col_count ) pParse->nMem = col_count+1;
+ sqlite3VdbeSetNumCols(v, col_count);
+ for (col=1; col<=col_count; col++) {
+ if ( !cval[col].str ) {
+ sqlite3ErrorMsg(pParse, "%s", "file control pragma response invalid");
+ pParse->nErr++;
+ pParse->rc = SQLITE_INTERNAL;
+ sqlite3_free ( cval );
+ return;
+ }
+ sqlite3VdbeSetColName(v, col-1, COLNAME_NAME, cval[col].str, \
SQLITE_TRANSIENT); + }
+ sqlite3_free ( cval );
+ /*
+ * Call the VFS repeatly to get the row data. VFS keeps context in aFcntl[3].
+ */
+ do {
+ /*
+ * Cleanup from previous call to sqlite3_file_control and call again.
+ */
+ sqlite3_free(aFcntl[0]);
+ aFcntl[0] = 0;
+ rc = sqlite3_file_control(db, zDb, SQLITE_FCNTL_PRAGMA, (void*)aFcntl);
+
+ if ( rc == SQLITE_ROW ) {
+ /*
+ * Next row retrieved, load registers from fields in string.
+ */
+ cval = parseVFSrow ( aFcntl[0] );
+ if ( !cval ) {
+ /* Parse error or memory allocation failed */
+ break;
+ }
+ if ( cval[0].length < col_count ) {
+ /* too few columns returned */
+ break;
+ }
+
+ for (col=1; col<=col_count; col++) {
+ if ( !convertVFSpragmaField(v, col, &cval[col], cval[0].str[col-1]) ) {
+ /* error! */
+ sqlite3_free ( cval );
+ rc = SQLITE_INTERNAL;
+ break;
+ }
+ }
+ /*
+ * Emit row result.
+ */
+ sqlite3VdbeAddOp2(v, OP_ResultRow, 1, col_count);
+ sqlite3_free ( cval );
+ }
+ } while ( rc == SQLITE_ROW );
+
+ if ( rc != SQLITE_DONE ) {
+ if ( aFcntl[0] ) sqlite3_free(aFcntl[0]);
+ aFcntl[0] = 0;
+ sqlite3ErrorMsg(pParse, "%s", "Error processing VFS pragma file control");
+ pParse->nErr++;
+ pParse->rc = rc;
+ }
+ #endif /* SQLITE_VFS_TABLE_PRAGMA */
}
/*
***************
*** 403,408 ****
--- 659,672 ----
returnSingleText(v, aFcntl[0]);
sqlite3_free(aFcntl[0]);
goto pragma_out;
+ #ifdef SQLITE_VFS_TABLE_PRAGMA
+ } else if( (rc==SQLITE_ROW) && (aFcntl[3] != 0) ){
+ /*
+ * VFS is returning a table and providing context for iterative calls.
+ */
+ returnTableFromVFS(pParse, v, db, zDb, aFcntl);
+ goto pragma_out;
+ #endif
}
if( rc!=SQLITE_NOTFOUND ){
if( aFcntl[0] ){
_______________________________________________
sqlite-dev mailing list
sqlite-dev@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic