databaseutils/databaseutils.go

package databaseutils

import (
  "database/sql"
  _ "github.com/go-sql-driver/mysql"
  "errors"
  "fmt"
)

var (
  DBUtil *DBUtils // DB handle for global access
)

const ( // Error constants
  ERR_EMPTY_RESULT = "ERR_EMPTY_RESULT"
  ERR_QUERY_FAILED =  "ERR_QUERY_FAILED"
  ERR_NO_COLUMNS = "ERR_NO_COLUMNS"
  ERR_FAILED_ROW_SCAN = "ERR_FAILED_ROW_SCAN"
)

type DBUtils struct {
  User string
  Password string
  Host string
  Database string
  Handle *sql.DB
}

func (dbUtil *DBUtils) Connect() { // setup handle for db
  var err error
  dbUtil.Handle, err = sql.Open("mysql", dbUtil.User + ":" + dbUtil.Password + "@tcp(" + dbUtil.Host + ")/" + dbUtil.Database) // open handle
  if err != nil {
    panic(err.Error()) // TODO
  }

  err = dbUtil.Handle.Ping() // try to connect
  if err != nil {
    panic(err.Error()) // TODO
  }
}

func (dbUtil DBUtils) Close() {
  dbUtil.Handle.Close()
}

func (dbUtil DBUtils) GetString(what string, from string, where string, wherevalue string) string {
  var buf string

  rows, err := dbUtil.Handle.Query("SELECT " + what + " FROM " + from + " WHERE " + where + " = '" + wherevalue + "'") // TODO Parse?
  if err != nil {
  	panic(err.Error()) // TODO
  }
  defer rows.Close()

  for rows.Next() {
  	err := rows.Scan(&buf)
  	if err != nil {
  		panic(err.Error()) // TODO
  	}
  }

  err = rows.Err()
  if err != nil {
  	panic(err.Error()) // TODO
  }

  return buf
}

// GetRow(what, from, where, wherevalue)
func (dbUtil DBUtils) GetRow(options ...string) ([]string, error) {
  var err error
  var rows *sql.Rows // TODO fix this weird bug

  if len(options) > 3 { // with or without where
    rows, err = dbUtil.Handle.Query("SELECT " + options[0] + " FROM " + options[1] + " WHERE " + options[2] + " = \"" + options[3] + "\"") // TODO Parse?
  } else {
    rows, err = dbUtil.Handle.Query("SELECT " + options[0] + " FROM " + options[1]) // TODO Parse?
  }
  defer rows.Close()

  if err != nil {
    panic(err.Error()) // TODO
  }

  cols, err := rows.Columns()
   if err != nil {
     return []string{""}, errors.New(ERR_NO_COLUMNS)
   }

   dest := make([]interface{}, len(cols)) // interface for scanning
   result := make([]sql.NullString, len(cols)) // NullString to check if empty
   results := make([]string, len(cols)) // final return-result

   for i, _ := range result {
     dest[i] = &result[i] // Put pointers to each string in the interface slice
   }

  for rows.Next() {
    err = rows.Scan(dest...) // copy results in dest
    if err != nil {
        return []string{""}, errors.New(ERR_FAILED_ROW_SCAN)
    }
  }

  errcounter := 0 // count invalid results

  for i, _ := range result {
    if result[i].Valid {
      results[i] = result[i].String // store valid results in return-slice
    } else {
      errcounter++
    }
  }

  if errcounter == len(cols) { // if nothings valid it's empty
    return []string{""}, errors.New(ERR_EMPTY_RESULT)
  }

  return results, nil
}

// GetRows(what, from, where, wherevalue)
func (dbUtil DBUtils) GetRows(options ...string) ([][]string, error) { // TODO umschreiben
  var err error
  var rows *sql.Rows // TODO fix this weird bug

  if len(options) > 3 { // with or without where
    rows, err = dbUtil.Handle.Query("SELECT " + options[0] + " FROM " + options[1] + " WHERE " + options[2] + " = \"" + options[3] + "\"") // TODO Parse?
  } else {
    rows, err = dbUtil.Handle.Query("SELECT " + options[0] + " FROM " + options[1]) // TODO Parse?
  }
  defer rows.Close()

  if err != nil {
    panic(err.Error()) // TODO
  }

  var columns []string
  columns, err = rows.Columns()
  if err != nil {
    fmt.Println(err.Error())
  }

  colNum := len(columns)
  all := [][]string{}

  for rows.Next() {
    vh := make([]string, len(columns))

    cols := make([]interface{}, colNum)
    for i := 0; i < colNum; i++ {
        cols[i] = &vh[i]
    }

    err = rows.Scan(cols...)
    if err != nil {
      fmt.Println(err.Error())
    }

    all = append(all, vh)
  }

  return all, err
}

