github.com/samiam2013/sqlvet@v0.0.0-20221210043606-d72f678fc0aa/pkg/parseutil/sqlx_test.go (about)

     1  package parseutil
     2  
     3  import "testing"
     4  
     5  func TestCompileQuery(t *testing.T) {
     6  	table := []struct {
     7  		Q, R, D, T, N string
     8  		V             []string
     9  	}{
    10  		// basic test for named parameters, invalid char ',' terminating
    11  		{
    12  			Q: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last)`,
    13  			R: `INSERT INTO foo (a,b,c,d) VALUES (?, ?, ?, ?)`,
    14  			D: `INSERT INTO foo (a,b,c,d) VALUES ($1, $2, $3, $4)`,
    15  			T: `INSERT INTO foo (a,b,c,d) VALUES (@p1, @p2, @p3, @p4)`,
    16  			N: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last)`,
    17  			V: []string{"name", "age", "first", "last"},
    18  		},
    19  		// This query tests a named parameter ending the string as well as numbers
    20  		{
    21  			Q: `SELECT * FROM a WHERE first_name=:name1 AND last_name=:name2`,
    22  			R: `SELECT * FROM a WHERE first_name=? AND last_name=?`,
    23  			D: `SELECT * FROM a WHERE first_name=$1 AND last_name=$2`,
    24  			T: `SELECT * FROM a WHERE first_name=@p1 AND last_name=@p2`,
    25  			N: `SELECT * FROM a WHERE first_name=:name1 AND last_name=:name2`,
    26  			V: []string{"name1", "name2"},
    27  		},
    28  		{
    29  			Q: `SELECT "::foo" FROM a WHERE first_name=:name1 AND last_name=:name2`,
    30  			R: `SELECT ":foo" FROM a WHERE first_name=? AND last_name=?`,
    31  			D: `SELECT ":foo" FROM a WHERE first_name=$1 AND last_name=$2`,
    32  			T: `SELECT ":foo" FROM a WHERE first_name=@p1 AND last_name=@p2`,
    33  			N: `SELECT ":foo" FROM a WHERE first_name=:name1 AND last_name=:name2`,
    34  			V: []string{"name1", "name2"},
    35  		},
    36  		{
    37  			Q: `SELECT 'a::b::c' || first_name, '::::ABC::_::' FROM person WHERE first_name=:first_name AND last_name=:last_name`,
    38  			R: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=? AND last_name=?`,
    39  			D: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=$1 AND last_name=$2`,
    40  			T: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=@p1 AND last_name=@p2`,
    41  			N: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=:first_name AND last_name=:last_name`,
    42  			V: []string{"first_name", "last_name"},
    43  		},
    44  		{
    45  			Q: `SELECT @name := "name", :age, :first, :last`,
    46  			R: `SELECT @name := "name", ?, ?, ?`,
    47  			D: `SELECT @name := "name", $1, $2, $3`,
    48  			N: `SELECT @name := "name", :age, :first, :last`,
    49  			T: `SELECT @name := "name", @p1, @p2, @p3`,
    50  			V: []string{"age", "first", "last"},
    51  		},
    52  		/* This unicode awareness test sadly fails, because of our byte-wise worldview.
    53  		 * We could certainly iterate by Rune instead, though it's a great deal slower,
    54  		 * it's probably the RightWay(tm)
    55  		{
    56  			Q: `INSERT INTO foo (a,b,c,d) VALUES (:あ, :b, :キコ, :名前)`,
    57  			R: `INSERT INTO foo (a,b,c,d) VALUES (?, ?, ?, ?)`,
    58  			D: `INSERT INTO foo (a,b,c,d) VALUES ($1, $2, $3, $4)`,
    59  			N: []string{"name", "age", "first", "last"},
    60  		},
    61  		*/
    62  	}
    63  
    64  	for _, test := range table {
    65  		qr, names, err := CompileNamedQuery([]byte(test.Q), QUESTION)
    66  		if err != nil {
    67  			t.Error(err)
    68  		}
    69  		if qr != test.R {
    70  			t.Errorf("expected %s, got %s", test.R, qr)
    71  		}
    72  		if len(names) != len(test.V) {
    73  			t.Errorf("expected %#v, got %#v", test.V, names)
    74  		} else {
    75  			for i, name := range names {
    76  				if name != test.V[i] {
    77  					t.Errorf("expected %dth name to be %s, got %s", i+1, test.V[i], name)
    78  				}
    79  			}
    80  		}
    81  		qd, _, _ := CompileNamedQuery([]byte(test.Q), DOLLAR)
    82  		if qd != test.D {
    83  			t.Errorf("\nexpected: `%s`\ngot:      `%s`", test.D, qd)
    84  		}
    85  
    86  		qt, _, _ := CompileNamedQuery([]byte(test.Q), AT)
    87  		if qt != test.T {
    88  			t.Errorf("\nexpected: `%s`\ngot:      `%s`", test.T, qt)
    89  		}
    90  
    91  		qq, _, _ := CompileNamedQuery([]byte(test.Q), NAMED)
    92  		if qq != test.N {
    93  			t.Errorf("\nexpected: `%s`\ngot:      `%s`\n(len: %d vs %d)", test.N, qq, len(test.N), len(qq))
    94  		}
    95  	}
    96  }