test_word2item.bash

search engine ala AlchemyDB

. ./bash_functions.sh

$CLI DROP TABLE item
$CLI CREATE TABLE item "(id INT, name TEXT)"

$CLI DROP TABLE word2item
$CLI CREATE TABLE word2item "(id INT, word_id INT, type INT, seller_id INT, pub_type INT, tag_id INT, item_id INT)"

$CLI CREATE INDEX ind_w2i_w ON word2item "(word_id)"
$CLI CREATE INDEX ind_w2i_i ON word2item "(item_id)"
C=200
REQ=1000000
if [ "$1" == "-P" ]; then
  amem_pre;
  time taskset -c 1 ./gen-benchmark -q -m "10000,5,100000,5,50,5000" -c $C -n $REQ -s -A OK -Q INSERT INTO word2item VALUES "(00000000000001,00000000000001,00000000000001,00000000000001,00000000000001,00000000000001,00000000000001)";
  amem_post
fi

$CLI INSERT INTO item VALUES "(1,'IPOD NANO BLACK')"
$CLI INSERT INTO item VALUES "(2,'IPOD NANO WHITE')"
$CLI INSERT INTO item VALUES "(3,'IPOD CLASSIC BLACK')"
$CLI INSERT INTO item VALUES "(4,'IPOD CLASSIC WHITE')"

# IPOD    (word_id =1) -> "IPOD NANO BLACK"    item_id(1)
$CLI INSERT INTO word2item VALUES "(1,1,1,1,1,1,1)"
# IPOD    (word_id =1) -> "IPOD NANO WHITE"    item_id(2)
$CLI INSERT INTO word2item VALUES "(2,1,1,1,1,1,2)"

# IPOD    (word_id =1) -> "IPOD CLASSIC BLACK" item_id(3)
$CLI INSERT INTO word2item VALUES "(3,1,1,1,1,1,3)"
# IPOD    (word_id =1) -> "IPOD CLASSIC WHITE" item_id(4)
$CLI INSERT INTO word2item VALUES "(4,1,1,1,1,1,4)"

# NANO    (word_id =2) -> "IPOD NANO BLACK"   item_id(1)
$CLI INSERT INTO word2item VALUES "(5,2,1,1,1,1,1)"
# NANO    (word_id =2) -> "IPOD NANO WHITE"   item_id(2)
$CLI INSERT INTO word2item VALUES "(6,2,1,1,1,1,2)"

# CLASSIC (word_id =3) -> "IPOD CLASSIC BLACK" item_id(3)
$CLI INSERT INTO word2item VALUES "(7,3,1,1,1,1,3)"
# CLASSIC (word_id =3) -> "IPOD CLASSIC WHITE" item_id(4)
$CLI INSERT INTO word2item VALUES "(8,3,1,1,1,1,4)"

# BLACK   (word_id =4) -> "* BLACK item_id(1,3,5,7)"
$CLI INSERT INTO word2item VALUES "(9,4,1,1,1,1,1)"
$CLI INSERT INTO word2item VALUES "(10,4,1,1,1,1,3)"
$CLI INSERT INTO word2item VALUES "(11,4,1,1,1,1,5)"
$CLI INSERT INTO word2item VALUES "(12,4,1,1,1,1,7)"
# WHITE   (word_id =5) -> "* WHITE item_id(2,4,6,8)"
$CLI INSERT INTO word2item VALUES "(13,5,1,1,1,1,2)"
$CLI INSERT INTO word2item VALUES "(14,5,1,1,1,1,4)"
$CLI INSERT INTO word2item VALUES "(15,5,1,1,1,1,6)"
$CLI INSERT INTO word2item VALUES "(16,5,1,1,1,1,8)"

echo SEARCH FOR "IPOD"
$CLI SELECT name FROM item WHERE "id IN (\$SELECT item_id FROM word2item WHERE word_id = 1)"

echo SEARCH FOR "IPOD+CLASSIC"
$CLI SELECT name FROM item WHERE "id IN (\$SELECT item_id FROM word2item WHERE word_id = 1 AND item_id IN (\$SELECT item_id FROM word2item WHERE word_id = 3) )"

echo SEARCH FOR "IPOD+CLASSIC+BLACK"
$CLI SELECT name FROM item WHERE "id IN (\$SELECT item_id FROM word2item WHERE word_id = 1 AND item_id IN (\$SELECT item_id FROM word2item WHERE word_id = 3) AND item_id IN (\$SELECT item_id FROM word2item WHERE word_id = 4) )"