func TestDates(t *testing.T) {
defer leaktest.AfterTest(t)
// From https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
locationNames := []string{
"Canada/Newfoundland", // half-hour zone
"Europe/London", // same as UTC, except for DST
"Pacific/Kiritimati", // maximum positive offset
"Pacific/Midway", // maximum negative offset
"US/Pacific", // negative offset
"UTC",
}
for _, locationName := range locationNames {
loc, err := time.LoadLocation(locationName)
if err != nil {
t.Error(err)
continue
}
s, db := setup(t, loc)
defer cleanup(s, db)
var date *driver.Date
for _, year := range []int{
1200, // distant past
2020, // present day, for DST rules
4000, // distant future
} {
for _, month := range []time.Month{
time.December, // winter
time.August, // summer
} {
for hour := 0; hour < 24; hour++ {
timestamp := time.Date(year, month, 20, hour, 34, 45, 123, loc)
if err := db.QueryRow("SELECT $1::DATE", timestamp).Scan(&date); err != nil {
t.Fatal(err)
}
if expected := driver.MakeDate(timestamp); !date.Equal(expected.Time) {
t.Fatalf("expected date to be truncated to:\n%s\nbut got:\n%s", expected, date)
}
}
}
}
}
}
func TestPlaceholders(t *testing.T) {
defer leaktest.AfterTest(t)
// loc is selected so that timeVal below maps to a different date in
// loc and UTC.
loc, err := time.LoadLocation("Pacific/Midway")
if err != nil {
t.Fatal(err)
}
s, db := setup(t, loc)
defer cleanup(s, db)
year, month, day := 3015, time.August, 30
timeVal := time.Date(year, month, day, 3, 34, 45, 345670000, loc)
dateVal := driver.Date{Time: time.Date(year, month, day, 0, 0, 0, 0, time.UTC)}
intervalVal, err := time.ParseDuration("34h2s")
if err != nil {
t.Fatal(err)
}
if result, err := db.Exec(`CREATE DATABASE t`); err != nil {
t.Fatal(err)
} else if _, err := result.LastInsertId(); !testutils.IsError(err, "no LastInsertId available after DDL statement") {
t.Error(err)
} else if _, err := result.RowsAffected(); !testutils.IsError(err, "no RowsAffected available after DDL statement") {
t.Error(err)
}
schema := `
CREATE TABLE t.alltypes (
a BIGINT PRIMARY KEY,
b FLOAT,
c TEXT,
d BYTES,
e BOOLEAN,
f TIMESTAMP,
g DATE,
h INTERVAL
)
`
if result, err := db.Exec(schema); err != nil {
t.Fatal(err)
} else if _, err := result.LastInsertId(); !testutils.IsError(err, "no LastInsertId available after DDL statement") {
t.Error(err)
} else if _, err := result.RowsAffected(); !testutils.IsError(err, "no RowsAffected available after DDL statement") {
t.Error(err)
}
var (
a int64
b sql.NullFloat64
c sql.NullString
d sql.NullString
e sql.NullBool
f *time.Time
g *driver.Date
h *time.Duration
)
if rows, err := db.Query("SELECT * FROM t.alltypes"); err != nil {
t.Fatal(err)
} else {
defer rows.Close()
cols, err := rows.Columns()
if err != nil {
t.Fatal(err)
}
if expected := []string{"a", "b", "c", "d", "e", "f", "g", "h"}; !reflect.DeepEqual(cols, expected) {
t.Errorf("got unexpected columns:\n%s\nexpected:\n%s", cols, expected)
}
}
// Insert values for all the different types.
if result, err := db.Exec(`INSERT INTO t.alltypes VALUES ($1, $2, $3, $4, $5, $6, $6::DATE, $7::INTERVAL)`,
123, 3.4, "blah", []byte("foo"), true, timeVal, intervalVal); err != nil {
t.Fatal(err)
} else if got, err := result.RowsAffected(); err != nil {
t.Fatal(err)
} else if e := int64(1); got != e {
t.Fatalf("expected %d rows affected, got %d", e, a)
}
// Insert a row with NULL values
if result, err := db.Exec(`INSERT INTO t.alltypes VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
456, nil, nil, nil, nil, nil, nil, nil); err != nil {
t.Fatal(err)
} else if got, err := result.RowsAffected(); err != nil {
t.Fatal(err)
} else if e := int64(1); got != e {
t.Fatalf("expected %d rows affected, got %d", e, a)
}
if _, err := db.Query("SELECT a, b FROM t.alltypes WHERE a IN ($1)", 123); err != nil {
t.Fatal(err)
}
if _, err := db.Query("SELECT a, b FROM t.alltypes WHERE b IN ($1)", 3.4); err != nil {
t.Fatal(err)
}
if _, err := db.Query("SELECT a, b FROM t.alltypes WHERE c IN ($1)", "blah"); err != nil {
t.Fatal(err)
}
if _, err := db.Query("SELECT a, b FROM t.alltypes WHERE d IN ($1)", []byte("foo")); err != nil {
t.Fatal(err)
}
if _, err := db.Query("SELECT a, b FROM t.alltypes WHERE e IN ($1)", true); err != nil {
t.Fatal(err)
}
if _, err := db.Query("SELECT a, b FROM t.alltypes WHERE f IN ($1)", timeVal); err != nil {
t.Fatal(err)
}
if _, err := db.Query("SELECT a, b FROM t.alltypes WHERE g IN ($1::DATE)", timeVal); err != nil {
t.Fatal(err)
}
if _, err := db.Query("SELECT a, b FROM t.alltypes WHERE h IN ($1::INTERVAL)", intervalVal); err != nil {
t.Fatal(err)
}
if rows, err := db.Query("SELECT * FROM t.alltypes"); err != nil {
t.Fatal(err)
} else {
defer rows.Close()
rows.Next()
if err := rows.Scan(&a, &b, &c, &d, &e, &f, &g, &h); err != nil {
t.Fatal(err)
}
if err := rows.Err(); err != nil {
t.Fatal(err)
}
if !(a == 123 && b.Float64 == 3.4 && c.String == "blah" && d.String == "foo" &&
e.Bool && f.Equal(timeVal) && g.Equal(dateVal.Time) && *h == intervalVal) {
t.Errorf(
"expected:\n%+v\ngot:\n%+v",
[]interface{}{123, 3.4, "blah", "foo", true, timeVal, dateVal, intervalVal},
[]interface{}{a, b, c, d, e, f, g, h},
)
}
rows.Next()
if err := rows.Scan(&a, &b, &c, &d, &e, &f, &g, &h); err != nil {
t.Fatal(err)
}
if err := rows.Err(); err != nil {
t.Fatal(err)
}
if !(a == 456 && !b.Valid && !c.Valid && !d.Valid && !e.Valid && f == nil && g == nil && h == nil) {
t.Errorf(
"expected:\n%+v\ngot:\n%+v",
[]interface{}{123, "<NOT NULL>", "<NOT NULL>", "<NOT NULL>", "<NOT NULL>", "<NULL>", "<NULL>", "<NULL>"},
[]interface{}{a, b, c, d, e, f, g, h},
)
}
if rows.Next() {
t.Error("expected rows to be complete")
}
}
// Delete a row using a placeholder param.
if result, err := db.Exec(`DELETE FROM t.alltypes WHERE a IN ($1)`, 123); err != nil {
t.Fatal(err)
} else if got, err := result.RowsAffected(); err != nil {
t.Fatal(err)
} else if e := int64(1); got != e {
t.Fatalf("expected %d rows affected, got %d", e, a)
}
if rows, err := db.Query("SELECT * FROM t.alltypes"); err != nil {
t.Fatal(err)
} else {
defer rows.Close()
rows.Next()
if err := rows.Scan(&a, &b, &c, &d, &e, &f, &g, &h); err != nil {
t.Fatal(err)
}
if err := rows.Err(); err != nil {
t.Fatal(err)
}
if !(a == 456 && !b.Valid && !c.Valid && !d.Valid && !e.Valid && f == nil && g == nil && h == nil) {
t.Errorf(
"expected:\n%+v\ngot:\n%+v",
[]interface{}{123, "<NOT NULL>", "<NOT NULL>", "<NOT NULL>", "<NOT NULL>", "<NULL>", "<NULL>", "<NULL>"},
[]interface{}{a, b, c, d, e, f, g, h},
)
}
if rows.Next() {
t.Error("expected rows to be complete")
}
}
}