sqlserver.go

package sqlserver

import (
	"database/sql"
	"database/sql/driver"
	"fmt"
	"net/url"
	"strconv"
	"sync"
	"time"

	"corelab.mkcl.org/MKCLOS/coredevelopmentplatform/corepkgv2/loggermdl"
	"corelab.mkcl.org/MKCLOS/coredevelopmentplatform/corepkgv2/sjsonhelpermdl"

	"corelab.mkcl.org/MKCLOS/coredevelopmentplatform/corepkgv2/errormdl"
	_ "github.com/denisenkom/go-mssqldb"
	"github.com/tidwall/gjson"
	"github.com/tidwall/sjson"
)

//SQLServerConfig
type SQLServerConfig struct {
	HostName        string          `json:"hostName"`
	Server          string          `json:"server"`
	Port            int             `json:"port"`
	Username        string          `json:"username"`
	Password        string          `json:"password"`
	Database        string          `json:"database"`
	IsDefault       bool            `json:"isDefault"`
	MaxIdleConns    int             `json:"maxIdleConns" `
	MaxOpenConns    int             `json:"maxOpenConns"`
	ConnMaxLifetime time.Duration   `json:"connMaxLifetime" `
	IsDisabled      bool            `json:"isDisabled" `
	SQLParameters   []SQLParameters `json:"sqlParameters"`
}

type SQLParameters struct {
	Key   string
	Value string
}

//SQLServerDAO
type SQLServerDAO struct {
	HostName string
}

var sqlServerConnections map[string]*sql.DB
var once sync.Once
var defaultHost string
var mutex sync.Mutex

func init() {
	sqlServerConnections = make(map[string]*sql.DB)
}

// InitUsingJSON initializes sqlserver Connections for give JSON data
func InitUsingJSON(configs []SQLServerConfig) error {

	var connectionErr error

	once.Do(func() {
		mutex.Lock()
		defer mutex.Unlock()

		for _, hostDetails := range configs {

			// Build connection string
			// connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
			// 	hostDetails.Server, hostDetails.Username, hostDetails.Password, hostDetails.Port, hostDetails.Database)
			connString := genereateConnectionString(hostDetails)
			var err error
			loggermdl.LogError("connString: ", connString)
			// Create connection pool
			conn, err := sql.Open("mssql", connString)
			if err != nil {
				connectionErr = err
				loggermdl.LogError("error while creating connection:", err)
				return
			}

			conn.SetConnMaxLifetime(hostDetails.ConnMaxLifetime)
			conn.SetMaxIdleConns(hostDetails.MaxIdleConns)
			conn.SetMaxOpenConns(hostDetails.MaxOpenConns)

			err = conn.Ping()
			if err != nil {
				connectionErr = err
				loggermdl.LogError("failed to connect:", err)
				return
			}

			sqlServerConnections[hostDetails.HostName] = conn
			if hostDetails.IsDefault {
				defaultHost = hostDetails.HostName
			}
		}
	})
	return connectionErr
}

// InitConnection - InitConnection
func InitConnection(connectionDetails SQLServerConfig) (*sql.DB, error) {

	// Build connection string
	// connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
	// 	connectionDetails.Server, connectionDetails.Username, connectionDetails.Password, connectionDetails.Port, connectionDetails.Database)
	connString := genereateConnectionString(connectionDetails)
	// Create connection pool
	connection, err := sql.Open("mssql", connString)
	if err != nil {
		loggermdl.LogError("error while creating connection:", err)
		return nil, err
	}

	connection.SetConnMaxLifetime(connectionDetails.ConnMaxLifetime)
	connection.SetMaxIdleConns(connectionDetails.MaxIdleConns)
	connection.SetMaxOpenConns(connectionDetails.MaxOpenConns)

	return connection, nil
}

//makeConnURL prepare the url for connection
func makeConnURL(config SQLServerConfig) *url.URL {
	return &url.URL{
		Scheme: "sqlserver",
		Host:   config.Server + ":" + strconv.Itoa(config.Port),
		User:   url.UserPassword(config.Username, config.Password),
	}
}

//GetSQLServerConnection returns connection by hostname
func GetSQLServerConnection(hostName string) (*sql.DB, error) {

	mutex.Lock()
	defer mutex.Unlock()

	if sqlServerConnections == nil {
		return nil, errormdl.Wrap("SQLSERVER_INIT_NOT_DONE")
	}
	if hostName == "" {
		if connection, ok := sqlServerConnections[defaultHost]; ok {
			return connection, nil
		}
	}
	if connection, ok := sqlServerConnections[hostName]; ok {
		return connection, nil
	}
	return nil, errormdl.Wrap("Connection not found for : " + hostName)
}

//GetSQLServerDAO  returns SQLServer DAO instance with default host
func GetSQLServerDAO() *SQLServerDAO {
	return &SQLServerDAO{
		HostName: defaultHost,
	}
}

//GetSQLServerDAOWithHost returns SQLServer DAO instance with provided host
func GetSQLServerDAOWithHost(hostName string) *SQLServerDAO {
	return &SQLServerDAO{
		HostName: hostName,
	}
}

