Seeing which functions are unique to a specific SQLite / Datasette instance#

pragma_function_list()#

You can list all of the functions available to SQLite using PRAGMA function_list.

Datasette doesn’t allow non-SELECT queries, but it does allow-list a set of pragma_x() functions which you can instead call like this:

1
select * from pragma_function_list()

Here’s that for latest.datasette.io (truncated):

I decided to create a comma-separated list of quoted names. I used this query:

1
select "'" || group_concat(name, "', '") || "'" from pragma_function_list()

Run against latest.datasette.io this returned the following:

'pow', 'group_concat', 'group_concat', 'json_type', 'json_type', 'julianday', 'ntile', 'nullif', 'sqlite_compileoption_get', 'json_valid', 'json_quote', 'json_patch', '->', 'json_array', 'current_timestamp', 'power', 'sqlite_compileoption_used', 'json_remove', 'json_object', 'json_insert', '->>', 'sin', 'sum', 'quote', 'printf', 'likelihood', 'json_replace', 'json_extract', 'last_value', 'rank', 'sign', 'sqrt', 'sinh', 'tan', 'round', 'round', 'rtrim', 'rtrim', 'nth_value', 'tanh', 'random', 'trim', 'trim', 'time', 'radians', 'trunc', 'total', 'substr', 'substr', 'replace', 'upper', 'subtype', 'typeof', 'load_extension', 'load_extension', 'soundex', 'json_group_array', 'avg', 'abs', 'json_group_object', 'json_array_length', 'json_array_length', 'strftime', 'atan', 'asin', 'acos', 'substring', 'substring', 'randomblob', 'unicode', 'percent_rank', 'row_number', 'atanh', 'asinh', 'acosh', 'cos', 'atan2', 'last_insert_rowid', 'sqlite_log', 'unlikely', 'cosh', 'ceil', 'char', 'unixepoch', 'exp', 'count', 'count', 'date', 'ceiling', 'total_changes', 'changes', 'sqlite_version', 'degrees', 'floor', 'coalesce', 'glob', 'zeroblob', 'hex', 'iif', 'sqlite_source_id', 'format', 'datetime', 'cume_dist', 'ln', 'instr', 'json', 'dense_rank', 'log', 'log', 'ifnull', 'current_date', 'current_time', 'lag', 'lag', 'lag', 'mod', 'log2', 'like', 'like', 'max', 'max', 'min', 'min', 'lead', 'lead', 'lead', 'log10', 'lower', 'ltrim', 'ltrim', 'first_value', 'pi', 'length', 'likely', 'json_set', 'escape_fts', 'prepare_connection_args', 'convert_units', 'sleep', 'rtreedepth', 'match', 'snippet', 'fts5_source_id', 'offsets', 'matchinfo', 'matchinfo', 'optimize', 'rtreecheck', 'rtreenode', 'highlight', 'bm25', 'fts3_tokenizer', 'fts3_tokenizer', 'fts5'

Comparing via copy-and-paste#

To see the functions that were registered for https://sqlite-extension-examples.fly.dev/ but not for https://latest.datasette.io/ I used the above output to construct the following query:

