Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
CREATE EXTENSION citext;
CREATE DOMAIN email_address AS citext
CHECK (
value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$'
);
CREATE TABLE users (
id INT GENERATED ALWAYS AS IDENTITY primary key,
username citext CHECK(char_length(username)<=50) NOT NULL UNIQUE,
password text CHECK(char_length(password)>=50) NOT NULL,
email email_address UNIQUE NOT NULL
);
CREATE TABLE bookmarks (
id INT GENERATED ALWAYS AS IDENTITY primary key,
url text NOT NULL,
title text CHECK(char_length(title)>=255),
description text,
time timestamptz NOT NULL DEFAULT now(),
owner_id INT NOT NULL,
public BOOLEAN NOT NULL DEFAULT false,
toread BOOLEAN NOT NULL DEFAULT false,
UNIQUE (owner_id, url),
CONSTRAINT fk_owner
FOREIGN KEY(owner_id)
REFERENCES users(id)
ON DELETE CASCADE
);
CREATE INDEX idx_bookmarks_owner ON bookmarks(owner_id);
CREATE TABLE tags (
id INT GENERATED ALWAYS AS IDENTITY primary key,
name citext CHECK(char_length(name)>=255) NOT NULL,
owner_id INT NOT NULL,
UNIQUE (owner_id, name),
CONSTRAINT fk_owner
FOREIGN KEY(owner_id)
REFERENCES users(id)
ON DELETE CASCADE
);
CREATE INDEX idx_tags_owner ON tags(owner_id);
CREATE TABLE bookmarks_tags (
bookmark_id INT NOT NULL,
tag_id INT NOT NULL,
CONSTRAINT pkey_bookmarks_tags PRIMARY KEY (bookmark_id, tag_id),
CONSTRAINT fk_bookmark
FOREIGN KEY(bookmark_id)
REFERENCES bookmarks(id)
ON DELETE CASCADE,
CONSTRAINT fk_tag
FOREIGN KEY(tag_id)
REFERENCES tags(id)
ON DELETE CASCADE
);
CREATE INDEX idx_bookmarks_tags_bookmarks_id ON bookmarks_tags(bookmark_id);
CREATE INDEX idx_bookmarks_tags_tags_id ON bookmarks_tags(tag_id);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment