goext/sq/scanner_test.go

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})

	_, 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})

	_, 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})

	_, 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)
}