[sqlite] virtual tables vs. expression-based indexes

[Jens Alfke]

I'm considering using a virtual table to query an external-to-SQLite data 
store. However, I've carefully read about the xBestIndex method, and it appears 
that virtual tables cannot have indexes on expressions; or rather that the 
SQLite query engine can't make use of such indexes, only indexes on columns.

Consider for example a virtual table with a column named "text", and a query 
with `WHERE length(text) > 100`. In my external data store I can create an 
index on `length(text)`, but it doesn't look as though SQLite has any way of 
asking me about it, so I assume it will just brute-force scan through every row.

The only workaround I can see is to add a virtual table column for every 
possible expression that might be queried against — like "text_length" — but 
the query interface in my project is open-ended enough that I can't delimit the 
set of expressions that might need to be exposed this way. (It might be 
feasible if I could alter the table on the fly to add columns as needed, but 
the docs explicitly say I can't do that.)

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

[sqlite] Virtual Tables xConnect Called When?

[Justin Olbrantz]

The documentation says that "The difference is that xConnect is called to
establish a new connection to an existing virtual table whereas xCreate is
called to create a new virtual table from scratch." But this leaves me
unclear on the circumstances where xConnect would be called (assuming I
have a non-eponymous table). How would you create a new connection to an
exiting virtual table?

-- 
Justin Olbrantz (Quantam)
"Ardente veritate
Urite mala mundi
Ardente veritate
Incendite tenebras mundi"
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

[dave]

Folks,
 
I cannot seem to find a means of filtering on negated operators, e.g. <>,
not null, not like, etc., in the xBestIndex() method for virtual vables.  As
best as I can tell, I cannot, unless there is something I am missing, hence
this inquiry.
 
In a few virtual tables I have implemented, I have handled the
SQLITE_INDEX_CONSTRAINT_EQ, and the SQLITE_INDEX_CONSTRAINT_LIKE (for
example) in the xBestIndex and xFilter.  These code paths are taken for
queries of the form:
 
select * from myvtab where mycol = 'xxx';
select * from myvtab where mycol like 'xxx';
 
but /not/ for queries of the form:
 
select * from myvtab where mycol <> 'xxx';
select * from myvtab where mycol not like 'xxx';
 
I can work around these things for now with caveats in documentation, but it
does sometimes cause confusion to users.
 
For example, in one case I have extended the syntax of LIKE .  That
extension of syntax is invoked for a positive LIKE constraint, but is
bypassed for a negated one.  I can work around that with an extension
function, but I won't get the hints at record enumeration time that could
reduce the dataset from the underlying source.
 
In other cases, I have some 'required' columns, which must be present in a
EQ constraints (usually they wind up being parameters to a function call
that generates the underlying data).  I emit an error when such constraints
are missing, but it can be confusing to users when:
 
select * from myvtab where mycol <> 'xxx';
 
indicates that "you must have a constraint on 'mycol'"
 
Lastly, some behavioural inconsistencies occur between these forms:
 
select * from myvtab where mycol = null;
select * from myvtab where mycol is null;
 
Since the first comes in as a constraint to xBestIndex, whereas the second
does not.
 
Anyway, as I said, I can work around this for now, but I thought I would ask
if:

1)  is it true:  xBestIndex doesn't get to see negated predicates, or is it
just somewhere that I have not found?
2)  if it's not possible, would it be worthwhile to consider extending the
operator set in some way to present the negative clauses at some release in
the future?
 
Thanks for any info!
 
-dave

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

[sqlite] virtual tables, xBestIndex/xFilter question

[David Fletcher]

First, thanks in advance for any help offered.

I'm pretty comfortable using sqlite but just now starting to develop 
with virtual tables.
I'm running into troubles and I'm pretty sure it's because my mental 
model of sqlite is wimpy.


I'm trying to build a tool that interfaces to C++ objects in memory that 
are basically
arranged as a tree (or graph or mesh).  For example, let's suppose I 
have a class called
A and objects of this class can own any number of B objects.  Every A 
and B object has

an ObjectID.  Each object also has an owner.

In C/C++ I would do this sort of thing:

    A* pA = GetOjectGivenID(12345);  B* pB;
    BIterator iter(pA);

    while ((pB = iter.Next()) != 0) {  assert(pB->pOwner == pA); }

That's simple enough.  I'm trying to achieve the same effect using 
virtual tables,

each of which follow this basic pattern:

    create table A(OID integer primary key, Owner integer, Attr1 
integer, Attr text, ...) without rowid;


That is, the first column of every table is OID, the object ID.  For 
most of the tables, there's also
a column called Owner and maybe other columns that act as foreign keys, 
too.  (I haven't actually

gone to the trouble to denote the columns are foreign keys just yet.)

Everything works when I have just a single table with SQL like this: 
select * from A where A.OID == 12345;
The statement will walk through all of the rows.  Behind the scenes, a 
C++ iterator is

doing all of the work.

I'm struggling with joins.  This statement doesn't work in my application:
    select * from A join B on A.OID == B.Owner where A.OID == 12345;

