github.com/yongjacky/phoenix-go-orm-builder@v0.3.5/sql_test.go (about)

     1  // Copyright 2018 The Xorm Authors. All rights reserved.
     2  // Use of this source code is governed by a BSD-style
     3  // license that can be found in the LICENSE file.
     4  
     5  package builder
     6  
     7  import (
     8  	sql2 "database/sql"
     9  	"fmt"
    10  	"io/ioutil"
    11  	"os"
    12  	"testing"
    13  
    14  	"github.com/go-xorm/sqlfiddle"
    15  	"github.com/stretchr/testify/assert"
    16  )
    17  
    18  const placeholderConverterSQL = "SELECT a, b FROM table_a WHERE b_id=(SELECT id FROM table_b WHERE b=?) AND id=? AND c=? AND d=? AND e=? AND f=?"
    19  const placeholderConvertedSQL = "SELECT a, b FROM table_a WHERE b_id=(SELECT id FROM table_b WHERE b=$1) AND id=$2 AND c=$3 AND d=$4 AND e=$5 AND f=$6"
    20  const placeholderBoundSQL = "SELECT a, b FROM table_a WHERE b_id=(SELECT id FROM table_b WHERE b=1) AND id=2.1 AND c='3' AND d=4 AND e='5' AND f=true"
    21  
    22  func TestPlaceholderConverter(t *testing.T) {
    23  	newSQL, err := ConvertPlaceholder(placeholderConverterSQL, "$")
    24  	assert.NoError(t, err)
    25  	assert.EqualValues(t, placeholderConvertedSQL, newSQL)
    26  }
    27  
    28  func BenchmarkPlaceholderConverter(b *testing.B) {
    29  	for i := 0; i < b.N; i++ {
    30  		ConvertPlaceholder(placeholderConverterSQL, "$")
    31  	}
    32  }
    33  
    34  func TestBoundSQLConverter(t *testing.T) {
    35  	newSQL, err := ConvertToBoundSQL(placeholderConverterSQL, []interface{}{1, 2.1, "3", uint(4), "5", true})
    36  	assert.NoError(t, err)
    37  	assert.EqualValues(t, placeholderBoundSQL, newSQL)
    38  
    39  	newSQL, err = ConvertToBoundSQL(placeholderConverterSQL, []interface{}{1, 2.1, sql2.Named("any", "3"), uint(4), "5", true})
    40  	assert.NoError(t, err)
    41  	assert.EqualValues(t, placeholderBoundSQL, newSQL)
    42  
    43  	newSQL, err = ConvertToBoundSQL(placeholderConverterSQL, []interface{}{1, 2.1, "3", 4, "5"})
    44  	assert.Error(t, err)
    45  	assert.EqualValues(t, ErrNeedMoreArguments, err)
    46  
    47  	newSQL, err = ToBoundSQL(Select("id").From("table").Where(In("a", 1, 2)))
    48  	assert.NoError(t, err)
    49  	assert.EqualValues(t, "SELECT id FROM table WHERE a IN (1,2)", newSQL)
    50  
    51  	newSQL, err = ToBoundSQL(Eq{"a": 1})
    52  	assert.NoError(t, err)
    53  	assert.EqualValues(t, "a=1", newSQL)
    54  
    55  	newSQL, err = ToBoundSQL(1)
    56  	assert.Error(t, err)
    57  	assert.EqualValues(t, ErrNotSupportType, err)
    58  }
    59  
    60  func TestSQL(t *testing.T) {
    61  	newSQL, args, err := ToSQL(In("a", 1, 2))
    62  	assert.NoError(t, err)
    63  	assert.EqualValues(t, "a IN (?,?)", newSQL)
    64  	assert.EqualValues(t, []interface{}{1, 2}, args)
    65  
    66  	newSQL, args, err = ToSQL(Select("id").From("table").Where(In("a", 1, 2)))
    67  	assert.NoError(t, err)
    68  	assert.EqualValues(t, "SELECT id FROM table WHERE a IN (?,?)", newSQL)
    69  	assert.EqualValues(t, []interface{}{1, 2}, args)
    70  
    71  	newSQL, args, err = ToSQL(1)
    72  	assert.Error(t, err)
    73  	assert.EqualValues(t, ErrNotSupportType, err)
    74  }
    75  
    76  type fiddler struct {
    77  	sessionCode string
    78  	dbType      int
    79  	f           *sqlfiddle.Fiddle
    80  }
    81  
    82  func readPreparationSQLFromFile(path string) (string, error) {
    83  	file, err := os.Open(path)
    84  	defer file.Close()
    85  	if err != nil {
    86  		return "", err
    87  	}
    88  
    89  	data, err := ioutil.ReadAll(file)
    90  	if err != nil {
    91  		return "", err
    92  	}
    93  
    94  	return string(data), nil
    95  }
    96  
    97  func newFiddler(fiddleServerAddr, dbDialect, preparationSQL string) (*fiddler, error) {
    98  	var dbType int
    99  	switch dbDialect {
   100  	case MYSQL:
   101  		dbType = sqlfiddle.Mysql5_6
   102  	case MSSQL:
   103  		dbType = sqlfiddle.MSSQL2017
   104  	case POSTGRES:
   105  		dbType = sqlfiddle.PostgreSQL96
   106  	case ORACLE:
   107  		dbType = sqlfiddle.Oracle11gR2
   108  	case SQLITE:
   109  		dbType = sqlfiddle.SQLite_WebSQL
   110  	default:
   111  		return nil, ErrNotSupportDialectType
   112  	}
   113  
   114  	f := sqlfiddle.NewFiddle(fiddleServerAddr)
   115  	response, err := f.CreateSchema(dbType, preparationSQL)
   116  	if err != nil {
   117  		return nil, err
   118  	}
   119  
   120  	return &fiddler{sessionCode: response.Code, f: f, dbType: dbType}, nil
   121  }
   122  
   123  func (f *fiddler) executableCheck(obj interface{}) error {
   124  	var sql string
   125  	var err error
   126  	switch obj.(type) {
   127  	case *Builder:
   128  		sql, err = obj.(*Builder).ToBoundSQL()
   129  		if err != nil {
   130  			return err
   131  		}
   132  	case string:
   133  		sql = obj.(string)
   134  	}
   135  
   136  	_, err = f.f.RunSQL(f.dbType, f.sessionCode, sql)
   137  	if err != nil {
   138  		return err
   139  	}
   140  
   141  	return nil
   142  }
   143  
   144  func TestReadPreparationSQLFromFile(t *testing.T) {
   145  	sqlFromFile, err := readPreparationSQLFromFile("testdata/mysql_fiddle_data.sql")
   146  	assert.NoError(t, err)
   147  	fmt.Println(sqlFromFile)
   148  }
   149  
   150  /*
   151  func TestNewFiddler(t *testing.T) {
   152  	sqlFromFile, err := readPreparationSQLFromFile("testdata/mysql_fiddle_data.sql")
   153  	assert.NoError(t, err)
   154  	f, err := newFiddler("", MYSQL, sqlFromFile)
   155  	assert.NoError(t, err)
   156  	assert.NotEmpty(t, f.sessionCode)
   157  }
   158  
   159  func TestExecutableCheck(t *testing.T) {
   160  	sqlFromFile, err := readPreparationSQLFromFile("testdata/mysql_fiddle_data.sql")
   161  	assert.NoError(t, err)
   162  	f, err := newFiddler("", MYSQL, sqlFromFile)
   163  	assert.NoError(t, err)
   164  	assert.NotEmpty(t, f.sessionCode)
   165  
   166  	assert.NoError(t, f.executableCheck("SELECT * FROM table1"))
   167  
   168  	err = f.executableCheck("SELECT * FROM table3")
   169  	assert.Error(t, err)
   170  }*/
   171  
   172  func TestToSQLInDifferentDialects(t *testing.T) {
   173  	sql, args, err := Postgres().Select().From("table1").Where(Eq{"a": "1"}.And(Neq{"b": "100"})).ToSQL()
   174  	assert.NoError(t, err)
   175  	assert.EqualValues(t, "SELECT * FROM table1 WHERE a=$1 AND b<>$2", sql)
   176  	assert.EqualValues(t, []interface{}{"1", "100"}, args)
   177  
   178  	sql, args, err = MySQL().Select().From("table1").Where(Eq{"a": "1"}.And(Neq{"b": "100"})).ToSQL()
   179  	assert.NoError(t, err)
   180  	assert.EqualValues(t, "SELECT * FROM table1 WHERE a=? AND b<>?", sql)
   181  	assert.EqualValues(t, []interface{}{"1", "100"}, args)
   182  
   183  	sql, args, err = MsSQL().Select().From("table1").Where(Eq{"a": "1"}.And(Neq{"b": "100"})).ToSQL()
   184  	assert.NoError(t, err)
   185  	assert.EqualValues(t, "SELECT * FROM table1 WHERE a=@p1 AND b<>@p2", sql)
   186  	assert.EqualValues(t, []interface{}{sql2.Named("p1", "1"), sql2.Named("p2", "100")}, args)
   187  
   188  	// test sql.NamedArg in cond
   189  	sql, args, err = MsSQL().Select().From("table1").Where(Eq{"a": sql2.NamedArg{Name: "param", Value: "1"}}.And(Neq{"b": "100"})).ToSQL()
   190  	assert.NoError(t, err)
   191  	assert.EqualValues(t, "SELECT * FROM table1 WHERE a=@p1 AND b<>@p2", sql)
   192  	assert.EqualValues(t, []interface{}{sql2.Named("p1", "1"), sql2.Named("p2", "100")}, args)
   193  
   194  	sql, args, err = Oracle().Select().From("table1").Where(Eq{"a": "1"}.And(Neq{"b": "100"})).ToSQL()
   195  	assert.NoError(t, err)
   196  	assert.EqualValues(t, "SELECT * FROM table1 WHERE a=:p1 AND b<>:p2", sql)
   197  	assert.EqualValues(t, []interface{}{sql2.Named("p1", "1"), sql2.Named("p2", "100")}, args)
   198  
   199  	// test sql.NamedArg in cond
   200  	sql, args, err = Oracle().Select().From("table1").Where(Eq{"a": sql2.Named("a", "1")}.And(Neq{"b": "100"})).ToSQL()
   201  	assert.NoError(t, err)
   202  	assert.EqualValues(t, "SELECT * FROM table1 WHERE a=:p1 AND b<>:p2", sql)
   203  	assert.EqualValues(t, []interface{}{sql2.Named("p1", "1"), sql2.Named("p2", "100")}, args)
   204  
   205  	sql, args, err = SQLite().Select().From("table1").Where(Eq{"a": "1"}.And(Neq{"b": "100"})).ToSQL()
   206  	assert.NoError(t, err)
   207  	assert.EqualValues(t, "SELECT * FROM table1 WHERE a=? AND b<>?", sql)
   208  	assert.EqualValues(t, []interface{}{"1", "100"}, args)
   209  }
   210  
   211  func TestToSQLInjectionHarmlessDisposal(t *testing.T) {
   212  	sql, err := MySQL().Select("*").From("table1").Where(Cond(Eq{"name": "cat';truncate table table1;"})).ToBoundSQL()
   213  	assert.NoError(t, err)
   214  	assert.EqualValues(t, "SELECT * FROM table1 WHERE name='cat'';truncate table table1;'", sql)
   215  
   216  	sql, err = MySQL().Update(Eq{`a`: 1, `b`: nil}).From(`table1`).ToBoundSQL()
   217  	assert.NoError(t, err)
   218  	assert.EqualValues(t, "UPDATE table1 SET a=1,b=null", sql)
   219  
   220  	sql, args, err := MySQL().Update(Eq{`a`: 1, `b`: nil}).From(`table1`).ToSQL()
   221  	assert.NoError(t, err)
   222  	assert.EqualValues(t, "UPDATE table1 SET a=?,b=null", sql)
   223  	assert.EqualValues(t, []interface{}{1}, args)
   224  }