やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3関数の一覧と分類

 133件あった。コンパイルオプションとライブラリの動的ロード次第でさらに増える。pragma関数63件も数に入ってない。

成果物

目次

状態を確認する

コンパイルオプション一覧

 関数は増減する。コンパイルオプションやライブラリの動的ロードによって。そこで、現在使用しているビルドに対するコンパイルオプションを示す。

sqlite3 :memory: "pragma compile_options;"
ALLOW_URI_AUTHORITY
COMPILER=gcc-6.3.0 20170516
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS4
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_ICU
ENABLE_JSON1
ENABLE_MEMSYS3
ENABLE_PREUPDATE_HOOK
ENABLE_RTREE
ENABLE_SESSION
ENABLE_SNAPSHOT
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
ENABLE_UNLOCK_NOTIFY
ENABLE_UPDATE_DELETE_LIMIT
HAVE_ISNAN
THREADSAFE=1
USE_ALLOCA
USE_FCNTL_TRACE

一覧

関数一覧

sqlite3 :memory: \
"select count(*) from pragma_function_list;" \
".headers on" \
"pragma function_list;"
133
name|builtin
group_concat|1
julianday|1
ntile|1
nullif|1
sqlite_compileoption_get|1
current_timestamp|1
sqlite_compileoption_used|1
sum|1
quote|1
printf|1
likelihood|1
last_value|1
rank|1
round|1
rtrim|1
nth_value|1
random|1
trim|1
time|1
total|1
substr|1
replace|1
upper|1
typeof|1
load_extension|1
avg|1
abs|1
strftime|1
unknown|1
randomblob|1
unicode|1
percent_rank|1
row_number|1
last_insert_rowid|1
sqlite_log|1
unlikely|1
char|1
count|1
date|1
sqlite_offset|1
total_changes|1
changes|1
sqlite_version|1
coalesce|1
glob|1
zeroblob|1
hex|1
sqlite_source_id|1
datetime|1
cume_dist|1
instr|1
dense_rank|1
ifnull|1
current_date|1
current_time|1
lag|1
like|1
max|1
min|1
lead|1
lower|1
ltrim|1
first_value|1
length|1
likely|1
edit|0
shell_int32|0
shell_putsnl|0
shell_module_schema|0
writefile|0
shell_escape_crnl|0
readfile|0
json_valid|0
json_type|0
json_set|0
json_extract|0
json_array_length|0
snippet|0
geopoly_overlap|0
fts5_fold|0
shell_add_schema|0
json_insert|0
fts5_expr_tcl|0
fts5|0
fts3_tokenizer|0
fts5_isalnum|0
geopoly_xform|0
icu_load_collation|0
fts5_expr|0
upper|0
json_array|0
zipfile_cds|0
fts5_rowid|0
fts5_source_id|0
offsets|0
fts5_decode_none|0
lsmode|0
json_group_array|0
json_object|0
geopoly_contains_point|0
highlight|0
sha3_query|0
rtreenode|0
sqlar_compress|0
json|0
rtreecheck|0
json_group_object|0
geopoly_regular|0
fts5_decode|0
json_quote|0
matchinfo|0
geopoly_debug|0
sha3|0
geopoly_bbox|0
match|0
lower|0
bm25|0
geopoly_svg|0
json_replace|0
optimize|0
regexp|0
geopoly_ccw|0
zipfile|0
json_patch|0
like|0
sqlar_uncompress|0
geopoly_blob|0
json_remove|0
geopoly_within|0
rtreedepth|0
geopoly_area|0
geopoly_json|0
geopoly_group_bbox|0

動的ロード関数

 とりあえず動的ロード関数については考えないものとする。

分類

 上記133の関数を分類する。

観点

 カテゴリは他にも以下が考えられる。

  • 表値関数
    • pragma関数
    • 仮想テーブルモジュール関数(json_each等)
      • モジュール(json1, fts5等)
  • 動的ロード関数
    • 数学関数

 だが、上記は分類するのが難しそうなので未分類とする。そもそもpragma関数に至っては一覧に出てこない。

