diff options
Diffstat (limited to 'sql')
46 files changed, 1170 insertions, 0 deletions
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..26f926c --- /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;a 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..6fe66f4 --- /dev/null +++ b/sql/functions/auth.user_delete.sql @@ -0,0 +1,54 @@ +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 general.roster + 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; diff --git a/sql/schemas/schemas.sql b/sql/schemas/schemas.sql new file mode 100644 index 0000000..abcfc3d --- /dev/null +++ b/sql/schemas/schemas.sql @@ -0,0 +1,5 @@ +-- set search_path = auth,general,contact,public; + +create schema auth; +create schema general; +create schema contact; diff --git a/sql/tables/auth.registrations.sql b/sql/tables/auth.registrations.sql new file mode 100644 index 0000000..91eb3a1 --- /dev/null +++ b/sql/tables/auth.registrations.sql @@ -0,0 +1,11 @@ +drop table auth.registrations cascade; + +create table auth.registrations ( + id serial8 primary key, + hash bytea not null, + first_name character varying(255) not null, + last_name character varying(255) not null, + email character varying(255) not null, + role_groups text, + created timestamp without time zone not null default now() +); diff --git a/sql/tables/auth.role_groups.sql b/sql/tables/auth.role_groups.sql new file mode 100644 index 0000000..8b6d225 --- /dev/null +++ b/sql/tables/auth.role_groups.sql @@ -0,0 +1,7 @@ +drop table auth.role_groups cascade; + +create table auth.role_groups ( + id serial8 primary key, + name character varying(255) not null, + description character varying(255) +); diff --git a/sql/tables/auth.role_groups_roles.sql b/sql/tables/auth.role_groups_roles.sql new file mode 100644 index 0000000..7ebab21 --- /dev/null +++ b/sql/tables/auth.role_groups_roles.sql @@ -0,0 +1,7 @@ +drop table auth.role_groups_roles cascade; + +create table auth.role_groups_roles ( + id serial8 primary key, + role_group_id bigint not null references auth.role_groups(id), + role_id bigint not null references auth.roles(id) +); diff --git a/sql/tables/auth.roles.sql b/sql/tables/auth.roles.sql new file mode 100644 index 0000000..f5873fe --- /dev/null +++ b/sql/tables/auth.roles.sql @@ -0,0 +1,7 @@ +drop table auth.roles cascade; + +create table auth.roles ( + id serial8 primary key, + name character varying(255) not null, + description character varying(255) +); diff --git a/sql/tables/auth.user_session_objects.sql b/sql/tables/auth.user_session_objects.sql new file mode 100644 index 0000000..aedfb42 --- /dev/null +++ b/sql/tables/auth.user_session_objects.sql @@ -0,0 +1,8 @@ +drop table auth.user_session_objects cascade; + +create table auth.user_session_objects ( + id serial8 primary key, + user_session_id bigint not null references auth.user_sessions(id), + session_key character varying(255), + serialization text +); diff --git a/sql/tables/auth.user_sessions.sql b/sql/tables/auth.user_sessions.sql new file mode 100644 index 0000000..2629d62 --- /dev/null +++ b/sql/tables/auth.user_sessions.sql @@ -0,0 +1,7 @@ +drop table auth.user_sessions cascade; + +create table auth.user_sessions ( + id serial8 primary key, + sessionid character varying(255) not null, + datetime bigint not null +); diff --git a/sql/tables/auth.users.sql b/sql/tables/auth.users.sql new file mode 100644 index 0000000..8eecf76 --- /dev/null +++ b/sql/tables/auth.users.sql @@ -0,0 +1,13 @@ +drop table auth.users cascade; + +create table auth.users ( + id serial8 primary key, + username character varying(255) not null, + pwd bytea, + first_name character varying(255) not null, + last_name character varying(255) not null, + email character varying(255) not null, + phone character varying(255), + active bool, + created timestamp without time zone not null default now() +); diff --git a/sql/tables/auth.users_role_groups.sql b/sql/tables/auth.users_role_groups.sql new file mode 100644 index 0000000..44cfe4a --- /dev/null +++ b/sql/tables/auth.users_role_groups.sql @@ -0,0 +1,7 @@ +drop table auth.users_role_groups cascade; + +create table auth.users_role_groups ( + id serial8 primary key, + user_id bigint not null references auth.users(id), + role_group_id bigint not null references auth.role_groups(id) +); diff --git a/sql/tables/auth.users_roles_append.sql b/sql/tables/auth.users_roles_append.sql new file mode 100644 index 0000000..f6603e6 --- /dev/null +++ b/sql/tables/auth.users_roles_append.sql @@ -0,0 +1,7 @@ +drop table auth.users_roles_append cascade; + +create table auth.users_roles_append ( + id serial8 primary key, + user_id bigint not null references auth.users(id), + role_id bigint not null references auth.roles(id) +); diff --git a/sql/tables/auth.users_roles_exclude.sql b/sql/tables/auth.users_roles_exclude.sql new file mode 100644 index 0000000..e47385e --- /dev/null +++ b/sql/tables/auth.users_roles_exclude.sql @@ -0,0 +1,7 @@ +drop table auth.users_roles_exclude cascade; + +create table auth.users_roles_exclude ( + id serial8 primary key, + user_id bigint not null references auth.users(id), + role_id bigint not null references auth.roles(id) +); diff --git a/sql/tables/contact.contact_us.sql b/sql/tables/contact.contact_us.sql new file mode 100644 index 0000000..0957e3c --- /dev/null +++ b/sql/tables/contact.contact_us.sql @@ -0,0 +1,6 @@ +drop table contact.contact_us cascade; + +create table contact.contact_us ( + id serial8 primary key, + content text +); diff --git a/sql/tables/contact.contact_us_posts.sql b/sql/tables/contact.contact_us_posts.sql new file mode 100644 index 0000000..5e208cb --- /dev/null +++ b/sql/tables/contact.contact_us_posts.sql @@ -0,0 +1,11 @@ +drop table contact.contact_us_posts cascade; + +create table contact.contact_us_posts ( + id serial8 primary key, + first_name character varying(255) not null, + last_name character varying(255) not null, + email character varying(255) not null, + phone character varying(255), + submitted timestamp without time zone not null default now(), + comments text +); diff --git a/sql/tables/contact.contact_us_posts_read.sql b/sql/tables/contact.contact_us_posts_read.sql new file mode 100644 index 0000000..80be27c --- /dev/null +++ b/sql/tables/contact.contact_us_posts_read.sql @@ -0,0 +1,7 @@ +drop table contact.contact_us_posts_read cascade; + +create table contact.contact_us_posts_read ( + id serial8 primary key, + contact_us_post_id bigint not null references contact.contact_us_posts(id), + user_id bigint not null references auth.users(id) +); diff --git a/sql/tables/general.about_us.sql b/sql/tables/general.about_us.sql new file mode 100644 index 0000000..237065b --- /dev/null +++ b/sql/tables/general.about_us.sql @@ -0,0 +1,6 @@ +drop table general.about_us cascade; + +create table general.about_us ( + id serial8 primary key, + content text +); diff --git a/sql/tables/general.testimonials.sql b/sql/tables/general.testimonials.sql new file mode 100644 index 0000000..64300a6 --- /dev/null +++ b/sql/tables/general.testimonials.sql @@ -0,0 +1,6 @@ +drop table general.testimonials cascade; + +create table general.testimonials ( + id serial8 primary key, + content text +); diff --git a/sql/types/auth.registrations_t.sql b/sql/types/auth.registrations_t.sql new file mode 100644 index 0000000..cde66d0 --- /dev/null +++ b/sql/types/auth.registrations_t.sql @@ -0,0 +1,12 @@ +drop type auth.registrations_t cascade; + +create type auth.registrations_t as ( + id bigint, + hash text, + first_name character varying, + last_name character varying, + email character varying, + role_groups text, + created timestamp without time zone, + valid_for interval +); diff --git a/sql/types/auth.role_groups_t.sql b/sql/types/auth.role_groups_t.sql new file mode 100644 index 0000000..615c481 --- /dev/null +++ b/sql/types/auth.role_groups_t.sql @@ -0,0 +1,9 @@ +drop type auth.role_groups_t cascade; + +CREATE TYPE auth.role_groups_t AS ( + user_role_group_id bigint, + user_id bigint, + role_group_id bigint, + name character varying, + description character varying +); diff --git a/sql/types/auth.user_roles_t.sql b/sql/types/auth.user_roles_t.sql new file mode 100644 index 0000000..e6b8443 --- /dev/null +++ b/sql/types/auth.user_roles_t.sql @@ -0,0 +1,13 @@ +drop type auth.user_roles_t cascade; + +CREATE TYPE auth.user_roles_t AS ( + user_role_group_id bigint, + user_id bigint, + role_group_id bigint, + role_group_name character varying, + role_group_description character varying, + role_group_role_id bigint, + role_id bigint, + role_name character varying, + role_description character varying +); |