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: <<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?>> 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] -----------------------------------------------------------------------------
