github.com/prebid/prebid-server/v2@v2.18.0/stored_requests/backends/db_provider/db_provider_test.go (about)

     1  package db_provider
     2  
     3  import (
     4  	"fmt"
     5  	"testing"
     6  
     7  	"github.com/stretchr/testify/assert"
     8  )
     9  
    10  func TestPrepareQuery(t *testing.T) {
    11  	tests := []struct {
    12  		description string
    13  
    14  		template      string
    15  		params        []QueryParam
    16  		mySqlQuery    string
    17  		mySqlArgs     []interface{}
    18  		postgresQuery string
    19  		postgresArgs  []interface{}
    20  	}{
    21  		{
    22  			description:   "Np parameters",
    23  			template:      "SELECT * FROM table",
    24  			params:        []QueryParam{},
    25  			mySqlQuery:    "SELECT * FROM table",
    26  			mySqlArgs:     []interface{}{},
    27  			postgresQuery: "SELECT * FROM table",
    28  			postgresArgs:  []interface{}{},
    29  		},
    30  		{
    31  			description:   "One simple parameter",
    32  			template:      "SELECT * FROM table WHERE id = $ID",
    33  			params:        []QueryParam{{Name: "ID", Value: "1001"}},
    34  			mySqlQuery:    "SELECT * FROM table WHERE id = ?",
    35  			mySqlArgs:     []interface{}{"1001"},
    36  			postgresQuery: "SELECT * FROM table WHERE id = $1",
    37  			postgresArgs:  []interface{}{"1001"},
    38  		},
    39  		{
    40  			description: "Two simple parameters",
    41  			template:    "SELECT * FROM table WHERE id = $ID AND name = $NAME",
    42  			params: []QueryParam{
    43  				{Name: "ID", Value: "1001"},
    44  				{Name: "NAME", Value: "Alice"},
    45  			},
    46  			mySqlQuery:    "SELECT * FROM table WHERE id = ? AND name = ?",
    47  			mySqlArgs:     []interface{}{"1001", "Alice"},
    48  			postgresQuery: "SELECT * FROM table WHERE id = $1 AND name = $2",
    49  			postgresArgs:  []interface{}{"1001", "Alice"},
    50  		},
    51  		{
    52  			description: "Two simple parameters, used several times",
    53  			template:    "SELECT $ID, $NAME, * FROM table WHERE id = $ID AND name = $NAME",
    54  			params: []QueryParam{
    55  				{Name: "ID", Value: "1001"},
    56  				{Name: "NAME", Value: "Alice"},
    57  			},
    58  			mySqlQuery:    "SELECT ?, ?, * FROM table WHERE id = ? AND name = ?",
    59  			mySqlArgs:     []interface{}{"1001", "Alice", "1001", "Alice"},
    60  			postgresQuery: "SELECT $1, $2, * FROM table WHERE id = $1 AND name = $2",
    61  			postgresArgs:  []interface{}{"1001", "Alice"},
    62  		},
    63  		{
    64  			description:   "Empty list parameter",
    65  			template:      "SELECT * FROM table WHERE id IN $IDS",
    66  			params:        []QueryParam{{Name: "IDS", Value: []interface{}{}}},
    67  			mySqlQuery:    "SELECT * FROM table WHERE id IN (NULL)",
    68  			mySqlArgs:     []interface{}{},
    69  			postgresQuery: "SELECT * FROM table WHERE id IN (NULL)",
    70  			postgresArgs:  []interface{}{},
    71  		},
    72  		{
    73  			description:   "One list parameter",
    74  			template:      "SELECT * FROM table WHERE id IN $IDS",
    75  			params:        []QueryParam{{Name: "IDS", Value: []interface{}{"1001", "1002"}}},
    76  			mySqlQuery:    "SELECT * FROM table WHERE id IN (?, ?)",
    77  			mySqlArgs:     []interface{}{"1001", "1002"},
    78  			postgresQuery: "SELECT * FROM table WHERE id IN ($1, $2)",
    79  			postgresArgs:  []interface{}{"1001", "1002"},
    80  		},
    81  		{
    82  			description: "Two list parameters",
    83  			template:    "SELECT * FROM table WHERE id IN $IDS OR name in $NAMES",
    84  			params: []QueryParam{
    85  				{Name: "IDS", Value: []interface{}{"1001"}},
    86  				{Name: "NAMES", Value: []interface{}{"Bob", "Nancy"}},
    87  			},
    88  			mySqlQuery:    "SELECT * FROM table WHERE id IN (?) OR name in (?, ?)",
    89  			mySqlArgs:     []interface{}{"1001", "Bob", "Nancy"},
    90  			postgresQuery: "SELECT * FROM table WHERE id IN ($1) OR name in ($2, $3)",
    91  			postgresArgs:  []interface{}{"1001", "Bob", "Nancy"},
    92  		},
    93  		{
    94  			description: "Mix of simple and list parameters",
    95  			template: `
    96  				SELECT * FROM table1
    97  				WHERE last_updated > $LAST_UPDATED
    98  				AND (id IN $IDS OR name in $NAMES)
    99  				UNION ALL
   100  				SELECT * FROM table1
   101  				WHERE last_updated > $LAST_UPDATED
   102  				AND (id IN $IDS OR name in $NAMES)
   103  				`,
   104  			params: []QueryParam{
   105  				{Name: "LAST_UPDATED", Value: "1970-01-01"},
   106  				{Name: "IDS", Value: []interface{}{"1001"}},
   107  				{Name: "NAMES", Value: []interface{}{"Bob", "Nancy"}},
   108  			},
   109  			mySqlQuery: `
   110  				SELECT * FROM table1
   111  				WHERE last_updated > ?
   112  				AND (id IN (?) OR name in (?, ?))
   113  				UNION ALL
   114  				SELECT * FROM table1
   115  				WHERE last_updated > ?
   116  				AND (id IN (?) OR name in (?, ?))
   117  				`,
   118  			mySqlArgs: []interface{}{
   119  				"1970-01-01",
   120  				"1001",
   121  				"Bob", "Nancy",
   122  				"1970-01-01",
   123  				"1001",
   124  				"Bob", "Nancy",
   125  			},
   126  			postgresQuery: `
   127  				SELECT * FROM table1
   128  				WHERE last_updated > $1
   129  				AND (id IN ($2) OR name in ($3, $4))
   130  				UNION ALL
   131  				SELECT * FROM table1
   132  				WHERE last_updated > $1
   133  				AND (id IN ($2) OR name in ($3, $4))
   134  				`,
   135  			postgresArgs: []interface{}{
   136  				"1970-01-01",
   137  				"1001",
   138  				"Bob", "Nancy",
   139  			},
   140  		},
   141  	}
   142  
   143  	for _, tt := range tests {
   144  		mySqlDbProvider := MySqlDbProvider{}
   145  		mySqlQuery, mySqlArgs := mySqlDbProvider.PrepareQuery(tt.template, tt.params...)
   146  		assert.Equal(t, tt.mySqlQuery, mySqlQuery, fmt.Sprintf("MySql: %s", tt.description))
   147  		assert.Equal(t, tt.mySqlArgs, mySqlArgs, fmt.Sprintf("MySql: %s", tt.description))
   148  
   149  		postgresDbProvider := PostgresDbProvider{}
   150  		postgresQuery, postgresArgs := postgresDbProvider.PrepareQuery(tt.template, tt.params...)
   151  		assert.Equal(t, tt.postgresQuery, postgresQuery, fmt.Sprintf("Postgres: %s", tt.description))
   152  		assert.Equal(t, tt.postgresArgs, postgresArgs, fmt.Sprintf("Postgres: %s", tt.description))
   153  	}
   154  }