Add tags for contacts too
With Oriol we agreed that contacts should have tags, too, and that the
“tag pool”, as it were, should be shared with the one for invoices (and
all future tags we might add).
I added the contact_tag relation and tag_contact function, just like
with invoices, and then realized that the SQL queries that Go had to
execute were becoming “complex” enough: i had to get not only the slug,
but the contact id to call tag_contact, and all inside a transaction.
Therefore, i opted to create the add_contact and edit_contact functions,
that mirror those for invoice and products, so now each “major” section
has these functions. They also simplified a bit the handling of the
VATIN and phone numbers, because it is now encapsuled inside the
PL/pgSQL function and Go does not know how to assemble the parts.
2023-03-26 00:32:53 +00:00
|
|
|
-- Deploy numerus:edit_contact to pg
|
|
|
|
|
-- requires: schema_numerus
|
|
|
|
|
-- requires: email
|
|
|
|
|
-- requires: extension_uri
|
|
|
|
|
-- requires: country_code
|
|
|
|
|
-- requires: tag_name
|
|
|
|
|
-- requires: contact
|
|
|
|
|
-- requires: extension_vat
|
|
|
|
|
-- requires: extension_pg_libphonenumber
|
|
|
|
|
|
|
|
|
|
begin;
|
|
|
|
|
|
|
|
|
|
set search_path to numerus, public;
|
|
|
|
|
|
|
|
|
|
create or replace function edit_contact(contact_slug uuid, business_name text, vatin text, trade_name text, phone text, email email, web uri, address text, city text, province text, postal_code text, country_code country_code, tags tag_name[]) returns uuid as
|
|
|
|
|
$$
|
|
|
|
|
declare
|
|
|
|
|
cid integer;
|
|
|
|
|
company integer;
|
|
|
|
|
begin
|
|
|
|
|
update contact
|
|
|
|
|
set business_name = edit_contact.business_name
|
|
|
|
|
, vatin = (edit_contact.country_code || edit_contact.vatin)::vatin
|
|
|
|
|
, trade_name = edit_contact.trade_name
|
|
|
|
|
, phone = parse_packed_phone_number( edit_contact.phone, edit_contact.country_code)
|
|
|
|
|
, email = edit_contact.email
|
|
|
|
|
, web = edit_contact.web
|
|
|
|
|
, address = edit_contact.address
|
|
|
|
|
, city = edit_contact.city
|
|
|
|
|
, province = edit_contact.province
|
|
|
|
|
, postal_code = edit_contact.postal_code
|
|
|
|
|
, country_code = edit_contact.country_code
|
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
|
|
|
, tags = edit_contact.tags
|
Add tags for contacts too
With Oriol we agreed that contacts should have tags, too, and that the
“tag pool”, as it were, should be shared with the one for invoices (and
all future tags we might add).
I added the contact_tag relation and tag_contact function, just like
with invoices, and then realized that the SQL queries that Go had to
execute were becoming “complex” enough: i had to get not only the slug,
but the contact id to call tag_contact, and all inside a transaction.
Therefore, i opted to create the add_contact and edit_contact functions,
that mirror those for invoice and products, so now each “major” section
has these functions. They also simplified a bit the handling of the
VATIN and phone numbers, because it is now encapsuled inside the
PL/pgSQL function and Go does not know how to assemble the parts.
2023-03-26 00:32:53 +00:00
|
|
|
where slug = contact_slug
|
|
|
|
|
returning contact_id, company_id
|
|
|
|
|
into cid, company
|
|
|
|
|
;
|
|
|
|
|
|
|
|
|
|
if cid is null then
|
|
|
|
|
return null;
|
|
|
|
|
end if;
|
|
|
|
|
|
|
|
|
|
return contact_slug;
|
|
|
|
|
end
|
|
|
|
|
$$
|
|
|
|
|
language plpgsql
|
|
|
|
|
;
|
|
|
|
|
|
|
|
|
|
revoke execute on function edit_contact(uuid, text, text, text, text, email, uri, text, text, text, text, country_code, tag_name[]) from public;
|
|
|
|
|
grant execute on function edit_contact(uuid, text, text, text, text, email, uri, text, text, text, text, country_code, tag_name[]) to invoicer;
|
|
|
|
|
grant execute on function edit_contact(uuid, text, text, text, text, email, uri, text, text, text, text, country_code, tag_name[]) to admin;
|
|
|
|
|
|
|
|
|
|
commit;
|
