27 lines
2.5 KiB
SQL
27 lines
2.5 KiB
SQL
CREATE TABLE pads (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(255) NOT NULL UNIQUE, title TEXT NOT NULL, content LONGTEXT NOT NULL DEFAULT (''), password_hash TEXT,
|
|
created_at VARCHAR(64) NOT NULL DEFAULT (CURRENT_TIMESTAMP), updated_at VARCHAR(64) NOT NULL DEFAULT (CURRENT_TIMESTAMP), owner_map LONGTEXT NOT NULL DEFAULT ('[]'), file_token VARCHAR(255) UNIQUE
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE revisions (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY, pad_id BIGINT NOT NULL, content LONGTEXT NOT NULL, created_at VARCHAR(64) NOT NULL DEFAULT (CURRENT_TIMESTAMP), author TEXT, owner_map LONGTEXT NOT NULL DEFAULT ('[]'),
|
|
CONSTRAINT fk_revisions_pad FOREIGN KEY (pad_id) REFERENCES pads(id) ON DELETE CASCADE, INDEX idx_revisions_pad_id (pad_id, id DESC)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE workspaces (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(255) NOT NULL UNIQUE, title TEXT NOT NULL, password_hash TEXT,
|
|
created_at VARCHAR(64) NOT NULL DEFAULT (CURRENT_TIMESTAMP), updated_at VARCHAR(64) NOT NULL DEFAULT (CURRENT_TIMESTAMP)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE notes (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY, workspace_id BIGINT NOT NULL, slug VARCHAR(255) NOT NULL, title TEXT NOT NULL, content LONGTEXT NOT NULL DEFAULT (''),
|
|
created_at VARCHAR(64) NOT NULL DEFAULT (CURRENT_TIMESTAMP), updated_at VARCHAR(64) NOT NULL DEFAULT (CURRENT_TIMESTAMP), owner_map LONGTEXT NOT NULL DEFAULT ('[]'), file_token VARCHAR(255) UNIQUE,
|
|
CONSTRAINT fk_notes_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE, UNIQUE KEY uq_notes_workspace_slug (workspace_id, slug), INDEX idx_notes_workspace (workspace_id, updated_at DESC)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE note_revisions (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY, note_id BIGINT NOT NULL, content LONGTEXT NOT NULL, created_at VARCHAR(64) NOT NULL DEFAULT (CURRENT_TIMESTAMP), author TEXT, owner_map LONGTEXT NOT NULL DEFAULT ('[]'),
|
|
CONSTRAINT fk_note_revisions_note FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE, INDEX idx_note_revisions_note (note_id, id DESC)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE published_pages (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY, token VARCHAR(255) NOT NULL UNIQUE, pad_id BIGINT UNIQUE, note_id BIGINT UNIQUE, created_at VARCHAR(64) NOT NULL DEFAULT (CURRENT_TIMESTAMP),
|
|
CONSTRAINT fk_published_pad FOREIGN KEY (pad_id) REFERENCES pads(id) ON DELETE CASCADE, CONSTRAINT fk_published_note FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
|
|
CHECK ((pad_id IS NOT NULL AND note_id IS NULL) OR (pad_id IS NULL AND note_id IS NOT NULL))
|
|
) ENGINE=InnoDB;
|