func schema(sn string) *pqt.Schema {
title := pqt.NewColumn("title", pqt.TypeText(), pqt.WithNotNull(), pqt.WithUnique())
lead := pqt.NewColumn("lead", pqt.TypeText())
news := pqt.NewTable("news", pqt.WithTableIfNotExists()).
AddColumn(pqt.NewColumn("id", pqt.TypeSerialBig(), pqt.WithPrimaryKey())).
AddColumn(title).
AddColumn(lead).
AddColumn(pqt.NewColumn("continue", pqt.TypeBool(), pqt.WithNotNull(), pqt.WithDefault("false"))).
AddColumn(pqt.NewColumn("content", pqt.TypeText(), pqt.WithNotNull())).
AddUnique(title, lead)
comment := pqt.NewTable("comment", pqt.WithTableIfNotExists()).
AddColumn(pqt.NewColumn("id", pqt.TypeSerialBig())).
AddColumn(pqt.NewColumn("content", pqt.TypeText(), pqt.WithNotNull())).
AddColumn(pqt.NewColumn(
"news_title",
pqt.TypeText(),
pqt.WithNotNull(),
pqt.WithReference(title, pqt.WithBidirectional(), pqt.WithOwnerName("comments_by_news_title"), pqt.WithInversedName("news_by_title")),
))
category := pqt.NewTable("category", pqt.WithTableIfNotExists()).
AddColumn(pqt.NewColumn("id", pqt.TypeSerialBig(), pqt.WithPrimaryKey())).
AddColumn(pqt.NewColumn("name", pqt.TypeText(), pqt.WithNotNull())).
AddColumn(pqt.NewColumn("content", pqt.TypeText(), pqt.WithNotNull())).
AddRelationship(
pqt.OneToMany(
pqt.SelfReference(),
pqt.WithBidirectional(),
pqt.WithInversedName("child_category"),
pqt.WithOwnerName("parent_category"),
pqt.WithColumnName("parent_id"),
),
)
pkg := pqt.NewTable("package", pqt.WithTableIfNotExists()).
AddColumn(pqt.NewColumn("id", pqt.TypeSerialBig(), pqt.WithPrimaryKey())).
AddColumn(pqt.NewColumn("break", pqt.TypeText())).
AddRelationship(pqt.ManyToOne(
category,
pqt.WithBidirectional(),
))
timestampable(news)
timestampable(comment)
timestampable(category)
timestampable(pkg)
comment.AddRelationship(pqt.ManyToOne(news, pqt.WithBidirectional(), pqt.WithInversedName("news_by_id")), pqt.WithNotNull())
pqt.ManyToMany(category, news, pqt.WithBidirectional())
return pqt.NewSchema(sn, pqt.WithSchemaIfNotExists()).
AddTable(category).
AddTable(pkg).
AddTable(news).
AddTable(comment)
}
func TestNewColumn(t *testing.T) {
collate := "UTF-7"
check := "username = '******'"
r := pqt.NewColumn("username", pqt.TypeText())
c := pqt.NewColumn(
"user_username",
pqt.TypeText(),
pqt.WithCollate(collate),
pqt.WithCheck(check),
pqt.WithDefault("janusz"),
pqt.WithUnique(),
pqt.WithTypeMapping(pqtgo.BuiltinType(types.Byte)),
pqt.WithNotNull(),
pqt.WithPrimaryKey(),
pqt.WithReference(r),
)
if c.Type.String() != pqt.TypeText().String() {
t.Errorf("wrong column type, expected %s but got %s", pqt.TypeText().String(), c.Type.String())
}
if c.Collate != collate {
t.Errorf("wrong column collate, expected %s but got %s", collate, c.Collate)
}
if c.Check != check {
t.Errorf("wrong column check, expected %s but got %s", check, c.Check)
}
if d, ok := c.Default[pqt.EventInsert]; ok && d != "janusz" {
t.Errorf("wrong column default, expected %s but got %s", "janusz", d)
}
if !c.Unique {
t.Error("wrong column unique, expected true but got false")
}
if !c.NotNull {
t.Error("wrong column not null, expected true but got false")
}
if !c.PrimaryKey {
t.Error("wrong column primary key, expected true but got false")
}
if c.Reference != r {
t.Errorf("wrong column reference, expected %p but got %p", r, c.Reference)
}
constraints := c.Constraints()
if len(constraints) != 3 {
t.Errorf("wrong number of constraints, expected 3 but got %d", len(constraints))
}
var hasPK, hasFK, hasCH bool
for _, constraint := range constraints {
switch constraint.Type {
case pqt.ConstraintTypePrimaryKey:
hasPK = true
case pqt.ConstraintTypeForeignKey:
hasFK = true
case pqt.ConstraintTypeCheck:
hasCH = true
}
}
if !hasPK {
t.Errorf("mising primary key constraint")
}
if !hasFK {
t.Errorf("mising foreign key constraint")
}
if !hasCH {
t.Errorf("mising check constraint")
}
}
func timestampable(t *pqt.Table) {
t.AddColumn(pqt.NewColumn("created_at", pqt.TypeTimestampTZ(), pqt.WithNotNull(), pqt.WithDefault("NOW()"))).
AddColumn(pqt.NewColumn("updated_at", pqt.TypeTimestampTZ(), pqt.WithDefault("NOW()", pqt.EventUpdate)))
}
func TestGenerator_Generate(t *testing.T) {
success := []struct {
expected string
given *pqt.Table
}{
{
expected: `-- do not modify, generated by pqt
CREATE TEMPORARY TABLE schema.user (
created_at TIMESTAMPTZ,
password TEXT,
username TEXT NOT NULL
);
`,
given: func() *pqt.Table {
return pqt.NewTable("user", pqt.WithTemporary()).
SetSchema(pqt.NewSchema("schema")).
AddColumn(&pqt.Column{Name: "username", Type: pqt.TypeText(), NotNull: true}).
AddColumn(&pqt.Column{Name: "password", Type: pqt.TypeText()}).
AddColumn(&pqt.Column{Name: "created_at", Type: pqt.TypeTimestampTZ()})
}(),
},
{
expected: `-- do not modify, generated by pqt
CREATE TABLE IF NOT EXISTS table_name (
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
created_by INTEGER NOT NULL,
enabled BOOL,
end_at TIMESTAMPTZ NOT NULL,
id SERIAL,
name TEXT,
price DECIMAL(10,1),
rel_id INTEGER,
slug TEXT NOT NULL,
start_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ,
updated_by INTEGER,
CONSTRAINT "public.table_name_id_pkey" PRIMARY KEY (id),
CONSTRAINT "public.table_name_name_key" UNIQUE (name),
CONSTRAINT "public.table_name_slug_key" UNIQUE (slug),
CONSTRAINT "public.table_name_rel_id_fkey" FOREIGN KEY (rel_id) REFERENCES related_table (id),
CONSTRAINT "public.table_name_start_at_end_at_check" CHECK ((start_at IS NULL AND end_at IS NULL) OR start_at < end_at)
);
`,
given: func() *pqt.Table {
id := pqt.Column{Name: "id", Type: pqt.TypeSerial()}
startAt := &pqt.Column{Name: "start_at", Type: pqt.TypeTimestampTZ(), NotNull: true}
endAt := &pqt.Column{Name: "end_at", Type: pqt.TypeTimestampTZ(), NotNull: true}
_ = pqt.NewTable("related_table").
AddColumn(&id)
return pqt.NewTable("table_name", pqt.WithTableIfNotExists()).
AddColumn(&pqt.Column{Name: "id", Type: pqt.TypeSerial(), PrimaryKey: true}).
AddColumn(&pqt.Column{Name: "rel_id", Type: pqt.TypeInteger(), Reference: &id}).
AddColumn(&pqt.Column{Name: "name", Type: pqt.TypeText(), Unique: true}).
AddColumn(&pqt.Column{Name: "enabled", Type: pqt.TypeBool()}).
AddColumn(&pqt.Column{Name: "price", Type: pqt.TypeDecimal(10, 1)}).
AddColumn(startAt).
AddColumn(endAt).
AddColumn(pqt.NewColumn("created_at", pqt.TypeTimestampTZ(), pqt.WithNotNull(), pqt.WithDefault("NOW()"))).
AddColumn(&pqt.Column{Name: "created_by", Type: pqt.TypeInteger(), NotNull: true}).
AddColumn(&pqt.Column{Name: "updated_at", Type: pqt.TypeTimestampTZ()}).
AddColumn(&pqt.Column{Name: "updated_by", Type: pqt.TypeInteger()}).
AddColumn(&pqt.Column{Name: "slug", Type: pqt.TypeText(), NotNull: true, Unique: true}).
AddCheck("(start_at IS NULL AND end_at IS NULL) OR start_at < end_at", startAt, endAt)
}(),
},
}
for i, data := range success {
q, err := pqtsql.NewGenerator().Generate(&pqt.Schema{
Tables: []*pqt.Table{data.given},
})
if err != nil {
t.Errorf("unexpected error for schema #%d : %s", i, err.Error())
continue
}
if string(q) != data.expected {
t.Errorf("wrong query, expected:\n'%s'\nbut got:\n'%s'", data.expected, q)
}
}
}
