2022-12-07 23:21:36 +01:00
|
|
|
package sq
|
|
|
|
|
|
|
|
import (
|
|
|
|
"context"
|
|
|
|
"database/sql"
|
|
|
|
"github.com/jmoiron/sqlx"
|
2024-01-13 14:10:25 +01:00
|
|
|
"gogs.mikescher.com/BlackForestBytes/goext/exerr"
|
2023-12-29 19:25:36 +01:00
|
|
|
"gogs.mikescher.com/BlackForestBytes/goext/langext"
|
2022-12-07 23:21:36 +01:00
|
|
|
"sync"
|
2024-10-05 00:45:55 +02:00
|
|
|
"time"
|
2022-12-07 23:21:36 +01:00
|
|
|
)
|
|
|
|
|
|
|
|
type DB interface {
|
2023-12-29 19:25:36 +01:00
|
|
|
Queryable
|
|
|
|
|
2022-12-07 23:21:36 +01:00
|
|
|
Ping(ctx context.Context) error
|
|
|
|
BeginTransaction(ctx context.Context, iso sql.IsolationLevel) (Tx, error)
|
2022-12-21 15:34:59 +01:00
|
|
|
AddListener(listener Listener)
|
2022-12-22 10:06:25 +01:00
|
|
|
Exit() error
|
2023-12-29 19:25:36 +01:00
|
|
|
RegisterConverter(DBTypeConverter)
|
2024-03-09 14:59:32 +01:00
|
|
|
}
|
|
|
|
|
|
|
|
type DBOptions struct {
|
|
|
|
RegisterDefaultConverter *bool
|
|
|
|
RegisterCommentTrimmer *bool
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
|
|
|
|
|
|
|
type database struct {
|
|
|
|
db *sqlx.DB
|
|
|
|
txctr uint16
|
|
|
|
lock sync.Mutex
|
2022-12-21 15:34:59 +01:00
|
|
|
lstr []Listener
|
2023-12-29 19:25:36 +01:00
|
|
|
conv []DBTypeConverter
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
|
|
|
|
2024-03-09 14:59:32 +01:00
|
|
|
func NewDB(db *sqlx.DB, opt DBOptions) DB {
|
|
|
|
sqdb := &database{
|
2022-12-07 23:21:36 +01:00
|
|
|
db: db,
|
|
|
|
txctr: 0,
|
|
|
|
lock: sync.Mutex{},
|
2022-12-21 15:34:59 +01:00
|
|
|
lstr: make([]Listener, 0),
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
2024-03-09 14:59:32 +01:00
|
|
|
|
|
|
|
if langext.Coalesce(opt.RegisterDefaultConverter, true) {
|
|
|
|
sqdb.registerDefaultConverter()
|
|
|
|
}
|
|
|
|
|
|
|
|
if langext.Coalesce(opt.RegisterCommentTrimmer, true) {
|
|
|
|
sqdb.AddListener(CommentTrimmer)
|
|
|
|
}
|
|
|
|
|
|
|
|
return sqdb
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
|
|
|
|
2022-12-21 15:34:59 +01:00
|
|
|
func (db *database) AddListener(listener Listener) {
|
|
|
|
db.lstr = append(db.lstr, listener)
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
|
|
|
|
2022-12-21 15:34:59 +01:00
|
|
|
func (db *database) Exec(ctx context.Context, sqlstr string, prep PP) (sql.Result, error) {
|
|
|
|
origsql := sqlstr
|
2024-10-05 00:45:55 +02:00
|
|
|
|
|
|
|
t0 := time.Now()
|
|
|
|
|
2024-10-05 01:02:25 +02:00
|
|
|
preMeta := PreExecMeta{Context: ctx, TransactionConstructorContext: nil}
|
2022-12-21 15:34:59 +01:00
|
|
|
for _, v := range db.lstr {
|
2024-10-05 00:45:55 +02:00
|
|
|
err := v.PreExec(ctx, nil, &sqlstr, &prep, preMeta)
|
2022-12-21 15:34:59 +01:00
|
|
|
if err != nil {
|
2024-01-13 14:19:19 +01:00
|
|
|
return nil, exerr.Wrap(err, "failed to call SQL pre-exec listener").Str("original_sql", origsql).Str("sql", sqlstr).Any("sql_params", prep).Build()
|
2022-12-21 15:34:59 +01:00
|
|
|
}
|
|
|
|
}
|
|
|
|
|
2024-10-05 00:45:55 +02:00
|
|
|
t1 := time.Now()
|
|
|
|
|
2022-12-21 15:34:59 +01:00
|
|
|
res, err := db.db.NamedExecContext(ctx, sqlstr, prep)
|
|
|
|
|
2024-10-05 01:02:25 +02:00
|
|
|
postMeta := PostExecMeta{Context: ctx, TransactionConstructorContext: nil, Init: t0, Start: t1, End: time.Now()}
|
2022-12-21 15:34:59 +01:00
|
|
|
for _, v := range db.lstr {
|
2024-10-05 00:45:55 +02:00
|
|
|
v.PostExec(nil, origsql, sqlstr, prep, postMeta)
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
|
|
|
|
|
|
|
if err != nil {
|
2024-01-13 14:10:25 +01:00
|
|
|
return nil, exerr.Wrap(err, "Failed to [exec] sql statement").Str("original_sql", origsql).Str("sql", sqlstr).Any("sql_params", prep).Build()
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
2024-10-05 00:45:55 +02:00
|
|
|
|
2022-12-07 23:21:36 +01:00
|
|
|
return res, nil
|
|
|
|
}
|
|
|
|
|
2022-12-21 15:34:59 +01:00
|
|
|
func (db *database) Query(ctx context.Context, sqlstr string, prep PP) (*sqlx.Rows, error) {
|
|
|
|
origsql := sqlstr
|
2024-10-05 00:45:55 +02:00
|
|
|
|
|
|
|
t0 := time.Now()
|
|
|
|
|
2024-10-05 01:02:25 +02:00
|
|
|
preMeta := PreQueryMeta{Context: ctx, TransactionConstructorContext: nil}
|
2022-12-21 15:34:59 +01:00
|
|
|
for _, v := range db.lstr {
|
2024-10-05 00:45:55 +02:00
|
|
|
err := v.PreQuery(ctx, nil, &sqlstr, &prep, preMeta)
|
2022-12-21 15:34:59 +01:00
|
|
|
if err != nil {
|
2024-01-13 14:19:19 +01:00
|
|
|
return nil, exerr.Wrap(err, "failed to call SQL pre-query listener").Str("original_sql", origsql).Str("sql", sqlstr).Any("sql_params", prep).Build()
|
2022-12-21 15:34:59 +01:00
|
|
|
}
|
|
|
|
}
|
|
|
|
|
2024-10-05 00:45:55 +02:00
|
|
|
t1 := time.Now()
|
|
|
|
|
2022-12-21 15:34:59 +01:00
|
|
|
rows, err := sqlx.NamedQueryContext(ctx, db.db, sqlstr, prep)
|
|
|
|
|
2024-10-05 01:02:25 +02:00
|
|
|
postMeta := PostQueryMeta{Context: ctx, TransactionConstructorContext: nil, Init: t0, Start: t1, End: time.Now()}
|
2022-12-21 15:34:59 +01:00
|
|
|
for _, v := range db.lstr {
|
2024-10-05 00:45:55 +02:00
|
|
|
v.PostQuery(nil, origsql, sqlstr, prep, postMeta)
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
|
|
|
|
|
|
|
if err != nil {
|
2024-01-13 14:10:25 +01:00
|
|
|
return nil, exerr.Wrap(err, "Failed to [query] sql statement").Str("original_sql", origsql).Str("sql", sqlstr).Any("sql_params", prep).Build()
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
2024-10-05 00:45:55 +02:00
|
|
|
|
2022-12-07 23:21:36 +01:00
|
|
|
return rows, nil
|
|
|
|
}
|
|
|
|
|
|
|
|
func (db *database) Ping(ctx context.Context) error {
|
2024-10-05 00:45:55 +02:00
|
|
|
|
|
|
|
t0 := time.Now()
|
|
|
|
|
2024-10-05 01:02:25 +02:00
|
|
|
preMeta := PrePingMeta{Context: ctx}
|
2022-12-21 15:34:59 +01:00
|
|
|
for _, v := range db.lstr {
|
2024-10-05 00:45:55 +02:00
|
|
|
err := v.PrePing(ctx, preMeta)
|
2022-12-21 15:34:59 +01:00
|
|
|
if err != nil {
|
|
|
|
return err
|
|
|
|
}
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
|
|
|
|
2024-10-05 00:45:55 +02:00
|
|
|
t1 := time.Now()
|
|
|
|
|
2022-12-07 23:21:36 +01:00
|
|
|
err := db.db.PingContext(ctx)
|
2022-12-21 15:34:59 +01:00
|
|
|
|
2024-10-05 01:02:25 +02:00
|
|
|
postMeta := PostPingMeta{Context: ctx, Init: t0, Start: t1, End: time.Now()}
|
2022-12-21 15:34:59 +01:00
|
|
|
for _, v := range db.lstr {
|
2024-10-05 00:45:55 +02:00
|
|
|
v.PostPing(err, postMeta)
|
2022-12-21 15:34:59 +01:00
|
|
|
}
|
|
|
|
|
2022-12-07 23:21:36 +01:00
|
|
|
if err != nil {
|
2024-01-13 14:10:25 +01:00
|
|
|
return exerr.Wrap(err, "Failed to [ping] sql database").Build()
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
2024-10-05 00:45:55 +02:00
|
|
|
|
2022-12-07 23:21:36 +01:00
|
|
|
return nil
|
|
|
|
}
|
|
|
|
|
|
|
|
func (db *database) BeginTransaction(ctx context.Context, iso sql.IsolationLevel) (Tx, error) {
|
2024-10-05 00:45:55 +02:00
|
|
|
|
|
|
|
t0 := time.Now()
|
|
|
|
|
2022-12-07 23:21:36 +01:00
|
|
|
db.lock.Lock()
|
|
|
|
txid := db.txctr
|
|
|
|
db.txctr += 1 // with overflow !
|
|
|
|
db.lock.Unlock()
|
|
|
|
|
2024-10-05 01:02:25 +02:00
|
|
|
preMeta := PreTxBeginMeta{Context: ctx}
|
2022-12-21 15:34:59 +01:00
|
|
|
for _, v := range db.lstr {
|
2024-10-05 00:45:55 +02:00
|
|
|
err := v.PreTxBegin(ctx, txid, preMeta)
|
2022-12-21 15:34:59 +01:00
|
|
|
if err != nil {
|
|
|
|
return nil, err
|
|
|
|
}
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
|
|
|
|
2024-10-05 00:45:55 +02:00
|
|
|
t1 := time.Now()
|
|
|
|
|
2022-12-07 23:21:36 +01:00
|
|
|
xtx, err := db.db.BeginTxx(ctx, &sql.TxOptions{Isolation: iso})
|
|
|
|
|
2024-10-05 01:02:25 +02:00
|
|
|
postMeta := PostTxBeginMeta{Context: ctx, Init: t0, Start: t1, End: time.Now()}
|
2022-12-21 15:34:59 +01:00
|
|
|
for _, v := range db.lstr {
|
2024-10-05 00:45:55 +02:00
|
|
|
v.PostTxBegin(txid, err, postMeta)
|
|
|
|
}
|
|
|
|
|
|
|
|
if err != nil {
|
|
|
|
return nil, exerr.Wrap(err, "Failed to start sql transaction").Build()
|
2022-12-21 15:34:59 +01:00
|
|
|
}
|
|
|
|
|
2024-10-05 00:58:15 +02:00
|
|
|
return newTransaction(ctx, xtx, txid, db), nil
|
2022-12-07 23:21:36 +01:00
|
|
|
}
|
2022-12-22 10:06:25 +01:00
|
|
|
|
|
|
|
func (db *database) Exit() error {
|
|
|
|
return db.db.Close()
|
|
|
|
}
|
2023-12-29 19:25:36 +01:00
|
|
|
|
|
|
|
func (db *database) ListConverter() []DBTypeConverter {
|
|
|
|
return db.conv
|
|
|
|
}
|
|
|
|
|
|
|
|
func (db *database) RegisterConverter(conv DBTypeConverter) {
|
|
|
|
db.conv = langext.ArrFilter(db.conv, func(v DBTypeConverter) bool { return v.ModelTypeString() != conv.ModelTypeString() })
|
|
|
|
db.conv = append(db.conv, conv)
|
|
|
|
}
|
|
|
|
|
2024-03-09 14:59:32 +01:00
|
|
|
func (db *database) registerDefaultConverter() {
|
2023-12-29 19:25:36 +01:00
|
|
|
db.RegisterConverter(ConverterBoolToBit)
|
2024-02-28 14:28:48 +01:00
|
|
|
|
2023-12-29 19:25:36 +01:00
|
|
|
db.RegisterConverter(ConverterTimeToUnixMillis)
|
2024-02-28 14:28:48 +01:00
|
|
|
|
2023-12-29 19:25:36 +01:00
|
|
|
db.RegisterConverter(ConverterRFCUnixMilliTimeToUnixMillis)
|
|
|
|
db.RegisterConverter(ConverterRFCUnixNanoTimeToUnixNanos)
|
|
|
|
db.RegisterConverter(ConverterRFCUnixTimeToUnixSeconds)
|
|
|
|
db.RegisterConverter(ConverterRFC339TimeToString)
|
|
|
|
db.RegisterConverter(ConverterRFC339NanoTimeToString)
|
2024-02-28 14:28:48 +01:00
|
|
|
db.RegisterConverter(ConverterRFCDateToString)
|
|
|
|
db.RegisterConverter(ConverterRFCTimeToString)
|
|
|
|
db.RegisterConverter(ConverterRFCSecondsF64ToString)
|
|
|
|
|
2024-01-05 10:25:05 +01:00
|
|
|
db.RegisterConverter(ConverterJsonObjToString)
|
|
|
|
db.RegisterConverter(ConverterJsonArrToString)
|
2024-02-28 14:28:48 +01:00
|
|
|
|
2024-01-07 04:18:03 +01:00
|
|
|
db.RegisterConverter(ConverterExErrCategoryToString)
|
|
|
|
db.RegisterConverter(ConverterExErrSeverityToString)
|
|
|
|
db.RegisterConverter(ConverterExErrTypeToString)
|
2023-12-29 19:25:36 +01:00
|
|
|
}
|