algernon/lua/mysql/mysql.go

package mysql

import (
	"database/sql"
	"fmt"
	"github.com/gogf/gf/v2/frame/g"
	"github.com/gogf/gf/v2/os/gctx"
	"strings"
	"sync"

	log "github.com/sirupsen/logrus"
	"github.com/xyproto/algernon/lua/convert"
	lua "github.com/xyproto/gopher-lua"

	// Using the PostgreSQL database engine
	_ "github.com/go-sql-driver/mysql"
	_ "github.com/lib/pq"
)

const (
	defaultQuery            = "SELECT version()"
	defaultConnectionString = "host=localhost port=5432 user=postgres dbname=test sslmode=disable"
)

type LValueWrapper struct {
	LValue lua.LValue
}
type LValueWrappers [][]uint8

var (
	// global map from connection string to database connection, to reuse connections, protected by a mutex
	reuseDB  = make(map[string]*sql.DB)
	reuseMut = &sync.RWMutex{}
)

// Load makes functions related to building a library of Lua code available
func Load(L *lua.LState, driver string) {
	// Register the PQ function
	L.SetGlobal("address", L.NewFunction(func(l *lua.LState) int {
		mainCtx := gctx.New()
		//root:=PDT49#80Z!RVv52_z@tcp(192.168.3.217:4000)/thirdparty
		address := g.Cfg().MustGet(mainCtx, "mysql.address", "").String()
		passWord := g.Cfg().MustGet(mainCtx, "mysql.passWord", "").String()
		userName := g.Cfg().MustGet(mainCtx, "mysql.userName", "").String()
		dbName := g.Cfg().MustGet(mainCtx, "mysql.dbName", "").String()
		mysqlCon := fmt.Sprintf("%s:%s@tcp(%s)/%s", userName, passWord, address, dbName)
		l.Push(lua.LString(mysqlCon))
		return 1

	}))
	L.SetGlobal("mysql", L.NewFunction(func(L *lua.LState) int {
		// Check if the optional argument is given
		query := defaultQuery
		if L.GetTop() >= 1 {
			query = L.ToString(1)
			if query == "" {
				query = defaultQuery
			}
		}
		connectionString := defaultConnectionString
		if L.GetTop() >= 2 {
			connectionString = L.ToString(2)
		}

		// Check if there is a connection that can be reused
		var db *sql.DB
		reuseMut.RLock()
		conn, ok := reuseDB[connectionString]
		reuseMut.RUnlock()

		if ok {
			// It exists, but is it still alive?
			err := conn.Ping()
			if err != nil {
				// no
				// log.Info("did not reuse the connection")
				reuseMut.Lock()
				delete(reuseDB, connectionString)
				reuseMut.Unlock()
			} else {
				// yes
				// log.Info("reused the connection")
				db = conn
			}
		}
		// Create a new connection, if needed
		var err error
		if db == nil {
			db, err = sql.Open(driver, connectionString)
			if err != nil {
				log.Error("Could not connect to database using " + connectionString + ": " + err.Error())
				return 0 // No results
			}
			// Save the connection for later
			reuseMut.Lock()
			reuseDB[connectionString] = db
			reuseMut.Unlock()
		}
		// log.Info(fmt.Sprintf("%s database: %v (%T)\n",driver, db, db))
		reuseMut.Lock()
		rows, err := db.Query(query)
		reuseMut.Unlock()
		if err != nil {
			errMsg := err.Error()
			if strings.Contains(errMsg, ": connect: connection refused") {
				log.Info(driver + " connection string: " + connectionString)
				log.Info(driver + " query: " + query)
				log.Error("Could not connect to database: " + errMsg)
			} else if strings.Contains(errMsg, "missing") && strings.Contains(errMsg, "in connection info string") {
				log.Info(driver + " connection string: " + connectionString)
				log.Info(driver + " query: " + query)
				log.Error(errMsg)
			} else {
				log.Info(driver + " query: " + query)
				log.Error("Query failed: " + errMsg)
			}
			return 0 // No results
		}
		if rows == nil {
			// Return an empty table
			L.Push(L.NewTable())
			return 1 // number of results
		}
		// Return the rows as a table
		var (
			m      map[string]string
			maps   []map[string]string
			values LValueWrappers
		)
		cols, err := rows.Columns()
		for rows.Next() {
			values = make(LValueWrappers, len(cols))
			err = rows.Scan(values.Interfaces()...)
			if err != nil {
				log.Error("Failed to scan data: " + err.Error())
				break
			}
			m = make(map[string]string, len(cols))
			for i, v := range values {
				cname := cols[i]
				m[cname] = string(v)
			}
			maps = append(maps, m)
		}
		// Convert the strings to a Lua table
		table := convert.ArrMaps2table(L, maps)
		// Return the table
		L.Push(table)
		return 1 // number of results
	}))
}
func (w LValueWrappers) Interfaces() (s []any) {
	s = make([]any, len(w))
	for i := range w {
		s[i] = &w[i]
	}
	return
}

/*func (w LValueWrappers) Unwrap() (s []lua.LValue) {
	s = make([]lua.LValue, len(w))
	for i, v := range w {
		s[i] = v
	}
	return
}*/