SQLite Forum

Query Detail Table
Login

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

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 [link]

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.