ファイル化

 関数名をファイルにする。

一覧

sqlite3 :memory: "select name from pragma_function_list order by name;" > sql_fn_list.txt

 builtinで分類する。

sqlite3 :memory: "select name from pragma_function_list where builtin=0;" > sql_fn_not_builtin.txt
sqlite3 :memory: "select name from pragma_function_list where builtin=1;" > sql_fn_builtin.txt

aggregate

sql_fn_aggregate.txt

avg
count
group_concat
max
min
sum
total

core

sql_fn_core.txt

abs
changes
char
coalesce
glob
hex
ifnull
instr
last_insert_rowid
length
like
likelihood
likely
load_extension
lower
ltrim
max
min
nullif
printf
quote
random
randomblob
replace
round
rtrim
soundex
sqlite_compileoption_get
sqlite_compileoption_used
sqlite_offset
sqlite_source_id
sqlite_version
substr
total_changes
trim
trim
typeof
unicode
unlikely
upper
zeroblob

date

sql_fn_date.txt

date
time
datetime
julianday
strftime

window

sql_fn_window.txt

row_number
rank
dense_rank
percent_rank
cume_dist
ntile
lag
lead
first_value
last_value
nth_value

分類表をつくる

 まずは分類表をつくる。上記までのファイルが必要。

make_categoly.sql

create table result     (name text, is_builtin text, categoly text);
create table list       (name text);
create table not_builtin(name text);
create table builtin    (name text);
create table aggregate  (name text);
create table core       (name text);
create table date       (name text);
create table window     (name text);

.import 'sql_fn_list.txt'        'list'
.import 'sql_fn_not_builtin.txt' 'not_builtin'
.import 'sql_fn_builtin.txt'     'builtin'
.import 'sql_fn_aggregate.txt'   'aggregate'
.import 'sql_fn_core.txt'        'core'
.import 'sql_fn_date.txt'        'date'
.import 'sql_fn_window.txt'      'window'

insert into result 
  select
    list.name,
    list.name in (select * from builtin),
    case 
      when list.name in (select * from aggregate) then 'aggregate'
      when list.name in (select * from core)      then 'core'
      when list.name in (select * from date)      then 'date'
      when list.name in (select * from window)    then 'window'
    end
  from list;

.mode tabs
.headers on
.output sql_fn_result.tsv
select * from result;
.output sql_fn_null_categoly_result.tsv
select * from result where categoly is NULL;
.output sql_fn_categoly_result.tsv
select * from result where categoly is not NULL;
.output stdout
sqlite3 :memory: ".read make_categoly.sql"

分類表

 分類した結果のTSVファイルは以下。

sql_fn_result.tsv

