ZNVxYD5c

1CREATE EXTENSION IF NOT EXISTS "unaccent";
2
3CREATE OR REPLACE FUNCTION slugify("value" TEXT)
4RETURNS TEXT AS $$
5  -- removes accents (diacritic signs) from a given string --
6  WITH "unaccented" AS (
7    SELECT unaccent("value") AS "value"
8  ),
9  -- lowercases the string
10  "lowercase" AS (
11    SELECT lower("value") AS "value"
12    FROM "unaccented"
13  ),
14  -- remove single and double quotes
15  "removed_quotes" AS (
16    SELECT regexp_replace("value", '[''"]+', '', 'gi') AS "value"
17    FROM "lowercase"
18  ),
19  -- replaces anything that's not a letter, number, hyphen('-'), or underscore('_') with a hyphen('-')
20  "hyphenated" AS (
21    SELECT regexp_replace("value", '[^a-z0-9\\-_]+', '-', 'gi') AS "value"
22    FROM "removed_quotes"
23  ),
24  -- trims hyphens('-') if they exist on the head or tail of the string
25  "trimmed" AS (
26    SELECT regexp_replace(regexp_replace("value", '\-+$', ''), '^\-', '') AS "value"
27    FROM "hyphenated"
28  )
29  SELECT "value" FROM "trimmed";
30$$ LANGUAGE SQL STRICT IMMUTABLE;
31
32CREATE FUNCTION public.set_slug_from_title() RETURNS trigger
33    LANGUAGE plpgsql
34    AS $$
35BEGIN
36  NEW.slug := slugify(NEW.title);
37  RETURN NEW;
38END
39$$;
40
41CREATE TABLE permissions (
42    id BIGSERIAL PRIMARY KEY,
43    name VARCHAR NOT NULL,
44    guard_name VARCHAR,
45    created_at timestamp,
46    updated_at timestamp
47);
48
49CREATE TABLE roles (
50    id BIGSERIAL PRIMARY KEY,
51    name VARCHAR NOT NULL,
52    guard_name VARCHAR,
53    created_at timestamp,
54    updated_at timestamp
55);
56
57CREATE TABLE user_role (
58    role_id BIGINT NOT NULL REFERENCES roles ON DELETE CASCADE ON UPDATE CASCADE,
59    user_id BIGINT NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE,
60    PRIMARY KEY (user_id, role_id)
61);
62
63
64CREATE TABLE permission_role (
65    permission_id BIGINT NOT NULL REFERENCES permissions ON DELETE CASCADE ON UPDATE CASCADE,
66    role_id BIGINT NOT NULL REFERENCES roles ON DELETE CASCADE ON UPDATE CASCADE,
67    PRIMARY KEY (permission_id, role_id)
68);
69
70
71CREATE TRIGGER "t_permissions_insert" BEFORE INSERT ON "permissions" FOR EACH ROW WHEN (NEW.name IS NOT NULL AND NEW.guard_name IS NULL)
72EXECUTE PROCEDURE set_slug_from_title();
73
74CREATE TRIGGER "t_roles_insert" BEFORE INSERT ON "roles" FOR EACH ROW WHEN (NEW.name IS NOT NULL AND NEW.guard_name IS NULL)
75EXECUTE PROCEDURE set_slug_from_title();