login and register, liking on homepage

8 files changed, 243 insertions, 0 deletions

diff --git a/src/db/rest/util/_api_get_user_id.sql b/src/db/rest/util/_api_get_user_id.sql
new file mode 100644
index 0000000..e86afc3
--- /dev/null
+++ b/src/db/rest/util/_api_get_user_id.sql
@@ -0,0 +1,22 @@
+CREATE FUNCTION _api.get_user_id()
+RETURNS INTEGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = CURRENT_SETTING(
+		'request.jwt.claims',
+		TRUE
+	)::JSON->>'user_id';
+
+	UPDATE admin.user
+	SET seen = clock_timestamp()
+	WHERE id = _user_id;
+
+	RETURN _user_id;
+END
+$BODY$;
+
+GRANT UPDATE ON TABLE admin.user
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_raise.sql b/src/db/rest/util/_api_raise.sql
new file mode 100644
index 0000000..5c740c6
--- /dev/null
+++ b/src/db/rest/util/_api_raise.sql
@@ -0,0 +1,50 @@
+CREATE TABLE _api.err_map (
+	err		INTEGER,
+	pg_err	TEXT
+);
+
+ALTER TABLE _api.err_map OWNER TO xssbook;
+
+ALTER TABLE ONLY _api.err_map
+	ADD CONSTRAINT err_map_pkey PRIMARY KEY (err);
+
+INSERT INTO _api.err_map (err, pg_err)
+VALUES
+	(400, 'P0001'),
+	(401, '42501'),
+	(403, '42501'),
+	(404, '42883'),
+	(409, '23505'),
+	(500, 'XX001');
+
+CREATE FUNCTION _api.raise(
+	_msg TEXT DEFAULT '',
+	_detail TEXT DEFAULT '',
+	_hint TEXT DEFAULT '',
+	_err INTEGER DEFAULT 400
+)
+RETURNS BOOLEAN
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_pg_err TEXT;
+BEGIN
+
+	SELECT pg_err INTO _pg_err
+		FROM _api.err_map
+		WHERE err = _err;
+
+	RAISE EXCEPTION USING
+		MESSAGE := _msg,
+		DETAIL := _detail,
+		HINT := _hint,
+		ERRCODE := _pg_err;
+
+	RETURN FALSE;
+END
+$BODY$;
+
+GRANT SELECT ON TABLE _api.err_map
+	TO rest_anon, rest_user;
+GRANT EXECUTE ON FUNCTION _api.raise(TEXT, TEXT, TEXT, INTEGER)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_raise_deny.sql b/src/db/rest/util/_api_raise_deny.sql
new file mode 100644
index 0000000..17406b7
--- /dev/null
+++ b/src/db/rest/util/_api_raise_deny.sql
@@ -0,0 +1,16 @@
+CREATE FUNCTION _api.raise_deny()
+RETURNS BOOLEAN
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+BEGIN
+	PERFORM _api.raise(
+		_msg => 'api_denied',
+		_err => 403
+	);
+
+	RETURN TRUE;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.raise_null(TEXT)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_raise_null.sql b/src/db/rest/util/_api_raise_null.sql
new file mode 100644
index 0000000..be6ee29
--- /dev/null
+++ b/src/db/rest/util/_api_raise_null.sql
@@ -0,0 +1,18 @@
+CREATE FUNCTION _api.raise_null(
+	_column TEXT DEFAULT ''
+)
+RETURNS BOOLEAN
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+BEGIN
+	PERFORM _api.raise(
+		_msg => 'api_null_value',
+		_detail => _column
+	);
+
+	RETURN TRUE;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.raise_null(TEXT)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_raise_unique.sql b/src/db/rest/util/_api_raise_unique.sql
new file mode 100644
index 0000000..a18d960
--- /dev/null
+++ b/src/db/rest/util/_api_raise_unique.sql
@@ -0,0 +1,18 @@
+CREATE FUNCTION _api.raise_unique(
+	_column TEXT DEFAULT ''
+)
+RETURNS BOOLEAN
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+BEGIN
+	PERFORM _api.raise(
+		_msg => 'api_unique_value',
+		_detail => _column
+	);
+
+	RETURN TRUE;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.raise_unique(TEXT)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_serve_media.sql b/src/db/rest/util/_api_serve_media.sql
new file mode 100644
index 0000000..8b0f0b8
--- /dev/null
+++ b/src/db/rest/util/_api_serve_media.sql
@@ -0,0 +1,41 @@
+CREATE FUNCTION _api.serve_media(
+	_name TEXT
+)
+RETURNS sys."*/*"
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_headers TEXT;
+	_data BYTEA;
+BEGIN
+
+	SELECT FORMAT(
+      '[{"Content-Type": "%s"},'
+       '{"Content-Disposition": "inline; filename=\"%s\""},'
+       '{"Cache-Control": "max-age=259200"}]'
+      ,	m.type, m.name)
+	FROM admin.media m
+	WHERE m.name = _name INTO _headers;
+
+	PERFORM SET_CONFIG('response.headers', _headers, true);
+
+	SELECT m.content
+	FROM admin.media m
+	WHERE m.name = _name
+	INTO _data;
+
+	IF FOUND THEN
+		RETURN(_data);
+	ELSE
+		PERFORM _api.raise(
+			_msg => 'api_not_found',
+			_err => 404
+		);
+	END IF;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.serve_media(TEXT)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.media
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_trim.sql b/src/db/rest/util/_api_trim.sql
new file mode 100644
index 0000000..c972282
--- /dev/null
+++ b/src/db/rest/util/_api_trim.sql
@@ -0,0 +1,25 @@
+CREATE FUNCTION _api.trim(
+	_text TEXT
+)
+RETURNS TEXT
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_new TEXT;
+BEGIN
+
+	IF _text IS NULL THEN
+		RETURN NULL;
+	END IF;
+
+	_new = _text;
+	_new = TRIM(_new);
+	_new = REGEXP_REPLACE(_new, '^(?: |\r|\n)*', '');
+	_new = REGEXP_REPLACE(_new, '(?: |\r|\n)*$', '');
+
+	RETURN _new;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.trim(TEXT)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_validate_text.sql b/src/db/rest/util/_api_validate_text.sql
new file mode 100644
index 0000000..ff3a227
--- /dev/null
+++ b/src/db/rest/util/_api_validate_text.sql
@@ -0,0 +1,53 @@
+CREATE FUNCTION _api.validate_text(
+	_column TEXT DEFAULT '',
+	_text TEXT DEFAULT NULL,
+	_min INTEGER DEFAULT NULL,
+	_max INTEGER DEFAULT NULL,
+	_nullable BOOLEAN DEFAULT FALSE
+)
+RETURNS BOOLEAN
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_length INTEGER;
+BEGIN
+
+	-- make sure that text can only be null
+	-- when we allow it
+	IF _text IS NULL AND NOT _nullable THEN
+		PERFORM _api.raise(
+			_msg => 'api_null_value',
+			_detail => _column
+		);
+	END IF;
+
+	IF _text IS NULL THEN
+		RETURN TRUE;
+	END IF;
+
+	_length = LENGTH(_text);
+
+	IF _min IS NOT NULL AND _length < _min THEN
+		PERFORM _api.raise(
+			_msg => 'api_min_value',
+			_detail => _column,
+			_hint => _min || ''
+		);
+		RETURN FALSE;
+	END IF;
+
+	IF _max IS NOT NULL AND _length > _max THEN
+		PERFORM _api.raise(
+			_msg => 'api_max_value',
+			_detail => _column,
+			_hint => _max || ''
+		);
+		RETURN FALSE;
+	END IF;
+
+	RETURN TRUE;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.validate_text(TEXT, TEXT, INTEGER, INTEGER, BOOLEAN)
+	TO rest_anon, rest_user;