57 lines
1.6 KiB
PL/PgSQL
57 lines
1.6 KiB
PL/PgSQL
CREATE TABLE IF NOT EXISTS glyph_users (
|
|
name TEXT PRIMARY KEY,
|
|
display_name TEXT NOT NULL,
|
|
password TEXT NOT NULL,
|
|
email TEXT NOT NULL UNIQUE,
|
|
disabled BOOLEAN NOT NULL,
|
|
picture TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS glyph_groups (
|
|
name TEXT PRIMARY KEY,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS glyph_users_groups (
|
|
user_name TEXT NOT NULL,
|
|
group_name TEXT NOT NULL,
|
|
PRIMARY KEY (user_name, group_name),
|
|
FOREIGN KEY (user_name) REFERENCES glyph_users(name) ON DELETE CASCADE,
|
|
FOREIGN KEY (group_name) REFERENCES glyph_groups(name) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION glyph_update_timestamp()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE TRIGGER glyph_update_users_timestamp
|
|
BEFORE UPDATE ON glyph_users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION glyph_update_timestamp();
|
|
|
|
CREATE OR REPLACE TRIGGER glyph_update_groups_timestamp
|
|
BEFORE UPDATE ON glyph_groups
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION glyph_update_timestamp();
|
|
|
|
CREATE OR REPLACE FUNCTION glyph_update_users_groups_timestamp()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
UPDATE glyph_users SET updated_at = NOW() WHERE name = NEW.user_name;
|
|
UPDATE glyph_groups SET updated_at = NOW() WHERE name = NEW.group_name;
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE TRIGGER glyph_update_users_groups_timestamp
|
|
AFTER INSERT OR DELETE ON glyph_users_groups
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION glyph_update_users_groups_timestamp();
|