やってみる

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

SQLite3構文 alter(add column)スキーマ再定義(テーブル再作成による定義変更)

 alter文ではできないことがある。新テーブル生成+データ移行+旧テーブル削除すればいい。

成果物

情報源

概要

 前回まででalter文にはできないことがあることがわかった。たとえばprimary key制約をもった列を追加することはできない。というか、テーブル変更できるパターンのほうが遥かに少ない。

 でも、それらの縛りを無視して変更したいときもある。そこで今回の方法を使う。

方法

  1. 外部キー制約が有効になっている場合、PRAGMA foreign_keys = OFFを使用して無効化
  2. トランザクションを開始する
  3. テーブルXに関連付けられているすべてのインデックス、トリガー、ビューを網羅する: SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.
  4. 新しいテーブルnew_Xを作る: create table new_X(...);
  5. データをコピーする: insert into new_X select ... from X;
  6. 旧テーブルXを削除する: drop table X;
  7. new_Xの名前をXに変更する: alter table new_X rename to X
  8. 3で見つけたインデックス、トリガー、ビューを変更する(テーブルと同様にcreate文で)
  9. ビューがスキーマXの影響を受ける方法でテーブルXを参照している場合、drop viewを使用してそれらのビューを削除し、create viewを使用してスキーマの変更に対応するために必要な変更を加えて再作成する
  10. 外部キー制約が最初に有効化されていた場合、PRAGMA foreign_key_checkを実行して、スキーマの変更が外部キー制約を壊していないことを確認する
  11. 2で開始したトランザクションcommitする
  12. 1で外部キー制約が有効になっていた場合は有効化する

 長い。自然言語だけでなくSQL文でも書いてくれ。切に願う。

 後で確かめてわかったが、上記の方法は順序がまちがってないか? 正しくは以下。renameは作り直し終えた後にやるのがポイント。どちらも公式に書いてあった。それとも私の理解がアレなせいか、翻訳のせいなのか。頼むから動作するSQL文を書いてくれ。

  1. 新しいテーブルを作成
  2. データをコピーする
  3. 古いテーブルをドロップ
  4. 新しい名前を古い名前に変更

 さらに公式には明記していないが、たぶん以下が正しいのでは?

  1. 新しいテーブルを作成
  2. データをコピーする
  3. インデックス、トリガー、ビューを削除&再作成
  4. 古いテーブルをドロップ
  5. 新しい名前を古い名前に変更

やってみる

 ターミナルで以下コマンド実行

sqlite3

 元テーブルを作成する。

pragma foreign_keys = ON;
create table X(
  A text,
  B text,
  C text
);
insert into X values('1','B0','C0');
create table Y(A integer primary key);

 変更してみる。

pragma foreign_keys = OFF;
begin transaction;

create table new_X(
  A integer primary key,
  C text unique default NULL,
  D text check(0<=D and D<=100),
  E text not null,
  F text default (datetime('now','localtime')),
  G integer references Y(A)
);
insert into new_X(A, C, D, E, G) select cast(A as int), C, 0, 'E', NULL from X;
drop table X;
alter table new_X rename to X;

commit;
pragma foreign_keys = ON;
select sql from sqlite_master;
CREATE TABLE Y(A integer primary key)
CREATE TABLE "X"(
  A integer primary key,
  C text unique default NULL,
  D text check(0<=D and D<=100),
  E text not null,
  F text default (datetime('now','localtime')),
  G integer references Y(A)
)
.headers on
.mode column
select * from X;
A|C|D|E|F|G
1|C0|0|E|2019-08-26 13:41:27|

インデックス、トリガー、ビュー有り

 ターミナルで以下コマンド実行

sqlite3

 元テーブルを作成する。

pragma foreign_keys = ON;
create table X(
  A text,
  B text,
  C text
);
insert into X values('1','B0','C0');
create table Y(A integer primary key);
insert into Y values(1);
create table trriger_log(id integer primary key, log text);
create index idx_A on X(A);
create view view_all as select * from X;
create trigger trg_1 insert on X
  begin
    insert into trriger_log(log) values('トリガーで挿入!');
  end;   

 まずはインデックスの存在確認。

