SSB in sqlite

load_and_run_ssb.tcl

set queries {
    {
        select sum(lo_extendedprice*lo_discount) as revenu
        from lineorder, date
        where lo_orderdate = d_datekey
        and d_yearmonthnum = 199401
        and lo_discount between 4 and 6
        and lo_quantity between 26 and 35
    }

    {
        select sum(lo_extendedprice*lo_discount) as revenue
        from lineorder, date
        where lo_orderdate = d_datekey
        and d_year = 1993
        and lo_discount between 1 and 3
        and lo_quantity < 25
    }

    {
        select sum(lo_extendedprice*lo_discount) as revenue
        from lineorder, date
        where lo_orderdate = d_datekey
        and d_yearmonthnum = 199401
        and lo_discount between 4 and 6
        and lo_quantity between 26 and 35
    }

    {
        select sum(lo_revenue), d_year, p_brand1
        from lineorder, date, part, supplier
        where lo_orderdate = d_datekey
        and lo_partkey = p_partkey
        and lo_suppkey = s_suppkey
        and p_category = 'MFGR#12'
        and s_region = 'AMERICA'
        group by d_year, p_brand1
        order by d_year, p_brand1
    }

    {
        select sum(lo_revenue), d_year, p_brand1
        from lineorder, date, part, supplier
        where lo_orderdate = d_datekey
        and lo_partkey = p_partkey
        and lo_suppkey = s_suppkey
        and p_brand1 between 'MFGR#2221' and 'MFGR#2228'
        and s_region = 'ASIA'
        group by d_year, p_brand1
        order by d_year, p_brand1
    }

    {
        select sum(lo_revenue), d_year, p_brand1
        from lineorder, date, part, supplier
        where lo_orderdate = d_datekey
        and lo_partkey = p_partkey
        and lo_suppkey = s_suppkey
        and p_brand1 = 'MFGR#2221'
        and s_region = 'EUROPE'
        group by d_year, p_brand1
        order by d_year, p_brand1
    }

    {
        select c_nation, s_nation, d_year, sum(lo_revenue) as revenue
        from customer, lineorder, supplier, date
        where lo_custkey = c_custkey
        and lo_suppkey = s_suppkey
        and lo_orderdate = d_datekey
        and c_region = 'ASIA' and s_region = 'ASIA'
        and d_year >= 1992 and d_year <= 1997
        group by c_nation, s_nation, d_year
        order by d_year, revenue desc
    }

    {
        select c_city, s_city, d_year, sum(lo_revenue) as revenue
        from customer, lineorder, supplier, date
        where lo_custkey = c_custkey
        and lo_suppkey = s_suppkey
        and lo_orderdate = d_datekey
        and c_nation = 'UNITED STATES'
        and s_nation = 'UNITED STATES'
        and d_year >= 1992 and d_year <= 1997
        group by c_city, s_city, d_year
        order by d_year, revenue desc
    }

    {
        select c_city, s_city, d_year, sum(lo_revenue) as revenue
        from customer, lineorder, supplier, date
        where lo_custkey = c_custkey
        and lo_suppkey = s_suppkey
        and lo_orderdate = d_datekey
        and (c_city='UNITED KI1' or c_city='UNITED KI5')
        and (s_city='UNITED KI1' or s_city='UNITED KI5')
        and d_year >= 1992 and d_year <= 1997
        group by c_city, s_city, d_year
        order by d_year, revenue desc
    }

    {
        select c_city, s_city, d_year, sum(lo_revenue) as revenue
        from customer, lineorder, supplier, date
        where lo_custkey = c_custkey
        and lo_suppkey = s_suppkey
        and lo_orderdate = d_datekey
        and (c_city='UNITED KI1' or c_city='UNITED KI5')
        and (s_city='UNITED KI1' or s_city='UNITED KI5')
        and d_yearmonth = 'Dec1997'
        group by c_city, s_city, d_year
        order by d_year, revenue desc
    }

    {
        select d_year, c_nation, sum(lo_revenue - lo_supplycost) as profit
        from date, customer, supplier, part, lineorder
        where lo_custkey = c_custkey
        and lo_suppkey = s_suppkey
        and lo_partkey = p_partkey
        and lo_orderdate = d_datekey
        and c_region = 'AMERICA'
        and s_region = 'AMERICA'
        and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
        group by d_year, c_nation
        order by d_year, c_nation
    }
    {
        select d_year, s_nation, p_category, sum(lo_revenue - lo_supplycost) as profit
        from date, customer, supplier, part, lineorder
        where lo_custkey = c_custkey
        and lo_suppkey = s_suppkey
        and lo_partkey = p_partkey
        and lo_orderdate = d_datekey
        and c_region = 'AMERICA'
        and s_region = 'AMERICA'
        and (d_year = 1997 or d_year = 1998)
        and (p_mfgr = 'MFGR#1'
             or p_mfgr = 'MFGR#2')
        group by d_year, s_nation, p_category order by d_year, s_nation, p_category
    }

    {
        select d_year, s_city, p_brand1, sum(lo_revenue - lo_supplycost) as profit
        from date, customer, supplier, part, lineorder
        where lo_custkey = c_custkey
        and lo_suppkey = s_suppkey
        and lo_partkey = p_partkey
        and lo_orderdate = d_datekey
        and c_region = 'AMERICA'
        and s_nation = 'UNITED STATES'
        and (d_year = 1997 or d_year = 1998)
        and p_category = 'MFGR#14'
        group by d_year, s_city, p_brand1 order by d_year, s_city, p_brand1
    }
}
proc pages_so_far {dbcmd} {
    upvar $dbcmd db
    db_enter db
    array set stats [btree_from_db [btree_from_db db]]
    db_leave db
    db close
    return $stats(page)
}
if {"--indexes" in $argv} {
    set db_file ./ssb/db-indexes
    set ddl_file ./ssb/SSB-sqlite/sqlite-ddl-indexes.sql
} else {
    set db_file ./ssb/db
    set ddl_file ./ssb/SSB-sqlite/sqlite-ddl.sql
}
proc create_db {} {
    puts "Creating database $::db_file"
    sqlite3 db $::db_file
    db eval [read [set f [open $::ddl_file]]]
    foreach i [db eval {select name from sqlite_schema where type = 'table'}] {
        puts $i;
         db copy  replace $i ./ssb/$i.tbl |
    }
    db close
}

