github.com/pavlo67/common@v0.5.3/common/sqllib/test_scenario.go (about)

     1  package sqllib
     2  
     3  import (
     4  	"database/sql"
     5  	"testing"
     6  
     7  	"github.com/stretchr/testify/require"
     8  )
     9  
    10  func TestDB(t *testing.T, db *sql.DB) {
    11  	require.NotNil(t, db)
    12  
    13  	// prepare table -----------------------------------------------
    14  
    15  	sqlDrop := "DROP TABLE IF EXISTS test"
    16  	sqlCreate := "CREATE TABLE test (a TEXT)"
    17  
    18  	_, err := db.Exec(sqlDrop)
    19  	require.NoError(t, err)
    20  
    21  	_, err = db.Exec(sqlCreate)
    22  	require.NoError(t, err)
    23  
    24  	// prepare statements ------------------------------------------
    25  
    26  	sqlInsert := "INSERT INTO test (a) VALUES (?)"
    27  	sqlUpdate := "UPDATE test SET a = ? WHERE a = ?"
    28  	sqlDelete := "DELETE FROM test WHERE a = ?"
    29  	sqlSelect := "SELECT a FROM test WHERE a = ?"
    30  	sqlList := SQLList("test", "a", "", nil) // Ranges: &crud.Ranges{OrderBy: []string{"a DESC"}}
    31  
    32  	var stmInsert, stmUpdate, stmDelete, stmSelect, stmList *sql.Stmt
    33  
    34  	sqlStmts := []SqlStmt{
    35  		{&stmInsert, sqlInsert},
    36  		{&stmUpdate, sqlUpdate},
    37  		{&stmDelete, sqlDelete},
    38  		{&stmSelect, sqlSelect},
    39  		{&stmList, sqlList},
    40  	}
    41  
    42  	for _, sqlStmt := range sqlStmts {
    43  		err := Prepare(db, sqlStmt.Sql, sqlStmt.Stmt)
    44  		require.NoError(t, err)
    45  	}
    46  
    47  	// insert, update ----------------------------------------------
    48  
    49  	_, err = stmInsert.Exec("a1")
    50  	require.NoError(t, err)
    51  
    52  	_, err = stmInsert.Exec("a2")
    53  	require.NoError(t, err)
    54  
    55  	rows1, err := stmSelect.Query("a2")
    56  	require.NoError(t, err)
    57  	require.NotNil(t, rows1)
    58  	defer rows1.Close()
    59  
    60  	_, err = stmUpdate.Exec("a3", "a2")
    61  	require.NoError(t, err)
    62  
    63  	// count -------------------------------------------------------
    64  
    65  	var num int
    66  
    67  	sqlCount1 := SQLCount("test", "a = 'a1'", nil)
    68  	row := db.QueryRow(sqlCount1)
    69  	require.NotNil(t, row)
    70  
    71  	err = row.Scan(&num)
    72  	require.NoError(t, err)
    73  	require.Equal(t, 1, num)
    74  
    75  	sqlCount2 := SQLCount("test", "a = 'a2'", nil)
    76  	row = db.QueryRow(sqlCount2)
    77  	require.NotNil(t, row)
    78  
    79  	err = row.Scan(&num)
    80  	require.NoError(t, err)
    81  	require.Equal(t, 0, num)
    82  
    83  	sqlCountAll := SQLCount("test", "", nil)
    84  	row = db.QueryRow(sqlCountAll)
    85  	require.NotNil(t, row)
    86  
    87  	err = row.Scan(&num)
    88  	require.NoError(t, err)
    89  	require.Equal(t, 2, num)
    90  
    91  	// list --------------------------------------------------------
    92  
    93  	rowsList, err := stmList.Query()
    94  	require.NoError(t, err)
    95  	require.NotNil(t, rowsList)
    96  	defer rowsList.Close()
    97  
    98  	var items []string
    99  	for rowsList.Next() {
   100  		var item string
   101  		err := rowsList.Scan(&item)
   102  		require.NoError(t, err)
   103  		items = append(items, item)
   104  	}
   105  	err = rowsList.Err()
   106  	require.NoError(t, err)
   107  	require.Equal(t, 2, len(items))
   108  
   109  	// count, delete, recount --------------------------------------
   110  
   111  	sqlCount3 := SQLCount("test", "a = 'a3'", nil)
   112  	row = db.QueryRow(sqlCount3)
   113  	require.NotNil(t, row)
   114  
   115  	err = row.Scan(&num)
   116  	require.NoError(t, err)
   117  	require.Equal(t, 1, num)
   118  
   119  	_, err = stmDelete.Exec("a3")
   120  	require.NoError(t, err)
   121  
   122  	row = db.QueryRow(sqlCount3)
   123  	require.NotNil(t, row)
   124  
   125  	err = row.Scan(&num)
   126  	require.NoError(t, err)
   127  	require.Equal(t, 0, num)
   128  
   129  }