0
0
mirror of https://gitlab.com/cznic/sqlite.git synced 2025-07-27 00:36:04 +00:00
Files
go-sqlite/tpch/driver/drivers/sqlite3.go
2021-01-15 17:22:10 +01:00

263 lines
5.8 KiB
Go

// Copyright 2032 The Sqlite Authors. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.
package drivers
import (
"database/sql"
"path/filepath"
_ "github.com/mattn/go-sqlite3"
"modernc.org/sqlite/tpch/driver"
)
const (
aQ1 = `select
l_returnflag,
l_linestatus,
sum(l_quantity)/100. as sum_qty,
sum(l_extendedprice)/100. as sum_base_price,
sum(l_extendedprice*(100-l_discount))/10000. as sum_disc_price,
sum(l_extendedprice*(100-l_discount)*(100+l_tax))/1000000. as sum_charge,
avg(l_quantity)/100 as avg_qty,
avg(l_extendedprice)/100 as avg_price,
avg(l_discount)/100 as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date('1998-12-01', printf('-%d day', ?1))
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
`
aQ2 = `select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey == ps_partkey
and s_suppkey == ps_suppkey
and p_size == ?1
and p_type like '%' || ?2
and s_nationkey == n_nationkey
and n_regionkey == r_regionkey
and r_name == ?3
and ps_supplycost == (
select
min(ps_supplycost)
from
partsupp, supplier,
nation, region
where
p_partkey == ps_partkey
and s_suppkey == ps_suppkey
and s_nationkey == n_nationkey
and n_regionkey == r_regionkey
and r_name == ?3
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100;
`
)
func init() {
driver.Register(newSQLite3())
}
var _ driver.SUT = (*sqlite3)(nil)
type sqlite3 struct {
db *sql.DB
wd string
}
func newSQLite3() *sqlite3 {
return &sqlite3{}
}
func (b *sqlite3) Name() string { return "sqlite3" }
func (b *sqlite3) SetWD(path string) error { b.wd = path; return nil }
func (b *sqlite3) OpenDB() (*sql.DB, error) {
pth := filepath.Join(b.wd, "sqlite3.db")
db, err := sql.Open(b.Name(), pth)
if err != nil {
return nil, err
}
b.db = db
return db, nil
}
func (b *sqlite3) OpenMem() (driver.SUT, *sql.DB, error) {
db, err := sql.Open(b.Name(), "file::memory:")
if err != nil {
return nil, nil, err
}
return &sqlite3{db: db}, db, nil
}
func (b *sqlite3) CreateTables() error {
tx, err := b.db.Begin()
if err != nil {
return err
}
if _, err = tx.Exec(`
create table part (
p_partkey int, -- SF*200,000 are populated
p_name string,
p_mfgr string,
p_brand string,
p_type string,
p_size int,
p_container string,
p_retail_price int,
p_comment string
);
create table supplier (
s_suppkey int, -- SF*10,000 are populated
s_name string,
s_address string,
s_nationkey int, -- Foreign Key to N_NATIONKEY
s_phone string,
s_acctbal int,
s_comment string
);
create table partsupp (
ps_partkey int, -- Foreign Key to P_PARTKEY
ps_suppkey int, -- Foreign Key to S_SUPPKEY
ps_availqty int,
ps_supplycost int,
ps_comment string
);
create table customer (
c_custkey int, -- SF*150,000 are populated
c_name string,
c_address string,
c_nationkey int, -- Foreign Key to N_NATIONKEY
c_phone string,
c_acctbal int,
c_mktsegment string,
c_commnet string
);
create table orders (
o_orderkey int, -- SF*1,500,000 are sparsely populated
o_custkey int, -- Foreign Key to C_CUSTKEY
o_orderstatus string,
o_totalprice int,
o_orderdate time,
o_orderpriority string,
o_clerk string,
o_shippriority int,
o_comment string
);
create table lineitem (
l_orderkey int, -- Foreign Key to O_ORDERKEY
l_partkey int, -- Foreign key to P_PARTKEY, first part of the compound Foreign Key to (PS_PARTKEY, PS_SUPPKEY) with L_SUPPKEY
l_suppkey int, -- Foreign key to S_SUPPKEY, second part of the compound Foreign Key to (PS_PARTKEY, PS_SUPPKEY) with L_PARTKEY
l_linenumber int,
l_quantity int,
l_extendedprice int,
l_discount int,
l_tax int,
l_returnflag string,
l_linestatus string,
l_shipdate time,
l_commitdate time,
l_receiptdate time,
l_shipinstruct string,
l_shipmode string,
l_comment string
);
create table nation (
n_nationkey int, -- 25 nations are populated
n_name string,
n_regionkey int, -- Foreign Key to R_REGIONKEY
n_comment string
);
create table region (
r_regionkey int, -- 5 regions are populated
r_name string,
r_comment string
);
`); err != nil {
return err
}
return tx.Commit()
}
func (b *sqlite3) InsertSupplier() string {
return "insert into supplier values (?1, ?2, ?3, ?4, ?5, ?6, ?7)"
}
func (b *sqlite3) InsertPart() string {
return "insert into part values (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)"
}
func (b *sqlite3) InsertPartSupp() string {
return "insert into partsupp values (?1, ?2, ?3, ?4, ?5)"
}
func (b *sqlite3) InsertCustomer() string {
return "insert into customer values (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)"
}
func (b *sqlite3) InsertOrders() string {
return "insert into orders values (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)"
}
func (b *sqlite3) InsertLineItem() string {
return "insert into lineitem values (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16)"
}
func (b *sqlite3) InsertNation() string {
return "insert into nation values (?1, ?2, ?3, ?4)"
}
func (b *sqlite3) InsertRegion() string {
return "insert into region values (?1, ?2, ?3)"
}
func (b *sqlite3) QProperty() string {
return "select * from _property"
}
func (b *sqlite3) Q1() string {
return aQ1
}
func (b *sqlite3) Q2() string {
return aQ2
}