// afterConnect creates the prepared statements that this application uses
func afterConnect(conn *pgx.Conn) (err error) {
_, err = conn.Prepare("getUrl", `
select url from shortened_urls where id=$1
`)
if err != nil {
return
}
_, err = conn.Prepare("deleteUrl", `
delete from shortened_urls where id=$1
`)
if err != nil {
return
}
// There technically is a small race condition in doing an upsert with a CTE
// where one of two simultaneous requests to the shortened URL would fail
// with a unique index violation. As the point of this demo is pgx usage and
// not how to perfectly upsert in PostgreSQL it is deemed acceptable.
_, err = conn.Prepare("putUrl", `
with upsert as (
update shortened_urls
set url=$2
where id=$1
returning *
)
insert into shortened_urls(id, url)
select $1, $2 where not exists(select 1 from upsert)
`)
return
}
func mustPrepare(db *pgx.Conn, name, query string) *pgx.PreparedStatement {
stmt, err := db.Prepare(name, query)
if err != nil {
log.Fatalf("Error when preparing statement %q: %s", query, err)
}
return stmt
}
func PrepareStatements(conn *pgx.Conn) error {
for name, sql := range preparedStatements {
if _, err := conn.Prepare(name, sql); err != nil {
return err
}
}
return nil
}
func mustPrepare(t testing.TB, conn *pgx.Conn, name, sql string) *pgx.PreparedStatement {
ps, err := conn.Prepare(name, sql)
if err != nil {
t.Fatalf("Could not prepare %v: %v", name, err)
}
return ps
}
func benchmarkSelectWithLog(b *testing.B, conn *pgx.Conn) {
_, err := conn.Prepare("test", "select 1::int4, 'johnsmith', '*****@*****.**', 'John Smith', 'male', '1970-01-01'::date, '2015-01-01 00:00:00'::timestamptz")
if err != nil {
b.Fatal(err)
}
b.ResetTimer()
for i := 0; i < b.N; i++ {
var record struct {
id int32
userName string
email string
name string
sex string
birthDate time.Time
lastLoginTime time.Time
}
err = conn.QueryRow("test").Scan(
&record.id,
&record.userName,
&record.email,
&record.name,
&record.sex,
&record.birthDate,
&record.lastLoginTime,
)
if err != nil {
b.Fatal(err)
}
// These checks both ensure that the correct data was returned
// and provide a benchmark of accessing the returned values.
if record.id != 1 {
b.Fatalf("bad value for id: %v", record.id)
}
if record.userName != "johnsmith" {
b.Fatalf("bad value for userName: %v", record.userName)
}
if record.email != "*****@*****.**" {
b.Fatalf("bad value for email: %v", record.email)
}
if record.name != "John Smith" {
b.Fatalf("bad value for name: %v", record.name)
}
if record.sex != "male" {
b.Fatalf("bad value for sex: %v", record.sex)
}
if record.birthDate != time.Date(1970, 1, 1, 0, 0, 0, 0, time.Local) {
b.Fatalf("bad value for birthDate: %v", record.birthDate)
}
if record.lastLoginTime != time.Date(2015, 1, 1, 0, 0, 0, 0, time.Local) {
b.Fatalf("bad value for lastLoginTime: %v", record.lastLoginTime)
}
}
}
func (self *Database) afterConnect(conn *pgx.Conn) (err error) {
_, err = conn.Prepare("getZoneById", `SELECT
"id", "name"
FROM "stores"
WHERE id=$1
`)
if err != nil {
self.Log.Error("Prepare", "getZoneById", err.Error())
return
}
_, err = conn.Prepare("setShowBanner", `INSERT INTO "banner_shows"
(created_at, updated_at, show_date, show_time, store_id, is_bot, ses_uuid, user_mac, user_ip, ipv4, accept_language,
ua_browser_family, ua_browser_version, ua_os_family, ua_device_family, is_mobile, user_agent, referrer)
VALUES ($1::timestamptz(0), NOW()::timestamptz(0), $1::date, $1::time(0), $3, $4, $2, $5, $6, $7, $8, $9, $10::int2, $11, $12, $13, $14, $15)
`)
if err != nil {
self.Log.Error("Prepare", "setShowBanner", err.Error())
return
}
_, err = conn.Prepare("getZoneByName", `
SELECT
"address",
"city",
"country",
"created_at",
"deleted_at",
"email",
"id",
"latitude",
"longitude",
"name",
"phone",
"position",
"region",
"updated_at",
"zip"
FROM "stores" WHERE lower(name)=lower($1)
`)
if err != nil {
self.Log.Error("Prepare", "getZoneByName", err.Error())
return
}
return
}
// initial all sql
func (pgdb *PostgresDB) afterConnect(conn *pgx.Conn) (err error) {
_, err = conn.Prepare("getTask", `
select id,code, description from tasks where id=$1
`)
checkError(err)
_, err = conn.Prepare("listTask", `
select id,code, description from tasks order by id asc
`)
checkError(err)
_, err = conn.Prepare("addTask", `
insert into tasks(code, description) values( $1, $2 )
`)
checkError(err)
_, err = conn.Prepare("updateTask", `
update tasks
set code = $2, description=$3
where id=$1
`)
checkError(err)
_, err = conn.Prepare("deleteTask", `
delete from tasks where id=$1
`)
checkError(err)
/**
_, err = conn.Prepare("transfer", `select * from transfer('Bob','Mary',14.00)`)
if err != nil {
return
}
*/
// There technically is a small race condition in doing an upsert with a CTE
// where one of two simultaneous requests to the shortened URL would fail
// with a unique index violation. As the point of this demo is pgx usage and
// not how to perfectly upsert in PostgreSQL it is deemed acceptable.
_, err = conn.Prepare("putTask", `
with upsert as (
update tasks
set code = $2, description=$3
where id=$1
returning *
)
insert into tasks(id, code, description)
select $1, $2, $3 where not exists(select 1 from upsert)
`)
checkError(err)
return
}
func mustPrepare(t testing.TB, conn *pgx.Conn, name, sql string) {
if _, err := conn.Prepare(name, sql); err != nil {
t.Fatalf("Could not prepare %v: %v", name, err)
}
}
func prepareStatements(database *pgx.Conn) error {
var err error
_, err = database.Prepare("queryCommit", "select product from commit where hash = $1")
if err != nil {
return err
}
_, err = database.Prepare("queryCommitWithProduct", "select commitDate from commit where hash = $1 and product = $2")
if err != nil {
return err
}
_, err = database.Prepare("insertCommit", "insert into commit (hash, commitDate, product, mergeBaseHash) values ($1, $2, $3, $4)")
if err != nil {
return err
}
_, err = database.Prepare("queryBenchmarks", "select name from benchmark")
if err != nil {
return err
}
_, err = database.Prepare("insertBenchmark", "insert into benchmark (name, disabled) values ($1, NULL)")
if err != nil {
return err
}
_, err = database.Prepare("queryConfig", "select monoExecutable, monoEnvironmentVariables, monoOptions from config where name = $1")
if err != nil {
return err
}
_, err = database.Prepare("insertConfig", "insert into config (name, monoExecutable, monoEnvironmentVariables, monoOptions) values ($1, $2, $3, $4)")
if err != nil {
return err
}
_, err = database.Prepare("queryMachine", "select architecture from machine where name = $1")
if err != nil {
return err
}
_, err = database.Prepare("insertMachine", "insert into machine (name, architecture, isDedicated) values ($1, $2, false)")
if err != nil {
return err
}
runSetColumns := "r.startedAt, r.finishedAt, r.buildURL, r.logURLs, c.hash as commit, r.secondaryCommits, m.name as machine, cfg.name as config, timedOutBenchmarks, crashedBenchmarks, pullRequest"
_, err = database.Prepare("queryRunSet", "select "+runSetColumns+" from runset r, commit c, machine m, config cfg where r.id = $1 and r.commit = c.id and r.machine = m.id and r.config = cfg.id")
if err != nil {
return err
}
runSetColumnsInsert := "startedAt, finishedAt, buildURL, logURLs, commit, secondaryCommits, machine, config, timedOutBenchmarks, crashedBenchmarks, pullRequest"
_, err = database.Prepare("insertRunSet", "insert into runSet ("+runSetColumnsInsert+") select $1, $2, $3, $4, c.id, $6, m.id, cfg.id, $9, $10, $11 from machine m, config cfg, commit c where c.hash = $5 and m.name = $7 and cfg.name = $8 returning runset.id")
if err != nil {
return err
}
_, err = database.Prepare("updateRunSet", "update runSet set logURLs = $2, timedOutBenchmarks = $3, crashedBenchmarks = $4 where id = $1")
if err != nil {
return err
}
_, err = database.Prepare("deleteRunSet", "delete from RunSet where id = $1")
if err != nil {
return err
}
_, err = database.Prepare("insertRun", "insert into run (benchmark, runSet) select b.id, $2 from benchmark b where b.name = $1 returning id")
if err != nil {
return err
}
_, err = database.Prepare("deleteRunByRunSetId", "delete from Run where runSet = $1")
if err != nil {
return err
}
_, err = database.Prepare("queryRunMetrics", "select r.id, b.name as benchmark, metric.name as metric, rm.result, rm.resultArray from run r, runMetric rm, benchmark b, metric where r.runSet = $1 and r.benchmark = b.id and rm.run = r.id and rm.metric = metric.id")
if err != nil {
return err
}
_, err = database.Prepare("queryRunMetricsForRun", "select rm.id, metric.name as metric from runMetric rm, metric where rm.run = $1 and rm.metric = metric.id")
if err != nil {
return err
}
_, err = database.Prepare("insertRunMetricNumber", "insert into runMetric (run, metric, result) select $1, metric.id, $3 from metric where metric.name = $2")
if err != nil {
return err
}
_, err = database.Prepare("updateRunMetricNumber", "update runMetric set result = $2 where id = $1")
if err != nil {
return err
}
_, err = database.Prepare("insertRunMetricArray", "insert into runMetric (run, metric, resultArray) select $1, metric.id, $3 from metric where metric.name = $2")
if err != nil {
return err
}
_, err = database.Prepare("deleteRunMetricByRunSetId", "delete from RunMetric where run in (select id from Run where runSet = $1)")
if err != nil {
return err
}
_, err = database.Prepare("updateRunMetricArray", "update runMetric set resultArray = $2 where id = $1")
if err != nil {
return err
}
_, err = database.Prepare("queryRunSetSummaries", "select rs.id, c.hash as commit from runSet rs, commit c, machine m, config cfg where rs.machine = m.id and rs.config = cfg.id and rs.commit = c.id and m.name = $1 and cfg.name = $2")
if err != nil {
return err
}
_, err = database.Prepare("insertPullRequest", "insert into pullRequest (baselineRunSet, url) values ($1, $2) returning id")
if err != nil {
return err
}
return nil
}