added rddb package

2 files changed, 525 insertions, 0 deletions

diff --git a/rddb/config.go b/rddb/config.go
new file mode 100644
index 0000000..790d69c
--- /dev/null
+++ b/rddb/config.go
@@ -0,0 +1,67 @@
+//
+//  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 (
+	"github.com/vaughan0/go-ini"
+)
+
+type config struct {
+	configfile string
+	dbHost     string
+	dbUser     string
+	dbPasswd   string
+	dbDb       string
+}
+
+func getIniValue(file ini.File, section string, key string, dflt string) string {
+	value, ok := file.Get(section, key)
+	if ok {
+		return value
+	}
+	return dflt
+}
+
+func (self *config) readConfigFile() error {
+	file, err := ini.LoadFile(self.configfile)
+	if err != nil {
+		return err
+	}
+
+	self.dbHost = getIniValue(file, "mySQL", "Hostname", "localhost")
+	self.dbUser = getIniValue(file, "mySQL", "Loginname", "rivendell")
+	self.dbPasswd = getIniValue(file, "mySQL", "Password", "letmein")
+	self.dbDb = getIniValue(file, "mySQL", "Database", "rivendell")
+	return nil
+}
+
+func newConfig(configfile string) (conf *config, err error) {
+	conf = new(config)
+	conf.configfile = configfile
+	if err = conf.readConfigFile(); err != nil {
+		return
+	}
+	return
+}
diff --git a/rddb/rddb.go b/rddb/rddb.go
new file mode 100644
index 0000000..efc0f4f
--- /dev/null
+++ b/rddb/rddb.go
@@ -0,0 +1,458 @@
+//
+//  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 RdDb 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 *RdDb) 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 *RdDb) 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 *RdDb) 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 *RdDb) 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 *RdDb) 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 *RdDb) 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 *RdDb) 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 *RdDb) 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 *RdDb) 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 RdDbChan struct {
+	getPasswordChan     chan<- getPasswordRequest
+	getGroupOfCartChan  chan<- getGroupOfCartRequest
+	getShowInfoChan     chan<- getShowInfoRequest
+	checkMusicGroupChan chan<- checkMusicGroupRequest
+	getMusicInfoChan    chan<- getMusicInfoRequest
+}
+
+func (db *RdDbChan) 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 *RdDbChan) 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 *RdDbChan) 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 *RdDbChan) 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 *RdDbChan) 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 *RdDbChan) 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 *RdDb) GetInterface() *RdDbChan {
+	ch := &RdDbChan{}
+	ch.getPasswordChan = db.getPasswordChan
+	ch.getGroupOfCartChan = db.getGroupOfCartChan
+	ch.getShowInfoChan = db.getShowInfoChan
+	ch.checkMusicGroupChan = db.checkMusicGroupChan
+	ch.getMusicInfoChan = db.getMusicInfoChan
+	return ch
+}
+
+func (db *RdDb) 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 NewRdDb(configfile string) (db *RdDb, err error) {
+	db = new(RdDb)
+	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
+}