summaryrefslogtreecommitdiff
path: root/db/rest/user
diff options
context:
space:
mode:
Diffstat (limited to 'db/rest/user')
-rw-r--r--db/rest/user/api_avatar.sql22
-rw-r--r--db/rest/user/api_user.sql23
-rw-r--r--db/rest/user/api_user_delete.sql30
-rw-r--r--db/rest/user/api_user_insert.sql128
-rw-r--r--db/rest/user/api_user_update.sql167
5 files changed, 0 insertions, 370 deletions
diff --git a/db/rest/user/api_avatar.sql b/db/rest/user/api_avatar.sql
deleted file mode 100644
index 981409f..0000000
--- a/db/rest/user/api_avatar.sql
+++ /dev/null
@@ -1,22 +0,0 @@
-CREATE FUNCTION api.avatar(
- user_id INTEGER DEFAULT 0
-)
-RETURNS sys."*/*"
-LANGUAGE plpgsql VOLATILE
-AS $BODY$
-DECLARE
- _mod INTEGER;
- _name TEXT;
-BEGIN
- _mod = MOD(user_id, 24);
- _name = 'default_avatar_' || _mod || '.png';
- RETURN _api.serve_media(_name);
-END
-$BODY$;
-
-GRANT EXECUTE ON FUNCTION api.avatar(INTEGER)
- TO rest_anon, rest_user;
-GRANT SELECT ON TABLE admin.user
- TO rest_anon, rest_user;
-GRANT SELECT ON TABLE admin.media
- TO rest_anon, rest_user;
diff --git a/db/rest/user/api_user.sql b/db/rest/user/api_user.sql
deleted file mode 100644
index e45768a..0000000
--- a/db/rest/user/api_user.sql
+++ /dev/null
@@ -1,23 +0,0 @@
-CREATE VIEW api.user AS
- SELECT
- u.id,
- u.username,
- NULL AS password,
- u.role,
- u.first_name,
- u.last_name,
- u.middle_name,
- u.email,
- u.gender,
- u.join_date,
- u.birth_date,
- u.profile_avatar,
- u.profile_banner,
- u.profile_bio
- FROM
- admin.user u;
-
-GRANT SELECT ON TABLE api.user
- TO rest_anon, rest_user;
-GRANT SELECT ON TABLE admin.user
- TO rest_anon, rest_user;
diff --git a/db/rest/user/api_user_delete.sql b/db/rest/user/api_user_delete.sql
deleted file mode 100644
index 8d7d52f..0000000
--- a/db/rest/user/api_user_delete.sql
+++ /dev/null
@@ -1,30 +0,0 @@
-CREATE FUNCTION _api.user_delete()
-RETURNS TRIGGER
-LANGUAGE plpgsql VOLATILE
-AS $BODY$
-DECLARE
- _user_id INTEGER;
-BEGIN
- _user_id = _api.get_user_id();
-
- IF OLD.id <> _user_id THEN
- PERFORM _api.raise_deny();
- END IF;
-
- DELETE FROM admin.user
- WHERE id = _user_id;
-END
-$BODY$;
-
-GRANT EXECUTE ON FUNCTION _api.user_delete()
- TO rest_user;
-GRANT DELETE ON TABLE api.user
- TO rest_user;
-GRANT DELETE ON TABLE admin.user
- TO rest_user;
-
-CREATE TRIGGER api_user_delete_trgr
- INSTEAD OF DELETE
- ON api.user
- FOR EACH ROW
- EXECUTE PROCEDURE _api.user_delete();
diff --git a/db/rest/user/api_user_insert.sql b/db/rest/user/api_user_insert.sql
deleted file mode 100644
index 2297ecd..0000000
--- a/db/rest/user/api_user_insert.sql
+++ /dev/null
@@ -1,128 +0,0 @@
-CREATE FUNCTION _api.user_insert()
-RETURNS TRIGGER
-LANGUAGE plpgsql VOLATILE
-AS $BODY$
-DECLARE
- _length INTEGER;
-BEGIN
-
- NEW.username := _api.trim(NEW.username);
- NEW.password := _api.trim(NEW.password);
- NEW.first_name := _api.trim(NEW.first_name);
- NEW.last_name := _api.trim(NEW.last_name);
- NEW.middle_name := _api.trim(NEW.middle_name);
- NEW.email := _api.trim(NEW.email);
- NEW.gender := _api.trim(NEW.gender);
- NEW.profile_bio := _api.trim(NEW.profile_bio);
-
- PERFORM _api.validate_text(
- _text => NEW.username,
- _column => 'username',
- _min => 1,
- _max => 24
- );
-
- PERFORM TRUE FROM admin.user
- WHERE username = NEW.username;
-
- IF FOUND THEN
- PERFORM _api.raise_unique('username');
- END IF;
-
- PERFORM _api.validate_text(
- _text => NEW.password,
- _column => 'password',
- _min => 1,
- _max => 256
- );
-
- PERFORM _api.validate_text(
- _text => NEW.first_name,
- _nullable => TRUE,
- _column => 'first_name',
- _max => 256
- );
- NEW.first_name = COALESCE(NEW.first_name, ''::text);
-
- PERFORM _api.validate_text(
- _text => NEW.last_name,
- _nullable => TRUE,
- _column => 'last_name',
- _max => 256
- );
- NEW.last_name = COALESCE(NEW.last_name, ''::text);
-
- PERFORM _api.validate_text(
- _text => NEW.middle_name,
- _nullable => TRUE,
- _column => 'middle_name',
- _max => 256
- );
- NEW.middle_name = COALESCE(NEW.middle_name, ''::text);
-
- PERFORM _api.validate_text(
- _text => NEW.email,
- _column => 'email',
- _max => 256
- );
-
- PERFORM _api.validate_text(
- _text => NEW.gender,
- _column => 'gender',
- _max => 256
- );
-
- IF NEW.birth_date IS NULL THEN
- PERFORM _api.raise_null('birth_date');
- END IF;
-
- PERFORM _api.validate_text(
- _text => NEW.profile_bio,
- _nullable => TRUE,
- _column => 'profile_bio',
- _max => 2048
- );
- NEW.profile_bio = COALESCE(NEW.profile_bio, ''::text);
-
- INSERT INTO admin.user (
- username,
- password,
- first_name,
- last_name,
- middle_name,
- email,
- gender,
- birth_date,
- profile_bio
- ) VALUES (
- NEW.username,
- NEW.password,
- NEW.first_name,
- NEW.last_name,
- NEW.middle_name,
- NEW.email,
- NEW.gender,
- NEW.birth_date,
- NEW.profile_bio
- );
-
- NEW.password := NULL;
-
- RETURN NEW;
-END
-$BODY$;
-
-GRANT EXECUTE ON FUNCTION _api.user_insert()
- TO rest_anon, rest_user;
-GRANT INSERT ON TABLE api.user
- TO rest_anon, rest_user;
-GRANT INSERT ON TABLE admin.user
- TO rest_anon, rest_user;
-GRANT UPDATE ON TABLE sys.user_id_seq
- TO rest_anon, rest_user;
-
-CREATE TRIGGER api_user_insert_trgr
- INSTEAD OF INSERT
- ON api.user
- FOR EACH ROW
- EXECUTE PROCEDURE _api.user_insert();
diff --git a/db/rest/user/api_user_update.sql b/db/rest/user/api_user_update.sql
deleted file mode 100644
index 28e4368..0000000
--- a/db/rest/user/api_user_update.sql
+++ /dev/null
@@ -1,167 +0,0 @@
-CREATE FUNCTION _api.user_update()
-RETURNS TRIGGER
-LANGUAGE plpgsql VOLATILE
-AS $BODY$
-DECLARE
- _user_id INTEGER;
- _changed BOOLEAN;
-BEGIN
- _user_id = _api.get_user_id();
- _changed = FALSE;
-
- IF OLD.id <> _user_id THEN
- PERFORM _api.raise_deny();
- END IF;
-
- -- username
- NEW.username = COALESCE(NEW.username, OLD.username);
- NEW.username := _api.trim(NEW.username);
- PERFORM _api.validate_text(
- _text => NEW.username,
- _column => 'username',
- _min => 1,
- _max => 24
- );
-
- IF NEW.username IS DISTINCT FROM OLD.username THEN
- PERFORM TRUE FROM admin.user
- WHERE username = NEW.username;
- IF FOUND THEN
- PERFORM _api.raise_unique('username');
- END IF;
- _changed = TRUE;
- END IF;
-
- -- password
- SELECT password
- INTO OLD.password
- FROM admin.user
- WHERE id = OLD.id;
-
- NEW.password = COALESCE(NEW.password, OLD.password);
- NEW.password := _api.trim(NEW.password);
- PERFORM _api.validate_text(
- _text => NEW.password,
- _column => 'password',
- _min => 1,
- _max => 256
- );
-
- IF NEW.password IS DISTINCT FROM OLD.password THEN
- _changed = TRUE;
- END IF;
-
- -- first name
- NEW.first_name = COALESCE(NEW.first_name, OLD.first_name);
- NEW.first_name := _api.trim(NEW.first_name);
- PERFORM _api.validate_text(
- _text => NEW.first_name,
- _column => 'first_name',
- _max => 256
- );
-
- IF NEW.first_name IS DISTINCT FROM OLD.first_name THEN
- _changed = TRUE;
- END IF;
-
- -- last name
- NEW.last_name = COALESCE(NEW.last_name, OLD.last_name);
- NEW.last_name := _api.trim(NEW.last_name);
- PERFORM _api.validate_text(
- _text => NEW.last_name,
- _column => 'last_name',
- _max => 256
- );
-
- IF NEW.last_name IS DISTINCT FROM OLD.last_name THEN
- _changed = TRUE;
- END IF;
-
- -- middle name
- NEW.middle_name = COALESCE(NEW.middle_name, OLD.middle_name);
- NEW.middle_name := _api.trim(NEW.middle_name);
- PERFORM _api.validate_text(
- _text => NEW.middle_name,
- _column => 'middle_name',
- _max => 256
- );
-
- IF NEW.middle_name IS DISTINCT FROM OLD.middle_name THEN
- _changed = TRUE;
- END IF;
-
- -- email
- NEW.email = COALESCE(NEW.email, OLD.email);
- NEW.email := _api.trim(NEW.email);
- PERFORM _api.validate_text(
- _text => NEW.email,
- _column => 'email',
- _max => 256
- );
-
- IF NEW.email IS DISTINCT FROM OLD.email THEN
- _changed = TRUE;
- END IF;
-
- -- gender
- NEW.gender = COALESCE(NEW.gender, OLD.gender);
- NEW.gender := _api.trim(NEW.gender);
- PERFORM _api.validate_text(
- _text => NEW.gender,
- _column => 'gender',
- _max => 256
- );
-
- IF NEW.gender IS DISTINCT FROM OLD.gender THEN
- _changed = TRUE;
- END IF;
-
- -- birth date
- NEW.birth_date = COALESCE(NEW.birth_date, OLD.birth_date);
- IF NEW.birth_date IS DISTINCT FROM OLD.birth_date THEN
- _changed = TRUE;
- END IF;
-
- -- profile bio
- NEW.profile_bio = COALESCE(NEW.profile_bio, OLD.profile_bio);
- NEW.profile_bio := _api.trim(NEW.profile_bio);
- PERFORM _api.validate_text(
- _text => NEW.profile_bio,
- _column => 'profile_bio',
- _max => 2048
- );
-
- IF NEW.profile_bio IS DISTINCT FROM OLD.profile_bio THEN
- _changed = TRUE;
- END IF;
-
- IF _changed THEN
- UPDATE admin.user SET
- username = NEW.username,
- password = NEW.password,
- first_name = NEW.first_name,
- last_name = NEW.last_name,
- middle_name = NEW.middle_name,
- email = NEW.email,
- gender = NEW.gender,
- birth_date = NEW.birth_date,
- profile_bio = NEW.profile_bio
- WHERE id = OLD.id;
- END IF;
-
- RETURN NEW;
-END
-$BODY$;
-
-GRANT EXECUTE ON FUNCTION _api.user_update()
- TO rest_user;
-GRANT UPDATE ON TABLE api.user
- TO rest_user;
-GRANT UPDATE ON TABLE admin.user
- TO rest_user;
-
-CREATE TRIGGER api_user_update_trgr
- INSTEAD OF UPDATE
- ON api.user
- FOR EACH ROW
- EXECUTE PROCEDURE _api.user_update();
generated by cgit v1.2.3-freya (git 2.51.0) at 2025-09-23 06:04:00 +0000