サイトの全体構成

isql コマンドライン・インタフェースから Virtuoso を使ってみる

このページでは，isql のコマンドライン・インタフェース を使って下記の操作を行う手順を，説明する．（コマンドの簡単な説明と，コマンドを実行させるための操作手順の両方を説明する）．

この Web ページで行うこと
前準備
isql の起動
SQL を用いたテーブル定義と一貫性制約の記述
SQL を用いたテーブルへの行の挿入
一貫性制約に違反する更新ができないことの確認
SQL 問い合わせの発行と評価結果の確認
SQL を用いた更新
SQL を用いた行の削除
テーブルのエクスポート
.explain による SQL 実行計画の表示
ファイルに入った SQL の実行
データベーススキーマの表示
リストを扱う例

1. この Web ページで行うこと

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

リレーショナル・スキーマ (relational schema): order_records(id, year, month, day, customer_name, product_name, unit_price, qty)

SQL 文:

create table order_records ( id INTEGER PRIMARY KEY not null, year INTEGER not null CHECK ( year > 2008 ), month INTEGER not null CHECK ( month >= 1 AND month <= 12 ), day INTEGER not null CHECK ( day >= 1 AND day <= 31 ), customer_name VARCHAR not null, product_name VARCHAR not null, unit_price REAL not null CHECK ( unit_price > 0 ), qty INTEGER not null DEFAULT 1 CHECK ( qty > 0 ), created_at DATETIME not null, updated_at DATETIME, CHECK ( ( unit_price * qty ) < 200000 ) );

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

insert into order_records values( 1, 2022, 10, 26, 'kaneko', 'orange A', 1.2, 10, now(), NULL ); insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2022, 10, 26, 'miyamoto', 'Apple M', 2.5, 2, now() ); insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2022, 10, 27, 'kaneko', 'orange B', 1.2, 8, now() ); insert into order_records (year, month, day, customer_name, product_name, unit_price, created_at) values( 2022, 10, 28, 'miyamoto', 'Apple L', 3, now() );

「now()」は，現在の日時を取得する Virtuoso の関数.

一貫性制約に違反する更新ができないことの確認

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

SELECT * FROM order_records; SELECT * FROM order_records WHERE day = 26; SELECT * FROM order_records WHERE customer_name = 'kaneko'; SELECT * FROM order_records WHERE unit_price > 2;

SQL を用いた更新

「UPDATE ... SET ...」は更新．

UPDATE order_records SET qty=12, updated_at=now() WHERE id = 1;

SQL を用いた行の削除
「DELETE FROM ... WHERE ...」は行の削除．
DELETE FROM order_records WHERE id = 2;
テーブルの CSV ファイル形式でのエクスポート
.explain による SQL 実行計画の表示
ファイルに入った SQL の実行
データベーススキーマの表示
テーブルのテキストファイル形式でのダンプとリストア

2. 前準備

Virtuoso のインストール（Ubuntu 上）

インストールするには，端末で，次のコマンドを実行する．

sudo apt -y install virtuoso-opensource

Virtuoso の使用法は http://docs.openlinksw.com/virtuoso/index.html

3. isql の起動

isql の起動

isql のドキュメント: http://docs.openlinksw.com/virtuoso/invokingisql/

iqsl-vt

ヘルプの表示

「help;」で，ヘルプが表示されます．

isql の終了

「exit;」で終了．

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

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

リレーショナル・スキーマ (relational schema): order_records(id, year, month, day, customer_name, product_name,  unit_price, qty)

次の SQL を入力

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

order_records テーブルを作る．

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

SQL の実行

「insert into ...」は行の挿入．

insert into order_records values( 1, 2022, 10, 26,  'kaneko',   'orange A', 1.2, 10, now(), NULL );
insert into order_records values( 2, 2022, 10, 26,  'miyamoto', 'Apple M',  2.5, 2,  now(), NULL );
insert into order_records values( 3, 2022, 10, 27,  'kaneko',   'orange B', 1.2, 8,  now(), NULL );
insert into order_records values( 4, 2022, 10, 28,  'miyamoto', 'Apple L',  3,   1,  now(), NULL );

now() は現在日時の取得．DATETIME型は、YYYY-MM-DD HH:MM:SS形式．

トランザクション機能を使いたいときは, 以下のコマンドなどを活用する．

set autocommit off;
commit work;
rollback work;

テーブルの確認
```
SELECT * FROM order_records; 
```

insert into には 2つの方法がある．

■ 属性の値を，テーブル定義の順に全て並べる

insert into order_records values( 1, 2022, 10, 26,  'kaneko', 'orange A', 1.2, 10, now(), NULL );

■ 属性の値の並び方を，属性名を使って明示的に指定する

このとき，属性値を省略すると，テーブル定義のときに指定されたデフォルト値が使われる

insert into order_records (id, year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2, 2022, 10, 26,  'miyamoto', 'Apple M',  2.5, 2, now() );

