// // rhrd-go // // The Radio Helsinki Rivendell Go Package // // // Copyright (C) 2016 Christian Pointner // // 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 . // package rddb import ( "database/sql" "fmt" "regexp" "strconv" "strings" "time" "github.com/ziutek/mymysql/godrv" ) var ( showMacroRe = regexp.MustCompile(`^LL 1 ([^ ]+) 0\!$`) mysqlTableNameRe = regexp.MustCompile(`^[_0-9a-zA-Z-]+$`) ) const ( DB_VERSION = 245 dropboxPseudoStation = "import-dropbox" ) 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 log string lowCart int highCart int } type getShowListResult struct { shows []ShowListEntry err error } type getShowListRequest struct { username string response chan<- getShowListResult } type CutListEntry struct { Number uint Evergreen bool Description string Duration time.Duration // Imported time.Time NumPlayed uint // LastPlayed time.Time } type CartListEntry struct { Number uint Exists bool Title string Cuts []CutListEntry } type getShowCartListResult struct { carts []CartListEntry err error } type getShowCartListRequest struct { show ShowListEntry response chan<- getShowCartListResult } 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 getShowListChan chan getShowListRequest getShowListStmt *sql.Stmt getShowCartListChan chan getShowCartListRequest getCartInfoStmt *sql.Stmt getCutInfoStmt *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 } if db.getShowListStmt, err = db.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 db.getCartInfoStmt, err = db.dbh.Prepare("select TITLE from CART where TYPE=1 and NUMBER = ?"); err != nil { return } // if db.getCutInfoStmt, err = db.dbh.Prepare("select CUT_NAME, EVERGREEN, DESCRIPTION, LENGTH, ORIGIN_DATETIME, PLAY_COUNTER, LAST_PLAY_DATETIME from CUTS where CART_NUMBER = ?"); err != nil { if db.getCutInfoStmt, err = db.dbh.Prepare("select CUT_NAME, EVERGREEN, DESCRIPTION, LENGTH, PLAY_COUNTER from CUTS where CART_NUMBER = ?"); 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, ¯os, &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) getShowList(username string) (result getShowListResult) { var rows *sql.Rows if rows, result.err = db.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, ¯os, &show.ID, ¶ms, &show.lowCart, &show.highCart); result.err != nil { return } show.log = showMacroRe.FindStringSubmatch(macros)[1] result.shows = append(result.shows, show) } result.err = rows.Err() return } func (db *DB) getCutInfo(cart uint) (cuts []CutListEntry, err error) { var rows *sql.Rows if rows, err = db.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 { if err = rows.Scan(&cutName, &evergreen, &cut.Description, &length, &cut.NumPlayed); 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 (db *DB) getCartInfo(cart uint) (c CartListEntry, err error) { c = CartListEntry{} c.Number = cart c.Exists = true err = db.getCartInfoStmt.QueryRow(cart).Scan(&c.Title) if err != nil { if err == sql.ErrNoRows { err = nil c.Exists = false } return } if c.Cuts, err = db.getCutInfo(cart); err != nil { return } return } func (db *DB) getShowCartList(show ShowListEntry) (result getShowCartListResult) { var carts []uint if carts, result.err = db.getShowCarts(show.log, show.lowCart, show.highCart); result.err != nil { return } for _, cart := range carts { var c CartListEntry if c, result.err = db.getCartInfo(cart); result.err != nil { return } result.carts = append(result.carts, c) } 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) case req := <-db.getShowListChan: req.response <- db.getShowList(req.username) case req := <-db.getShowCartListChan: req.response <- db.getShowCartList(req.show) } } } // ********************************************************* // Public Interface type DBChan struct { getPasswordChan chan<- getPasswordRequest getGroupOfCartChan chan<- getGroupOfCartRequest getShowInfoChan chan<- getShowInfoRequest checkMusicGroupChan chan<- checkMusicGroupRequest getMusicInfoChan chan<- getMusicInfoRequest getShowListChan chan<- getShowListRequest getShowCartListChan chan<- getShowCartListRequest } 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 *DBChan) GetShowList(username string) ([]ShowListEntry, error) { resCh := make(chan getShowListResult) req := getShowListRequest{} req.username = username req.response = resCh db.getShowListChan <- req res := <-resCh if res.err != nil { return nil, res.err } return res.shows, nil } func (db *DBChan) GetShowCartList(show ShowListEntry) ([]CartListEntry, error) { resCh := make(chan getShowCartListResult) req := getShowCartListRequest{} req.show = show req.response = resCh db.getShowCartListChan <- req res := <-resCh if res.err != nil { return nil, res.err } return res.carts, 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 ch.getShowListChan = db.getShowListChan ch.getShowCartListChan = db.getShowCartListChan 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() } if db.getShowListStmt != nil { db.getShowListStmt.Close() } if db.getCartInfoStmt != nil { db.getCartInfoStmt.Close() } if db.getCutInfoStmt != nil { db.getCutInfoStmt.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) db.getShowListChan = make(chan getShowListRequest, 10) db.getShowCartListChan = make(chan getShowCartListRequest, 10) if err = db.init(); err != nil { return } go db.dispatchRequests() return }