From cd4a8633e67e4c02e3b44e35fd9f517b8c2a87b2 Mon Sep 17 00:00:00 2001 From: Christian Pointner Date: Fri, 8 Jan 2016 00:47:29 +0100 Subject: added rddb package diff --git a/README.md b/README.md index 7ddd5b6..03691ce 100644 --- a/README.md +++ b/README.md @@ -1,8 +1,8 @@ -# The Radio Helsinki Rivendell Go Package +# The Radio Helsinki Rivendell Go Packages -This golang package contains a set of tools and bindings to the rivendell -database. It can be used for various tasks in the Radio Helsinki Deployment -of Rivendell. +This repository contains a set of golang packages with bindings to the Rivendell +database, tools and utils around Rivendell. They can be used for various tasks in +the Radio Helsinki Deployment of Rivendell. ## Status @@ -10,8 +10,7 @@ This is currently on development. Any api calls are subject to change! ## API -[![GoDoc](https://godoc.org/helsinki.at/rhrd-go?status.svg)](https://godoc.org/helsinki.at/rhrd-go) -[Source code.](https://git.helsinki.at/?p=rhrd-go.git;a=summary) +tba... ## Licence 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 +// +// 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 ( + "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 +// +// 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" + "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, ¯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 *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 +} diff --git a/rhrd.go b/rhrd.go deleted file mode 100644 index 08af2d5..0000000 --- a/rhrd.go +++ /dev/null @@ -1,44 +0,0 @@ -// -// 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 rhrd-go contains a set of tools and bindings to the rivendell -// database. It can be used for various tasks in the Radio Helsinki Deployment -// of Rivendell. -package rhrd - -import ( - "io/ioutil" - "log" - "os" -) - -var ( - rhrdl = log.New(ioutil.Discard, "[rhrd]\t", log.LstdFlags) -) - -func init() { - if _, exists := os.LookupEnv("RHRD_DEBUG"); exists { - rhrdl.SetOutput(os.Stderr) - } -} -- cgit v0.10.2