proc eval_query {q} {
    sqlite3 db $::db_file
    db eval $q
    set bt [btree_from_db db]
    db_enter db
    array set stats [btree_pager_stats $bt]
    db_leave db
    db close
    foreach n {read miss} {
        puts "$n => $stats($n)"
    }
}

# create the datbase
if {! [file exists $::db_file]} {
    create_db
}

# Run the queries
set qi 0
foreach q $queries {
    incr qi
    puts "# Query: $qi"
    eval_query $q
}

sqlite_ddl.sql

CREATE TABLE date (
	d_datekey          INT,     -- identifier, unique id -- e.g. 19980327 (what we use)
	d_date             TEXT,  -- varchar(18), --fixed text, size 18, longest: december 22, 1998
	d_dayofweek        TEXT,  -- varchar(8), --fixed text, size 8, sunday, monday, ..., saturday)
	d_month            TEXT,  -- varchar(9), --fixed text, size 9: january, ..., december
	d_year             INT,     -- unique value 1992-1998
	d_yearmonthnum     INT,     -- numeric (yyyymm) -- e.g. 199803
	d_yearmonth        TEXT,  -- varchar(7), --fixed text, size 7: mar1998 for example
	d_daynuminweek     INT,     -- numeric 1-7
	d_daynuminmonth    INT,     -- numeric 1-31
	d_daynuminyear     INT,     -- numeric 1-366
	d_monthnuminyear   INT,     -- numeric 1-12
	d_weeknuminyear    INT,     -- numeric 1-53
	d_sellingseason    TEXT,  -- varchar(12), --text, size 12 (christmas, summer,...)
	d_lastdayinweekfl  INT,     -- 1 bit
	d_lastdayinmonthfl INT,     -- 1 bit
	d_holidayfl        INT,     -- 1 bit
	d_weekdayfl        INT     -- 1 bit
	-- PRIMARY KEY (d_datekey)
);

CREATE TABLE supplier (
	s_suppkey INT,     -- identifier
	s_name    TEXT,  -- varchar(25), --fixed text, size 25: 'supplier'||suppkey
	s_address TEXT,  -- varchar(25), --variable text, size 25 (city below)
	s_city    TEXT,  -- varchar(10), --fixed text, size 10 (10/nation: nation_prefix||(0-9))
	s_nation  TEXT,  -- varchar(15), --fixed text(15) (25 values, longest united kingdom)
	s_region  TEXT,  -- varchar(12), --fixed text, size 12 (5 values: longest middle east)
	s_phone   TEXT  -- varchar(15) --fixed text, size 15 (many values, format: 43-617-354-1222)
	-- PRIMARY KEY (s_suppkey)
);

