goext/sq/scanner_test.go

235 lines
6.3 KiB
Go
Raw Permalink Normal View History

2024-02-09 15:17:51 +01:00
package sq
import (
"context"
"database/sql"
"fmt"
"github.com/glebarez/go-sqlite"
"github.com/jmoiron/sqlx"
"gogs.mikescher.com/BlackForestBytes/goext/langext"
"gogs.mikescher.com/BlackForestBytes/goext/tst"
"path/filepath"
"testing"
)
func TestInsertSingle(t *testing.T) {
type request struct {
ID string `json:"id" db:"id"`
Timestamp int `json:"timestamp" db:"timestamp"`
StrVal string `json:"strVal" db:"str_val"`
FloatVal float64 `json:"floatVal" db:"float_val"`
Dummy bool `json:"dummyBool" db:"dummy_bool"`
JsonVal JsonObj `json:"jsonVal" db:"json_val"`
}
if !langext.InArray("sqlite3", sql.Drivers()) {
sqlite.RegisterAsSQLITE3()
}
ctx := context.Background()
dbdir := t.TempDir()
dbfile1 := filepath.Join(dbdir, langext.MustHexUUID()+".sqlite3")
url := fmt.Sprintf("file:%s?_pragma=journal_mode(%s)&_pragma=timeout(%d)&_pragma=foreign_keys(%s)&_pragma=busy_timeout(%d)", dbfile1, "DELETE", 1000, "true", 1000)
xdb := tst.Must(sqlx.Open("sqlite", url))(t)
db := NewDB(xdb, DBOptions{RegisterDefaultConverter: langext.PTrue})
2024-02-09 15:17:51 +01:00
_, err := db.Exec(ctx, `
CREATE TABLE requests (
id TEXT NOT NULL,
timestamp INTEGER NOT NULL,
str_val TEXT NOT NULL,
float_val REAL NOT NULL,
dummy_bool INTEGER NOT NULL CHECK(dummy_bool IN (0, 1)),
json_val TEXT NOT NULL,
PRIMARY KEY (id)
) STRICT
`, PP{})
tst.AssertNoErr(t, err)
_, err = InsertSingle(ctx, db, "requests", request{
ID: "9927",
Timestamp: 12321,
StrVal: "hello world",
Dummy: true,
FloatVal: 3.14159,
JsonVal: JsonObj{
"firs": 1,
"second": true,
},
})
tst.AssertNoErr(t, err)
}
func TestUpdateSingle(t *testing.T) {
type request struct {
ID string `json:"id" db:"id"`
Timestamp int `json:"timestamp" db:"timestamp"`
StrVal string `json:"strVal" db:"str_val"`
FloatVal float64 `json:"floatVal" db:"float_val"`
Dummy bool `json:"dummyBool" db:"dummy_bool"`
JsonVal JsonObj `json:"jsonVal" db:"json_val"`
}
if !langext.InArray("sqlite3", sql.Drivers()) {
sqlite.RegisterAsSQLITE3()
}
ctx := context.Background()
dbdir := t.TempDir()
dbfile1 := filepath.Join(dbdir, langext.MustHexUUID()+".sqlite3")
url := fmt.Sprintf("file:%s?_pragma=journal_mode(%s)&_pragma=timeout(%d)&_pragma=foreign_keys(%s)&_pragma=busy_timeout(%d)", dbfile1, "DELETE", 1000, "true", 1000)
xdb := tst.Must(sqlx.Open("sqlite", url))(t)
db := NewDB(xdb, DBOptions{RegisterDefaultConverter: langext.PTrue})
2024-02-09 15:17:51 +01:00
_, err := db.Exec(ctx, `
CREATE TABLE requests (
id TEXT NOT NULL,
timestamp INTEGER NOT NULL,
str_val TEXT NOT NULL,
float_val REAL NOT NULL,
dummy_bool INTEGER NOT NULL CHECK(dummy_bool IN (0, 1)),
json_val TEXT NOT NULL,
PRIMARY KEY (id)
) STRICT
`, PP{})
tst.AssertNoErr(t, err)
_, err = InsertSingle(ctx, db, "requests", request{
ID: "9927",
Timestamp: 12321,
StrVal: "hello world",
Dummy: true,
FloatVal: 3.14159,
JsonVal: JsonObj{
"first": 1,
"second": true,
},
})
tst.AssertNoErr(t, err)
v, err := QuerySingle[request](ctx, db, "SELECT * FROM requests WHERE id = '9927' LIMIT 1", PP{}, SModeExtended, Safe)
tst.AssertNoErr(t, err)
tst.AssertEqual(t, v.Timestamp, 12321)
tst.AssertEqual(t, v.StrVal, "hello world")
tst.AssertEqual(t, v.Dummy, true)
tst.AssertEqual(t, v.FloatVal, 3.14159)
tst.AssertStrRepEqual(t, v.JsonVal["first"], 1)
tst.AssertStrRepEqual(t, v.JsonVal["second"], true)
_, err = UpdateSingle(ctx, db, "requests", request{
ID: "9927",
Timestamp: 9999,
StrVal: "9999 hello world",
Dummy: false,
FloatVal: 123.222,
JsonVal: JsonObj{
"first": 2,
"second": false,
},
}, "id")
v, err = QuerySingle[request](ctx, db, "SELECT * FROM requests WHERE id = '9927' LIMIT 1", PP{}, SModeExtended, Safe)
tst.AssertNoErr(t, err)
tst.AssertEqual(t, v.Timestamp, 9999)
tst.AssertEqual(t, v.StrVal, "9999 hello world")
tst.AssertEqual(t, v.Dummy, false)
tst.AssertEqual(t, v.FloatVal, 123.222)
tst.AssertStrRepEqual(t, v.JsonVal["first"], 2)
tst.AssertStrRepEqual(t, v.JsonVal["second"], false)
}
func TestInsertMultiple(t *testing.T) {
type request struct {
ID string `json:"id" db:"id"`
Timestamp int `json:"timestamp" db:"timestamp"`
StrVal string `json:"strVal" db:"str_val"`
FloatVal float64 `json:"floatVal" db:"float_val"`
Dummy bool `json:"dummyBool" db:"dummy_bool"`
JsonVal JsonObj `json:"jsonVal" db:"json_val"`
}
if !langext.InArray("sqlite3", sql.Drivers()) {
sqlite.RegisterAsSQLITE3()
}
ctx := context.Background()
dbdir := t.TempDir()
dbfile1 := filepath.Join(dbdir, langext.MustHexUUID()+".sqlite3")
url := fmt.Sprintf("file:%s?_pragma=journal_mode(%s)&_pragma=timeout(%d)&_pragma=foreign_keys(%s)&_pragma=busy_timeout(%d)", dbfile1, "DELETE", 1000, "true", 1000)
xdb := tst.Must(sqlx.Open("sqlite", url))(t)
db := NewDB(xdb, DBOptions{RegisterDefaultConverter: langext.PTrue})
2024-02-09 15:17:51 +01:00
_, err := db.Exec(ctx, `
CREATE TABLE requests (
id TEXT NOT NULL,
timestamp INTEGER NOT NULL,
str_val TEXT NOT NULL,
float_val REAL NOT NULL,
dummy_bool INTEGER NOT NULL CHECK(dummy_bool IN (0, 1)),
json_val TEXT NOT NULL,
PRIMARY KEY (id)
) STRICT
`, PP{})
tst.AssertNoErr(t, err)
_, err = InsertMultiple(ctx, db, "requests", []request{
{
ID: "1",
Timestamp: 1000,
StrVal: "one",
Dummy: true,
FloatVal: 0.1,
JsonVal: JsonObj{
"arr": []int{0},
},
},
{
ID: "2",
Timestamp: 2000,
StrVal: "two",
Dummy: true,
FloatVal: 0.2,
JsonVal: JsonObj{
"arr": []int{0, 0},
},
},
{
ID: "3",
Timestamp: 3000,
StrVal: "three",
Dummy: true,
FloatVal: 0.3,
JsonVal: JsonObj{
"arr": []int{0, 0, 0},
},
},
}, -1)
tst.AssertNoErr(t, err)
_, err = QuerySingle[request](ctx, db, "SELECT * FROM requests WHERE id = '1' LIMIT 1", PP{}, SModeExtended, Safe)
tst.AssertNoErr(t, err)
_, err = QuerySingle[request](ctx, db, "SELECT * FROM requests WHERE id = '2' LIMIT 1", PP{}, SModeExtended, Safe)
tst.AssertNoErr(t, err)
_, err = QuerySingle[request](ctx, db, "SELECT * FROM requests WHERE id = '3' LIMIT 1", PP{}, SModeExtended, Safe)
tst.AssertNoErr(t, err)
}