path: root/rddb/rddb.go

//
//  rhrd-go
//
//  The Radio Helsinki Rivendell Go Package
//
//
//  Copyright (C) 2016 Christian Pointner <equinox@helsinki.at>
//
//  This file is part of rhrd-go.
//
//  rhrd-go is free software: you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation, either version 3 of the License, or
//  any later version.
//
//  rhrd-go is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//  GNU General Public License for more details.
//
//  You should have received a copy of the GNU General Public License
//  along with rhrd-go. If not, see <http://www.gnu.org/licenses/>.
//

package rddb

import (
	"database/sql"
	"fmt"
	"github.com/ziutek/mymysql/godrv"
	"regexp"
	"strings"
)

var (
	showMacroRe      = regexp.MustCompile(`^LL 1 ([^ ]+) 0\!$`)
	mysqlTableNameRe = regexp.MustCompile(`^[_0-9a-zA-Z-]+$`)
)

const (
	DB_VERSION = 245
)

type getPasswordResult struct {
	password string
	err      error
}

type getPasswordRequest struct {
	username string
	cached   bool
	response chan<- getPasswordResult
}

type getGroupOfCartResult struct {
	group string
	err   error
}

type getGroupOfCartRequest struct {
	cart     uint
	response chan<- getGroupOfCartResult
}

type getShowInfoResult struct {
	title   string
	group   string
	carts   []uint
	normLvl int
	trimLvl int
	err     error
}

type getShowInfoRequest struct {
	showid   uint
	response chan<- getShowInfoResult
}

type checkMusicGroupResult struct {
	ismusic bool
	err     error
}

type checkMusicGroupRequest struct {
	group    string
	response chan<- checkMusicGroupResult
}

type getMusicInfoResult struct {
	normLvl int
	trimLvl int
	err     error
}

type getMusicInfoRequest struct {
	group    string
	response chan<- getMusicInfoResult
}

type DB struct {
	conf                *config
	dbh                 *sql.DB
	passwordCache       map[string]string
	getPasswordChan     chan getPasswordRequest
	getPasswordStmt     *sql.Stmt
	getGroupOfCartChan  chan getGroupOfCartRequest
	getGroupOfCartStmt  *sql.Stmt
	getShowInfoChan     chan getShowInfoRequest
	getShowInfoStmt     *sql.Stmt
	checkMusicGroupChan chan checkMusicGroupRequest
	checkMusicGroupStmt *sql.Stmt
	getMusicInfoChan    chan getMusicInfoRequest
	getMusicInfoStmt    *sql.Stmt
	quit                chan bool
	done                chan bool
}

func (db *DB) init() (err error) {
	godrv.Register("SET CHARACTER SET utf8;")

	dsn := fmt.Sprintf("tcp:%s:3306*%s/%s/%s", db.conf.dbHost, db.conf.dbDb, db.conf.dbUser, db.conf.dbPasswd)
	if db.dbh, err = sql.Open("mymysql", dsn); err != nil {
		return
	}

	var dbver int
	err = db.dbh.QueryRow("select DB from VERSION;").Scan(&dbver)
	if err != nil {
		err = fmt.Errorf("fetching version: %s", err)
		return
	}
	if dbver != DB_VERSION {
		err = fmt.Errorf("version mismatch is %d, should be %d", dbver, DB_VERSION)
		return
	}

	if db.getPasswordStmt, err = db.dbh.Prepare("select PASSWORD from USERS where LOGIN_NAME = ?;"); err != nil {
		return
	}
	if db.getGroupOfCartStmt, err = db.dbh.Prepare("select NAME,DEFAULT_LOW_CART,DEFAULT_HIGH_CART from GROUPS where DEFAULT_LOW_CART <= ? and DEFAULT_HIGH_CART >= ?;"); err != nil {
		return
	}
	if db.getShowInfoStmt, err = db.dbh.Prepare("select CART.TITLE,CART.MACROS,DROPBOXES.GROUP_NAME,DROPBOXES.NORMALIZATION_LEVEL,DROPBOXES.AUTOTRIM_LEVEL,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART from CART, DROPBOXES, GROUPS where CART.NUMBER = DROPBOXES.TO_CART and GROUPS.NAME = DROPBOXES.GROUP_NAME and CART.NUMBER = ?;"); err != nil {
		return
	}
	if db.checkMusicGroupStmt, err = db.dbh.Prepare("select count(*) from DROPBOXES where GROUP_NAME = ? and SET_USER_DEFINED like \"M;%\";"); err != nil {
		return
	}
	if db.getMusicInfoStmt, err = db.dbh.Prepare("select NORMALIZATION_LEVEL,AUTOTRIM_LEVEL from DROPBOXES where DROPBOXES.GROUP_NAME = ?;"); err != nil {
		return
	}
	return
}