// UpdateRow(what, where, whereval, []key=>val udpate vals)
func (dbUtil DBUtils) UpdateRow(what string, where string, whereVal string, colsVals [][]string) error {
  query := "UPDATE `" + what + "` " // begin sql query

  for i, x := range colsVals { // append keys/values to update
    fmt.Printf("%#v", x)
    if (i == 0) && (i == (len(colsVals) - 1)) { // if only one thing to update
      query += "SET `" + string(colsVals[i][0]) + "` = '" + colsVals[i][1] + "' "
    } else if (i == 0) { // first, but more coming
      query += "SET `" + colsVals[i][0] + "` = '" + colsVals[i][1] + "', "
    } else if i == (len(colsVals) - 1) && i != 0 { // last one, no ',' at end
      query += "`" + string(colsVals[i][0]) + "` = '" + colsVals[i][1] + "' "
    } else if i != 0 { // everything else (in the middle of others)
      query += "`" + colsVals[i][0] + "` = '" + colsVals[i][1] + "', "
    }
  }

  query += "WHERE `" + where + "` = '" + whereVal + "';" // finish query

  _, err := dbUtil.Handle.Query(query) // execute query
  if err != nil { // check if everything went well
  	fmt.Printf("QUERY AYY: %#v\n", err.Error())
    return errors.New("Query Failed") // TODO logging system
  }

  return nil
}

// InsertRow(what, cols)
func (dbUtil DBUtils) InsertRow(what string, colsVals [][]string) error {
  query := "INSERT INTO `" + what + "` (" // begin sql query
  vals := ") VALUES ("

  for i, x := range colsVals { // append keys/values to update
    fmt.Printf("%#v", x)
    if (i == 0) && (i == (len(colsVals) - 1)) { // if only one thing to update
      query += "`" + string(colsVals[i][0]) + "`"
      vals +="'" + colsVals[i][1] + "'"
    } else if (i == 0) { // first, but more coming
      query += "`" + colsVals[i][0] + "`,"
      vals += "'" + colsVals[i][1] + "', "
    } else if i == (len(colsVals) - 1) && i != 0 { // last one, no ',' at end
      query += "`" + string(colsVals[i][0]) + "`"
      vals += "'" + colsVals[i][1] + "'"
    } else if i != 0 { // everything else (in the middle of others)
      query += "`" + colsVals[i][0] + "`, "
      vals += "'" + colsVals[i][1] + "', "
    }
  }

  query += vals + ");"

  fmt.Println(query)

  _, err := dbUtil.Handle.Query(query) // execute query
  if err != nil { // check if everything went well
  	fmt.Printf("QUERY AYY: %#v\n", err.Error())
    return errors.New("Query Failed") // TODO logging system
  }

  return err
}

// GetRowsDoubleCond("users", "tokens", "`token-id` = `tokens`.`id`", "`tokens`.`value` = 'SefxSlkejpuJOjIN'")
func (dbUtil DBUtils) GetRowsDoubleCond(fromA string, fromB string, whereA string, whereB string) ([][]string, error) { // TODO make this method great again
  query := "SELECT * FROM `" + fromA + "`, `" + fromB + "` WHERE " + whereA + " AND " + whereB + ";"

  rows, err := dbUtil.Handle.Query(query)
  if err != nil {
    return [][]string{}, err
  }

  var columns []string
  columns, err = rows.Columns()
  if err != nil {
    return [][]string{}, err
  }

  colNum := len(columns)
  all := [][]string{}

  for rows.Next() {
    vh := make([]string, len(columns))

    cols := make([]interface{}, colNum)
    for i := 0; i < colNum; i++ {
        cols[i] = &vh[i]
    }

    err = rows.Scan(cols...)
    if err != nil {
      return [][]string{}, err
    }

    all = append(all, vh)
  }

  if len(all) > 0 {
    return all, nil
  }

  return [][]string{}, errors.New(ERR_EMPTY_RESULT)
}