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?
--- "Evans, Mark (Tandem)" <[EMAIL PROTECTED]> wrote: > 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). I thought all cursors are read-only in effect, and the only modification is done via xUpdate once the rowid is known. Would your scheme work with UPDATE statements with correlated sub-queries on the same table? __ 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] -
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] -
Re: [sqlite] How many virtual table implemenations are there out there?
Hi Darren, On 11/3/07, Darren Duncan <[EMAIL PROTECTED]> wrote: > At 9:42 AM -0400 11/2/07, Samuel R. Neff wrote: > I think the real problem here is "virtual" is a broad enough term ... I immediately think about MySQL when you use terms like "federated" and "data engine". With the word "federated", anyone with a MySQL background can immediately "see" what the virtual table is all about - if that's what it is, that is. -- Best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How many virtual table implemenations are there out there?
At 9:42 AM -0400 11/2/07, Samuel R. Neff wrote: I like the term virtual 'cause that's exactly what they are.. a table that does not really exist in the db and is provided by some other system. This is not inconsistent with other DBMS's which use terms like "virtualized view", both are tables that are not linked to underlying physical data. The fact that the mechanism which provides these tables is different does not mean they are not both validly virtual tables. I think the real problem here is "virtual" is a broad enough term that it can validly apply to a broad spectrum of situations, which can differ a lot. At the very least, "virtual" is very applicable both to a SQL view and to a module whose interface looks like a SQL table, and so to apply the term to just the latter would cause confusion. Therefore, my primary recommendation is to use any reasonable term that is *not* spelled "virtual", not because "virtual" doesn't describe what we're talking about, but because "virtual" is too broad and causes confusion. At 7:37 AM -0700 11/2/07, Trevor Talbot wrote: It's simply an interface that looks like a table. Having thought about the matter more, perhaps the table-like-module-API feature could be called "interface table" then. In some respects, "interface" is very broad too, but then so is "table", however, I see it more important to not use the term "virtual" here. At 8:08 AM -0600 11/2/07, John Stanton wrote: To my mind "virtual" means something different. "Foreign" would be a more intuitive name. That would also work for me. In fact, "foreign" is arguably better than "federated", since "federated" also means to take multiple databases and make them look like one. And that is something that SQLite arguably does natively, where each file is a database, and a collection mounted at once being a federation thereof. At 7:37 AM -0700 11/2/07, Trevor Talbot wrote: Terms like "foreign" and "federated" conjure up images of physical tables that reside in external databases, which is not at all what this feature is. Maybe or maybe not. The relational model of data is intended to abstract the physical storage mechanism away from the user, such that users just see tables/relvars which they can run queries on and update the values of, and how this is implemented behind the scenes in terms of physical data formats or locations of data is something they shouldn't need to be concerned with. So in that respect, all of normal tables (real|base relvars), which conceptually store the data themselves, and normal "views" (virtual relvars), which conceptually have their data stored in other, normal tables, these are all just tables/relvars to the user. I now bring up a broader matter for consideration ... If SQLite's table-like-module feature is meant to be like a normal table in all ways but for its implementation, I would suggest considering looking at this whole issue in a different way, that SQLite supports multiple "engines". When you define a database in SQLite, you also indicate what "engine" you want to implement it with. SQLite has a built-in default engine, what normally gets used, or perhaps the file vs memory could be considered 2 engines. The third-party modules such as we were discussing would each then provide additional engines to choose from beyond SQLite's standard built-ins. For backwards compatibility or simplicity, if schema designers don't explicitly indicate an engine to use, they get SQLite's built-in by default. Now while I'm not going to argue any merits of MySQL as a DBMS, it can still serve as an example of the concept, with its multiple engine types, eg InnoDB vs Falcon or what have you. Other DBMS may have similar concepts. That said, and particularly since SQLite is already supports multiple databases under a common transaction, I strongly recommend that the pick of engine is applied at the level of a whole database, not at the individual table level. Note that I consider a database to be the widest range of multi-table database constraints, such as foreign keys, because all of its parts are always defined and live together. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How many virtual table implemenations are there out there?
Terms like "foreign" and "federated" conjure up images of physical tables that reside in external databases, which is not at all what this feature is. It's simply an interface that looks like a table. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How many virtual table implemenations are there out there?
I like the term virtual 'cause that's exactly what they are.. a table that does not really exist in the db and is provided by some other system. This is not inconsistent with other DBMS's which use terms like "virtualized view", both are tables that are not linked to underlying physical data. The fact that the mechanism which provides these tables is different does not mean they are not both validly virtual tables. Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Thursday, November 01, 2007 5:33 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How many virtual table implemenations are there out there? I agree. How about "Synthetic Table" or "Abstract Table"? --- Darren Duncan <[EMAIL PROTECTED]> wrote: > While you're at it, I strongly recommend changing the feature name > from "virtual table" to "federated table", or at least not something > called "virtual", because the older/current name is a source of > confusion. - To unsubscribe, send email to [EMAIL PROTECTED] -
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] -
Re: [sqlite] How many virtual table implemenations are there out there?
I agree. How about "Synthetic Table" or "Abstract Table"? --- Darren Duncan <[EMAIL PROTECTED]> wrote: > While you're at it, I strongly recommend changing the feature name > from "virtual table" to "federated table", or at least not something > called "virtual", because the older/current name is a source of > confusion. __ 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] -
Re: [sqlite] How many virtual table implemenations are there out there?
At 9:03 AM -0400 11/1/07, D. Richard Hipp wrote: The current virtual-table implementation does not work when you have shared cache mode enabled. We would like to fix this so that that you can (for example) use FTS and shared cache at the same time. But to do so seems likely to require an incompatible change to the virtual-table interface. The virtual-table interface is currently listed as "experimental" and does not appear in the "official" documentation in capi3ref.html. So we are free to change it if we need to. But I wonder how many people this would inconvenience. If you have or know of a virtual table implementation (other than the ones that are included with SQLite - such as FTS1-3) that will be broken by an API change, please let me know. And please also advice me how much of a hardship a change would be for you. While you're at it, I strongly recommend changing the feature name from "virtual table" to "federated table", or at least not something called "virtual", because the older/current name is a source of confusion. In general relational database contexts, the term "virtual table" usually means the same thing as "view" means, which is something that looks like a "real/base table" but is actually a mapping to one or more real/base tables as typically defined by a relational query, and this is done internally by the main DBMS that does the real/base tables. (In fact, a proper viewed/virtual table implementation would be such that users of the database don't have to know which tables are base or viewed, and if a DBA wanted to change the schema later to make viewed tables real and real tables viewed, it would not affect the users. Views that don't reflect enough mapping data notwithstanding.) Your term "virtual table" would be better called "federated table" (or "foreign table" or "remote table"), which is the more standard term with relational databases for tables that are interfaces to other DBMS systems with remote storage. If you're making an incompatible change anyway, then this rename would be good for hand-in-hand with it, to help people migrating apps from the old to the new; best to do all the incompatible changes at once. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How many virtual table implemenations are there out there?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 D. Richard Hipp wrote: > The current virtual-table implementation does not work when you have > shared cache mode enabled. Can you also fix the xFindFunction api? The problem with it is that I have no idea when SQLite is finished with the function pointer. That isn't an issue if the functions are static, but when they are dynamically allocated (such as in a Python wrapper) I have to ensure the pointer remains valid for as long as SQLite needs it, and then free what it is pointing to under the hood when SQLite is done so that there are no memory leaks. I'm somewhat curious as to how you implemented xFindFunction for TCL without keeping the function around forever (memory leak) or potentially destroying it early (process crash). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHKjoqmOOfHg372QQRAukfAKC4tnfgd2TYpsqO1lqcSsvyKzw44wCcDuBd 4/P6Qvm6fDJvG6tTbEPtXWE= =DjFl -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How many virtual table implemenations are there out there?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 D. Richard Hipp wrote: > If you have or know of a virtual table implementation (other than the ones > that are included with SQLite - such as FTS1-3) that will be broken by an > API change, please let me know. And please also advice me how much of > a hardship a change would be for you. APSW lets you write virtual tables in Python. The BitPim project is using the functionality, but I have no idea how many others are. Since you marked the API as experimental, I have no problem with even severe changes. (The existing version of SQLite works just fine so noone is forced to upgrade). However please ensure that old code will fail to compile (ie error) rather than have compiler warnings so that it isn't possible for old api calls to be erroneously used with the new api. It would be nice to get some bugs fixed such as 2127. 2128 may even be worth a look - maybe virtual tables could use any SQLite data type as the rowid and not just integers? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHKiVXmOOfHg372QQRAkyWAKDOPFHsNVVc2QGS6a8RIKqmP6mbZwCfdKW8 aIlTPjkIHm8JCQGdKYVqzQk= =NNR/ -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How many virtual table implemenations are there out there?
To make virtual tables compatible with shared cache would be a convenience for us, and not in the least inconvenient. D. Richard Hipp wrote: The current virtual-table implementation does not work when you have shared cache mode enabled. We would like to fix this so that that you can (for example) use FTS and shared cache at the same time. But to do so seems likely to require an incompatible change to the virtual-table interface. The virtual-table interface is currently listed as "experimental" and does not appear in the "official" documentation in capi3ref.html. So we are free to change it if we need to. But I wonder how many people this would inconvenience. If you have or know of a virtual table implementation (other than the ones that are included with SQLite - such as FTS1-3) that will be broken by an API change, please let me know. And please also advice me how much of a hardship a change would be for you. D. Richard Hipp [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?
Hello, > The current virtual-table implementation does not work when you have > shared cache mode enabled. We would like to fix this so that that > you can (for example) use FTS and shared cache at the same time. > But to do so seems likely to require an incompatible change to the > virtual-table interface. An incompatible change would be no hardship for the potential benefits of a shared cache. Well, that is true for my virtual table implementation. Thanks, Andy. > > The virtual-table interface is currently listed as "experimental" > and does not appear in the "official" documentation in > capi3ref.html. So we are free to change it if we need to. But I > wonder how many people this would inconvenience. > > If you have or know of a virtual table implementation (other than > the ones that are included with SQLite - such as FTS1-3) that will > be broken by an API change, please let me know. And please also > advice me how much of a hardship a change would be for you. > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How many virtual table implemenations are there out there?
The current virtual-table implementation does not work when you have shared cache mode enabled. We would like to fix this so that that you can (for example) use FTS and shared cache at the same time. But to do so seems likely to require an incompatible change to the virtual-table interface. The virtual-table interface is currently listed as "experimental" and does not appear in the "official" documentation in capi3ref.html. So we are free to change it if we need to. But I wonder how many people this would inconvenience. If you have or know of a virtual table implementation (other than the ones that are included with SQLite - such as FTS1-3) that will be broken by an API change, please let me know. And please also advice me how much of a hardship a change would be for you. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
