forked from logoove/sqlite
-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqlite3.go
262 lines (227 loc) · 5.77 KB
/
sqlite3.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
// 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
}