trantor/lib/database/database.go

package database

import (
	"time"

	"github.com/go-pg/pg/v10"
	"github.com/go-pg/pg/v10/orm"
)

type DB interface {
	Close() error
	AddBook(book Book) error
	GetBooks(query string, length int, start int) (books []Book, num int, err error)
	GetNewBooks(query string, length int, start int) (books []Book, num int, err error)
	GetBookID(id string) (Book, error)
	DeleteBook(id string) error
	UpdateBook(id string, data map[string]interface{}) error
	ActiveBook(id string) error
	IsBookActive(id string) bool
	ExistsBookHash(hash []byte) bool
	AddUser(name string, pass string) error
	AddRawUser(name string, hpass []byte, salt []byte, role string) error
	SetAdminUser(name string, pass string) error
	GetRole(name string) (string, error)
	SetPassword(name string, pass string) error
	SetRole(name, role string) error
	ValidPassword(name string, pass string) bool
	ListUsers() ([]User, error)
	AddNews(text string) error
	AddRawNews(text string, date time.Time) error
	GetNews(num int, days int) (news []New, err error)
	IncViews(ID string) error
	IncDownloads(ID string) error
	GetDownloadCounter(ID string) (int, error)
	GetFrontPage() FrontPage
	AddSubmission(submission Submission, userName string) (id int, err error)
	UpdateSubmission(id int, status string, book *Book) error
	UpdateSubmissionByBook(bookID string, status string, book *Book) error
	UpdateSubmissionComment(submissionID, bookID, comment string) error
	GetSubmission(submissionID string) (submission []Submission, err error)
	GetUserSubmissions(userName string) (submission []Submission, err error)
	GetComment(bookID string) (string, error)

	BookLister
}

const (
	tagsDisplay    = 50
	booksFrontPage = 6
)

type pgDB struct {
	sql       *pg.DB
	frontPage FrontPage
}

type FrontPage struct {
	Count    int
	Last     []Book
	Visited  []Book
	Download []Book
	Tags     []string
}

// Options for the database
type Options struct {
	Addr     string
	User     string
	Password string
	Name     string
}

// Init the database connection
func Init(options Options) (DB, error) {
	db, err := _init(options)
	if err != nil {
		return nil, err
	}
	go db.frontPageUpdater()
	go db.submissionsCleaner()

	return db, nil
}

func _init(options Options) (*pgDB, error) {
	network := "tcp"
	if options.Addr[0] == '/' {
		network = "unix"
	}
	sql := pg.Connect(&pg.Options{
		Network:  network,
		Addr:     options.Addr,
		User:     options.User,
		Password: options.Password,
		Database: options.Name,
	})

	var db pgDB
	db.sql = sql

	err := db.create()
	return &db, err
}

// Close the database connection
func (db pgDB) Close() error {
	return db.sql.Close()
}

func (db pgDB) create() error {
	orm.RegisterTable(&BookListEntry{})
	models := []interface{}{&Book{}, &New{}, &User{}, &Visit{}, &Submission{}, &BookList{}, &BookListEntry{}}
	for _, model := range models {
		options := &orm.CreateTableOptions{
			IfNotExists:   true,
			FKConstraints: true,
		}
		switch model.(type) {
		case *Submission:
			// XXX: ON DELETE SET NULL contraint is not supported
			options.FKConstraints = false
		}
		err := db.sql.Model(model).CreateTable(options)
		if err != nil {
			return err
		}
	}

	_, err := db.sql.Exec(createSQL)
	if err != nil {
		return err
	}
	return nil
}

func RO(db DB) DB {
	return &roDB{db}
}

