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

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

成果物

• github

情報源

• https://www.sqlite.org/lang_altertable.html#otheralter

概要

　前回までで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行ずつが個別のコマンドになっていると思われる。begin〜commitをメソッドのような単位だと思っていたら勘違いする。

所感

面倒すぎ

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

さらに

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

1. alter add column: （以下2, 3以外）
2. 今回のパターン: 列の削除・順序変更。UNIQUE・PKの追加／削除。CHECK/FOREIGN KEY/NOT NULLの追加。型の変更
3. 前回のパターン: CHECK、FOREIGN KEY、NOT NULLの削除。列のDEFAULT値の追加・削除・変更

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

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

感想

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

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

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

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

対象環境

• 2019-08-26
• 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）制約
• SQL構文　alter（add column）sqlite_master変更しても反映されない