path: root/src/helsinki.at/rhimport/rddb.go

//
//  rhimportd
//
//  The Radio Helsinki Rivendell Import Daemon
//
//
//  Copyright (C) 2015 Christian Pointner <equinox@helsinki.at>
//
//  This file is part of rhimportd.
//
//  rhimportd 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.
//
//  rhimportd 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 rhimportd. If not, see <http://www.gnu.org/licenses/>.
//

package rhimport

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
	norm_lvl int
	trim_lvl 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 {
	norm_lvl int
	trim_lvl int
	err      error
}

type getMusicInfoRequest struct {
	group    string
	response chan getMusicInfoResult
}

type RdDb struct {
	dbh                 *sql.DB
	password_cache      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 (self *RdDb) init(conf *Config) (err error) {
	godrv.Register("SET CHARACTER SET utf8;")

	dsn := fmt.Sprintf("tcp:%s:3306*%s/%s/%s", conf.db_host, conf.db_db, conf.db_user, conf.db_passwd)
	if self.dbh, err = sql.Open("mymysql", dsn); err != nil {
		return
	}

	var dbver int
	err = self.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 self.getPasswordStmt, err = self.dbh.Prepare("select PASSWORD from USERS where LOGIN_NAME = ?;"); err != nil {
		return
	}
	if self.getGroupOfCartStmt, err = self.dbh.Prepare("select NAME,DEFAULT_LOW_CART,DEFAULT_HIGH_CART from GROUPS where DEFAULT_LOW_CART <= ? and DEFAULT_HIGH_CART >= ?;"); err != nil {
		return
	}
	if self.getShowInfoStmt, err = self.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 self.checkMusicGroupStmt, err = self.dbh.Prepare("select count(*) from DROPBOXES where GROUP_NAME = ? and SET_USER_DEFINED like \"M;%\";"); err != nil {
		return
	}
	if self.getMusicInfoStmt, err = self.dbh.Prepare("select NORMALIZATION_LEVEL,AUTOTRIM_LEVEL from DROPBOXES where DROPBOXES.GROUP_NAME = ?;"); err != nil {
		return
	}
	return
}

func (self *RdDb) getPassword(username string, cached bool) (result getPasswordResult) {
	if cached {
		result.password = self.password_cache[username]
	}

	if result.password == "" {
		if result.err = self.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
		}
		self.password_cache[username] = result.password
	}

	return
}

func (self *RdDb) getGroupOfCart(cart uint) (result getGroupOfCartResult) {
	var rows *sql.Rows
	if rows, result.err = self.getGroupOfCartStmt.Query(cart, cart); result.err != nil {
		return
	}
	defer rows.Close()
	size_min := ^uint(0)
	for rows.Next() {
		var name string
		var low_cart, high_cart uint
		if result.err = rows.Scan(&name, &low_cart, &high_cart); result.err != nil {
			return
		}
		if high_cart >= low_cart {
			size := (high_cart - low_cart) + 1
			if size_min > size {
				result.group = name
				size_min = 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 (self *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 (self *RdDb) getShowCarts(log string, low_cart, high_cart int) (carts []uint, err error) {
	var logtable string
	if logtable, err = self.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, low_cart, high_cart)
	var rows *sql.Rows
	if rows, err = self.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 (self *RdDb) getShowInfo(showid uint) (result getShowInfoResult) {
	var macros string
	var low_cart, high_cart int
	result.err = self.getShowInfoStmt.QueryRow(showid).Scan(&result.title, &macros, &result.group, &result.norm_lvl, &result.trim_lvl, &low_cart, &high_cart)
	if result.err != nil {
		if result.err == sql.ErrNoRows {
			result.err = fmt.Errorf("show '%d' not found", showid)
		}
		return
	}
	result.norm_lvl /= 100
	result.trim_lvl /= 100
	result.carts, result.err = self.getShowCarts(showMacroRe.FindStringSubmatch(macros)[1], low_cart, high_cart)
	return
}

func (self *RdDb) checkMusicGroup(group string) (result checkMusicGroupResult) {
	var cnt int
	if result.err = self.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 (self *RdDb) getMusicInfo(group string) (result getMusicInfoResult) {
	result.err = self.getMusicInfoStmt.QueryRow(group).Scan(&result.norm_lvl, &result.trim_lvl)
	if result.err != nil {
		if result.err == sql.ErrNoRows {
			result.err = fmt.Errorf("music pool '%s' not found", group)
		}
		return
	}
	return
}

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

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

func NewRdDb(conf *Config) (db *RdDb, err error) {
	db = new(RdDb)

	db.quit = make(chan bool)
	db.done = make(chan bool)
	db.password_cache = make(map[string]string)
	db.getPasswordChan = make(chan getPasswordRequest)
	db.getGroupOfCartChan = make(chan getGroupOfCartRequest)
	db.getShowInfoChan = make(chan getShowInfoRequest)
	db.checkMusicGroupChan = make(chan checkMusicGroupRequest)
	db.getMusicInfoChan = make(chan getMusicInfoRequest)

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

	go db.dispatchRequests()
	return
}