Query Detail Table
(1) By anonymous on 2021-07-06 20:04:41 [link]
Sqlite is great. I am curious if there is a feature which enables you to define and get high level (facet?) details about your query. For instance I might always pass in any query and get the max length, min length, and number of distinct values for each column. Is there a feature that does this? Much like the sqlite-statement-vtab extension but ability to apply functions across each column in a query. I know I can write a query for each query, just trying to get rid of boilerplate. Cheers
(2) By ThanksRyan on 2021-07-06 20:18:17 in reply to 1 [link]
Maybe what you want are [views](https://sqlite.org/lang_createview.html).
(3) By anonymous on 2021-07-06 20:57:06 in reply to 2 [link]
Thanks for the reply, was hoping there was a feature which was not static and more dynamic.
(4) By Larry Brasfield (larrybr) on 2021-07-06 21:17:11 in reply to 3 [link]
You should look at <u>[the eval() extension](https://www.sqlite.org/src/file?name=ext/misc/eval.c&ci=trunk)</u>. With that and a view using the sqlite_schema table, a semi-skilled SQL user could devise extraction of the summary info you seek.
(5) By anonymous on 2021-07-07 01:55:03 in reply to 4 [link]
I know what you mean, that gives details at the table level, might have to settle for the time being, Was thinking it would be more versatile at the query level. Of course their are data types to consider as well which would gbe even more helpful.
(6) By Larry Brasfield (larrybr) on 2021-07-07 02:54:06 in reply to 5 [link]
Below, I have included some SQL, contributed or linked by Keith Medcalf in this forum some time ago. It implements something like the catalog mentioned in Codd's 4th rule and available in heavy-weight DBMS's. Once the views are in place, you should be able to generate queries that conform to the shape of your tables, then run them using eval(). P.S. To Keith: I could not find the post where you made this known. And I'm not sure it is your most current version; it's just what I kept as such. I've added blank comments to improve rendering from markdown. <code> \-- Schema Info Views \-- \-- This is a set of views providing Schema information \-- for SQLite DBs in table format. \-- DROP VIEW IF EXISTS SysIndexColumns; DROP VIEW IF EXISTS SysIndexes; DROP VIEW IF EXISTS SysColumns; DROP VIEW IF EXISTS SysObjects; \-- CREATE VIEW SysObjects AS SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE FROM (SELECT type AS ObjectType, name AS ObjectName FROM sqlite_master WHERE type IN ('table', 'view', 'index') ) ; \-- CREATE VIEW SysColumns AS SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, ColumnID COLLATE NOCASE, ColumnName COLLATE NOCASE, Type COLLATE NOCASE, Affinity COLLATE NOCASE, IsNotNull, DefaultValue, IsPrimaryKey FROM (SELECT ObjectType, ObjectName, cid AS ColumnID, name AS ColumnName, type AS Type, CASE WHEN trim(type) = '' THEN 'Blob' WHEN instr(UPPER(type),'INT' )>0 THEN 'Integer' WHEN instr(UPPER(type),'CLOB')>0 THEN 'Text' WHEN instr(UPPER(type),'CHAR')>0 THEN 'Text' WHEN instr(UPPER(type),'TEXT')>0 THEN 'Text' WHEN instr(UPPER(type),'BLOB')>0 THEN 'Blob' WHEN instr(UPPER(type),'REAL')>0 THEN 'Real' WHEN instr(UPPER(type),'FLOA')>0 THEN 'Real' WHEN instr(UPPER(type),'DOUB')>0 THEN 'Real' ELSE 'Numeric' END AS Affinity, "notnull" AS IsNotNull, dflt_value as DefaultValue, pk AS IsPrimaryKey FROM SysObjects JOIN pragma_table_info(ObjectName) ) ; \-- CREATE VIEW SysIndexes AS SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName COLLATE NOCASE, IndexID, IsUnique COLLATE NOCASE, IndexOrigin COLLATE NOCASE, isPartialIndex FROM (SELECT ObjectType,ObjectName,name AS IndexName, seq AS IndexID, "unique" AS isUnique, origin AS IndexOrigin, partial AS isPartialIndex FROM SysObjects JOIN pragma_index_list(ObjectName) ) ; \-- CREATE VIEW SysIndexColumns AS SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName COLLATE NOCASE, IndexColumnSequence, ColumnID, ColumnName COLLATE NOCASE, isDescendingOrder, Collation, isPartOfKey FROM (SELECT ObjectType, ObjectName, IndexName, seqno AS IndexColumnSequence, cid AS ColumnID, name AS ColumnName, "desc" AS isDescendingOrder, coll AS Collation, key AS isPartOfKey FROM SysIndexes JOIN pragma_index_xinfo(IndexName) ) ; </code>
(7) By Stephan Beal (stephan) on 2021-07-07 03:07:22 in reply to 6 [link]
> Below, I have included some SQL, contributed or linked by Keith Medcalf in this forum some time ago. It implements something like the catalog mentioned in Codd's 4th rule and available in heavy-weight DBMS's. Sidebar for those attempting to use this schema via arbitrary applications: they might impose restrictions which break these queries. e.g. fossil disallows `pragma_index_list()`: ``` $ fossil sql -R ../libfossil.fossil ... sqlite> select * from SysIndexColumns limit 10; Error: unsafe use of virtual table "pragma_index_list" $ sqlite3 ../libfossil.fossil ... sqlite> select * from SysIndexColumns limit 10; table|blob|sqlite_autoindex_blob_1|0|3|uuid|0|BINARY|1 table|blob|sqlite_autoindex_blob_1|1|-1||0|BINARY|0 table|delta|delta_i1|0|1|srcid|0|BINARY|1 table|delta|delta_i1|1|-1||0|BINARY|0 ... ``` Not a bug, just something to be aware of.
(8) By Keith Medcalf (kmedcalf) on 2021-07-07 03:18:05 in reply to 7
Make the view in temp (temporary view). Views and Triggers in temp can run against anything and are always trusted.
(9) By anonymous on 2021-07-20 18:52:40 in reply to 6 [link]
Thanks Larry, I ended up using statement_vtab with the json extension to essentially query, manipulate data, and display it without having to create views and other database objects. Much appreciated feedback from everyone.