tandem
/
numerus
2
0
Fork 0
Code Issues 39 Pull Requests Packages Projects 1 Releases 1 Wiki Activity
numerus/pkg/pgtypes.go

342 lines
9.3 KiB
Go
Raw Permalink Normal View History

Properly register array and composite PostgreSQL types with pgtype I have moved everything into a different file, even though it is related to “db”, because it was starting to get a bit ugly. Apparently i was doing too much work and had to read the code to understand what i was supposed to do, because pgtypes’ documentation, as all other projects from the same author, is almost non-existent.
2023-02-20 10:42:21 +00:00
package pkg
import (
"context"
"fmt"
"github.com/jackc/pgtype"
"github.com/jackc/pgx/v4"
)
Add a cache of OID in database to register types It makes no sense to retrieve the same OIDs each and every connection, because they are not going to change unless the database is reset, something it is very unlikely to happen in production. Thus, it is best to query them the first time the application connects to the database, that it is done at startup to query the available languages, and then reuse the OIDs. I can get away of using an “unprotected” map, instead of sync.Map or a map in tandem with sync.RWMutex, because the application establishes a connection at startup from a single goroutine, and it registers _all_ types we will need to register within the application’s lifespan, hence it there will be no more writes to that map once the web server is listening for incoming connections. This is risky, however, and i hope i do not have to regret it.
2023-10-27 10:44:24 +00:00
var (
oidCache = make(map[string]uint32)
)
Split contact relation into tax_details, phone, web, and email We need to have contacts with just a name: we need to assign freelancer’s quote as expense linked the government, but of course we do not have a phone or email for that “contact”, much less a VATIN or other tax details. It is also interesting for other expenses-only contacts to not have to input all tax details, as we may not need to invoice then, thus are useless for us, but sometimes it might be interesting to have them, “just in case”. Of course, i did not want to make nullable any of the tax details required to generate an invoice, otherwise we could allow illegal invoices. Therefore, that data had to go in a different relation, and invoice’s foreign key update to point to that relation, not just customer, or we would again be able to create invalid invoices. We replaced the contact’s trade name with just name, because we do not need _three_ names for a contact, but we _do_ need two: the one we use to refer to them and the business name for tax purposes. The new contact_phone, contact_web, and contact_email relations could be simply a nullable field, but i did not see the point, since there are not that many instances where i need any of this data. Now company.taxDetailsForm is no longer “the same as contactForm with some extra fields”, because i have to add a check whether the user needs to invoice the contact, to check that the required values are there. I have an additional problem with the contact form when not using JavaScript: i must set the required field to all tax details fields to avoid the “(optional)” suffix, and because they _are_ required when that checkbox is enabled, but i can not set them optional when the check is unchecked. My solution for now is to ignore the form validation, and later i will add some JavaScript that adds the validation again, so it will work in all cases.
2023-06-30 19:32:48 +00:00
type CustomerTaxDetails struct {
BusinessName string
VATIN string
Address string
City string
Province string
PostalCode string
CountryCode string
}
func (src CustomerTaxDetails) EncodeBinary(ci *pgtype.ConnInfo, buf []byte) ([]byte, error) {
typeName := "tax_details"
dt, ok := ci.DataTypeForName(typeName)
if !ok {
return nil, fmt.Errorf("unable to find oid for type name %v", typeName)
}
values := []interface{}{
src.BusinessName,
src.VATIN,
src.Address,
src.City,
src.Province,
src.PostalCode,
src.CountryCode,
}
ct := pgtype.NewValue(dt.Value).(pgtype.ValueTranscoder)
if err := ct.Set(values); err != nil {
return nil, err
}
return ct.EncodeBinary(ci, buf)
}
Properly register array and composite PostgreSQL types with pgtype I have moved everything into a different file, even though it is related to “db”, because it was starting to get a bit ugly. Apparently i was doing too much work and had to read the code to understand what i was supposed to do, because pgtypes’ documentation, as all other projects from the same author, is almost non-existent.
2023-02-20 10:42:21 +00:00
type NewInvoiceProductArray []*invoiceProductForm
func (src NewInvoiceProductArray) EncodeBinary(ci *pgtype.ConnInfo, buf []byte) ([]byte, error) {
typeName := "new_invoice_product[]"
dt, ok := ci.DataTypeForName(typeName)
if !ok {
return nil, fmt.Errorf("unable to find oid for type name %v", typeName)
}
var values [][]interface{}
for _, form := range src {
Accept “invalid” quantity, price, and discount on invoice update This is for “free products”, where the user adds an invoice row, does not select a product from the search, and clicks the update button. Numerus should select “appropriate” values for those that are left unspecified. I also no longer require the product_id to be an integer; if it is empty, then it is assumed to be a “free product”.
2023-04-24 18:40:10 +00:00
var productId interface{} = form.ProductId.Val
if form.ProductId.Val == "" {
productId = nil
}
Properly register array and composite PostgreSQL types with pgtype I have moved everything into a different file, even though it is related to “db”, because it was starting to get a bit ugly. Apparently i was doing too much work and had to read the code to understand what i was supposed to do, because pgtypes’ documentation, as all other projects from the same author, is almost non-existent.
2023-02-20 10:42:21 +00:00
values = append(values, []interface{}{
Accept “invalid” quantity, price, and discount on invoice update This is for “free products”, where the user adds an invoice row, does not select a product from the search, and clicks the update button. Numerus should select “appropriate” values for those that are left unspecified. I also no longer require the product_id to be an integer; if it is empty, then it is assumed to be a “free product”.
2023-04-24 18:40:10 +00:00
productId,
Properly register array and composite PostgreSQL types with pgtype I have moved everything into a different file, even though it is related to “db”, because it was starting to get a bit ugly. Apparently i was doing too much work and had to read the code to understand what i was supposed to do, because pgtypes’ documentation, as all other projects from the same author, is almost non-existent.
2023-02-20 10:42:21 +00:00
form.Name.Val,
form.Description.Val,
form.Price.Val,
form.Quantity.Val,
form.Discount.Float64() / 100.0,
form.Tax.Selected,
})
}
array := pgtype.NewValue(dt.Value).(pgtype.ValueTranscoder)
if err := array.Set(values); err != nil {
return nil, err
}
return array.EncodeBinary(ci, buf)
}
Add the edit form for invoices I had to change the way /invoices/new and /invoices/batch are handled, because httprouter was not happy with the new POST /invoices/:slug/edit route, claiming that /invoices/:slug conflicts with the previously existing routes. I also could not make it work with the PATCH method, even though i correctly added the patchMethod override function, therefore editing invoices is also weird because i have to take into account the “quick” invoice status change. I use the same form for both new and edit invoices, because the only changes are that we can not edit the invoice date and number, by Oriol’s design, but must be able to change the status; very similar forms.
2023-03-13 14:00:35 +00:00
type EditedInvoiceProductArray []*invoiceProductForm
func (src EditedInvoiceProductArray) EncodeBinary(ci *pgtype.ConnInfo, buf []byte) ([]byte, error) {
typeName := "edited_invoice_product[]"
dt, ok := ci.DataTypeForName(typeName)
if !ok {
return nil, fmt.Errorf("unable to find oid for type name %v", typeName)
}
var values [][]interface{}
for _, form := range src {
values = append(values, []interface{}{
Use a null as product ID when adding new products to invoices Otherwise, pgx (rightfully) tries to convert a "" into a integer, as this is the field’s type, cannot, and panics with an error. Added a IntegerOrNull method to FormField because this is exactly the same that happens with the invoiceProductId, and made no sense to have to do the logic twice, or in a function inside form.
2023-05-22 09:06:06 +00:00
form.InvoiceProductId.IntegerOrNil(),
form.ProductId.IntegerOrNil(),
Add the edit form for invoices I had to change the way /invoices/new and /invoices/batch are handled, because httprouter was not happy with the new POST /invoices/:slug/edit route, claiming that /invoices/:slug conflicts with the previously existing routes. I also could not make it work with the PATCH method, even though i correctly added the patchMethod override function, therefore editing invoices is also weird because i have to take into account the “quick” invoice status change. I use the same form for both new and edit invoices, because the only changes are that we can not edit the invoice date and number, by Oriol’s design, but must be able to change the status; very similar forms.
2023-03-13 14:00:35 +00:00
form.Name.Val,
form.Description.Val,
form.Price.Val,
form.Quantity.Val,
form.Discount.Float64() / 100.0,
form.Tax.Selected,
})
}
array := pgtype.NewValue(dt.Value).(pgtype.ValueTranscoder)
if err := array.Set(values); err != nil {
return nil, err
}
return array.EncodeBinary(ci, buf)
}
Add HTTP controller and view to add quotes It still does not support quotes without contact or payment.
2023-06-07 14:35:31 +00:00
type NewQuoteProductArray []*quoteProductForm
func (src NewQuoteProductArray) EncodeBinary(ci *pgtype.ConnInfo, buf []byte) ([]byte, error) {
typeName := "new_quote_product[]"
dt, ok := ci.DataTypeForName(typeName)
if !ok {
return nil, fmt.Errorf("unable to find oid for type name %v", typeName)
}
var values [][]interface{}
for _, form := range src {
var productId interface{} = form.ProductId.Val
if form.ProductId.Val == "" {
productId = nil
}
values = append(values, []interface{}{
productId,
form.Name.Val,
form.Description.Val,
form.Price.Val,
form.Quantity.Val,
form.Discount.Float64() / 100.0,
form.Tax.Selected,
})
}
array := pgtype.NewValue(dt.Value).(pgtype.ValueTranscoder)
if err := array.Set(values); err != nil {
return nil, err
}
return array.EncodeBinary(ci, buf)
}
type EditedQuoteProductArray []*quoteProductForm
func (src EditedQuoteProductArray) EncodeBinary(ci *pgtype.ConnInfo, buf []byte) ([]byte, error) {
typeName := "edited_quote_product[]"
dt, ok := ci.DataTypeForName(typeName)
if !ok {
return nil, fmt.Errorf("unable to find oid for type name %v", typeName)
}
var values [][]interface{}
for _, form := range src {
values = append(values, []interface{}{
form.QuoteProductId.IntegerOrNil(),
form.ProductId.IntegerOrNil(),
form.Name.Val,
form.Description.Val,
form.Price.Val,
form.Quantity.Val,
form.Discount.Float64() / 100.0,
form.Tax.Selected,
})
}
array := pgtype.NewValue(dt.Value).(pgtype.ValueTranscoder)
if err := array.Set(values); err != nil {
return nil, err
}
return array.EncodeBinary(ci, buf)
}
Properly register array and composite PostgreSQL types with pgtype I have moved everything into a different file, even though it is related to “db”, because it was starting to get a bit ugly. Apparently i was doing too much work and had to read the code to understand what i was supposed to do, because pgtypes’ documentation, as all other projects from the same author, is almost non-existent.
2023-02-20 10:42:21 +00:00
func registerPgTypes(ctx context.Context, conn *pgx.Conn) error {
Temporarily switch to admin role to register types The authenticator role does not have access to the numerus schema, so it can not see the oid of discount_rate, for instance.
2023-02-24 12:17:37 +00:00
if _, err := conn.Exec(ctx, "set role to admin"); err != nil {
return err
}
Replace tag relations with array attributes It all started when i wanted to try to filter invoices by multiple tags using an “AND”, instead of “OR” as it was doing until now. But something felt off and seemed to me that i was doing thing much more complex than needed, all to be able to list the tags as a suggestion in the input field—which i am not doing yet. I found this article series[0] exploring different approaches for tagging, which includes the one i was using, and comparing their performance. I have not actually tested it, but it seems that i have chosen the worst option, in both query time and storage. I attempted to try using an array attribute to each table, which is more or less the same they did in the articles but without using a separate relation for tags, and i found out that all the queries were way easier to write, and needed two joins less, so it was a no-brainer. [0]: http://www.databasesoup.com/2015/01/tag-all-things.html
2023-04-07 19:31:35 +00:00
tagNameOID, err := registerPgType(ctx, conn, &pgtype.Text{}, "tag_name")
if err != nil {
return err
}
tagNameArray := pgtype.NewArrayType("tag_name[]", tagNameOID, func() pgtype.ValueTranscoder {
return &pgtype.Text{}
})
if _, err := registerPgType(ctx, conn, tagNameArray, tagNameArray.TypeName()); err != nil {
return err
}
Properly register array and composite PostgreSQL types with pgtype I have moved everything into a different file, even though it is related to “db”, because it was starting to get a bit ugly. Apparently i was doing too much work and had to read the code to understand what i was supposed to do, because pgtypes’ documentation, as all other projects from the same author, is almost non-existent.
2023-02-20 10:42:21 +00:00
discountRateOID, err := registerPgType(ctx, conn, &pgtype.Numeric{}, "discount_rate")
if err != nil {
return err
}
Add HTTP controller and view to add quotes It still does not support quotes without contact or payment.
2023-06-07 14:35:31 +00:00
Properly register array and composite PostgreSQL types with pgtype I have moved everything into a different file, even though it is related to “db”, because it was starting to get a bit ugly. Apparently i was doing too much work and had to read the code to understand what i was supposed to do, because pgtypes’ documentation, as all other projects from the same author, is almost non-existent.
2023-02-20 10:42:21 +00:00
newInvoiceProduct, err := pgtype.NewCompositeType(
"new_invoice_product",
[]pgtype.CompositeTypeField{
{"product_id", pgtype.Int4OID},
{"name", pgtype.TextOID},
{"description", pgtype.TextOID},
{"price", pgtype.TextOID},
{"quantity", pgtype.Int4OID},
{"discount_rate", discountRateOID},
{"tax", pgtype.Int4ArrayOID},
},
conn.ConnInfo(),
)
if err != nil {
return err
}
newInvoiceProductOID, err := registerPgType(ctx, conn, newInvoiceProduct, newInvoiceProduct.TypeName())
if err != nil {
return err
}
newInvoiceProductArray := pgtype.NewArrayType("new_invoice_product[]", newInvoiceProductOID, func() pgtype.ValueTranscoder {
value := newInvoiceProduct.NewTypeValue()
return value.(pgtype.ValueTranscoder)
})
_, err = registerPgType(ctx, conn, newInvoiceProductArray, newInvoiceProductArray.TypeName())
if err != nil {
return err
}
Temporarily switch to admin role to register types The authenticator role does not have access to the numerus schema, so it can not see the oid of discount_rate, for instance.
2023-02-24 12:17:37 +00:00
Add the edit form for invoices I had to change the way /invoices/new and /invoices/batch are handled, because httprouter was not happy with the new POST /invoices/:slug/edit route, claiming that /invoices/:slug conflicts with the previously existing routes. I also could not make it work with the PATCH method, even though i correctly added the patchMethod override function, therefore editing invoices is also weird because i have to take into account the “quick” invoice status change. I use the same form for both new and edit invoices, because the only changes are that we can not edit the invoice date and number, by Oriol’s design, but must be able to change the status; very similar forms.
2023-03-13 14:00:35 +00:00
editedInvoiceProduct, err := pgtype.NewCompositeType(
"edited_invoice_product",
[]pgtype.CompositeTypeField{
{"invoice_product_id", pgtype.Int4OID},
{"product_id", pgtype.Int4OID},
{"name", pgtype.TextOID},
{"description", pgtype.TextOID},
{"price", pgtype.TextOID},
{"quantity", pgtype.Int4OID},
{"discount_rate", discountRateOID},
{"tax", pgtype.Int4ArrayOID},
},
conn.ConnInfo(),
)
if err != nil {
return err
}
editedInvoiceProductOID, err := registerPgType(ctx, conn, editedInvoiceProduct, editedInvoiceProduct.TypeName())
if err != nil {
return err
}
editedInvoiceProductArray := pgtype.NewArrayType("edited_invoice_product[]", editedInvoiceProductOID, func() pgtype.ValueTranscoder {
value := editedInvoiceProduct.NewTypeValue()
return value.(pgtype.ValueTranscoder)
})
_, err = registerPgType(ctx, conn, editedInvoiceProductArray, editedInvoiceProductArray.TypeName())
if err != nil {
return err
}
Add HTTP controller and view to add quotes It still does not support quotes without contact or payment.
2023-06-07 14:35:31 +00:00
newQuoteProduct, err := pgtype.NewCompositeType(
"new_quote_product",
[]pgtype.CompositeTypeField{
{"product_id", pgtype.Int4OID},
{"name", pgtype.TextOID},
{"description", pgtype.TextOID},
{"price", pgtype.TextOID},
{"quantity", pgtype.Int4OID},
{"discount_rate", discountRateOID},
{"tax", pgtype.Int4ArrayOID},
},
conn.ConnInfo(),
)
if err != nil {
return err
}
newQuoteProductOID, err := registerPgType(ctx, conn, newQuoteProduct, newQuoteProduct.TypeName())
if err != nil {
return err
}
newQuoteProductArray := pgtype.NewArrayType("new_quote_product[]", newQuoteProductOID, func() pgtype.ValueTranscoder {
value := newQuoteProduct.NewTypeValue()
return value.(pgtype.ValueTranscoder)
})
_, err = registerPgType(ctx, conn, newQuoteProductArray, newQuoteProductArray.TypeName())
if err != nil {
return err
}
editedQuoteProduct, err := pgtype.NewCompositeType(
"edited_quote_product",
[]pgtype.CompositeTypeField{
{"quote_product_id", pgtype.Int4OID},
{"product_id", pgtype.Int4OID},
{"name", pgtype.TextOID},
{"description", pgtype.TextOID},
{"price", pgtype.TextOID},
{"quantity", pgtype.Int4OID},
{"discount_rate", discountRateOID},
{"tax", pgtype.Int4ArrayOID},
},
conn.ConnInfo(),
)
if err != nil {
return err
}
editedQuoteProductOID, err := registerPgType(ctx, conn, editedQuoteProduct, editedQuoteProduct.TypeName())
if err != nil {
return err
}
editedQuoteProductArray := pgtype.NewArrayType("edited_quote_product[]", editedQuoteProductOID, func() pgtype.ValueTranscoder {
value := editedQuoteProduct.NewTypeValue()
return value.(pgtype.ValueTranscoder)
})
_, err = registerPgType(ctx, conn, editedQuoteProductArray, editedQuoteProductArray.TypeName())
if err != nil {
return err
}
Split contact relation into tax_details, phone, web, and email We need to have contacts with just a name: we need to assign freelancer’s quote as expense linked the government, but of course we do not have a phone or email for that “contact”, much less a VATIN or other tax details. It is also interesting for other expenses-only contacts to not have to input all tax details, as we may not need to invoice then, thus are useless for us, but sometimes it might be interesting to have them, “just in case”. Of course, i did not want to make nullable any of the tax details required to generate an invoice, otherwise we could allow illegal invoices. Therefore, that data had to go in a different relation, and invoice’s foreign key update to point to that relation, not just customer, or we would again be able to create invalid invoices. We replaced the contact’s trade name with just name, because we do not need _three_ names for a contact, but we _do_ need two: the one we use to refer to them and the business name for tax purposes. The new contact_phone, contact_web, and contact_email relations could be simply a nullable field, but i did not see the point, since there are not that many instances where i need any of this data. Now company.taxDetailsForm is no longer “the same as contactForm with some extra fields”, because i have to add a check whether the user needs to invoice the contact, to check that the required values are there. I have an additional problem with the contact form when not using JavaScript: i must set the required field to all tax details fields to avoid the “(optional)” suffix, and because they _are_ required when that checkbox is enabled, but i can not set them optional when the check is unchecked. My solution for now is to ignore the form validation, and later i will add some JavaScript that adds the validation again, so it will work in all cases.
2023-06-30 19:32:48 +00:00
countryCodeOID, err := registerPgType(ctx, conn, &pgtype.Text{}, "country_code")
if err != nil {
return err
}
taxDetailsType, err := pgtype.NewCompositeType(
"tax_details",
[]pgtype.CompositeTypeField{
{"business_name", pgtype.TextOID},
{"vatin", pgtype.TextOID},
{"address", pgtype.TextOID},
{"city", pgtype.TextOID},
{"province", pgtype.TextOID},
{"postal_code", pgtype.TextOID},
{"discount_rate", countryCodeOID},
},
conn.ConnInfo(),
)
if err != nil {
return err
}
_, err = registerPgType(ctx, conn, taxDetailsType, taxDetailsType.TypeName())
if err != nil {
return err
}
Temporarily switch to admin role to register types The authenticator role does not have access to the numerus schema, so it can not see the oid of discount_rate, for instance.
2023-02-24 12:17:37 +00:00
_, err = conn.Exec(ctx, "reset role")
return err
Properly register array and composite PostgreSQL types with pgtype I have moved everything into a different file, even though it is related to “db”, because it was starting to get a bit ugly. Apparently i was doing too much work and had to read the code to understand what i was supposed to do, because pgtypes’ documentation, as all other projects from the same author, is almost non-existent.
2023-02-20 10:42:21 +00:00
}
func registerPgType(ctx context.Context, conn *pgx.Conn, value pgtype.Value, name string) (oid uint32, err error) {
Add a cache of OID in database to register types It makes no sense to retrieve the same OIDs each and every connection, because they are not going to change unless the database is reset, something it is very unlikely to happen in production. Thus, it is best to query them the first time the application connects to the database, that it is done at startup to query the available languages, and then reuse the OIDs. I can get away of using an “unprotected” map, instead of sync.Map or a map in tandem with sync.RWMutex, because the application establishes a connection at startup from a single goroutine, and it registers _all_ types we will need to register within the application’s lifespan, hence it there will be no more writes to that map once the web server is listening for incoming connections. This is risky, however, and i hope i do not have to regret it.
2023-10-27 10:44:24 +00:00
var found bool
if oid, found = oidCache[name]; !found {
if err = conn.QueryRow(ctx, "select $1::regtype::oid", name).Scan(&oid); err != nil {
return
}
oidCache[name] = oid
Properly register array and composite PostgreSQL types with pgtype I have moved everything into a different file, even though it is related to “db”, because it was starting to get a bit ugly. Apparently i was doing too much work and had to read the code to understand what i was supposed to do, because pgtypes’ documentation, as all other projects from the same author, is almost non-existent.
2023-02-20 10:42:21 +00:00
}
conn.ConnInfo().RegisterDataType(pgtype.DataType{Value: value, Name: name, OID: oid})
return
}