SQLite3構文 virtual table（create/drop）

　仮想テーブルの作成。

成果物

• ytyaru

情報源

• https://www.sqlite.org/lang_createvtab.html
• https://www.sqlite.org/vtab.html

仮想テーブルとは

　仮想テーブルとは、テーブルであるように見えるが、実際にデータベースファイルに情報を格納しない外部記憶装置又は計算エンジンへのインターフェースである。

仮想テーブル用モジュール

　仮想テーブルにはC言語で実装されたモジュールが必要。モジュール一覧は以下で取得できる。ただしSQLITE_INTROSPECTION_PRAGMASコンパイルオプション必須。また、モジュールを有効化するには各コンパイルオプションなどが必要なこともある。たとえば、こちらのとおりにビルドすれば以下のような結果になる。

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

仮想テーブルの制限

　基本的にはテーブルと同じように操作できる。ただし以下はできない。

• 作成できない
  • インデックス
  • トリガー
• ほとんどの仮想テーブルは読取専用

仮想テーブルの削除

• drop tableで行う。drop virtual table文は無い

構文

• create virtual table テーブル名 using モジュール名
• create virtual table if not exists テーブル名 using モジュール名
• drop table

create virtual table テーブル名 using モジュール名

　FTS5を使う。以下の環境構築を終えていること。

• SQLite3学習　FTSまとめ（ICU, MeCab）

.load /home/pi/root/sys/env/tool/sqlite_ext/fts5_mecab
create virtual table posts using fts5(title, body, tokenize = 'mecab');
insert into posts values('記事のタイトル', '記事の本文。');
insert into posts values('SQLite3 VirtualTable', '仮想テーブルについての記事。');
select * from posts where posts match 'SQLite3';

SQLite3 VirtualTable|仮想テーブルについての記事。

　sqlite_master表にも追加されている。

.headers on
select * from sqlite_master where sql like 'CREATE VIRTUAL TABLE %';

type|name|tbl_name|rootpage|sql
table|posts|posts|0|CREATE VIRTUAL TABLE posts using fts5(title, body, tokenize = 'mecab')

　typeがtableなので、テーブルと区別がつかない。そこでsqlそのままを絞込条件にした。

create virtual table if not exists テーブル名 using モジュール名

　既存の仮想テーブル名をcreateしようとすると、以下エラーになる。

Error: table テーブル名 already exists

　これを抑制するにはif not existsを使う。

.load /home/pi/root/sys/env/tool/sqlite_ext/fts5_mecab
create virtual table posts using fts5(title, body, tokenize = 'mecab');
create virtual table if not exists posts using fts5(title, body, tokenize = 'mecab');

drop table

.load /home/pi/root/sys/env/tool/sqlite_ext/fts5_mecab
create virtual table posts using fts5(title, body, tokenize = 'mecab');
drop table posts;
drop table if exists posts;

対象環境

• 2019-09-09
• Raspbierry pi 3 Model B+
• Raspbian stretch 9.0 2018-11-13
• bash 4.4.12(1)-release
• SQLite 3.29.0
• MeCab 0.996ユーザ辞書

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

前回まで

• SQLite3学習　俯瞰まとめ
• SQLite3学習　環境構築まとめ
• SQLite3学習　インタフェースまとめ（C言語、CLI、対話モード、Tcl...）
• SQLite3学習　ドットコマンドまとめ
• SQLite3学習　JSON拡張まとめ
• SQLite3学習　FTSまとめ（ICU, MeCab）
• SQLite3学習　再帰クエリ（WITH RECURSIVE）
• SQLite3学習　R-Treeモジュール
• SQLite3学習　Geopoly（2次元ベクタ画像の生成）
• SQLite3学習　拡張関数（generate_series）
• SQLite3学習　拡張ライブラリ数学関数（extension-functions.c）
• SQLite3学習　謎と名前
• SQL文の分類（DDL,DML,TCL,DCL）
• SQLite3構文　alter（rename）
• SQLite3構文　alter（add column）概要
• SQLite3構文　alter（add column）制約
• SQLite3構文　alter（add column）sqlite_master変更しても反映されない
• SQLite3構文　alter（add column）スキーマ再定義（テーブル再作成による定義変更）
• SQLite3構文　analyze
• SQLite3構文　attach/detach
• SQLite3構文　begin,end,commit,rollback,savepoint（deferred,immediate,exclusive）
• SQLite3構文　コメント
• SQLite3構文　create/drop
• SQLite3構文　index（create/drop）
• SQLite3構文　table（create/drop）
• SQLite3構文　列制約（default）
• SQLite3構文　列制約（collate）
• SQLite3構文　列制約（primary key）
• SQLite3構文　列制約（primary key）ベストプラクティス
• SQLite3構文　列制約（unique）
• SQLite3構文　列制約（not null）
• SQLite3構文　列制約（check）
• SQLite3構文　列制約（foreign key references）
• SQLite3構文　表制約（primary key, unique, check, foreign key）
• SQLite3でメタデータを取得する方法（DB名(スキーマ名)、テーブル名、列名、制約）
• SQLite3でTEMPの保存先を指定する
• SQLite3構文　delete
• SQLite3ビルド失敗（SQLITE_ENABLE_UPDATE_DELETE_LIMIT）
• SQLite3をソースからビルドする（SQLITE_ENABLE_UPDATE_DELETE_LIMIT）
• SQLite3構文　delete（limit offset, order by）
• SQLite3クエリプランニング（インデックスの働き）
• SQLite3構文　explain
• SQLite3構文　expression
• SQLite3構文　expression（リテラル）
• SQLite3構文　expression（パラメータ）
• SQLite3構文　expression（演算子）
• SQLite3構文　expression（like 句）
• SQLite3構文　expression（glob 句）
• SQLite3構文　expression（regexp 句）
• SQLite3構文　expression（match 句）
• SQLite3構文　expression（is 句）
• SQLite3構文　expression（in 句）
• SQLite3構文　expression（between 句）
• SQLite3構文　expression（case 句）
• SQLite3構文　expression（exists 句）
• SQLite3構文　expression（サブクエリ）
• SQLite3構文　expression（cast）
• SQLite3構文　indexed by
• SQLite3構文　insert
• SQLite3構文　pragma
• SQLite3構文　reindex
• SQLite3構文　select
• SQLite3構文　update
• SQLite3構文　vacuum
• SQLite3構文　trigger（create/drop）
• SQLite3構文　view（create/drop）