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"
	"path/filepath"
	"regexp"
	"strconv"
	"strings"
	"time"

	"github.com/ziutek/mymysql/godrv"
)

var (
	showMacroRe      = regexp.MustCompile(`^LL 1 ([^ ]+) 0\!$`)
	mysqlTableNameRe = regexp.MustCompile(`^[_0-9a-zA-Z-]+$`)
	rhythmRegexp     = regexp.MustCompile(`^[01]{4}$`)
	starttimeRegexp  = regexp.MustCompile(`^[0-2][0-9][0-5][0-9]$`)
)

const (
	DB_VERSION           = 250
	dropboxPseudoStation = "import-dropbox"
	defaultCartTitle     = "[new cart]"
)

type ShowType string

const (
	ShowTypeRegular ShowType = "n"
	ShowTypeRerun   ShowType = "r"
	ShowTypeSpecial ShowType = "s"
)

func (s ShowType) String() string {
	switch s {
	case ShowTypeRegular:
		return "regular"
	case ShowTypeRerun:
		return "re-run"
	case ShowTypeSpecial:
		return "special"
	}
	return "unknown"
}

type Weekday int

func (w Weekday) String() string {
	switch w {
	case 1:
		return "Montag"
	case 2:
		return "Dienstag"
	case 3:
		return "Mittwoch"
	case 4:
		return "Donnerstag"
	case 5:
		return "Freitag"
	case 6:
		return "Samstag"
	case 7:
		return "Sonntag"
	}
	return "-"
}

func showtypeFromString(in string) ShowType {
	switch ShowType(in) {
	case ShowTypeRerun:
		return ShowTypeRerun
	case ShowTypeSpecial:
		return ShowTypeSpecial
	}
	return ShowTypeRegular
}

func rhythmFromString(in string) string {
	if !rhythmRegexp.Match([]byte(in)) || in == "0000" {
		return "????"
	}
	return in
}

func dowFromString(in string) Weekday {
	tmp, err := strconv.ParseInt(in, 10, 32)
	if err != nil {
		return -1
	}
	if tmp < 1 || tmp > 7 {
		return -1
	}
	return Weekday(tmp)
}

func starttimeFromString(in string) string {
	if in == "----" {
		return "--:--"
	}
	if !starttimeRegexp.Match([]byte(in)) {
		return "??:??"
	}
	tmp, err := strconv.ParseUint(in, 10, 32)
	if err != nil || tmp > 2359 {
		return "??:??"
	}
	return in[:2] + ":" + in[2:]
}

func showlengthFromString(in string) uint {
	tmp, err := strconv.ParseUint(in, 10, 32)
	if err != nil {
		return 0
	}
	return uint(tmp)
}

func parseShowParams(params string) (rhythm, starttime string, dow Weekday, length uint, stype ShowType) {
	p := strings.Split(params, ";")
	rhythm = "????"
	dow = -1
	starttime = "??:??"
	length = 0
	stype = ShowTypeRegular

	if len(p) < 2 {
		return
	}
	rhythm = rhythmFromString(p[1])

	if len(p) < 3 {
		return
	}
	dow = dowFromString(p[2])

	if len(p) < 4 {
		return
	}
	starttime = starttimeFromString(p[3])

	if len(p) < 5 {
		return
	}
	length = showlengthFromString(p[4])

	if len(p) < 6 {
		return
	}
	stype = showtypeFromString(p[5])
	return
}

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 ShowListEntry struct {
	ID        uint
	Title     string
	TitleFull string
	log       string
	Rhythm    string
	StartTime string
	Dow       Weekday
	Length    uint
	Type      ShowType
	lowCart   int
	highCart  int
}

type ShowList []ShowListEntry

func (s ShowList) Len() int {
	return len(s)
}

func (s ShowList) Less(i, j int) bool {
	ititle := strings.ToLower(s[i].TitleFull)
	jtitle := strings.ToLower(s[j].TitleFull)
	if ititle == jtitle {
		if s[i].Rhythm == s[j].Rhythm {
			if s[i].Dow == s[j].Dow {
				if s[i].StartTime == s[j].StartTime {
					return s[i].ID < s[j].ID
				}
				return s[i].StartTime < s[j].StartTime
			}
			return s[i].Dow < s[j].Dow
		}
		return s[i].Rhythm > s[j].Rhythm
	}
	return ititle < jtitle
}