const createSQL = `

-- Visits default values
ALTER TABLE visits ALTER COLUMN downloads SET DEFAULT 0 ;
ALTER TABLE visits ALTER COLUMN views SET DEFAULT 0 ;


-- Books default values
ALTER TABLE books ALTER COLUMN active SET DEFAULT false ;

-- Books column indexes
CREATE INDEX IF NOT EXISTS books_lang_idx ON books (lang);
CREATE INDEX IF NOT EXISTS books_isbn_idx ON books (isbn);
CREATE INDEX IF NOT EXISTS books_active_idx ON books (active);
CREATE INDEX IF NOT EXISTS books_file_hash_idx ON books (file_hash);



-- Books trigram indexes
CREATE INDEX IF NOT EXISTS books_title_idx ON books USING GIN (title gin_trgm_ops);
CREATE INDEX IF NOT EXISTS books_contributor_idx ON books USING GIN (contributor gin_trgm_ops);
CREATE INDEX IF NOT EXISTS books_publisher_idx ON books USING GIN (publisher gin_trgm_ops);
CREATE INDEX IF NOT EXISTS books_date_idx ON books USING GIN (date gin_trgm_ops);
CREATE OR REPLACE FUNCTION text(text[]) RETURNS text immutable AS $$
select $1::text
$$ language sql;
CREATE INDEX IF NOT EXISTS books_authors_idx ON books USING GIN (text(authors) gin_trgm_ops);
CREATE INDEX IF NOT EXISTS books_tags_idx ON books USING GIN (text(tags) gin_trgm_ops);

-- Books text search index
CREATE OR REPLACE FUNCTION books_trigger() RETURNS trigger AS $$
declare
	lang_config regconfig;
begin
	lang_config := 'simple';
	if new.lang = 'da' then
		lang_config := 'danish';
	elsif new.lang = 'nl' then
		lang_config := 'dutch';
	elsif new.lang = 'en' then
		lang_config := 'english';
	elsif new.lang = 'fi' then
		lang_config := 'finnish';
	elsif new.lang = 'fr' then
   		lang_config := 'french';
	elsif new.lang = 'de' then
		lang_config :='german';
	elsif new.lang = 'hu' then
		lang_config :='hungarian';
	elsif new.lang = 'it' then
		lang_config :='italian';
	elsif new.lang = 'no' then
		lang_config :='norwegian';
	elsif new.lang = 'pt' then
		lang_config :='portuguese';
	elsif new.lang = 'ro' then
		lang_config :='romanian';
	elsif new.lang = 'ru' then
		lang_config :='russian';
	elsif new.lang = 'es' then
		lang_config :='spanish';
	elsif new.lang = 'sv' then
		lang_config :='swedish';
	elsif new.lang = 'tr' then
		lang_config :='turkish';
	end if;

	new.tsv :=
		setweight(to_tsvector(lang_config, coalesce(new.title,'')), 'A') ||
		setweight(to_tsvector('simple', coalesce(array_to_string(new.authors, ' '),'')), 'A') ||
		setweight(to_tsvector('simple', coalesce(new.contributor,'')), 'B') ||
		setweight(to_tsvector('simple', coalesce(new.publisher,'')), 'B') ||
		setweight(to_tsvector(lang_config, coalesce(array_to_string(new.tags, ' '),'')), 'C') ||
		setweight(to_tsvector(lang_config, coalesce(new.description,'')), 'D');
	return new;
end
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS tsvectorupdate ON books;
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
    ON books FOR EACH ROW EXECUTE PROCEDURE books_trigger();
CREATE INDEX IF NOT EXISTS books_idx ON books USING GIN (tsv);
CREATE OR REPLACE FUNCTION to_tsquery_multilingual(text) RETURNS tsquery AS $$
SELECT
	to_tsquery('simple', $1) ||
	to_tsquery('danish', $1) ||
	to_tsquery('dutch', $1) ||
	to_tsquery('english', $1) ||
	to_tsquery('finnish', $1) ||
	to_tsquery('french', $1) ||
	to_tsquery('german', $1) ||
	to_tsquery('hungarian', $1) ||
	to_tsquery('italian', $1) ||
	to_tsquery('norwegian', $1) ||
	to_tsquery('portuguese', $1) ||
	to_tsquery('romanian', $1) ||
	to_tsquery('russian', $1) ||
	to_tsquery('spanish', $1) ||
	to_tsquery('swedish', $1) ||
	to_tsquery('turkish', $1)
$$ LANGUAGE sql IMMUTABLE;

-- Visits indexes
CREATE INDEX IF NOT EXISTS visits_downloads_idx on visits(downloads DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS visits_views_idx on visits(views DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS visits_book_id_idx on visits(book_id);

-- Submissions indexes
CREATE INDEX IF NOT EXISTS submissions_id_idx on submissions(submission_id);
CREATE INDEX IF NOT EXISTS submissions_book_idx on submissions(book_id);
CREATE INDEX IF NOT EXISTS submissions_user_idx on submissions(user_id);
CREATE INDEX IF NOT EXISTS submissions_last_modified_idx on submissions(last_modified);

-- Submissions constraint
ALTER TABLE ONLY submissions
    DROP CONSTRAINT IF EXISTS submissions_book_id_fkey;
ALTER TABLE ONLY submissions
    ADD CONSTRAINT submissions_book_id_fkey FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE SET NULL;
ALTER TABLE ONLY submissions
    DROP CONSTRAINT IF EXISTS submissions_user_id_fkey;
ALTER TABLE ONLY submissions
    ADD CONSTRAINT submissions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;

-- BookLists indexes
CREATE INDEX IF NOT EXISTS book_lists_list_id_idx on book_lists(list_id);
CREATE INDEX IF NOT EXISTS book_lists_user_idx on book_lists(user_id);
CREATE INDEX IF NOT EXISTS book_list_entries_list_idx on book_list_entries(book_list_id);
CREATE INDEX IF NOT EXISTS book_list_entries_book_idx on book_list_entries(book_id);
`