sqle

README

sqle

SQLE is a general purpose helper package for sql.DB that simplifies and reduces error checking for SQL based operations.

Installation

To install SQLE, simly run:

$ go get github.com/harwoeck/sqle

Documentation

Overview

Package SQLE is a general purpose helper package for sql.DB that simplifies and reduces error checking for SQL based operations.

In order to initialize a new `*sqle.Sqle` instance you need an working `*sql.DB` connection. Pass it to the `New` function

var db *sql.DB
db := myDBInitAndOpen()

var s *sqle.Sqle
s = sqle.New(db)

If you have your `*sqle.Sqle` instance you can implement functions similar to this

var (
	s *sqle.Sqle
)

// InsertMessage inserts the passed string into the msg table and
// returns the auto-incremented id (foreign-key in other tables).
func InsertMessage(msg string) (id int64, err error) {
	return s.ExecID(queryInsertMsg, msg)
}

// SelectMessage receives the message associated to the passed
// id.
func SelectMessage(id int64) (msg string, exists bool, err error) {
	exists, err = s.SelectExists("SELECT message FROM msg WHERE id=?", []interface{}{id}, []interface{}{&msg})
	return
}

// ExistsMessage checks wheter the id (and therefore) it's
// associated value exist or not.
func ExistsMessage(id int64) (exists bool, err error) {
	return s.Exists("SELECT id FROM msg WHERE id=?", id)
}

// SelectMessageExists checks whether the id (and therefore) it's
// associated value exists or not and if so also returns the value.
// If the id doesn't exists the msg pointer will be nil.
func SelectMessageExists(id int64) (msg *string, err error) {
	exists, err := s.SelectExists("SELECT message FROM msg WHERE id=?", []interface{}{id}, []interface{}{msg})
	if err != nil || !exists {
		return nil, err
	}
	return msg, err
}

More sophisticated usages of `SelectRange` could look like the following (these examples are copied from the vikebot.com database code)

// JoinedUsersCtx returns the `userID`s of all users which joined the round
// specified through the roundID.
func JoinedUsersCtx(roundID int, ctx *zap.Logger) (joined []int, success bool) {
	users := []int{}

	var userID int
	err := s.SelectRange("SELECT user_id FROM roundentry WHERE round_id=?",
		[]interface{}{roundID},
		[]interface{}{&userID},
		func() {
			users = append(users, userID)
		})
	if err != nil {
		ctx.Error("vbdb.JoinedUsersCtx",
			zap.Int("roundID", roundID),
			zap.Error(err))
		return nil, false
	}

	return users, true
}

// ActiveRoundsCtx loads all rounds which have not status `vbcore.RoundStatusFinished`.
func ActiveRoundsCtx(ctx *zap.Logger) (rounds []vbcore.Round, success bool) {
	rounds = []vbcore.Round{}

	var id, joined, min, max, roundstatus int
	var name, wallpaper string
	var starttime mysql.NullTime
	err := sqlSelectRange(`
		SELECT round.id,
			round.name,
			round.wallpaper,
			(SELECT COUNT(id) FROM roundentry WHERE roundentry.round_id = round.id) AS "joined",
			roundsize.min,
			roundsize.max,
			round.starttime,
			round.roundstatus_id
		FROM round, roundsize
		WHERE round.roundstatus_id IN (?, ?, ?)
		ORDER BY round.id ASC`,
		[]interface{}{vbcore.RoundStatusOpen, vbcore.RoundStatusClosed, vbcore.RoundStatusRunning},
		[]interface{}{&id, &name, &wallpaper, &joined, &min, &max, &starttime, &roundstatus},
		func() {
			r := vbcore.Round{
				ID:          id,
				Name:        name,
				Wallpaper:   wallpaper,
				Joined:      joined,
				Min:         min,
				Max:         max,
				Starttime:   starttime.Time,
				RoundStatus: roundstatus,
			}
			rounds = append(rounds, r)
		})
	if err != nil {
		ctx.Error("vbdb.ActiveRoundsCtx", zap.Error(err))
		return nil, false
	}

	return rounds, true
}

Index

