このページでは,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 ) );
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 の関数.
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;
「
「DELETE FROM ... WHERE ...」は行の削除.
UPDATE order_records SET qty=12, updated_at=now()
WHERE id = 1;
DELETE FROM order_records
WHERE id = 2;
インストールするには,端末で,次のコマンドを実行する.
sudo apt -y install virtuoso-opensource
Virtuoso の使用法は http://docs.openlinksw.com/virtuoso/index.html
isql のドキュメント: http://docs.openlinksw.com/virtuoso/invokingisql/
iqsl-vt
「help;」で,ヘルプが表示されます.
「exit;」で終了.
SQL を用いて,order_records テーブルを定義し,一貫性制約を記述する.
リレーショナル・スキーマ (relational schema): order_records(id, year, month, day, customer_name, product_name, unit_price, qty)
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 ) );
order_records テーブルを作る.
以下の手順で,SQL を用いてorder records テーブルへの行の挿入を行う
「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形式.
トランザクション機能を使いたいときは, 以下のコマンドなどを活用する.
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() );
ここでは,一貫性制約に違反するような更新を試みる.データベース管理システムソフトウエアが一貫性を維持するので, 一貫性制約に違反するような更新はできない.
insert into order_records values( 3, 2022, 10, 30, 'kaneko', 'banana', 10, 3, now(), NULL );
※ すでに属性 id には 3 という値がある. 主キー制約「PRIMARY KEY」に違反.
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 ) );」に違反
ここでは,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';
SQL を用いたデータの更新 (update)の実行例を示す. 「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は,データの更新である.
「UPDATE ... SET ...」は更新.
UPDATE order_records SET qty=12, updated_at=now() WHERE id = 1;
SELECT * FROM order_records;
SQL を用いた行の削除 (delete row(s))の実行例を示す.
「DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である.
DELETE FROM order_records WHERE id = 2;
SELECT * FROM order_records;
端末で、次のように実行する
echo "SELECT * FROM order_records;" | isql-vt > /tmp/hoge
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 を実行するには「load」を使う.
echo "select * from order_records;" > /tmp/hoge.query isql-vt load '/tmp/hoge.query'; exit;
tables;
select * from tables; select * from columns;
◆ テストデータ生成用 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 "------------------------"