テーブルそのものの操作方法（テーブル名変更・列の増減など）
一般的なデータベースでは、テーブルの列を増やしたりテーブル名を変更する時にはALTER TABLE文を使います。
ALTER TABLE table ADD ( new_column INT(10) )
ALTER TABLE文はサブコマンドに次のようなものがあります。
- ADD
- 列の追加
- MODIFY
- 列の名前やデータ長などの変更
- DROP
- 列の削除
- RENAME
- テーブル名の変更
しかしSQLiteのALTER TABLE文のサポートは限定的です。
ALTER TABLE
sql-statement ::= ALTER TABLE [database-name .] table-name alteration
alteration ::= RENAME TO new-table-name
alteration ::= ADD [COLUMN] column-def
定義を見ての通り、テーブル名の変更とカラムの追加しかできません。なによりバージョン3.1.3での実装のようです。
これ以外の操作や、もっと古いバージョンではどのようにしたらよいのでしょうか。
SQLiteでの代替手段は？
FAQに載っていました。「一時テーブル作成→データの待避→対象テーブルの削除→再作成→データを戻す」という方法を用いるようです。
For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:
（例えば、列名"a", "b", "c"を持ったテーブル"t1"の、列"c"を削除したいとする。手順は次の通り。）BEGIN TRANSACTION; -- 後述 CREATE TEMPORARY TABLE t1_backup(a,b); -- 一時テーブルを作成(1) INSERT INTO t1_backup SELECT a,b FROM t1; -- データの待避 DROP TABLE t1; -- 元のテーブルを消す CREATE TABLE t1(a,b); -- 列定義やテーブル名を新しくしたテーブルを作り直す(2) INSERT INTO t1 SELECT a,b FROM t1_backup; -- 待避したデータを書き戻す DROP TABLE t1_backup; -- 一時テーブルの削除(3) COMMIT;
(1)のTEMPORARY TABLE（一時テーブル）は次のような性質を持つテーブルです。
If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible within that same database connection and is automatically deleted when the database connection is closed. Any indices created on a temporary table are also temporary. Temporary tables and indices are stored in a separate file distinct from the main database file.
（"CREATE"と"TABLE"の間に"TEMP"や"TEMPORARY"キーワードがあるなら、同じデータベース接続にだけ見えて、そのデータベース接続が閉じた時に自動的に消去されるテーブルを作成する。一時テーブルに作られたインデックスもまた一時的である。一時テーブルとインデックスはメインデータベースファイルとは別のファイルに分けられて記録される。）
(3)でこのテーブルを消去していますが、daemon動作のようなDB接続のセッションが長くなる場合だけでよいと思います。例えばCGIのようにセッションが短い場合には、すぐに自動消去が起こるため、不要でしょう。
(2)で新しいテーブルを作成しています。ここでテーブル構造を修正します。元のt1は(a,b,c)だったので、cがなくなっています。
なおSQLite2系の場合、ATTACH DATABASEで接続した追加のデータベースのテーブルを作成・削除することができません。SQLite3(3.0以降)なら可能のようです。
You can read from and write to an attached database and you can modify the schema of the attached database. This is a new feature of SQLite version 3.0. In SQLite 2.8, schema changes to attached databases were not allowed.
（接続したデータベースの読み書きとスキーマ（テーブル構造）の変更が可能です。これはSQLite 3.0の新要素で、SQLite 2.8では接続したデータベースのスキーマの変更は許されていません。）
SQLite Query Language: ATTACH DATABASE
ただし一時テーブルの作成・削除は2系でも可能とのこと。通常のテーブルと異なるスキーマに記録されているためでしょう。
ではSQLiteではALTER TABLE文を使うべきではないのか？
シンプルに割り切ることはできません。ALTER TABLE文にも利点があります。
TEMPORARY TABLEの特性は自動的に削除されるというだけなので、そのセッションにおいては通常のテーブルと違いがありません*1。ですので全行コピーを二度も行うことになり、どんなにSQLiteのINSERT処理が優秀だったとしても処理時間はO(2n)*2となります。
一方でALTER TABLE文は説明によると、テーブルの行数に依らないそうです。
The execution time of the ALTER TABLE command is independent of the amount of data in the table. The ALTER TABLE command runs as quickly on a table with 10 million rows as it does on a table with 1 row.
（ALTER TABLEコマンドの実行時間はテーブルのデータ数から独立している。ALTER TABLEコマンドは1000万件(10M件)の行を持つテーブルでも、1行しかないテーブルと同じぐらいの早さで処理をする。）
ALTER TABLE文が使える状況なら、積極的に使わないと損ですね。環境や処理内容に応じて使い分けましょう。
ちょっと寄り道：BEGIN TRANSACTION
まとまったデータ操作を行う場合には必ずBEGIN TRANSACTION 〜 COMMITを忘れないようにしましょう。一部（SELECT文など）を除いた全ての文の実行にはトランザクションに入っていることが必要ですが、SQLiteではトランザクションに入っていない場合でも自動的にBEGIN TRANSACTION 〜 COMMITを付け加えます。
例を挙げます。自動的な（暗黙の）トランザクションを利用すると、文を実行するたびにトランザクションが処理されます。
-- トランザクション処理は2回 CREATE TEMPORARY TABLE t1_backup(a,b); INSERT INTO t1_backup SELECT a,b FROM t1; /* 実際は次のように処理される BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(a,b); COMMIT; BEGIN TRANSACTION; INSERT INTO t1_backup SELECT a,b FROM t1; COMMIT; */
これに対してきちんとトランザクションを明示すると、たった一回で済みます。
-- トランザクション処理は1回 BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(a,b); INSERT INTO t1_backup SELECT a,b FROM t1; COMMIT;
ちょっとしたINSERTやUPDATEには自動的なトランザクションが便利なのですが、まとまった操作を行う場合にはこれが大きなオーバーヘッドになってしまいます。忘れないようにしましょう。
