SQLite 備忘録: SQLite3 テーブルのスキーマ文を編集する

概要

コマンドラインツールの「.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

2021年1月25日月曜日

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

概要

詳細

実行例

実行環境

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

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

参考URL