tandem
/
numerus
2
0
Fork 0
Code Issues 39 Pull Requests Packages Projects 1 Releases 1 Wiki Activity
numerus/test/edit_contact.sql

72 lines
4.4 KiB
MySQL
Raw Permalink Normal View History

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
-- Test edit_contact
set client_min_messages to warning;
create extension if not exists pgtap;
reset client_min_messages;
begin;
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
select plan(12);
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
set search_path to auth, numerus, public;
select has_function('numerus', 'edit_contact', array ['uuid', 'text', 'text', 'text', 'text', 'email', 'uri', 'text', 'text', 'text', 'text', 'country_code', 'tag_name[]']);
select function_lang_is('numerus', 'edit_contact', array ['uuid', 'text', 'text', 'text', 'text', 'email', 'uri', 'text', 'text', 'text', 'text', 'country_code', 'tag_name[]'], 'plpgsql');
select function_returns('numerus', 'edit_contact', array ['uuid', 'text', 'text', 'text', 'text', 'email', 'uri', 'text', 'text', 'text', 'text', 'country_code', 'tag_name[]'], 'uuid');
select isnt_definer('numerus', 'edit_contact', array ['uuid', 'text', 'text', 'text', 'text', 'email', 'uri', 'text', 'text', 'text', 'text', 'country_code', 'tag_name[]']);
select volatility_is('numerus', 'edit_contact', array ['uuid', 'text', 'text', 'text', 'text', 'email', 'uri', 'text', 'text', 'text', 'text', 'country_code', 'tag_name[]'], 'volatile');
select function_privs_are('numerus', 'edit_contact', array ['uuid', 'text', 'text', 'text', 'text', 'email', 'uri', 'text', 'text', 'text', 'text', 'country_code', 'tag_name[]'], 'guest', array []::text[]);
select function_privs_are('numerus', 'edit_contact', array ['uuid', 'text', 'text', 'text', 'text', 'email', 'uri', 'text', 'text', 'text', 'text', 'country_code', 'tag_name[]'], 'invoicer', array ['EXECUTE']);
select function_privs_are('numerus', 'edit_contact', array ['uuid', 'text', 'text', 'text', 'text', 'email', 'uri', 'text', 'text', 'text', 'text', 'country_code', 'tag_name[]'], 'admin', array ['EXECUTE']);
select function_privs_are('numerus', 'edit_contact', array ['uuid', 'text', 'text', 'text', 'text', 'email', 'uri', 'text', 'text', 'text', 'text', 'country_code', 'tag_name[]'], 'authenticator', array []::text[]);
set client_min_messages to warning;
truncate contact cascade;
truncate payment_method cascade;
truncate company cascade;
reset client_min_messages;
set constraints "company_default_payment_method_id_fkey" deferred;
insert into company (company_id, business_name, vatin, trade_name, phone, email, web, address, city, province, postal_code, country_code, currency_code, default_payment_method_id)
values (1, 'Company 2', 'XX123', '', '555-555-555', 'a@a', '', '', '', '', '', 'ES', 'EUR', 111)
;
insert into payment_method (payment_method_id, company_id, name, instructions)
values (111, 1, 'cash', 'cash')
, (112, 1, 'bank', 'send money to my bank account')
;
set constraints "company_default_payment_method_id_fkey" immediate;
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
insert into contact (contact_id, company_id, slug, business_name, vatin, trade_name, phone, email, web, address, city, province, postal_code, country_code, tags)
values (12, 1, '7ac3ae0e-b0c1-4206-a19b-0be20835edd4', 'Contact 1', 'XX555', '', '777-777-777', 'c@c', '', '', '', '', '', 'ES', '{tag1}')
, (13, 1, 'b57b980b-247b-4be4-a0b7-03a7819c53ae', 'Contact 2', 'XX666', '', '888-888-888', 'd@d', '', '', '', '', '', 'ES', '{tag2}')
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
;
select lives_ok(
$$ select edit_contact('7ac3ae0e-b0c1-4206-a19b-0be20835edd4', 'Contact 2.1', '40404040D', 'Trade Contact 2.1', '999-999-999', 'c1@c1', 'https://c', 'Fake St., 123', 'City 2.1', 'Province 2.1', '19486', 'ES', array['tag1']) $$,
'Should be able to edit the first contact'
);
select lives_ok(
$$ select edit_contact('b57b980b-247b-4be4-a0b7-03a7819c53ae', 'Contact 2.2', '41414141L', 'Trade Contact 2.2', '111-111-111', 'd2@d2', 'https://d', 'Another Fake St., 123', 'City 2.2', 'Province 2.2', '17417', 'ES', array['tag1', 'tag3']) $$,
'Should be able to edit the second contact'
);
select bag_eq(
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
$$ select company_id, business_name, vatin::text, trade_name, phone::text, email::text, web::text, address, city, province, postal_code, country_code::text, tags, created_at from contact $$,
$$ values (1, 'Contact 2.1', 'ES40404040D', 'Trade Contact 2.1', '+34 999 99 99 99', 'c1@c1', 'https://c', 'Fake St., 123', 'City 2.1', 'Province 2.1', '19486', 'ES', '{tag1}'::tag_name[], CURRENT_TIMESTAMP)
, (1, 'Contact 2.2', 'ES41414141L', 'Trade Contact 2.2', '+34 111111111', 'd2@d2', 'https://d', 'Another Fake St., 123', 'City 2.2', 'Province 2.2', '17417', 'ES', '{tag1,tag3}'::tag_name[], CURRENT_TIMESTAMP)
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
$$,
'Should have updated all contacts'
);
select *
from finish();
rollback;