.indices X
idx_A

 ビューの確認。

select * from view_all;
1|B0|C0

 トリガーの確認。

select * from trriger_log;





insert into X values(2, 'B1', 'C1');
select * from trriger_log;
1|トリガーで挿入!

変更

pragma foreign_keys = OFF;
begin transaction;

create table new_X(
  A integer primary key,
  C text unique default NULL,
  D text check(0<=D and D<=100),
  E text not null,
  F text default (datetime('now','localtime')),
  G integer references Y(A)
);
insert into new_X(A, C, D, E, G) select cast(A as int), C, 0, 'E', NULL from X;

drop view view_all;
drop index idx_A;
drop trriger trg_1;
drop table X;
create index idx_A on new_X(A);
create view view_all as select * from new_X;
create trigger trg_1 insert on new_X
  begin
    insert into trriger_log(log) values('トリガーで挿入!');
  end;   

alter table new_X rename to X;

commit;
pragma foreign_keys = ON;

 長文のほうの方法にあったのとは一部順番が違う。renameする前にデータ移行とインデックス等の削除&再作成している。内容が合っているかもわからん。そもそもインデックス、ビュー、トリガーについて未学習ゆえよく知らん。

 テーブル確認。

.tables
X            Y            trriger_log  view_all   

 インデックス。sqlite_autoindex_X_1って何? 勝手にできたの? いつ? どこで? なぜ?

.indices X
idx_A                 sqlite_autoindex_X_1

 トリガーは動いた。

insert into X(A, C, D, E, G) values(3, NULL, 0, '', 1);
select * from trriger_log;
1|トリガーで挿入!

 最後にsqlite_masterを確認。

select * from sqlite_master;
table|Y|Y|3|CREATE TABLE Y(A integer primary key)
table|trriger_log|trriger_log|4|CREATE TABLE trriger_log(id integer primary key, log text)
table|X|X|6|CREATE TABLE "X"(
  A integer primary key,
  C text unique default NULL,
  D text check(0<=D and D<=100),
  E text not null,
  F text default (datetime('now','localtime')),
  G integer references Y(A)
)
index|sqlite_autoindex_X_1|X|7|
index|idx_A|X|2|CREATE INDEX idx_A on "X"(A)
view|view_all|view_all|0|CREATE VIEW view_all as select * from "X"
trigger|trg_1|X|0|CREATE TRIGGER trg_1 insert on "X"
  begin
    insert into trriger_log(log) values('トリガーで挿入!');
  end

 sqlite_autoindex_X_1ってsql入ってないんだが。ゴミじゃないの? どうしよう処理に困る……。

失敗ログ

失敗1

pragma foreign_keys = OFF;
begin transaction;

create table new_X(
  A integer primary key,
  C text unique default NULL,
  D text check(0<=D and D<=100),
  E text not null,
  F text default (datetime('now','localtime')),
  G integer references Y(A)
);
insert into new_X(A, C, D, E, G) select cast(A as int), C, 0, 'E', NULL from X;

drop view view_all;
drop table X;
create view view_all as select * from new_X;

alter table new_X rename to X;

commit;
pragma foreign_keys = ON;
.tables
X            Y            trriger_log  view_all   

 インデックスは、名前が変わってる?

.indices X
sqlite_autoindex_X_1

 トリガーも死んでる。

insert into X(A, C, D, E, G) values(3, NULL, 0, '', 1);
select * from trriger_log;



失敗2

pragma foreign_keys = OFF;
begin transaction;

create table new_X(
  A integer primary key,
  C text unique default NULL,
  D text check(0<=D and D<=100),
  E text not null,
  F text default (datetime('now','localtime')),
  G integer references Y(A)
);
insert into new_X(A, C, D, E, G) select cast(A as int), C, 0, 'E', NULL from X;
drop table X;
alter table new_X rename to X;

