187 lines
7.5 KiB
PL/PgSQL
187 lines
7.5 KiB
PL/PgSQL
begin;
|
|
|
|
create extension "basejump-supabase_test_helpers" version '0.0.6';
|
|
|
|
select no_plan();
|
|
|
|
-- =====================================================
|
|
-- CHECK Constraint Tests
|
|
-- =====================================================
|
|
|
|
-- Setup
|
|
select tests.create_supabase_user('constraint_owner', 'constraint_owner@test.com');
|
|
select makerkit.set_identifier('constraint_owner', 'constraint_owner@test.com');
|
|
|
|
set local role service_role;
|
|
select public.create_team_account('Constraint Verein', tests.get_supabase_uid('constraint_owner'));
|
|
|
|
set local role postgres;
|
|
insert into public.role_permissions (role, permission)
|
|
values ('owner', 'members.write')
|
|
on conflict do nothing;
|
|
|
|
-- -------------------------------------------------------
|
|
-- Test: DOB in future rejected
|
|
-- -------------------------------------------------------
|
|
set local role service_role;
|
|
|
|
select throws_ok(
|
|
$test$ insert into public.members (
|
|
account_id, first_name, last_name, date_of_birth, status, entry_date, created_by, updated_by
|
|
) values (
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'Future', 'Baby', current_date + interval '1 day', 'active', current_date,
|
|
tests.get_supabase_uid('constraint_owner'), tests.get_supabase_uid('constraint_owner')
|
|
) $test$,
|
|
'new row for relation "members" violates check constraint "chk_members_dob_not_future"',
|
|
'Future date of birth is rejected'
|
|
);
|
|
|
|
-- -------------------------------------------------------
|
|
-- Test: Exit date before entry date rejected
|
|
-- -------------------------------------------------------
|
|
select throws_ok(
|
|
$test$ insert into public.members (
|
|
account_id, first_name, last_name, status, entry_date, exit_date, created_by, updated_by
|
|
) values (
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'Wrong', 'Dates', 'resigned', '2024-06-01', '2024-01-01',
|
|
tests.get_supabase_uid('constraint_owner'), tests.get_supabase_uid('constraint_owner')
|
|
) $test$,
|
|
'new row for relation "members" violates check constraint "chk_members_exit_after_entry"',
|
|
'Exit date before entry date is rejected'
|
|
);
|
|
|
|
-- -------------------------------------------------------
|
|
-- Test: Entry date in future rejected
|
|
-- -------------------------------------------------------
|
|
select throws_ok(
|
|
$test$ insert into public.members (
|
|
account_id, first_name, last_name, status, entry_date, created_by, updated_by
|
|
) values (
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'Future', 'Entry', 'active', current_date + interval '2 days',
|
|
tests.get_supabase_uid('constraint_owner'), tests.get_supabase_uid('constraint_owner')
|
|
) $test$,
|
|
'new row for relation "members" violates check constraint "chk_members_entry_not_future"',
|
|
'Future entry date is rejected'
|
|
);
|
|
|
|
-- -------------------------------------------------------
|
|
-- Test: Valid member insert succeeds
|
|
-- -------------------------------------------------------
|
|
select lives_ok(
|
|
$test$ insert into public.members (
|
|
account_id, first_name, last_name, status, entry_date,
|
|
date_of_birth, created_by, updated_by
|
|
) values (
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'Valid', 'Member', 'active', '2024-01-15', '1990-05-20',
|
|
tests.get_supabase_uid('constraint_owner'), tests.get_supabase_uid('constraint_owner')
|
|
) $test$,
|
|
'Valid member with correct dates succeeds'
|
|
);
|
|
|
|
-- -------------------------------------------------------
|
|
-- Test: Duplicate email in same account rejected
|
|
-- -------------------------------------------------------
|
|
insert into public.members (
|
|
account_id, first_name, last_name, email, status, entry_date, created_by, updated_by
|
|
) values (
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'First', 'Email', 'duplicate@test.com', 'active', current_date,
|
|
tests.get_supabase_uid('constraint_owner'), tests.get_supabase_uid('constraint_owner')
|
|
);
|
|
|
|
select throws_ok(
|
|
$test$ insert into public.members (
|
|
account_id, first_name, last_name, email, status, entry_date, created_by, updated_by
|
|
) values (
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'Second', 'Email', 'duplicate@test.com', 'active', current_date,
|
|
tests.get_supabase_uid('constraint_owner'), tests.get_supabase_uid('constraint_owner')
|
|
) $test$,
|
|
'duplicate key value violates unique constraint "uix_members_email_per_account"',
|
|
'Duplicate email in same account is rejected'
|
|
);
|
|
|
|
-- -------------------------------------------------------
|
|
-- Test: NULL emails allowed (multiple)
|
|
-- -------------------------------------------------------
|
|
select lives_ok(
|
|
$test$ insert into public.members (
|
|
account_id, first_name, last_name, email, status, entry_date, created_by, updated_by
|
|
) values (
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'No', 'Email1', null, 'active', current_date,
|
|
tests.get_supabase_uid('constraint_owner'), tests.get_supabase_uid('constraint_owner')
|
|
) $test$,
|
|
'NULL email is allowed'
|
|
);
|
|
|
|
select lives_ok(
|
|
$test$ insert into public.members (
|
|
account_id, first_name, last_name, email, status, entry_date, created_by, updated_by
|
|
) values (
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'No', 'Email2', null, 'active', current_date,
|
|
tests.get_supabase_uid('constraint_owner'), tests.get_supabase_uid('constraint_owner')
|
|
) $test$,
|
|
'Multiple NULL emails allowed'
|
|
);
|
|
|
|
-- -------------------------------------------------------
|
|
-- Test: Invalid IBAN rejected on sepa_mandates
|
|
-- -------------------------------------------------------
|
|
insert into public.members (
|
|
account_id, first_name, last_name, status, entry_date, member_number, created_by, updated_by
|
|
) values (
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'SEPA', 'Test', 'active', current_date, 'SEPA01',
|
|
tests.get_supabase_uid('constraint_owner'), tests.get_supabase_uid('constraint_owner')
|
|
);
|
|
|
|
select throws_ok(
|
|
$test$ insert into public.sepa_mandates (
|
|
member_id, account_id, mandate_reference, iban, account_holder, mandate_date, status
|
|
) values (
|
|
(select id from public.members where first_name = 'SEPA' limit 1),
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'MANDATE-001', 'invalid-iban', 'Test Holder', current_date, 'active'
|
|
) $test$,
|
|
'new row for relation "sepa_mandates" violates check constraint "chk_sepa_iban_format"',
|
|
'Invalid IBAN format is rejected'
|
|
);
|
|
|
|
-- -------------------------------------------------------
|
|
-- Test: Valid IBAN accepted
|
|
-- -------------------------------------------------------
|
|
select lives_ok(
|
|
$test$ insert into public.sepa_mandates (
|
|
member_id, account_id, mandate_reference, iban, account_holder, mandate_date, status
|
|
) values (
|
|
(select id from public.members where first_name = 'SEPA' limit 1),
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'MANDATE-002', 'DE89370400440532013000', 'Test Holder', current_date, 'active'
|
|
) $test$,
|
|
'Valid German IBAN is accepted'
|
|
);
|
|
|
|
-- -------------------------------------------------------
|
|
-- Test: Negative dues amount rejected
|
|
-- -------------------------------------------------------
|
|
select throws_ok(
|
|
$test$ insert into public.dues_categories (
|
|
account_id, name, amount
|
|
) values (
|
|
(select id from public.accounts where slug = 'constraint-verein' limit 1),
|
|
'Negative Fee', -50
|
|
) $test$,
|
|
'new row for relation "dues_categories" violates check constraint "chk_dues_amount_non_negative"',
|
|
'Negative dues amount is rejected'
|
|
);
|
|
|
|
select * from finish();
|
|
|
|
rollback;
|