func Test_StringAgg(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
expect.SQL(
`string_agg("times"."when", ', ')`,
StringAgg(times.C["when"], ", "),
)
}
func TestWithin(t *testing.T) {
expect := aspect.NewTester(t, &postgres.PostGres{})
expect.SQL(
`ST_Within(ST_Point(-104.984722, 39.739167), "shapes"."area")`,
Within(shapes.C["area"], Point{-104.984722, 39.739167}),
)
}
func TestFunctions(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
expect.SQL(
`ISEMPTY("times"."when" * $1)`,
IsEmpty(C(times.C["when"]).Intersection("range")),
"range",
)
}
func TestColumn(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
expect.SQL(
`"times"."when" @> $1`,
C(times.C["when"]).Contains("[0, 1)"),
"[0, 1)",
)
}
func TestLatLong(t *testing.T) {
expect := aspect.NewTester(t, &postgres.PostGres{})
// TODO parameterization?
expect.SQL(
`ST_SetSRID(ST_Point(-104.984722, 39.739167), 4326)::geography`,
LatLong{39.739167, -104.984722},
)
}
func TestUUID(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
expect.Create("UUID", UUID{})
expect.Create("UUID PRIMARY KEY", UUID{PrimaryKey: true})
expect.Create(
"UUID PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4()",
UUID{PrimaryKey: true, NotNull: true, Default: GenerateV4},
)
}
func TestJSON(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
expect.Create("JSON", JSON{})
expect.Create("JSON PRIMARY KEY", JSON{PrimaryKey: true})
expect.Create(
"JSON PRIMARY KEY NOT NULL",
JSON{PrimaryKey: true, NotNull: true},
)
}
func TestExclude(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
expect.Create(
`EXCLUDE ("room" WITH =)`,
Exclude(Equal.With("room")),
)
expect.Create(
`EXCLUDE USING gist ("room" WITH =, "when" WITH &&)`,
Exclude(Equal.With("room"), Overlap.With("when")).Using(Gist),
)
}
func TestSequence(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
// Alter statements without a clause will error
expect.Error(AlterSequence(Sequence("companies_id_seq")))
expect.SQL(
`ALTER SEQUENCE "companies_id_seq" RENAME TO 'whatever'`,
AlterSequence(Sequence("companies_id_seq")).RenameTo("whatever"),
)
expect.SQL(
`ALTER SEQUENCE "companies_id_seq" RESTART WITH 1`,
AlterSequence(Sequence("companies_id_seq")).RestartWith(1),
)
}
func TestColumn(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
expect.SQL(
`"times"."when" @> $1`,
C(times.C["when"]).Contains("[0, 1)"),
"[0, 1)",
)
// JSON selector
// http://www.postgresql.org/docs/9.3/static/functions-json.html
expect.SQL(
`"members"."info" ->> 'name' AS "Name"`,
C(members.C["info"]).GetJSONText("name").As("Name"),
)
}
func TestDatetime(t *testing.T) {
expect := aspect.NewTester(t, &Sqlite3{})
expect.Create("DATETIME", Datetime{})
expect.Create(
"DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP",
Datetime{NotNull: true, Default: CurrentTimestamp},
)
d := time.Date(2014, 1, 1, 12, 0, 0, 0, time.UTC)
value, err := Datetime{}.Validate(d)
assert.Nil(t, err)
assert.Equal(t, d, value)
_, err = Datetime{}.Validate(123)
assert.NotNil(t, err)
}
func TestSerial(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
expect.Create("SERIAL", Serial{})
expect.Create("SERIAL PRIMARY KEY", Serial{PrimaryKey: true})
expect.Create(
"SERIAL PRIMARY KEY NOT NULL",
Serial{PrimaryKey: true, NotNull: true},
)
value, err := Serial{}.Validate(123)
assert.Nil(t, err)
assert.EqualValues(t, 123, value)
value, err = Serial{}.Validate("123")
assert.Nil(t, err)
assert.EqualValues(t, 123, value)
_, err = Serial{}.Validate("HEY")
assert.NotNil(t, err)
}
func TestInet(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
expect.Create("INET", Inet{})
}
func TestGeoJSON(t *testing.T) {
expect := aspect.NewTester(t, &postgres.PostGres{})
expect.SQL(`ST_AsGeoJSON("shapes"."area")`, AsGeoJSON(shapes.C["area"]))
}
func TestInsert(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
stmt := Insert(
users.C["name"],
users.C["password"],
).Returning(
users.C["id"],
)
// By default, an INSERT without values will assume a single entry
expect.SQL(
`INSERT INTO "users" ("name", "password") VALUES ($1, $2) RETURNING "users"."id"`,
stmt,
nil,
nil,
)
// Adding values should set parameters
admin := user{Name: "admin", Password: "******"}
expect.SQL(
`INSERT INTO "users" ("name", "password") VALUES ($1, $2) RETURNING "users"."id"`,
stmt.Values(admin),
"admin",
"secret",
)
// Insert multiple values
inserts := []user{admin, {Name: "client", Password: "******"}}
expect.SQL(
`INSERT INTO "users" ("name", "password") VALUES ($1, $2), ($3, $4) RETURNING "users"."id"`,
stmt.Values(inserts),
"admin",
"secret",
"client",
"1234",
)
// Statements with a returning clause should be unaffected
expect.SQL(
`INSERT INTO "users" ("name", "password") VALUES ($1, $2)`,
Insert(users.C["name"], users.C["password"]),
nil,
nil,
)
// Insert with a omitted field - empty and non-empty
omitAdmin := omitID{Name: "admin", Password: "******"}
expect.SQL(
`INSERT INTO "users" ("name", "password") VALUES ($1, $2)`,
Insert(users).Values(omitAdmin),
"admin",
"1234",
)
expect.SQL(
`INSERT INTO "users" ("id", "name", "password") VALUES ($1, $2, $3)`,
Insert(users).Values(omitID{ID: 1, Name: "admin", Password: "******"}),
1,
"admin",
"1234",
)
// Return all user table columns
expect.SQL(
`INSERT INTO "users" ("name", "password") VALUES ($1, $2) RETURNING "users"."id", "users"."name", "users"."password", "users"."is_active", "users"."created_at"`,
Insert(users).Values(omitAdmin).Returning(users),
"admin",
"1234",
)
// Selecting a column or table that is not part of the insert table
// should produce an error
expect.Error(Insert(users).Values(omitAdmin).Returning(hasUUIDs))
expect.Error(Insert(users).Values(omitAdmin).Returning(hasUUIDs.C["uuid"]))
}
func TestOperators(t *testing.T) {
expect := aspect.NewTester(t, &PostGres{})
expect.SQL(`"room" WITH =`, Equal.With("room"))
}