You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
73 lines
2.0 KiB
MySQL
73 lines
2.0 KiB
MySQL
6 months ago
|
PRAGMA foreign_keys=1;
|
||
|
|
CREATE TABLE user (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
email TEXT UNIQUE NOT NULL,
|
||
|
|
password TEXT NOT NULL,
|
||
|
|
kindle_email TEXT
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE feed (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
user_id INTEGER UNIQUE NOT NULL,
|
||
|
|
url TEXT,
|
||
|
|
type TEXT NOT NULL,
|
||
|
|
name TEXT UNIQUE,
|
||
|
|
icon_url TEXT,
|
||
|
|
created TIMESTAMP NOT NULL,
|
||
|
|
updated TIMESTAMP NOT NULL,
|
||
|
|
last_fetch TIMESTAMP,
|
||
|
|
raw_data TEXT,
|
||
|
|
folder TEXT,
|
||
|
|
etag TEXT,
|
||
|
|
modified_header TEXT,
|
||
|
|
filters TEXT,
|
||
|
|
FOREIGN KEY(user_id) REFERENCES user(id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE entry (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
feed_id INTEGER UNIQUE,
|
||
|
|
user_id INTEGER NOT NULL,
|
||
|
|
remote_id TEXT UNIQUE NOT NULL,
|
||
|
|
title TEXT,
|
||
|
|
username TEXT,
|
||
|
|
user_url TEXT,
|
||
|
|
display_name TEXT,
|
||
|
|
avatar_url TEXT,
|
||
|
|
content_short TEXT,
|
||
|
|
content_full TEXT,
|
||
|
|
target_url TEXT,
|
||
|
|
content_url TEXT,
|
||
|
|
comments_url TEXT,
|
||
|
|
media_url TEXT,
|
||
|
|
created TIMESTAMP NOT NULL,
|
||
|
|
updated TIMESTAMP NOT NULL,
|
||
|
|
display_date TIMESTAMP NOT NULL,
|
||
|
|
sort_date TIMESTAMP NOT NULL,
|
||
|
|
viewed TIMESTAMP,
|
||
|
|
favorited TIMESTAMP,
|
||
|
|
pinned TIMESTAMP,
|
||
|
|
sent_to_kindle TIMESTAMP,
|
||
|
|
raw_data TEXT,
|
||
|
|
header TEXT,
|
||
|
|
icon_url TEXT,
|
||
|
|
FOREIGN KEY(feed_id) REFERENCES feed(id),
|
||
|
|
FOREIGN KEY(user_id) REFERENCES user(id)
|
||
|
|
);
|
||
|
|
|
||
|
|
|
||
|
|
CREATE INDEX ix_feed_folder ON feed(folder);
|
||
|
|
CREATE INDEX ix_feed_user_id ON feed(user_id);
|
||
|
|
CREATE INDEX ix_name_user ON feed(user_id, name);
|
||
|
|
CREATE INDEX ix_feed_created ON feed(created);
|
||
|
|
|
||
|
|
CREATE INDEX ix_entry_favorited ON entry(favorited);
|
||
|
|
CREATE INDEX ix_entry_created ON entry(created);
|
||
|
|
CREATE INDEX ix_entry_sort_date ON entry(sort_date);
|
||
|
|
CREATE INDEX ix_entry_sent_to_kindle ON entry(sent_to_kindle);
|
||
|
|
CREATE INDEX entry_sort_ts ON entry(sort_date DESC);
|
||
|
|
CREATE INDEX ix_entry_viewed ON entry(viewed);
|
||
|
|
CREATE INDEX ix_entry_pinned ON entry(pinned);
|
||
|
|
CREATE INDEX ix_entry_user_id ON entry(user_id);
|
||
|
|
CREATE INDEX ix_entry_username ON entry(username);
|