トップページ情報工学を学ぶisql コマンドライン・インタフェースから Virtuoso を使ってみる

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

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

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

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

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

[image]

ヘルプの表示

help;」で,ヘルプが表示されます.

[image]

isql の終了

exit;」で終了.

[image]

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

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

リレーショナル・スキーマ (relational schema): order_records(id, year, month, day, customer_name, product_name,  unit_price, qty)
  1. 次の 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 ) );
    

    [image]

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

order_records テーブルを作る.

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

  1. 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形式.

    [image]

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

    • set autocommit off;
    • commit work;
    • rollback work;
  2. テーブルの確認
    SELECT * FROM order_records; 
    

    [image]

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」に違反.

[image]

非空制約 (not null)

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

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

[image]

その他の一貫性制約

一貫性制約に違反する例

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

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

[image]

一貫性制約に違反する例

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

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

[image]

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

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

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

SELECT * FROM order_records;

[image]

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

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

[image]

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

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

[image]
SELECT * FROM order_records WHERE unit_price > 2;

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

[image]

8. SQL を用いた更新

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

  1. SQL の実行

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

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

    [image]
  2. order_records テーブルの中身を確認

    SELECT * FROM order_records; 
    

    [image]

SQL を用いた行の削除

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

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

  1. SQL の実行

    DELETE FROM order_records
    WHERE id = 2;
    

    [image]
  2. order_records テーブルの中身を確認

    SELECT * FROM order_records; 
    

    [image]

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

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

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

[image]

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;

[image]

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

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

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

[image]

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

テーブル名の一覧

tables; 

[image]

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

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

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

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 にマッピングし、次のような自己結合を行う。 その評価結果は、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
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;

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

◆ 二次索引

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

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