select name from pragma_function_list() where name not in ('pow', 'group_concat', 'group_concat', 'json_type', 'json_type', 'julianday', 'ntile', 'nullif', 'sqlite_compileoption_get', 'json_valid', 'json_quote', 'json_patch', '->', 'json_array', 'current_timestamp', 'power', 'sqlite_compileoption_used', 'json_remove', 'json_object', 'json_insert', '->>', 'sin', 'sum', 'quote', 'printf', 'likelihood', 'json_replace', 'json_extract', 'last_value', 'rank', 'sign', 'sqrt', 'sinh', 'tan', 'round', 'round', 'rtrim', 'rtrim', 'nth_value', 'tanh', 'random', 'trim', 'trim', 'time', 'radians', 'trunc', 'total', 'substr', 'substr', 'replace', 'upper', 'subtype', 'typeof', 'load_extension', 'load_extension', 'soundex', 'json_group_array', 'avg', 'abs', 'json_group_object', 'json_array_length', 'json_array_length', 'strftime', 'atan', 'asin', 'acos', 'substring', 'substring', 'randomblob', 'unicode', 'percent_rank', 'row_number', 'atanh', 'asinh', 'acosh', 'cos', 'atan2', 'last_insert_rowid', 'sqlite_log', 'unlikely', 'cosh', 'ceil', 'char', 'unixepoch', 'exp', 'count', 'count', 'date', 'ceiling', 'total_changes', 'changes', 'sqlite_version', 'degrees', 'floor', 'coalesce', 'glob', 'zeroblob', 'hex', 'iif', 'sqlite_source_id', 'format', 'datetime', 'cume_dist', 'ln', 'instr', 'json', 'dense_rank', 'log', 'log', 'ifnull', 'current_date', 'current_time', 'lag', 'lag', 'lag', 'mod', 'log2', 'like', 'like', 'max', 'max', 'min', 'min', 'lead', 'lead', 'lead', 'log10', 'lower', 'ltrim', 'ltrim', 'first_value', 'pi', 'length', 'likely', 'json_set', 'escape_fts', 'prepare_connection_args', 'convert_units', 'sleep', 'rtreedepth', 'match', 'snippet', 'fts5_source_id', 'offsets', 'matchinfo', 'matchinfo', 'optimize', 'rtreecheck', 'rtreenode', 'highlight', 'bm25', 'fts3_tokenizer', 'fts3_tokenizer', 'fts5')

This returned the following list:

1
http_cookies
2
http_headers_date
3
http_headers
4
http_debug
5
html_count
6
html_text
7
html_text
8
html_extract
9
html_group_element_span
10
html_group_element_div
11
html_element
12
http_version
13
html_escape
14
html
15
fts5_rowid
16
fts5_decode_none
17
html_unescape
18
html_trim
19
fts5_decode
20
http_headers_get
21
html_attr_has
22
fts5_expr_tcl
23
html_attr_get
24
fts5_expr
25
fts5_isalnum
26
html_version
27
http_headers_has
28
fts5_fold
29
html_valid
30
html_table
31
html_debug
32
html_attribute_get
33
html_attribute_has

A better solution using json_group_array() and json_each()#

Alex pointed out an alternative solution using SQLite’s JSON functions, which is actually better because it avoids any risk of commas or quotation marks in the values breaking the resulting string.

1
select json_group_array(distinct name)
2
from pragma_function_list()

Try that against latest.datasette.io

Output:

["pow","group_concat","json_type","julianday","ntile","nullif","sqlite_compileoption_get","json_valid","json_quote","json_patch","->","json_array","current_timestamp","power","sqlite_compileoption_used","json_remove","json_object","json_insert","->>","sin","sum","quote","printf","likelihood","json_replace","json_extract","last_value","rank","sign","sqrt","sinh","tan","round","rtrim","nth_value","tanh","random","trim","time","radians","trunc","total","substr","replace","upper","subtype","typeof","load_extension","soundex","json_group_array","avg","abs","json_group_object","json_array_length","strftime","atan","asin","acos","substring","randomblob","unicode","percent_rank","row_number","atanh","asinh","acosh","cos","atan2","last_insert_rowid","sqlite_log","unlikely","cosh","ceil","char","unixepoch","exp","count","date","ceiling","total_changes","changes","sqlite_version","degrees","floor","coalesce","glob","zeroblob","hex","iif","sqlite_source_id","format","datetime","cume_dist","ln","instr","json","dense_rank","log","ifnull","current_date","current_time","lag","mod","log2","like","max","min","lead","log10","lower","ltrim","first_value","pi","length","likely","json_set","escape_fts","prepare_connection_args","convert_units","sleep","rtreedepth","match","snippet","fts5_source_id","offsets","matchinfo","optimize","rtreecheck","rtreenode","highlight","bm25","fts3_tokenizer","fts5"]

Then use this query and run it on the other instance:

1
select
2
  name
3
from
4
  pragma_function_list()
5
where
6
  name not in (
7
    select
8
      value
9
    from
10
      json_each(:json)
11
  )

A neat thing about this alternative is you can pass the single JSON string as a named parameter, rather than needing to paste the list of IN terms into the SQL query itself.

Try that against sqlite-extension-examples.fly.dev.