やってみる

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

📌 ？
トップ > SQLite3 > SQLite3構文　alter（add column）sqlite_master変更しても反映されない

SQLite3構文　alter（add column）sqlite_master変更しても反映されない

SQLite3

　騙された。

成果物

情報源

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の追加・削除・変更

　だが、できなかった。

方法

  1. トランザクションを開始する
  2. pragma schema_version;で現在のスキーマバージョン番号を確認する（手順6で必要になる）
  3. PRAGMA writable_schema = ON;でスキーマ編集可にする
  4. UPDATE文を実行してsqlite_masterテーブルのテーブルXの定義を変更する: UPDATE sqlite_master SET sql = ... WHERE type = 'table' AND name = 'X';
    • 注意: 破損リスクあり。構文エラーが含まれているとDB破損して読取不能になる。別の空DBでUPDATE文をテストしバックアップすべき
  5. テーブルXへの変更が他のテーブルにも影響する場合、またはインデックスまたはトリガーがスキーマ内のビューである場合は、UPDATE文を実行してこれらの他のテーブルのインデックスおよびビューも変更する。たとえば、列の名前が変更された場合、その列を参照するすべてのFOREIGN KEY制約、トリガー、インデックス、ビューを変更する必要がある。
    • 注意: 破損リスクあり（同上）
  6. PRAGMA schema_version = Xを使用してスキーマバージョン番号をインクリメントする
  7. PRAGMA writable_schema = OFFを使用してスキーマ編集を無効にする
  8. PRAGMA integrity_checkを実行して、スキーマの変更がデータベースに損傷を与えていないことを確認する
  9. 手順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>

所感

　仮に成功したとしても、スキーマバージョンのインクリメントを自動化したいんだよ。手作業でなんてやってられるか。いずれできないか試してみる。

対象環境

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

前回まで