func (s ShowList) Swap(i, j int) {
	s[i], s[j] = s[j], s[i]
}

type getShowListResult struct {
	shows ShowList
	err   error
}

type getShowListRequest struct {
	username string
	response chan<- getShowListResult
}

type CutListEntry struct {
	Number      uint
	Evergreen   bool
	Description string
	Duration    time.Duration
	Imported    NullTime
	NumPlayed   uint
	LastPlayed  NullTime
}

type CartListEntry struct {
	Number      uint
	Exists      bool
	Artist      string
	Title       string
	Album       string
	UserDefined string
	Cuts        []CutListEntry
}

type getShowCartListResult struct {
	carts []CartListEntry
	err   error
}

type getShowCartListRequest struct {
	show     ShowListEntry
	response chan<- getShowCartListResult
}

type JingleListEntry struct {
	Group string
	Title string
	Cart  int
}

type JingleList []JingleListEntry

func (jl JingleList) Len() int {
	return len(jl)
}

func (jl JingleList) Less(i, j int) bool {
	ititle := strings.ToLower(jl[i].Title)
	jtitle := strings.ToLower(jl[j].Title)
	return ititle < jtitle
}

func (jl JingleList) Swap(i, j int) {
	jl[i], jl[j] = jl[j], jl[i]
}

type getJingleListResult struct {
	jingles JingleList
	err     error
}

type getJingleListRequest struct {
	username string
	response chan<- getJingleListResult
}

type getJingleCutListResult struct {
	cart CartListEntry
	err  error
}

type getJingleCutListRequest struct {
	jingle   JingleListEntry
	response chan<- getJingleCutListResult
}

type PoolListEntry struct {
	Group     string
	ShortName string
	Title     string
}

type PoolList []PoolListEntry

func (p PoolList) Len() int {
	return len(p)
}

func (p PoolList) Less(i, j int) bool {
	ititle := strings.ToLower(p[i].Title)
	jtitle := strings.ToLower(p[j].Title)
	return ititle < jtitle
}

func (p PoolList) Swap(i, j int) {
	p[i], p[j] = p[j], p[i]
}

type getPoolListResult struct {
	pools PoolList
	err   error
}

type getPoolListRequest struct {
	username string
	response chan<- getPoolListResult
}

type getPoolCartListResult struct {
	carts map[uint]CartListEntry
	err   error
}

type getPoolCartListRequest struct {
	pool     PoolListEntry
	response chan<- getPoolCartListResult
}

type updateCutCartTitleResult struct {
	err error
}

type updateCutCartTitleRequest struct {
	cart       uint
	cut        uint
	groupName  string
	sourceFile string
	response   chan<- updateCutCartTitleResult
}

type updateCartUserDefinedResult struct {
	err error
}

type updateCartUserDefinedRequest struct {
	cart     uint
	value    string
	response chan<- updateCartUserDefinedResult
}

