golang/mysqltojson/sql/mysql.go

package sql

import (
	"database/sql"
	"errors"
	"strings"

	// _ "github.com/go-sql-driver/mysql"
	_ "github.com/go-sql-driver/mysql"
)

//SQL ...
type SQL struct {
	db  *sql.DB
	err error
}

/* NewSQL
@parameter server string (ex: myserver.com:3306)
@parameter login string
@parameter mdp string
@parameter base string
*/
func NewMySQL(server, login, mdp, base string) *SQL {
	db, err := Connect(server, login, mdp, base)
	if err != nil {
		panic(err.Error())
	}
	s := new(SQL)
	s.db = db
	s.err = err
	return s
}

// Connect (Use NewMySQL for the connection)
func Connect(server, login, mdp, base string) (*sql.DB, error) {
	db, err := sql.Open("mysql", login+":"+mdp+"@tcp("+server+")/"+base)
	return db, err
}

/* Insert
@parameter table string
@parameter column []string (optional is nil (INSERT INTO table VALUES ( , , ,)))
@parameter values []string
@return error
*/
func (s *SQL) Insert(table string, columns []string, values [][]string) error {
	if len(table) <= 0 || values == nil {
		return errors.New("Incorrect parameters.")
	}
	if columns != nil {
		for _, v := range values {
			if len(v) != len(columns) {
				return errors.New("It does not have the number of columns and values.")
			}
		}
	}
	if values == nil {
		return errors.New("Values is null.")
	}
	cols := "("
	if columns != nil {
		for i, column := range columns {
			if i != 0 {
				cols += ", "
			}
			cols += column
		}
		cols += ")"
	}
	vals := ""
	for j, value := range values {
		if j != 0 {
			vals += ", "
		}
		vals += "("
		for i := 0; i < len(value); i++ {
			if i != 0 {
				vals += ", "
			}
			if value[i] != "" {
				vals += "'" + value[i] + "'"
			} else {
				vals += "null"
			}
		}
		vals += ")"
	}
	sql := "INSERT INTO " + table
	if columns != nil {
		sql += " " + cols + " VALUES " + vals + ";"
	} else {
		sql += " VALUES " + vals + ";"
	}
	insert, err := s.db.Query(sql)
	if err != nil || insert.Err() != nil {
		return errors.New("error syntax : " + sql)
	}
	defer insert.Close()
	return nil
}

/* DeleteAND
@parameter table string
@parameter wheres [][]string (Each line is combined with AND)
@return error
*/
func (s *SQL) DeleteAND(table string, wheres [][]string) error {
	if len(table) <= 0 || wheres == nil {
		return errors.New("Incorrect parameter")
	}
	lines := ""
	flag := 0
	for _, where := range wheres {
		if flag != 0 {
			lines += " AND "
		}
		lines += where[0] + " = '" + where[1] + "'"
		flag = 1
	}
	sql := "DELETE FROM " + table + " WHERE " + lines + ";"
	delete, err := s.db.Query(sql)
	if err != nil || delete.Err() != nil {
		return errors.New("error syntax : " + sql)
	}
	defer delete.Close()
	return nil
}

/* UpdateAND
@parameter table string
@parameter sets [][]string
@parameter wheres [][]string (Each line is combined with AND)
@return error
*/
func (s *SQL) UpdateAND(table string, sets [][]string, wheres [][]string) error {
	if len(table) <= 0 || sets == nil || wheres == nil {
		return errors.New("parameter incorrect")
	}
	conds := ""
	flag := 0
	for _, where := range wheres {
		if flag != 0 {
			conds += " AND "
		}
		conds += where[0] + " = '" + where[1] + "'"
		flag = 1
	}
	vls := ""
	flagA := 0
	for _, set := range sets {
		if flagA != 0 {
			vls += ", "
		}
		vls += set[0] + " = '" + set[1] + "'"
		flagA = 1
	}
	sql := "UPDATE " + table + " SET " + vls + " WHERE " + conds + ";"
	update, err := s.db.Query(sql)
	if err != nil || update.Err() != nil {
		return errors.New("error syntax : " + sql)
	}
	defer update.Close()
	return nil
}

/*
SelectAND
@parameter table string
@parameter colvals [][]string (Each line is combined with AND) (optional is nil (SELECT * FROM table;))
@return [column][row]string, error
*/
func (s *SQL) SelectAND(table string, colvals [][]string) ([][]string, error) {
	if len(table) <= 0 {
		return nil, errors.New("parameter incorrect")
	}
	var rsltRow [][]string
	var requete string = "SELECT * FROM " + table
	if colvals != nil {
		requete += " WHERE "
		for i, colval := range colvals {
			if i != 0 {
				requete += " AND "
			}
			if strings.IndexRune(colval[1], '*') > 0 {
				requete += colval[0] + " LIKE '" + colval[1] + "'"
			} else {
				requete += colval[0] + " = '" + colval[1] + "'"
			}
		}
	}
	requete += ";"
	rcolumns, _ := s.db.Query(requete)
	columns, _ := rcolumns.ColumnTypes()
	for _, column := range columns {
		sql := strings.Replace(requete, "*", column.Name(), 1)
		rows, errQuery := s.db.Query(sql)
		if errQuery != nil {
			panic("error syntax : " + sql)
		}
		var bytes []byte
		var rsltCol []string
		rsltCol = append(rsltCol, column.Name())
		for rows.Next() {
			err := rows.Scan(&bytes)
			if err != nil {
				panic(err.Error())
			}
			rsltCol = append(rsltCol, string(bytes[:]))
		}
		rsltRow = append(rsltRow, rsltCol)
		defer rows.Close()
	}
	return rsltRow, nil
}

/*
Close
*/
func (s *SQL) Close() {
	defer s.db.Close()
}