やってみる

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

📌 ？
トップ > SQLite3 > SQLite3でメタデータを取得する方法（DB名(スキーマ名)、テーブル名、列名、制約）

SQLite3でメタデータを取得する方法（DB名(スキーマ名)、テーブル名、列名、制約）

SQLite3

　DB名(スキーマ名)、テーブル名、列名、制約。

成果物

一覧

DB名(スキーマ名)

.databases

main:

　または以下。

pragma database_list;

seq|name|file
0|main|

テーブル名

select name from sqlite_master;

.tables;

列名

create table users(
  id   integer primary key,
  name text    unique not null,
  age  integer default 0 check(0<=age),
  cid  references classes(id)
);
create table classes(id integer primary key);
pragma table_info(users);

cid|name|type|notnull|dflt_value|pk
0|id|integer|0||1
1|name|text|1||0
2|age|integer|0|0|0
3|cid||0||0

　これをテーブルにして一部だけ取得したいときはどうする？　たとえば列名の一覧だけを取得したいときは？

create table users(
  id   integer primary key,
  name text    unique not null,
  age  integer default 0 check(0<=age),
  cid  references classes(id)
);
create table classes(id integer primary key);
.headers on
.mode tabs
.output ./meta_users.tsv
pragma table_info(users);
.output stdout
.import ./meta_users.tsv meta_users
.headers off
select name from meta_users;

id
name
age
cid

　（あとでわかったことだが、プラグマ関数を使えばいいだけだった……）

sqlite3 :memory: \
"create table users(\
  id   integer primary key,\
  name text    unique not null,\
  age  integer default 0 check(0<=age),\
  cid  references classes(id)\
);" \
"create table classes(id integer primary key);" \
".headers on" \
"select * from pragma_table_info('users');"

型

　指定した列名の型は？

select type from meta_users where name='age';

integer

制約

primary key

　主キー制約された列名一覧。

select name from meta_users where pk='1';

id

unique

　インデックスと同じ。

not null

　not null制約された列名一覧。

select name from meta_users where notnull='1';

Error: near "notnull": syntax error

　なぜ？　もしや予約語？　ダブルクォートして識別子として認識させると成功した。

select name from meta_users where "notnull"='1';

name

default

　指定した列名におけるdefault値。

select dflt_value from meta_users where name='age';

0

check

　不明。

foreign key

　外部キー制約一覧。

.mode list
.headers on
pragma foreign_key_list(users);

id|seq|table|from|to|on_update|on_delete|match
0|0|classes|cid|id|NO ACTION|NO ACTION|NONE

インデックス

　インデックス名一覧。

.indexes

sqlite_autoindex_users_1

　または以下。

.headers on
pragma index_list(users);

seq|name|unique|origin|partial
0|sqlite_autoindex_users_1|1|u|0

　インデックス名における対象の列名などを取得する。

.headers on
pragma index_info(sqlite_autoindex_users_1);
pragma index_xinfo(sqlite_autoindex_users_1);

seqno|cid|name
0|1|name

seqno|cid|name|desc|coll|key
0|1|name|0|BINARY|1
1|-1||0|BINARY|0

　ご覧の通り、unique制約にしたname列がインデックス対象列らしい。

トリガー

create trigger trg_0 delete on users begin delete from classes; end;
.headers on
.mode list
select * from sqlite_master where type = 'trigger';

type|name|tbl_name|rootpage|sql
trigger|trg_0|users|0|CREATE TRIGGER trg_0 delete on users begin delete from classes; end
  • name列: トリガー名
  • tbl_name列: トリガー対象テーブル名

ビュー

create view view_all_users as select * from users;
.headers on
.mode list
select * from sqlite_master where type = 'view';

type|name|tbl_name|rootpage|sql
view|view_all_users|view_all_users|0|CREATE VIEW view_all_users as select * from users

仮想テーブル

pragma module_list;

zipfile
completion
fsdir
sqlite_stmt
json_tree
json_each
sqlite_dbpage
sqlite_dbdata
fts5vocab
fts3
fts4
sqlite_dbptr
fts3tokenize
dbstat
fts5
rtree
rtree_i32
fts4aux
geopoly

pragma一覧

pragma pragma_list;

　pragmaには他にも便利な機能がある。それを一覧するには上記。

対象環境

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

前回まで