// SelectQuery - SelectQuery
func (ss *SQLServerDAO) SelectQuery(query string, args ...interface{}) (*gjson.Result, error) {
	connection, connectionError := GetSQLServerConnection(ss.HostName)
	if errormdl.CheckErr(connectionError) != nil {
		loggermdl.LogError("GetSqlServerConnection Err : ", connectionError)
		return nil, errormdl.CheckErr(connectionError)
	}

	// loggermdl.LogSpot(connection)
	pingError := connection.Ping()
	if errormdl.CheckErr(pingError) != nil && pingError != driver.ErrBadConn {
		loggermdl.LogError(pingError)
		return nil, errormdl.CheckErr(pingError)
	}
	rows, queryError := connection.Query(query, args...)
	if errormdl.CheckErr(queryError) != nil {
		loggermdl.LogError(queryError)
		return nil, errormdl.CheckErr(queryError)
	}
	defer rows.Close()
	columns, err := rows.Columns()
	if errormdl.CheckErr2(err) != nil {
		loggermdl.LogError("GetAllData rows.Columns() Err : ", err)
		return nil, errormdl.CheckErr2(err)
	}
	values := make([]interface{}, len(columns))
	valuePtrs := make([]interface{}, len(columns))
	tableData := "[]"
	for rows.Next() {
		for i := 0; i < len(columns); i++ {
			valuePtrs[i] = &values[i]
		}
		rows.Scan(valuePtrs...)
		data, err := sjsonhelpermdl.SetMultiple("", columns, values)
		if errormdl.CheckErr3(err) != nil {
			loggermdl.LogError("GetAllData sjson.Set Err : ", err)
			return nil, errormdl.CheckErr3(err)
		}
		tableData, err = sjson.Set(tableData, "-1", gjson.Parse(data).Value())
		if errormdl.CheckErr3(err) != nil {
			loggermdl.LogError("GetAllData sjson.Set Err : ", err)
			return nil, errormdl.CheckErr3(err)
		}
	}
	resultSet := gjson.Parse(tableData)
	return &resultSet, nil
}

// SelectTxQuery - SelectTxQuery
func SelectTxQuery(query string, tx *sql.Tx, args ...interface{}) (*gjson.Result, error) {
	rows, queryError := tx.Query(query, args...)
	if errormdl.CheckErr(queryError) != nil {
		loggermdl.LogError(queryError)
		return nil, errormdl.CheckErr(queryError)
	}
	defer rows.Close()
	columns, err := rows.Columns()
	if errormdl.CheckErr2(err) != nil {
		loggermdl.LogError("GetAllData rows.Columns() Err : ", err)
		return nil, errormdl.CheckErr2(err)
	}
	values := make([]interface{}, len(columns))
	valuePtrs := make([]interface{}, len(columns))
	tableData := "[]"
	for rows.Next() {
		for i := 0; i < len(columns); i++ {
			valuePtrs[i] = &values[i]
		}
		rows.Scan(valuePtrs...)
		data, err := sjsonhelpermdl.SetMultiple("", columns, values)
		if errormdl.CheckErr3(err) != nil {
			loggermdl.LogError("GetAllData sjson.Set Err : ", err)
			return nil, errormdl.CheckErr3(err)
		}
		tableData, err = sjson.Set(tableData, "-1", gjson.Parse(data).Value())
		if errormdl.CheckErr3(err) != nil {
			loggermdl.LogError("GetAllData sjson.Set Err : ", err)
			return nil, errormdl.CheckErr3(err)
		}
	}
	resultSet := gjson.Parse(tableData)
	return &resultSet, nil
}

//ExecQuery to execute query
func (ss *SQLServerDAO) ExecQuery(query string, args ...interface{}) (string, error) {

	conn, err := GetSQLServerConnection(ss.HostName)
	if err != nil {
		return "", err
	}

	result, err := conn.Exec(query, args...)
	if err != nil {
		return "", err
	}
	_ = result

	//TODO: Get last insertedID

	// var lastInsertedID interface{}
	// conn.QueryRow("SELECT SCOPE_IDENTITY()")

	// last, err := result.LastInsertId()

	return "-1", nil
}

// ExecTxQuery - ExecTxQuery
func ExecTxQuery(query string, tx *sql.Tx, args ...interface{}) (string, error) {

	result, execError := tx.Exec(query, args...)
	if errormdl.CheckErr(execError) != nil {
		loggermdl.LogError(execError)
		return "", errormdl.CheckErr(execError)
	}
	_, affectError := result.RowsAffected()
	if errormdl.CheckErr(affectError) != nil {
		loggermdl.LogError(affectError)
		return "", errormdl.CheckErr(affectError)
	}

	//TODO: get last inserted id
	// ID, err := result.LastInsertId()
	// if errormdl.CheckErr(err) != nil {
	// 	loggermdl.LogError(err)
	// 	return "", errormdl.CheckErr(err)
	// }
	// return strconv.Itoa(int(ID)), nil
	return "-1", nil
}

func genereateConnectionString(config SQLServerConfig) string {
	query := url.Values{}
	// added each sql parameters in url.Values
	query.Add("database", config.Database)
	for _, sqlParam := range config.SQLParameters {
		if _, ok := query[sqlParam.Key]; !ok {
			query.Add(sqlParam.Key, sqlParam.Value)
		}
	}

	u := &url.URL{
		Scheme:   "sqlserver",
		User:     url.UserPassword(config.Username, config.Password),
		Host:     fmt.Sprintf("%s:%d", config.Server, config.Port),
		RawQuery: query.Encode(),
	}

	return u.String()
}