Re: [sqlite] Virtual table function calls

[David Jones]

Jens Alfke asks:
>Maybe pass the column name as a string, i.e. `attr('H',3)`

2 problems with that idea. First, the first argument has to refer to a value in 
the virtual table in order to invoke the overridden version (overrides are per 
table, so I use the ppArg to bind function invocation to associated virtual 
table). Second, if I created a view that gives column H a name of Total, I’d 
want to the function call to be attr(Total,3). I don’t know how to make the 
function figure out that attr(‘Total’,3) really means column H in some table.
>>Dave
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual table function calls

[David Jones]

I’ve written a virtual table to view spreadsheet data inside Excel workbook 
(.xlsx) files as SQlite tables. I want to implement an SQL function, attr(), 
which the virtual table overrides to give access to the raw attributes of the 
cell being returned. I’m looking for a robust way to find the actual column 
refered to by an overriden function, not just the value returned by the xColumn 
call.

Example:
   sqlite> .load xlsxvtab.so # creates 
xlsxvtab module and attr() function
   sqlite> create virtual table summary using 
xlsxvtab(‘expenses.xlsx’,’sheet1’,’F20’, ‘H32’);
   sqlite> select F,G,H,attr(H,3) from summary;   # show formula used 
to calculate column H.

The issue is that attr gets called with 2 values and I need to divine that the 
first value came from column H of the current row of the cursor opened by this 
select statement. The hack I’m using now is to give the values returned by the 
xColumn method a subtype equal to the column number. In the attr() function I 
retrieve the subtype and re-fetch that column from the current row of the last 
cursor open on that table.

Various pitfalls with this technique:
- Subtype numbers are limited to the range 0-255.
- Assumes all functions called before xNext() method called again.
- Complex queries with multiple cursors?

Any suggestions?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[David Jones]

I think of it as direct access, though I could see people confusing that with 
Windows Server DirectAccess.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sanitizing module arguments in virtual table create statement

[David Jones]

My application naively tried preparing the statement: “CREATE VIRTUAL TABLE xxx 
USING zipfile(?1);” and
binding the name of the zip file, supplied by user, to parameter 1 for 
execution. This doesn’t work, and the documention reveals that this is by 
design. I therefore have to expand the argument(s) myself to get the
statement to prepare. The page says there are no restrictions on the argument 
other than balanced parentheses, leaving me unclear how the parser deals with 
the statement following left parenthesis after the module name.
Do I make the argument a literal and double embedded occurrences of single 
quotes?

Even though my example used the zipfile module, I want to handle the general 
case.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiling 3.6.11 on Solaris results in runtime segmentation fault

[David Jones]

I am compiling 3.6.11 from amalgamated sources on Solaris and I get a
Segmentation Fault whenever I type something at the shell. Pasted
below are some information which might be helpful. Does anyone else
see this error or have a workaround?

I get the same behavior when I run in gdb or just from the command
line. In both cases, as soon as I type anything at the shell, such as
".tables" or a create table command, it immediately crashes. I am
compiling statically and installing into my homedir, which I don't
imagine should cause any problems. If I compile shared, I get the same
error.

It looks like previous bugs (700, 1158 in particular) saw
memory-alignment-based crashes. In the traceback, you can see that
there is an assignment to a memory location like 0x10591c. This
indicates that it is not 16-byte aligned. If I add __attribute__
((__aligned__(16))) to the definition of prereqRight, I see a
different alignment, but still not correct (something like 0x10588c).

$ gcc -v
Using built-in specs.
Target: sparc-sun-solaris2.10
Configured with: ./configure --prefix=/opt/gcc/4.2.4
--enable-languages=c,c++,fortran,obj-c++,objc --disable-libgcj
--disable-nls --with-gmp=/usr/local --with-mpfr=/usr/local
--enable-threads=posix --with-cpu=ultrasparc
--host=sparc-sun-solaris2.10
Thread model: posix
gcc version 4.2.4
$ CFLAGS="-DSQLITE_DEBUG -g" ./configure --prefix=$HOME
--enable-static --disable-shared
$ make && make install
$ gdb ~/bin/sqlite3
..snip..
This GDB was configured as "sparc-sun-solaris2.9"...
(gdb) run test.sq3
Starting program: /home/fairway/a/enedev/bin/sqlite3 test.sq3
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables

Program received signal SIGSEGV, Segmentation fault.
0x000b8b20 in exprAnalyze (pSrc=0x102910, pWC=0x1058c4, idxTerm=1)
at sqlite3.c:82139
82139   pTerm->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
(gdb) list 82135,82143
82135 | exprSelectTableUsage(pMaskSet,
pExpr->pSelect);
82136 }else if( op==TK_ISNULL ){
82137   pTerm->prereqRight = 0;
82138 }else{
82139   pTerm->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
82140 }
82141 prereqAll = exprTableUsage(pMaskSet, pExpr);
82142 if( ExprHasProperty(pExpr, EP_FromJoin) ){
82143   Bitmask x = getMask(pMaskSet, pExpr->iRightJoinTable);
(gdb) print pTerm
$1 = (WhereTerm *) 0x105904
(gdb) print pTerm->prereqRight
$2 = 0
(gdb) print >prereqRight
$3 = (Bitmask *) 0x10591c
(gdb) print pMaskSet
$4 = (WhereMaskSet *) 0x10597c
(gdb) print pExpr->pRight
$5 = (Expr *) 0x0
(gdb) print >pRight
$6 = (Expr **) 0x1023d4
(gdb) print *pMaskSet
$7 = {n = 1, ix = {1, 0 }}
(gdb) print *pExpr
$8 = {op = 19 '\023', affinity = 0 '\0', flags = 132, pColl = 0x0,
  pLeft = 0x102430, pRight = 0x0, pList = 0x0, token = {z = 0x0, dyn = 0,
n = 0}, span = {
z = 0xcd10a "name NOT LIKE 'sqlite_%'UNION ALL SELECT name FROM
sqlite_temp_master WHERE type IN ('table','view') ORDER BY 1", dyn =
0, n = 24},
  iTable = 0, iColumn = 0, pAggInfo = 0x0, iAgg = -1, iRightJoinTable = 0,
  pSelect = 0x0, pTab = 0x0, nHeight = 3}
(gdb)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users