CREATE TABLE customer (
	c_custkey    INT,--numeric identifier
	c_name       TEXT,     -- varchar(25), --variable text, size 25 'customer'||custkey
	c_address    TEXT,     -- varchar(25), --variable text, size 25 (city below)
	c_city       TEXT,     -- varchar(10), --fixed text, size 10 (10/nation: nation_prefix||(0-9)
	c_nation     TEXT,     -- varchar(15), --fixed text(15) (25 values, longest united kingdom)
	c_region     TEXT,     -- varchar(12), --fixed text, size 12 (5 values: longest middle east)
	c_phone      TEXT,     -- varchar(15), --fixed text, size 15 (many values, format: 43-617-354-1222)
	c_mktsegment TEXT     -- varchar(10) --fixed text, size 10 (longest is automobile)
	-- PRIMARY KEY (c_custkey)
);

CREATE TABLE part (
	p_partkey   INT,        -- identifier
	p_name      TEXT,     -- varchar(22), --variable text, size 22 (not unique per part but never was)
	p_mfgr      TEXT,     -- varchar(6), --fixed text, size 6 (mfgr#1-5, card = 5)
	p_category  TEXT,     -- varchar(7), --fixed text, size 7 ('mfgr#'||1-5||1-5: card = 25)
	p_brand1    TEXT,     -- varchar(9), --fixed text, size 9 (category||1-40: card = 1000)
	p_color     TEXT,     -- varchar(11), --variable text, size 11 (card = 94)
	p_type      TEXT,     -- varchar(25), --variable text, size 25 (card = 150)
	p_size      INT,        -- numeric 1-50 (card = 50)
	p_container TEXT     -- varchar(15) --fixed text(10) (card = 40)
	-- PRIMARY KEY (p_partkey)
);

CREATE TABLE lineorder (
	lo_orderkey      INT,     -- numeric (int up to sf 300) first 8 of each 32 keys used
	lo_linenumber    INT,     -- numeric 1-7
	lo_custkey       INT,     -- numeric identifier foreign key reference to c_custkey
	lo_partkey       INT,     -- identifier foreign key reference to p_partkey
	lo_suppkey       INT,     -- numeric identifier foreign key reference to s_suppkey
	lo_orderdate     INT,     -- identifier foreign key reference to d_datekey
	lo_orderpriority TEXT,  -- varchar(15), --fixed text, size 15 (5 priorities: 1-urgent, etc.)
	lo_shippriority  TEXT,  -- varchar(1), --fixed text, size 1
	lo_quantity      INT,     -- numeric 1-50 (for part)
	lo_extendedprice INT,     -- numeric, max about 55,450 (for part)
	lo_ordtotalprice INT,     -- numeric, max about 388,000 (for order)
	lo_discount      INT,     -- numeric 0-10 (for part) -- (represents percent)
	lo_revenue       INT,     -- numeric (for part: (extendedprice*(100-discount))/100)
	lo_supplycost    INT,     -- numeric (for part, cost from supplier, max = ?)
	lo_tax           INT,     -- numeric 0-8 (for part)
	lo_commitdate    INT,     -- foreign key reference to d_datekey
	lo_shipmode      TEXT  -- varchar(10) --fixed text, size 10 (modes: reg air, air, etc.)
	-- PRIMARY KEY (lo_orderkey, lo_linenumber), --Compound Primary Key: ORDERKEY, LINENUMBER
	-- FOREIGN KEY (lo_orderdate)  REFERENCES date    (d_datekey), --identifier foreign key reference to D_DATEKEY
	-- FOREIGN KEY (lo_commitdate) REFERENCES date    (d_datekey), --Foreign Key reference to D_DATEKEY
	-- FOREIGN KEY (lo_suppkey)    REFERENCES supplier (s_suppkey), --numeric identifier foreign key reference to S_SUPPKEY
	-- FOREIGN KEY (lo_custkey)    REFERENCES customer (c_custkey)  --numeric identifier foreign key reference
);

sqlite_ddl_indexes.sql

CREATE TABLE date (
	d_datekey          INT,     -- identifier, unique id -- e.g. 19980327 (what we use)
	d_date             TEXT,  -- varchar(18), --fixed text, size 18, longest: december 22, 1998
	d_dayofweek        TEXT,  -- varchar(8), --fixed text, size 8, sunday, monday, ..., saturday)
	d_month            TEXT,  -- varchar(9), --fixed text, size 9: january, ..., december
	d_year             INT,     -- unique value 1992-1998
	d_yearmonthnum     INT,     -- numeric (yyyymm) -- e.g. 199803
	d_yearmonth        TEXT,  -- varchar(7), --fixed text, size 7: mar1998 for example
	d_daynuminweek     INT,     -- numeric 1-7
	d_daynuminmonth    INT,     -- numeric 1-31
	d_daynuminyear     INT,     -- numeric 1-366
	d_monthnuminyear   INT,     -- numeric 1-12
	d_weeknuminyear    INT,     -- numeric 1-53
	d_sellingseason    TEXT,  -- varchar(12), --text, size 12 (christmas, summer,...)
	d_lastdayinweekfl  INT,     -- 1 bit
	d_lastdayinmonthfl INT,     -- 1 bit
	d_holidayfl        INT,     -- 1 bit
	d_weekdayfl        INT,     -- 1 bit
	PRIMARY KEY (d_datekey)
);

CREATE TABLE supplier (
	s_suppkey INT,     -- identifier
	s_name    TEXT,  -- varchar(25), --fixed text, size 25: 'supplier'||suppkey
	s_address TEXT,  -- varchar(25), --variable text, size 25 (city below)
	s_city    TEXT,  -- varchar(10), --fixed text, size 10 (10/nation: nation_prefix||(0-9))
	s_nation  TEXT,  -- varchar(15), --fixed text(15) (25 values, longest united kingdom)
	s_region  TEXT,  -- varchar(12), --fixed text, size 12 (5 values: longest middle east)
	s_phone   TEXT,  -- varchar(15) --fixed text, size 15 (many values, format: 43-617-354-1222)
	PRIMARY KEY (s_suppkey)
);

create TABLE customer (
	c_custkey    INT,--numeric identifier
	c_name       TEXT,     -- varchar(25), --variable text, size 25 'customer'||custkey
	c_address    TEXT,     -- varchar(25), --variable text, size 25 (city below)
	c_city       TEXT,     -- varchar(10), --fixed text, size 10 (10/nation: nation_prefix||(0-9)
	c_nation     TEXT,     -- varchar(15), --fixed text(15) (25 values, longest united kingdom)
	c_region     TEXT,     -- varchar(12), --fixed text, size 12 (5 values: longest middle east)
	c_phone      TEXT,     -- varchar(15), --fixed text, size 15 (many values, format: 43-617-354-1222)
	c_mktsegment TEXT     -- varchar(10) --fixed text, size 10 (longest is automobile)
	,PRIMARY KEY (c_custkey)
);

CREATE TABLE part (
	p_partkey   INT,        -- identifier
	p_name      TEXT,     -- varchar(22), --variable text, size 22 (not unique per part but never was)
	p_mfgr      TEXT,     -- varchar(6), --fixed text, size 6 (mfgr#1-5, card = 5)
	p_category  TEXT,     -- varchar(7), --fixed text, size 7 ('mfgr#'||1-5||1-5: card = 25)
	p_brand1    TEXT,     -- varchar(9), --fixed text, size 9 (category||1-40: card = 1000)
	p_color     TEXT,     -- varchar(11), --variable text, size 11 (card = 94)
	p_type      TEXT,     -- varchar(25), --variable text, size 25 (card = 150)
	p_size      INT,        -- numeric 1-50 (card = 50)
	p_container TEXT     -- varchar(15) --fixed text(10) (card = 40)
	,PRIMARY KEY (p_partkey)
);

CREATE TABLE lineorder (
	lo_orderkey      INT,     -- numeric (int up to sf 300) first 8 of each 32 keys used
	lo_linenumber    INT,     -- numeric 1-7
	lo_custkey       INT,     -- numeric identifier foreign key reference to c_custkey
	lo_partkey       INT,     -- identifier foreign key reference to p_partkey
	lo_suppkey       INT,     -- numeric identifier foreign key reference to s_suppkey
	lo_orderdate     INT,     -- identifier foreign key reference to d_datekey
	lo_orderpriority TEXT,  -- varchar(15), --fixed text, size 15 (5 priorities: 1-urgent, etc.)
	lo_shippriority  TEXT,  -- varchar(1), --fixed text, size 1
	lo_quantity      INT,     -- numeric 1-50 (for part)
	lo_extendedprice INT,     -- numeric, max about 55,450 (for part)
	lo_ordtotalprice INT,     -- numeric, max about 388,000 (for order)
	lo_discount      INT,     -- numeric 0-10 (for part) -- (represents percent)
	lo_revenue       INT,     -- numeric (for part: (extendedprice*(100-discount))/100)
	lo_supplycost    INT,     -- numeric (for part, cost from supplier, max = ?)
	lo_tax           INT,     -- numeric 0-8 (for part)
	lo_commitdate    INT,     -- foreign key reference to d_datekey
	lo_shipmode      TEXT  -- varchar(10) --fixed text, size 10 (modes: reg air, air, etc.)
	,PRIMARY KEY (lo_orderkey, lo_linenumber), --Compound Primary Key: ORDERKEY, LINENUMBER
	FOREIGN KEY (lo_orderdate)  REFERENCES date    (d_datekey), --identifier foreign key reference to D_DATEKEY
	FOREIGN KEY (lo_commitdate) REFERENCES date    (d_datekey), --Foreign Key reference to D_DATEKEY
	FOREIGN KEY (lo_suppkey)    REFERENCES supplier (s_suppkey), --numeric identifier foreign key reference to S_SUPPKEY
	FOREIGN KEY (lo_custkey)    REFERENCES customer (c_custkey)  --numeric identifier foreign key reference
);