148 lines
4.2 KiB
Go
148 lines
4.2 KiB
Go
// Copyright 2018 Lars Hoogestraat
|
|
// Use of this source code is governed by a MIT-style
|
|
// license that can be found in the LICENSE file.
|
|
|
|
package database
|
|
|
|
import (
|
|
"database/sql"
|
|
|
|
_ "github.com/mattn/go-sqlite3"
|
|
)
|
|
|
|
// SQLiteConfig represents sqlite configuration type
|
|
type SQLiteConfig struct {
|
|
File string
|
|
}
|
|
|
|
// Open receives handle for sqlite database, returns an error if connection failed
|
|
func (d SQLiteConfig) Open() (*sql.DB, error) {
|
|
return sql.Open("sqlite3", d.File)
|
|
}
|
|
|
|
// InitTables creates the tables
|
|
func InitTables(db *sql.DB) error {
|
|
if _, err := db.Exec("CREATE TABLE user " +
|
|
"(" +
|
|
"id INTEGER PRIMARY KEY, " +
|
|
"username VARCHAR(60) NOT NULL, " +
|
|
"email VARCHAR(191) NOT NULL, " +
|
|
"display_name VARCHAR(191) NOT NULL, " +
|
|
"password CHAR(60) NOT NULL, " +
|
|
"salt CHAR(32) NOT NULL, " +
|
|
"is_admin boolean NOT NULL DEFAULT false, " +
|
|
"active boolean NOT NULL DEFAULT true, " +
|
|
"last_modified datetime NOT NULL," +
|
|
"CONSTRAINT user_email_key UNIQUE (username), " +
|
|
"CONSTRAINT user_email_key UNIQUE (email) " +
|
|
");"); err != nil {
|
|
return err
|
|
}
|
|
|
|
if _, err := db.Exec("CREATE TABLE user_invite " +
|
|
"(" +
|
|
"id INTEGER PRIMARY KEY, " +
|
|
"hash VARCHAR(191) NOT NULL, " +
|
|
"username VARCHAR(60) NOT NULL, " +
|
|
"email VARCHAR(191) NOT NULL, " +
|
|
"display_name VARCHAR(191) NOT NULL, " +
|
|
"is_admin boolean NOT NULL DEFAULT false, " +
|
|
"active boolean NOT NULL DEFAULT true, " +
|
|
"created_at datetime NOT NULL," +
|
|
"created_by INT NOT NULL, " +
|
|
"FOREIGN KEY (created_by) REFERENCES user(id), " +
|
|
"CONSTRAINT userinvite_hash_key UNIQUE (hash), " +
|
|
"CONSTRAINT userinvite_username_key UNIQUE (username), " +
|
|
"CONSTRAINT userinvite_email_key UNIQUE (email) " +
|
|
");"); err != nil {
|
|
return err
|
|
}
|
|
|
|
if _, err := db.Exec("CREATE TABLE article " +
|
|
"(" +
|
|
"id INTEGER PRIMARY KEY, " +
|
|
"headline VARCHAR(100) NOT NULL, " +
|
|
"slug VARCHAR(191) NOT NULL, " +
|
|
"teaser text NOT NULL, " +
|
|
"content text NOT NULL, " +
|
|
"published boolean NOT NULL DEFAULT false, " +
|
|
"published_on datetime, " +
|
|
"last_modified datetime NOT NULL, " +
|
|
"user_id INT NOT NULL, " +
|
|
"category_id INT, " +
|
|
"CONSTRAINT blog_slug_key UNIQUE (slug), " +
|
|
"CONSTRAINT `fk_article_user` " +
|
|
"FOREIGN KEY (user_id) REFERENCES user(id) " +
|
|
"ON DELETE CASCADE, " +
|
|
"FOREIGN KEY (category_id) REFERENCES category(id)" +
|
|
");"); err != nil {
|
|
return err
|
|
}
|
|
|
|
if _, err := db.Exec("CREATE TABLE site " +
|
|
"(" +
|
|
"id INTEGER PRIMARY KEY, " +
|
|
"title VARCHAR(100) NOT NULL, " +
|
|
"link VARCHAR(100) NOT NULL, " +
|
|
"content text NOT NULL, " +
|
|
"section VARCHAR(191) NOT NULL, " +
|
|
"published boolean NOT NULL DEFAULT false, " +
|
|
"published_on datetime, " +
|
|
"last_modified datetime NOT NULL, " +
|
|
"order_no INT NOT NULL, " +
|
|
"user_id INT NOT NULL, " +
|
|
"CONSTRAINT site_link_key UNIQUE (link), " +
|
|
"FOREIGN KEY (user_id) REFERENCES user(id) " +
|
|
"ON DELETE CASCADE " +
|
|
");"); err != nil {
|
|
return err
|
|
}
|
|
|
|
if _, err := db.Exec("CREATE TABLE file " +
|
|
"(" +
|
|
"id INTEGER PRIMARY KEY, " +
|
|
"filename VARCHAR(191) NOT NULL, " +
|
|
"unique_name VARCHAR(191) NOT NULL, " +
|
|
"size BIGINT NOT NULL, " +
|
|
"content_type VARCHAR(150) NOT NULL, " +
|
|
"inline boolean NOT NULL DEFAULT false, " +
|
|
"last_modified datetime NOT NULL, " +
|
|
"user_id INT NOT NULL, " +
|
|
"CONSTRAINT `fk_file_user` " +
|
|
"FOREIGN KEY (user_id) REFERENCES user(id) " +
|
|
"ON DELETE CASCADE, " +
|
|
"CONSTRAINT file_unique_name_key UNIQUE (unique_name) " +
|
|
");"); err != nil {
|
|
return err
|
|
}
|
|
|
|
if _, err := db.Exec("CREATE TABLE category " +
|
|
"(" +
|
|
"id INTEGER PRIMARY KEY, " +
|
|
"name VARCHAR(191) NOT NULL, " +
|
|
"slug VARCHAR(191) NOT NULL, " +
|
|
"last_modified datetime NOT NULL, " +
|
|
"user_id INT NOT NULL, " +
|
|
"CONSTRAINT category_name_key UNIQUE (name) " +
|
|
");"); err != nil {
|
|
return err
|
|
}
|
|
|
|
if _, err := db.Exec("CREATE TABLE token " +
|
|
"(" +
|
|
"id INTEGER PRIMARY KEY, " +
|
|
"hash VARCHAR(191) NOT NULL, " +
|
|
"requested_at datetime NOT NULL, " +
|
|
"token_type VARCHAR(100) NOT NULL, " +
|
|
"user_id INT NOT NULL, " +
|
|
"CONSTRAINT `fk_token_user` " +
|
|
"FOREIGN KEY (user_id) REFERENCES user(id) " +
|
|
"ON DELETE CASCADE, " +
|
|
"CONSTRAINT token_key UNIQUE (hash) " +
|
|
");"); err != nil {
|
|
return err
|
|
}
|
|
|
|
return nil
|
|
}
|