>> The pragma foreign_key_list appears to be deprecated in 3.7.4: Oh no. That is bad news. Please restore this feature.
> Now that foreign key constraints are enforced natively, why would you want to > have a list of them? Why should the foreign_key_list pragma continue to > consume code space and developer maintenance time? Because we need to access the schema in order to properly construct our applications. For instance, a list of foreign keys allows us to present to the user of our applications a list of related rows, as shown here: http://www.databare.com/related.html and a popup list of available entries in a column, as shown here: http://www.databare.com/data_choices.html It's similar to our requirement to extract a list of tables and views for a database or the list of columns in a table or view. Sure, SQLite internally knows about them, but we need to be able to extract that list to present to the user, either directly or indirectly. So we use select * from SQLite_Master and pragma table_info(). As for the bigger picture of "why would you want to have a list of ..." schema information in general: I'd like to see it taken further, to make most of the schema parsing information available either via SQL or at worst via C. At present, there are a few pragmas that give us limited info. For instance, pragma table_info gives us the name and declared types of columns in a table or view, but it doesn't tell us the expression used to create that column in a view, and other column specific attributes. Instead of pragmas, it would be most useful to have schema info accessible via queries on built in tables or views (similar to SQLite_Master). That way we can join various tables in a pure SQL query, without having to reinject data or parse it outside of SQL. For example, to get the steps of the trigger responsible for updates to the column "Name First" in the view "Customers Entry", we could: select Step_SQL from SQLite_Trigger_Steps left join SQLite_Trigger_Update_Columns on SQLite_Trigger_Update_Columns.Trigger_Name = SQLite_Trigger_Steps.Trigger_Name left join SQLite_Master on Name = SQLite_Trigger_Update_Columns.Trigger_Name where SQLite_Master.Tbl_Name = 'Customers Entry' and SQLite_Master.Type = 'trigger' and SQLite_Trigger_Update_Columns.Column_Name = 'Name First' This example is very useful to make data editable in views in my applications, since it tells me the dependancies for any changes. For example, see the screenshots here: http://www.databare.com/column_trigger.html http://www.databare.com/update_view.html At present, I have to do this by parsing the whole schema in my own code, which seems unnecessarily repetitive (since SQLite already does it internally but I can't get to it), inefficient (since I'm separated from the internal SQLite workings) and inaccurate (because I'm using the documentation to make an educated guess as to how SQLite is dealing with the schema). Thanks for your consideration and a great product, 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
