Re: [sqlite] Virtual table used to query big external database
Hi Ben, You said: "You will notice that for certain queries, xBestIndex/xFilter does not get used. That means that the SQLite engine is going to have to walk through your entire table, evaluating the conditions on each field." I am not aware of this behavior. Could you cite an example? I thought xOpen and xFilter always kick off a walk over a range of virtual table rows. Prerequisite of xOpen is index id returned by xBestIndex. So you can't just walk a virtual table without xBestIndex having been called, unless I missed a boat somewhere It is true that xOpen/xFilter/xClose can be called multiple times for an index returned by xBestIndex. So if you allocate any objects in xBestIndex, you can't let go of them in xClose. Memory leak is unavoidable unless you modify the virtual table interface as I had to do to pass additional information in the calls (prepared statement handle). When I have more time, I'd like to suggest changes to the virtual table call interface to facilitate resource management in the VT module. This doesn't come up with the FTS VT modules because they seem to always return a small index to a static structure. One other consideration: If the query or update has to walk a large range of rows, there's no way for the core to tell the VTM that it's done accessing a given row as it sweeps the cursor forward. You can end up with a huge number of virtual table rows in memory. Regards, Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Ben Harper > Sent: Tuesday, April 01, 2008 4:53 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Virtual table used to query big > external database > > The only limitation imposed by SQL that I can think of would > be inefficiencies in its query plan builder. That is the part > that figures out how to use the indexes available in the > database in order to execute the SQL query most efficiently. > So it really depends on what type of SQL queries you are > going to be running against this huge DB. > The dangers are easy to evaluate: > Create a quick-and-dirty dummy virtual table mechanism, and > respond to the xBestIndex/xFilter functions. You will notice > that for certain queries, xBestIndex/xFilter does not get > used. That means that the SQLite engine is going to have to > walk through your entire table, evaluating the conditions on > each field. This is obviously what you wish to avoid. As an > example, I noticed briefly (I did not investigate > thoroughly) that having an OR condition in a query would > prevent the indexes from being used. That was some time ago, > and it was before the rewrite of the SQL VM, so I don't know > if that still applies. You'll have to investigate your > potential queries yourself. A simple query such as "WHERE > myvalue > 100" should definitely invoke the use of your own indexes. > > Ben > > > On Tue, Apr 1, 2008 at 10:12 PM, Aladdin Lampé > <[EMAIL PROTECTED]> wrote: > > > > Hi all! > > > > Very often, when people ask this list why they have trouble > managing in sqlite a "big table" (50 million lines or more > than 10 Go), they are told that sqlite is an embedded > database and is not meant to be used for very big databases/tables. > > > > I'm currently in the process of designing a specific, > read-only, sqlite "virtual table" in order to enable sqlite > to access data stored in an external database which is > specially designed to handle very big tables. > > > > My final objective is to be able to easily query a big > external table (stored in another database) through the - > excellent - sqlite interface. > > > > Now I have this terrible doubt: will the existing sqlite > "limitations" for big sqlite tables also apply to my > read-only virtual tables? > > > > Thus... am I currently losing my time developing such a > "virtual table" with this objective in mind? Or is there a > better way to achieve my objective? > > > > Thank you for your help! > > > > _ > > Votre contact a choisi Hotmail, l'e-mail ultra sécurisé. > Créez un compte gratuitement ! > > http://www.windowslive.fr/hotmail/default.asp > > ___ > > 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] sqlite with emacs (OT)
If you are talking about the sqlite3 shell, add -interactive to the command line. Cheers, Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Derrell Lipman > Sent: Wednesday, April 02, 2008 9:36 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] sqlite with emacs (OT) > > On Wed, Apr 2, 2008 at 10:30 AM, Wensui Liu > <[EMAIL PROTECTED]> wrote: > > > good morning, all, > > when i tried to use sqlite in emacs with shell mode, it > doesn't work. > > is there a interface for sqlite in emacs / xemacs? > > > > I use sqlite in emacs shell mode all the time. What problem > are you encountering? (Note that sqlite's built-in readline > history mechanism won't be available, but shell mode's own > history will be.) > > Derrell > ___ > 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] SQLite Like Query Optimization
...snip... > > LIKE operators cannot use indices unless the index is case > insensitive. Use GLOB for case sensitive fields. > Richard - i'm not sure i understand "unless the index is case insensitive." How does that relate to: sqlite> create table t (a varchar(10) primary key, b, c); sqlite> pragma CASE_SENSITIVE_LIKE=OFF; sqlite> explain query plan select * from t where a like 'a%'; 0|0|TABLE t sqlite> pragma CASE_SENSITIVE_LIKE=ON; sqlite> explain query plan select * from t where a like 'a%'; 0|0|TABLE t WITH INDEX sqlite_autoindex_t_1 sqlite> Dumb question: Is CASE_SENSITIVE_LIKE a different concept from "case sensitive index"? > LIKE and GLOB operators cannot use indices if the pattern > begins with a wildcard. > > Nothing in SQLite will use an index if you are connecting > terms using OR. > > It looks like what you really want to use here is a full-text > index. Please read about the FTS3 support in SQLite. That > seems to be what you are trying to accomplish. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > Thanks, Mark ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual table xBestIndex and when to free index resource
SQLite experts:
The xBestIndex method of the SQLite virtual table interface implemented by the
VT module returns an output to the core by setting idxNum member var of struct
sqlite3_index_info to a value that is meaningful to the VT module. Assume that
a memory resource was created in conjunction with the chosen index that will
hold information passed by xFilter.
The question is: How can VT module tell when it is safe to release that
resource? I'm thinking, it's when the associated statement is finalized. But
how does the VT module know that? I have found that xClose() call is not the
answer because I have stumbled on a test sequence that shows this to be unsafe:
do_test update-1.0 {
execsql {DELETE FROM t1}
execsql {insert into t1 values(1,2,3)}
execsql {SELECT * FROM t1 }
execsql {UPDATE t1 SET y=3 WHERE x=1}
execsql {SELECT * FROM t1 }
} {1 2 3 1 3 3}
After execution of the UPDATE, the VT module call sequence for the next SELECT
does not include xBestIndex as I was expecting. It calls xFilter with the
idxNum that the previous SELECT created (I think). I crash and burn because I
released the index resource in the xClose call for the first SELECT.
I'd be most appreciative if an expert could steer me in the right direction.
Mark
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] RE: Assertion for Virtual Table INSERT
I tracked down the cause of this problem to an experimental change made locally to SQLite. :-( All is well with SQLite! :-) Mark > -Original Message- > From: Evans, Mark (Tandem) > Sent: Friday, January 18, 2008 3:45 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Assertion for Virtual Table INSERT > > Hi all, > > SQLite version 3.5.3 - with custom virtual table module. > > I am having a problem with assertion failure following > processing of an INSERT statement relative to a virtual > table. Everything works through the call for VUpdate, then > it asserts in vdbe.o in leg for Dup. I'm wondering if this > is a bug in SQLite vdbe code generation. What is the purpose > of Dup following VUpdate below? I have duplicated the > problem on two different architectures. > > > sqlite> explain insert into t1 values (0,'foo', 0); > 0|Goto|0|16| > 1|Null|0|0| > 2|Null|0|0| > 3|Integer|0|0| > 4|String8|0|0|foo > 5|Integer|0|0| > 6|VUpdate|1|5|vtab:9E002B8:FD0A60 > 7|Dup|2|1| > 8|NotNull|1|10| > 9|Halt|19|2|T1.X may not be NULL > 10|Dup|0|1| > 11|NotNull|1|13| > 12|Halt|19|2|T1.Z may not be NULL > 13|MakeRecord|3|0|dad > 14|Insert|0|3|T1 > 15|Halt|0|0| > 16|Transaction|0|1| > 17|VerifyCookie|0|2| > 18|VBegin|0|0|vtab:9E002B8:FD0A60 > 19|Goto|0|1| > 20|Noop|0|0| > sqlite> insert into t1 values (0,'foo', 0); > sqlite3: ../../../../Source/sqlite-3.5.3-hp/src/vdbe.c:893: > sqlite3VdbeExec: Assertion `pFrom<=pTos && pFrom>=p->aStack' failed. > Abort (core dumped) > [EMAIL PROTECTED] MSE]$ > > Many thanks, > Mark > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite character comparisons
Doh! Wrong thread. :) :) :) > -Original Message- > From: Zbigniew Baniewski [mailto:[EMAIL PROTECTED] > Sent: Friday, January 18, 2008 5:14 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] SQLite character comparisons > > On Fri, Jan 18, 2008 at 11:04:09PM +, Evans, Mark (Tandem) wrote: > > > Think of NULL as "value is unknown". > > > > With zero length blob/text, value is known: 0-length blob/text > > OK, I will :) > -- > pozdrawiam / regards > > Zbigniew Baniewski > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite character comparisons
Think of NULL as "value is unknown". With zero length blob/text, value is known: 0-length blob/text Cheers, Mark > -Original Message- > From: Zbigniew Baniewski [mailto:[EMAIL PROTECTED] > Sent: Friday, January 18, 2008 4:51 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] SQLite character comparisons > > On Fri, Jan 18, 2008 at 02:13:51PM -0800, Darren Duncan wrote: > > > Don't read too much into that statement; I'm was not raising the > > auto-trim thing. > > Yes, yes - I know... "Roma locuta"... > -- > pozdrawiam / regards > > Zbigniew Baniewski > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Assertion for Virtual Table INSERT
Hi all, SQLite version 3.5.3 - with custom virtual table module. I am having a problem with assertion failure following processing of an INSERT statement relative to a virtual table. Everything works through the call for VUpdate, then it asserts in vdbe.o in leg for Dup. I'm wondering if this is a bug in SQLite vdbe code generation. What is the purpose of Dup following VUpdate below? I have duplicated the problem on two different architectures. sqlite> explain insert into t1 values (0,'foo', 0); 0|Goto|0|16| 1|Null|0|0| 2|Null|0|0| 3|Integer|0|0| 4|String8|0|0|foo 5|Integer|0|0| 6|VUpdate|1|5|vtab:9E002B8:FD0A60 7|Dup|2|1| 8|NotNull|1|10| 9|Halt|19|2|T1.X may not be NULL 10|Dup|0|1| 11|NotNull|1|13| 12|Halt|19|2|T1.Z may not be NULL 13|MakeRecord|3|0|dad 14|Insert|0|3|T1 15|Halt|0|0| 16|Transaction|0|1| 17|VerifyCookie|0|2| 18|VBegin|0|0|vtab:9E002B8:FD0A60 19|Goto|0|1| 20|Noop|0|0| sqlite> insert into t1 values (0,'foo', 0); sqlite3: ../../../../Source/sqlite-3.5.3-hp/src/vdbe.c:893: sqlite3VdbeExec: Assertion `pFrom<=pTos && pFrom>=p->aStack' failed. Abort (core dumped) [EMAIL PROTECTED] MSE]$ Many thanks, Mark
RE: [sqlite] Re: Distinguishing empty-result SELECT from INSERT, UPDATE, DELETE, etc.
Joe & Igor - thanks for the tips! Mark > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 27, 2007 5:39 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Re: Distinguishing empty-result SELECT > from INSERT, UPDATE, DELETE, etc. > > See also sqlite3_sql() if sqlite3_prepare_v2() or > sqlite3_prepare16_v2() was used. > > http://www.sqlite.org/cvstrac/chngview?cn=4543 > > --- Igor Tandetnik <[EMAIL PROTECTED]> wrote: > > Evans, Mark (Tandem) <[EMAIL PROTECTED]> wrote: > > > Would I be the first person to wish there were a way for, say, a > > > JDBC driver that uses SQLite to be able to tell what kind of SQL > > > statement it is executing? > > > > sqlite3_column_count returns 0 for a non-SELECT statement, and a > > non-zero value for SELECT. > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Distinguishing empty-result SELECT from INSERT, UPDATE, DELETE, etc.
Greetings lite-meisters, Would I be the first person to wish there were a way for, say, a JDBC driver that uses SQLite to be able to tell what kind of SQL statement it is executing? Unless I missed an API, sqlite3_step() indicates either a SELECT that matched at least one row, or any statement including SELECT (with no matches). One use case is to facilitate capture of statistics of statement types executed, number of rows selected, inserted, updated, etc. AFAICS, the driver would have to parse the first word of each statement and behave accordingly. If there's a way to dredge this from SQLite, I'd appreciate a heads up. Thanks, Mark
RE: [sqlite] Suggests for improving the SQLite website
Just visited the new front page. I like it a lot. But one of the crown jewel features (at least to me) is buried in the fine print: Virtual Tables. Is this an oversight? I would at least put a blurb on the Features or Distinctive Features page unless there's a motivation not to do so (because it's experimental interface?). Just my 2c. Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Suggests for improving the SQLite website
I like 3 too. There's a minor glitch on Firefox - increasing font size causes the right side of menu bar to be whited out but display when cursor hovers. Is this the bug Joe refers to below? Mark > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Friday, November 09, 2007 1:30 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Suggests for improving the SQLite website > > --- [EMAIL PROTECTED] wrote: > > I put up 4 variations. Please, everyone, offer your opinions: > > > >(1) http://sqlite.hwaci.com/v1/ No CSS of any kind. > >(2) http://sqlite.hwaci.com/v2/ CSS menus with > rounded corners > >(3) http://sqlite.hwaci.com/v3/ CSS menus with square corners > >(4) http://sqlite.hwaci.com/v4/ CSS font specification only > > > > (2) and (3) do not work on IE6. (1) has ugly fonts, I am told. > > That leaves me with (4). > > > > I suppose we could go with (4) now and change it later > > I prefer (3). (SUPPORT 'T' render bug in Firefox aside). > > (4) would also be good if you just centered the contents as > in (3) to look better on wider resolutions. > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection > around http://mail.yahoo.com > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Suggests for improving the SQLite website
I love the website. Suggested improvements: - link to the users site from home: http://www.mail-archive.com/sqlite-users@sqlite.org/info.html - a publicity page that contains links to interviews, reviews, etc. - expand the list of things that cannot be done with virtual tables (it lists triggers but not check constraints and maybe other things) - developers page with pictures and brief bios Cheers, Mark > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 08, 2007 10:29 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Suggests for improving the SQLite website > > We are looking at renovating the design of the SQLite website > and would love to have suggestions from the community. If > you have any ideas on how to improve the SQLite website, > please constribute either to the mailing list or directly to me. > > Here are some links to competing database products that might > inspire comments: > >http://www.postgresql.org/ >http://www.firebirdsql.org/ >http://www.hsqldb.org/ >http://opensource.ingres.com/ >http://db.apache.org/derby/ >http://exist.sourceforge.net/ > > Among the comments received already are these: > > (1) It is not clear from the homepage that the software > is free. > (2) Half the page is devoted to talking about bugs in > the software. This suggests low quality. > (3) The "News" contains scary words: "radical changes". > (4) Three releases in as many months suggests the > code is not stable. > (5) Move the BigNameUsers to the front page > (see http://www.sqlite.org/wiki?p=BigNameUsers) > (6) Need more eye-candy. > > I do not necessary agree with the above comments, but I am > open to any and all ideas. You will not hurt my feels, so > speak freely. > > Thanks in advance for your input. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How should virtual table module return its own error messages?
Thanks Andy > -Original Message- > From: Andrew McDermott [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 07, 2007 7:47 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How should virtual table module return > its own error messages? > > > Hi, > > > Greetings, > > > > I followed one of the wiki notes on how a virtual table > module method > > such as xUpdate should set error message for consumption > upstream. A > > code snippet is: > > ... > > sqlite3_free(aVTab->zErrMsg); > > aVTab->zErrMsg = sqlite3_mprintf( "No such FOO: %s", foo ); > > ... > > > > where aVTab points to sqlite3_vtab. > > > > But my message gets ignored and replaced with stock error message > > associated with the return code such as: > > > > "SQL logic error or missing database" > > > > Did I follow an obsolete note? Is it possible to do what I > want? Is > > there a special return code I should return in such cases > rather than > > SQLITE_ERROR? > > See: http://www.sqlite.org/cvstrac/tktview?tn=2215 > > It would be great if this could be fixed. > > Thanks, > Andy. > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Virtual Table LIKE operator constaint?
To answer my own question: The magic is in either SQLITE_CASE_SENSITIVE_LIKE compile time option or CASE_SENSITIVE_LIKE pragma. I have seen this before, but it just didn't stick in my small memory bank. Mark > -Original Message- > From: Evans, Mark (Tandem) > Sent: Monday, November 05, 2007 4:34 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Virtual Table LIKE operator constaint? > > Hi all, > > In a virtual table context, I expected a statement such as > > select * from t where x like 'abc%' > > to pass a constraint for the LIKE clause to xBestIndex method > of virtual table module but it doesn't get such a constraint. > I expected SQLITE_INDEX_CONSTRAINT_MATCH. Do I have to do > something special to enable this constraint to be passed or > does it just do a full-table scan when it sees the LIKE, > GLOB, etc. operator? > > Thanks, > Mark > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Virtual Table LIKE operator constaint?
Hi all, In a virtual table context, I expected a statement such as select * from t where x like 'abc%' to pass a constraint for the LIKE clause to xBestIndex method of virtual table module but it doesn't get such a constraint. I expected SQLITE_INDEX_CONSTRAINT_MATCH. Do I have to do something special to enable this constraint to be passed or does it just do a full-table scan when it sees the LIKE, GLOB, etc. operator? Thanks, Mark
RE: [sqlite] How many virtual table implemenations are there out there?
After further research, I realize that suggestion 2 below is not necessary. I had not realized that SQLite core supports default value declarations in call to sqlite3_declare_vtab(). So by defining any default values when defining virtual table columns, one knows in xUpdate callback for INSERT that any NULL values in the value list mean that either user specified NULL in the value list or else there is no default value. Cool! Joe Wilson asked, regarding suggestion 1: <> Joe, good question. The intent was to save having to do an extra read for a row update on a file system (HP NonStop Enscribe) that requires one to do a read-with-lock before doing updating write. The application domain is such that most of the rows selected by WHERE clause of UPDATE will be updated. But I had not considered the possibility of sub-queries. Thanks for broadening my horizons. Mark > -Original Message- > From: Evans, Mark (Tandem) > Sent: Sunday, November 04, 2007 7:12 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] How many virtual table implemenations > are there out there? > > I would like to second the suggestion of Ralf and add a > couple of my own: > > 1) Modify xOpen interface to: > int ndmOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor, int > update) ; > > where 'update' is an additional argument that indicates > whether the context is for read-only select (0), or update > (1), or delete (2). > > 2) Add a function, > int sqlite3_value_default_type( sqlite3_value* ); > > that returns true for v if: > sqlite3_value_type(v) returns SQLITE_NULL and v represents > omitted column from column-list of INSERT statement: > > INSERT [OR conflict-algorithm] INTO [database-name .] > table-name [(column-list)] VALUES(value-list) | INSERT [OR > conflict-algorithm] INTO [database-name .] table-name > [(column-list)] select-statement > > Suggestion 1 is for virtual table implementations for > external data store where record locks need to be set on read > in order to do update or delete. > > Suggestion 2 allows virtual table implementation to properly > choose between setting a column value to NULL or a default > value known to the virtual table module . > > There is also a VT bug I just submitted (ticket 2759): > > select * from VT where X is NULL; > > causes assertion failure in bestVirtualIndex(). This is a > show-stopper for VT implementations that allow NULL for > unkown column values. > > Warm regards, > Mark > > > -Original Message- > > From: Ralf Junker [mailto:[EMAIL PROTECTED] > > Sent: Friday, November 02, 2007 7:38 AM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] How many virtual table implemenations > are there > > out there? > > > > > > >But to do so seems likely to require an incompatible change > > to the virtual-table interface. > > > > Could I kindly request an addition to the incompatible > change to the > > virtual-table interface? > > > > I would very much appreciate a corresponding function to > > > > function xRowID( > > pCursor: psqlite3_vtab_cursor; > > pRowID: PInt64): Integer; > > > > which would notify virtual table implementations that the > the SQLite > > engine no longer uses this particular RowID like, for example: > > > > function xRowID_Done( > > pCursor: psqlite3_vtab_cursor; > > pRowID: PInt64): Integer; > > > > The reason behind this is that some DB engines store RowIDs / > > BookMarks in malloced memory structures. Obviously, they have to be > > freed when no longer in use. Unfortunately, the current VT > interface > > does not give notice when this is the case. With xRowID_Done, > > implementations will be able to free malloced memory when no longer > > needed by SQLite and thus avoid accumulating malloced > RowIDs until the > > table is closed. > > > > Ralf > > > > > > -- > > --- > > To unsubscribe, send email to [EMAIL PROTECTED] > > -- > > --- > > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How many virtual table implemenations are there out there?
I would like to second the suggestion of Ralf and add a couple of my own: 1) Modify xOpen interface to: int ndmOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor, int update) ; where 'update' is an additional argument that indicates whether the context is for read-only select (0), or update (1), or delete (2). 2) Add a function, int sqlite3_value_default_type( sqlite3_value* ); that returns true for v if: sqlite3_value_type(v) returns SQLITE_NULL and v represents omitted column from column-list of INSERT statement: INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES(value-list) | INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] select-statement Suggestion 1 is for virtual table implementations for external data store where record locks need to be set on read in order to do update or delete. Suggestion 2 allows virtual table implementation to properly choose between setting a column value to NULL or a default value known to the virtual table module . There is also a VT bug I just submitted (ticket 2759): select * from VT where X is NULL; causes assertion failure in bestVirtualIndex(). This is a show-stopper for VT implementations that allow NULL for unkown column values. Warm regards, Mark > -Original Message- > From: Ralf Junker [mailto:[EMAIL PROTECTED] > Sent: Friday, November 02, 2007 7:38 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How many virtual table implemenations > are there out there? > > > >But to do so seems likely to require an incompatible change > to the virtual-table interface. > > Could I kindly request an addition to the incompatible change > to the virtual-table interface? > > I would very much appreciate a corresponding function to > > function xRowID( > pCursor: psqlite3_vtab_cursor; > pRowID: PInt64): Integer; > > which would notify virtual table implementations that the the > SQLite engine no longer uses this particular RowID like, for example: > > function xRowID_Done( > pCursor: psqlite3_vtab_cursor; > pRowID: PInt64): Integer; > > The reason behind this is that some DB engines store RowIDs / > BookMarks in malloced memory structures. Obviously, they have > to be freed when no longer in use. Unfortunately, the current > VT interface does not give notice when this is the case. With > xRowID_Done, implementations will be able to free malloced > memory when no longer needed by SQLite and thus avoid > accumulating malloced RowIDs until the table is closed. > > Ralf > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Virtual Table module: Can cursor distinguish read-only from UPDATE/DELETE context?
I would like a virtual table cursor to distinguish read-only access (SELECT statement/clause) from searched UPDATE/DELETE access. For UPDATE / DELETE, I want to lock *my* rows in xNext cursor operation. Is there a way to distinguish or is this a dumb question? Many thanks Mark
[sqlite] INSERT into virtual table - missing value versus explicit NULL
Hi all, It's nit-picking time... I'm writing a virtual table module and ran into a little gotcha. The INSERT syntax allows one to specify NULL as a column value or to omit a column name. Compare: insert into T(a,c) values (1,2) take default value for column B if any versus insert into T values (1, NULL, 2) set B to NULL For normal tables SQLite is able to distinguish the two cases and behave as indicated by margin comment. It appears, however, that the call to virtual table module xUpdate() passes a NULL value object for column B in both of the above cases. It would have been nice if the column value array passed into xUpdate had pointer value 0 for any omitted column as in case 1 above, and a pointer to a NULL object for second case. Or have I missed a way to distinguish these cases? Thanks, Mark
RE: [sqlite] Virtual table xFilter argv object longevity
Thanks Dan > -Original Message- > From: Dan Kennedy [mailto:[EMAIL PROTECTED] > Sent: Sunday, October 28, 2007 11:11 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Virtual table xFilter argv object longevity > > On Sun, 2007-10-28 at 14:03 +, Evans, Mark (Tandem) wrote: > > The question is what is the lifetime of sqlite3_value > objects passed > > as argv array to the xFilter virtual table module call? Can I save > > the pointers and reference the values for constraint testing in my > > implementation of xNext? > > No. They are only good until the xFilter() call returns. The > popStack() near the bottom of the OP_VFilter opcode in vdbe.c > will invalidate them. > > Dan. > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Virtual table xFilter argv object longevity
The question is what is the lifetime of sqlite3_value objects passed as argv array to the xFilter virtual table module call? Can I save the pointers and reference the values for constraint testing in my implementation of xNext? Fingers crossed. Many thanks, Mark
[sqlite] Virtual Table questions
Hi All, I am exploring the use of the Virtual Table capability of SQLite 3.5.1 for implementing alternative backing store and have a couple of questions for virtual table gurus: Say I define a virtual table module registered as "custom" and then define virtual table T as follows: CREATE VIRTUAL TABLE T USING custom ( args ); This results in call to custom.xcreate() which calls sqlite3_declare_vtab() with a second argument of, say: "CREATE TABLE xxx ( a, b, c )". Now the sqlite3 shell meta command, .schema, outputs: CREATE VIRTUAL TABLE T USING custom ( args ); but not: CREATE TABLE xxx ( a, b, c ); How cosmic would it be to extend the .schema command to show both the CREATE VIRTUAL TABLE and corresponding CREATE TABLE statements? This is just a nicety, for debug checking. Secondly, and more important, I was hoping that it would be possible to have CHECK constraints on a virtual table, by saying, for example: "CREATE TABLE xxx ( a, b, c, CHECK( a < 10 ) )" as second argument to sqlite3_declare_vtab(). But it appears that the code generator routine, sqlite3GenerateConstraintChecks((), does not get called for virtual tables. I wonder how cosmic it would be to add a routine, sqlite3GenerateVirtualTableConstraintChecks(), that would generate constraint checking code for virtual tables. Is the current virtual table module interface sufficient to support such a capability? Regards, Mark
RE: [sqlite] 3.5.1 build issues
Thanks Joe > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Monday, October 08, 2007 1:45 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] 3.5.1 build issues > > "make test" already fixed in CVS. > > http://www.sqlite.org/cvstrac/tktview?tn=2689 > - To unsubscribe, send email to [EMAIL PROTECTED] -
FW: [sqlite] 3.5.1 build issues
Addendum: There is a new file, src/test_onefile.c, that needs to be added to Makefile.in. -Original Message- From: Evans, Mark (Tandem) Sent: Monday, October 08, 2007 12:23 PM To: sqlite-users@sqlite.org Subject: [sqlite] 3.5.1 build issues A glitch or two got introduced into the 3.5.1 build for cygwin and possibly other platforms. Please advise if I did something wrong: There are undefined symbols when building testfixture: /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2511: undefined reference to `_Sqlitetest7_Init' /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2520: undefined reference to `_SqlitetestThread_Init' /cygdrive/c/mse/ccm_wa/hsspla/SDM/SQLite-REL01/SQLite/build/cygwin/build /bld/../../../../Source/sqlite-3.5.1-hp/src/tclsqlite.c:2521: undefined reference to `_SqlitetestOnefile_Init' The first function, Sqlitetest7_Init, turns up undefined due to change in #if-s in test7.c: from: #if OS_UNIX && SQLITE_THREADSAFE && \ defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE) to: #if defined(SQLITE_SERVER) && !defined(SQLITE_OMIT_SHARED_CACHE) #if defined(OS_UNIX) && OS_UNIX && SQLITE_THREADSAFE The latter two functions were moved to test_thread.c which does not appear in Makefile.in but they are referenced by tclsqlite.c. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] why not get max-constants from limits.h ?
I am building sqlite 3.5.0 using x-dev tools for HP NonStop OSS target machine and am working through some issues. One of the issues is a compiler error that results from a literal, SQLITE_BIG_DBL, used in src/where.c. That literal is defined in sqliteInt.h as (1e99) which is bigger than the maximum double for this machine, defined in /usr/include/limits.h as #define DBL_MAX 1.15792089237316192e77. I can obviously work around this problem by adding a -DSQLITE_BIG_DBL to the c-flags, but I'm curious why sqlite does not #include and use DBL_MAX as the default value for SQLITE_BIG_DBL. Regards, Mark
RE: [sqlite] test failures on cygwin
It's hard to drag my Linux server to Starbucks. :-) Next Windows laptop, though, will definitely have to have a Linux/Windows dual personality. I'll have to wait until the next mega-merger for a Mac. Mark > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, September 21, 2007 6:05 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] test failures on cygwin > > > Of course, the easiest option by far is to use a Linux box or > a Mac. :-) > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] test failures on cygwin
When I run just autoinc.test alone, I get the following summary
output:
54 errors out of 66 tests
Failures on these tests: autoinc-6.1 autoinc-1.1 autoinc-1.2 autoinc-1.3
autoinc-1.4 autoinc-1.6 autoinc-2.1 autoinc-2.2 autoinc-2.3 autoinc-2.4
autoinc-2.5 autoinc-2.6 autoinc-2.7 autoinc-2.8 autoinc-2.9 autoinc-2.10
autoinc-2.11 autoinc-2.12 autoinc-2.13 autoinc-2.21 autoinc-2.23
autoinc-2.25 autoinc-2.27 autoinc-2.29 autoinc-2.51 autoinc-2.52
autoinc-2.53 autoinc-2.54 autoinc-2.55 autoinc-2.70 autoinc-2.71
autoinc-2.72 autoinc-2.73 autoinc-2.74 autoinc-3.1 autoinc-3.2
autoinc-3.3 autoinc-3.4 autoinc-4.1 autoinc-4.2 autoinc-4.3 autoinc-4.4
autoinc-4.4.1 autoinc-4.5 autoinc-4.6 autoinc-4.7 autoinc-4.8
autoinc-4.9 autoinc-4.10 autoinc-5.1 autoinc-5.2 autoinc-5.3 autoinc-5.4
autoinc-7.1
**
N.B.: The version of TCL that you used to build this test harness
is defective in that it does not support 64-bit integers. Some or
all of the test failures above might be a result from this defect
in your TCL build.
**
All memory allocations freed - no leaks
Maximum memory usage: 76060 bytes
So it looks like I have a cygwin TCL issue. Is this fixable?
Mark
> -Original Message-
> From: Evans, Mark (Tandem)
> Sent: Friday, September 21, 2007 5:47 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] test failures on cygwin
>
> Hi all,
>
> I have been lurking on the message board and am in awe of the
> collective wisdom.
>
> I'm just getting my feet wet learning the internals of
> SQLite, drinking from the proverbial firehose. I am using
> cygwin 1.90 as my learning
> platform and I have built SQLite 3.5.0. I ran 'make test'
> (quick.test
> suite) and it reports some test failures as follows:
>
> 41 errors out of 28604 tests
> Failures on these tests: autoinc-6.1 bind-3.1 cast-3.1
> cast-3.2 cast-3.4
> cast-3.5 cast-3.6 cast-3.8 cast-3.11 cast-3.12 cast-3.14 cast-3.15
> cast-3.16 cast-3.18 cast-3.21 cast-3.22 cast-3.24 expr-1.102
> expr-1.106
> func-18.14 func-18.16 func-18.17 func-18.31 lastinsert-8.1
> lastinsert-9.1 misc1-9.1 misc2-4.1 misc2-4.2 misc2-4.4 misc2-4.5
> misc2-4.6 misc3-3.6 misc3-3.7 misc3-3.8 misc3-3.9 misc3-3.10
> misc3-3.11
> misc5-2.2 shared-1.11.9 shared-2.11.9 types-2.1.8
>
> Should I be surprised by these failures?
>
> Taking the first one that failed, autoinc-6.1, it reported:
>
> autoinc-6.1...
> Expected: [9223372036854775807]
> Got: [-1]
>
> The corresonding test code is:
>
> ifcapable {!rowid32} {
> do_test autoinc-6.1 {
> execsql {
> CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w);
> INSERT INTO t6 VALUES(9223372036854775807,1);
> SELECT seq FROM main.sqlite_sequence WHERE name='t6';
> }
> } 9223372036854775807
> }
>
> So the SELECT clause returns -1 instead of the expected
> 0x7FFF (MAXLONGLONG), or in other words MAXLONGLONG + 1.
> Should the value returned by SELECT seq ... be the the key
> value (rowid) of the last insert, or, is seq supposed to
> represent the next rowid (autoincrement of last key)?
>
> Regards,
> Mark
>
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] test failures on cygwin
Hi all,
I have been lurking on the message board and am in awe of the collective
wisdom.
I'm just getting my feet wet learning the internals of SQLite, drinking
from the proverbial firehose. I am using cygwin 1.90 as my learning
platform and I have built SQLite 3.5.0. I ran 'make test' (quick.test
suite) and it reports some test failures as follows:
41 errors out of 28604 tests
Failures on these tests: autoinc-6.1 bind-3.1 cast-3.1 cast-3.2 cast-3.4
cast-3.5 cast-3.6 cast-3.8 cast-3.11 cast-3.12 cast-3.14 cast-3.15
cast-3.16 cast-3.18 cast-3.21 cast-3.22 cast-3.24 expr-1.102 expr-1.106
func-18.14 func-18.16 func-18.17 func-18.31 lastinsert-8.1
lastinsert-9.1 misc1-9.1 misc2-4.1 misc2-4.2 misc2-4.4 misc2-4.5
misc2-4.6 misc3-3.6 misc3-3.7 misc3-3.8 misc3-3.9 misc3-3.10 misc3-3.11
misc5-2.2 shared-1.11.9 shared-2.11.9 types-2.1.8
Should I be surprised by these failures?
Taking the first one that failed, autoinc-6.1, it reported:
autoinc-6.1...
Expected: [9223372036854775807]
Got: [-1]
The corresonding test code is:
ifcapable {!rowid32} {
do_test autoinc-6.1 {
execsql {
CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w);
INSERT INTO t6 VALUES(9223372036854775807,1);
SELECT seq FROM main.sqlite_sequence WHERE name='t6';
}
} 9223372036854775807
}
So the SELECT clause returns -1 instead of the expected
0x7FFF (MAXLONGLONG), or in other words MAXLONGLONG + 1.
Should the value returned by SELECT seq ... be the the key value (rowid)
of the last insert, or, is seq supposed to represent the next rowid
(autoincrement of last key)?
Regards,
Mark
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
