From 9e011074bcc80e00e6deb5b914371718e5991ce2 Mon Sep 17 00:00:00 2001 From: Adam Mathes Date: Tue, 12 Jun 2018 14:58:02 -0700 Subject: sqlite3 support --- README.md | 29 ++++++++++++++++++++--------- config/config.go | 1 + main.go | 2 +- models/db.go | 25 ++++++++++++++----------- mysql.init.sql | 30 ++++++++++++++++++++++++++++++ sqlite.init.sql | 30 ++++++++++++++++++++++++++++++ 6 files changed, 96 insertions(+), 21 deletions(-) create mode 100644 mysql.init.sql create mode 100644 sqlite.init.sql diff --git a/README.md b/README.md index f47fc09..dc03e60 100644 --- a/README.md +++ b/README.md @@ -29,7 +29,9 @@ This is not very easy to use/setup/ yet. Sorry! Consider it WIP, pull requests f ### Prerequesites * [Go](https://golang.org) - * [MySQL](https://dev.mysql.com) or a drop-in replacement like [MariaDB](https://mariadb.com) + * Persistence layer via one of either + * [SQLite](https://sqlite.org/) (newly supported and easiest to setup) + * [MySQL](https://dev.mysql.com) or a drop-in replacement like [MariaDB](https://mariadb.com) PostgreSQL support is left as an exercise for the reader to implement and send a pull request for. @@ -47,8 +49,16 @@ See also: [The GOPATH environment variable](https://golang.org/doc/code.html#GOP This will download neko code, dependencies, and build them all in $HOME/go/src/ A `neko` binary should now be in $HOME/go/bin/ - -### Create MySQL table and user + +### Database Setup + +#### SQLite + +Just choose + +#### Create MySQL table and user + +If you are using MySQL or equivalent -- $ mysqladmin -uroot -p create neko $ mysql -uroot -p neko < init.sql @@ -67,7 +77,8 @@ The configuration is JSON which was probably not a good idea. | name | value | example | |--------------|--------------------------------------------------|----------------| -| `db` | mysql database connection string | root:@tcp(127.0.0.1:3306)/neko | +| `db_driver` | database driver - sqlite3 or mysql | sqlite3 | +| `db` | mysql connection string OR sqlite file | root:@tcp(127.0.0.1:3306)/neko | | `web` | web address/port to bind to | 127.0.0.0.1:4994 | | `username` | username for single user auth | user | `password` | plaintext -- will be encrypted in client cookie | notagoodpassword | @@ -76,23 +87,23 @@ The configuration is JSON which was probably not a good idea. ### Add Feeds - $ neko -add http://trenchant.org/rss.xml + $ neko --add http://trenchant.org/rss.xml Add as many feeds as you'd like to start. You can add more in the web ui later. Neko will look for a `config.json` in the local directory -- otherwise specify the location with the `-c` flag. - $ neko -add -c /path/to/config.json + $ neko --add -c /path/to/config.json ### Crawl Feeds - $ neko -update + $ neko --update This should fetch, download, parse, and store in the database your feeds. ### Run web server - $ neko -serve + $ neko --serve UI should now be available at the address in your `web` configuration setting. @@ -102,7 +113,7 @@ UI should now be available at the address in your `web` configuration setting. Depending on your binaries/configs something like -- - 34 * * * * neko -c /etc/neko.config + 34 * * * * neko -c /etc/neko.config -u -- should crawl regularly on the hour in cron. diff --git a/config/config.go b/config/config.go index fef1add..2b4dc41 100644 --- a/config/config.go +++ b/config/config.go @@ -7,6 +7,7 @@ import ( ) type Settings struct { + DBDriver string `json:"db_driver"` DBServer string `json:"db"` WebServer string `json:"web"` Username string `json:"username"` diff --git a/main.go b/main.go index d23c4ec..afa34e7 100644 --- a/main.go +++ b/main.go @@ -30,7 +30,7 @@ func main() { } config.Read(configFile) - models.InitDB(config.Config.DBServer) + models.InitDB() vlog.VERBOSE = verbose if update { diff --git a/models/db.go b/models/db.go index b8bc655..31d8bdd 100644 --- a/models/db.go +++ b/models/db.go @@ -4,21 +4,24 @@ Package neko/models implements behavior for the entities necessary for the subsc package models import ( - "database/sql" + "adammathes.com/neko/config" + "database/sql" _ "github.com/go-sql-driver/mysql" - "log" + _ "github.com/mattn/go-sqlite3" + "log" ) var DB *sql.DB -func InitDB(dataSourceName string) { - var err error - DB, err = sql.Open("mysql", dataSourceName) - if err != nil { - log.Panic(err) - } +func InitDB() { + var err error + // DB, err = sql.Open("mysql", dataSourceName) + DB, err = sql.Open(config.Config.DBDriver, config.Config.DBServer) + if err != nil { + log.Panic(err) + } - if err = DB.Ping(); err != nil { - log.Panic(err) - } + if err = DB.Ping(); err != nil { + log.Panic(err) + } } diff --git a/mysql.init.sql b/mysql.init.sql new file mode 100644 index 0000000..dbbc310 --- /dev/null +++ b/mysql.init.sql @@ -0,0 +1,30 @@ +SET NAMES 'utf8mb4'; +SET CHARACTER SET utf8mb4; + +CREATE TABLE feed ( + id INT NOT NULL AUTO_INCREMENT, + url VARCHAR(255) NOT NULL, + web_url VARCHAR(255) NOT NULL DEFAULT "", + title VARCHAR(255) NOT NULL DEFAULT "", + category VARCHAR(255) NOT NULL DEFAULT "uncategorized", + last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY (url), + PRIMARY KEY (id) +); + +CREATE TABLE item ( + id INT NOT NULL AUTO_INCREMENT, + feed_id INT NOT NULL, + title TEXT NOT NULL DEFAULT "", + url VARCHAR(255) NOT NULL, + description TEXT NOT NULL DEFAULT "", + full_content TEXT NOT NULL DEFAULT "", + header_image TEXT NOT NULL DEFAULT "", + publish_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, + read_state BOOLEAN DEFAULT FALSE NOT NULL, + starred BOOLEAN DEFAULT FALSE NOT NULL, + FOREIGN KEY (feed_id) REFERENCES feed(id) ON DELETE CASCADE, + UNIQUE KEY (url), + INDEX (publish_date), + PRIMARY KEY (id) +); diff --git a/sqlite.init.sql b/sqlite.init.sql new file mode 100644 index 0000000..d4d08c8 --- /dev/null +++ b/sqlite.init.sql @@ -0,0 +1,30 @@ +CREATE TABLE IF NOT EXISTS "feed" ( + "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + "url" varchar(100) NOT NULL UNIQUE, + "web_url" varchar(255) NOT NULL DEFAULT '', + "title" varchar(255) NOT NULL DEFAULT '', + "last_updated" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + "category" varchar(255) NOT NULL DEFAULT 'uncategorized' +); +CREATE INDEX "feed_url" ON "feed" ("url"); +CREATE INDEX "feed_category" ON "feed" ("category"); +CREATE INDEX "feed_id" ON "feed" ("id"); + +CREATE TABLE IF NOT EXISTS "item" ( + "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + "feed_id" int(11) NOT NULL, + "title" text NOT NULL DEFAULT '', + "url" varchar(255) NOT NULL UNIQUE, + "description" text NOT NULL DEFAULT '', + "publish_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + "read_state" tinyint(1) NOT NULL DEFAULT '0', + "starred" tinyint(1) NOT NULL DEFAULT '0', + "full_content" text NOT NULL DEFAULT '', + "header_image" text NOT NULL DEFAULT '', + CONSTRAINT "item_ibfk_1" FOREIGN KEY ("feed_id") REFERENCES "feed" ("id") ON DELETE CASCADE +); +CREATE INDEX "item_url" ON "item" ("url"); +CREATE INDEX "item_publish_date" ON "item" ("publish_date"); +CREATE INDEX "item_feed_id" ON "item" ("feed_id"); +CREATE INDEX "item_rev_id" ON "item" ("id"); +CREATE INDEX "item_read_state" ON "item" ("read_state"); -- cgit v1.2.3