テーブルの更新とトランザクション

【概要】 トランザクションは複数のデータベース操作を一つの処理として扱う機能である．BEGIN TRANSACTIONで開始され，これ以降の操作はCOMMITかROLLBACKまで一つの処理単位として扱われる．COMMITはトランザクション内のすべての操作を確定させ，データベースの変更を永続的に反映する．一方，ROLLBACKはトランザクション内のすべての操作を取り消し，データベースをトランザクション開始前の状態に戻す．この仕組みにより，データベース操作の信頼性と一貫性が保証される．トランザクション処理中の変更はジャーナルファイルに一時的に記録され，システム障害時の回復やROLLBACK時に活用される．

大学で使用した自作の資料等を，手直しの上公開している． クリエイティブ・コモンズ BY NC SA．

演習を行うために必要になる機能や文法

  • begin transaction, commit, ROLLBACK

    データベース更新を行うときは，最初に「begin transaction;」を実行する． データベース更新が終わったら「commit;」または「ROLLBACK;」を実行する．

  • INSERT 文

    SQL での INSERT 文は，表に行を挿入するためのものである．次の2種類に大別される．

    1. 値を指定して 1 行挿入 
          INSERT
    INTO    R(A1', A2', ..., Ap')
    VALUES    (a1', a2', ..., ap')

      これは，表 R(A1, A2, ..., An) に，行(..., a1', ..., a2', ..., ap', ...) を挿入する．A1', A2', ..., Ap' 以外の値は NULL（空値）になる．

      なお「R(A1', A2', ..., Ap')」ではなく，「R」とだけテーブル名を書く場合もある（次のように） 

          INSERT
    INTO    R
    VALUES    (a1, a2, ..., an)

      このときは，表 R(A1, A2, ..., An) に，行(a1, a2, ..., an) が挿入される(A1にa1が, A2にa2が，というように挿入される）

    2. あるSELECT文の評価結果を挿入 
          INSERT
    INTO    R(A1', A2', ..., Ap')
        SELECT B1, B2, ..., Bp
        FROM ＜探索表リスト＞
        WHERE ＜探索条件＞

      これは，SELECT文で得られた評価結果（表の形をしている）を，表R に挿入する．

  • DELETE 文

    DELETE 文は，条件に合致する行の削除を行う．一般形は「DELETE FROM <table-name> WHERE <expression>;」のようになる． 例えば，テーブルRから＜探索条件＞を満たす行を全て削除するときは次のように書く． 

        DELETE
    FROM    R
    WHERE ＜探索条件＞
  • UPDATE 文

    UPDATE 文は，条件に合致する行に関するデータの更新を行う．一般形は「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」のようになる．

    例えば，表R の中で，探索条件を満たす行について，指定した属性（複数可）の値を更新するときは次のように書く． 

        UPDATE R
    SET     A1 = 値式1
        A2 = 値式2
        ...
        Ap = 値式p
    WHERE    ＜探索条件＞
  • now

    現在の日時を取得する SQLite 3 の関数である．

    A SQLite 3 function to get the current datetime.

  • datetime

    日時のデータを「YYYY-MM-DD HH:MM:SS」形式の文字列に変換する SQLite 3 の関数である．

    A SQLite 3 function to convert a datetime data into the string like "YYYY-MM-DD HH:MM:SS".

    Sqliteman で既存のデータベースを開く

    すでに作成済みのデータベースを，下記の手順で開くことができる．

    以下の手順で，既存のデータベースファイルを開く．

    1. 「File」→ 「Open」
    2. データベースファイルを開く

      * Ubuntu での実行例（「SQLite/mydb」を開く場合）

      データベースファイル SQLite/mydb を選び， 「開く」をクリック

      * Windows での実行例（「C:\SQLite\mydb」を開く場合）

      データベースファイル C:\SQLite\mydb を選び， 「開く」をクリック

      要するに，/home/＜ユーザ名＞/SQLite 3の下の mydb を選ぶ．　

  • 「Tables」を展開すると，テーブルの一覧 (List of Tables) が表示されるので確認する

    SQL を用いたテーブル定義と一貫性制約の記述

    SQL を用いて，E テーブルを定義し，一貫性制約を記述する． 

    リレーショナル・スキーマ (relational schema); E(name, score, student_name)
    1. E テーブルの定義

      次の SQL を入力し，「Run SQL」のアイコンをクリック 

      create table E (
    name          text     not null,
    score         integer  not null check ( score >= 0 AND score <=100 ),
    student_name  text     not null,
    created_at    datetime not null,
    updated_at    datetime,
    unique (name, student_name) );
    2. コンソールの確認

      エラーメッセージが出ていないことを確認する．

    SQL を用いたテーブルへの行の挿入

    テーブルの 'created_at' 属性には，行を挿入する日時を記録する．

    以下の手順で，SQL を用いて E テーブルへの行の挿入を行う

    1. SQL プログラムの記述

      「insert into ...」は行の挿入である．ここには 5つの SQL 文を， 「begin transaction」と「commit」で囲む． 

      begin transaction;
insert into E values( 'Database',    80, 'KK', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    95, 'AA', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    80, 'LL', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 85, 'KK', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 75, 'LL', datetime('now', 'localtime'), NULL );
commit;
    2. 複数の SQL 文の一括実行

      複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．

    3. 「Script Output」ウインドウの確認

      エラーメッセージが出ていないことを確認する．

    SQL 問い合わせの発行と評価結果の確認

    SQL を用いた問い合わせの実行例を示す．

    テーブルの全ての行の表示 

    SELECT * FROM E;

    条件を満足する行のみの表示 

    SELECT * FROM E WHERE name = 'Database';

    SELECT score FROM E WHERE name = 'Database';

    Sqliteman を用いたデータのブラウズ

    SQL 内の文法エラー

    1. まずは，文法エラーを含むSQLプログラムの記述

      なお，「insert into E values( HOGEHOGE );」の「HOGEHOGE」は間違いである（あとで，間違いを含むプログラムでの振る舞いを見たいから，わざと間違えている）．(There is a mistake in SQL. what happen ?) 

      begin transaction;
insert into E values( HOGEHOGE );
commit;
    2. 複数の SQL 文の一括実行

      複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．

    3. 「insert into E values( HOGEHOGE );」の「HOGEHOGE」が間違っているのでエラーメッセージが出る．

      ここでは，処理を続行したいので「Ignore」を選ぶ． 処理を継続したので「begin transaction;」で開始したトランザクションも継続している．

    4. 処理が続行され「commit;」まで進む．ここで，トランザクションが終わる．
    5. 確認のため，テーブルの全ての行の表示．テーブル E に変化はない． 
      SELECT * FROM E;

    トランザクション開始後に，トランザクション開始できない

    1. 前の SQL プログラムをそのまま使う 
      begin transaction;
insert into E values( HOGEHOGE );
commit;
    2. もう１度，複数の SQL 文の一括実行
    3. 「insert into E values( HOGEHOGE );」の「HOGEHOGE」が間違っているのでエラーメッセージが出る．

      今度は，「Abort」を選ぶ．すると， 「insert into E values( HOGEHOGE );」の行以降がすべて無視されることになる． したがって，処理を中断したけれど「begin transaction;」で開始したトランザクションは継続している． （commit も ROLLBACK も行われていない状態である）

    4. ロールバック(ROLLBACK)したいので，末尾に「ROLLBACK」と書き加えて，「Run SQL」のアイコンをクリック 
      ROLLBACK;
    5. もしロールバック(ROLLBACK) せずに， もう１度，SQL文の実行を行ったとする． すると，「Can not start a transaction withing a transaction」 (トランザクションの中でトランザクションを開始できない)というエラーメッセージが出る．

      これは，１度トランザクションを開始したのに，その終了をせずに，再びトランザクションを開始しようとしているためである．決して，故障ではない．１度，commit あるいは ROLLBACK で，トランザクションを終了させること．

    SQL を用いた更新

    SQL を用いたデータの更新 (update)の実行例を示す．

    「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は，データの更新である． この SQL 文を 「begin transaction」と「commit」で囲む．("UPDATE ... SET ..." means database update).

    1. SQL プログラムの記述

      「UPDATE ... SET ...」は更新である．ここには 1つの SQL 文を書き， 「begin transaction」と「commit」で囲む． ("UPDATE ... SET ..." means database update. 

      begin transaction;
UPDATE E SET score=90, updated_at=datetime('now', 'localtime')
WHERE name = 'Database' AND student_name ='KK';
commit;
    2. 複数の SQL 文の一括実行

      複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．

    3. 「Script Output」ウインドウの確認

      エラーメッセージが出ていないことを確認する．

    4. E テーブル のブラウズ
    5. SQL プログラムの記述

      「UPDATE ... SET ...」は更新である．ここには 1つの SQL 文を書き， 「begin transaction」と「commit」で囲む． 

      begin transaction;
UPDATE E SET score=80, updated_at=datetime('now', 'localtime')
WHERE name = 'Programming' AND student_name ='LL';
commit;
    6. 複数の SQL 文の一括実行

      複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．

    7. 「Script Output」ウインドウの確認

      エラーメッセージが出ていないことを確認する．

    8. E テーブル のブラウズ

    SQL を用いた行の削除

    SQL を用いた行の削除 (delete row(s))の実行例を示す．

    「DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である． この SQL 文を 「begin transaction」と「commit」で囲む．("DELETE FROM ... WHERE ..." means deletion of rows).

    1. SQL プログラムの記述 
      begin transaction;
DELETE FROM E
WHERE name = 'Database' AND student_name ='AA';
commit;
    2. 複数の SQL 文の一括実行

      複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．

    3. 「Script Output」ウインドウの確認

      エラーメッセージが出ていないことを確認する．

    4. E テーブル のブラウズ

    ジャーナルファイルの確認

    1. SQL プログラムの記述

      今度は commit を書かない 

      begin transaction;
insert into E values( 'Database',    90, 'BB', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 95, 'BB', datetime('now', 'localtime'), NULL );
    2. 複数の SQL 文の一括実行

      複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．

    3. 「Script Output」ウインドウの確認

      エラーメッセージが出ていないことを確認する．

    4. ジャーナル・ファイルの確認

      * Linuxでの実行例

      まず端末を開く．デスクトップに「端末」アイコンがある場合には，それをクリック（下記）．ない場合にはメニューで「アクセサリ」→「端末」 と操作する．

      端末を開いて次のように操作する．「cd SQLite」は，SQLite 3 のデータベースファイルが置いてあるディレクトリに移動する操作なので，別のディレクトリにデータベースファイルを作った場合には，適切に読み替えること． 

      cd
cd SQLite
ls

      「mydb-journal」というファイルができている．これがジャーナルファイルである．

      * Windows での実行例

      C:\SQLite を開く．「mydb-journal」がジャーナル・ファイルである．

    5. Sqliteman で commit の発行 
      commit;
    6. 「Script Output」ウインドウの確認

      エラーメッセージが出ていないことを確認する．

    7. ジャーナル・ファイルの確認

      commit を発行した．データベースの修正は終わっている．そのためジャーナルファイルが消えている．

      * Windows での実行例

      C:\SQLite を開く．

    ROLLBACK の確認

    1. SQL プログラムの記述

      今度も commit を書かない 

      begin transaction;
insert into E values( 'Database',    95, 'CC', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 80, 'CC', datetime('now', 'localtime'), NULL );
    2. 複数の SQL 文の一括実行

      複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする． (Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

    3. 「Script Output」ウインドウの確認

      エラーメッセージが出ていないことを確認する．

    4. ジャーナル・ファイルの確認

      「mydb-journal」がジャーナル・ファイルである．

      * Windows での実行例

      C:\SQLite を開く．

    5. E テーブル のブラウズ

      「insert into E values( 'Database', 95, 'CC', datetime('now', 'localtime'), NULL );」， 「insert into E values( 'Programming', 80, 'CC', datetime('now', 'localtime'), NULL );」の結果がデータベースに反映されているように見える．

    6. Sqliteman で ROLLBACK の発行 (ROLLBACK)

      今度は ROLLBACK を発行する． 

      ROLLBACK;
    7. 「Script Output」ウインドウの確認

      エラーメッセージが出ていないことを確認する．

    8. データベース・ファイルとジャーナル・ファイルの確認

      実はデータベースファイルは一切修正されていない．

    9. E テーブル のブラウズ

    演習問題と解答例

    次の問いに答えよ．その後，下記の解答例を確認せよ．

    問い

    1. 次の PTABLE テーブルに関する問題 
      name    |  type   |  color
------------------------------
apple   |  fruit  |  red
apple   |  fruit  |  blue
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  yellow
      1. PTABLE を次のように更新する SQL を書きなさい 
        name    |  type   |  color
------------------------------
apple   |  fruit  |  red
apple   |  fruit  |  blue
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  blue
      2. さらに，PTABLE から行を削除し，次のテーブルに変更する SQL を書きなさい 
        name    |  type   |  color
------------------------------
apple   |  fruit  |  red
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  blue
    2. commit, ROLLBACK に関する次の問に解答せよ．
      1. SQLite を使い，下記の SQL を評価させる．「select * from GG;」の評価結果は何か？ 
            create table GG (
        id integer primary key,
        name text );
    begin transaction;
    insert into GG values ( 1, 'X' );
    commit;
    select * from GG;
      2. その後，SQLite を使い，下記の SQL を評価させる．「select * from GG;」の評価結果は何か？ 
            begin transaction;
    insert into GG values ( 2, 'Y' );
    ROLLBACK;
    select * from GG;

    解答例

    1. 問１ 
      1. UPDATE PTABLE SET color='blue' WHERE name='rose' AND type='flower' AND color='yellow';

      2. DELETE FROM PTABLE WHERE name='apple' AND type='fruit' AND color='blue';
    2. 問２

      ２問とも，評価結果は次の通りである． 

      id|name
1|X