func TestColumn_DefaultOn(t *testing.T) { success := []struct { d string e []pqt.Event }{ { d: "NOW()", e: []pqt.Event{pqt.EventUpdate}, }, } for _, data := range success { c := pqt.NewColumn("column", pqt.TypeTimestampTZ(), pqt.WithDefault(data.d, data.e...)) EventLoop: for _, e := range data.e { d, ok := c.DefaultOn(e) if !ok { t.Errorf("missing default value for %s", e) continue EventLoop } if d != data.d { t.Errorf("wrong value, expected %s but got %s", data.d, d) } } } }
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) } } }