For the SQLite ADO.NET wrapper, I actually wrote two functions into the core
engine to help me retrieve key information. My requirements were a little
more complex than just getting rowid, but here's what I did to get me that
far at least:
Given a sqlite_stmt pointer consisting of a SELECT on one or more tables,
1. For each table, fetch the internal cursor for that table in the
statement
2. For each cursor, fetch the rowid
That was the API modification part. Caveats are that this information is
not always available. So if its not, I just return a failure indicator.
The next part involved querying the schema of each table, looking up indexes
and finding the most logical primary key definitions for the table(s)
involved in the select, and compiling a side-by-side SELECT clause returning
the user's defined primary keys using a WHERE clause with the above fetched
rowid's.
So given these table definitions:
CREATE TABLE Foo (MyID INTEGER, BarID INTEGER, MyValue, CONSTRAINT pk
PRIMARY KEY(MyId, MyKey));
CREATE TABLE Bar (BarID INTEGER PRIMARY KEY, MyBarValue);
And this SELECT clause:
SELECT MyValue, MyBarValue FROM Foo INNER JOIN Bar ON Foo.BarID = Bar.BarID;
I could then construct a hidden side-by-side query, looking like:
SELECT MyID, BarID FROM Foo WHERE ROWID = ?
(I already had the rowid from the Bar table without a sub-query using the
API calls I made earlier)
All this is wrapped up inside the SQLiteDataReader when you specify a
KeyInfo flag to open the reader on the command.
As for the API extensions ...
The only reason I never posted it and wasn't sure of its suitability is that
I've only ever tested it the way I use it and dunno how stable it is for
general purpose usage. Also, the initial function call to
sqlite_table_cursor requires the database index and root page number which
can only be obtained by previous fetches into the database schema.
Here's the code at any rate:
/*
[in] pstmt -- The statement to fetch a cursor on
[in] iDb -- The database index (id from a previous call to
PRAMGA database_list)
[in] tableRootPage -- Root page of the table, (fetched from
sqlite_master table)
For a given row-returning statement which may contain multiple cursors,
return the index of
the cursor responsible for iterating the specified table. You must
specify the root page of that
table and the database it's in to get a match.
RETURNS
-1 on failure, or a 0-based index of the cursor for the given table
*/
int sqlite3_table_cursor(sqlite3_stmt *pstmt, int iDb, Pgno tableRootPage)
{
Vdbe *p = (Vdbe *)pstmt;
int n;
for (n = 0; n < p->nCursor && p->apCsr[n] != NULL; n++)
{
if (p->apCsr[n]->isTable == FALSE) continue;
if (p->apCsr[n]->iDb != iDb) continue;
if (p->apCsr[n]->pCursor->pgnoRoot == tableRootPage)
return n;
}
return -1;
}
/*
[in] pstmt -- A row-returning SQLite statement
[in] cursor -- A cursor previously returned from sqlite3_table_cursor()
[out] prowid -- The rowid for the specified cursor
Fetches (if possible) a rowid for a given cursor in a row-returning
statement.
RETURNS
0 on success, or one of the SQLite error codes otherwise
*/
__declspec(dllexport) int WINAPI sqlite3_cursor_rowid(sqlite3_stmt *pstmt,
int cursor, sqlite_int64 *prowid)
{
Vdbe *p = (Vdbe *)pstmt;
int rc = 0;
Cursor *pC;
if (cursor < 0 || cursor >= p->nCursor) return SQLITE_ERROR;
if (p->apCsr[cursor] == NULL) return SQLITE_ERROR;
pC = p->apCsr[cursor];
rc = sqlite3VdbeCursorMoveto(pC);
if( rc ) return rc;
if( pC->rowidIsValid )
{
*prowid = pC->lastRowid;
}
else if(pC->pseudoTable )
{
*prowid = keyToInt(pC->iKey);
}
else if(pC->nullRow || pC->pCursor==0)
{
return SQLITE_ERROR;
}
else
{
if (pC->pCursor == NULL) return SQLITE_ERROR;
sqlite3BtreeKeySize(pC->pCursor, prowid);
*prowid = keyToInt(*prowid);
}
return 0;
}
> -----Original Message-----
> From: Ralf Junker [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 07, 2007 4:23 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Is there a SQLiteSpy-like thing that will let me
> change data from a grid?
>
>
> >> The SQLite library lacks certain API required to implement cell
> >> editing: Most notably, there is no functionality to retrieve
> >> the exact origin (table, row and column) of a cell, which must
> >> be known to store back the modified value.
> >
> >See:
> >
> > http://www.sqlite.org/capi3ref.html#sqlite3_column_database_name
> > http://www.sqlite.org/capi3ref.html#sqlite3_column_origin_name
> > http://www.sqlite.org/capi3ref.html#sqlite3_column_table_name
>
> I know, and these functions were much appreciated when they entered
> SQLite. However, they do not cover the table row (RowID). Here is the
> discussion about what's still missing. The thread also points out a few
> inconsistencies in the implementation of the above functions, IIRC:
>
> http://thread.gmane.org/gmane.comp.db.sqlite.general/19323
>
> Ralf
>
>
> -----------------------------------------------------------------------
> ------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------
> ------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
