20 files changed, 164 insertions, 0 deletions
diff --git a/database/sql/schema_version_1.sql b/database/sql/schema_version_1.sql
new file mode 100644
index 0000000..cb7c213
--- /dev/null
+++ b/database/sql/schema_version_1.sql
@@ -0,0 +1,103 @@
+create table schema_version (
+    version text not null
+);
+
+create table users (
+    id serial not null,
+    username text not null unique,
+    password text,
+    is_admin bool default 'f',
+    language text default 'en_US',
+    timezone text default 'UTC',
+    theme text default 'default',
+    last_login_at timestamp with time zone,
+    primary key (id)
+);
+
+create table sessions (
+    id serial not null,
+    user_id int not null,
+    token text not null unique,
+    created_at timestamp with time zone default now(),
+    user_agent text,
+    ip text,
+    primary key (id),
+    unique (user_id, token),
+    foreign key (user_id) references users(id) on delete cascade
+);
+
+create table categories (
+    id serial not null,
+    user_id int not null,
+    title text not null,
+    primary key (id),
+    unique (user_id, title),
+    foreign key (user_id) references users(id) on delete cascade
+);
+
+create table feeds (
+    id bigserial not null,
+    user_id int not null,
+    category_id int not null,
+    title text not null,
+    feed_url text not null,
+    site_url text not null,
+    checked_at timestamp with time zone default now(),
+    etag_header text default '',
+    last_modified_header text default '',
+    parsing_error_msg text default '',
+    parsing_error_count int default 0,
+    primary key (id),
+    unique (user_id, feed_url),
+    foreign key (user_id) references users(id) on delete cascade,
+    foreign key (category_id) references categories(id) on delete cascade
+);
+
+create type entry_status as enum('unread', 'read', 'removed');
+
+create table entries (
+    id bigserial not null,
+    user_id int not null,
+    feed_id bigint not null,
+    hash text not null,
+    published_at timestamp with time zone not null,
+    title text not null,
+    url text not null,
+    author text,
+    content text,
+    status entry_status default 'unread',
+    primary key (id),
+    unique (feed_id, hash),
+    foreign key (user_id) references users(id) on delete cascade,
+    foreign key (feed_id) references feeds(id) on delete cascade
+);
+
+create index entries_feed_idx on entries using btree(feed_id);
+
+create table enclosures (
+    id bigserial not null,
+    user_id int not null,
+    entry_id bigint not null,
+    url text not null,
+    size int default 0,
+    mime_type text default '',
+    primary key (id),
+    foreign key (user_id) references users(id) on delete cascade,
+    foreign key (entry_id) references entries(id) on delete cascade
+);
+
+create table icons (
+    id bigserial not null,
+    hash text not null unique,
+    mime_type text not null,
+    content bytea not null,
+    primary key (id)
+);
+
+create table feed_icons (
+    feed_id bigint not null,
+    icon_id bigint not null,
+    primary key(feed_id, icon_id),
+    foreign key (feed_id) references feeds(id) on delete cascade,
+    foreign key (icon_id) references icons(id) on delete cascade
+);
diff --git a/database/sql/schema_version_10.sql b/database/sql/schema_version_10.sql
new file mode 100644
index 0000000..abd26d8
--- /dev/null
+++ b/database/sql/schema_version_10.sql
@@ -0,0 +1,8 @@
+drop table tokens;
+
+create table sessions (
+    id text not null,
+    data jsonb not null,
+    created_at timestamp with time zone not null default now(),
+    primary key(id)
+);
+\ No newline at end of file
diff --git a/database/sql/schema_version_11.sql b/database/sql/schema_version_11.sql
new file mode 100644
index 0000000..79f08d7
--- /dev/null
+++ b/database/sql/schema_version_11.sql
@@ -0,0 +1,6 @@
+alter table integrations add column wallabag_enabled bool default 'f';
+alter table integrations add column wallabag_url text default '';
+alter table integrations add column wallabag_client_id text default '';
+alter table integrations add column wallabag_client_secret text default '';
+alter table integrations add column wallabag_username text default '';
+alter table integrations add column wallabag_password text default '';
+\ No newline at end of file
diff --git a/database/sql/schema_version_12.sql b/database/sql/schema_version_12.sql
new file mode 100644
index 0000000..34bc9fd
--- /dev/null
+++ b/database/sql/schema_version_12.sql
@@ -0,0 +1 @@
+alter table entries add column starred bool default 'f';
+\ No newline at end of file
diff --git a/database/sql/schema_version_13.sql b/database/sql/schema_version_13.sql
new file mode 100644
index 0000000..c2b5df7
--- /dev/null
+++ b/database/sql/schema_version_13.sql
@@ -0,0 +1,2 @@
+create index entries_user_status_idx on entries(user_id, status);
+create index feeds_user_category_idx on feeds(user_id, category_id);
diff --git a/database/sql/schema_version_14.sql b/database/sql/schema_version_14.sql
new file mode 100644
index 0000000..bfd7ca2
--- /dev/null
+++ b/database/sql/schema_version_14.sql
@@ -0,0 +1,3 @@
+alter table integrations add column nunux_keeper_enabled bool default 'f';
+alter table integrations add column nunux_keeper_url text default '';
+alter table integrations add column nunux_keeper_api_key text default '';
+\ No newline at end of file
diff --git a/database/sql/schema_version_15.sql b/database/sql/schema_version_15.sql
new file mode 100644
index 0000000..b5cb386
--- /dev/null
+++ b/database/sql/schema_version_15.sql
@@ -0,0 +1 @@
+alter table enclosures alter column size set data type bigint;
+\ No newline at end of file
diff --git a/database/sql/schema_version_16.sql b/database/sql/schema_version_16.sql
new file mode 100644
index 0000000..e4f6d23
--- /dev/null
+++ b/database/sql/schema_version_16.sql
@@ -0,0 +1 @@
+alter table entries add column comments_url text default '';
+\ No newline at end of file
diff --git a/database/sql/schema_version_17.sql b/database/sql/schema_version_17.sql
new file mode 100644
index 0000000..b4cc848
--- /dev/null
+++ b/database/sql/schema_version_17.sql
@@ -0,0 +1,3 @@
+alter table integrations add column pocket_enabled bool default 'f';
+alter table integrations add column pocket_access_token text default '';
+alter table integrations add column pocket_consumer_key text default '';
diff --git a/database/sql/schema_version_18.sql b/database/sql/schema_version_18.sql
new file mode 100644
index 0000000..4b36904
--- /dev/null
+++ b/database/sql/schema_version_18.sql
@@ -0,0 +1 @@
+alter table user_sessions alter column ip set data type inet using ip::inet;
+\ No newline at end of file
diff --git a/database/sql/schema_version_19.sql b/database/sql/schema_version_19.sql
new file mode 100644
index 0000000..130ae08
--- /dev/null
+++ b/database/sql/schema_version_19.sql
@@ -0,0 +1,2 @@
+alter table feeds add column username text default '';
+alter table feeds add column password text default '';
+\ No newline at end of file
diff --git a/database/sql/schema_version_2.sql b/database/sql/schema_version_2.sql
new file mode 100644
index 0000000..d88b404
--- /dev/null
+++ b/database/sql/schema_version_2.sql
@@ -0,0 +1,3 @@
+create extension if not exists hstore;
+alter table users add column extra hstore;
+create index users_extra_idx on users using gin(extra);
diff --git a/database/sql/schema_version_20.sql b/database/sql/schema_version_20.sql
new file mode 100644
index 0000000..228e162
--- /dev/null
+++ b/database/sql/schema_version_20.sql
@@ -0,0 +1,3 @@
+alter table entries add column document_vectors tsvector;
+update entries set document_vectors = to_tsvector(title || ' ' || coalesce(content, ''));
+create index document_vectors_idx on entries using gin(document_vectors);
+\ No newline at end of file
diff --git a/database/sql/schema_version_3.sql b/database/sql/schema_version_3.sql
new file mode 100644
index 0000000..d58e35d
--- /dev/null
+++ b/database/sql/schema_version_3.sql
@@ -0,0 +1,6 @@
+create table tokens (
+    id text not null,
+    value text not null,
+    created_at timestamp with time zone not null default now(),
+    primary key(id, value)
+);
+\ No newline at end of file
diff --git a/database/sql/schema_version_4.sql b/database/sql/schema_version_4.sql
new file mode 100644
index 0000000..988c486
--- /dev/null
+++ b/database/sql/schema_version_4.sql
@@ -0,0 +1,2 @@
+create type entry_sorting_direction as enum('asc', 'desc');
+alter table users add column entry_direction entry_sorting_direction default 'asc';
diff --git a/database/sql/schema_version_5.sql b/database/sql/schema_version_5.sql
new file mode 100644
index 0000000..dac7937
--- /dev/null
+++ b/database/sql/schema_version_5.sql
@@ -0,0 +1,15 @@
+create table integrations (
+    user_id int not null,
+    pinboard_enabled bool default 'f',
+    pinboard_token text default '',
+    pinboard_tags text default 'miniflux',
+    pinboard_mark_as_unread bool default 'f',
+    instapaper_enabled bool default 'f',
+    instapaper_username text default '',
+    instapaper_password text default '',
+    fever_enabled bool default 'f',
+    fever_username text default '',
+    fever_password text default '',
+    fever_token text default '',
+    primary key(user_id)
+)
diff --git a/database/sql/schema_version_6.sql b/database/sql/schema_version_6.sql
new file mode 100644
index 0000000..6f79ca8
--- /dev/null
+++ b/database/sql/schema_version_6.sql
@@ -0,0 +1 @@
+alter table feeds add column scraper_rules text default '';
diff --git a/database/sql/schema_version_7.sql b/database/sql/schema_version_7.sql
new file mode 100644
index 0000000..f4d2a9c
--- /dev/null
+++ b/database/sql/schema_version_7.sql
@@ -0,0 +1 @@
+alter table feeds add column rewrite_rules text default '';
diff --git a/database/sql/schema_version_8.sql b/database/sql/schema_version_8.sql
new file mode 100644
index 0000000..d86c29b
--- /dev/null
+++ b/database/sql/schema_version_8.sql
@@ -0,0 +1 @@
+alter table feeds add column crawler boolean default 'f';
diff --git a/database/sql/schema_version_9.sql b/database/sql/schema_version_9.sql
new file mode 100644
index 0000000..7761724
--- /dev/null
+++ b/database/sql/schema_version_9.sql
@@ -0,0 +1 @@
+alter table sessions rename to user_sessions;
+\ No newline at end of file