From 08e435104c42751689553537b1f20ffb14b8dfcc Mon Sep 17 00:00:00 2001 From: ckonstanski Date: Sat, 18 Jul 2026 21:18:18 -0600 Subject: initial commit --- sql/functions/auth.delete_registration.sql | 14 ++ sql/functions/auth.delete_role_groups_for_user.sql | 16 ++ .../auth.get_active_role_groups_for_user.sql | 25 ++++ sql/functions/auth.get_active_user_by_id.sql | 27 ++++ .../auth.get_active_user_by_username_pwd.sql | 30 ++++ sql/functions/auth.get_all_active_users.sql | 25 ++++ sql/functions/auth.get_all_inactive_users.sql | 24 +++ .../auth.get_all_role_groups_for_user.sql | 36 +++++ sql/functions/auth.get_all_roles_for_user.sql | 163 +++++++++++++++++++++ sql/functions/auth.get_registration_by_hash.sql | 25 ++++ sql/functions/auth.get_registration_by_id.sql | 25 ++++ sql/functions/auth.get_user_by_id.sql | 26 ++++ sql/functions/auth.has_role.sql | 28 ++++ sql/functions/auth.insert_registration.sql | 42 ++++++ sql/functions/auth.insert_user.sql | 59 ++++++++ sql/functions/auth.insert_user_role_group.sql | 44 ++++++ sql/functions/auth.registrations_gc.sql | 11 ++ sql/functions/auth.superuser_p.sql | 34 +++++ sql/functions/auth.update_password.sql | 19 +++ sql/functions/auth.update_profile.sql | 32 ++++ sql/functions/auth.update_user.sql | 45 ++++++ sql/functions/auth.upsert_user.sql | 66 +++++++++ sql/functions/auth.user_delete.sql | 50 +++++++ sql/functions/auth.user_toggle_active.sql | 31 ++++ ...contact.get_contact_us_posts_by_id_and_read.sql | 30 ++++ sql/functions/contact.insert_contact_us_post.sql | 45 ++++++ sql/functions/contact.mark_contact_us_post.sql | 38 +++++ 27 files changed, 1010 insertions(+) create mode 100644 sql/functions/auth.delete_registration.sql create mode 100644 sql/functions/auth.delete_role_groups_for_user.sql create mode 100644 sql/functions/auth.get_active_role_groups_for_user.sql create mode 100644 sql/functions/auth.get_active_user_by_id.sql create mode 100644 sql/functions/auth.get_active_user_by_username_pwd.sql create mode 100644 sql/functions/auth.get_all_active_users.sql create mode 100644 sql/functions/auth.get_all_inactive_users.sql create mode 100644 sql/functions/auth.get_all_role_groups_for_user.sql create mode 100644 sql/functions/auth.get_all_roles_for_user.sql create mode 100644 sql/functions/auth.get_registration_by_hash.sql create mode 100644 sql/functions/auth.get_registration_by_id.sql create mode 100644 sql/functions/auth.get_user_by_id.sql create mode 100644 sql/functions/auth.has_role.sql create mode 100644 sql/functions/auth.insert_registration.sql create mode 100644 sql/functions/auth.insert_user.sql create mode 100644 sql/functions/auth.insert_user_role_group.sql create mode 100644 sql/functions/auth.registrations_gc.sql create mode 100644 sql/functions/auth.superuser_p.sql create mode 100644 sql/functions/auth.update_password.sql create mode 100644 sql/functions/auth.update_profile.sql create mode 100644 sql/functions/auth.update_user.sql create mode 100644 sql/functions/auth.upsert_user.sql create mode 100644 sql/functions/auth.user_delete.sql create mode 100644 sql/functions/auth.user_toggle_active.sql create mode 100644 sql/functions/contact.get_contact_us_posts_by_id_and_read.sql create mode 100644 sql/functions/contact.insert_contact_us_post.sql create mode 100644 sql/functions/contact.mark_contact_us_post.sql (limited to 'sql/functions') diff --git a/sql/functions/auth.delete_registration.sql b/sql/functions/auth.delete_registration.sql new file mode 100644 index 0000000..5adf705 --- /dev/null +++ b/sql/functions/auth.delete_registration.sql @@ -0,0 +1,14 @@ +drop function auth.delete_registration ( + p_hash text +); + +create or replace function auth.delete_registration ( + p_hash text +) +returns void +as $$ +begin + delete from auth.registrations r + where r.hash = ('\x' || p_hash)::bytea; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.delete_role_groups_for_user.sql b/sql/functions/auth.delete_role_groups_for_user.sql new file mode 100644 index 0000000..1b14f0d --- /dev/null +++ b/sql/functions/auth.delete_role_groups_for_user.sql @@ -0,0 +1,16 @@ +drop function auth.delete_role_groups_for_user ( + p_user_id bigint +); + +create or replace function auth.delete_role_groups_for_user ( + p_user_id bigint +) +returns void +as $$ +begin + delete from auth.users_role_groups urg + where user_id = p_user_id; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.get_active_role_groups_for_user.sql b/sql/functions/auth.get_active_role_groups_for_user.sql new file mode 100644 index 0000000..41f7712 --- /dev/null +++ b/sql/functions/auth.get_active_role_groups_for_user.sql @@ -0,0 +1,25 @@ +drop function auth.get_active_role_groups_for_user ( + p_user_id bigint +); + +create or replace function auth.get_active_role_groups_for_user ( + p_user_id bigint +) +returns setof auth.role_groups_t +as $$ +begin + return query + select urg.id as user_role_group_id, + urg.user_id, + rg.id as role_group_id, + rg.name, + rg.description + from auth.users_role_groups urg + inner join auth.role_groups rg + on urg.role_group_id = rg.id + where urg.user_id = p_user_id + order by rg.name asc; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.get_active_user_by_id.sql b/sql/functions/auth.get_active_user_by_id.sql new file mode 100644 index 0000000..7f635a0 --- /dev/null +++ b/sql/functions/auth.get_active_user_by_id.sql @@ -0,0 +1,27 @@ +drop function auth.get_active_user_by_id ( + p_user_id bigint +); + +create or replace function auth.get_active_user_by_id ( + p_user_id bigint +) +returns setof auth.users +as $$ +begin + return query + select u.id, + u.username, + u.pwd, + u.first_name, + u.last_name, + u.email, + u.phone, + u.active, + u.created + from auth.users u + where u.id = p_user_id + and u.active = true; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.get_active_user_by_username_pwd.sql b/sql/functions/auth.get_active_user_by_username_pwd.sql new file mode 100644 index 0000000..138234a --- /dev/null +++ b/sql/functions/auth.get_active_user_by_username_pwd.sql @@ -0,0 +1,30 @@ +drop function auth.get_active_user_by_username_pwd ( + p_username character varying, + p_pwd character varying +); + +create or replace function auth.get_active_user_by_username_pwd ( + p_username character varying, + p_pwd character varying +) +returns setof auth.users +as $$ +begin + return query + select u.id, + u.username, + u.pwd, + u.first_name, + u.last_name, + u.email, + u.phone, + u.active, + u.created + from auth.users u + where u.username = p_username + and u.pwd = digest(p_pwd, 'sha512') + and u.active = true; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.get_all_active_users.sql b/sql/functions/auth.get_all_active_users.sql new file mode 100644 index 0000000..243be63 --- /dev/null +++ b/sql/functions/auth.get_all_active_users.sql @@ -0,0 +1,25 @@ +drop function auth.get_all_active_users (); + +create or replace function auth.get_all_active_users () +returns setof auth.users +as $$ +begin + return query + select u.id, + u.username, + u.pwd, + u.first_name, + u.last_name, + u.email, + u.phone, + u.active, + u.created + from auth.users u + where u.active = true::bool + order by u.last_name asc, + u.first_name asc, + u.id asc; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.get_all_inactive_users.sql b/sql/functions/auth.get_all_inactive_users.sql new file mode 100644 index 0000000..eee0ab9 --- /dev/null +++ b/sql/functions/auth.get_all_inactive_users.sql @@ -0,0 +1,24 @@ +drop function auth.get_all_users (); + +create or replace function auth.get_all_users () +returns setof auth.users +as $$ +begin + return query + select u.id, + u.username, + u.pwd, + u.first_name, + u.last_name, + u.email, + u.phone, + u.active, + u.created + from auth.users u + order by u.last_name asc, + u.first_name asc, + u.id asc; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.get_all_role_groups_for_user.sql b/sql/functions/auth.get_all_role_groups_for_user.sql new file mode 100644 index 0000000..f8c111d --- /dev/null +++ b/sql/functions/auth.get_all_role_groups_for_user.sql @@ -0,0 +1,36 @@ +drop function auth.get_all_role_groups_for_user ( + p_user_id bigint +); + +create or replace function auth.get_all_role_groups_for_user ( + p_user_id bigint +) +returns setof auth.role_groups_t +as $$ +begin + if auth.superuser_p(p_user_id) = true::bool then + return query + select null::bigint as user_role_group_id, + p_user_id as user_id, + id as role_group_id, + name, + description + from auth.role_groups + order by name asc; + else + return query + select urg.id as user_role_group_id, + urg.user_id, + rg.id as role_group_id, + rg.name, + rg.description + from auth.users_role_groups urg + inner join auth.role_groups rg + on urg.role_group_id = rg.id + where urg.user_id = p_user_id + order by rg.name asc; + end if; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.get_all_roles_for_user.sql b/sql/functions/auth.get_all_roles_for_user.sql new file mode 100644 index 0000000..712bd4b --- /dev/null +++ b/sql/functions/auth.get_all_roles_for_user.sql @@ -0,0 +1,163 @@ +drop function auth.get_all_roles_for_user ( + p_user_id bigint +); + +create or replace function auth.get_all_roles_for_user ( + p_user_id bigint +) +returns setof auth.user_roles_t +as $$ +begin + if auth.superuser_p(p_user_id) = true::bool then + return query + with all_user_roles as ( + select null::bigint as user_role_group_id, + p_user_id as user_id, + null::bigint as role_group_id, + null as role_group_name, + null as role_group_description, + null::bigint as role_group_role_id, + null::bigint as role_id, + '_Public' as role_name, + null as role_description, + null::bigint as role_id_exclude, + null::bigint as role_id_append + union + select null::bigint as user_role_group_id, + p_user_id as user_id, + rg.id as role_group_id, + rg.name as role_group_name, + rg.description as role_group_description, + rgr.id as role_group_role_id, + r.id as role_id, + r.name as role_name, + r.description as role_description, + null::bigint as role_id_exclude, + null::bigint as role_id_append + from auth.role_groups rg + inner join auth.role_groups_roles rgr + on rgr.role_group_id = rg.id + inner join auth.roles r + on rgr.role_id = r.id + ) + select a.user_role_group_id, + a.user_id, + a.role_group_id, + a.role_group_name, + a.role_group_description, + a.role_group_role_id, + a.role_id, + a.role_name, + a.role_description + from ( + select r.user_role_group_id as user_role_group_id, + r.user_id as user_id, + r.role_group_id as role_group_id, + r.role_group_name as role_group_name, + r.role_group_description as role_group_description, + r.role_group_role_id as role_group_role_id, + r.role_id as role_id, + r.role_name as role_name, + r.role_description as role_description, + r.role_id_exclude as role_id_exclude, + r.role_id_append as role_id_append + from all_user_roles r + ) a + order by role_name asc; + else + return query + with all_user_roles as ( + select null::bigint as user_role_group_id, + p_user_id as user_id, + null::bigint as role_group_id, + null as role_group_name, + null as role_group_description, + null::bigint as role_group_role_id, + null::bigint as role_id, + '_Public' as role_name, + null as role_description, + null::bigint as role_id_exclude, + null::bigint as role_id_append + union + select urg.id as user_role_group_id, + urg.user_id, + rg.id as role_group_id, + rg.name as role_group_name, + rg.description as role_group_description, + rgr.id as role_group_role_id, + r.id as role_id, + r.name as role_name, + r.description as role_description, + null::bigint as role_id_exclude, + null::bigint as role_id_append + from auth.users_role_groups urg + inner join auth.role_groups rg + on urg.role_group_id = rg.id + inner join auth.role_groups_roles rgr + on rgr.role_group_id = rg.id + inner join auth.roles r + on rgr.role_id = r.id + union + select null::bigint as user_role_group_id, + ure.user_id, + null::bigint as role_group_id, + null as role_group_name, + null role_group_description, + null::bigint as role_group_role_id, + r.id as role_id, + r.name as role_name, + r.description role_description, + ure.role_id as role_id_exclude, + null::bigint as role_id_append + from auth.users_roles_exclude ure + inner join auth.roles r + on ure.role_id = r.id + where ure.user_id = p_user_id + union + select null::bigint as user_role_group_id, + ura.user_id, + null::bigint as role_group_id, + null as role_group_name, + null role_group_description, + null::bigint as role_group_role_id, + r.id as role_id, + r.name as role_name, + r.description role_description, + null::bigint as role_id_exclude, + ura.role_id as role_id_append + from auth.users_roles_append ura + inner join auth.roles r + on ura.role_id = r.id + where ura.user_id = p_user_id + ) + select a.user_role_group_id, + a.user_id, + a.role_group_id, + a.role_group_name, + a.role_group_description, + a.role_group_role_id, + a.role_id, + a.role_name, + a.role_description + from ( + select r.user_role_group_id as user_role_group_id, + r.user_id as user_id, + r.role_group_id as role_group_id, + r.role_group_name as role_group_name, + r.role_group_description as role_group_description, + r.role_group_role_id as role_group_role_id, + r.role_id as role_id, + r.role_name as role_name, + r.role_description as role_description, + r.role_id_exclude as role_id_exclude, + r.role_id_append as role_id_append + from all_user_roles r + where user_id = p_user_id + ) a + where a.role_id_exclude is null + order by role_name asc; + end if; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.get_registration_by_hash.sql b/sql/functions/auth.get_registration_by_hash.sql new file mode 100644 index 0000000..87d622e --- /dev/null +++ b/sql/functions/auth.get_registration_by_hash.sql @@ -0,0 +1,25 @@ +drop function auth.get_registration_by_hash ( + p_hash text +); + +create or replace function auth.get_registration_by_hash ( + p_hash text +) +returns setof auth.registrations_t +as $$ +begin + return query + select r.id, + substring(r.hash::text from 3), + r.first_name, + r.last_name, + r.email, + r.role_groups, + r.created, + (r.created - (now() - interval '3 days'))::interval as valid_for + from auth.registrations r + where r.hash = ('\x' || p_hash)::bytea; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.get_registration_by_id.sql b/sql/functions/auth.get_registration_by_id.sql new file mode 100644 index 0000000..dc9fdc1 --- /dev/null +++ b/sql/functions/auth.get_registration_by_id.sql @@ -0,0 +1,25 @@ +drop function auth.get_registration_by_id ( + p_id bigint +); + +create or replace function auth.get_registration_by_id ( + p_id bigint +) +returns setof auth.registrations_t +as $$ +begin + return query + select r.id, + substring(r.hash::text from 3), + r.first_name, + r.last_name, + r.email, + r.role_groups, + r.created, + (r.created - (now() - interval '3 days'))::interval as valid_for + from auth.registrations r + where r.id = p_id; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.get_user_by_id.sql b/sql/functions/auth.get_user_by_id.sql new file mode 100644 index 0000000..d745c49 --- /dev/null +++ b/sql/functions/auth.get_user_by_id.sql @@ -0,0 +1,26 @@ +drop function auth.get_user_by_id ( + p_user_id bigint +); + +create or replace function auth.get_user_by_id ( + p_user_id bigint +) +returns setof auth.users +as $$ +begin + return query + select u.id, + u.username, + u.pwd, + u.first_name, + u.last_name, + u.email, + u.phone, + u.active, + u.created + from auth.users u + where u.id = p_user_id; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.has_role.sql b/sql/functions/auth.has_role.sql new file mode 100644 index 0000000..1d6cdbd --- /dev/null +++ b/sql/functions/auth.has_role.sql @@ -0,0 +1,28 @@ +drop function auth.has_role ( + p_user_id bigint, + p_role_name character varying +); + +create or replace function auth.has_role ( + p_user_id bigint, + p_role_name character varying +) +returns setof auth.user_roles_t +as $$ +begin + return query + select user_role_group_id, + user_id, + role_group_id, + role_group_name, + role_group_description, + role_group_role_id, + role_id, + role_name, + role_description + from auth.get_all_roles_for_user(p_user_id) + where role_name = p_role_name; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.insert_registration.sql b/sql/functions/auth.insert_registration.sql new file mode 100644 index 0000000..0f0b73d --- /dev/null +++ b/sql/functions/auth.insert_registration.sql @@ -0,0 +1,42 @@ +drop function auth.insert_registration ( + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_role_groups text +); + +create or replace function auth.insert_registration ( + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_role_groups text +) +returns bigint +as $$ +declare + l_id bigint; +begin + insert into auth.registrations ( + hash, + first_name, + last_name, + email, + role_groups + ) values ( + digest(concat(cast(current_timestamp as text), random()::text), 'sha512'), + p_first_name, + p_last_name, + p_email, + p_role_groups + ); + + select id into l_id + from auth.registrations + where first_name = p_first_name + and last_name = p_last_name + and email = p_email + and role_groups = p_role_groups; + + return l_id; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.insert_user.sql b/sql/functions/auth.insert_user.sql new file mode 100644 index 0000000..597fd39 --- /dev/null +++ b/sql/functions/auth.insert_user.sql @@ -0,0 +1,59 @@ +drop function auth.insert_user( + p_username character varying, + p_pwd character varying, + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_phone character varying, + p_active boolean +); + +create or replace function auth.insert_user ( + p_username character varying, + p_pwd character varying, + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_phone character varying, + p_active boolean +) +returns bigint +as $$ +declare + l_id bigint; +begin + select u.id into l_id + from auth.users u + where u.username = p_username + and u.pwd = digest(p_pwd, 'sha512'); + + if(l_id is null) then + insert into auth.users ( + username, + pwd, + first_name, + last_name, + email, + phone, + active + ) values ( + p_username, + digest(p_pwd, 'sha512'), + p_first_name, + p_last_name, + p_email, + p_phone, + p_active + ); + + select u.id into l_id + from auth.users u + where u.username = p_username + and u.pwd = digest(p_pwd, 'sha512'); + + return l_id; + else + return null; + end if; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.insert_user_role_group.sql b/sql/functions/auth.insert_user_role_group.sql new file mode 100644 index 0000000..ee8b21e --- /dev/null +++ b/sql/functions/auth.insert_user_role_group.sql @@ -0,0 +1,44 @@ +drop function auth.insert_user_role_group ( + p_user_id bigint, + p_role_group_name character varying +); + +create or replace function auth.insert_user_role_group ( + p_user_id bigint, + p_role_group_name character varying +) +returns bigint +as $$ +declare + l_role_group_id bigint; + l_user_role_group_id bigint; +begin + select rg.id into l_role_group_id + from auth.role_groups rg + where rg.name = p_role_group_name; + + if(l_role_group_id is not null) then + select urg.id into l_user_role_group_id + from auth.users_role_groups urg + where urg.user_id = p_user_id + and urg.role_group_id = l_role_group_id; + + if(l_user_role_group_id is null) then + insert into auth.users_role_groups ( + user_id, + role_group_id + ) values ( + p_user_id, + l_role_group_id + ); + + select urg.id into l_user_role_group_id + from auth.users_role_groups urg + where urg.user_id = p_user_id + and urg.role_group_id = l_role_group_id; + end if; + end if; + + return l_user_role_group_id; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.registrations_gc.sql b/sql/functions/auth.registrations_gc.sql new file mode 100644 index 0000000..c1e1d90 --- /dev/null +++ b/sql/functions/auth.registrations_gc.sql @@ -0,0 +1,11 @@ +drop function auth.registrations_gc (); + +create or replace function auth.registrations_gc () +returns void +as $$ +begin + delete + from auth.registrations + where created < now() - interval '3 days'; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.superuser_p.sql b/sql/functions/auth.superuser_p.sql new file mode 100644 index 0000000..f6865ec --- /dev/null +++ b/sql/functions/auth.superuser_p.sql @@ -0,0 +1,34 @@ +drop function auth.superuser_p ( + p_user_id bigint +); + +create or replace function auth.superuser_p ( + p_user_id bigint +) +returns bool +as $$ +declare + l_user_id bigint; +begin + with roles as ( + select urg.user_id, + rg.id as role_group_id, + rg.name + from auth.users_role_groups urg + inner join auth.role_groups_roles rgr + on urg.role_group_id = rgr.role_group_id + inner join auth.role_groups rg + on rgr.role_group_id = rg.id + where urg.user_id = p_user_id + and rg.name = 'superuser' + ) + select user_id into l_user_id + from roles; + + if(l_user_id is null) then + return false::bool; + else + return true::bool; + end if; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.update_password.sql b/sql/functions/auth.update_password.sql new file mode 100644 index 0000000..8dc730c --- /dev/null +++ b/sql/functions/auth.update_password.sql @@ -0,0 +1,19 @@ +drop function auth.update_password ( + p_id bigint, + p_pwd character varying +); + +create or replace function auth.update_password ( + p_id bigint, + p_pwd character varying +) +returns bigint +as $$ +begin + update auth.users + set pwd = digest(p_pwd, 'sha512') + where id = p_id; + + return p_id; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.update_profile.sql b/sql/functions/auth.update_profile.sql new file mode 100644 index 0000000..8e08c4b --- /dev/null +++ b/sql/functions/auth.update_profile.sql @@ -0,0 +1,32 @@ +drop function auth.update_profile ( + p_id bigint, + p_username character varying, + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_phone character varying +); + +create or replace function auth.update_profile ( + p_id bigint, + p_username character varying, + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_phone character varying +) +returns bigint +as $$ +begin + update auth.users + set username = p_username, + pwd = p_pwd, + first_name = p_first_name, + last_name = p_last_name, + email = p_email, + phone = p_phone + where id = p_id; + + return p_id; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.update_user.sql b/sql/functions/auth.update_user.sql new file mode 100644 index 0000000..312bf2e --- /dev/null +++ b/sql/functions/auth.update_user.sql @@ -0,0 +1,45 @@ +drop function auth.update_user( + p_id bigint, + p_username character varying, + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_phone character varying +); + +create or replace function auth.update_user ( + p_id bigint, + p_username character varying, + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_phone character varying +) +returns bigint +as $$ +declare + l_id bigint; +begin + select u.id into l_id + from auth.users u + where u.id = p_id; + + if(l_id is not null) then + update auth.users + set username = p_username, + first_name = p_first_name, + last_name = p_last_name, + email = p_email, + phone = p_phone + where id = p_id; + + select u.id into l_id + from auth.users u + where u.id = p_id; + + return l_id; + else + return null; + end if; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.upsert_user.sql b/sql/functions/auth.upsert_user.sql new file mode 100644 index 0000000..28e94a0 --- /dev/null +++ b/sql/functions/auth.upsert_user.sql @@ -0,0 +1,66 @@ +drop function auth.upsert_user ( + p_id bigint, + p_username character varying, + p_pwd character varying, + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_phone character varying, + p_active boolean +); + +create or replace function auth.upsert_user ( + p_id bigint, + p_username character varying, + p_pwd character varying, + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_phone character varying, + p_active boolean +) +returns bigint +as $$ +begin + if(p_id is null) then + insert into auth.users ( + username, + pwd, + first_name, + last_name, + email, + phone, + active + ) values ( + p_username, + digest(p_pwd, 'sha512'), + p_first_name, + p_last_name, + p_email, + p_phone, + p_active + ); + + select id into p_id + from auth.users + where username = p_username + and pwd = digest(p_pwd, 'sha512') + and first_name = p_first_name + and last_name = p_last_name + and email = p_email + and phone = p_phone; + else + update auth.users + set username = p_username, + pwd = digest(p_pwd, 'sha512'), + first_name = p_first_name, + last_name = p_last_name, + email = p_email, + phone = p_phone, + active = p_active + where id = p_id; + end if; + + return p_id; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.user_delete.sql b/sql/functions/auth.user_delete.sql new file mode 100644 index 0000000..8cb399e --- /dev/null +++ b/sql/functions/auth.user_delete.sql @@ -0,0 +1,50 @@ +drop function auth.user_delete ( + p_id bigint +); + +create or replace function auth.user_delete ( + p_id bigint +) +returns bool +as $$ +declare + l_id bigint; + l_result bool; +begin + if p_id is null then + l_result := false::bool; + else + select id into l_id + from auth.users + where id = p_id; + + if l_id is null or l_id != p_id then + l_result := false::bool; + else + delete + from auth.users_roles_append + where user_id = l_id; + + delete + from auth.users_roles_exclude + where user_id = l_id; + + delete + from auth.users_role_groups + where user_id = l_id; + + delete + from contact.contact_us_posts_read + where user_id = l_id; + + delete + from auth.users + where id = l_id; + + l_result := true::bool; + end if; + end if; + + return l_result; +end; +$$ language plpgsql; diff --git a/sql/functions/auth.user_toggle_active.sql b/sql/functions/auth.user_toggle_active.sql new file mode 100644 index 0000000..adc1b62 --- /dev/null +++ b/sql/functions/auth.user_toggle_active.sql @@ -0,0 +1,31 @@ +drop function auth.user_toggle_active ( + p_id bigint +); + +create or replace function auth.user_toggle_active ( + p_id bigint +) +returns bigint +as $$ +declare + l_id bigint; +begin + select u.id into l_id + from auth.users u + where u.id = p_id; + + if(l_id is not null) then + update auth.users + set active = not active + where id = p_id; + + select u.id into l_id + from auth.users u + where u.id = p_id; + + return l_id; + else + return null; + end if; +end; +$$ language plpgsql; diff --git a/sql/functions/contact.get_contact_us_posts_by_id_and_read.sql b/sql/functions/contact.get_contact_us_posts_by_id_and_read.sql new file mode 100644 index 0000000..646fe0f --- /dev/null +++ b/sql/functions/contact.get_contact_us_posts_by_id_and_read.sql @@ -0,0 +1,30 @@ +drop function contact.get_contact_us_posts_by_id_and_read ( + p_user_id bigint, + p_read bool +); + +create or replace function contact.get_contact_us_posts_by_id_and_read ( + p_user_id bigint, + p_read bool +) +returns setof contact.contact_us_posts +as $$ +begin + return query + select c.id, + c.first_name, + c.last_name, + c.email, + c.phone, + c.submitted, + c.comments + from contact.contact_us_posts c + left outer join contact.contact_us_posts_read cr + on c.id = cr.contact_us_post_id + where (p_read = false and cr.user_id is null) + or (p_read = true and cr.user_id = p_user_id) + order by c.submitted desc; + + return; +end; +$$ language plpgsql; diff --git a/sql/functions/contact.insert_contact_us_post.sql b/sql/functions/contact.insert_contact_us_post.sql new file mode 100644 index 0000000..739f084 --- /dev/null +++ b/sql/functions/contact.insert_contact_us_post.sql @@ -0,0 +1,45 @@ +drop function contact.insert_contact_us_post ( + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_phone character varying, + p_comments text +); + +create or replace function contact.insert_contact_us_post ( + p_first_name character varying, + p_last_name character varying, + p_email character varying, + p_phone character varying, + p_comments text +) +returns bigint +as $$ +declare + l_id bigint; +begin + insert into contact.contact_us_posts ( + first_name, + last_name, + email, + phone, + comments + ) values ( + p_first_name, + p_last_name, + p_email, + p_phone, + p_comments + ); + + select id into l_id + from contact.contact_us_posts + where first_name = p_first_name + and last_name = p_last_name + and email = p_email + and phone = p_phone + and comments = p_comments; + + return l_id; +end; +$$ language plpgsql; diff --git a/sql/functions/contact.mark_contact_us_post.sql b/sql/functions/contact.mark_contact_us_post.sql new file mode 100644 index 0000000..7bcc001 --- /dev/null +++ b/sql/functions/contact.mark_contact_us_post.sql @@ -0,0 +1,38 @@ +drop function contact.mark_contact_us_post ( + p_contact_us_post_id int, + p_user_id int, + p_read_p bool +); + +create or replace function contact.mark_contact_us_post ( + p_contact_us_post_id int, + p_user_id int, + p_read_p bool +) +returns void +as $$ +declare + l_id bigint; +begin + if(p_read_p) then + select id into l_id + from contact.contact_us_posts_read + where contact_us_post_id = p_contact_us_post_id + and user_id = p_user_id; + + if(l_id is null) then + insert into contact.contact_us_posts_read ( + contact_us_post_id, + user_id + ) values ( + p_contact_us_post_id, + p_user_id + ); + end if; + else + delete from contact.contact_us_posts_read + where contact_us_post_id = p_contact_us_post_id + and user_id = p_user_id; + end if; +end; +$$ language plpgsql; -- cgit v1.3