type db struct {
	conf                      *config
	readonly                  bool
	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
	getShowListChan           chan getShowListRequest
	getShowListStmt           *sql.Stmt
	getShowCartListChan       chan getShowCartListRequest
	getCartInfoStmt           *sql.Stmt
	getCutInfoStmt            *sql.Stmt
	getJingleListChan         chan getJingleListRequest
	getJingleListStmt         *sql.Stmt
	getJingleCutListChan      chan getJingleCutListRequest
	getPoolListChan           chan getPoolListRequest
	getPoolListStmt           *sql.Stmt
	getPoolCartListChan       chan getPoolCartListRequest
	getPoolCartsStmt          *sql.Stmt
	updateCutCartTitleChan    chan updateCutCartTitleRequest
	getGroupDefaultTitleStmt  *sql.Stmt
	updateCutTitleStmt        *sql.Stmt
	updateCartTitleMaybeStmt  *sql.Stmt
	updateCartUserDefinedChan chan updateCartUserDefinedRequest
	updateCartUserDefinedStmt *sql.Stmt
	quit                      chan bool
	done                      chan bool
}

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

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

	var dbver int
	err = d.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 d.getPasswordStmt, err = d.dbh.Prepare("select PASSWORD from USERS where LOGIN_NAME = ?;"); err != nil {
		return
	}
	if d.getGroupOfCartStmt, err = d.dbh.Prepare("select NAME,DEFAULT_LOW_CART,DEFAULT_HIGH_CART from GROUPS where DEFAULT_LOW_CART <= ? and DEFAULT_HIGH_CART >= ?;"); err != nil {
		return
	}
	if d.getShowInfoStmt, err = d.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 d.checkMusicGroupStmt, err = d.dbh.Prepare("select count(*) from DROPBOXES where GROUP_NAME = ? and SET_USER_DEFINED like \"M;%\";"); err != nil {
		return
	}
	if d.getMusicInfoStmt, err = d.dbh.Prepare("select NORMALIZATION_LEVEL,AUTOTRIM_LEVEL from DROPBOXES where DROPBOXES.GROUP_NAME = ?;"); err != nil {
		return
	}
	if d.getShowListStmt, err = d.dbh.Prepare("select CART.TITLE,CART.MACROS,DROPBOXES.TO_CART,DROPBOXES.SET_USER_DEFINED,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART from CART, USER_PERMS, DROPBOXES, GROUPS where CART.NUMBER = DROPBOXES.TO_CART and USER_PERMS.USER_NAME = ? and DROPBOXES.GROUP_NAME = USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME = GROUPS.NAME and DROPBOXES.STATION_NAME = ?;"); err != nil {
		return
	}
	if d.getCartInfoStmt, err = d.dbh.Prepare("select TITLE from CART where TYPE=1 and NUMBER = ?"); err != nil {
		return
	}
	if d.getCutInfoStmt, err = d.dbh.Prepare("select CUT_NAME,EVERGREEN,DESCRIPTION,LENGTH,ORIGIN_DATETIME,PLAY_COUNTER,LAST_PLAY_DATETIME from CUTS where CART_NUMBER = ?"); err != nil {
		return
	}
	if d.getJingleListStmt, err = d.dbh.Prepare("select GROUPS.NAME,DROPBOXES.SET_USER_DEFINED,GROUPS.DEFAULT_LOW_CART,GROUPS.DESCRIPTION from USER_PERMS, DROPBOXES, GROUPS where USER_PERMS.USER_NAME = ? and DROPBOXES.GROUP_NAME = USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME = GROUPS.NAME and DROPBOXES.STATION_NAME = ?;"); err != nil {
		return
	}
	if d.getPoolListStmt, err = d.dbh.Prepare("select GROUPS.NAME,DROPBOXES.SET_USER_DEFINED,DROPBOXES.PATH,GROUPS.DESCRIPTION from USER_PERMS, DROPBOXES, GROUPS where USER_PERMS.USER_NAME = ? and DROPBOXES.GROUP_NAME = USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME = GROUPS.NAME and DROPBOXES.STATION_NAME = ?;"); err != nil {
		return
	}
	if d.getPoolCartsStmt, err = d.dbh.Prepare("select CART.NUMBER,CART.ARTIST,CART.TITLE,CART.ALBUM,CART.USER_DEFINED,CUTS.CUT_NAME,CUTS.EVERGREEN,CUTS.DESCRIPTION,CUTS.LENGTH,CUTS.ORIGIN_DATETIME,CUTS.PLAY_COUNTER,CUTS.LAST_PLAY_DATETIME from CUTS,CART where CUTS.CART_NUMBER = CART.NUMBER and CART.GROUP_NAME = ?;"); err != nil {
		return
	}
	if d.getGroupDefaultTitleStmt, err = d.dbh.Prepare("select DEFAULT_TITLE from GROUPS where NAME = ?;"); err != nil {
		return
	}

	if !d.readonly {
		if d.updateCutTitleStmt, err = d.dbh.Prepare("update CUTS set DESCRIPTION = ? where CUT_NAME = ?;"); err != nil {
			return
		}
		if d.updateCartTitleMaybeStmt, err = d.dbh.Prepare("update CART set TITLE = ? where NUMBER = ? and TITLE = ?;"); err != nil {
			return
		}
		if d.updateCartUserDefinedStmt, err = d.dbh.Prepare("update CART set USER_DEFINED = ? where NUMBER = ?;"); err != nil {
			return
		}
	}

	return
}

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

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

	return
}