6. 一貫性制約に違反する更新ができないことの確認

ここでは，一貫性制約に違反するような更新を試みる．データベース管理システムソフトウエアが一貫性を維持するので，一貫性制約に違反するような更新はできない．

主キー制約 (PRIMARY KEY)

insert into order_records values( 3, 2022, 10, 30, 'kaneko', 'banana', 10, 3, now(), NULL );

※ すでに属性 id には 3 という値がある．主キー制約「PRIMARY KEY」に違反．

非空制約 (not null)

insert into order_records values( 3, 2022, 10, 30,  NULL, 'melon', 10, 3, now(), NULL );

※ 非空制約「not null」．属性 customer_name には NULL を入れることができない．

その他の一貫性制約

一貫性制約に違反する例

insert into order_records values( 6, 1009, 10, 30,  'kaneko', 'melon', 10, 3, now(), NULL );

※ 制約「CHECK ( year > 2008 )」に違反

一貫性制約に違反する例

insert into order_records values( 7, 2022, 10, 31,  'kaneko', 'strawberry', 4.6, 100000, now(), NULL );

※ 制約「CHECK ( ( unit_price * qty ) < 200000 ) );」に違反

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

ここでは，SQL を用いた問い合わせの実行例を示す． SQL 問い合わせの詳細については，別の Web ページで説明する．ここでは，テーブルの中身を確認して欲しい．

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

SELECT * FROM order_records;

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

SELECT * FROM order_records WHERE product_name = 'orange A';

SELECT * FROM order_records WHERE product_name LIKE 'orange%';

LIKE は文字列のパターンマッチ

SELECT * FROM order_records WHERE unit_price > 2;

SELECT * FROM order_records WHERE qty > 9 AND customer_name = 'kaneko';

8. SQL を用いた更新

SQL を用いたデータの更新 (update)の実行例を示す．「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は，データの更新である．

SQL の実行

「UPDATE ... SET ...」は更新．

UPDATE order_records SET qty=12, updated_at=now()
WHERE id = 1;

order_records テーブルの中身を確認
```
SELECT * FROM order_records; 
```

SQL を用いた行の削除

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

「DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である．

SQL の実行

DELETE FROM order_records
WHERE id = 2;

order_records テーブルの中身を確認
```
SELECT * FROM order_records; 
```

13. テーブルのエクスポート

端末で、次のように実行する

echo "SELECT * FROM order_records;" | isql-vt > /tmp/hoge

14. explain による SQL 実行計画の表示

SQL 文の前に「set explain on;」を実行すると，SQL は評価されずに，実行計画が表示されます．「set explain off;」により元に戻ります．

isql-vt
set explain on; 
select * from order_records;
set explain off;
select * from order_records;
exit;

ファイルに入った SQL の実行

ファイルに入った SQL を実行するには「load」を使う．

echo "select * from order_records;" > /tmp/hoge.query
isql-vt
load '/tmp/hoge.query';
exit;

データベーススキーマの表示

テーブル名の一覧

tables;

テーブル情報、属性情報の表示

select * from tables;
select * from columns;

リストを扱う例

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

◆ テストデータベース生成（試行）

データベースファイル名: /tmp/1.db
テーブル名: dat
リスト数: 10
各リストの要素数: 5

rm -f /tmp/1.csv
ruby /tmp/hoge.rb 10 5 | fgrep -v '#' > /tmp/1.csv
rm -f /tmp/1.db
isql-vt
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;" | isql-vt

◆ エッジリストの生成

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

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

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

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/500000.db, /tmp/1000000.db, /tmp/2000000.db, /tmp/5000000.db
テーブル名: dat
リスト数: 500000, 1000000, 2000000, 5000000
各リストの要素数: 5

# 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
isql-vt
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
#
isql-vt
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;

◆ 実行結果の例（性能評価部分）

要素数 500000: 約9秒
要素数 1000000: 約19秒
要素数 2000000: 約39秒
要素数 5000000: 約98秒

◆ 二次索引

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

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

要素数 500000: 約7秒
要素数 1000000: 約10秒
要素数 2000000: 約22秒
要素数 5000000: 約56秒

本サイトは金子邦彦研究室のWebページである．

資料等の公開では，原則，「クリエイティブコモンズ BY NC SA」として公開するようにしている． PDFファイル，パワーポイントファイルなどには，「クリエイティブコモンズ BY NC SA」を明記するとともに，ロゴを記載するようにしている（作業が間に合っていない分もあるのでご容赦ください）．

公開している資料をご利用になる場合の，再配布の条件，剽窃の防止などについて，別ページ »で説明再配布や資料改変の際には，そのページをご確認ください．

サイトマップは，サイトマップのページをご覧下さい．本サイト内の検索は，サイト内検索のページをご利用下さい．

問い合わせ先：金子邦彦（かねこくにひこ） [image]