witten
/
luminotes
Archived
1
0
Fork 0
This repository has been archived on 2023-12-16. You can view files and clone it, but cannot push or open issues or pull requests.
luminotes/model/schema.sqlite

217 lines
5.8 KiB
Plaintext

--
-- SQLite database schema
--
CREATE TABLE file (
id text NOT NULL,
revision timestamp with time zone,
notebook_id text,
note_id text,
filename text,
size_bytes integer,
content_type text
);
CREATE TABLE tag (
id text NOT NULL,
revision timestamp with time zone,
notebook_id text,
user_id text,
name text,
description text
);
CREATE TABLE tag_notebook (
notebook_id text,
tag_id text,
value text,
user_id text
);
CREATE TABLE tag_note (
note_id text,
tag_id text,
value text
);
CREATE TABLE invite (
id text NOT NULL,
revision timestamp with time zone NOT NULL,
from_user_id text,
notebook_id text,
email_address text,
read_write boolean,
"owner" boolean,
redeemed_user_id text
);
CREATE TABLE luminotes_group (
id text NOT NULL,
revision timestamp with time zone NOT NULL,
name text
);
CREATE VIEW luminotes_group_current AS
SELECT id, revision, name FROM luminotes_group WHERE (luminotes_group.revision IN (SELECT max(sub_group.revision) AS max FROM luminotes_group sub_group WHERE (sub_group.id = luminotes_group.id)));
CREATE TABLE luminotes_user (
id text NOT NULL,
revision timestamp with time zone NOT NULL,
username text,
salt text,
password_hash text,
email_address text,
storage_bytes integer,
rate_plan integer
);
CREATE VIEW luminotes_user_current AS
SELECT id, revision, username, salt, password_hash, email_address, storage_bytes, rate_plan FROM luminotes_user WHERE (luminotes_user.revision IN (SELECT max(sub_user.revision) AS max FROM luminotes_user sub_user WHERE (sub_user.id = luminotes_user.id)));
CREATE TABLE note (
id text NOT NULL,
revision timestamp with time zone NOT NULL,
title text,
contents text,
notebook_id text,
startup boolean DEFAULT false,
deleted_from_id text,
rank numeric,
search tsvector,
user_id text
);
CREATE TABLE note_current (
id text NOT NULL,
revision timestamp with time zone NOT NULL,
title text,
contents text,
notebook_id text,
startup boolean DEFAULT false,
deleted_from_id text,
rank numeric,
user_id text
);
CREATE TABLE notebook (
id text NOT NULL,
revision timestamp with time zone NOT NULL,
name text,
trash_id text,
deleted boolean DEFAULT false,
user_id text
);
CREATE VIEW notebook_current AS
SELECT id, revision, name, trash_id, deleted, user_id FROM notebook WHERE (notebook.revision IN (SELECT max(sub_notebook.revision) AS max FROM notebook sub_notebook WHERE (sub_notebook.id = notebook.id))) and notebook.name is not null;
CREATE TABLE password_reset (
id text NOT NULL,
revision timestamp with time zone NOT NULL,
email_address text,
redeemed boolean
);
CREATE TABLE download_access (
id text NOT NULL,
revision timestamp with time zone NOT NULL,
item_number text,
transaction_id text
);
CREATE TABLE user_group (
user_id text NOT NULL,
group_id text NOT NULL,
"admin" boolean DEFAULT false
);
CREATE TABLE user_notebook (
user_id text NOT NULL,
notebook_id text NOT NULL,
read_write boolean DEFAULT false,
"owner" boolean DEFAULT false,
rank numeric,
own_notes_only boolean DEFAULT false
);
CREATE INDEX file_pkey ON file (id);
CREATE INDEX tag_pkey ON tag (id);
CREATE INDEX tag_notebook_pkey ON tag_notebook (user_id, notebook_id, tag_id);
CREATE INDEX tag_note_pkey ON tag_note (note_id, tag_id);
CREATE INDEX invite_pkey ON invite (id);
CREATE INDEX luminotes_user_pkey ON luminotes_user (id, revision);
CREATE INDEX note_pkey ON note (id, revision);
CREATE INDEX note_current_pkey ON note_current (id);
CREATE INDEX notebook_pkey ON notebook (id, revision);
CREATE INDEX password_reset_pkey ON password_reset (id);
CREATE INDEX download_access_pkey ON download_access (id);
CREATE INDEX user_notebook_pkey ON user_notebook (user_id, notebook_id);
CREATE INDEX file_note_id_index ON file (note_id);
CREATE INDEX file_notebook_id_index ON file (notebook_id);
CREATE INDEX tag_notebook_id_index ON tag (notebook_id);
CREATE INDEX tag_user_id_index ON tag (user_id);
CREATE INDEX luminotes_group_pkey ON luminotes_group (id, revision);
CREATE INDEX luminotes_user_email_address_index ON luminotes_user (email_address);
CREATE INDEX luminotes_user_username_index ON luminotes_user (username);
CREATE INDEX note_notebook_id_index ON note (notebook_id);
CREATE INDEX note_notebook_id_startup_index ON note (notebook_id, startup);
CREATE INDEX note_notebook_id_title_index ON note (notebook_id, title);
CREATE INDEX note_user_id_index ON note (user_id);
CREATE INDEX note_current_notebook_id_index ON note_current (notebook_id);
CREATE INDEX note_current_notebook_id_startup_index ON note_current (notebook_id, startup);
CREATE INDEX note_current_notebook_id_title_index ON note_current (notebook_id, title);
CREATE INDEX note_current_user_id_index ON note_current (user_id);
CREATE INDEX password_reset_id_index ON password_reset (id);
CREATE INDEX password_reset_email_address_index ON password_reset (email_address);
CREATE INDEX download_access_id_index ON password_reset (id);
CREATE INDEX download_access_transaction_id_index ON download_access (transaction_id);
CREATE INDEX search_index ON note (search);
create trigger note_current_insert
after insert on note_current
for each row
begin
insert into note values ( NEW.id, NEW.revision, NEW.title, NEW.contents, NEW.notebook_id,
NEW.startup, NEW.deleted_from_id, NEW.rank, null, NEW.user_id );
end;
create trigger note_current_update
after update on note_current
for each row
begin
insert into note values ( NEW.id, NEW.revision, NEW.title, NEW.contents, NEW.notebook_id,
NEW.startup, NEW.deleted_from_id, NEW.rank, null, NEW.user_id );
end;