func (db *DB) getPassword(username string, cached bool) (result getPasswordResult) {
	if cached {
		result.password = db.passwordCache[username]
	}

	if result.password == "" {
		if result.err = db.getPasswordStmt.QueryRow(username).Scan(&result.password); result.err != nil {
			if result.err == sql.ErrNoRows {
				result.err = fmt.Errorf("user '%s' not known by rivendell", username)
			}
			return
		}
		db.passwordCache[username] = result.password
	}

	return
}

func (db *DB) getGroupOfCart(cart uint) (result getGroupOfCartResult) {
	var rows *sql.Rows
	if rows, result.err = db.getGroupOfCartStmt.Query(cart, cart); result.err != nil {
		return
	}
	defer rows.Close()
	sizeMin := ^uint(0)
	for rows.Next() {
		var name string
		var lowCart, highCart uint
		if result.err = rows.Scan(&name, &lowCart, &highCart); result.err != nil {
			return
		}
		if highCart >= lowCart {
			size := (highCart - lowCart) + 1
			if sizeMin > size {
				result.group = name
				sizeMin = size
			}
		}
	}
	if result.err = rows.Err(); result.err != nil {
		return
	}
	if result.group == "" {
		result.err = fmt.Errorf("cart is outside of all group cart ranges")
	}
	return
}

func (db *DB) getLogTableName(log string) (logtable string, err error) {
	logtable = strings.Replace(log, " ", "_", -1) + "_LOG"
	if !mysqlTableNameRe.MatchString(logtable) {
		return "", fmt.Errorf("the log table name contains illegal charecters: %s", logtable)
	}
	return
}

func (db *DB) getShowCarts(log string, lowCart, highCart int) (carts []uint, err error) {
	var logtable string
	if logtable, err = db.getLogTableName(log); err != nil {
		return
	}
	q := fmt.Sprintf("select CART_NUMBER from %s where CART_NUMBER >= %d and CART_NUMBER <= %d order by COUNT;", logtable, lowCart, highCart)
	var rows *sql.Rows
	if rows, err = db.dbh.Query(q); err != nil {
		return
	}
	defer rows.Close()
	for rows.Next() {
		var cart uint
		if err = rows.Scan(&cart); err != nil {
			return
		}
		carts = append(carts, cart)
	}
	err = rows.Err()
	return
}

func (db *DB) getShowInfo(showid uint) (result getShowInfoResult) {
	var macros string
	var lowCart, highCart int
	result.err = db.getShowInfoStmt.QueryRow(showid).Scan(&result.title, &macros, &result.group, &result.normLvl, &result.trimLvl, &lowCart, &highCart)
	if result.err != nil {
		if result.err == sql.ErrNoRows {
			result.err = fmt.Errorf("show '%d' not found", showid)
		}
		return
	}
	result.normLvl /= 100
	result.trimLvl /= 100
	result.carts, result.err = db.getShowCarts(showMacroRe.FindStringSubmatch(macros)[1], lowCart, highCart)
	return
}

func (db *DB) checkMusicGroup(group string) (result checkMusicGroupResult) {
	var cnt int
	if result.err = db.checkMusicGroupStmt.QueryRow(group).Scan(&cnt); result.err != nil {
		if result.err == sql.ErrNoRows {
			result.err = nil
			result.ismusic = false
		}
		return
	}
	result.ismusic = cnt > 0
	return
}

func (db *DB) getMusicInfo(group string) (result getMusicInfoResult) {
	result.err = db.getMusicInfoStmt.QueryRow(group).Scan(&result.normLvl, &result.trimLvl)
	if result.err != nil {
		if result.err == sql.ErrNoRows {
			result.err = fmt.Errorf("music pool '%s' not found", group)
		}
		return
	}
	return
}

func (db *DB) dispatchRequests() {
	defer func() { db.done <- true }()
	for {
		select {
		case <-db.quit:
			return
		case req := <-db.getPasswordChan:
			req.response <- db.getPassword(req.username, req.cached)
		case req := <-db.getGroupOfCartChan:
			req.response <- db.getGroupOfCart(req.cart)
		case req := <-db.getShowInfoChan:
			req.response <- db.getShowInfo(req.showid)
		case req := <-db.checkMusicGroupChan:
			req.response <- db.checkMusicGroup(req.group)
		case req := <-db.getMusicInfoChan:
			req.response <- db.getMusicInfo(req.group)
		}
	}
}

// *********************************************************
// Public Interface

