If you parse such a simple query like
SELECT [*,] bla [, bla] FROM table-name WHERE blabla ,
you'll get into less troubles than if you play with the rowid trick. All
you need is to check if returned colnames can be found in table-name. And
it is a simple question using PRAGMA table_info(table-name);
Fanda
On Mon, 19 Dec 2005 22:11:32 +0100, Will Leshner <[EMAIL PROTECTED]>
wrote:
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.