func (d *db) getGroupOfCart(cart uint) (result getGroupOfCartResult) {
	var rows *sql.Rows
	if rows, result.err = d.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 (d *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 (d *db) getShowCarts(log string, lowCart, highCart int) (carts []uint, err error) {
	var logtable string
	if logtable, err = d.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 = d.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 (d *db) getShowInfo(showid uint) (result getShowInfoResult) {
	var macros string
	var lowCart, highCart int
	result.err = d.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 = d.getShowCarts(showMacroRe.FindStringSubmatch(macros)[1], lowCart, highCart)
	return
}

func (d *db) checkMusicGroup(group string) (result checkMusicGroupResult) {
	var cnt int
	if result.err = d.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 (d *db) getMusicInfo(group string) (result getMusicInfoResult) {
	result.err = d.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 (d *db) getShowList(username string) (result getShowListResult) {
	var rows *sql.Rows
	if rows, result.err = d.getShowListStmt.Query(username, dropboxPseudoStation); result.err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var show ShowListEntry
		var macros, params string

		if result.err = rows.Scan(&show.Title, &macros, &show.ID, &params, &show.lowCart, &show.highCart); result.err != nil {
			return
		}
		if params[0] != 'S' {
			continue
		}
		show.log = showMacroRe.FindStringSubmatch(macros)[1]
		show.Rhythm, show.StartTime, show.Dow, show.Length, show.Type = parseShowParams(params)

		show.TitleFull = show.Title
		switch show.Type {
		case ShowTypeRerun:
			show.TitleFull += " (Wiederholung)"
		case ShowTypeSpecial:
			show.TitleFull += " (Sondersendung)"
		}
		result.shows = append(result.shows, show)
	}
	result.err = rows.Err()
	return
}

func (d *db) getCutInfo(cart uint) (cuts []CutListEntry, err error) {
	var rows *sql.Rows
	if rows, err = d.getCutInfoStmt.Query(cart); err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var cut = CutListEntry{}
		var cutName, evergreen string
		var length uint

		if err = rows.Scan(&cutName, &evergreen, &cut.Description, &length, &cut.Imported, &cut.NumPlayed, &cut.LastPlayed); err != nil {
			return
		}

		parts := strings.Split(cutName, "_")
		if len(parts) == 2 {
			if cn, converr := strconv.ParseUint(parts[1], 10, 32); converr != nil {
				continue
			} else {
				cut.Number = uint(cn)
			}
		} else {
			continue
		}
		switch evergreen {
		case "Y":
			cut.Evergreen = true
		default:
			cut.Evergreen = false
		}
		cut.Duration = time.Duration(length) * time.Millisecond

		cuts = append(cuts, cut)
	}
	err = rows.Err()
	return
}

func (d *db) getCartInfo(cart uint) (c CartListEntry, err error) {
	c = CartListEntry{}
	c.Number = cart
	c.Exists = true
	err = d.getCartInfoStmt.QueryRow(cart).Scan(&c.Title)
	if err != nil {
		if err == sql.ErrNoRows {
			err = nil
			c.Exists = false
		}
		return
	}
	if c.Cuts, err = d.getCutInfo(cart); err != nil {
		return
	}
	return
}

func (d *db) getShowCartList(show ShowListEntry) (result getShowCartListResult) {
	var carts []uint
	if carts, result.err = d.getShowCarts(show.log, show.lowCart, show.highCart); result.err != nil {
		return
	}

	for _, cart := range carts {
		var c CartListEntry
		if c, result.err = d.getCartInfo(cart); result.err != nil {
			return
		}
		result.carts = append(result.carts, c)
	}
	return
}

func (d *db) getJingleList(username string) (result getJingleListResult) {
	var rows *sql.Rows
	if rows, result.err = d.getJingleListStmt.Query(username, dropboxPseudoStation); result.err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var jingle JingleListEntry
		var params string

		if result.err = rows.Scan(&jingle.Group, &params, &jingle.Cart, &jingle.Title); result.err != nil {
			return
		}
		if params[0] != 'J' {
			continue
		}
		result.jingles = append(result.jingles, jingle)
	}
	result.err = rows.Err()
	return
}

func (d *db) getJingleCutList(jingle JingleListEntry) (result getJingleCutListResult) {
	if result.cart, result.err = d.getCartInfo(uint(jingle.Cart)); result.err != nil {
		return
	}
	return
}

func (d *db) getPoolList(username string) (result getPoolListResult) {
	var rows *sql.Rows
	if rows, result.err = d.getPoolListStmt.Query(username, dropboxPseudoStation); result.err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		var pool PoolListEntry
		var params string
		var shortName sql.NullString

		if result.err = rows.Scan(&pool.Group, &params, &shortName, &pool.Title); result.err != nil {
			return
		}
		if params[0] != 'M' {
			continue
		}
		pool.ShortName = shortName.String
		result.pools = append(result.pools, pool)
	}
	result.err = rows.Err()
	return
}

func (d *db) getPoolCartList(pool PoolListEntry) (result getPoolCartListResult) {
	var rows *sql.Rows
	if rows, result.err = d.getPoolCartsStmt.Query(pool.Group); result.err != nil {
		return
	}
	defer rows.Close()

	result.carts = make(map[uint]CartListEntry)
	for rows.Next() {
		var cut CutListEntry
		var cart, length uint
		var cutName, evergreen string
		var artist, title, album, userDefined sql.NullString

		if result.err = rows.Scan(&cart, &artist, &title, &album, &userDefined, &cutName, &evergreen, &cut.Description, &length, &cut.Imported, &cut.NumPlayed, &cut.LastPlayed); result.err != nil {
			return
		}

		parts := strings.Split(cutName, "_")
		if len(parts) == 2 {
			if cn, converr := strconv.ParseUint(parts[1], 10, 32); converr != nil {
				continue
			} else {
				cut.Number = uint(cn)
			}
		} else {
			continue
		}
		switch evergreen {
		case "Y":
			cut.Evergreen = true
		default:
			cut.Evergreen = false
		}
		cut.Duration = time.Duration(length) * time.Millisecond

		if c, exists := result.carts[cart]; !exists {
			c = CartListEntry{cart, true, artist.String, title.String, album.String, userDefined.String, nil}
			c.Cuts = append(c.Cuts, cut)
			result.carts[cart] = c
		} else {
			c.Cuts = append(c.Cuts, cut)
		}
	}
	result.err = rows.Err()
	return
}

func (d *db) updateCutCartTitle(cart, cut uint, groupName, sourceFile string) (result updateCutCartTitleResult) {
	if d.readonly {
		result.err = fmt.Errorf("updating Cut/Cart title prohibited: read-only database connection")
		return
	}

	path, filename := filepath.Split(sourceFile)
	ext := strings.TrimLeft(filepath.Ext(filename), ".")
	filename = strings.TrimRight(strings.TrimSuffix(filename, ext), ".")

	if groupName == "" {
		res := d.getGroupOfCart(cart)
		if res.err != nil {
			result.err = res.err
			return
		}
		groupName = res.group
	}

	groupDefaultTitle := ""
	result.err = d.getGroupDefaultTitleStmt.QueryRow(groupName).Scan(&groupDefaultTitle)
	if result.err != nil {
		return
	}

	r := strings.NewReplacer("%p", path, "%f", filename, "%e", ext)
	title := r.Replace(groupDefaultTitle)

	if _, result.err = d.updateCutTitleStmt.Exec(title, fmt.Sprintf("%06d_%03d", cart, cut)); result.err != nil {
		return
	}
	if _, result.err = d.updateCartTitleMaybeStmt.Exec(title, cart, defaultCartTitle); result.err != nil {
		return
	}
	return
}

func (d *db) updateCartUserDefined(cart uint, value string) (result updateCartUserDefinedResult) {
	if d.readonly {
		result.err = fmt.Errorf("updating Cart User-defined value prohibited: read-only database connection")
		return
	}
	_, result.err = d.updateCartUserDefinedStmt.Exec(value, cart)
	return
}

func (d *db) dispatchRequests() {
	defer func() { d.done <- true }()
	for {
		select {
		case <-d.quit:
			return
		case req := <-d.getPasswordChan:
			req.response <- d.getPassword(req.username, req.cached)
		case req := <-d.getGroupOfCartChan:
			req.response <- d.getGroupOfCart(req.cart)
		case req := <-d.getShowInfoChan:
			req.response <- d.getShowInfo(req.showid)
		case req := <-d.checkMusicGroupChan:
			req.response <- d.checkMusicGroup(req.group)
		case req := <-d.getMusicInfoChan:
			req.response <- d.getMusicInfo(req.group)
		case req := <-d.getShowListChan:
			req.response <- d.getShowList(req.username)
		case req := <-d.getShowCartListChan:
			req.response <- d.getShowCartList(req.show)
		case req := <-d.getJingleListChan:
			req.response <- d.getJingleList(req.username)
		case req := <-d.getJingleCutListChan:
			req.response <- d.getJingleCutList(req.jingle)
		case req := <-d.getPoolListChan:
			req.response <- d.getPoolList(req.username)
		case req := <-d.getPoolCartListChan:
			req.response <- d.getPoolCartList(req.pool)
		case req := <-d.updateCutCartTitleChan:
			req.response <- d.updateCutCartTitle(req.cart, req.cut, req.groupName, req.sourceFile)
		case req := <-d.updateCartUserDefinedChan:
			req.response <- d.updateCartUserDefined(req.cart, req.value)
		}
	}
}

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

type DB struct {
	getPasswordChan           chan<- getPasswordRequest
	getGroupOfCartChan        chan<- getGroupOfCartRequest
	getShowInfoChan           chan<- getShowInfoRequest
	checkMusicGroupChan       chan<- checkMusicGroupRequest
	getMusicInfoChan          chan<- getMusicInfoRequest
	getShowListChan           chan<- getShowListRequest
	getShowCartListChan       chan<- getShowCartListRequest
	getJingleListChan         chan<- getJingleListRequest
	getJingleCutListChan      chan<- getJingleCutListRequest
	getPoolListChan           chan<- getPoolListRequest
	getPoolCartListChan       chan<- getPoolCartListRequest
	updateCutCartTitleChan    chan<- updateCutCartTitleRequest
	updateCartUserDefinedChan chan<- updateCartUserDefinedRequest
}

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

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

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

	for {
		resCh := make(chan getPasswordResult)
		req := getPasswordRequest{}
		req.username = username
		req.cached = cached
		req.response = resCh
		d.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 (d *DB) GetGroupOfCart(cart uint) (string, error) {
	resCh := make(chan getGroupOfCartResult)
	req := getGroupOfCartRequest{}
	req.cart = cart
	req.response = resCh
	d.getGroupOfCartChan <- req

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

func (d *DB) GetShowInfo(showid uint) (string, int, int, []uint, error) {
	resCh := make(chan getShowInfoResult)
	req := getShowInfoRequest{}
	req.showid = showid
	req.response = resCh
	d.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 (d *DB) CheckMusicGroup(groupname string) (bool, error) {
	resCh := make(chan checkMusicGroupResult)
	req := checkMusicGroupRequest{}
	req.group = groupname
	req.response = resCh
	d.checkMusicGroupChan <- req

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

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

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

func (d *DB) GetShowList(username string) (ShowList, error) {
	resCh := make(chan getShowListResult)
	req := getShowListRequest{}
	req.username = username
	req.response = resCh
	d.getShowListChan <- req

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

func (d *DB) GetShowCartList(show ShowListEntry) ([]CartListEntry, error) {
	resCh := make(chan getShowCartListResult)
	req := getShowCartListRequest{}
	req.show = show
	req.response = resCh
	d.getShowCartListChan <- req

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

func (d *DB) GetJingleList(username string) (JingleList, error) {
	resCh := make(chan getJingleListResult)
	req := getJingleListRequest{}
	req.username = username
	req.response = resCh
	d.getJingleListChan <- req

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

func (d *DB) GetJingleCutList(jingle JingleListEntry) (CartListEntry, error) {
	resCh := make(chan getJingleCutListResult)
	req := getJingleCutListRequest{}
	req.jingle = jingle
	req.response = resCh
	d.getJingleCutListChan <- req

	res := <-resCh
	return res.cart, res.err
}

func (d *DB) GetPoolList(username string) (PoolList, error) {
	resCh := make(chan getPoolListResult)
	req := getPoolListRequest{}
	req.username = username
	req.response = resCh
	d.getPoolListChan <- req

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

func (d *DB) GetPoolCartList(pool PoolListEntry) (map[uint]CartListEntry, error) {
	resCh := make(chan getPoolCartListResult)
	req := getPoolCartListRequest{}
	req.pool = pool
	req.response = resCh
	d.getPoolCartListChan <- req

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

func (d *DB) UpdateCutCartTitle(cart, cut uint, groupName, sourceFile string) error {
	resCh := make(chan updateCutCartTitleResult)
	req := updateCutCartTitleRequest{}
	req.cart = cart
	req.cut = cut
	req.groupName = groupName
	req.sourceFile = sourceFile
	req.response = resCh
	d.updateCutCartTitleChan <- req

	res := <-resCh
	return res.err
}

func (d *DB) UpdateCartUserDefined(cart uint, value string) error {
	resCh := make(chan updateCartUserDefinedResult)
	req := updateCartUserDefinedRequest{}
	req.cart = cart
	req.value = value
	req.response = resCh
	d.updateCartUserDefinedChan <- req

	res := <-resCh
	return res.err
}

func (d *db) GetInterface() *DB {
	ch := &DB{}
	ch.getPasswordChan = d.getPasswordChan
	ch.getGroupOfCartChan = d.getGroupOfCartChan
	ch.getShowInfoChan = d.getShowInfoChan
	ch.checkMusicGroupChan = d.checkMusicGroupChan
	ch.getMusicInfoChan = d.getMusicInfoChan
	ch.getShowListChan = d.getShowListChan
	ch.getShowCartListChan = d.getShowCartListChan
	ch.getJingleListChan = d.getJingleListChan
	ch.getJingleCutListChan = d.getJingleCutListChan
	ch.getPoolListChan = d.getPoolListChan
	ch.getPoolCartListChan = d.getPoolCartListChan
	ch.updateCutCartTitleChan = d.updateCutCartTitleChan
	ch.updateCartUserDefinedChan = d.updateCartUserDefinedChan
	return ch
}

func (d *db) Cleanup() {
	d.quit <- true
	<-d.done
	close(d.quit)
	close(d.done)
	close(d.getPasswordChan)
	if d.dbh != nil {
		d.dbh.Close()
	}
	if d.getPasswordStmt != nil {
		d.getPasswordStmt.Close()
	}
	if d.getGroupOfCartStmt != nil {
		d.getGroupOfCartStmt.Close()
	}
	if d.getShowInfoStmt != nil {
		d.getShowInfoStmt.Close()
	}
	if d.checkMusicGroupStmt != nil {
		d.checkMusicGroupStmt.Close()
	}
	if d.getMusicInfoStmt != nil {
		d.getMusicInfoStmt.Close()
	}
	if d.getShowListStmt != nil {
		d.getShowListStmt.Close()
	}
	if d.getCartInfoStmt != nil {
		d.getCartInfoStmt.Close()
	}
	if d.getCutInfoStmt != nil {
		d.getCutInfoStmt.Close()
	}
	if d.getJingleListStmt != nil {
		d.getJingleListStmt.Close()
	}
	if d.getPoolListStmt != nil {
		d.getPoolListStmt.Close()
	}
	if d.getPoolCartsStmt != nil {
		d.getPoolCartsStmt.Close()
	}
	if d.getGroupDefaultTitleStmt != nil {
		d.getGroupDefaultTitleStmt.Close()
	}
	if d.updateCutTitleStmt != nil {
		d.updateCutTitleStmt.Close()
	}
	if d.updateCartTitleMaybeStmt != nil {
		d.updateCartTitleMaybeStmt.Close()
	}
	if d.updateCartUserDefinedStmt != nil {
		d.updateCartUserDefinedStmt.Close()
	}

}

func newDB(configfile string, readonly bool) (d *db, err error) {
	d = new(db)
	if d.conf, err = newConfig(configfile); err != nil {
		return
	}
	d.readonly = readonly
	d.quit = make(chan bool)
	d.done = make(chan bool)
	d.passwordCache = make(map[string]string)
	d.getPasswordChan = make(chan getPasswordRequest, 10)
	d.getGroupOfCartChan = make(chan getGroupOfCartRequest, 10)
	d.getShowInfoChan = make(chan getShowInfoRequest, 10)
	d.checkMusicGroupChan = make(chan checkMusicGroupRequest, 10)
	d.getMusicInfoChan = make(chan getMusicInfoRequest, 10)
	d.getShowListChan = make(chan getShowListRequest, 10)
	d.getShowCartListChan = make(chan getShowCartListRequest, 10)
	d.getJingleListChan = make(chan getJingleListRequest, 10)
	d.getJingleCutListChan = make(chan getJingleCutListRequest, 10)
	d.getPoolListChan = make(chan getPoolListRequest, 10)
	d.getPoolCartListChan = make(chan getPoolCartListRequest, 10)
	d.updateCutCartTitleChan = make(chan updateCutCartTitleRequest, 10)
	d.updateCartUserDefinedChan = make(chan updateCartUserDefinedRequest, 10)

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

	go d.dispatchRequests()
	return
}

func NewDB(configfile string) (*db, error) {
	return newDB(configfile, false)
}

func NewDBReadOnly(configfile string) (*db, error) {
	return newDB(configfile, true)
}