騙された。
成果物
情報源
sqlite_master
書き換えによる列の追加
できない。これは公式の説明どおりなのでOK。
create table T(A text); select sql from sqlite_master; update sqlite_master set sql='create table T(A text, B text)';
Error: table sqlite_master may not be modified
PRAGMA writable_schema
SQLテキストだけ変更できる。
PRAGMA writable_schema = 1; update sqlite_master set sql='create table T(A text, B text)'; PRAGMA writable_schema = 0;
SQLテキスト確認。変更されている。
select sql from sqlite_master;
create table T(A text, B text)
挿入できるか確認。できない。
insert into T values('AAA','BBB');
Error: table T has 1 columns but 2 values were supplied
PRAGMA writable_schema
を使ってもSQLテキストが変更されるだけであって、テーブル構造を変更できるわけではないようだ。
この方法で変更できるパターン
公式によると以下の場合はsqlite_master
の修正で変更できるらしい。
CHECK
、FOREIGN KEY
、NOT NULL
の削除DEFAULT
の追加・削除・変更
だが、できなかった。
方法
- トランザクションを開始する
pragma schema_version;
で現在のスキーマバージョン番号を確認する(手順6で必要になる)PRAGMA writable_schema = ON;
でスキーマ編集可にするUPDATE
文を実行してsqlite_master
テーブルのテーブルX
の定義を変更する:UPDATE sqlite_master SET sql = ... WHERE type = 'table' AND name = 'X';
- 注意: 破損リスクあり。構文エラーが含まれているとDB破損して読取不能になる。別の空DBで
UPDATE
文をテストしバックアップすべき
- 注意: 破損リスクあり。構文エラーが含まれているとDB破損して読取不能になる。別の空DBで
- テーブル
X
への変更が他のテーブルにも影響する場合、またはインデックスまたはトリガーがスキーマ内のビューである場合は、UPDATE
文を実行してこれらの他のテーブルのインデックスおよびビューも変更する。たとえば、列の名前が変更された場合、その列を参照するすべてのFOREIGN KEY
制約、トリガー、インデックス、ビューを変更する必要がある。- 注意: 破損リスクあり(同上)
PRAGMA schema_version = X
を使用してスキーマバージョン番号をインクリメントするPRAGMA writable_schema = OFF
を使用してスキーマ編集を無効にするPRAGMA integrity_check
を実行して、スキーマの変更がデータベースに損傷を与えていないことを確認する- 手順1で開始したトランザクションをコミットする
できなかった
ターミナルで以下コマンドを実行する。
sqlite3
まずはテスト用テーブルを作成。制約が有効であることを確認。
制約 | 作成 | 確認 | 期待値 |
---|---|---|---|
check |
create table T(A int check(0<A)); |
insert into T values(0); |
Error: CHECK constraint failed: T |
not null |
create table T(A int not null); |
insert into T values(NULL); |
Error: NOT NULL constraint failed: T.A |
default |
create table T(A int default '<DEFAULT>', B int); |
insert into T(B) values(0); select A from T; |
<DEFAULT> |
foreign key
については確認しなかった。上記が全滅だったので。
ここからが本番。
トランザクション開始。
begin transaction;
スキーマバージョンの確認。(テーブル作成後に0
から1
となる)
pragma schema_version;
1
スキーマを書き換える。制約を消したcreate table
文に。
pragma writable_schema = ON;
制約 | 更新 |
---|---|
check /not null |
update sqlite_master set sql='create table T(A int);'; |
default |
update sqlite_master set sql='create table T(A int, B int);'; |
スキーマバージョンを+1
してセット。
pragma schema_version = 2;
スキーマを書き換え終了。
pragma writable_schema = OFF;
DB損傷していないことを確認。
pragma integrity_check;
ok
コミットする。
commit;
check
制約が削除されたか確認。
insert into T values(0);
Error: CHECK constraint failed: T
あれ? 残ってる……。ほかの制約でも同様……。変更できないじゃん。公式に騙された。私どこか間違えた? それとも翻訳マジック? 再現コードください。
制約 | 確認 | 期待値 | 実際 |
---|---|---|---|
check |
insert into T values(0); select * from T; |
0 |
Error: CHECK constraint failed: T |
not null |
insert into T values(NULL); select * from T; |
NULL |
Error: NOT NULL constraint failed: T.A |
default |
insert into T(B) values(0); select A from T; |
NULL |
<DEFAULT> <DEFAULT> |
所感
仮に成功したとしても、スキーマバージョンのインクリメントを自動化したいんだよ。手作業でなんてやってられるか。いずれできないか試してみる。
対象環境
- 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構文 alter(rename)
- SQL構文 alter(add column)概要
- SQL構文 alter(add column)制約