----- Original Message -----
From: "Will Leshner" <[EMAIL PROTECTED]>
On Dec 20, 2005, at 7:46 AM, John Stanton wrote:
I haven't looked closely at the problem, so these are just first ideas
extending CM's approach. Basically there should be no reason to perform
any analysis of the SQL since that has already been done and the
metalanguage generated. My approach would be to prepare the SQL
statement and then use the compiled SQL to generate an UPDATE statement.
The existing SQLite VM gives you a model for the framework of your
engine.
This sounds like a very interesting idea. Could you be more specific
about how you would generate an UPDATE statement from the compiled SQL?
My approach in the ADO.NET 2.0 provider was to modify the core engine
slightly. I added a database pragma, which when enabled, directs the
internal generateColumnNames() function in select.c to emit the underlying
database, table name and column name for a given column. So for example:
Given the definition:
CREATE TABLE Foo (ID INTEGER PRIMARY KEY, Description VARCHAR(50), Value
INT)
... and the statement:
SELECT ID AS MyID, Description AS MyDescription, Value AS MyValue FROM Foo
The output column names would normally be MyId, MyDescription, MyValue.
However, with the pragma turned on, it generates them as main.Foo.ID,
main.Foo.Description, main.Foo.Value
The code doesn't rename aggregate or literal columns, only columns that back
directly into a column in a table or view. When selecting from a view, the
view's column definitions are displayed instead.
Once I have the full database, table and column name for a given column I
can then query the schema and determine datatypes, indexes, etc. In the
case of a column from a view, I can also recursively parse (SQLite does the
parsing actually) the original view definition statement and drill down to
the base table(s) involved in the view(s).
What I'm left with are the alias names and original database table and
column names for all the fields in the query, complete with information on
whether or not any field is a primary key, how many different tables are
involved in the query, etc etc. If there is only one table involved, I can
generate the statements. If a unique index/primary key is included, I can
generate delete/update statements as well.
I originally tried to go with parsing the EXPLAIN output, but there's just
not enough information there to fully reconstruct the query's underlying
sources, and without rebuilding my own virtual machine to follow the output,
its just too difficult to figure out.
Robert
