go.charczuk.com@v0.0.0-20240327042549-bc490516bd1a/sdk/db/query_test.go (about)

     1  /*
     2  
     3  Copyright (c) 2023 - Present. Will Charczuk. All rights reserved.
     4  Use of this source code is governed by a MIT license that can be found in the LICENSE file at the root of the repository.
     5  
     6  */
     7  
     8  package db
     9  
    10  import (
    11  	"context"
    12  	"sync"
    13  	"testing"
    14  	"time"
    15  
    16  	. "go.charczuk.com/sdk/assert"
    17  	"go.charczuk.com/sdk/uuid"
    18  )
    19  
    20  func Test_Query_OutMany(t *testing.T) {
    21  	tx, err := defaultDB().BeginTx(context.Background())
    22  	ItsNil(t, err)
    23  	defer func() { _ = tx.Rollback() }()
    24  
    25  	seedErr := seedObjects(10, tx)
    26  	ItsNil(t, seedErr)
    27  
    28  	var all []benchObj
    29  	err = defaultDB().Invoke(OptTx(tx)).Query("select * from bench_object").OutMany(&all)
    30  	ItsNil(t, err)
    31  	ItsNotEmpty(t, all)
    32  }
    33  
    34  func Test_Query_OutMany_scalars(t *testing.T) {
    35  	tx, err := defaultDB().BeginTx(context.Background())
    36  	ItsNil(t, err)
    37  	defer func() { _ = tx.Rollback() }()
    38  
    39  	seedErr := seedObjects(10, tx)
    40  	ItsNil(t, seedErr)
    41  
    42  	var all []int
    43  	err = defaultDB().Invoke(OptTx(tx)).Query("select id from bench_object").OutMany(&all)
    44  	ItsNil(t, err)
    45  	ItsNotEmpty(t, all)
    46  }
    47  
    48  func Test_Query_Out(t *testing.T) {
    49  	tx, err := defaultDB().BeginTx(context.Background())
    50  	ItsNil(t, err)
    51  	defer func() { _ = tx.Rollback() }()
    52  
    53  	seedErr := seedObjects(10, tx)
    54  	ItsNil(t, seedErr)
    55  
    56  	var out benchObj
    57  	_, err = defaultDB().Invoke(OptTx(tx)).Query("select * from bench_object limit 1").Out(&out)
    58  	ItsNil(t, err)
    59  	ItsNotEqual(t, 0, out.ID)
    60  }
    61  
    62  func Test_Query_Do(t *testing.T) {
    63  	tx, err := defaultDB().BeginTx(context.Background())
    64  	ItsNil(t, err)
    65  	defer func() { _ = tx.Rollback() }()
    66  
    67  	seedErr := seedObjects(10, tx)
    68  	ItsNil(t, seedErr)
    69  
    70  	rows, err := defaultDB().Invoke(OptTx(tx)).Query("select * from bench_object").Do()
    71  	ItsNil(t, err)
    72  	defer rows.Close()
    73  	ItsEqual(t, true, rows.Next())
    74  	ItsNil(t, rows.Err())
    75  }
    76  
    77  func Test_Query_Each(t *testing.T) {
    78  	tx, err := defaultDB().BeginTx(context.Background())
    79  	ItsNil(t, err)
    80  	defer func() { _ = tx.Rollback() }()
    81  
    82  	seedErr := seedObjects(10, tx)
    83  	ItsNil(t, seedErr)
    84  
    85  	var all []benchObj
    86  	var popErr error
    87  	err = defaultDB().Invoke(OptTx(tx)).Query("select * from bench_object").Each(func(r Rows) error {
    88  		bo := benchObj{}
    89  		popErr = bo.Populate(r)
    90  		if popErr != nil {
    91  			return popErr
    92  		}
    93  		all = append(all, bo)
    94  		return nil
    95  	})
    96  	ItsNil(t, err)
    97  	ItsNotEmpty(t, all)
    98  }
    99  
   100  func Test_Query_Any(t *testing.T) {
   101  	tx, err := defaultDB().BeginTx(context.Background())
   102  	ItsNil(t, err)
   103  	defer func() { _ = tx.Rollback() }()
   104  
   105  	err = seedObjects(10, tx)
   106  	ItsNil(t, err)
   107  
   108  	var all []benchObj
   109  	allErr := defaultDB().Invoke(OptTx(tx)).All(&all)
   110  	ItsNil(t, allErr)
   111  	ItsNotEmpty(t, all)
   112  
   113  	obj := all[0]
   114  
   115  	exists, err := defaultDB().Invoke(OptTx(tx)).Query("select 1 from bench_object where id = $1", obj.ID).Any()
   116  	ItsNil(t, err)
   117  	ItsEqual(t, true, exists)
   118  
   119  	notExists, err := defaultDB().Invoke(OptTx(tx)).Query("select 1 from bench_object where id = $1", -1).Any()
   120  	ItsNil(t, err)
   121  	ItsEqual(t, false, notExists)
   122  }
   123  
   124  func Test_Query_None(t *testing.T) {
   125  	tx, err := defaultDB().BeginTx(context.Background())
   126  	ItsNil(t, err)
   127  	defer func() { _ = tx.Rollback() }()
   128  
   129  	seedErr := seedObjects(10, tx)
   130  	ItsNil(t, seedErr)
   131  
   132  	var all []benchObj
   133  	allErr := defaultDB().Invoke(OptTx(tx)).All(&all)
   134  	ItsNil(t, allErr)
   135  	ItsNotEmpty(t, all)
   136  
   137  	obj := all[0]
   138  
   139  	exists, existsErr := defaultDB().Invoke(OptTx(tx)).Query("select 1 from bench_object where id = $1", obj.ID).None()
   140  	ItsNil(t, existsErr)
   141  	ItsEqual(t, false, exists)
   142  
   143  	notExists, notExistsErr := defaultDB().Invoke(OptTx(tx)).Query("select 1 from bench_object where id = $1", -1).None()
   144  	ItsNil(t, notExistsErr)
   145  	ItsEqual(t, true, notExists)
   146  }
   147  
   148  func Test_Query_PanicHandling(t *testing.T) {
   149  	tx, err := defaultDB().BeginTx(context.Background())
   150  	ItsNil(t, err)
   151  	defer func() { _ = tx.Rollback() }()
   152  
   153  	err = seedObjects(10, tx)
   154  	ItsNil(t, err)
   155  
   156  	err = defaultDB().Invoke(OptTx(tx)).Query("select * from bench_object").Each(func(r Rows) error {
   157  		panic("THIS IS A TEST PANIC")
   158  	})
   159  	ItsNotNil(t, err) // this should have the result of the panic
   160  
   161  	// we now test to see if the connection is still in a good state, i.e. that we recovered from the panic
   162  	// and closed the connection / rows / statement
   163  	hasRows, err := defaultDB().Invoke(OptTx(tx)).Query("select * from bench_object").Any()
   164  	ItsNil(t, err)
   165  	ItsEqual(t, true, hasRows)
   166  }
   167  
   168  func Test_Query_Any_MultipleQueriesPerTransaction(t *testing.T) {
   169  	tx, err := defaultDB().BeginTx(context.Background())
   170  	ItsNil(t, err)
   171  	defer func() { _ = tx.Rollback() }()
   172  
   173  	wg := sync.WaitGroup{}
   174  	wg.Add(3)
   175  
   176  	ItsNotNil(t, defaultDB().conn)
   177  
   178  	err = seedObjects(10, nil)
   179  	ItsNil(t, err)
   180  
   181  	go func() {
   182  		defer wg.Done()
   183  		hasRows, err := defaultDB().Query("select * from bench_object").Any()
   184  		ItsNil(t, err)
   185  		ItsEqual(t, true, hasRows)
   186  	}()
   187  
   188  	go func() {
   189  		defer wg.Done()
   190  		hasRows, err := defaultDB().Query("select * from bench_object").Any()
   191  		ItsNil(t, err)
   192  		ItsEqual(t, true, hasRows)
   193  	}()
   194  
   195  	go func() {
   196  		defer wg.Done()
   197  		hasRows, err := defaultDB().Query("select * from bench_object").Any()
   198  		ItsNil(t, err)
   199  		ItsEqual(t, true, hasRows)
   200  	}()
   201  
   202  	wg.Wait()
   203  
   204  	hasRows, err := defaultDB().Query("select * from bench_object").Any()
   205  	ItsNil(t, err)
   206  	ItsEqual(t, true, hasRows)
   207  }
   208  
   209  func Test_Query_First(t *testing.T) {
   210  	tx, err := defaultDB().BeginTx(context.Background())
   211  	ItsNil(t, err)
   212  	defer func() { _ = tx.Rollback() }()
   213  
   214  	seedErr := seedObjects(10, tx)
   215  	ItsNil(t, seedErr)
   216  
   217  	var first benchObj
   218  	var found bool
   219  	found, err = defaultDB().Invoke(OptTx(tx)).Query("select * from bench_object").First(func(r Rows) error {
   220  		return first.Populate(r)
   221  	})
   222  	ItsNil(t, err)
   223  	ItsEqual(t, true, found)
   224  	ItsEqual(t, 1, first.ID)
   225  }
   226  
   227  func Test_Query_Scan(t *testing.T) {
   228  	tx, err := defaultDB().BeginTx(context.Background())
   229  	ItsNil(t, err)
   230  	defer func() { _ = tx.Rollback() }()
   231  
   232  	seedErr := seedObjects(10, tx)
   233  	ItsNil(t, seedErr)
   234  
   235  	var id int
   236  	_, err = defaultDB().Invoke(OptTx(tx)).Query("select id from bench_object limit 1").Scan(&id)
   237  	ItsNil(t, err)
   238  	ItsEqual(t, 1, id)
   239  }
   240  
   241  func Test_Query_PopulateByname(t *testing.T) {
   242  	tx, err := defaultDB().BeginTx(context.Background())
   243  	ItsNil(t, err)
   244  	defer func() { _ = tx.Rollback() }()
   245  
   246  	var first benchObj
   247  	cols := defaultDB().TypeMeta(first)
   248  	_, err = defaultDB().Invoke(OptTx(tx)).Query("select * from bench_object").First(func(r Rows) error {
   249  		return PopulateByName(&first, r, cols)
   250  	})
   251  	ItsNil(t, err)
   252  	ItsEqual(t, 1, first.ID)
   253  }
   254  
   255  type benchWithPointer struct {
   256  	ID        int        `db:"id,pk,auto"`
   257  	UUID      string     `db:"uuid,uk"`
   258  	Name      string     `db:"name"`
   259  	Timestamp *time.Time `db:"timestamp_utc"`
   260  	Amount    float32    `db:"amount"`
   261  	Pending   bool       `db:"pending"`
   262  	Category  string     `db:"category"`
   263  }
   264  
   265  func (t benchWithPointer) TableName() string {
   266  	return "bench_object"
   267  }
   268  
   269  func Test_Query_Out_withDirtyStruct(t *testing.T) {
   270  	tx, err := defaultDB().BeginTx(context.Background())
   271  	ItsNil(t, err)
   272  	defer func() { _ = tx.Rollback() }()
   273  
   274  	err = createTable(tx)
   275  	ItsNil(t, err)
   276  
   277  	uniq := uuid.V4().String()
   278  
   279  	i, err := defaultDB().Invoke(OptTx(tx)).Exec("INSERT INTO bench_object (uuid, name, category) VALUES ($1, $2, $3)",
   280  		uniq, "Foo", "Bar")
   281  	ItsNil(t, err)
   282  	ra, _ := i.RowsAffected()
   283  	ItsEqual(t, 1, ra)
   284  
   285  	timeObj := time.Now()
   286  
   287  	dirty := benchWithPointer{
   288  		ID:        192,
   289  		UUID:      uuid.V4().String(),
   290  		Name:      "Widget",
   291  		Timestamp: &timeObj,
   292  		Amount:    4.99,
   293  		Category:  "Baz",
   294  	}
   295  
   296  	b, err := defaultDB().Invoke(OptTx(tx)).Query("SELECT * FROM bench_object WHERE uuid = $1", uniq).Out(&dirty)
   297  	ItsNil(t, err)
   298  	ItsEqual(t, true, b)
   299  	ItsNil(t, dirty.Timestamp)
   300  	ItsEqual(t, true, dirty.Amount == 0)
   301  }