データベースの物理構造

【概要】 SQLの主要な操作としてテーブル定義がある.テーブル定義はSQLのCREATE TABLE文を用いて行い,「create table path (id integer primary key autoincrement, ...)」のように,フィールド名,データ型,制約条件などを指定する.SQLite 3データベースファイルの物理構造を理解することも重要である.SQLite 3の物理構造は,データベースヘッダとデータページで構成され,通常データページのサイズは1024バイト(16進数で0x400)である.SQLiteデータベースの基本的な格納単位はレコードであり,テーブルの1行に相当する.レコードは複数のフィールド値で構成され,バイナリエディタを使用すると,path,val,created_atなどのフィールド値をバイト列として確認できる.

大学授業用に作成した資料を更新・改良して公開しています.これらは クリエイティブコモンズ 表示-非営利-継承 4.0 国際ライセンス(CC BY-NC-SA 4.0) で提供しており,事前の許可なく自由に利用できます.条件は著作者表示(BY),非営利目的のみ(NC),同一ライセンスでの再配布(SA)です.

演習で行うこと

Windows では,バイナリエディタのインストール

Windows 用のバイナリエディタとしては,Stirling,BZ,HxD などが有名である.

  1. バイナリエディタ BZ のファイルの入手
  2. ファイルの確認
  3. ファイルの解凍

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

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

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

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

    * Ubuntu での実行例(「mydb」を開く場合)

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

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

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

    要するに,/home/<ユーザ名>/SQLite 3の mydb を選ぶ. 

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

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

    SQL を用いて,R テーブルを定義し,一貫性制約を記述する.

    リレーショナル・スキーマ (relational schema): path(id, docid, path, val, created_at)
    
    1. path テーブルの定義

      次の SQL を入力し,「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

      create table path (
          id         integer primary key autoincrement NULL,
          docid      integer not null,
          path       text not null,
          val        text not null,
          created_at DATETIME not null );
      

      * 「SQL Editor」のウインドウには,SQL プログラムを書くことができる. In the 'SQL string' window, you can write down SQL program(s).

    2. コンソールの確認

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

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

    次のような path テーブルを作る.

    以下の手順で,SQL を用いて path テーブルへの行の挿入を行う (Insert rows into table 'path' using SQL)

    1. SQL プログラムの記述
      begin transaction;
      insert into path values( 1, 1, '/root/title', 'report A', datetime('now', 'localtime') );
      insert into path values( 2, 1, '/root/author', 'kaneko', datetime('now', 'localtime') );
      insert into path values( 3, 1, '/root/date', '2012/11/21', datetime('now', 'localtime') );
      commit;
      
    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」ウインドウの確認

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

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

    SQLite 3 のデータベースファイルの物理構造

    SQLite 3 のデータベースファイルは,レコードを単位とした物理構造になっている

    1. Sqliteman を終了しておくこと
    2. バイナリエディタの起動

      以下の手順で,データベースファイル mydb をバイナリエディタで開き, データベースファイルが,レコードを単位とした物理構造になっていることを確認しておく.

      * Ubuntu の場合 (GHex を起動する)

      「プログラミング」→ 「Hex エディタ」のように操作する

      * 端末を開いて, 「ghex」 のように実行しても良い.

      * ghex2 が無いときは,「sudo apt -y install ghex2」を実行して,ghex2 をインストールする.あるいは,類似の同機能のソフトウェア (okteta など) を試す.

      * Windows の場合 (Bz を開く)

    3. データベースファイルを開く

      * Ubuntu の場合 (GHex を使う場合)

      「ファイル」→「開く」

      ディレクトリを選ぶ. ここでは,データベースファイル mydb2 を置いているディレクトリである「/home/ubuntuuser」を選んでいる

      ファイルを選ぶ. ここでは,「mydb」を選んでいる (「mydb」をダブルクリック).

      * Windows の場合 (Bz)

    4. データベース・ファイルの中から R テーブルのレコードを探す

      * Ubuntu の場合 (GHex を使う場合)

      「編集」→ 「検索」

      検索文字列「root/」を指定して,「検索」をクリックする.

      末尾に「.」が自動で入る.これは気にしないこと.

    5. データベースの中身の確認

      バイナリエディタ GHex では,

      • 左側: ファイルの中身が16進数で
      • 右側: ファイルの中身がアルファベット,数字,英記号で

      で表示され,ファイルの中身を簡単に確認できる.

      データベース・ファイルのデータページの中には,レコードが並んでいることが確認できる.データページの中には未使用部分がある.

      There a sequence of records in data pages in database file.

      * データベースの構造

      • SQLite 3 では,データベースヘッダがある
      • SQLite 3 では,データページのサイズは 1024バイト (16進数で 400)
    6. path フィールドの値の確認
    7. val フィールドの値の確認
    8. created_at フィールドの値の確認

      created_at には,now を使って現在時刻を入れたので,値が違っているであろう.

      この演習では docid フィールドの値の確認は行わない.docid は整数データである.整数データはコード化されている.数値データのコード化体系はデータベース管理システムの種類によって違う. In this exercise, ignore the 'docid' field. The integer value is encoded.

    9. レコードの長さキー

      レコードヘッダには,レコードの長さとレコードのキー (key) が格納されている

      今回は

      • レコードの長さ: 2E, キーの値: 03
      • レコードの長さ: 2C, キーの値: 02
      • レコードの長さ: 2D, キーの値: 01

      「id integer primary key autoincrement NULL,」と定義したので,idがキーである.