SQLite Forum

SQL to return list of PRAGMAs

SQL to return list of PRAGMAs

(1) By anonymous on 2021-03-17 09:35:15 [link]

What is the SQL statement that will return the list of PRAGMAs in the version of SQLite in use?

(2) By ddevienne on 2021-03-17 10:43:28 in reply to 1 [link]

[see the documentation](https://www.sqlite.org/pragma.html#pragma_pragma_list)

(3) By anonymous on 2021-03-17 11:31:50 in reply to 2 [link]

Thanks; I had looked but not closely enough, obviously.

Actually, I'm interested in the list of PRAGMAs which do NOT have their respective default values(s).  Can I get this list using SQL?

(4) By Kees Nuyt (knu) on 2021-03-17 17:46:15 in reply to 3 [link]

> Can I get this list using SQL?

Not just like that. You'll have to write a script/program that executes the pragmas you are interested in against your database, and compare the outcome with the outcome of the same script run against a "default database".

Something like:

for p in $(cat relevant_pragmas) ; do printf ".print %s::\nPRAGMA %s;\n" "$p" "$p" ; done | sqlite3 default.sqlite >pragma0.txt
for p in $(cat relevant_pragmas) ; do printf ".print %s::\nPRAGMA %s;\n" "$p" "$p" ; done | sqlite3  yourdb.sqlite >pragma1.txt
diff -u pragma0.txt pragma1.txt


(5.1) By David Jones (vman59) on 2021-03-17 23:05:02 edited from 5.0 in reply to 4 [link]

It would be nice if there was an eponymous virtual table that would let you do the pragma operation as part of an SQL select statement, e.g.:

>SQL select name,type from sqlite_pragma('function_list') where builtin=0;

(7) By anonymous on 2021-03-17 23:41:12 in reply to 5.1 [link]

What does <i>builtin</i> mean?

<i>Available in the DLL but needs to be enabled?</i>


<i>Can be available subject to loading an extension?</i>


<i>Something else?</i>

(9) By Keith Medcalf (kmedcalf) on 2021-03-18 01:13:24 in reply to 7 [link]

If builtin is true (not null and non-zero) that means that the function is builtin (is part of SQLite3).  If builtin is false (zero or null) that means that the function was not "builtin" and was added by a user by some method (even though to a lay observer it appears to be part of the structure).

Note that the concept of "builtin" is the same as the concept of "builtin" when buying a house.  If the bathroom is "builtin" that means that it came with the house.  If you add another bathroom, then that was not "builtin" when you bought the house, even though it may now form an integral part of the structure.

Perhaps a more apropos description would be "written and maintained by the authors of SQLite3" although there is no reason why a "user" could not add their own functions that report as being "builtin" if they wanted to do so.

(10) By anonymous on 2021-03-18 08:21:08 in reply to 9 [link]

Using version 3.35.1 <u>out of the box</u> (i.e. neither loading any extension nor defining any functions), consider this session:

SQLite version 3.35.1 2021-03-15 16:53:57
Enter ".help" for usage hints.
sqlite> select count(*) from pragma_function_list where builtin != 1;
sqlite> select * from pragma_function_list where name like 'geo%';
name                    builtin  type  enc   narg  flags
----------------------  -------  ----  ----  ----  -------
geopoly_regular         0        s     utf8  4     2099200
geopoly_contains_point  0        s     utf8  3     2099200
geopoly_debug           0        s     utf8  1     524288
geopoly_bbox            0        s     utf8  1     2099200
geopoly_overlap         0        s     utf8  2     2099200
geopoly_xform           0        s     utf8  7     2099200
geopoly_svg             0        s     utf8  -1    2099200
geopoly_area            0        s     utf8  1     2099200
geopoly_blob            0        s     utf8  1     2099200
geopoly_within          0        s     utf8  2     2099200
geopoly_json            0        s     utf8  1     2099200
geopoly_ccw             0        s     utf8  1     2099200
geopoly_group_bbox      0        a     utf8  1     2099200
So, the geo* functions are <b>NOT</b> <i>written and maintained by the authors of SQLite3</i> (as you so succinctly described) and I did not add/define them.

1. Where do they come from?
2. Can they be used outright?
3. Can they be used subject to some other configuration oe pre-condition?

(11) By ddevienne on 2021-03-18 09:20:47 in reply to 10

1. [it's an SQLite extension, from DRH himself](https://www.sqlite.org/geopoly.html)
2. Not sure what you mean. Of course they can be used.
3. Many things in SQLite can be turned ON/OFF by a `#define`, including Geopoly.

So your *Out-of-the-Box* is simply that whoever built the SQLite lib or CLI you used,  
included the **Geopoly** extension in the build, that's it.

Whether it should be considered *built-in* or not, well, who cares?  
It does come from the SQLite3 authors (the main one in fact), but since  
it's an extension, that's likely why it's not considered *built-in* I suspect. FWIW.

(12) By Keith Medcalf (kmedcalf) on 2021-03-18 09:27:33 in reply to 10 [link]

Well they are, but they are not builtin functions.  They are part of the geopoly extension which happens to be maintained by the authors of SQLite3 and also happens to be included in the CLI.

Technically, the "builtin" flag represents whether or not the function is registered in the "builtin" table or is in the "user defined" table.

They can be used for working with geopoly data.

(8) By Keith Medcalf (kmedcalf) on 2021-03-18 01:04:23 in reply to 5.1 [link]

You could have your application retrieve the names of the pragma's, remove the ones you don't want to run from the list, then execute the pragma and collect the results:

pragma_list = [[row[0], None] for row in db.cursor().execute('pragma pragma_list')]
for e in pragma_list:
   if not e[0] in ['foreign_key_check', 'integrity_check', 'quick_check' ]:
         e[1] = db.cursor().execute('pragma ' + e[0]).fetchone()[0]

after which the list pragma_list will contain a list of all pragma's together with the result obtained from running that pragma.

(6) By anonymous on 2021-03-17 23:36:41 in reply to 4 [link]

Thanks for the script; as I am using the SQLite3 APIs directly from C#, I don't think I can use it directly ... <i>but I get the idea and I think I can adapt the idea for this purpose.</i>