On 11/12/2010, at 12:29 PM, Simon Slavin wrote:
> The problem with foreign keys (and triggers !) as separate rows of
> SQLITE_MASTER is that it would all have to be one long string, so you'd have
> to write a parser.
I'm not sure what you mean here. Triggers are already listed in SQLite_Master.
Their SQL definition is listed as "one long string". The only element that is
parsed out for us is the name of the table (or view) upon which the trigger
operates, in the Tbl_Name column. I want that "one long string" to be parsed
into smaller chunks, the way that some pragmas do now, but made more usable via
select queries instead.
> I think a better idea would be to expand table_info with a second parameter
> so it could list all tables, and report on columns, indexes, triggers and
> foreign keys all in one PRAGMA. Then all the other PRAGMAs that do this
> could be removed.
No, that's horrible. Columns, indexes, triggers and foreign keys all have
different elements so need different tables to show them. We'd want a properly
normalized schema.
And, as per my post before this, I would like to see them accessed via select
query, rather than pragmas, so we can properly filter the result.
In short, what we need is to be able to access the schema components as tables
and perform standard sort, filtering etc on them. You know, kinda like how a
database works. Hang on, SQLite is a database, so why not use its own built in
features to do the job? (Meant for humour and to highlight the obvious, not
condescension ;-) )
We already have:
create table SQLite_Master
( Type text
, Name text
, Tbl_Name text
, Rootpage int
, SQL text
)
In a similar fashion, I'm proposing/requesting that we morph pragma
foreign_key_list into:
create table SQLite_Foreign_Keys
( ID integer primary key
, Sequence integer
, Name text
, From_Table text
, From_Column text
, To_Table text
, To_Column text
, On_Update text
, On_Delete text
, Match text
, Deferrable boolean
, Initially text
)
Similarly, I propose that pragma table_info() would be better as selectable
tables:
create table SQLite_Table_Columns
( ID integer primary key
, Table_Name text
, Sequence integer
, Name text
, Type text
, Constraints text
)
and:
create table SQLite_View_Columns
( ID integer primary key
, View_Name text
, Sequence integer
, Name text
, Type text
, Expression text
, Origin_Column_ID
references SQLite_Table_Columns(ID)
)
Triggers are a different animal again, so belong in their own table:
create table SQLite_Triggers
( ID integer primary key
, Name text
, Table_Name text
, Event text -- delete, insert, update, update of
, Occur text -- before, after, instead of
)
create table SQLite_Trigger_Update_Columns
( ID integer primary key
, Column_Name
, Trigger_ID integer
references SQLite_Triggers(ID)
)
create table SQLite_Trigger_Steps
( ID integer primary key
, Trigger_ID integer
references SQLite_Triggers(ID)
, Sequence integer
, SQL text
)
Then we could do all manner of introspection in a single select statement, such
as (rewording some examples I gave before):
1. In an "Invoices Entry" view, in a "Customer" column, show the list of
allowed values (from the origin and foreign key column). To the user it may
look something like this:
http://www.databare.com/data_choices.html
2. For a particular column in a view, get the expression, name (alias) and
result type of the column, along with the trigger that is activated by an
update to that column in the view. See the first four snapshots here for a
visual example:
http://www.databare.com/trace.html
3. Navigate through the hierarchy of any object, such as
View->Column->Trigger->Steps->Insert->Select. For example:
http://www.databare.com/column_trigger.html
I currently do this via a pile of parsing code, but I feel like I'm reinventing
the wheel, since SQLite obviously already has this information internally, but
won't share. And I'm concerned that if SQLite's internals change, my external
mimicking will fail.
I hope this clarifies what I'm talking about.
Thanks,
Tom
BareFeetWare
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
