データベースの物理構造 (Physical Structure of Relational Database)

SQL を用いたテーブル定義と一貫性制約の記述 (Table definition and integrity constraint specification using SQL)

SQL を用いて，R テーブルを定義し，一貫性制約を記述する． (Define 'R' table and specify integrity constrants of the table using SQL)

リレーショナル・スキーマ (relational schema): path(id, docid, path, val, created_at)

1. path テーブルの定義 (Define a table)

   次の 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. コンソールの確認 (Inspect console)

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

   

SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

次のような path テーブルを作る． (Construct table 'R')

以下の手順で，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 文の一括実行 (Run multiple SQL statements)

   複数の 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」ウインドウの確認 (Inspect "Script Output" window)

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

   

Sqliteman を用いたデータのブラウズ (Browse Data using Sqliteman)

• R テーブル

  まず，オブジェクト・ブラウザ (Object Browser) の中の「Tables」を展開 (Click 'Tables')

  

  次に，テーブル pathを選ぶ (Select table 'path')

  

  テーブル pathが表示される (table 'path' appears)

  

  ※ もし，データに間違いがあれば，このウインドウで修正できる (If you find any mistakes, you can modify the data using this window).

• 「System Catalogue」を展開し，「sqlite_master」をダブルクリックすると，データベース・スキーマ (database schema) が表示されるので確認する (Database schema appears by clicking 'sqlite_master')
  
• データベーススキーマの中のpath テーブルの行を確認する． (Examine the table 'path' in the database schema information)
  

  path テーブルのルート・ページ番号が分かる．この場合はたまたま「9」になっているが，「9」と違う値になっていても問題はない． (The root page number of table 'path'. In this figure, the number is '9').

  ルート・ページ番号は，データベース管理システムが自動的に決める番号である．

• Sqliteman を終了 (Exit the Sqliteman)

  このときデータベースが保存される (Database is saved automatically)

  

SQLite 3 のデータベースファイルの物理構造 (Physical Structure of SQLite Database File)

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

This figure is from the 'SQLite Database File Format' Web page. https://www.sqlite.org/fileformat.html

1. Sqliteman を終了しておくこと (Exit the Sqliteman)
2. バイナリエディタの起動 (Execute the binary editor)

   以下の手順で、データベースファイル mydb をバイナリエディタで開き， データベースファイルが，レコードを単位とした物理構造になっていることを確認しておく． (The physical structure of relational database is based on the record).

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

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

   ※ 端末を開いて， 「ghex」 のように実行しても良い．

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

   ■ Windows の場合 (Bz を開く)

3. データベースファイルを開く (Open the database file)

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

   「ファイル」→「開く」

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

   ファイルを選ぶ． ここでは，「mydb」を選んでいる （「mydb」をダブルクリック）．

   ■ Windows の場合 (Bz)

4. データベース・ファイルの中から R テーブルのレコードを探す (Find the table 'records' in a database file manually)

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

   「編集」→ 「検索」

   検索文字列「root/」を指定して、「検索」をクリック

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

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

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

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

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

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

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

   

   ※ データベースの構造

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

   created_at には，now を使って現在時刻を入れたので，値が違っているでしょう．

   

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

9. レコードの長さとキー (Key and Record Lenght)

   レコードヘッダには，レコードの長さとレコードのキー (key) が格納されている (Record header includes record length and key value).

   今回は

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

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