トップページ -> コンピュータ実習 -> リレーショナルデータベースの基礎 -> リレーショナルデータベースのテーブルでリストを扱う例(Virtuoso を使用)
[サイトマップへ]  

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

サイト構成 連絡先,業績など コンピュータ実習 データの扱い コンピュータ設定 教材(公開) サポートページ

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

テストデータ生成用 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 のようなファイル名で保存し、次のコマンドを実行する

テストデータベース生成(試行)

rm -f /tmp/1.csv
ruby /tmp/hoge.rb 10 5 | fgrep -v '#' > /tmp/1.csv
rm -f /tmp/1.db
/usr/local/virtuoso-opensource/bin/isql
create table dat (
  id       integer primary key not null, 
  list_num integer, 
  item_num integer, 
  x        real, 
  y        real, 
  price    integer, 
  name     varchar ); 
csv_load( file_open('/tmp/1.csv'), 0, null, 'dat');
exit;

確認表示

echo "select * from dat;" | /usr/local/virtuoso-opensource/bin/isql

エッジリストの生成

ノードリストの集合 {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
echo "create table dat500000 (" > /tmp/def.query
echo "  id       integer primary key not null, " >> /tmp/def.query
echo "  list_num integer, " >> /tmp/def.query
echo "  item_num integer, " >> /tmp/def.query
echo "  x        real, " >> /tmp/def.query
echo "  y        real, " >> /tmp/def.query
echo "  price    integer, " >> /tmp/def.query
echo "  name     varchar ); " >> /tmp/def.query
echo "create table dat1000000 (" >> /tmp/def.query
echo "  id       integer primary key not null, " >> /tmp/def.query
echo "  list_num integer, " >> /tmp/def.query
echo "  item_num integer, " >> /tmp/def.query
echo "  x        real, " >> /tmp/def.query
echo "  y        real, " >> /tmp/def.query
echo "  price    integer, " >> /tmp/def.query
echo "  name     varchar ); " >> /tmp/def.query
echo "create table dat2000000 (" >> /tmp/def.query
echo "  id       integer primary key not null, " >> /tmp/def.query
echo "  list_num integer, " >> /tmp/def.query
echo "  item_num integer, " >> /tmp/def.query
echo "  x        real, " >> /tmp/def.query
echo "  y        real, " >> /tmp/def.query
echo "  price    integer, " >> /tmp/def.query
echo "  name     varchar ); " >> /tmp/def.query
echo "create table dat5000000 (" >> /tmp/def.query
echo "  id       integer primary key not null, " >> /tmp/def.query
echo "  list_num integer, " >> /tmp/def.query
echo "  item_num integer, " >> /tmp/def.query
echo "  x        real, " >> /tmp/def.query
echo "  y        real, " >> /tmp/def.query
echo "  price    integer, " >> /tmp/def.query
echo "  name     varchar ); " >> /tmp/def.query
/usr/local/virtuoso-opensource/bin/isql
load '/tmp/def.query'; 
exit; 

# populate table
cd /tmp
rm -f /tmp/500000.csv
ruby /tmp/hoge.rb 500000 5 | fgrep -v '#' > /tmp/500000.csv
#
rm -f /tmp/1000000.csv
ruby /tmp/hoge.rb 1000000 5 | fgrep -v '#' > /tmp/1000000.csv
#
rm -f /tmp/2000000.csv
ruby /tmp/hoge.rb 2000000 5 | fgrep -v '#' > /tmp/2000000.csv
#
rm -f /tmp/5000000.csv
ruby /tmp/hoge.rb 5000000 5 | fgrep -v '#' > /tmp/5000000.csv
#
/usr/local/virtuoso-opensource/bin/isql
csv_load( file_open('/tmp/500000.csv'), 0, null, 'dat500000');
csv_load( file_open('/tmp/1000000.csv'), 0, null, 'dat1000000');
csv_load( file_open('/tmp/2000000.csv'), 0, null, 'dat2000000');
csv_load( file_open('/tmp/5000000.csv'), 0, null, 'dat5000000');

# run tests (repeat the same query)
drop table T;
select now(); 
create table T as select A.id, A.list_num, A.item_num, B.id, B.list_num, B.item_num from dat500000 A, dat500000 B where A.item_num + 1 = B.item_num AND A.list_num = B.list_num;
select count(*) from T;
select now(); 

exit;

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

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

◆ 二次索引

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

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 "------------------------"