280 lines
8 KiB
PL/PgSQL
280 lines
8 KiB
PL/PgSQL
-- Chattyness Helper Functions
|
|
-- PostgreSQL 18
|
|
--
|
|
-- Utility functions and common operations
|
|
-- Load via: psql -f schema/functions/001_helpers.sql
|
|
|
|
\set ON_ERROR_STOP on
|
|
|
|
BEGIN;
|
|
|
|
-- =============================================================================
|
|
-- Updated At Trigger Function
|
|
-- =============================================================================
|
|
-- Automatically updates updated_at column on row modification.
|
|
-- =============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION public.update_updated_at_column() IS
|
|
'Trigger function to automatically update updated_at timestamp';
|
|
|
|
-- =============================================================================
|
|
-- Session Context Functions
|
|
-- =============================================================================
|
|
-- Functions to get/set session context for RLS policies.
|
|
-- Application sets these variables when handling requests.
|
|
-- =============================================================================
|
|
|
|
-- Set current user ID for RLS
|
|
CREATE OR REPLACE FUNCTION public.set_current_user_id(user_id UUID)
|
|
RETURNS VOID AS $$
|
|
BEGIN
|
|
PERFORM set_config('app.current_user_id', user_id::TEXT, false);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Get current user ID for RLS
|
|
CREATE OR REPLACE FUNCTION public.current_user_id()
|
|
RETURNS UUID AS $$
|
|
BEGIN
|
|
RETURN NULLIF(current_setting('app.current_user_id', true), '')::UUID;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE;
|
|
|
|
-- Set current realm ID for RLS
|
|
CREATE OR REPLACE FUNCTION public.set_current_realm_id(realm_id UUID)
|
|
RETURNS VOID AS $$
|
|
BEGIN
|
|
PERFORM set_config('app.current_realm_id', realm_id::TEXT, false);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Get current realm ID for RLS
|
|
CREATE OR REPLACE FUNCTION public.current_realm_id()
|
|
RETURNS UUID AS $$
|
|
BEGIN
|
|
RETURN NULLIF(current_setting('app.current_realm_id', true), '')::UUID;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE;
|
|
|
|
-- Set current guest session ID for RLS
|
|
CREATE OR REPLACE FUNCTION public.set_current_guest_session_id(guest_session_id UUID)
|
|
RETURNS VOID AS $$
|
|
BEGIN
|
|
PERFORM set_config('app.current_guest_session_id', guest_session_id::TEXT, false);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Get current guest session ID for RLS
|
|
CREATE OR REPLACE FUNCTION public.current_guest_session_id()
|
|
RETURNS UUID AS $$
|
|
BEGIN
|
|
RETURN NULLIF(current_setting('app.current_guest_session_id', true), '')::UUID;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE;
|
|
|
|
-- Check if current user is a server admin
|
|
CREATE OR REPLACE FUNCTION public.is_server_admin()
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1 FROM server.staff
|
|
WHERE user_id = public.current_user_id()
|
|
AND role IN ('owner', 'admin')
|
|
);
|
|
EXCEPTION
|
|
WHEN OTHERS THEN RETURN FALSE;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
|
|
|
|
-- Check if current user is a server moderator (or higher)
|
|
CREATE OR REPLACE FUNCTION public.is_server_moderator()
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1 FROM server.staff
|
|
WHERE user_id = public.current_user_id()
|
|
AND role IN ('owner', 'admin', 'moderator')
|
|
);
|
|
EXCEPTION
|
|
WHEN OTHERS THEN RETURN FALSE;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
|
|
|
|
-- Check if current user is a realm owner/moderator
|
|
CREATE OR REPLACE FUNCTION public.is_realm_moderator(check_realm_id UUID)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
-- Server admins are moderators everywhere
|
|
IF public.is_server_admin() THEN
|
|
RETURN TRUE;
|
|
END IF;
|
|
|
|
RETURN EXISTS (
|
|
SELECT 1 FROM realm.memberships
|
|
WHERE realm_id = check_realm_id
|
|
AND user_id = public.current_user_id()
|
|
AND role IN ('owner', 'moderator')
|
|
);
|
|
EXCEPTION
|
|
WHEN OTHERS THEN RETURN FALSE;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
|
|
|
|
-- Check if current user has membership in a realm
|
|
CREATE OR REPLACE FUNCTION public.has_realm_membership(check_realm_id UUID)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1 FROM realm.memberships
|
|
WHERE realm_id = check_realm_id
|
|
AND user_id = public.current_user_id()
|
|
);
|
|
EXCEPTION
|
|
WHEN OTHERS THEN RETURN FALSE;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
|
|
|
|
-- =============================================================================
|
|
-- Friendship Helper Functions
|
|
-- =============================================================================
|
|
|
|
-- Check if two users are friends
|
|
CREATE OR REPLACE FUNCTION auth.are_friends(user_a UUID, user_b UUID)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1 FROM auth.friendships
|
|
WHERE friend_a = LEAST(user_a, user_b)
|
|
AND friend_b = GREATEST(user_a, user_b)
|
|
AND is_accepted = true
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE;
|
|
|
|
-- Check if user_a has blocked user_b
|
|
CREATE OR REPLACE FUNCTION auth.has_blocked(blocker UUID, blocked UUID)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1 FROM auth.blocks
|
|
WHERE blocker_id = blocker
|
|
AND blocked_id = blocked
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE;
|
|
|
|
-- Check if either user has blocked the other
|
|
CREATE OR REPLACE FUNCTION auth.is_blocked_either_way(user_a UUID, user_b UUID)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1 FROM auth.blocks
|
|
WHERE (blocker_id = user_a AND blocked_id = user_b)
|
|
OR (blocker_id = user_b AND blocked_id = user_a)
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE;
|
|
|
|
-- =============================================================================
|
|
-- Spatial Helper Functions
|
|
-- =============================================================================
|
|
|
|
-- Create a virtual point from x,y coordinates
|
|
CREATE OR REPLACE FUNCTION public.make_virtual_point(x REAL, y REAL)
|
|
RETURNS public.virtual_point AS $$
|
|
BEGIN
|
|
RETURN ST_SetSRID(ST_MakePoint(x, y), 0)::public.virtual_point;
|
|
END;
|
|
$$ LANGUAGE plpgsql IMMUTABLE;
|
|
|
|
-- Create scene bounds from width and height (origin at 0,0)
|
|
CREATE OR REPLACE FUNCTION public.make_scene_bounds(width REAL, height REAL)
|
|
RETURNS public.scene_bounds AS $$
|
|
BEGIN
|
|
RETURN ST_MakeEnvelope(0, 0, width, height, 0)::public.scene_bounds;
|
|
END;
|
|
$$ LANGUAGE plpgsql IMMUTABLE;
|
|
|
|
-- Get distance between two virtual points
|
|
CREATE OR REPLACE FUNCTION public.virtual_distance(p1 public.virtual_point, p2 public.virtual_point)
|
|
RETURNS REAL AS $$
|
|
BEGIN
|
|
RETURN ST_Distance(p1, p2)::REAL;
|
|
END;
|
|
$$ LANGUAGE plpgsql IMMUTABLE;
|
|
|
|
-- Check if a point is within scene bounds
|
|
CREATE OR REPLACE FUNCTION public.point_in_bounds(
|
|
point public.virtual_point,
|
|
bounds public.scene_bounds
|
|
)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN ST_Within(point, bounds);
|
|
END;
|
|
$$ LANGUAGE plpgsql IMMUTABLE;
|
|
|
|
-- =============================================================================
|
|
-- Audit Helper Functions
|
|
-- =============================================================================
|
|
|
|
-- Log an audit event
|
|
CREATE OR REPLACE FUNCTION audit.log_event(
|
|
p_category audit.event_category,
|
|
p_action TEXT,
|
|
p_target_type TEXT DEFAULT NULL,
|
|
p_target_id UUID DEFAULT NULL,
|
|
p_details JSONB DEFAULT '{}',
|
|
p_success BOOLEAN DEFAULT TRUE,
|
|
p_error_message TEXT DEFAULT NULL
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_event_id UUID;
|
|
BEGIN
|
|
INSERT INTO audit.events (
|
|
category,
|
|
action,
|
|
user_id,
|
|
ip_address,
|
|
target_type,
|
|
target_id,
|
|
realm_id,
|
|
details,
|
|
success,
|
|
error_message
|
|
) VALUES (
|
|
p_category,
|
|
p_action,
|
|
public.current_user_id(),
|
|
NULLIF(current_setting('app.client_ip', true), '')::INET,
|
|
p_target_type,
|
|
p_target_id,
|
|
public.current_realm_id(),
|
|
p_details,
|
|
p_success,
|
|
p_error_message
|
|
)
|
|
RETURNING id INTO v_event_id;
|
|
|
|
RETURN v_event_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION audit.log_event IS 'Helper to create audit log entries';
|
|
|
|
COMMIT;
|