Slugify and Pathify

20230504090000_tracks.sql

CREATE TABLE public.artists (
    title character varying NOT NULL,
    slug character varying NOT NULL,
    id SERIAL PRIMARY KEY -- I use a different ID technique
);


ALTER TABLE public.artists OWNER TO postgres;

CREATE TABLE public.tracks (
    title character varying NOT NULL,
    slug character varying NOT NULL,
    path character varying NOT NULL,
    artist public.shortkey NOT NULL,
    id SERIAL PRIMARY KEY -- I use a different ID technique
);


ALTER TABLE public.tracks OWNER TO postgres;

20230504100000_slugify.sql

DROP TRIGGER trigger_artists_slug ON artists;
DROP TRIGGER trigger_shows_slug ON shows;
DROP TRIGGER trigger_chapters_slug ON chapters;
DROP TRIGGER trigger_tracks_slug ON tracks;


CREATE EXTENSION IF NOT EXISTS unaccent;

CREATE OR REPLACE FUNCTION public.slugify(value TEXT) RETURNS TEXT
  LANGUAGE plpgsql
  STRICT IMMUTABLE AS
$function$
BEGIN
  -- 1. trim trailing and leading whitespaces from text
  -- 2. remove accents (diacritic signs) from a given text
  -- 3. lowercase unaccented text
  -- 4. replace non-alphanumeric with a hyphen
  -- 5. trim leading and trailing hyphens
  RETURN trim(BOTH '-' FROM regexp_replace(lower(unaccent(trim(value))), '[^a-z0-9\\-]+', '-', 'gi'));
END;
$function$;

CREATE OR REPLACE FUNCTION public.generate_slug_from_title() RETURNS trigger
  LANGUAGE plpgsql
  AS $$
BEGIN
  IF NEW.slug IS NULL AND NEW.title IS NOT NULL THEN
    NEW.slug := slugify(NEW.title);
  END IF;
  RETURN NEW;
END
$$;

ALTER FUNCTION public.slugify(value TEXT) OWNER TO postgres;

CREATE TRIGGER trigger_profiles_slug BEFORE INSERT ON public.profiles FOR EACH ROW EXECUTE PROCEDURE generate_slug_from_title();
CREATE TRIGGER trigger_shows_slug BEFORE INSERT ON public.shows FOR EACH ROW EXECUTE PROCEDURE generate_slug_from_title();
CREATE TRIGGER trigger_chapters_slug BEFORE INSERT ON public.chapters FOR EACH ROW EXECUTE PROCEDURE generate_slug_from_title();
CREATE TRIGGER trigger_tracks_slug BEFORE INSERT ON public.tracks FOR EACH ROW EXECUTE PROCEDURE generate_slug_from_title();
CREATE TRIGGER trigger_artists_slug BEFORE INSERT ON public.artists FOR EACH ROW EXECUTE PROCEDURE generate_slug_from_title();

20230504110000_pathify.sql

CREATE OR REPLACE FUNCTION public.trackPathify() RETURNS trigger
  LANGUAGE plpgsql
  AS $$
DECLARE
artist_slug TEXT;
BEGIN
  IF NEW.path IS NULL AND NEW.slug IS NOT NULL AND NEW.artist IS NOT NULL THEN
    artist_slug = (SELECT slug FROM artists WHERE id = NEW.artist);
    IF artist_slug IS NOT NULL THEN
      NEW.path := concat(
        '/artists/',
        (SELECT slug FROM artists WHERE id = NEW.artist),
        '/tracks/',
        NEW.slug
      );
    ELSE
      RAISE EXCEPTION 'Artist not found';
    END IF;
  END IF;
  RETURN NEW;
END
$$;

ALTER FUNCTION public.trackPathify() OWNER TO postgres;

-- NOTE the trigger name is alphabetical order after the slugify trigger as this trigger depends on the slug to be present first
CREATE TRIGGER trigger_tracks_slug_path BEFORE INSERT ON public.tracks FOR EACH ROW EXECUTE PROCEDURE public.trackPathify();