name is_builtin  categoly
abs 1   core
avg 1   aggregate
bm25    0   
changes 1   core
char    1   core
coalesce    1   core
count   1   aggregate
cume_dist   1   window
current_date    1   
current_time    1   
current_timestamp   1   
date    1   date
datetime    1   date
dense_rank  1   window
edit    0   
first_value 1   window
fts3_tokenizer  0   
fts5    0   
fts5_decode 0   
fts5_decode_none    0   
fts5_expr   0   
fts5_expr_tcl   0   
fts5_fold   0   
fts5_isalnum    0   
fts5_rowid  0   
fts5_source_id  0   
geopoly_area    0   
geopoly_bbox    0   
geopoly_blob    0   
geopoly_ccw 0   
geopoly_contains_point  0   
geopoly_debug   0   
geopoly_group_bbox  0   
geopoly_json    0   
geopoly_overlap 0   
geopoly_regular 0   
geopoly_svg 0   
geopoly_within  0   
geopoly_xform   0   
glob    1   core
group_concat    1   aggregate
hex 1   core
highlight   0   
icu_load_collation  0   
ifnull  1   core
instr   1   core
json    0   
json_array  0   
json_array_length   0   
json_extract    0   
json_group_array    0   
json_group_object   0   
json_insert 0   
json_object 0   
json_patch  0   
json_quote  0   
json_remove 0   
json_replace    0   
json_set    0   
json_type   0   
json_valid  0   
julianday   1   date
lag 1   window
last_insert_rowid   1   core
last_value  1   window
lead    1   window
length  1   core
like    1   core
like    1   core
likelihood  1   core
likely  1   core
load_extension  1   core
lower   1   core
lower   1   core
lsmode  0   
ltrim   1   core
match   0   
matchinfo   0   
max 1   aggregate
min 1   aggregate
nth_value   1   window
ntile   1   window
nullif  1   core
offsets 0   
optimize    0   
percent_rank    1   window
printf  1   core
quote   1   core
random  1   core
randomblob  1   core
rank    1   window
readfile    0   
regexp  0   
replace 1   core
round   1   core
row_number  1   window
rtreecheck  0   
rtreedepth  0   
rtreenode   0   
rtrim   1   core
sha3    0   
sha3_query  0   
shell_add_schema    0   
shell_escape_crnl   0   
shell_int32 0   
shell_module_schema 0   
shell_putsnl    0   
snippet 0   
sqlar_compress  0   
sqlar_uncompress    0   
sqlite_compileoption_get    1   core
sqlite_compileoption_used   1   core
sqlite_log  1   
sqlite_offset   1   core
sqlite_source_id    1   core
sqlite_version  1   core
strftime    1   date
substr  1   core
sum 1   aggregate
time    1   date
total   1   aggregate
total_changes   1   core
trim    1   core
typeof  1   core
unicode 1   core
unknown 1   
unlikely    1   core
upper   1   core
upper   1   core
writefile   0   
zeroblob    1   core
zipfile 0   
zipfile_cds 0   

関数(未分類)

name is_builtin  categoly
bm25    0  
current_date 1  
current_time    1  
current_timestamp    1  
edit    0  
fts3_tokenizer  0  
fts5    0  
fts5_decode 0  
fts5_decode_none    0  
fts5_expr   0  
fts5_expr_tcl   0  
fts5_fold   0  
fts5_isalnum    0  
fts5_rowid  0  
fts5_source_id  0  
geopoly_area    0  
geopoly_bbox    0  
geopoly_blob    0  
geopoly_ccw 0  
geopoly_contains_point  0  
geopoly_debug   0  
geopoly_group_bbox  0  
geopoly_json    0  
geopoly_overlap 0  
geopoly_regular 0  
geopoly_svg 0  
geopoly_within  0  
geopoly_xform   0  
highlight   0  
icu_load_collation  0  
json    0  
json_array  0  
json_array_length   0  
json_extract    0  
json_group_array    0  
json_group_object   0  
json_insert 0  
json_object 0  
json_patch  0  
json_quote  0  
json_remove 0  
json_replace    0  
json_set    0  
json_type   0  
json_valid  0  
lsmode  0  
match   0  
matchinfo   0  
offsets 0  
optimize    0  
readfile    0  
regexp  0  
rtreecheck  0  
rtreedepth  0  
rtreenode   0  
sha3    0  
sha3_query  0  
shell_add_schema    0  
shell_escape_crnl   0  
shell_int32 0  
shell_module_schema 0  
shell_putsnl    0  
snippet 0  
sqlar_compress  0  
sqlar_uncompress    0  
sqlite_log  1  
unknown 1  
writefile   0  
zipfile 0  
zipfile_cds 0  

 FTS, JSON1, SQLArchive, geopolyなど、仮想テーブルのモジュールで使う関数。

 current_date, current_time, current_timestampは日時関数に分類すべきな気がするが、なぜかdateには載っていない。

関数(分類済み)

