SQLite 備忘録: pragma

概要

コマンドラインツールの「.schema」コマンドで表示されるスキーマ文(DDL)を編集する方法。

デフォルトでは編集できないが、「Pragma writable_schema=ON」を設定することで変更できるようになる。

スキーマ文を編集するだけでテーブルに登録されているデータ構造を変更することは出来ない。詳しくは下記を参照。

以下のような用途に限定される。

テーブルにカラム(列)を追加して読みにくくなったスキーマ文を修正する
外部キー制約を追加する/削除する
デフォルト値制約を追加する/削除する
CHECK制約を設定する

詳細

各テーブルやビューのスキーマ文は「sqlite_schema」というテーブルに保存されている。このテーブルはSQLite3のシステムが使用するテーブルで、ユーザが操作することはほぼない。 (「.table」コマンドでも表示されない)

「sqlite_schema」テーブルはデフォルトだと操作出来ないが「pragma writable_schema」を変更すると操作できるようになる。

このテーブルの「sql」カラムを更新することでスキーマ文を編集する。

実行例

実行環境

Windows 10 64bit
SQLite3 (3.34.0) Command-Line Shell

「.schema」コマンドで表示されるスキーマ文(DDL)は「sqlite_schema」テーブルの「sql」カラムに登録されている。
デフォルトのまま「sqlite_schema」テーブルを更新しようとしてもエラーになる。
「writable_schema」をONにすると更新できるようになる。
以下の点に気を付ければいい。
- 改行や空白がそのまま反映される。
- セミコロン(;)は無くてもいい。
- 「tbl_name」がテーブル名を指しているのでwhere句でテーブル名を指定する。
更新した内容が「.schema」コマンドで表示される。
終わったら「writable_schema」はOFFにしておく。

                    
sqlite> create table test (id integer, name text);
sqlite> .schema test
CREATE TABLE test (id integer, name text);

sqlite> -- # 1.
sqlite> .mode table
sqlite> select * from sqlite_schema;
+-------+------+----------+----------+-------------------------------------------+
| type  | name | tbl_name | rootpage |                    sql                    |
+-------+------+----------+----------+-------------------------------------------+
| table | test | test     | 2        | CREATE TABLE test (id integer, name text) |
+-------+------+----------+----------+-------------------------------------------+

sqlite> -- # 2.
sqlite> update
   ...>     sqlite_schema
   ...> set
   ...>     sql=
   ...> 'create table test (
   ...>     id   integer,
   ...>     name text
   ...> )'
   ...> where
   ...>     tbl_name = 'test';
Error: table sqlite_master may not be modified

sqlite> -- # 3.
sqlite> pragma writable_schema=on;
sqlite> update
   ...>     sqlite_schema
   ...> set
   ...>     sql=
   ...> 'create table test (
   ...>     id   integer,
   ...>     name text
   ...> )'
   ...> where
   ...>     tbl_name = 'test';

sqlite> -- # 4.
sqlite> .schema test
create table test (
    id   integer,
    name text
);

sqlite> -- # 5.
sqlite> pragma writable_schema=off;
sqlite>

データ構造は変更されない

スキーマ文だけを変更してもテーブルのデータには影響しない。その結果、スキーマ文とデータに齟齬が出てしまう。

テーブルのデータ構造を変更する場合は alter table などのDDLを使用したほうがいい。

SQLite 3 ALTER TABLE に関するまとめ

データ構造が変更されない状況の例

データが入っているテーブルのスキーマ文からカラムを削除する。
スキーマ文にないカラムにデータを挿入することはできるし、参照もできてしまう。


sqlite> .mode table
sqlite> create table test (id integer, name text);
sqlite> insert into test values (1, 'sample_name');
sqlite> select * from test;
+----+-------------+
| id |    name     |
+----+-------------+
| 1  | sample_name |
+----+-------------+

sqlite> -- # 1.
sqlite> pragma writable_schema=on;
sqlite> update
   ...>     sqlite_schema
   ...> set
   ...>     sql='create table test (id integer)'
   ...> where
   ...>     tbl_name = 'test'
   ...> ;
sqlite> .schema test
create table test (id integer);

sqlite> -- # 2.
sqlite> insert into test values (2, 'sample_name2');
sqlite> select * from test;
+----+--------------+
| id |     name     |
+----+--------------+
| 1  | sample_name  |
| 2  | sample_name2 |
+----+--------------+

上記のようになってしまう理由は sqlite 3 ではスキーマ文とテーブルのデータが別々に管理されているため。
詳しい情報は公式サイトを参照のこと

参考URL

Pragma writable_schema
公式の writable_schema に関するドキュメント
https://www.sqlite.org/pragma.html#pragma_writable_schema

概要

Pragma schema_versionにはDB構成が変更された回数が記録されている。
これだけ聞くとバージョン管理に使えそうだが、実はそうでもない。

詳細

DBの構成を変更したときにschema_versionの数字が +1される。例えば以下のような操作をした時。

新規テーブルの作成
テーブルの変更
テーブルの削除

他にもビューの編集やトリガーの編集でも +1される。データの新規挿入、更新、削除などでは変更されない。

注意

schema_versionの値はvacuumコマンドでも変更されてしまう。
これは仕様で対処のしようがないため、vacuumを随時実行しているような場合、 schema_versionをDB構成のバージョンとして扱うのは難しい。さらに詳しい情報は公式サイトを参照のこと。

確認方法

以下のコマンドを実行する。


pragma schema_version;

実行例

実行環境

Windows 10 64bit
SQLite3 (3.34.0) Command-Line Shell


sqlite> -- # 1.
sqlite> pragma schema_version;
0

sqlite> -- # 2.
sqlite> create table test (id integer, name text);
sqlite> pragma schema_version;
1

sqlite> alter table test add column email text;
sqlite> pragma schema_version;
2

sqlite> -- # 3.
sqlite> insert into test values (1, 'sample_name', 'sample@example.com');
sqlite> pragma schema_version;
2
sqlite> delete from test;
sqlite> pragma schema_version;
2

sqlite> -- # 4.
sqlite> vacuum;
sqlite> pragma schema_version;
3

DB新規作成時、schema_versionの値は0
テーブル作成や変更でschema_versionが増えていく
データ挿入や削除ではschema_versionは変わらない
vacuumを実行するとschema_versionが増える

pragma schema_version の値の変更

一応schema_versionの値は自由に変更できる。


pragma schema_version=integer;

しかし、schema_versionを変更した状態でデータを操作するとデータが壊れる可能性があるので基本的には変更しない方がいい。

参考URL

Pragma schema_version
公式の schema_version に関するドキュメント
https://www.sqlite.org/pragma.html#pragma_schema_version

SQLite 備忘録

2021年1月25日月曜日

SQLite3 テーブルのスキーマ文を編集する

概要

詳細

実行例

実行環境

データ構造は変更されない

データ構造が変更されない状況の例

参考URL

2021年1月19日火曜日

SQLite 3 pragma schema_versionとは？

概要

詳細

注意

確認方法

実行例

実行環境

pragma schema_version の値の変更

参考URL