Hi. I apologize in advance for the length of this question, but it is
a little involved.
I am the author of a wrapper for SQLite and in that wrapper there is
an object called a RecordSet that represents the results of a query.
One of the things you can do with a RecordSet is edit records. The
way I've implemented editing a RecordSet is to construct an UPDATE
statement based on the new values for the fields of the record. The
problem, though, is that while query results are always returned in
the form of a RecordSet, not all RecordSets represent query results
that are really editable in any meaningful way. The big problem is
knowing which record is really being represented by a particular row
in a RecordSet object. What I do is add a rowid column to every
query. For many queries, this results in a syntax error. For example,
if the query involves joins and such. That's exactly what I want to
have happen. If I get a syntax error, then I know the results of the
query aren't really something I want to let the user edit, so I
requery without the rowid and just remember that the resulting
RecordSet isn't editable. The problem, however, is that some queries
allow a rowid column, but still shouldn't be editable. For example:
SELECT rowid,count(rowid) FROM table
One might argue that such a query should return an error, but even if
it did, I couldn't rely on it, because this query is perfectly fine:
SELECT rowid,count(rowid) FROM table GROUP BY rowid
So, the question I have is this: Is there some way I can determine,
short of parsing the SQL for myself, that the results of a query are
"suitable for editing"? I think, by "suitable for editing", that I
mean the results all come from one table and only include natural
columns of that table. I'm perfectly happy to detect "suitability"
during the execution of the VM. I have my own loop where I call
sqlite3_step and such, if that makes any difference. For example, if
I had a way to know, during the execution of the VM, that the results
were coming from more than one table at the same time, that would
certainly be a big step in the right direction.
A related topic is the ability to get the rowid of the rows returned
by a query without having to add the 'rowid' column explicitly. Right
now I manually add 'rowid' in the wrapper, but I really hate doing
that because I'd like to touch the user's SQL as little as possible.
I think it would be really cool if there were a way to tell SQLite to
return, along with the regular results, the rowid for each row. If
'rowid' makes no sense for the query, then maybe SQLite could return
some kind of ILLEGAL_ROWID value. Would anybody else find such a
thing useful?
Thanks.
