1package squirrel
2
3import (
4	"database/sql"
5	"fmt"
6	"strings"
7	"testing"
8
9	"github.com/stretchr/testify/assert"
10)
11
12type DBStub struct {
13	err error
14
15	LastPrepareSql string
16	PrepareCount   int
17
18	LastExecSql  string
19	LastExecArgs []interface{}
20
21	LastQuerySql  string
22	LastQueryArgs []interface{}
23
24	LastQueryRowSql  string
25	LastQueryRowArgs []interface{}
26}
27
28var StubError = fmt.Errorf("this is a stub; this is only a stub")
29
30func (s *DBStub) Prepare(query string) (*sql.Stmt, error) {
31	s.LastPrepareSql = query
32	s.PrepareCount++
33	return nil, nil
34}
35
36func (s *DBStub) Exec(query string, args ...interface{}) (sql.Result, error) {
37	s.LastExecSql = query
38	s.LastExecArgs = args
39	return nil, nil
40}
41
42func (s *DBStub) Query(query string, args ...interface{}) (*sql.Rows, error) {
43	s.LastQuerySql = query
44	s.LastQueryArgs = args
45	return nil, nil
46}
47
48func (s *DBStub) QueryRow(query string, args ...interface{}) RowScanner {
49	s.LastQueryRowSql = query
50	s.LastQueryRowArgs = args
51	return &Row{RowScanner: &RowStub{}}
52}
53
54var sqlizer = Select("test")
55var sqlStr = "SELECT test"
56
57func TestExecWith(t *testing.T) {
58	db := &DBStub{}
59	ExecWith(db, sqlizer)
60	assert.Equal(t, sqlStr, db.LastExecSql)
61}
62
63func TestQueryWith(t *testing.T) {
64	db := &DBStub{}
65	QueryWith(db, sqlizer)
66	assert.Equal(t, sqlStr, db.LastQuerySql)
67}
68
69func TestQueryRowWith(t *testing.T) {
70	db := &DBStub{}
71	QueryRowWith(db, sqlizer)
72	assert.Equal(t, sqlStr, db.LastQueryRowSql)
73}
74
75func TestWithToSqlErr(t *testing.T) {
76	db := &DBStub{}
77	sqlizer := Select()
78
79	_, err := ExecWith(db, sqlizer)
80	assert.Error(t, err)
81
82	_, err = QueryWith(db, sqlizer)
83	assert.Error(t, err)
84
85	err = QueryRowWith(db, sqlizer).Scan()
86	assert.Error(t, err)
87}
88
89var testDebugUpdateSQL = Update("table").SetMap(Eq{"x": 1, "y": "val"})
90var expectedDebugUpateSQL = "UPDATE table SET x = '1', y = 'val'"
91
92func TestDebugSqlizerUpdateColon(t *testing.T) {
93	testDebugUpdateSQL.PlaceholderFormat(Colon)
94	assert.Equal(t, expectedDebugUpateSQL, DebugSqlizer(testDebugUpdateSQL))
95}
96
97func TestDebugSqlizerUpdateAtp(t *testing.T) {
98	testDebugUpdateSQL.PlaceholderFormat(AtP)
99	assert.Equal(t, expectedDebugUpateSQL, DebugSqlizer(testDebugUpdateSQL))
100}
101
102func TestDebugSqlizerUpdateDollar(t *testing.T) {
103	testDebugUpdateSQL.PlaceholderFormat(Dollar)
104	assert.Equal(t, expectedDebugUpateSQL, DebugSqlizer(testDebugUpdateSQL))
105}
106
107func TestDebugSqlizerUpdateQuestion(t *testing.T) {
108	testDebugUpdateSQL.PlaceholderFormat(Question)
109	assert.Equal(t, expectedDebugUpateSQL, DebugSqlizer(testDebugUpdateSQL))
110}
111
112var testDebugDeleteSQL = Delete("table").Where(And{
113	Eq{"column": "val"},
114	Eq{"other": 1},
115})
116var expectedDebugDeleteSQL = "DELETE FROM table WHERE (column = 'val' AND other = '1')"
117
118func TestDebugSqlizerDeleteColon(t *testing.T) {
119	testDebugDeleteSQL.PlaceholderFormat(Colon)
120	assert.Equal(t, expectedDebugDeleteSQL, DebugSqlizer(testDebugDeleteSQL))
121}
122
123func TestDebugSqlizerDeleteAtp(t *testing.T) {
124	testDebugDeleteSQL.PlaceholderFormat(AtP)
125	assert.Equal(t, expectedDebugDeleteSQL, DebugSqlizer(testDebugDeleteSQL))
126}
127
128func TestDebugSqlizerDeleteDollar(t *testing.T) {
129	testDebugDeleteSQL.PlaceholderFormat(Dollar)
130	assert.Equal(t, expectedDebugDeleteSQL, DebugSqlizer(testDebugDeleteSQL))
131}
132
133func TestDebugSqlizerDeleteQuestion(t *testing.T) {
134	testDebugDeleteSQL.PlaceholderFormat(Question)
135	assert.Equal(t, expectedDebugDeleteSQL, DebugSqlizer(testDebugDeleteSQL))
136}
137
138var testDebugInsertSQL = Insert("table").Values(1, "test")
139var expectedDebugInsertSQL = "INSERT INTO table VALUES ('1','test')"
140
141func TestDebugSqlizerInsertColon(t *testing.T) {
142	testDebugInsertSQL.PlaceholderFormat(Colon)
143	assert.Equal(t, expectedDebugInsertSQL, DebugSqlizer(testDebugInsertSQL))
144}
145
146func TestDebugSqlizerInsertAtp(t *testing.T) {
147	testDebugInsertSQL.PlaceholderFormat(AtP)
148	assert.Equal(t, expectedDebugInsertSQL, DebugSqlizer(testDebugInsertSQL))
149}
150
151func TestDebugSqlizerInsertDollar(t *testing.T) {
152	testDebugInsertSQL.PlaceholderFormat(Dollar)
153	assert.Equal(t, expectedDebugInsertSQL, DebugSqlizer(testDebugInsertSQL))
154}
155
156func TestDebugSqlizerInsertQuestion(t *testing.T) {
157	testDebugInsertSQL.PlaceholderFormat(Question)
158	assert.Equal(t, expectedDebugInsertSQL, DebugSqlizer(testDebugInsertSQL))
159}
160
161var testDebugSelectSQL = Select("*").From("table").Where(And{
162	Eq{"column": "val"},
163	Eq{"other": 1},
164})
165var expectedDebugSelectSQL = "SELECT * FROM table WHERE (column = 'val' AND other = '1')"
166
167func TestDebugSqlizerSelectColon(t *testing.T) {
168	testDebugSelectSQL.PlaceholderFormat(Colon)
169	assert.Equal(t, expectedDebugSelectSQL, DebugSqlizer(testDebugSelectSQL))
170}
171
172func TestDebugSqlizerSelectAtp(t *testing.T) {
173	testDebugSelectSQL.PlaceholderFormat(AtP)
174	assert.Equal(t, expectedDebugSelectSQL, DebugSqlizer(testDebugSelectSQL))
175}
176
177func TestDebugSqlizerSelectDollar(t *testing.T) {
178	testDebugSelectSQL.PlaceholderFormat(Dollar)
179	assert.Equal(t, expectedDebugSelectSQL, DebugSqlizer(testDebugSelectSQL))
180}
181
182func TestDebugSqlizerSelectQuestion(t *testing.T) {
183	testDebugSelectSQL.PlaceholderFormat(Question)
184	assert.Equal(t, expectedDebugSelectSQL, DebugSqlizer(testDebugSelectSQL))
185}
186
187func TestDebugSqlizer(t *testing.T) {
188	sqlizer := Expr("x = ? AND y = ? AND z = '??'", 1, "text")
189	expectedDebug := "x = '1' AND y = 'text' AND z = '?'"
190	assert.Equal(t, expectedDebug, DebugSqlizer(sqlizer))
191}
192
193func TestDebugSqlizerErrors(t *testing.T) {
194	errorMsg := DebugSqlizer(Expr("x = ?", 1, 2)) // Not enough placeholders
195	assert.True(t, strings.HasPrefix(errorMsg, "[DebugSqlizer error: "))
196
197	errorMsg = DebugSqlizer(Expr("x = ? AND y = ?", 1)) // Too many placeholders
198	assert.True(t, strings.HasPrefix(errorMsg, "[DebugSqlizer error: "))
199
200	errorMsg = DebugSqlizer(Lt{"x": nil}) // Cannot use nil values with Lt
201	assert.True(t, strings.HasPrefix(errorMsg, "[ToSql error: "))
202}
203