Create database package (refactoring)

23 files changed, 446 insertions, 0 deletions

diff --git a/database/database.go b/database/database.go
new file mode 100644
index 0000000..42f2292
--- /dev/null
+++ b/database/database.go
@@ -0,0 +1,25 @@
+// Copyright 2018 Frédéric Guillot. All rights reserved.
+// Use of this source code is governed by the Apache 2.0
+// license that can be found in the LICENSE file.
+
+package database
+
+import (
+	"database/sql"
+
+	// Postgresql driver import
+	_ "github.com/lib/pq"
+)
+
+// NewConnectionPool configures the database connection pool.
+func NewConnectionPool(dsn string, minConnections, maxConnections int) (*sql.DB, error) {
+	db, err := sql.Open("postgres", dsn)
+	if err != nil {
+		return nil, err
+	}
+
+	db.SetMaxOpenConns(maxConnections)
+	db.SetMaxIdleConns(minConnections)
+
+	return db, nil
+}
diff --git a/database/migration.go b/database/migration.go
new file mode 100644
index 0000000..e348e40
--- /dev/null
+++ b/database/migration.go
@@ -0,0 +1,55 @@
+// Copyright 2018 Frédéric Guillot. All rights reserved.
+// Use of this source code is governed by the Apache 2.0
+// license that can be found in the LICENSE file.
+
+package database
+
+import (
+	"database/sql"
+	"fmt"
+	"strconv"
+
+	"github.com/miniflux/miniflux/logger"
+)
+
+const schemaVersion = 20
+
+// Migrate executes database migrations.
+func Migrate(db *sql.DB) {
+	var currentVersion int
+	db.QueryRow(`select version from schema_version`).Scan(&currentVersion)
+
+	fmt.Println("Current schema version:", currentVersion)
+	fmt.Println("Latest schema version:", schemaVersion)
+
+	for version := currentVersion + 1; version <= schemaVersion; version++ {
+		fmt.Println("Migrating to version:", version)
+
+		tx, err := db.Begin()
+		if err != nil {
+			logger.Fatal("[Migrate] %v", err)
+		}
+
+		rawSQL := SqlMap["schema_version_"+strconv.Itoa(version)]
+		// fmt.Println(rawSQL)
+		_, err = tx.Exec(rawSQL)
+		if err != nil {
+			tx.Rollback()
+			logger.Fatal("[Migrate] %v", err)
+		}
+
+		if _, err := tx.Exec(`delete from schema_version`); err != nil {
+			tx.Rollback()
+			logger.Fatal("[Migrate] %v", err)
+		}
+
+		if _, err := tx.Exec(`insert into schema_version (version) values($1)`, version); err != nil {
+			tx.Rollback()
+			logger.Fatal("[Migrate] %v", err)
+		}
+
+		if err := tx.Commit(); err != nil {
+			logger.Fatal("[Migrate] %v", err)
+		}
+	}
+}
diff --git a/database/sql.go b/database/sql.go
new file mode 100644
index 0000000..a9468ec
--- /dev/null
+++ b/database/sql.go
@@ -0,0 +1,202 @@
+// Code generated by go generate; DO NOT EDIT.
+
+package database
+
+var SqlMap = map[string]string{
+	"schema_version_1": `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
+);
+`,
+	"schema_version_10": `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)
+);`,
+	"schema_version_11": `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 '';`,
+	"schema_version_12": `alter table entries add column starred bool default 'f';`,
+	"schema_version_13": `create index entries_user_status_idx on entries(user_id, status);
+create index feeds_user_category_idx on feeds(user_id, category_id);
+`,
+	"schema_version_14": `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 '';`,
+	"schema_version_15": `alter table enclosures alter column size set data type bigint;`,
+	"schema_version_16": `alter table entries add column comments_url text default '';`,
+	"schema_version_17": `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 '';
+`,
+	"schema_version_18": `alter table user_sessions alter column ip set data type inet using ip::inet;`,
+	"schema_version_19": `alter table feeds add column username text default '';
+alter table feeds add column password text default '';`,
+	"schema_version_2": `create extension if not exists hstore;
+alter table users add column extra hstore;
+create index users_extra_idx on users using gin(extra);
+`,
+	"schema_version_20": `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);`,
+	"schema_version_3": `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)
+);`,
+	"schema_version_4": `create type entry_sorting_direction as enum('asc', 'desc');
+alter table users add column entry_direction entry_sorting_direction default 'asc';
+`,
+	"schema_version_5": `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)
+)
+`,
+	"schema_version_6": `alter table feeds add column scraper_rules text default '';
+`,
+	"schema_version_7": `alter table feeds add column rewrite_rules text default '';
+`,
+	"schema_version_8": `alter table feeds add column crawler boolean default 'f';
+`,
+	"schema_version_9": `alter table sessions rename to user_sessions;`,
+}
+
+var SqlMapChecksums = map[string]string{
+	"schema_version_1":  "00b2fa9e945565625c93ef9d4242a8b6583dc3cd7edf38d2fc95c0f3f7b926ae",
+	"schema_version_10": "8faf15ddeff7c8cc305e66218face11ed92b97df2bdc2d0d7944d61441656795",
+	"schema_version_11": "dc5bbc302e01e425b49c48ddcd8e29e3ab2bb8e73a6cd1858a6ba9fbec0b5243",
+	"schema_version_12": "a95abab6cdf64811fc744abd37457e2928939d999c5ef00d2bdd9398e16f32fb",
+	"schema_version_13": "9073fae1e796936f4a43a8120ebdb4218442fe7d346ace6387556a357c2d7edf",
+	"schema_version_14": "4622e42c4a5a88b6fe1e61f3d367b295968f7260ab5b96481760775ba9f9e1fe",
+	"schema_version_15": "13ff91462bdf4cda5a94a4c7a09f757761b0f2c32b4be713ba4786a4837750e4",
+	"schema_version_16": "9d006faca62fd7ab787f64aef0e0a5933d142466ec4cab0e096bb920d2797e34",
+	"schema_version_17": "b9f15d6217275fedcf6d948dd85ebe978b869bf37f42a86fd5b50a51919fa0e1",
+	"schema_version_18": "c0ec24847612c7f2dc326cf735baffba79391a56aedd73292371a39f38724a71",
+	"schema_version_19": "a83f77b41cc213d282805a5b518f15abbf96331599119f0ef4aca4be037add7b",
+	"schema_version_2":  "e8e9ff32478df04fcddad10a34cba2e8bb1e67e7977b5bd6cdc4c31ec94282b4",
+	"schema_version_20": "6c4e9b2c5bccdc3243c239c390fb1caa5e15624e669b2c07e14c126f6d2e2cd6",
+	"schema_version_3":  "a54745dbc1c51c000f74d4e5068f1e2f43e83309f023415b1749a47d5c1e0f12",
+	"schema_version_4":  "216ea3a7d3e1704e40c797b5dc47456517c27dbb6ca98bf88812f4f63d74b5d9",
+	"schema_version_5":  "46397e2f5f2c82116786127e9f6a403e975b14d2ca7b652a48cd1ba843e6a27c",
+	"schema_version_6":  "9d05b4fb223f0e60efc716add5048b0ca9c37511cf2041721e20505d6d798ce4",
+	"schema_version_7":  "33f298c9aa30d6de3ca28e1270df51c2884d7596f1283a75716e2aeb634cd05c",
+	"schema_version_8":  "9922073fc4032d8922617ec6a6a07ae8d4817846c138760fb96cb5608ab83bfc",
+	"schema_version_9":  "de5ba954752fe808a993feef5bf0c6f808e0a4ced5379de8bec8342678150892",
+}
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