トップページ -> 教材 -> リレーショナルデータベースの基礎 -> リレーショナルデータベースのテーブルでリストを扱う例
[サイトマップへ], [サイト内検索へ],

リレーショナルデータベースのテーブルでリストを扱う例

サイト構成 連絡先,業績など 実践知識 データの扱い コンピュータ 教材 サポートページ

リスト L = (e1, e2, ..., en) を、リレーショナルデータベースのテーブルに {(1, e1), (2, e2), ..., (n, en)} のようにマッピングすることを考える. このとき、リレーションスキーマはR(要素番号, 要素値)である. 複数のリスト {L1, L2, ..., Lk} をマッピングするときは、 リレーショナルデータベースのテーブルに、リスト番号の属性を含める.そのリレーションスキーマは R(リスト番号, 要素番号, 要素値) のようになる


◆ テストデータ生成用 Ruby プログラム

CSV 形式のテストデータを標準出力に出力する

◆ Ruby プログラム

#! ruby -Ks
# coding: windows-31j
# usage: ruby hoge.rb 10 5
LEN = 8
c = 0
TOTAL_LIST_NUM=ARGV[0].to_i
LIST_LEN=ARGV[1].to_i

puts "# id, list_num, item_num, x, y, price, name"

for i in 1..TOTAL_LIST_NUM do 
  for j in 1..LIST_LEN do 
    id = c
    list_num = i
    item_num = j
    x = 100 * rand 
    y = 100 * rand 
    price = ( 1000 * rand ).ceil
    name = (("a".."z").to_a + ("A".."Z").to_a + (0..9).to_a).shuffle[0..(LEN-1)].join

    printf("%d, %d, %d, %f, %f, %d, %s\n", id, list_num, item_num, x, y, price, name)

    c = c + 1
  end
end


テストデータベース生成

上の Ruby プログラムを hoge.rb のようなファイル名で保存し、次のコマンドを実行する

◆ bash プログラム

#!/bin/bash
rm -f /tmp/1.csv
ruby /tmp/hoge.rb 10 5 | fgrep -v '#' > /tmp/1.csv

rm -f /tmp/1.$$.sql
cat >/tmp/1.$$.sql << SQL
create table dat (
  id       integer primary key not null, 
  list_num integer, 
  item_num integer, 
  x        real, 
  y        real, 
  price    integer, 
  name     text ); 
.mode csv
.import /tmp/1.csv dat
.exit
SQL

rm -f /tmp/1.db
cat /tmp/1.$$.sql | sqlite3 /tmp/1.db

確認表示

sqliteman /tmp/1.db


◆ エッジリストの生成

ノードリストの集合 {L1, L2, ..., Lk} では、各集合 Li の要素はノードである。

ノードリストの集合をリレーショナルデータベースのテーブル dat にマッピングし、次のような自己結合を行う。 その評価結果は、1行を1つのエッジ(2つのノードをつなぐエッジ) とするようなテーブルになる

エッジリストの生成プログラム

select A.id, A.list_num, A.item_num, B.id, B.list_num, B.item_num from dat A, dat B where A.item_num + 1 = B.item_num AND A.list_num = B.list_num;

エッジリストの生成プログラムの実行結果例


テストデータベース生成と性能評価

安心してエッジリストの生成を行いたいので、性能を評価しておく。そのために、次のプログラムを実行する.

# define table

rm -f /tmp/1.$$.sql
cat >/tmp/1.$$.sql << SQL
create table dat (
  id       integer primary key not null, 
  list_num integer, 
  item_num integer, 
  x        number, 
  y        number, 
  price    integer, 
  name     text ); 
.exit
SQL

cd /tmp
rm -f /tmp/500000.db
sqlite3 /tmp/500000.db < /tmp/1.$$.sql
cd /tmp
rm -f /tmp/1000000.db
sqlite3 /tmp/1000000.db < /tmp/1.$$.sql
cd /tmp
rm -f /tmp/2000000.db
sqlite3 /tmp/2000000.db < /tmp/1.$$.sql
cd /tmp
rm -f /tmp/5000000.db
sqlite3 /tmp/5000000.db < /tmp/1.$$.sql

# populate table
cd /tmp
rm -f /tmp/500000.csv
ruby /tmp/hoge.rb 500000 5 | fgrep -v '#' > /tmp/500000.csv
echo ".mode csv" > /tmp/500000.query
echo ".import /tmp/500000.csv dat" >> /tmp/500000.query
#
rm -f /tmp/1000000.csv
ruby /tmp/hoge.rb 1000000 5 | fgrep -v '#' > /tmp/1000000.csv
echo ".mode csv" > /tmp/1000000.query
echo ".import /tmp/1000000.csv dat" >> /tmp/1000000.query
#
rm -f /tmp/2000000.csv
ruby /tmp/hoge.rb 2000000 5 | fgrep -v '#' > /tmp/2000000.csv
echo ".mode csv" > /tmp/2000000.query
echo ".import /tmp/2000000.csv dat" >> /tmp/2000000.query
#
rm -f /tmp/5000000.csv
ruby /tmp/hoge.rb 5000000 5 | fgrep -v '#' > /tmp/5000000.csv
echo ".mode csv" > /tmp/5000000.query
echo ".import /tmp/5000000.csv dat" >> /tmp/5000000.query
#
sqlite3 /tmp/500000.db < /tmp/500000.query
sqlite3 /tmp/1000000.db < /tmp/1000000.query
sqlite3 /tmp/2000000.db < /tmp/2000000.query
sqlite3 /tmp/5000000.db < /tmp/5000000.query

# run tests (repeat the same query)
echo "drop table T;" > /tmp/run.query 
echo "create table T as select A.id, A.list_num, A.item_num, B.id, B.list_num, B.item_num from dat A, dat B where A.item_num + 1 = B.item_num AND A.list_num = B.list_num;" >> /tmp/run.query 
echo "select count(*) from T;" >> /tmp/run.query 
#
echo "------------------------" 
echo 500000.db
date
sqlite3 /tmp/500000.db < /tmp/run.query
date
echo "------------------------" 
echo 1000000.db
date
sqlite3 /tmp/1000000.db < /tmp/run.query
date
echo "------------------------" 
echo 2000000.db
date
sqlite3 /tmp/2000000.db < /tmp/run.query
date
echo "------------------------" 
echo 5000000.db
date
sqlite3 /tmp/5000000.db < /tmp/run.query
date
echo "------------------------" 

◆ 実行結果の例(テストデータベース生成部分)

◆ 実行結果の例(性能評価部分)

◆ 二次索引

引き続き、次を実行してみる

echo "create index idx001 on dat(item_num, list_num);" > /tmp/idx.query
sqlite3 /tmp/500000.db < /tmp/idx.query
sqlite3 /tmp/1000000.db < /tmp/idx.query
sqlite3 /tmp/2000000.db < /tmp/idx.query
sqlite3 /tmp/5000000.db < /tmp/idx.query

echo "------------------------" 
echo 500000.db
date
sqlite3 /tmp/500000.db < /tmp/run.query
date
echo "------------------------" 
echo 1000000.db
date
sqlite3 /tmp/1000000.db < /tmp/run.query
date
echo "------------------------" 
echo 2000000.db
date
sqlite3 /tmp/2000000.db < /tmp/run.query
date
echo "------------------------" 
echo 5000000.db
date
sqlite3 /tmp/5000000.db < /tmp/run.query
date
echo "------------------------"