type DBChan struct {
	getPasswordChan     chan<- getPasswordRequest
	getGroupOfCartChan  chan<- getGroupOfCartRequest
	getShowInfoChan     chan<- getShowInfoRequest
	checkMusicGroupChan chan<- checkMusicGroupRequest
	getMusicInfoChan    chan<- getMusicInfoRequest
}

func (db *DBChan) GetPassword(username string, cached bool) (string, error) {
	resCh := make(chan getPasswordResult)
	req := getPasswordRequest{}
	req.username = username
	req.cached = cached
	req.response = resCh
	db.getPasswordChan <- req

	res := <-resCh
	if res.err != nil {
		return "", res.err
	}
	return res.password, nil
}

func (db *DBChan) CheckPassword(username, password string) (bool, error) {
	cached := true

	for {
		resCh := make(chan getPasswordResult)
		req := getPasswordRequest{}
		req.username = username
		req.cached = cached
		req.response = resCh
		db.getPasswordChan <- req

		res := <-resCh
		if res.err != nil {
			return false, res.err
		}
		if password == res.password {
			return true, nil
		}
		if cached {
			cached = false
		} else {
			break
		}
	}
	return false, nil
}

func (db *DBChan) GetGroupOfCart(cart uint) (string, error) {
	resCh := make(chan getGroupOfCartResult)
	req := getGroupOfCartRequest{}
	req.cart = cart
	req.response = resCh
	db.getGroupOfCartChan <- req

	res := <-resCh
	if res.err != nil {
		return "", res.err
	}
	return res.group, nil
}

func (db *DBChan) GetShowInfo(showid uint) (string, int, int, []uint, error) {
	resCh := make(chan getShowInfoResult)
	req := getShowInfoRequest{}
	req.showid = showid
	req.response = resCh
	db.getShowInfoChan <- req

	res := <-resCh
	if res.err != nil {
		return "", 0, 0, nil, res.err
	}
	return res.group, res.normLvl, res.trimLvl, res.carts, nil
}

func (db *DBChan) CheckMusicGroup(groupname string) (bool, error) {
	resCh := make(chan checkMusicGroupResult)
	req := checkMusicGroupRequest{}
	req.group = groupname
	req.response = resCh
	db.checkMusicGroupChan <- req

	res := <-resCh
	if res.err != nil {
		return false, res.err
	}
	return res.ismusic, nil
}

func (db *DBChan) GetMusicInfo(groupname string) (int, int, error) {
	resCh := make(chan getMusicInfoResult)
	req := getMusicInfoRequest{}
	req.group = groupname
	req.response = resCh
	db.getMusicInfoChan <- req

	res := <-resCh
	if res.err != nil {
		return 0, 0, res.err
	}
	return res.normLvl, res.trimLvl, nil
}

func (db *DB) GetInterface() *DBChan {
	ch := &DBChan{}
	ch.getPasswordChan = db.getPasswordChan
	ch.getGroupOfCartChan = db.getGroupOfCartChan
	ch.getShowInfoChan = db.getShowInfoChan
	ch.checkMusicGroupChan = db.checkMusicGroupChan
	ch.getMusicInfoChan = db.getMusicInfoChan
	return ch
}

func (db *DB) Cleanup() {
	db.quit <- true
	<-db.done
	close(db.quit)
	close(db.done)
	close(db.getPasswordChan)
	if db.dbh != nil {
		db.dbh.Close()
	}
	if db.getPasswordStmt != nil {
		db.getPasswordStmt.Close()
	}
	if db.getGroupOfCartStmt != nil {
		db.getGroupOfCartStmt.Close()
	}
	if db.getShowInfoStmt != nil {
		db.getShowInfoStmt.Close()
	}
	if db.checkMusicGroupStmt != nil {
		db.checkMusicGroupStmt.Close()
	}
	if db.getMusicInfoStmt != nil {
		db.getMusicInfoStmt.Close()
	}
}

func NewDB(configfile string) (db *DB, err error) {
	db = new(DB)
	if db.conf, err = newConfig(configfile); err != nil {
		return
	}
	db.quit = make(chan bool)
	db.done = make(chan bool)
	db.passwordCache = make(map[string]string)
	db.getPasswordChan = make(chan getPasswordRequest, 10)
	db.getGroupOfCartChan = make(chan getGroupOfCartRequest, 10)
	db.getShowInfoChan = make(chan getShowInfoRequest, 10)
	db.checkMusicGroupChan = make(chan checkMusicGroupRequest, 10)
	db.getMusicInfoChan = make(chan getMusicInfoRequest, 10)

	if err = db.init(); err != nil {
		return
	}

	go db.dispatchRequests()
	return
}