The xBestIndex function is called a couple of times if this is the first 
time
the table has been defined.  (xBestIndex is called fewer times if the 
table has
been seen before.  I'm guessing sqlite is caching some info.)  When the 
xFilter
function is called, it's being handed the virtual table for B, not A.  
Because
there's no object ID to act as the iterator's source, the xFilter 
function ends in failure.
I'm struggling to find aConstraintUsage settings that will cause A, the 
left-hand

table in my mental model, to be presented before B.

I'm setting the estimated number of rows correctly, I think.  I think the
argvIndex values are being set correctly, too, I think.  At least sqlite 
isn't complaining

about malformed expressions.  But, something is missing.

I spent some time looking at other examples but most of them seem to use
other tables within sqlite to hold the data vs. objects in memory. I did 
stumble
across https://osquery.io/  yesterday, which looks interesting and 
useful.  It's also
somewhat complex and I haven't delved into its xBestIndex/xFilter 
implementations

just yet.

I think I'd benefit from looking at any application that uses C/C++ objects
in memory plus iterators to traverse objects in a tree/graph/mesh/etc.  Can
anyone point out projects like this?

I think I'd also benefit with documentation that shows more of what's 
happening
behind the scenes with xBestIndex/xFilter.  Can anyone point me to 
documentation
that is more detailed?  I've looked at the sqlite docs, Jay Kreibich's 
book& Mike Owens

book and "Query Anything" documentation.

Thanks,

David


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

[sqlite] virtual tables, and theTrue meaning of pIdxInfo->estimatedCost, pIdxInfo->estimatedRows, and pIdxInfo->idxFlags...

[dave]

Hi folks,
 
I am trying to fully understand the impact and correct use of a few subtle
features related to virtual tables' the xBestIndex mechanism, and their
correct use.  Here are my current beliefs:
 
*  pIdxInfo->estimatedCost
  obviously the cost of the proposed plan; a metric of the 'viscosity' of
the table when traversing through xNext relative to other tables and
especially to filesystem access
*  pIdxInfo->estimatedRows
  obviously the approximate number of rows that a proposed plan will return.
But less obvious to me is how this materially affects the query plan,
especially relative to pIdxInfo->estimatedCost
  and a little bit with respect to:
* pIdxInfo->idxFlags
  when the SQLITE_INDEX_SCAN_UNIQUE is set.  Isn't setting
pIdxInfo->estimatedRows to 0 or 1 enough to communicate this same
information?
 
Anyway, I am dutifully setting both estimatedRows and idxFlags in cases
where I have a 0-or-1-result table (I have several of these), and I am also
estimatedRows to LLONG_MAX along with estimatedCost to DBL_MAX in cases
where a plan can never be executed (btw I would respectfully suggest perhaps
using a bit in idxFlags to communicate 'never use this plan, it will never
work').
 
I haven't had any ill effects doing the above, but wonder if that is
'correct'.  Also, it would be interesting just to know what the material
effect of estimatedRows and idxFlags is, so that I can maybe use them more
effectively.
 
Any thoughts or corrections to my thinking?  Thanks in advance; cheers!
-dave
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual tables and table-valued functions

[E.Pasma]

6 dec 2015, Charles Leifer:

> In working on a Python wrapper around virtual tables, I thought it  
> might be
> beneficial if SQLite provided an official C API for creating simple
> table-valued functions. The wrapper could build on the existing  
> virtual
> table APIs and would consist of:
>
> * user supplied list of parameters, which would be converted to HIDDEN
> columns in a virtual table.
> * user supplied list of column names, which would constitute the  
> return
> values of the table-valued function
> * user supplied initialization function which would accept the  
> parameters
> passed, i.e. SELECT * FROM user_func(p1, p2, p3), the init function  
> would
> receive (p1, p2, p3).
> * user supplied iterate function which would accept a zero-based  
> index and
> a result context, returning 1 to indicate EOF and 0 to indicate more
> results exist.
>
> I think this might lead to a larger ecosystem of quality open-source
> table-valued functions. Given the fact that creating even a simple  
> vtable
> is cumbersome, providing an API that required the bare minimum seems  
> like a
> good idea to me.
>
> Thoughts?
>
> PS if you're curious about the Python wrapper, I posted on it a day  
> or two
> ago.
One thought that is only a change in the current class, is to have a  
default xBestIndex method. This would just optimize for plans with the  
highest number of usable parameter values, starting from number 1..  
The function may then become optional. In xFIlter the number of  
parameters is still available from the argc argument.
The xBestIndex is rather verbose and it looks that in most cases it  
should do just the above.

[sqlite] Virtual tables and table-valued functions

[Charles Leifer]

In working on a Python wrapper around virtual tables, I thought it might be
beneficial if SQLite provided an official C API for creating simple
table-valued functions. The wrapper could build on the existing virtual
table APIs and would consist of:

* user supplied list of parameters, which would be converted to HIDDEN
columns in a virtual table.
* user supplied list of column names, which would constitute the return
values of the table-valued function
* user supplied initialization function which would accept the parameters
passed, i.e. SELECT * FROM user_func(p1, p2, p3), the init function would
receive (p1, p2, p3).
* user supplied iterate function which would accept a zero-based index and
a result context, returning 1 to indicate EOF and 0 to indicate more
results exist.

I think this might lead to a larger ecosystem of quality open-source
table-valued functions. Given the fact that creating even a simple vtable
is cumbersome, providing an API that required the bare minimum seems like a
good idea to me.

Thoughts?

PS if you're curious about the Python wrapper, I posted on it a day or two
ago.

[sqlite] Virtual tables/xBestIndex: Is this a bug?

[Eric Hill]

Thanks, Dan.  I grabbed the fix and tried it out - works great, and a much 
better fix than what I suggested (not shocking).  It really cleans up 
xBestIndex.  In my simple cases, it's like the usable flag is now superfluous; 
xBestIndex is only getting passed usable constraints (and not getting called it 
all when there are no usable constraints).  But perhaps there are still 
scenarios where an unusable constraint will show up.

Eric

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy
Sent: Thursday, June 11, 2015 1:14 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Virtual tables/xBestIndex: Is this a bug?

On 06/11/2015 03:49 AM, Eric Hill wrote:
> Is it a bug that SQLite changes the order of the constraints passed to 
> xBestIndex based merely on the order of the ON clause, when SQL (AFAIK) says 
> nothing about what the order of the ON clause should be?

No. The order is undefined.

However, it is less than perfect that the constraints on "film_id=" are marked 
usable when they are really not. That's the root of your problem I think - 
SQLite is asking for the wrong thing. When it eventually figures out that it 
can't actually use the plan it requested from xBestIndex (because the film_id= 
constraint is not actually usable) it falls back to a linear scan.

There is now a change on the trunk that should fix this:

   http://www.sqlite.org/src/info/7b446771cadedafb

Dan.

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

[sqlite] Virtual tables/xBestIndex: Is this a bug?

[Dan Kennedy]

On 06/11/2015 03:49 AM, Eric Hill wrote:
> Is it a bug that SQLite changes the order of the constraints passed to 
> xBestIndex based merely on the order of the ON clause, when SQL (AFAIK) says 
> nothing about what the order of the ON clause should be?

No. The order is undefined.

However, it is less than perfect that the constraints on "film_id=" are 
marked usable when they are really not. That's the root of your problem 
I think - SQLite is asking for the wrong thing. When it eventually 
figures out that it can't actually use the plan it requested from 
xBestIndex (because the film_id= constraint is not actually usable) it 
falls back to a linear scan.

There is now a change on the trunk that should fix this:

   http://www.sqlite.org/src/info/7b446771cadedafb

Dan.

[sqlite] Virtual tables/xBestIndex: Is this a bug?

[Eric Hill]

Hey,

This is a follow-up from the thread entitled "Virtual Table query - why isn't 
SQLite using my indexes?" in order to raise the visibility of this issue:

Consider this SQL, where all of the tables involved are virtual:


SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title,

 t4.category_id, t5."name"

FROM rental  t2

 LEFT OUTER JOIN inventory t1

  ON  ( t1.inventory_id = t2.inventory_id )

 LEFT OUTER JOIN film t3

  ON  ( t3.film_id = t1.film_id )

 LEFT OUTER JOIN film_category t4

  ON  ( t4.film_id = t1.film_id )

 LEFT OUTER JOIN category t5

  ON  ( t5.category_id = t4.category_id )  ;

When xBestIndex gets called for the inventory table, the constraints will be in 
this order:


jmpvtab BEST INDEX:  Table: inventory  nConstraints: 3

   CONST[0]: 0 (inventory_id) = Usable

   CONST[1]: 1 (film_id) = Usable

   CONST[2]: 1 (film_id) = Usable



This is helpful, because the constraint that matters, i.e., the column that 
SQLite would benefit from having an index on, is listed first.  However, if I 
instead submit this SQL:


SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title,

t4.category_id, t5."name"

FROM rental  t2

LEFT OUTER JOIN inventory t1

ON  ( t2.inventory_id = t1.inventory_id )

LEFT OUTER JOIN film t3

ON  ( t3.film_id = t1.film_id )

LEFT OUTER JOIN film_category t4

ON  ( t4.film_id = t1.film_id )

LEFT OUTER JOIN category t5

ON  ( t5.category_id = t4.category_id )  ;

where the only difference is the order of the ON clause for the LEFT OUTER JOIN 
with inventory, the xBestIndex call looks like this:


jmpvtab BEST INDEX:  Table: inventory  nConstraints: 3

   CONST[0]: 1 (film_id) = Usable

   CONST[1]: 1 (film_id) = Usable

   CONST[2]: 0 (inventory_id) = Usable

So, with just that tiny change, now the interesting constraint comes last 
instead of first.

Is it a bug that SQLite changes the order of the constraints passed to 
xBestIndex based merely on the order of the ON clause, when SQL (AFAIK) says 
nothing about what the order of the ON clause should be?

I am attempting to create whatever indexes SQLite tells me it needs, but SQLite 
is playing a shell game with this information.

Thanks very much!

Eric

[sqlite] virtual tables (was : SQLite as a meta database)

[Laurent Dami]

Hi there,

Since virtual tables were recenty mentioned in the thread "SQLite as a 
meta database" , I take this opportunity to announce that the lastest 
version of the  DBD::SQLite driver for Perl now has support for virtual 
tables (see https://metacpan.org/pod/DBD::SQLite ).


This means that you can implement virtual tables through Perl classes 
instead of writing C code, which (at least in my view) is much easier to 
write and to debug.


Laurent Dami



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

[sqlite] Virtual tables and foreign keys

[Andy Gibbs]

Hi,

I need (and have attempted to add) limited support for foreign key 
constraints mapping to virtual tables.  It seems this is not possible 
currently in sqlite, or at least, it gives me "foreign key mismatch" when I 
try.


My use-case is this:

create virtual table vtbl using vtblXYZ;
create table x(i integer references vtbl(id));
insert into x(i) values(5);

where vtblXYZ is declared:

sqlite3_declare_vtab(db, "CREATE TABLE a(id INTEGER PRIMARY KEY,data TEXT 
...


The patch below provides this support, but is limited to mapping to integer 
primary keys on the virtual table, and it works as far as I know and am able 
to test.  My question is, is there a reason why virtual tables 
can't/shouldn't be used in this way.  Is my approach valid or is it likely 
to back-fire!?


Many thanks for your input.

Andy


(This patch is based off version 3.7.10, sorry that its so old!)

Index: sqlite3.c
===
--- sqlite3.c (revision 9938)
+++ sqlite3.c (working copy)
@@ -83132,7 +83132,7 @@ SQLITE_PRIVATE void sqlite3AddPrimaryKey
  Table *pTab = pParse->pNewTable;
  char *zType = 0;
  int iCol = -1, i;
-  if( pTab==0 || IN_DECLARE_VTAB ) goto primary_key_exit;
+  if( pTab==0 ) goto primary_key_exit;
  if( pTab->tabFlags & TF_HasPrimaryKey ){
sqlite3ErrorMsg(pParse,
  "table \"%s\" has more than one primary key", pTab->zName);
@@ -83169,7 +83169,7 @@ SQLITE_PRIVATE void sqlite3AddPrimaryKey
sqlite3ErrorMsg(pParse, "AUTOINCREMENT is only allowed on an "
   "INTEGER PRIMARY KEY");
#endif
-  }else{
+  }else if( !IN_DECLARE_VTAB ){
Index *p;
p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, 
sortOrder, 0);

if( p ){
@@ -89121,10 +89121,45 @@ static void fkLookupParent(
sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk, regTemp);
  }

-  sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
-  sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp);
-  sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
-  sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
+  if (pTab->tabFlags & TF_Virtual) {
+/* For a INTEGER PRIMARY KEY in a virtual table, we need to build
+** an explicit "EXISTS ( SELECT 1 FROM parent WHERE pkey=fkey )"
+** expression.  */
+sqlite3* db = pParse->db;
+Expr* pPIK = sqlite3Expr(db, TK_ID, pFKey->aCol[0].zCol ?: "oid"); 
/* use ROWID if zCol null */

+Expr* pFIK = sqlite3Expr(db, TK_REGISTER, 0);
+if( pFIK ){
+  pFIK->iTable = regTemp;
+}
+
+Token tFrom;
+tFrom.z = pTab->zName;
+tFrom.n = sqlite3Strlen30(tFrom.z);
+
+Select* pSelect = sqlite3SelectNew(pParse,
+sqlite3ExprListAppend(pParse, 0, sqlite3PExpr(pParse, 
TK_INTEGER, 0, 0, [1])), /* SELECT 1 */

+sqlite3SrcListAppend(db, 0, , 0), /* FROM parent */
+sqlite3PExpr(pParse, TK_EQ, pPIK, pFIK, 0), /* WHERE pkey=fkey 
*/

+0, 0, 0, 0,
+sqlite3PExpr(pParse, TK_INTEGER, 0, 0, [1]), 
/* LIMIT 1 */

+0
+);
+
+SelectDest dest;
+int regTemp2 = sqlite3GetTempReg(pParse);
+sqlite3SelectDestInit(, SRT_Exists, regTemp2);
+sqlite3VdbeAddOp2(v, OP_Integer, 0, regTemp2); /* initialise target 
register */

+sqlite3Select(pParse, pSelect, );
+sqlite3VdbeAddOp3(v, OP_If, regTemp2, iOkNoCur, 0); /* if found, 
then ok */

+
+sqlite3SelectDelete(db, pSelect);
+sqlite3ReleaseTempReg(pParse, regTemp2);
+  }else{
+sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
+sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp);
+sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
+sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
+  }
  sqlite3VdbeJumpHere(v, iMustBeInt);
  sqlite3ReleaseTempReg(pParse, regTemp);
}else{
@@ -89192,6 +89227,7 @@ static void fkLookupParent(

  sqlite3VdbeResolveLabel(v, iOk);
  sqlite3VdbeAddOp1(v, OP_Close, iCur);
+  sqlite3VdbeResolveLabel(v, iOkNoCur);
}

/*
@@ -104518,6 +104553,13 @@ SQLITE_API int sqlite3_declare_vtab(sqli
 && (pParse->pNewTable->tabFlags & TF_Virtual)==0
){
  if( !pTab->aCol ){
+if( (pParse->pNewTable->tabFlags & TF_HasPrimaryKey) &&
+pParse->pNewTable->iPKey >= 0 ){
+  assert( !(pTab->tabFlags & TF_HasPrimaryKey) );
+  pTab->tabFlags |= TF_HasPrimaryKey;
+  pTab->iPKey = pParse->pNewTable->iPKey;
+  pTab->keyConf = pParse->pNewTable->keyConf;
+}
pTab->aCol = pParse->pNewTable->aCol;
pTab->nCol = pParse->pNewTable->nCol;
pParse->pNewTable->nCol = 0;



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

Re: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

[Clemens Ladisch]

James K. Lowden wrote:
> Clemens Ladisch  wrote:
>>> Is there a way to force rows to be dispatched (using the group by)
>>> and aggregated on the fly instead of being stored, sorted and then
>>> aggregated?
>>
>> SQLite can optimize GROUP BY this way only if it can prove that the
>> table is already sorted on the grouping column.  Otherwise, it creates
>> a temporary index for all records, and then groups on those.
>
> IIUC "prove that the table is already sorted" means
> xBestIndex sets orderByConsumed to 1.  One of the orderings proposed
> to xBestIndex is chosen by the query planner and given to xFilter as
> idxNum and idxStr.  The function is then obliged to provide the rows in
> that order, allowing SQLite to skip the step of writing the index to a
> temporary table.
>
> Do I have that right?

Yes:
  > create virtual table t using fts3([...]);
  > explain query plan select * from t group by docid;
  0|0|0|SCAN TABLE t VIRTUAL TABLE INDEX 0:ASC

>> In theory, it would be possible to create a temporary index that
>> stores the current aggregation _result_ for each group seen so far,
>> and that is updated dynamically.
>
> It's possible in theory, but it would complicate user-defined
> aggregation functions greatly.  As it stands, a UDF is called
> with xStep N times, and xFinal once per group.

Sorry, what I meant to say was "stores the current accumulator".

And you're right, this would require that the aggregation function
can store its entire state in a sqlite3_value.

Having two different aggregation APIs is probably not worth the
effort.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

[James K. Lowden]

On Thu, 19 Dec 2013 12:03:48 +0100
Clemens Ladisch  wrote:

> > Is there a way to force rows to be dispatched (using the group by)
> > and aggregated on the fly instead of being stored, sorted and then
> > aggregated?
> 
> SQLite can optimize GROUP BY this way only if it can prove that the
> table is already sorted on the grouping column.  Otherwise, it creates
> a temporary index for all records, and then groups on those.
> 
> In theory, it would be possible to create a temporary index that
> stores the current aggregation _result_ for each group seen so far,
> and that is updated dynamically.  However, this is not implemented in
> SQLite.

That answer sent me back to the documentation. I've been toying with
virtual table functions that parse files because it seems to me there
are way too many file formats out there, and very few tools nearly as
good as SQLite for dealing with them.  

IIUC "prove that the table is already sorted" means
xBestIndex sets orderByConsumed to 1.  One of the orderings proposed
to xBestIndex is chosen by the query planner and given to xFilter as
idxNum and idxStr.  The function is then obliged to provide the rows in
that order, allowing SQLite to skip the step of writing the index to a
temporary table.  

Do I have that right?  

> In theory, it would be possible to create a temporary index that
> stores the current aggregation _result_ for each group seen so far,
> and that is updated dynamically.  

It's possible in theory, but it would complicate user-defined
aggregation functions greatly.  As it stands, a UDF is called
with xStep N times, and xFinal once per group.  The groups of rows are
implicitly provided to the function in order, allowing the function to
operate only on the values, oblivious of the key.  

Not all functions are amenable to incremental update.  It's quite
difficult, for example, to compute a median without access to the
entire dataset.  

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

Re: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

[Perrin, Lionel]

Thanks for your answers. As far as I understand, I won't be able to take 
advantage of sqlite to perform an 'in memory' aggregation.
Actually, I did the same type of test with a 'normal' table and ended to the 
same conclusion. When no index is provided, all rows are retrieved and store in 
a temporary table, then sorted, and finally aggregated. 

What do you think? Should I contribute to sqlite source code? I have the 
feeling that this need is not only relevant for virtual tables and might be 
required for large unsorted tables...

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Clemens Ladisch
Sent: jeudi 19 décembre 2013 12:43
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] virtual tables and group by: how could we prevent to 
retrieve all rows from a vtab and sort them later ?

Hick Gunter wrote:
> You are in error on number 4.
>
> SQLite reads your CFL table once and performs the aggregation in a 
> temporary table that holds 1 entry per "contract" i.e. about
> 1000 rows of max 16 byte records which I estimate to using less than 
> 64k.
>
> For each record read, SQLite will update or insert the matching row in 
> the temporary table.

sqlite> CREATE VIRTUAL TABLE t USING fts3(x,y); EXPLAIN SELECT x, SUM(y) 
sqlite> FROM t GROUP BY x;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
1 SorterOpen 1 3 0 keyinfo(1,BINARY)  00  nColumn=3
...
7 VOpen  0 0 0 vtab:82F090:7F0C37EF47A0  00
8 Noop   0 0 000  Begin Join Loop 0
9 Integer0 10000  r[10]=0
10Integer0 11000  r[11]=0
11VFilter0 1810   00  i0an=r[10] z0an=''
12VColumn0 0 12   00  r[12]=vcolumn(0); t.x
13Sequence   1 13000  r[13]=rowid
14VColumn0 1 14   00  r[14]=vcolumn(1); t.y
15MakeRecord 123 15   00  r[15]=mkrec(r[12..14])
16SorterInsert   1 15000
17VNext  0 12000
...

This copies all records into the temporary table without yet aggregating them.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-

Moody's monitors email communications through its networks for regulatory 
compliance purposes and to protect its clients, employees and business and 
where allowed to do so by applicable law. Parties communicating with Moody's 
consent to such monitoring by their use of the email communication. The 
information contained in this e-mail message, and any attachment thereto, is 
confidential and may not be disclosed without our express permission. If you 
are not the intended recipient or an employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
you have received this message in error and that any review, dissemination, 
distribution or copying of this message, or any attachment thereto, in whole or 
in part, is strictly prohibited. If you have received this message in error, 
please immediately notify us by telephone, fax or e-mail and delete the message 
and all of its attachments. Thank you. Every effort is made to keep our network 
free from viruses. You should, however, review this e-mail message, as well as 
any attachment thereto, for viruses. We take no responsibility and have no 
liability for any computer virus which may be transferred via this e-mail 
message. 

-

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

Re: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

[Clemens Ladisch]

Hick Gunter wrote:
> You are in error on number 4.
>
> SQLite reads your CFL table once and performs the aggregation in
> a temporary table that holds 1 entry per "contract" i.e. about
> 1000 rows of max 16 byte records which I estimate to using less than
> 64k.
>
> For each record read, SQLite will update or insert the matching row in
> the temporary table.

sqlite> CREATE VIRTUAL TABLE t USING fts3(x,y);
sqlite> EXPLAIN SELECT x, SUM(y) FROM t GROUP BY x;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
1 SorterOpen 1 3 0 keyinfo(1,BINARY)  00  nColumn=3
...
7 VOpen  0 0 0 vtab:82F090:7F0C37EF47A0  00
8 Noop   0 0 000  Begin Join Loop 0
9 Integer0 10000  r[10]=0
10Integer0 11000  r[11]=0
11VFilter0 1810   00  i0an=r[10] z0an=''
12VColumn0 0 12   00  r[12]=vcolumn(0); t.x
13Sequence   1 13000  r[13]=rowid
14VColumn0 1 14   00  r[14]=vcolumn(1); t.y
15MakeRecord 123 15   00  r[15]=mkrec(r[12..14])
16SorterInsert   1 15000
17VNext  0 12000
...

This copies all records into the temporary table without yet aggregating
them.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

[Hick Gunter]

You are in error on number 4.

SQLite reads your CFL table once and performs the aggregation in a temporary 
table that holds 1 entry per "contract" i.e. about 1000 rows of max 16 byte 
records which I estimate to using less than 64k.

For each record read, SQLite will update or insert the matching row in the 
temporary table.

-Ursprüngliche Nachricht-
Von: Perrin, Lionel [mailto:lionel.per...@moodys.com]
Gesendet: Mittwoch, 18. Dezember 2013 14:58
An: sqlite-users@sqlite.org
Betreff: [sqlite] virtual tables and group by: how could we prevent to retrieve 
all rows from a vtab and sort them later ?

Hello,

I plan to use sqlite to implement an 'aggregation tool'. Basically, the design 
would be the following:


1.   I implement a virtual table CFL(contract, amount) which may provide up 
to 1 billion unsorted rows.

2.   The aggregation phasis will be defined at run time and may consists in 
something as simple as 'select contract, sum(amount) from cfl group by 
contract'.

3.   Since there will be only ~1000 different contracts, I expect SQLite to 
aggregate 'on the fly' the rows from CFL.

4.   Unfortunately, as shown by the explain plan, it looks that SQLite 
first select all rows from CFL. When all rows are retrieved (which implies 
something like a 8Go temporary file), the rows are sorted and aggregated.

[0, 0, 0, "SCAN TABLE CFL VIRTUAL TABLE INDEX 0: (~0 rows)"]

[0, 0, 0, "USE TEMP B-TREE FOR GROUP BY"]


Is there a way to force rows to be dispatched (using the group by) and 
aggregated on the fly instead of being stored, sorted and then aggregated? Note 
that I can't change the production order of the rows in the virtual table.

Thanks for your help,

Regards,

Lionel
-

Moody's monitors email communications through its networks for regulatory 
compliance purposes and to protect its clients, employees and business and 
where allowed to do so by applicable law. Parties communicating with Moody's 
consent to such monitoring by their use of the email communication. The 
information contained in this e-mail message, and any attachment thereto, is 
confidential and may not be disclosed without our express permission. If you 
are not the intended recipient or an employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
you have received this message in error and that any review, dissemination, 
distribution or copying of this message, or any attachment thereto, in whole or 
in part, is strictly prohibited. If you have received this message in error, 
please immediately notify us by telephone, fax or e-mail and delete the message 
and all of its attachments. Thank you. Every effort is made to keep our network 
fr  ee from viruses. You should, however, review this e-mail message, as well 
as any attachment thereto, for viruses. We take no responsibility and have no 
liability for any computer virus which may be transferred via this e-mail 
message.

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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

[Clemens Ladisch]

Perrin, Lionel wrote:
> I plan to use sqlite to implement an 'aggregation tool'. Basically,
> the design would be the following:
>
> 1. I implement a virtual table CFL(contract, amount) which may provide
>up to 1 billion unsorted rows.
>
> 2. The aggregation phasis will be defined at run time and may consists
>in something as simple as 'select contract, sum(amount) from cfl
>group by contract'.
>
> 3. Since there will be only ~1000 different contracts, I expect SQLite
>to aggregate 'on the fly' the rows from CFL.
>
> 4. Unfortunately, as shown by the explain plan, it looks that SQLite
>first select all rows from CFL. When all rows are retrieved (which
>implies something like a 8Go temporary file), the rows are sorted
>and aggregated.
>
>[0, 0, 0, "SCAN TABLE CFL VIRTUAL TABLE INDEX 0: (~0 rows)"]
>[0, 0, 0, "USE TEMP B-TREE FOR GROUP BY"]
>
> Is there a way to force rows to be dispatched (using the group by) and
> aggregated on the fly instead of being stored, sorted and then
> aggregated?

SQLite can optimize GROUP BY this way only if it can prove that the
table is already sorted on the grouping column.  Otherwise, it creates
a temporary index for all records, and then groups on those.

In theory, it would be possible to create a temporary index that stores
the current aggregation _result_ for each group seen so far, and that is
updated dynamically.  However, this is not implemented in SQLite.


If you have only 1000 groups, you can just do a "SELECT x,y FROM cfl"
and do the aggregation in your code, in memory.


> The information contained in this e-mail message, and any attachment
> thereto, is confidential and may not be disclosed [...]

*** DISCLAIMER ***
This e-mail contains public information intended for any subscriber of
this mailing list and for anybody else who bothers to read it; it will
be copied, disclosed and distributed to the public.  If you think you
are not the intended recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or
answering this e-mail, and supersede any disclaimers in those e-mails.
Additionally, disclaimers in those e-mails will cause a legal
processing fee of $42 per line; you have agreed to this by reading
this disclaimer.
*** END OF DISCLAIMER ***


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ?

[Perrin, Lionel]

Hello,

I plan to use sqlite to implement an 'aggregation tool'. Basically, the design 
would be the following:


1.   I implement a virtual table CFL(contract, amount) which may provide up 
to 1 billion unsorted rows.

2.   The aggregation phasis will be defined at run time and may consists in 
something as simple as 'select contract, sum(amount) from cfl group by 
contract'.

3.   Since there will be only ~1000 different contracts, I expect SQLite to 
aggregate 'on the fly' the rows from CFL.

4.   Unfortunately, as shown by the explain plan, it looks that SQLite 
first select all rows from CFL. When all rows are retrieved (which implies 
something like a 8Go temporary file), the rows are sorted and aggregated.

[0, 0, 0, "SCAN TABLE CFL VIRTUAL TABLE INDEX 0: (~0 rows)"]

[0, 0, 0, "USE TEMP B-TREE FOR GROUP BY"]


Is there a way to force rows to be dispatched (using the group by) and 
aggregated on the fly instead of being stored, sorted and then aggregated? Note 
that I can't change the production order of the rows in the virtual table.

Thanks for your help,

Regards,

Lionel
-

Moody's monitors email communications through its networks for regulatory 
compliance purposes and to protect its clients, employees and business and 
where allowed to do so by applicable law. Parties communicating with Moody's 
consent to such monitoring by their use of the email communication. The 
information contained in this e-mail message, and any attachment thereto, is 
confidential and may not be disclosed without our express permission. If you 
are not the intended recipient or an employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
you have received this message in error and that any review, dissemination, 
distribution or copying of this message, or any attachment thereto, in whole or 
in part, is strictly prohibited. If you have received this message in error, 
please immediately notify us by telephone, fax or e-mail and delete the message 
and all of its attachments. Thank you. Every effort is made to keep our network 
fr
 ee from viruses. You should, however, review this e-mail message, as well as 
any attachment thereto, for viruses. We take no responsibility and have no 
liability for any computer virus which may be transferred via this e-mail 
message. 

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

Re: [sqlite] Virtual tables are driving me insane!

[Jörgen Hägglund]

Thanks OBones!

Your link gave me the solution to why my code didn't work!
It was (of course) I who made an error in translating function 
parameters from C to Pascal!


Best regards!

/Jörgen

sqlite-users-requ...@sqlite.org skrev 2012-09-24 18:00:

Re: [sqlite] Virtual tables are driving me insane!



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

Re: [sqlite] Virtual tables are driving me insane!

[OBones]

Hello,

have a look at what's here:

http://code.google.com/p/sv-utils/wiki/Intro

There is a complete and unit tested encapsulation for SQlite and its 
virtual tables.


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

Re: [sqlite] Virtual tables are driving me insane!

[Jay A. Kreibich]

On Fri, Sep 21, 2012 at 11:44:09PM +0200, Jörgen Hägglund scratched on the wall:

> I tried creating a super simple test which just return "default"
> values on all calls.
> This is the order my functions are being called:
> xCreate (enter function)
> xCreate (exit function)
> xConnect (enter function)
> xConnect (exit function)
> At this point I get an access violation in SQLite3.dll (reading of
> address 0008)
> I have tried everything I can think of, but this is how far I get.

  Did you allocate a sqlite3_vtab structure and pass it back?  You also
  need to set *errMsg and (*vtab)->zErrMsg to NULL.

> I then tried a simple test in VS2010 where I include sqlite3.h (that
> is, no DLL).
> Using the same calls/queries as in Delphi, I get this order of calls:
> xConnect (enter function)
> xConnect (exit function)
> xDisconnect (enter function)
> xDisconnect (exit function)
> xConnect (enter function)
> xConnect (exit function)
> xCommit (enter function)
> xCommit (exit function)
> xBestIndex (enter function)
> xBestIndex (exit function)
> And here the application crashes sending Windows to search for a solution.

  That's really odd since, xBestIndex is one of the functions that can
  do absolutly nothing but return, and everything should still work.

> The SQL statement used to create my virtual table, in both cases, is:
> CREATE TABLE x (Severity TEXT, Source TEXT, IP TEXT, Message TEXT)
> 
> And, finally, both cases are run against a newly created database.
> 
> Does anyone have any ideas to what I'm doing wrong?

  Virtual tables are powerful and advanced.  There are no safe-guards or 
  double-checks in the code, as there are with some of the more basic
  interfaces.  SQLite expects your code to be perfect, and if it isn't,
  it will likely crash.  Make sure you read the docs very, very
  carefully and do *exactly* what they say.  Make no assumptions.

> Anyone having some source of how to implement a really simple
> virtual table (in any language)?

  There is a fairly large chapter in "Using SQLite" that attempts to
  cover virtual tables in some detail.  It also goes through two full
  examples.  Even if you don't want to buy the book, you can download
  the example code here:

http://shop.oreilly.com/product/9780596521196.do

  Just use the "Download Example Code" link on the right side of the
  page.  Have a look at the examples from chapter 10.

> I am aware of that I am uncertain of how to implement xBestIndex,
> but that is my next headache, I guess... :)

  Yeah, xBestIndex takes a bit to wrap your head around, but don't
  worry about it too much.  Unless you're writing a VT that provides a
  specialized index, you can usually just ignore it and get the basic
  VT working with table scans before you worry about making the VT
  index aware.  A lot of the VT modules I write don't use xBestIndex.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual tables are driving me insane!

[Clemens Ladisch]

Jörgen Hägglund wrote:
> I'm trying to implement virtual tables, but I seem to be in over
> my head with this.
> I get an access violation in SQLite3.dll (reading of address 0008)
>
> Does anyone have any ideas to what I'm doing wrong?

Not really.  But one mistake I had made was to read this paragraph:
| The xCreate method need not initialize the pModule, nRef, and zErrMsg
| fields of the sqlite3_vtab object. The SQLite core will take care of
| that chore.

... and to believe it.

> Anyone having some source of how to implement a really simple virtual
> table (in any language)?

Below, in C.


Regards,
Clemens
-- 


#include 
#include 
#include 
#include 

static int test_CreateConnect(sqlite3 *db, void *pAux, int argc, const char 
*const argv[], sqlite3_vtab **ppVTab, char **pzErr)
{
int err;

*ppVTab = sqlite3_malloc(sizeof(struct sqlite3_vtab));
if (!*ppVTab)
return SQLITE_NOMEM;
memset(*ppVTab, 0, sizeof(**ppVTab));

err = sqlite3_declare_vtab(db, "CREATE TABLE x(ID INTEGER, Name TEXT)");
if (err != SQLITE_OK) {
sqlite3_free(*ppVTab);
*ppVTab = NULL;
return err;
}

return SQLITE_OK;
}

static int test_BestIndex(sqlite3_vtab *pVTab, sqlite3_index_info *index_info)
{
index_info->idxNum = 0;
index_info->idxStr = "";
index_info->estimatedCost = 100;
return SQLITE_OK;
}

static int test_DisconnectDestroy(sqlite3_vtab *pVTab)
{
sqlite3_free(pVTab);
return SQLITE_OK;
}

struct test_cursor {
sqlite3_vtab_cursor base;
int row;
};

static int test_Open(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor)
{
struct test_cursor *cursor;

cursor = sqlite3_malloc(sizeof(struct test_cursor));
if (!cursor)
return SQLITE_NOMEM;
memset(cursor, 0, sizeof(*cursor));

*ppCursor = >base;

return SQLITE_OK;
}

static int test_Close(sqlite3_vtab_cursor *pCursor)
{
sqlite3_free(pCursor);
return SQLITE_OK;
}

static int test_Eof(sqlite3_vtab_cursor *pCursor)
{
struct test_cursor *cursor = (void *)pCursor;

return cursor->row >= 5;
}

static int test_Filter(sqlite3_vtab_cursor *pCursor, int idxNum, const char 
*idxStr, int argc, sqlite3_value **argv)
{
struct test_cursor *cursor = (void *)pCursor;

cursor->row = 0;
return SQLITE_OK;
}

static int test_Next(sqlite3_vtab_cursor *pCursor)
{
struct test_cursor *cursor = (void *)pCursor;

cursor->row++;
return SQLITE_OK;
}

static int test_Column(sqlite3_vtab_cursor *pCursor, sqlite3_context *context, 
int N)
{
struct test_cursor *cursor = (void *)pCursor;
char str[16];

switch (N) {
case 0:
sqlite3_result_int(context, 42 + cursor->row);
break;
case 1:
sprintf(str, "row%d", cursor->row);
sqlite3_result_text(context, str, -1, SQLITE_TRANSIENT);
break;
}
return SQLITE_OK;
}

static int test_Rowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid)
{
struct test_cursor *cursor = (void *)pCursor;

*pRowid = cursor->row;
return SQLITE_OK;
}

static int test_Rename(sqlite3_vtab* pVTab, const char *zNew)
{
return SQLITE_OK;
}

static sqlite3_module module = {
.iVersion = 1,
.xCreate = test_CreateConnect,
.xConnect = test_CreateConnect,
.xBestIndex = test_BestIndex,
.xDisconnect = test_DisconnectDestroy,
.xDestroy = test_DisconnectDestroy,
.xOpen = test_Open,
.xClose = test_Close,
.xFilter = test_Filter,
.xNext = test_Next,
.xEof = test_Eof,
.xColumn = test_Column,
.xRowid = test_Rowid,
.xRename = test_Rename,
};

int main(int argc, char *argv[])
{
sqlite3 *db;
sqlite3_stmt *stmt;
int res, cols, i;

res = sqlite3_open(":memory:", );
assert(res == SQLITE_OK);

res = sqlite3_create_module(db, "test", , NULL);
assert(res == SQLITE_OK);

res = sqlite3_exec(db, "create virtual table t1 using test", NULL, 
NULL, NULL);
assert(res == SQLITE_OK);

res = sqlite3_prepare_v2(db, "select * from t1", -1, , NULL);
assert(res == SQLITE_OK);
cols = sqlite3_column_count(stmt);
res = sqlite3_step(stmt);
while (res == SQLITE_ROW) {
for (i = 0; i < cols; i++)
printf(" %s", sqlite3_column_text(stmt, i));
putchar('\n');
res = sqlite3_step(stmt);
}
assert(res == SQLITE_DONE);
sqlite3_finalize(stmt);

sqlite3_close(db);

return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org

Re: [sqlite] Virtual tables are driving me insane!

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 21/09/12 14:44, Jörgen Hägglund wrote:
> At this point I get an access violation in SQLite3.dll (reading of
> address 0008)

You are reading the third member of a structure that is NULL.

You should use a debugger that catches this sort of thing and lets you
examine what is going on.  It is also a good idea to use memory debugger
tools.  (The Linux tools are very good at both of those, Windows is
considerably less productive.)

Virtual tables can be very simple to write once you have the glue done
correctly.  For example here is one in Python that provides access to the
files in a bunch of directories where most of the columns correspond to
struct stat members:

 http://apidoc.apsw.googlecode.com/hg/example.html#example-vtable

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBc5ggACgkQmOOfHg372QSInQCfQjVvH/A0Ob8stVDhQwOU1uWi
if0An21SYYi22k9RB9DTiTtnxCImP63b
=PKA0
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual tables are driving me insane!

[Jörgen Hägglund]

Hi all!
First off, my configuration:
Windows 7 Ultimate x64
SQLite3 v3.7.14 Amalgamation, compiled to DLL using VS2010
Used directives:
SQLITE_4_BYTE_ALIGNED_MALLOC (*)
SQLITE_THREADSAFE=2
SQLITE_OMIT_DEPRECATED
SQLITE_DEBUG (*)
SQLITE_MEMDEBUG (*)
(*) Tried both with and without these

Now, my problem...
I am trying to create a virtual table module in my Delphi application 
using my own interface to SQLite3.dll.
All tests so far of my interface are working correctly, i.e. queries, 
preparations, functions etc.
Now, I'm trying to implement virtual tables, but I seem to be in over my 
head with this.
I tried creating a super simple test which just return "default" values 
on all calls.

This is the order my functions are being called:
xCreate (enter function)
xCreate (exit function)
xConnect (enter function)
xConnect (exit function)
At this point I get an access violation in SQLite3.dll (reading of 
address 0008)

I have tried everything I can think of, but this is how far I get.

I then tried a simple test in VS2010 where I include sqlite3.h (that is, 
no DLL).

Using the same calls/queries as in Delphi, I get this order of calls:
xConnect (enter function)
xConnect (exit function)
xDisconnect (enter function)
xDisconnect (exit function)
xConnect (enter function)
xConnect (exit function)
xCommit (enter function)
xCommit (exit function)
xBestIndex (enter function)
xBestIndex (exit function)
And here the application crashes sending Windows to search for a solution.

The SQL statement used to create my virtual table, in both cases, is:
CREATE TABLE x (Severity TEXT, Source TEXT, IP TEXT, Message TEXT)

And, finally, both cases are run against a newly created database.

Does anyone have any ideas to what I'm doing wrong?
Anyone having some source of how to implement a really simple virtual 
table (in any language)?
I am aware of that I am uncertain of how to implement xBestIndex, but 
that is my next headache, I guess... :)


Best regards all!

/Jörgen

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

Re: [sqlite] Virtual Tables: xSync without xBegin

[Jay A. Kreibich]

On Thu, Jul 12, 2012 at 03:05:39PM +0200, OBones scratched on the wall:
> Hello again.
> 
> Does anyone have any suggestion here?

  There is a footnote in "Using SQLite" (Chapter 10: "Virtual Tables and
  Modules," p242) on this.

  The text is:

If you do need to support your own transactions, it is important to
keep the program flow in mind.  xBegin() will always be the first
function called.**  Typically, there will be calls to xUpdate()
followed by a two-step sequence of calls to xSync() and xCommit().
[...]

** In theory.  Currently, calls are made directly to xSync() and
   xCommit()following the call to xCreate().  It isn't clear if
   this is considered a bug or not, so this behavior may change
   in future versions of SQLite.

  "Using SQLite" was written before 3.7 was released, so this isn't a
  new thing.  Overall, I'd call this a documentation bug, as the
  behavior makes sense to me.  The system can't call xBegin() before
  xCreate(), yet creating the table is likely to be a transaction-based
  operation (just like xUpdate()).  Calling xBegin() after doesn't make
  sense either.
  
  I'd try to work around it in a way that will still work correctly
  if it is changed.  A simple "in transaction" flag would allow these
  calls to short-cut out.


"Using SQLite": http://shop.oreilly.com/product/9780596521196.do

   -j
  

> >I'm pushing my experiment with virtual tables a bit further by
> >trying out the transaction functions.
> >As a result, I gave values for the xBegin, xSync, xCommit and
> >xRollback members of my sqlite3_module structure.
> >Then after having registered the module, I sent those two statements:
> >
> >CREATE VIRTUAL TABLE SomeTable USING test(a INTEGER);
> >INSERT INTO SomeTable VALUES (50);
> >
> >via appropriate calls to sqlite3_exec
> >However, this fails quite badly because the xSync function is
> >called outside any transaction that would have been started by a
> >call to xBegin.
> >Basically, xBegin is never called in my simple test, despite the
> >documentation saying that this should not happen.
> >
> >What have I done wrong here?
> >
> >Any suggestion is most welcome
> >
> >Regards
> >Olivier
> >___
> >sqlite-users mailing list
> >sqlite-users@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Tables: retrieving the ppArg given in xFindFunction

[OBones]

Hello,

Is there a place where I should enter such a documentation request?

Regards
Olivier

OBones wrote:

Hello all,

As I'm moving forward in my usage of virtual tables, I'm using the 
xFindFunction method to overload some functions with my own 
implementation.
That works just fine, but I stumbled upon a hurdle that was not 
obvious to solve at first glance.
Inside the xFindFunction, you can give a value to *ppArg that the 
documentation says is user data. But there are no indication as to how 
to retrieve that user data from within the function which pointer is 
set to *pxFunc
I initially expected that this function would receive it as a 
parameter much like xCreate and xConnect, but there is no such 
parameter to that function.
Turns out that one can get the value back by calling 
sqlite3_user_data(pContext) and that works quite nice.


Considering that it's unlikely that the signature for pxFunc would be 
changed, could at least the documentation be updated so that it 
indicates how to retrieve the value with sqlite3_user_data?


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





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

Re: [sqlite] Virtual Tables: xSync without xBegin

[OBones]

Hello again.

Does anyone have any suggestion here?

Regards
Olivier

OBones wrote:

Hello all,

I'm pushing my experiment with virtual tables a bit further by trying 
out the transaction functions.
As a result, I gave values for the xBegin, xSync, xCommit and 
xRollback members of my sqlite3_module structure.

Then after having registered the module, I sent those two statements:

CREATE VIRTUAL TABLE SomeTable USING test(a INTEGER);
INSERT INTO SomeTable VALUES (50);

via appropriate calls to sqlite3_exec
However, this fails quite badly because the xSync function is called 
outside any transaction that would have been started by a call to xBegin.
Basically, xBegin is never called in my simple test, despite the 
documentation saying that this should not happen.


What have I done wrong here?

Any suggestion is most welcome

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





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

Re: [sqlite] Virtual Tables: idxStr parameter for xBestIndex and xFilter

[OBones]

Jay A. Kreibich wrote:

On Tue, Jun 26, 2012 at 03:56:33PM +0200, OBones scratched on the wall:

Hello all,

As I'm experimenting with Virtual Tables, I have had to implement
xBestIndex and xFilter.
The documentation says that idxNum and idxStr are of no importance
to the SQLite core and that it's our responsibility to make sure
xFilter and xBestIndex agree on the meaning.
This is fine by me but I'd like to suggest to change the type of
idxStr from "char *" to "void *" to make it even clearer to everyone
that this parameter is not used in any specific way inside SQLite
core.

   99% of the modules that use idxStr pass a string.  It is usually an
   SQL statement used by xFilter() that has parameter names mapped to
   the parameters defined by xBestIndex().  This is the primary use of
   this API.

   I see you're point, but the variable name is idx"Str", and that's how
   most people use it.  I don't think redefining it as a void* implies
   any more or less ownership by SQLite than a char*.  The docs are
   clear, the code is clear, and that's how the vast majority of users
   use it.  Forcing them to cast back and forth for the exception to the
   rule seems like a bit of waste.

-j
Thanks for the clarification, I assumed it was the case and was just 
making a suggestion.


Regards
Olivier


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

Re: [sqlite] Virtual tables: rename, disconnect and reconnect

[OBones]

Jay A. Kreibich wrote:

On Tue, Jun 26, 2012 at 03:47:07PM +0200, OBones scratched on the wall:

I would have thought that since the disconnect was given a valid
pVTab, this one could have been given back to xConnect when
reconnecting the database after its rename.

   Except SQLite doesn't store the data in that structure.  sqlite3_vtab
   is a "live" structure, in the sense that it only exists in-memory in
   an active database process.  The whole idea of xCreate()/xConnect()
   is to re-create this data structure, every time, from the original
   virtual table declaration.  The whole point of
   xDestroy()/xDisconnect() is to clean up, destroy, and free the
   sqlite3_vtab structure.  It only exists between a pair of those calls.
I understand that SQLite doesn't store the data, but I was thinking that 
it stored the pointer to it during the "rename" internal procedure. 
Hence my question about that.



   Regardless, any possible saving of the state is up to the application.

Which is fine by me.


Is this behavior expected? I'm fine with my current solution, but I
was expecting xConnect to get a non zero *pPVTab most, if not all
the time.

   This is exactly how the API is designed.
Fair enough, I'm in discovery mode here, with some habits from my 
experience but no hard expectations.



   You seem to be fighting the API, which makes me think there might be
   a slight disconnect between how you expect the API to work and how
   the API was designed to be used.
I'm definitely not fighting, merely discovering and learning along the 
way. And because there were not so many discussions on virtual tables in 
the archive, I thought that answers to my questions would help others in 
the future.


Thanks for the pointers, I already read the documentation page and will 
seek for the book.


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

Re: [sqlite] Virtual Tables: idxStr parameter for xBestIndex and xFilter

[Jay A. Kreibich]

On Tue, Jun 26, 2012 at 03:56:33PM +0200, OBones scratched on the wall:
> Hello all,
> 
> As I'm experimenting with Virtual Tables, I have had to implement
> xBestIndex and xFilter.
> The documentation says that idxNum and idxStr are of no importance
> to the SQLite core and that it's our responsibility to make sure
> xFilter and xBestIndex agree on the meaning.

> This is fine by me but I'd like to suggest to change the type of
> idxStr from "char *" to "void *" to make it even clearer to everyone
> that this parameter is not used in any specific way inside SQLite
> core.

  99% of the modules that use idxStr pass a string.  It is usually an
  SQL statement used by xFilter() that has parameter names mapped to
  the parameters defined by xBestIndex().  This is the primary use of
  this API.

  I see you're point, but the variable name is idx"Str", and that's how
  most people use it.  I don't think redefining it as a void* implies
  any more or less ownership by SQLite than a char*.  The docs are
  clear, the code is clear, and that's how the vast majority of users
  use it.  Forcing them to cast back and forth for the exception to the
  rule seems like a bit of waste.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual tables: rename, disconnect and reconnect

[Jay A. Kreibich]

On Tue, Jun 26, 2012 at 03:47:07PM +0200, OBones scratched on the wall:
> Hello all,
> 
> I'm experimenting with virtual tables and I must say that it's a
> very nice feature to have, it saves me from inserting millions of
> records in a database when I can simply map the source file in
> memory and read it directly.
> However, I have a small issue when renaming a database. What is
> happening is the following:
> 
> xDisconnect
> xRename
> xConnect

> Those calls are quite expected and fine by me.
> However, what surprises me is that xConnect is given a zero ppVTab
> parameter (that is, *ppVTab == 0)

  The whole point of both the xCreate() and xConnect() functions is to
  allocate, define, and return the sqlite3_vtab structure.  It must be
  allocated by the xCreate()/xConnect() function because it is standard
  practice to over-allocate the data structure so that the virtual table
  can append additional data fields to the end of the sqlite3_vtab
  structure.  Since SQLite has no idea how much extra memory you might
  want, it is up to the functions to allocate this structure and pass
  it back to SQLite via the ppVTab parameter.

  This exact same pattern can be seen in xOpen(), where the virtual
  table is tasked with allocating, init'ing, and returning the
  sqlite3_vtab_cursor structure.  In xClose(), the virtual table must
  clean-up and deallocate the same structure.  Again, it is standard
  practice for virtual tables to over-allocate this data structure, so
  the actual memory allocation needs to be done by the virtual table
  code, not by SQLite.

> I would have thought that since the disconnect was given a valid
> pVTab, this one could have been given back to xConnect when
> reconnecting the database after its rename.

  Except SQLite doesn't store the data in that structure.  sqlite3_vtab
  is a "live" structure, in the sense that it only exists in-memory in
  an active database process.  The whole idea of xCreate()/xConnect()
  is to re-create this data structure, every time, from the original
  virtual table declaration.  The whole point of
  xDestroy()/xDisconnect() is to clean up, destroy, and free the
  sqlite3_vtab structure.  It only exists between a pair of those calls.

  If you're trying to keep track of some type of connection
  information or external file-name or something, it is best that such
  information is part of the CREATE VIRTUAL TABLE statement, as this
  data will always be available.

  In theory, your application could save the partial state of any
  custom fields in xDisconnect() and then read them back in xConnect().
  Any application that uses "shadow tables" (such as the built-in FTS
  and R-Tree modules) essentially does this, as there is state saved
  into the database the virtual table uses to provide its service.
  These types of virtual tables have different xCreate()/xConnect()
  functions and different xDestroy()/xDisconnect() functions, as one
  set of functions needs to create/drop the shadow tables, while the
  other set only needs to verify that they're there.  Regardless, any
  possible saving of the state is up to the application.

> Is this behavior expected? I'm fine with my current solution, but I
> was expecting xConnect to get a non zero *pPVTab most, if not all
> the time.

  This is exactly how the API is designed.

  You seem to be fighting the API, which makes me think there might be
  a slight disconnect between how you expect the API to work and how
  the API was designed to be used.  I would recommend you have a good
  read through all the docs on the SQLite website about virtual tables,
  especially this:   http://www.sqlite.org/vtab.html

  The book "Using SQLite" (O'Reilly) also has an entire chapter on
  virtual tables and how to use them.  It happens to be the longest
  chapter in the book, and walks through two full examples.  One of the
  examples is using a virtual table to map web-server logs to an SQLite
  table without importing the data.  From what you said, that sounds
  somewhat similar to your problem.

  http://shop.oreilly.com/product/9780596521196.do


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Tables: xSync without xBegin

[OBones]

Hello all,

I'm pushing my experiment with virtual tables a bit further by trying 
out the transaction functions.
As a result, I gave values for the xBegin, xSync, xCommit and xRollback 
members of my sqlite3_module structure.

Then after having registered the module, I sent those two statements:

CREATE VIRTUAL TABLE SomeTable USING test(a INTEGER);
INSERT INTO SomeTable VALUES (50);

via appropriate calls to sqlite3_exec
However, this fails quite badly because the xSync function is called 
outside any transaction that would have been started by a call to xBegin.
Basically, xBegin is never called in my simple test, despite the 
documentation saying that this should not happen.


What have I done wrong here?

Any suggestion is most welcome

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

[sqlite] Virtual Tables: retrieving the ppArg given in xFindFunction

[OBones]

Hello all,

As I'm moving forward in my usage of virtual tables, I'm using the 
xFindFunction method to overload some functions with my own implementation.
That works just fine, but I stumbled upon a hurdle that was not obvious 
to solve at first glance.
Inside the xFindFunction, you can give a value to *ppArg that the 
documentation says is user data. But there are no indication as to how 
to retrieve that user data from within the function which pointer is set 
to *pxFunc
I initially expected that this function would receive it as a parameter 
much like xCreate and xConnect, but there is no such parameter to that 
function.
Turns out that one can get the value back by calling 
sqlite3_user_data(pContext) and that works quite nice.


Considering that it's unlikely that the signature for pxFunc would be 
changed, could at least the documentation be updated so that it 
indicates how to retrieve the value with sqlite3_user_data?


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

[sqlite] Virtual Tables: idxStr parameter for xBestIndex and xFilter

[OBones]

Hello all,

As I'm experimenting with Virtual Tables, I have had to implement 
xBestIndex and xFilter.
The documentation says that idxNum and idxStr are of no importance to 
the SQLite core and that it's our responsibility to make sure xFilter 
and xBestIndex agree on the meaning.
This is fine by me but I'd like to suggest to change the type of idxStr 
from "char *" to "void *" to make it even clearer to everyone that this 
parameter is not used in any specific way inside SQLite core.
Maybe this was already requested in the past and denied because the 
interface of virtual tables cannot change, but I could not find any 
trace of such thing.


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

[sqlite] Virtual tables: rename, disconnect and reconnect

[OBones]

Hello all,

I'm experimenting with virtual tables and I must say that it's a very 
nice feature to have, it saves me from inserting millions of records in 
a database when I can simply map the source file in memory and read it 
directly.
However, I have a small issue when renaming a database. What is 
happening is the following:


xDisconnect
xRename
xConnect

Those calls are quite expected and fine by me.
However, what surprises me is that xConnect is given a zero ppVTab 
parameter (that is, *ppVTab == 0)
I would have thought that since the disconnect was given a valid pVTab, 
this one could have been given back to xConnect when reconnecting the 
database after its rename.
As it turns out, I use pAux to point to my own structure, so I was able 
to create a list of "dangling" databases to pick from when connecting.
That is to say, when xDisconnect is called, the database is put in that 
list of "dangling" databases and using the name in xConnect, its value 
is retrieved at that time.
I know that the documentation says that xConnect and xCreate can be the 
same, and that most implementation that I have seen are actually the 
same, but when they are not, the current situation makes it difficult 
(at first sight) not to call again lengthy code when *ppVTab is zero.


Is this behavior expected? I'm fine with my current solution, but I was 
expecting xConnect to get a non zero *pPVTab most, if not all the time.


Thanks in advance for any clarification on this subject.

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

Re: [sqlite] Virtual Tables and tcl / apsw

[Dan Kennedy]

On 10/25/2011 04:28 PM, Alexey Pechnikov wrote:

2011/10/25 Dan Kennedy:

Not possible. The Tcl interface has no bindings for either the
virtual table or VFS interfaces.


But why? Is there any technical/ideological problems?



None that are insurmountable, I would think. Just that
those interfaces were not considered all that useful for
scripting languages. That's a matter of opinion though
obviously.



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

Re: [sqlite] Virtual Tables and tcl / apsw

[Alexey Pechnikov]

2011/10/25 Dan Kennedy :
> Not possible. The Tcl interface has no bindings for either the
> virtual table or VFS interfaces.

But why? Is there any technical/ideological problems?

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Tables and tcl / apsw

[Dan Kennedy]

On 10/25/2011 02:31 PM, sqlite-us...@h-rd.org wrote:

Hi,

I have some questions on virtual tables and tcl compared to perl and
python/apsw.

As I understand you can build your own virtual table implementation with
apsw (and also with perl). Is this also possible with tclsqlite?
Any pointers greatly appreciated, I could not find it in the docs.

A bit related is a question on the vfs. apsw allows to implement a vfs.
Is this also possible with tcl?


Not possible. The Tcl interface has no bindings for either the
virtual table or VFS interfaces.

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

[sqlite] Virtual Tables and tcl / apsw

[sqlite-us...@h-rd.org]

Hi,

I have some questions on virtual tables and tcl compared to perl and  
python/apsw.


As I understand you can build your own virtual table implementation  
with apsw (and also with perl).  Is this also possible with tclsqlite?

Any pointers greatly appreciated, I could not find it in the docs.

A bit related is a question on the vfs.  apsw allows to implement a  
vfs.  Is this also possible with tcl?


thanks,


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

Re: [sqlite] Virtual tables and OR clause

[Dan Kennedy]

On 05/04/2011 08:58 PM, Schrum, Allan wrote:
> Hi Folks,
>
> Using virtual tables the WHERE clause is broken up and sent to the "best 
> index" function to determine the best index. Then the "filter" function is 
> called to perform the actual work. I've noticed that the SQLITE engine seems 
> to process OR clauses outside of the virtual table process, while AND clauses 
> are provided to the "filter" function to use. How can we get the OR clauses 
> sent to the "filter" function where we can make use of that information?
>
> Using SQLITE 3.6.18.

If you do this:

   SELECT * FROM vtab WHERE a=1 OR b=2

Then SQLite will invoke xBestIndex once for each of the two
conditions and once for a full-scan (no WHERE conditions at
all).

If it thinks there is advantage in doing so (based on the
estimatedCost values returned by the three xBestIndex calls),
SQLite may implement the query by using xFilter/xNext to get
all the a=1 rows from the virtual table, then again for all of
the b=2 rows. It uses the rowid values to avoid returning
duplicates to the caller.

It is not possible for SQLite to request a (a=1 OR b=2) with
a single xFilter/xNext scan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual tables and OR clause

[Schrum, Allan]

Hi Folks,

Using virtual tables the WHERE clause is broken up and sent to the "best index" 
function to determine the best index. Then the "filter" function is called to 
perform the actual work. I've noticed that the SQLITE engine seems to process 
OR clauses outside of the virtual table process, while AND clauses are provided 
to the "filter" function to use. How can we get the OR clauses sent to the 
"filter" function where we can make use of that information?

Using SQLITE 3.6.18.

Thanks,

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

Re: [sqlite] virtual tables

[Vivien Malerba]

On 23 November 2010 17:39, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 11/23/2010 02:04 AM, Vivien Malerba wrote:
>> The Libgda library (http://www.gnome-db.org) uses virtual tables
>
> Are you sure?  It looks like an abstraction layer that sits above several
> databases, with similar functionality to ODBC/JDBC.

Yes, it is, but with some more features.

Basically here is how one uses it to execute statements involving data
stored in two different databases:
* open a connection (let's name it c1) to, for example, a PostgreSQL db,
* open a connection (c2) to, for example, a MySQL db,
* open a connection (c3) binding c1 and c2 and execute the SELECT,
UPDATE,... statements in c3 using tables from c1 and c2

c3 is actually an SQLite (in memory) connection which takes all the
tables in c1 and makes them appear as tables in c3 using SQLite's
virtual tables (and the same for c2's tables). The SQL used in c3 is
parsed and executed by SQLite (internally it creates some other
statements to access data from c1 and c2's tables).

It is also possible to add individual tables in c3 (for example from a
CVS file), or add other connections (c4, c5,...) to c3.

Regards,

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

Re: [sqlite] virtual tables

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/23/2010 02:04 AM, Vivien Malerba wrote:
> The Libgda library (http://www.gnome-db.org) uses virtual tables

Are you sure?  It looks like an abstraction layer that sits above several
databases, with similar functionality to ODBC/JDBC.

We are talking about virtual tables as in the functionality builtin to
SQLite - you issue SQL queries that the SQLite query parser and optimiser
use and provide backend data:

  http://www.sqlite.org/vtab.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkzr7iIACgkQmOOfHg372QRlTACgttISlPwzRpWorAO8iSGTEZj9
BJEAoOAglqefWS+syplfDLTSeq88vua6
=Vazm
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] virtual tables

[Vivien Malerba]

The Libgda library (http://www.gnome-db.org) uses virtual tables to
enable one to execute statements on several tables from several
database backends (SQlite, PostgreSQL, MySQL, Oracle, Jdbc, SqlCipher,
MDB) and CSV files.

Regards,

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

Re: [sqlite] virtual tables

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/22/2010 06:59 PM, dcharno wrote:
> What are some of the things that people use virtual tables for?  Are 
> there any good example usages?

FTS3 uses them.  Various CSV extensions do too.

With my APSW package I include a virtual table that lets you access CouchDB:

 http://apidoc.apsw.googlecode.com/hg/couchdb.html

There are a list of other suggestions in the documentation:

 http://apidoc.apsw.googlecode.com/hg/vtable.html

In the example code I map the filesystem as a vtable,  This also shows how
much effort it is to write one in a higher level language like Python:

 http://apidoc.apsw.googlecode.com/hg/example.html#example-vtable

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkzrZUwACgkQmOOfHg372QR1CQCZAaOBwSBoMuBWdM5q0vONUqCp
VhYAn1upr5J7htGR3mGeBPFUHR/CR2pK
=ifrv
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] virtual tables

[Ben Harper]

I have a database library that abstracts various DB types, such as DBF, 
shapefiles, CSV, PostGIS, etc.
I use the Sqlite virtual table mechanism to provide SQL functionality on top of 
any of these DB types.
Normally one would only need to do this for crude databases, such as a CSV 
file, but
the manner in which I cater for long transactions makes it necessary for me to 
strap Sqlite onto,
for instance, a modified Postgres table that has not yet been committed to the 
DB.

Ben

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of dcharno
Sent: 23 November 2010 05:00 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] virtual tables

What are some of the things that people use virtual tables for?  Are 
there any good example usages?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] virtual tables

[boscowitch]

Am Montag, den 22.11.2010, 21:59 -0500 schrieb dcharno:
> What are some of the things that people use virtual tables for?  Are 
> there any good example usages?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 


It's sth very special and i think virtual tables are not only usefull
for it but take a look at the FTS3 extension.
http://www.sqlite.org/fts3.html

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

[sqlite] virtual tables

[dcharno]

What are some of the things that people use virtual tables for?  Are 
there any good example usages?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual tables

[Matt Young]

Got that one two, thanks, this mail list saves time.

On 5/6/10, Jay A. Kreibich  wrote:
> On Thu, May 06, 2010 at 12:31:26AM -0700, Matt Young scratched on the wall:
>
>> Can I create a virtual table mapped to an existing table in my
>> database?
>
>   You'll need to write a fair amount of code, but yes.
>
>> Does this let me alias a whole table?
>
>   It lets you do pretty much anything you want.  If all you want is a
>   static alias or logical mapping, a VIEW would be a lot easier.
>
>> Thinking out loud,
>> does this give me ability to write a query on the virtual table, then
>> remap the virtual table to a current table and execute the query?
>
>   By "a current table" I assume you mean one of a set of tables.
>
>   Still "Yes," although it is a lot more complex and a lot more
>   inefficient than that.  Whatever the problem is, I would try to look
>   for solutions involving JOINs and/or VIEWs before diving into virtual
>   tables.  Virtual tables are very powerful, but they're also somewhat
>   complex.
>
>-j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] virtual tables, how experimental? what's the history of it?

[Jay A. Kreibich]

On Thu, May 06, 2010 at 12:10:56PM -0700, Trey Jackson scratched on the wall:


> So I was looking at the Virtual Table http://www.sqlite.org/vtab.html

> Anyway, can someone provide history on how much has it has changed over
> releases, when it was introduced?  Just so I have some contextual basis on
> whether or not to explore this option.

  Virtual Tables were first introduced in 2006 (IIRC).  There have been
  minor changes since the very first version, but the current API has
  remained stable for several years.  Additionally, several key bits
  of SQLite code, including the Full Text Search system, depend on the
  current Virtual Table API.

  I don't speak for the SQLite team, but it seems very unlikely that
  the API would change at this point in time.  If you're wondering
  about API stability and the risk of committing development time to
  the APIs, I would have a very high degree of confidence.

   -j



  Hum...  it looks like DRH just re-classified the APIs anyways.
  Problem solved!

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual tables

[Jay A. Kreibich]

On Thu, May 06, 2010 at 12:31:26AM -0700, Matt Young scratched on the wall:

> Can I create a virtual table mapped to an existing table in my
> database? 

  You'll need to write a fair amount of code, but yes.

> Does this let me alias a whole table? 

  It lets you do pretty much anything you want.  If all you want is a
  static alias or logical mapping, a VIEW would be a lot easier.

> Thinking out loud,
> does this give me ability to write a query on the virtual table, then
> remap the virtual table to a current table and execute the query?

  By "a current table" I assume you mean one of a set of tables.

  Still "Yes," although it is a lot more complex and a lot more
  inefficient than that.  Whatever the problem is, I would try to look
  for solutions involving JOINs and/or VIEWs before diving into virtual
  tables.  Virtual tables are very powerful, but they're also somewhat
  complex.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual tables

[Andy Gibbs]

I don't think there is anything in the vanilla sqlite to do this, but...

Have a look at src/test8.c in the main (not amalgamation) source code... 
this may give you some ideas.  I don't think it does what you want exactly, 
but a quick skim through makes me think it has some pointers in the right 
direction if you're happy to get your fingers dirty!



- Original Message - 
From: "Matt Young" 
Newsgroups: gmane.comp.db.sqlite.general
To: "General Discussion of SQLite Database" 

Sent: Thursday, May 06, 2010 9:31 AM
Subject: Virtual tables


> Can I create a virtual table mapped to an existing table in my
> database?  Does this let me alias a whole table?  Thinking out loud,
> does this give me ability to write a query on the virtual table, then
> remap the virtual table to a current table and execute the query?
> ___
> sqlite-users mailing list
> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

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

[sqlite] Virtual tables

[Matt Young]

Can I create a virtual table mapped to an existing table in my
database?  Does this let me alias a whole table?  Thinking out loud,
does this give me ability to write a query on the virtual table, then
remap the virtual table to a current table and execute the query?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Tables problem with queries like to "rowid in (...)"

[Alexey Pechnikov]

Hello!

This query produce full-scan of the FTS3 virtual table 'data':
sqlite> select count(*) from data where rowid in (1);
^CError: interrupted
After 30 minuts I cancel the query.

sqlite> explain query plan select count(*) from data where rowid=1;
0|0|TABLE data VIRTUAL TABLE INDEX 1:

sqlite> explain query plan select count(*) from data where rowid in (1);
0|0|TABLE data VIRTUAL TABLE INDEX 0:

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Tables & Transactions

[Jay A. Kreibich]

  Documentation error and/or bug:

  http://sqlite.org/vtab.html#xsync

  "This method is only invoked after call to the xBegin method..."

  Not true (3.6.20).  An xSync/xCommit pair is given after the initial
  xCreate call.  I'm not sure if that is intentional or not.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] virtual tables may not be indexed

[P Kishor]

On Wed, Aug 5, 2009 at 5:37 PM, Lukas Haase wrote:
> Hi list,
>
> I have a huge problem: A database with 2 HTML fragements should
> contain a fulltext index. For that reason I put all data into a virtual
> table:
>
> CREATE VIRTUAL TABLE topics USING fts3(
>        topicID INTEGER,
>        topic_title VARCHAR(200) COLLATE NOCASE,
>        topic TEXT,
>        TOKENIZE simple);
>
> topic contains the HTML fragments, topic_title the title and topicID is
> needed for locating a specific entry. Well, and that's actually the
> problem... Before (i.e. without FTS) I did:
>
> SELECT topic FROM topics WHERE topicID=9874;
>
> which was quite fast. Now this is very, very slow (a few seconds!). I
> guess this is because topicID is not a primary key any more and no index
> is defined. So I wanted to create an index but I got the error in the
> subject. Really big problem :-( But I really need a way to *quickly*
> locate an entry by its ID. I do NOT want to store the data twice :-(

That is strange. I would have thought that a query of the form

SELECT topic FROM topics WHERE topicID=9874;

would use an index on topicID

whereas, to use FTS for search, which you would use only for textual
searches, and, for which, you would have a fts-specific table called,
say 'fts_topics', you would have to do something like

SELECT * FROM topics t JOIN (
SELECT rowid, Snippet(fts_topics, '', '', '...') AS context
FROM fts_topics
WHERE topic MATCH ?
  ) ft ON t. topicID = ft.rowid

That way you would get the best of both indexed SQL as well full-text searches.


>
> What I am doing wrong?
>
> Best regards,
> Luke
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] virtual tables may not be indexed

[Scott Hess]

fts has an implicit index on the docid (aka rowid), and a fulltext
index on the terms in the columns.  All columns are treated as TEXT,
you can say INTEGER or VARCHAR, but they are TEXT.

The end of this page:
   http://code.google.com/apis/gears/api_database.html
has an example of how you might construct two tables which can be
joined to work like I believe you want things to work.  Basically, you
have an fts-only table, then another table which contains the things
you wish to index in more traditional SQL ways.

-scott



On Wed, Aug 5, 2009 at 3:37 PM, Lukas Haase wrote:
> Hi list,
>
> I have a huge problem: A database with 2 HTML fragements should
> contain a fulltext index. For that reason I put all data into a virtual
> table:
>
> CREATE VIRTUAL TABLE topics USING fts3(
>        topicID INTEGER,
>        topic_title VARCHAR(200) COLLATE NOCASE,
>        topic TEXT,
>        TOKENIZE simple);
>
> topic contains the HTML fragments, topic_title the title and topicID is
> needed for locating a specific entry. Well, and that's actually the
> problem... Before (i.e. without FTS) I did:
>
> SELECT topic FROM topics WHERE topicID=9874;
>
> which was quite fast. Now this is very, very slow (a few seconds!). I
> guess this is because topicID is not a primary key any more and no index
> is defined. So I wanted to create an index but I got the error in the
> subject. Really big problem :-( But I really need a way to *quickly*
> locate an entry by its ID. I do NOT want to store the data twice :-(
>
> What I am doing wrong?
>
> Best regards,
> Luke
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] virtual tables may not be indexed

[Lukas Haase]

Hi list,

I have a huge problem: A database with 2 HTML fragements should 
contain a fulltext index. For that reason I put all data into a virtual 
table:

CREATE VIRTUAL TABLE topics USING fts3(
topicID INTEGER,
topic_title VARCHAR(200) COLLATE NOCASE,
topic TEXT,
TOKENIZE simple);

topic contains the HTML fragments, topic_title the title and topicID is 
needed for locating a specific entry. Well, and that's actually the 
problem... Before (i.e. without FTS) I did:

SELECT topic FROM topics WHERE topicID=9874;

which was quite fast. Now this is very, very slow (a few seconds!). I 
guess this is because topicID is not a primary key any more and no index 
is defined. So I wanted to create an index but I got the error in the 
subject. Really big problem :-( But I really need a way to *quickly* 
locate an entry by its ID. I do NOT want to store the data twice :-(

What I am doing wrong?

Best regards,
Luke

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

Re: [sqlite] Virtual tables

[Kees Nuyt]

On Sat, 13 Sep 2008 18:36:26 +0200, you wrote:

>Exactly Stephen! I was trying to dump a database and I was wondering  
>how to deal with virtual tables.
>
>I think that a good way to dump a database skipping internally  
>generated real tables could be to:
>- first create all tables that contains the CREATE VIRTUAL TABLE  
>statement
>- then get the name of all the tables (not virtual) created inside the  
>db (save their names somewhere)
>- and at the end copy all the tables whose name was not previously saved
>
>I wondering if is there a simpler/better solution...

Just a suggestion (wild guess, that is):
Perhaps the rootpage column in sqlite_master can be of help?
I don't have a virtual table handy to try it myself.

select name,rootpage
from sqlite_master
where type = 'table'
order by name;

>---
>Marco Bambini
>http://www.sqlabs.net
>http://www.sqlabs.net/blog/
>http://www.sqlabs.net/realsqlserver/
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual tables

[Marco Bambini]

Exactly Stephen! I was trying to dump a database and I was wondering  
how to deal with virtual tables.

I think that a good way to dump a database skipping internally  
generated real tables could be to:
- first create all tables that contains the CREATE VIRTUAL TABLE  
statement
- then get the name of all the tables (not virtual) created inside the  
db (save their names somewhere)
- and at the end copy all the tables whose name was not previously saved

I wondering if is there a simpler/better solution...
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/



On Sep 13, 2008, at 7:25 PM, Stephen Woodbridge wrote:

> Kishor,
>
> I think Marco may want to be able to know how to determine which  
> tables
> in a DB are real tables and which ones below to virtual tables. If you
> want to do something like dump tables from the database, you do not  
> want
> to be dumping all the internally generated real tables. It might be  
> nice
> if there were some way to identify if a given table was:
>
> 1) a normal table
> 2) a virtual table
> 3) a child of a virtual table
>
> But I'm only guess that this might be what Marco wants? Marco?
>
> -Steve
>
> P Kishor wrote:
>> On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote:
>>> Yes but creating a virtual tables involves the creations of other
>>> related tables ...
>>
>> Well, the FTSn mechanism does all the extra table voodoo for you, so
>> you don't have to be bothered about it. From what it seems like, the
>> other magic tables are not virtual tables. In any case, we are not
>> advised to mess with them unless we have security clearance.
>>
>>> does all the virtual table implementations (fts1,
>>> fts2, fts3) follow the same schema or it is implementation  
>>> dependent?
>>
>> Probably there is some difference from FTS1..3, but I have no
>> recollection of 2, and I never implemented 1.
>>
>> In any case, the table that is VIRTUAL is the one that you create
>> yourself. And, per your original question of how to identify it,  
>> well,
>> it says so in the schema. There might be a PRAGMA command for it as
>> well, but nothing could be clearer than the word VIRTUAL right there
>> in the schema.
>>
>>
>>>
>>> ---
>>> Marco Bambini
>>> http://www.sqlabs.net
>>> http://www.sqlabs.net/blog/
>>> http://www.sqlabs.net/realsqlserver/
>>>
>>>
>>>
>>>
>>> On Sep 13, 2008, at 4:02 PM, P Kishor wrote:
>>>
 On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote:
> What is the best way to identify virtual tables inside a sqlite
> database?


 isn't the schema enough? In my world it says

 CREATE VIRTUAL TABLE ...

>
> Thanks a lot.
> ---
> Marco Bambini
> http://www.sqlabs.net
> http://www.sqlabs.net/blog/
> http://www.sqlabs.net/realsqlserver/
>
>>>
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Virtual tables

[Stephen Woodbridge]

Kishor,

I think Marco may want to be able to know how to determine which tables 
in a DB are real tables and which ones below to virtual tables. If you 
want to do something like dump tables from the database, you do not want 
to be dumping all the internally generated real tables. It might be nice 
if there were some way to identify if a given table was:

1) a normal table
2) a virtual table
3) a child of a virtual table

But I'm only guess that this might be what Marco wants? Marco?

-Steve

P Kishor wrote:
> On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote:
>> Yes but creating a virtual tables involves the creations of other
>>  related tables ...
> 
> Well, the FTSn mechanism does all the extra table voodoo for you, so
> you don't have to be bothered about it. From what it seems like, the
> other magic tables are not virtual tables. In any case, we are not
> advised to mess with them unless we have security clearance.
> 
>> does all the virtual table implementations (fts1,
>>  fts2, fts3) follow the same schema or it is implementation dependent?
> 
> Probably there is some difference from FTS1..3, but I have no
> recollection of 2, and I never implemented 1.
> 
> In any case, the table that is VIRTUAL is the one that you create
> yourself. And, per your original question of how to identify it, well,
> it says so in the schema. There might be a PRAGMA command for it as
> well, but nothing could be clearer than the word VIRTUAL right there
> in the schema.
> 
> 
>>
>>  ---
>>  Marco Bambini
>>  http://www.sqlabs.net
>>  http://www.sqlabs.net/blog/
>>  http://www.sqlabs.net/realsqlserver/
>>
>>
>>
>>
>> On Sep 13, 2008, at 4:02 PM, P Kishor wrote:
>>
>>  > On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote:
>>  >> What is the best way to identify virtual tables inside a sqlite
>>  >> database?
>>  >
>>  >
>>  > isn't the schema enough? In my world it says
>>  >
>>  > CREATE VIRTUAL TABLE ...
>>  >
>>  >>
>>  >> Thanks a lot.
>>  >> ---
>>  >> Marco Bambini
>>  >> http://www.sqlabs.net
>>  >> http://www.sqlabs.net/blog/
>>  >> http://www.sqlabs.net/realsqlserver/
>>  >>
>>
>>> ___
>>  > sqlite-users mailing list
>>  > sqlite-users@sqlite.org
>>  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>  ___
>>  sqlite-users mailing list
>>  sqlite-users@sqlite.org
>>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> 
> 

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

Re: [sqlite] Virtual tables

[P Kishor]

On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote:
> Yes but creating a virtual tables involves the creations of other
>  related tables ...

Well, the FTSn mechanism does all the extra table voodoo for you, so
you don't have to be bothered about it. From what it seems like, the
other magic tables are not virtual tables. In any case, we are not
advised to mess with them unless we have security clearance.

> does all the virtual table implementations (fts1,
>  fts2, fts3) follow the same schema or it is implementation dependent?

Probably there is some difference from FTS1..3, but I have no
recollection of 2, and I never implemented 1.

In any case, the table that is VIRTUAL is the one that you create
yourself. And, per your original question of how to identify it, well,
it says so in the schema. There might be a PRAGMA command for it as
well, but nothing could be clearer than the word VIRTUAL right there
in the schema.


>
>
>  ---
>  Marco Bambini
>  http://www.sqlabs.net
>  http://www.sqlabs.net/blog/
>  http://www.sqlabs.net/realsqlserver/
>
>
>
>
> On Sep 13, 2008, at 4:02 PM, P Kishor wrote:
>
>  > On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote:
>  >> What is the best way to identify virtual tables inside a sqlite
>  >> database?
>  >
>  >
>  > isn't the schema enough? In my world it says
>  >
>  > CREATE VIRTUAL TABLE ...
>  >
>  >>
>  >> Thanks a lot.
>  >> ---
>  >> Marco Bambini
>  >> http://www.sqlabs.net
>  >> http://www.sqlabs.net/blog/
>  >> http://www.sqlabs.net/realsqlserver/
>  >>
>
> > ___
>  > sqlite-users mailing list
>  > sqlite-users@sqlite.org
>  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual tables

[Marco Bambini]

Yes but creating a virtual tables involves the creations of other  
related tables ... does all the virtual table implementations (fts1,  
fts2, fts3) follow the same schema or it is implementation dependent?

---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/



On Sep 13, 2008, at 4:02 PM, P Kishor wrote:

> On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote:
>> What is the best way to identify virtual tables inside a sqlite
>> database?
>
>
> isn't the schema enough? In my world it says
>
> CREATE VIRTUAL TABLE ...
>
>>
>> Thanks a lot.
>> ---
>> Marco Bambini
>> http://www.sqlabs.net
>> http://www.sqlabs.net/blog/
>> http://www.sqlabs.net/realsqlserver/
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Virtual tables

[P Kishor]

On 9/13/08, Marco Bambini <[EMAIL PROTECTED]> wrote:
> What is the best way to identify virtual tables inside a sqlite
>  database?


isn't the schema enough? In my world it says

CREATE VIRTUAL TABLE ...

>
>  Thanks a lot.
>  ---
>  Marco Bambini
>  http://www.sqlabs.net
>  http://www.sqlabs.net/blog/
>  http://www.sqlabs.net/realsqlserver/
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual tables

[Marco Bambini]

What is the best way to identify virtual tables inside a sqlite  
database?

Thanks a lot.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/



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

Re: [sqlite] Virtual tables declaration statements

[Aladdin Lampé]

Hi Fred,

I agree with you but I think that a specific rule could be added in the parser 
in order to deal with an identifier containing dots only when it is used after 
a "using" in a "create virtual table" statement... Am I wrong?
Anyway, this is not a very big issue :-)

Aladdin

> Date: Mon, 5 May 2008 09:59:52 -0500
> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Virtual tables declaration statements
>
> Don't think that will happen. "Dot" notation is used as in
> "databasename.tablename" and is therefore a restricted use notation.
>
> Fred
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Aladdin Lampe
> Sent: Monday, May 05, 2008 8:57 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Virtual tables declaration statements
>
>
>
> Hi list!
>
> I've just finished a set of sqlite3 virtual tables and I would like to name
> them with a "dot notation", ie "data.source1", "data.source2" created like
> that:
> sqlite3_create_module(db, "data.source1", , 0);
> sqlite3_create_module(db, "data.source2", , 0);
>
> ... and then use the following calling convention:
> create virtual table tab1 using data.source1(arg1, arg2);
> create virtual table tab2 using data.source2(arg1, arg2, arg3);
>
> ... but the parser claims a syntax error near "." :-(
>
> Note that the following three notations just work, but I find them a bit
> weird and not very user-friendly:
> create virtual table tab1 using 'data.source1'(arg1, arg2);
> create virtual table tab1 using "data.source1"(arg1, arg2);
> create virtual table tab1 using [data.source1](arg1, arg2);
>
> Is there any chance that this would be solved in a future version of SQLite,
> ie considering adding the "." to the authorized characters in an identifier
> following the key word "using"?
>
> BTW, I do not see lots of virtual tables out there. I find it's a really
> great and distinctive feature and I think that, following the recent
> tutorial ideas, our SQLite community should set up a sharepoint with users
> contributions (just like the "/contrib" area, but in a more user friendly,
> collaborative and "visible" way). Same thing for user-defined functions,
> aggregates, collations...
> That would help people starting with sqlite and allow us to build some
> useful tools/examples that everybody could use or inspire of...
>
> Have a nice day,
> Aladdin
>
> _
> Retouchez, classez et partagez vos photos gratuitement avec le logiciel
> Galerie de Photos !
> http://www.windowslive.fr/galerie/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_
Avec Hotmail, vos e-mails vous suivent partout ! Mettez Hotmail sur votre 
mobile !
http://www.messengersurvotremobile.com/?d=hotmail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual tables declaration statements

[Fred Williams]

Don't think that will happen.  "Dot" notation is used as in
"databasename.tablename" and is therefore a restricted use notation.

Fred

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Aladdin Lampe
Sent: Monday, May 05, 2008 8:57 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Virtual tables declaration statements



Hi list!

I've just finished a set of sqlite3 virtual tables and I would like to name
them with a "dot notation", ie "data.source1", "data.source2" created like
that:
sqlite3_create_module(db, "data.source1", , 0);
sqlite3_create_module(db, "data.source2", , 0);

... and then use the following calling convention:
create virtual table tab1 using data.source1(arg1, arg2);
create virtual table tab2 using data.source2(arg1, arg2, arg3);

... but the parser claims a  syntax error near "." :-(

Note that the following three notations just work, but I find them a bit
weird and not very user-friendly:
create virtual table tab1 using 'data.source1'(arg1, arg2);
create virtual table tab1 using "data.source1"(arg1, arg2);
create virtual table tab1 using [data.source1](arg1, arg2);

Is there any chance that this would be solved in a future version of SQLite,
ie considering adding the "." to the authorized characters in an identifier
following the key word "using"?

BTW, I do not see lots of virtual tables out there. I find it's a really
great and distinctive feature and I think that, following the recent
tutorial ideas, our SQLite community should set up a sharepoint with users
contributions (just like the "/contrib" area, but in a more user friendly,
collaborative and "visible" way). Same thing for user-defined functions,
aggregates, collations...
That would help people starting with sqlite and allow us to build some
useful tools/examples that everybody could use or inspire of...

Have a nice day,
Aladdin

_
Retouchez, classez et partagez vos photos gratuitement avec le logiciel
Galerie de Photos !
http://www.windowslive.fr/galerie/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Virtual tables declaration statements

[Aladdin Lampé]

Hi list!

I've just finished a set of sqlite3 virtual tables and I would like to name 
them with a "dot notation", ie "data.source1", "data.source2" created like that:
sqlite3_create_module(db, "data.source1", , 0);
sqlite3_create_module(db, "data.source2", , 0);

... and then use the following calling convention:
create virtual table tab1 using data.source1(arg1, arg2);
create virtual table tab2 using data.source2(arg1, arg2, arg3);

... but the parser claims a  syntax error near "." :-(

Note that the following three notations just work, but I find them a bit weird 
and not very user-friendly:
create virtual table tab1 using 'data.source1'(arg1, arg2);
create virtual table tab1 using "data.source1"(arg1, arg2);
create virtual table tab1 using [data.source1](arg1, arg2);

Is there any chance that this would be solved in a future version of SQLite, ie 
considering adding the "." to the authorized characters in an identifier 
following the key word "using"?

BTW, I do not see lots of virtual tables out there. I find it's a really great 
and distinctive feature and I think that, following the recent tutorial ideas, 
our SQLite community should set up a sharepoint with users contributions (just 
like the "/contrib" area, but in a more user friendly, collaborative and 
"visible" way). Same thing for user-defined functions, aggregates, collations...
That would help people starting with sqlite and allow us to build some useful 
tools/examples that everybody could use or inspire of...

Have a nice day,
Aladdin

_
Retouchez, classez et partagez vos photos gratuitement avec le logiciel Galerie 
de Photos !
http://www.windowslive.fr/galerie/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Tables performance

[Eric Grange]

We've begun implementing some virtual tables with latest SQLite build
and run into a few questions performance-wise, all these questions
revolve around the same issue (join performance).

1) Is there a way to know which fields are going to be potentially
requested by xColumn? For instance if I have a virtual table with 10
columns, but in the query only 2 of them are referred, is there a way to
know which these are? (without parsing the SQL independantly from SQLite)
I'm asking because the virtual tables we've been considering would be
aggregates, with potentially hundreds of virtual columns, and knowing
which xColumn calls may happen after an xFilter would be quite helpful.

2) Since SQLite uses loops for joins, this can result in a lot of
xFilter calls, and when the actual data is in an other DB server, this
means a lot of actual queries, which are slow, even when xFilter queries
for a specific primary key/index. To reduce this load, we've used
temporary tables with good success (selecting from the virtual table
into the temp table, then joining against the temp table).
Would there be a way to use sub-selects to that purpose? (ie. without
manually creating the temp table)

3) As soon as there are "or" or "in" terms in the SQL, xBestIndex isn't
solicited, and xFilter requests everything unfiltered, the solution to
that we found was to "union" the selects, which doesn't exactly improve
readability. Is there a better solution?
In our case, merely having a range constraint would already be enough to
drastically improve the row count of xFilter (ie. "field in (40, 50,
70)" being presented as a "field>=40 and field<=70" constraint)

Thanks,

Eric

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

[sqlite] Virtual tables cannot be used with a shared cache?

[Joe Wilson]

In the documentation for sqlite3_enable_shared_cache it says:

  ** Virtual tables cannot be used with a shared cache.  When shared
  ** cache is enabled, the [sqlite3_create_module()] API used to register
  ** virtual tables will always return an error.

Just curious why is there such a restriction. The two concepts seem to 
be orthogonal - I wouldn't think a virtual table could make use of the 
page cache.

Is there a way to have regular tables/indexes use a shared cache and 
virtual tables not?


  

Check out the hottest 2008 models today at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] (Virtual Tables) xFilter - called only once per xOpen ?

[drh]

"Michael Brehm" <[EMAIL PROTECTED]> wrote:
> Hi again, still working on my Virtual Table implementation/object model
> here, and I have a question about the relationship between xOpen and
> xFilter.  By perusing the code and playing around with some sample
> statements, it looks to me like there will only be one call to xFilter for
> any given cursor instance.  Is this a fact I can count on, 

No.  You can get xFilter to be called multiple times by using
it in a join.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] (Virtual Tables) xFilter - called only once per xOpen ?

[Michael Brehm]

Hi again, still working on my Virtual Table implementation/object model
here, and I have a question about the relationship between xOpen and
xFilter.  By perusing the code and playing around with some sample
statements, it looks to me like there will only be one call to xFilter for
any given cursor instance.  Is this a fact I can count on, or am I staying
up too late and missing something silly?
 
What I would like to do is delay construction of a Cursor wrapper object
instance until xFilter is called so that the arguments can be passed into a
constructor, rather than constructing the object at xOpen and calling an
initialization method on it at xFilter.  For example (pseudo-code):
 
int xOpen(...)
{
// All that happens in here for this model would be the creation of the
// specialized sqlite_vtab_cursor structure.  It would remain
disassociated
// with a wrapper class instance until we hit the xFilter callback
 
customized_sqlite_vtab_cursor c = new customized_sqlite_vtab_cursor();
c->instancePointer = NULL;
*ppcursor = c;
}
 
int xFilter(...)
{
customized_sqlite_vtab_cursor* c =
reinterpret_cast(pCursor);

// Note that the constructor for the wrapper instance is here, not in
xOpen.
// This is what I would *like* to do so the index and argument data can
be
// provided in it's constructor, but I am concerned there are situations
where
// xFilter may be called multiple times against a single cursor
instance, OR
// xFilter will never be called
 
CursorWrapperClass instance = new CursorWrapperClass(idxNum, idxStr,
argc, argv);
 
c->instancePointer = instance;// Set up the instance pointer
c->eof = !instance->MoveNext();// Move to the first row
automatically
}
 
int xClose(...)
{
customized_sqlite_vtab_cursor* c =
(customized_sqlite_vtab_cursor)pCursor;
c->instancePointer->Close();
delete c->instancePointer;
delete c;
}
 
Does that make enough sense?  It is a little late here, so if I've totally
dumbfounded everyone I apologize :)
 
By the way, if you haven't played with virtual tables yet, they are
extremely slick.  I've only implemented a simple directory module for
testing here with my object model, but the entire concept is well thought
out and provides an unprecidented level of control over index selection.  I
can't wait to finish and get it into people's hands.  Very very very nice,
SQLite team!
 
Mike Brehm
 

[sqlite] Virtual tables and locking.

[Guillaume Fougnies]

hi,

Is it possible to add some informations about locking
mechanism in the wiki page VirtualTables?

I'm facing concurrency problem in developing a cluster
Sqlite database. One table per database. Multiple databases
indexed like x.db, x.db-001, x.db-002, x.db-003, ... each
containing a choosen number of rows (like 100K rows with
incremental prikey).
Conceptually, this should increase concurrent access to data...
1) "CREATE VIRTUAL TABLE" applied on the x.db
2) Update a row in x.db-001 via x.db vtab
3) Update a row in x.db-002 via x.db vtab

I'm afraid each update processed on the x.db to modify x.db-001
via the virtual table module could create a lock on x.db and
x.db-001. So the trick will be useless.

I was thinking integrating some other storage engine like PostgreSQL
or MySQL with virtual tables... but the locking concurrency problem
could be the same.

Any ideas?

Thanks.
--
Guillaume

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Virtual tables

[Dennis Cote]

Gerry Snyder wrote:


BTW, I really liked the video presentation mentioned on the list 
recently, not least because I had not been sure how to pronounce 
SQLite (I had thought it might be ESS CUE LITE instead of the proper 
ESS CUE ELL (L)ITE)


Well, that's really only one person's opinion of how to pronounce 
"SQLite". Even if he is the author. :-)


I personally prefer the ESS CUE LITE pronunciation because it is 
shorter. On the other hand, I also pronounce SQL as ESS CUE ELL, and not 
SEQUEL as so many other do.


The same issue has surrounded Linux for a long while. There are several 
different pronunciations given to the letter "i". Some (like me) use a 
short i sound (like fin), some use a long i sound (like fine), some use 
a long e sound (like bee). The later is derived from the correct 
pronunciation of Linus Torvlads first name in his native Finish. Linus 
himself uses the first style as demonstrated at 
http://uranus.it.swin.edu.au/~jn/linux/saylinux.htm


Dennis Cote

Re: [sqlite] Virtual tables

[Gerry Snyder]

[EMAIL PROTECTED] wrote:

http://www.sqlite.org/cvstrac/wiki?p=VirtualTables

This is a feature currently under active development.
--
D. Richard Hipp   <[EMAIL PROTECTED]>
  

Wow!

I really look forward to the day when this and full-text search are 
ready for prime-time use (including accessibility using the Tcl bindings).


Two giant leaps forward coming soon to a PC near you!


Thanks for a great product, DRH (and others)!!!

Gerry

BTW, I really liked the video presentation mentioned on the list 
recently, not least because I had not been sure how to pronounce SQLite 
(I had thought it might be ESS CUE LITE instead of the proper ESS CUE 
ELL (L)ITE)

Re: [sqlite] Virtual tables

[Dennis Cote]

Matthew Jones wrote:
Is there any straight forward way I could use SQLite3 with virtual 
tables. By that I mean SQLite contains all the table and index 
definitions but none of the data - actually it will contain the data 
for some tables but not others. I only need to query access to the 
tables so I need to intercept the fetching of data from these virtual 
tables & indices. From what I have read in the architecture, it seems 
like I want to intercept the btree implementation for these virtual 
tables but that doesn't mean it has to be done that way.



Matthew,

See http://www.sqlite.org/cvstrac/wiki?p=VirtualTables for the approach 
that is being built into sqlite at this very moment.


Dennis Cote

Re: [sqlite] Virtual tables

[drh]

Matthew Jones <[EMAIL PROTECTED]> wrote:
> Is there any straight forward way I could use SQLite3 with virtual 
> tables. By that I mean SQLite contains all the table and index 
> definitions but none of the data - actually it will contain the data for 
> some tables but not others. I only need to query access to the tables so 
> I need to intercept the fetching of data from these virtual tables & 
> indices. From what I have read in the architecture, it seems like I want 
> to intercept the btree implementation for these virtual tables but that 
> doesn't mean it has to be done that way.
> 

http://www.sqlite.org/cvstrac/wiki?p=VirtualTables

This is a feature currently under active development.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Virtual tables

[Matthew Jones]

Is there any straight forward way I could use SQLite3 with virtual 
tables. By that I mean SQLite contains all the table and index 
definitions but none of the data - actually it will contain the data for 
some tables but not others. I only need to query access to the tables so 
I need to intercept the fetching of data from these virtual tables & 
indices. From what I have read in the architecture, it seems like I want 
to intercept the btree implementation for these virtual tables but that 
doesn't mean it has to be done that way.


Thanks