リレーショナルデータベースのテーブルでリストを扱う例（SQLite 3, R を使用）

#! 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

#!/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

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