Example #1
1
// 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
}
Example #2
0
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
}
Example #3
0
func PrepareStatements(conn *pgx.Conn) error {
	for name, sql := range preparedStatements {
		if _, err := conn.Prepare(name, sql); err != nil {
			return err
		}
	}
	return nil
}
Example #4
0
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
}
Example #5
0
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)
		}
	}
}
Example #6
0
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
}
Example #8
0
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)
	}
}
Example #9
0
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
}