name is_builtin  categoly
abs  1  core
avg  1  aggregate
changes 1  core
char   1  core
coalesce 1  core
count    1  aggregate
cume_dist    1  window
date   1  date
datetime    1  date
dense_rank   1  window
first_value 1  window
glob    1  core
group_concat    1  aggregate
hex 1  core
ifnull  1  core
instr    1  core
julianday   1  date
lag 1  window
last_insert_rowid   1  core
last_value  1  window
lead    1  window
length   1  core
like  1  core
like  1  core
likelihood  1  core
likely  1  core
load_extension  1  core
lower    1  core
lower    1  core
ltrim    1  core
max  1  aggregate
min  1  aggregate
nth_value   1  window
ntile   1  window
nullif   1  core
percent_rank 1  window
printf  1  core
quote   1  core
random  1  core
randomblob  1  core
rank 1  window
replace  1  core
round    1  core
row_number  1  window
rtrim    1  core
sqlite_compileoption_get    1  core
sqlite_compileoption_used   1  core
sqlite_offset   1  core
sqlite_source_id    1  core
sqlite_version  1  core
strftime    1  date
substr   1  core
sum  1  aggregate
time    1  date
total   1  aggregate
total_changes   1  core
trim 1  core
typeof  1  core
unicode 1  core
unlikely    1  core
upper    1  core
upper    1  core
zeroblob    1  core

 builtin(組込)関数であり、SQLite3ドキュメントで分類されていたもの。

動的ロード関数

 動的ロード関数は、ロードしていない一覧と、ロードしたときの一覧の差分を出せば求まる。

pragma関数

 pragma関数は、プラグマ名の前にpragma_を付与したもの。from句などで使える。関数とは言ったが引数()がないものも使える。

get_pragma_function.sh

echo -n "" > pragma.txt
for pragma in `sqlite3 :memory: "select name from pragma_pragma_list;"`; do
#  sqlite3 :memory: "select * from pragma_${pragma};"
    echo "pragma_${pragma}" >> pragma.txt
done
cat pragma.txt | wc -l 
63

pragma.txt

pragma_application_id
pragma_auto_vacuum
pragma_automatic_index
pragma_busy_timeout
pragma_cache_size
pragma_cache_spill
pragma_case_sensitive_like
pragma_cell_size_check
pragma_checkpoint_fullfsync
pragma_collation_list
pragma_compile_options
pragma_count_changes
pragma_data_version
pragma_database_list
pragma_default_cache_size
pragma_defer_foreign_keys
pragma_empty_result_callbacks
pragma_encoding
pragma_foreign_key_check
pragma_foreign_key_list
pragma_foreign_keys
pragma_freelist_count
pragma_full_column_names
pragma_fullfsync
pragma_function_list
pragma_ignore_check_constraints
pragma_incremental_vacuum
pragma_index_info
pragma_index_list
pragma_index_xinfo
pragma_integrity_check
pragma_journal_mode
pragma_journal_size_limit
pragma_legacy_alter_table
pragma_legacy_file_format
pragma_locking_mode
pragma_max_page_count
pragma_mmap_size
pragma_module_list
pragma_optimize
pragma_page_count
pragma_page_size
pragma_pragma_list
pragma_query_only
pragma_quick_check
pragma_read_uncommitted
pragma_recursive_triggers
pragma_reverse_unordered_selects
pragma_schema_version
pragma_secure_delete
pragma_short_column_names
pragma_shrink_memory
pragma_soft_heap_limit
pragma_synchronous
pragma_table_info
pragma_table_xinfo
pragma_temp_store
pragma_temp_store_directory
pragma_threads
pragma_user_version
pragma_wal_autocheckpoint
pragma_wal_checkpoint
pragma_writable_schema

学習方針

  • core
  • date
  • agglegate
  • window

 組込を優先。上記はすべて組込である。非組込はコンパイルオプションによる仮想テーブルのモジュール関数と思われる。

 非組込(未分類)の関数については、それぞれのモジュールごとに分類して使ってみたい。最終的に「モジュールまとめ」としたい。以下のように、すでにほとんど実施済みかもしれない。漏れを探して実施したい。

 プラグマ関数については、以下ですでにやったこととする。ただ、「プラグマ関数まとめ」として今回やったように一覧を作りたい。

 動的ロード関数については、そもそもライブラリのビルドからやらねばならない。以下、すでにやったものを挙げる。他のをどれだけやるかは未定。できれば「拡張関数まとめ」としてすべて網羅したい。

 上記すべてを「SQLite3関数まとめ」の項目にできたら完成。

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで