Greetings.

Is there a way to find out the PRAGMA options set in a connection?

Thanks.

Most of the pragmas have a version that returns what their current setting is, so you can go through all the ones you're interested in and check their settings.

I don't believe there's any way to find out what the compiled defaults are to compare against though, so there's no way to know if one has been changed since the connection started. (Baring querying them all at the start and saving the results of course)

And there's no reset command to force them back to default.

Thanks, David. Yes, I knew those statements you mentioned above. But knowing how smart the SQLite tool is, I thought that there was some,

SELECT * FROM sqlite_master WHERE type='schema' WHERE value != default;

or something like that, that would list all the schemas set during that connection. There should be, IMHO. :-)

There isn't, but not because of the tool's non smartness, simply because of the unneccessity of the request (Imagine that's a real word).

It's not a thing lots of people wonder about, mainly because they are in control of what pragmas get set, and any prgama that wasn't set by them is at the (well-known) default value (bar two or three, like utf8 mode, page-size, and Journal mode).

Same with the Schema change request you mentioned - not something many people want to know within the same session that changed it.

If you find yourself wanting to know this, consider these two steps:

On creating the connection, run this SQL:

DROP TABLE IF EXISTS "schema_change_tracker";
CREATE TABLE "schema_change_tracker" AS SELECT * FROM sqlite_schema WHERE SQL IS NOT NULL;

and just before closing the connection, this:

DROP TABLE IF EXISTS "schema_change_tracker";

Now you have a way to, at any point, see what changes happened to the schema since opening the connection by simply querying the differences.

Here is my favourite diff query, showing what has been added, deleted and even the sql that has changed (where applicable):

WITH CHG(schema_change, name, object_name, old_sql, new_sql) AS (
	SELECT CASE WHEN OLD.name IS NULL THEN 'New ' ELSE 'Changed ' END || CUR.type,
	       CUR.name, CUR.tbl_name,
	       trim(replace(replace(
	         replace(replace(replace(OLD.sql,CHAR(9),' '),CHAR(10),' '),CHAR(13),' ')
	       ,'  ',' '),'  ',' ')),
	       trim(replace(replace(
	         replace(replace(replace(CUR.sql,CHAR(9),' '),CHAR(10),' '),CHAR(13),' ')
	       ,'  ',' '),'  ',' '))
	  FROM      "sqlite_schema"         AS CUR
	  LEFT JOIN "schema_change_tracker" AS OLD ON OLD.name = CUR.name AND OLD.type = CUR.type
	 WHERE (CUR.sql IS NOT NULL) AND (OLD.name IS NULL or OLD.sql <> CUR.sql)
	UNION ALL
	SELECT 'Deleted ' || DEL.type, DEL.name, DEL.tbl_name,
	       trim(replace(replace(
	         replace(replace(replace(DEL.sql,CHAR(9),' '),CHAR(10),' '),CHAR(13),' ')
	       ,'  ',' '),'  ',' ')), NULL
	  FROM      "schema_change_tracker" AS DEL
	  LEFT JOIN "sqlite_schema"         AS CUR ON DEL.name = CUR.name AND DEL.type = CUR.type
	 WHERE CUR.name IS NULL
 )
 SELECT schema_change, name, object_name,
	CASE
	    WHEN old_sql IS NULL THEN substr(new_sql,1,80)
	    WHEN new_sql IS NULL THEN substr(old_sql,1,80)
            WHEN substr(new_sql,1   ,32) <> substr(old_sql,1   ,32) THEN          substr(old_sql,1   ,32) || '.. -->   ' || substr(new_sql,1   ,32) || '...'
            WHEN substr(new_sql,1*32,32) <> substr(old_sql,1*32,32) THEN '...' || substr(old_sql,1*32,32) || '.. --> ..' || substr(new_sql,1*32,32) || '...'
            WHEN substr(new_sql,2*32,32) <> substr(old_sql,2*32,32) THEN '...' || substr(old_sql,2*32,32) || '.. --> ..' || substr(new_sql,2*32,32) || '...'
            WHEN substr(new_sql,3*32,32) <> substr(old_sql,3*32,32) THEN '...' || substr(old_sql,3*32,32) || '.. --> ..' || substr(new_sql,3*32,32) || '...'
            WHEN substr(new_sql,4*32,32) <> substr(old_sql,4*32,32) THEN '...' || substr(old_sql,4*32,32) || '.. --> ..' || substr(new_sql,4*32,32) || '...'
            WHEN substr(new_sql,5*32,32) <> substr(old_sql,5*32,32) THEN '...' || substr(old_sql,5*32,32) || '.. --> ..' || substr(new_sql,5*32,32) || '...'
            WHEN substr(new_sql,6*32,32) <> substr(old_sql,6*32,32) THEN '...' || substr(old_sql,6*32,32) || '.. --> ..' || substr(new_sql,6*32,32) || '...'
                                                                    ELSE '...' || substr(old_sql,7*32,32) || '.. --> ..' || substr(new_sql,7*32,32) || '...'
	END AS changed_sql
   FROM CHG
  ;

Note: You could filter out the table named

"schema_change_tracker"

(since it will always be a "new table" in the query), but I like to leave it in just to be sure I do get output in case of no changes, since it's an easy confirmation that the mechanism is working.

How about:

with settings(pragma, value) as
     (
                  select 'analysis_limit', * from pragma_analysis_limit
        union all select 'auto_vacuum', * from pragma_auto_vacuum
        union all select 'automatic_index', * from pragma_automatic_index
        union all select 'busy_timeout', * from pragma_busy_timeout
        union all select 'cache_size', * from pragma_cache_size
        union all select 'cache_spill', * from pragma_cache_spill
        union all select 'cell_size_check', * from pragma_cell_size_check
        union all select 'checkpoint_fullfsync', * from pragma_checkpoint_fullfsync
        union all select 'defer_foreign_keys', * from pragma_defer_foreign_keys
        union all select 'foreign_keys', * from pragma_foreign_keys
        union all select 'fullfsync', * from pragma_fullfsync
        union all select 'hard_heap_limit', * from pragma_hard_heap_limit
        union all select 'ignore_check_constraints', * from pragma_ignore_check_constraints
        union all select 'journal_mode', * from pragma_journal_mode
        union all select 'journal_size_limit', * from pragma_journal_size_limit
        union all select 'legacy_alter_table', * from pragma_legacy_alter_table
        union all select 'locking_mode', * from pragma_locking_mode
        union all select 'max_page_count', * from pragma_max_page_count
        union all select 'query_only', * from pragma_query_only
        union all select 'read_uncommitted', * from pragma_read_uncommitted
        union all select 'recursive_triggers', * from pragma_recursive_triggers
        union all select 'reverse_unordered_selects', * from pragma_reverse_unordered_selects
        union all select 'secure_delete', * from pragma_secure_delete
        union all select 'soft_heap_limit', * from pragma_soft_heap_limit
        union all select 'synchronous', * from pragma_synchronous
        union all select 'temp_store', * from pragma_temp_store
        union all select 'threads', * from pragma_threads
        union all select 'trusted_schema', * from pragma_trusted_schema
        union all select 'writable_schema', * from pragma_writable_schema
--      union all select 'case_sensitive_like', * from pragma_case_sensitive_like
--      union all select 'mmap_size', * from pragma_mmap_size
--      union all select 'wal_autocheckpoint', * from pragma_wal_autocheckpoint
     )
select * from settings
;

This will generate a table of pragma settings. The entries that are commented at the end (with the SQL -- comment indicator) are settings that cannot be accessed via the pragma_ virtual tables for some reason that I cannot fathom, despite things that are useless to access via pragma_ tables are valid.

It will produce pretty output like so:

┌─────────────────────────────┬────────────┐
│           pragma            │   value    │
├─────────────────────────────┼────────────┤
│ 'analysis_limit'            │ 0          │
│ 'auto_vacuum'               │ 0          │
│ 'automatic_index'           │ 1          │
│ 'busy_timeout'              │ 5000       │
│ 'cache_size'                │ -1048576   │
│ 'cache_spill'               │ 253719     │
│ 'cell_size_check'           │ 0          │
│ 'checkpoint_fullfsync'      │ 0          │
│ 'defer_foreign_keys'        │ 0          │
│ 'foreign_keys'              │ 1          │
│ 'fullfsync'                 │ 0          │
│ 'hard_heap_limit'           │ 8589934592 │
│ 'ignore_check_constraints'  │ 0          │
│ 'journal_mode'              │ 'delete'   │
│ 'journal_size_limit'        │ -1         │
│ 'legacy_alter_table'        │ 0          │
│ 'locking_mode'              │ 'normal'   │
│ 'max_page_count'            │ 1073741823 │
│ 'query_only'                │ 0          │
│ 'read_uncommitted'          │ 0          │
│ 'recursive_triggers'        │ 1          │
│ 'reverse_unordered_selects' │ 0          │
│ 'secure_delete'             │ 0          │
│ 'soft_heap_limit'           │ 8589934592 │
│ 'synchronous'               │ 2          │
│ 'temp_store'                │ 0          │
│ 'threads'                   │ 8          │
│ 'trusted_schema'            │ 0          │
│ 'writable_schema'           │ 0          │
└─────────────────────────────┴────────────┘

Well, don't know if the OP will use it, but it's going right into my standard views. Thanks!

Well, don't know if the OP will use it, but it's going right into my standard views. Thanks!

Oh, believe me! I'll use it. :-)

Thank you! Thank you! Thank you all!