• type Sqle
  • func New(db *sql.DB) *Sqle
  • func (s Sqle) Exec(query string, args ...interface{}) error
  • func (s Sqle) ExecBatch(queries []string) error
  • func (s Sqle) ExecID(query string, args ...interface{}) (lastInsertID int64, err error)
  • func (s Sqle) ExecTx(tx *sql.Tx, query string, args ...interface{}) error
  • func (s Sqle) ExecTxID(tx *sql.Tx, query string, args ...interface{}) (lastInsertID int64, err error)
  • func (s Sqle) MysqlExists(query string, args ...interface{}) (exists bool, err error)
  • func (s Sqle) Select(query string, args []interface{}, dest []interface{}) error
  • func (s Sqle) SelectExists(query string, args []interface{}, dest []interface{}) (exists bool, err error)
  • func (s Sqle) SelectExistsTx(tx *sql.Tx, query string, args []interface{}, dest []interface{}) (exists bool, err error)
  • func (s Sqle) SelectRange(query string, args []interface{}, dest []interface{}, handleRow func()) error
  • func (s Sqle) SelectRangeTx(tx *sql.Tx, query string, args []interface{}, dest []interface{}, ...) error
  • func (s Sqle) UnsafeMysqlCount(table, column string) (count int, err error)

Types

type Sqle

type Sqle struct {
	// contains filtered or unexported fields
}

Sqle provides methods to simply a few specific database operations.

func New

func New(db *sql.DB) *Sqle

New intializes a new `sqle.(*Sqle)` instance and returns it.

func (Sqle) Exec

func (s Sqle) Exec(query string, args ...interface{}) error

Exec will execute the query with it's argument against the database and returns any occurring errors.

func (Sqle) ExecBatch

func (s Sqle) ExecBatch(queries []string) error

ExecBatch runs 'Exec` without any arguments for every entry in the slice parameter.

This method IS NOT SAFE AGAINST SQL-INJECTION. Use it only with trusted input!

func (Sqle) ExecID

func (s Sqle) ExecID(query string, args ...interface{}) (lastInsertID int64, err error)

ExecID executes the query with it's arguments against the database and returns any occurring errors.

If the executed query is an `INSERT` operation with an auto-generated ID (for example chosen by the database because of a `AUTO-INCREMENT` flag), then `lastInsertID` will contain the generated ID value.

func (Sqle) ExecTx

func (s Sqle) ExecTx(tx *sql.Tx, query string, args ...interface{}) error

ExecTx is the same as `Exec` but uses the passed transaction `tx` to execute the operations.

func (Sqle) ExecTxID

func (s Sqle) ExecTxID(tx *sql.Tx, query string, args ...interface{}) (lastInsertID int64, err error)

ExecTxID is the same as `ExecID` but uses the passed transaction `tx` to execute the operations.

func (Sqle) MysqlExists

func (s Sqle) MysqlExists(query string, args ...interface{}) (exists bool, err error)

MysqlExists checks whether the statement defined by the `query` and `args` would return a result.

As the method's name already clarifies, a Mysql-specific feature is used. Therefore don't use this method against other databases.

func (Sqle) Select

func (s Sqle) Select(query string, args []interface{}, dest []interface{}) error

Select selects the `query` string from the database. The `args` interface slice should contain all primitive value arguments. The `dest` interface slice should contain a collection of pointer to primitive types. The results of the query will be saved into these pointers.

func (Sqle) SelectExists

func (s Sqle) SelectExists(query string, args []interface{}, dest []interface{}) (exists bool, err error)

SelectExists is the same as `Select`, but additionally returns an boolean value, whether or not the row existed.

func (Sqle) SelectExistsTx

func (s Sqle) SelectExistsTx(tx *sql.Tx, query string, args []interface{}, dest []interface{}) (exists bool, err error)

SelectExistsTx is the same as `SelectExists` but uses the passed transaction `tx` to execute the operations.

func (Sqle) SelectRange

func (s Sqle) SelectRange(query string, args []interface{}, dest []interface{}, handleRow func()) error

SelectRange selects a range of results from the database, defined by the `query` and it's arguments. The `args` interface slice should contain all primitive value arguments. The `dest` interface slice should contain a collection of pointer to primitive types. The results of the query will be saved into these pointers. As soon as one row has been loaded the `handleRow` callback will be called. It is the package caller's responsibility to copy the values from the `dest` collection into another data structure. After returning the `handleRow` function the values of `dest` will be overwritten with the next row's values.

func (Sqle) SelectRangeTx

func (s Sqle) SelectRangeTx(tx *sql.Tx, query string, args []interface{}, dest []interface{}, handleRow func()) error

SelectRangeTx is the same as `SelectRange` but uses the passed transaction `tx` to execute the operations.

func (Sqle) UnsafeMysqlCount

func (s Sqle) UnsafeMysqlCount(table, column string) (count int, err error)

UnsafeMysqlCount counts the rows for a single column in a specified table.

This method IS NOT SAFE AGAINST SQL-INJECTION. Use it only with trusted input!

As the method's name already clarifies, a Mysql-specific feature is used. Therefore don't use this method against other databases.