49 lines
1.5 KiB
SQL
49 lines
1.5 KiB
SQL
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
DROP Table if exists entry_categories;
|
|
drop table if exists categories;
|
|
drop table if exists entries;
|
|
drop table if exists users;
|
|
|
|
CREATE TABLE users (
|
|
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
uid UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
|
|
created_at timestamp NOT NULL DEFAULT NOW(),
|
|
updated_at timestamp,
|
|
real_name text NOT NULL,
|
|
username text NOT NULL UNIQUE,
|
|
hash text NOT NULL,
|
|
email text NOT NULL,
|
|
pubkey jsonb NOT NULL
|
|
);
|
|
|
|
CREATE TABLE categories (
|
|
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
created_at timestamp NOT NULL DEFAULT NOW(),
|
|
updated_at timestamp,
|
|
name text NOT NULL,
|
|
descr text NOT NULL
|
|
);
|
|
|
|
CREATE TABLE entries (
|
|
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
pubic bool DEFAULT TRUE,
|
|
created_at timestamp NOT NULL DEFAULT NOW(),
|
|
updated_at timestamp,
|
|
title text NOT NULL DEFAULT '',
|
|
descr text NOT NULL DEFAULT '',
|
|
signature text NOT NULL
|
|
);
|
|
|
|
CREATE TABLE entry_categories (
|
|
user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
entry_id INTEGER NOT NULL REFERENCES entries (id) ON DELETE CASCADE,
|
|
category_id INTEGER NOT NULL REFERENCES categories (id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX entry_trgm_idx ON entries USING gist (title, descr gist_trgm_ops);
|