I would like to see some more access to the database structure via SQL calls.
For instance, we can currently get the list of tables, views, indexes and/or triggers via queries to SQLite_Master. For instance, to get the name and SQL of all triggers that are initiated by MyTable, we can query: select Name, SQL from SQLite_Master were Tbl_Name = 'MyTable' and Type = 'trigger'; And we can get the name and declared type of all columns of a table or view via: pragma table_info(MyTable); But that's about all we can do. I'd like to see similar SQL syntax available to get properties such as: 1. The constraints of a table. 2. The default value, and constraints of a table. Perhaps as an extension of the current pragma table_info() function, but preferably as a select query on a dynamic table (perhaps SQLite_Tables) so that we can feed the output into a larger query within SQL. 3. The members of a constraint, one row for each. 4. Improve the declared_type result of a column to show a declared type for an expression in a view. The declared_type C call and the pragma table_info() SQL call currently return the type of a column in a view if the column is simply referring to a column in a table. But if the column in the view is an expression, it returns null. I would like to see it return the declared type of the outer function of the expression, since most functions have a known result type. For instance, if a column in a view is defined as cast(mycomplexfunction() as integer), then the declared type is known to be an integer. If the function is abs(), the type is real. If the function is length(), the type is integer. Obviously a few functions such as coalesce() have varied types so they would still have to show a declared_type of null, but the cast() function gives the SQL author the chance to explicitly declare the type of such a column. 5. The components of a create view select statement, that would provide access to the parsed parameters: distinct/all, column expressions and names, from, where, group by, having, order by, etc. 6. Parsing of triggers into before/after/instead of, delete/insert/ update/update of, when, steps etc. I have written routines to do all this in my own code, but it would be far more accessible, especially for new SQLite programmers, to have access to this via SQL. It also seems to me that the SQLite code must already be parsing out these parameters internally so it would be relatively simple to make them available, and far more consistent than us re-inventing the wheel. Thanks, Tom BareFeet _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
