func (se *SQLSettlementEngine) setupSettlementTables() (err error) {
	openString := fmt.Sprintf("%s:%s@%s(%s)/", se.dbUsername, se.dbPassword, se.dbAddr.Network(), se.dbAddr.String())
	var rootHandler *sql.DB
	if rootHandler, err = sql.Open("mysql", openString); err != nil {
		err = fmt.Errorf("Error opening database for setup settlement tables: %s", err)
		return
	}
	defer rootHandler.Close()
	if err = rootHandler.Ping(); err != nil {
		err = fmt.Errorf("Could not ping the database, is it running: %s", err)
		return
	}
	var tx *sql.Tx
	if tx, err = rootHandler.Begin(); err != nil {
		err = fmt.Errorf("Error when beginning transaction for setup settlement tables: %s", err)
		return
	}
	defer func() {
		if err != nil {
			tx.Rollback()
			err = fmt.Errorf("Error while setting up settlement tables: \n%s", err)
			return
		}
		err = tx.Commit()
	}()
	if _, err = tx.Exec("CREATE SCHEMA IF NOT EXISTS " + se.balanceSchema + ";"); err != nil {
		err = fmt.Errorf("Error creating schema for setup settlement tables: %s", err)
		return
	}
	if _, err = tx.Exec("USE " + se.balanceSchema + ";"); err != nil {
		err = fmt.Errorf("Could not use %s schema: %s", se.balanceSchema, err)
		return
	}
	createTableQuery := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (%s);", se.coin.Name, settlementEngineSchema)
	if _, err = tx.Exec(createTableQuery); err != nil {
		err = fmt.Errorf("Error creating settlement table: %s", err)
		return
	}
	return
}
setupSettlementTables sets up the tables needed for the auction orderbook.
This assumes the schema name is set
package cxdbsql
import (
	"database/sql"
	"fmt"
	"net"
	_ "github.com/go-sql-driver/mysql"
	"github.com/mit-dci/lit/coinparam"
	"github.com/mit-dci/opencx/logging"
	"github.com/mit-dci/opencx/match"
)
type SQLSettlementEngine struct {
	DBHandler *sql.DB
	dbUsername string
	dbPassword string
	dbAddr net.Addr
	balanceSchema string
	coin *coinparam.Params
}
const (
	settlementEngineSchema = "pubkey VARBINARY(66), balance BIGINT(64), PRIMARY KEY (pubkey)"
)
func CreateSettlementEngine(coin *coinparam.Params) (engine match.SettlementEngine, err error) {
	conf := new(dbsqlConfig)
	*conf = *defaultConf
	dbConfigSetup(conf)
	var addr net.Addr
	if addr, err = net.ResolveTCPAddr("tcp", net.JoinHostPort(conf.DBHost, fmt.Sprintf("%d", conf.DBPort))); err != nil {
		err = fmt.Errorf("Couldn't resolve db address for CreateSQLSettlementEngine: %s", err)
		return
	}
	se := &SQLSettlementEngine{
		dbUsername:    conf.DBUsername,
		dbPassword:    conf.DBPassword,
		balanceSchema: conf.BalanceSchemaName,
		dbAddr:        addr,
		coin:          coin,
	}
	if err = se.setupSettlementTables(); err != nil {
		err = fmt.Errorf("Error setting up settlement engine tables while creating engine: %s", err)
		return
	}
	openString := fmt.Sprintf("%s:%s@%s(%s)/", se.dbUsername, se.dbPassword, se.dbAddr.Network(), se.dbAddr.String())
	if se.DBHandler, err = sql.Open("mysql", openString); err != nil {
		err = fmt.Errorf("Error opening database for CreateSQLSettlementEngine: %s", err)
		return
	}
	if err = se.DBHandler.Ping(); err != nil {
		err = fmt.Errorf("Could not ping the database, is it running: %s", err)
		return
	}
	engine = se
	return
}
func (se *SQLSettlementEngine) ApplySettlementExecution(setExec *match.SettlementExecution) (setRes *match.SettlementResult, err error) {
	var tx *sql.Tx
	if tx, err = se.DBHandler.Begin(); err != nil {
		err = fmt.Errorf("Error beginning transaction while applying settlement exec: \n%s", err)
		return
	}
	defer func() {
		if err != nil {
			tx.Rollback()
			err = fmt.Errorf("Error while applying settlement exec: \n%s", err)
			return
		}
		err = tx.Commit()
	}()
	if _, err = tx.Exec("USE " + se.balanceSchema + ";"); err != nil {
		return
	}
	var rows *sql.Rows
	curBalQuery := fmt.Sprintf("SELECT balance FROM %s WHERE pubkey='%x';", se.coin.Name, setExec.Pubkey)
	if rows, err = tx.Query(curBalQuery); err != nil {
		err = fmt.Errorf("Error querying for balance while applying settlement exec: %s", err)
		return
	}
	var curBal uint64
	if rows.Next() {
		if err = rows.Scan(&curBal); err != nil {
			err = fmt.Errorf("Error scanning when applying settlement exec: %s", err)
			return
		}
	}
	if err = rows.Close(); err != nil {
		err = fmt.Errorf("Error closing rows for ApplySettlementExecution: %s", err)
		return
	}
	var newBal uint64
	if setExec.Type == match.Debit {
		newBal = curBal + setExec.Amount
	} else if setExec.Type == match.Credit {
		newBal = curBal - setExec.Amount
	}
	newBalQuery := fmt.Sprintf("INSERT INTO %s (balance, pubkey) VALUES (%d, '%x') ON DUPLICATE KEY UPDATE  balance='%[2]d';", se.coin.Name, newBal, setExec.Pubkey)
	if _, err = tx.Exec(newBalQuery); err != nil {
		err = fmt.Errorf("Error applying settlement exec new bal query: %s", err)
		return
	}
	setRes = &match.SettlementResult{
		NewBal:         newBal,
		SuccessfulExec: setExec,
	}
	return
}
func (se *SQLSettlementEngine) CheckValid(setExec *match.SettlementExecution) (valid bool, err error) {
	if setExec.Type == match.Debit {
		valid = true
		return
	}
	var tx *sql.Tx
	if tx, err = se.DBHandler.Begin(); err != nil {
		err = fmt.Errorf("Error beginning transaction while checking settlement exec: \n%s", err)
		return
	}
	defer func() {
		if err != nil {
			tx.Rollback()
			err = fmt.Errorf("Error while checking settlement exec: \n%s", err)
			return
		}
		err = tx.Commit()
	}()
	if _, err = tx.Exec("USE " + se.balanceSchema + ";"); err != nil {
		err = fmt.Errorf("Error using balance schema for CheckValid: %s", err)
		return
	}
	var row *sql.Row
	curBalQuery := fmt.Sprintf("SELECT balance FROM %s WHERE pubkey='%x';", setExec.Asset, setExec.Pubkey)
	row = tx.QueryRow(curBalQuery)
	var curBal uint64
	if err = row.Scan(&curBal); err != nil {
		err = fmt.Errorf("Error scanning when checking settlement exec: %s", err)
		return
	}
	logging.Infof("User with %d %s trying to complete action costing %d %[2]s.", curBal, se.coin.Name, setExec.Amount)
	valid = setExec.Amount < curBal
	return
}