commit;
pragma foreign_keys = ON;

 renameした後にエラー発生して中断された。

Error: error in view view_all: no such table: main.X

 おそらく*で全列を取得しているから。修正によって列が追加・削除・変更されたので作り直す必要があるのだろう。

失敗3

 方法のとおり、rename後にdrop view, create viewしてみる。

pragma foreign_keys = OFF;
begin transaction;

create table new_X(
  A integer primary key,
  C text unique default NULL,
  D text check(0<=D and D<=100),
  E text not null,
  F text default (datetime('now','localtime')),
  G integer references Y(A)
);
insert into new_X(A, C, D, E, G) select cast(A as int), C, 0, 'E', NULL from X;

drop table X;
alter table new_X rename to X;
drop view view_all;
create view view_all as select * from X;

commit;
pragma foreign_keys = ON;

 やはりrename後にエラー。

Error: error in view view_all: no such table: main.X

 というか、commitしてないのになぜエラーになる?

失敗4

pragma foreign_keys = OFF;
begin transaction;

create table new_X(
  A integer primary key,
  C text unique default NULL,
  D text check(0<=D and D<=100),
  E text not null,
  F text default (datetime('now','localtime')),
  G integer references Y(A)
);
insert into new_X(A, C, D, E, G) select cast(A as int), C, 0, 'E', NULL from X;

drop view view_all;
drop table X;
create view view_all as select * from new_X;

alter table new_X rename to X;

commit;
pragma foreign_keys = ON;

 やはりrename後にエラー。

Error: error in view view_all: no such table: main.X

 というか、commitしてないのになぜエラーになる?

 new_Xがそのまま残っている。

.tables
Y            new_X        trriger_log  view_all   

 コマンドを一括コピペしたからエラー後にコミットしてしまったようだ。

 気持ち的にはエラー時点で中断されるつもりだった。が、1行ずつが個別のコマンドになっていると思われる。begincommitをメソッドのような単位だと思っていたら勘違いする。

所感

面倒すぎ

 とてつもなく面倒くさい。RDBMSってスキーマを変更するとデータ移行などがものすごく大変な印象がある。特に制約が絡んでくると頭までこんがらがってくる。その上SQLite3はalterコマンドが貧弱なせいで、今回のような作り直しが必要になる……。

さらに

 だが、なんと公式にはさらに面倒なことが書いてある。じつはSQLite3でスキーマ再定義しようとすると以下の3パターンに分けられる。

  1. alter add column: (以下2, 3以外)
  2. 今回のパターン: 列の削除・順序変更。UNIQUEPKの追加/削除。CHECK/FOREIGN KEY/NOT NULLの追加。型の変更
  3. 前回のパターン: CHECKFOREIGN KEYNOT NULLの削除。列のDEFAULT値の追加・削除・変更

 条件が複雑すぎ! 3は「ディスク上のコンテンツにまったく影響を与えない一部の変更」ということらしいが、よく意味がわからない。私にはこれらを適時適切に使い分けられる自信がない。そもそも条件を覚えられない。

 たのむから「どういう内容に変更するか?」という本質的な問題に集中させてくれ。「どうやって」でこんなに悩みたくない。

感想

 3前回パターンはsqlite_masterを変更する方法。これを確認すべきかと思ったが……もうやだ。やめる。使い分けられる自信がない。条件を覚えられない。

 2ができれば3の場合でもスキーマ変更できるから問題ない。3のほうがデータ移行せずに済むという大きなメリットはあるけど。

 あとさ、パッと見、トリガー、ビュー、インデックスって面倒なわりにできることが中途半端じゃない? これ便利なもんなの? 使いどころを考えるのも難しそうなんだが。

 あとさ、制約のせいで面倒になるなら、いっそ使わないほうがいいんじゃない? プログラム側でバリデーションしたほうがいいんでない? どうせ制約では簡単なことしかできないのだから。外せないのはPKくらいか?

対象環境

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

前回まで