fix #51: support new flavor SQLite

huandu · Dec 26, 2020 · b61940f · b61940f
1 parent 12583cc
commit b61940f
Show file tree

Hide file tree

Showing 5 changed files with 84 additions and 19 deletions.
diff --git a/README.md b/README.md
@@ -50,9 +50,9 @@ Following builders are implemented right now. API document and examples are prov
 - [Build](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Build): Advanced freestyle builder using special syntax defined in [Args#Compile](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#Args.Compile).
 - [BuildNamed](https://pkg.go.dev/github.com/huandu/go-sqlbuilder#BuildNamed): Advanced freestyle builder using `${key}` to refer the value of a map by key.
 
-### Build SQL for MySQL or PostgreSQL
+### Build SQL for MySQL, PostgreSQL or SQLite
 
-Parameter markers are different in MySQL and PostgreSQL. This package provides some methods to set the type of markers (we call it "flavor") in all builders.
+Parameter markers are different in MySQL, PostgreSQL and SQLite. This package provides some methods to set the type of markers (we call it "flavor") in all builders.
 
 By default, all builders uses `DefaultFlavor` to build SQL. The default value is `MySQL`.
 
@@ -62,7 +62,7 @@ We can wrap any `Builder` with a default flavor through `WithFlavor`.
 
 To be more verbose, we can use `PostgreSQL.NewSelectBuilder()` to create a `SelectBuilder` with the `PostgreSQL` flavor. All builders can be created in this way.
 
-Right now, there are only two flavors, `MySQL` and `PostgreSQL`. Open new issue to me to ask for a new flavor if you find it necessary.
+Right now, there are only three flavors, `MySQL`, `PostgreSQL` and `SQLite`. Open new issue to me to ask for a new flavor if you find it necessary.
 
 ### Using `Struct` as a light weight ORM
 
@@ -273,7 +273,7 @@ fmt.Println(err)
 // <nil>
 ```
 
-And a sample for PostgreSQL. Note that the dollar quote is supported.
+Here is a sample for PostgreSQL. Note that the dollar quote is supported.
 
 ```go
 // Only the last `$1` is interpolated.

diff --git a/args.go b/args.go
@@ -233,7 +233,7 @@ func (args *Args) compileArg(buf *bytes.Buffer, flavor Flavor, values []interfac
 		}
 	default:
 		switch flavor {
-		case MySQL:
+		case MySQL, SQLite:
 			buf.WriteRune('?')
 		case PostgreSQL:
 			fmt.Fprintf(buf, "$%v", len(values)+1)

diff --git a/flavor.go b/flavor.go
@@ -14,6 +14,7 @@ const (
 
 	MySQL
 	PostgreSQL
+	SQLite
 )
 
 var (
@@ -43,6 +44,8 @@ func (f Flavor) String() string {
 		return "MySQL"
 	case PostgreSQL:
 		return "PostgreSQL"
+	case SQLite:
+		return "SQLite"
 	}
 
 	return "<invalid>"
@@ -59,6 +62,8 @@ func (f Flavor) Interpolate(sql string, args []interface{}) (string, error) {
 		return mysqlInterpolate(sql, args...)
 	case PostgreSQL:
 		return postgresqlInterpolate(sql, args...)
+	case SQLite:
+		return sqliteInterpolate(sql, args...)
 	}
 
 	return "", ErrInterpolateNotImplemented
@@ -117,13 +122,13 @@ func (f Flavor) UnionAll(builders ...Builder) *UnionBuilder {
 // as table name or field name.
 //
 // * For MySQL, use back quote (`) to quote name;
-// * For PostgreSQL, use double quote (") to quote name.
+// * For PostgreSQL and SQLite, use double quote (") to quote name.
 func (f Flavor) Quote(name string) string {
 	switch f {
 	case MySQL:
-		return fmt.Sprintf("`%v`", name)
-	case PostgreSQL:
-		return fmt.Sprintf(`"%v"`, name)
+		return fmt.Sprintf("`%s`", name)
+	case PostgreSQL, SQLite:
+		return fmt.Sprintf(`"%s"`, name)
 	}
 
 	return name

diff --git a/flavor_test.go b/flavor_test.go
@@ -15,6 +15,7 @@ func TestFlavor(t *testing.T) {
 		0:          "<invalid>",
 		MySQL:      "MySQL",
 		PostgreSQL: "PostgreSQL",
+		SQLite:     "SQLite",
 	}
 
 	for f, expected := range cases {
@@ -105,6 +106,27 @@ func TestFlavorInterpolate(t *testing.T) {
 			"SELECT $12345678901234567890", nil,
 			"", errOutOfRange,
 		},
+
+		{
+			SQLite,
+			"SELECT * FROM a WHERE name = ? AND state IN (?, ?, ?, ?, ?)", []interface{}{"I'm fine", 42, int8(8), int16(-16), int32(32), int64(64)},
+			"SELECT * FROM a WHERE name = 'I\\'m fine' AND state IN (42, 8, -16, 32, 64)", nil,
+		},
+		{
+			SQLite,
+			"SELECT * FROM `a?` WHERE name = \"?\" AND state IN (?, '?', ?, ?, ?, ?, ?)", []interface{}{"\r\n\b\t\x1a\x00\\\"'", uint(42), uint8(8), uint16(16), uint32(32), uint64(64), "useless"},
+			"SELECT * FROM `a?` WHERE name = \"?\" AND state IN ('\\r\\n\\b\\t\\Z\\0\\\\\\\"\\'', '?', 42, 8, 16, 32, 64)", nil,
+		},
+		{
+			SQLite,
+			"SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?", []interface{}{true, false, float32(1.234567), float64(9.87654321), []byte(nil), []byte("I'm bytes"), dt, time.Time{}, nil},
+			"SELECT TRUE, FALSE, 1.234567, 9.87654321, NULL, X'49276D206279746573', '2019-04-24 12:23:34.123', '0000-00-00', NULL", nil,
+		},
+		{
+			SQLite,
+			"SELECT '\\'?', \"\\\"?\", `\\`?`, \\?", []interface{}{SQLite},
+			"SELECT '\\'?', \"\\\"?\", `\\`?`, \\'SQLite'", nil,
+		},
 	}
 
 	for idx, c := range cases {
@@ -134,7 +156,7 @@ func ExampleFlavor() {
 	// [1234 3]
 }
 
-func ExampleFlavor_Interpolate() {
+func ExampleFlavor_Interpolate_mySQL() {
 	sb := MySQL.NewSelectBuilder()
 	sb.Select("name").From("user").Where(
 		sb.NE("id", 1234),
@@ -176,3 +198,21 @@ SELECT * FROM dup($1);`, []interface{}{42})
 	// SELECT * FROM dup(42);
 	// <nil>
 }
+
+func ExampleFlavor_Interpolate_sqlite() {
+	sb := SQLite.NewSelectBuilder()
+	sb.Select("name").From("user").Where(
+		sb.NE("id", 1234),
+		sb.E("name", "Charmy Liu"),
+		sb.Like("desc", "%mother's day%"),
+	)
+	sql, args := sb.Build()
+	query, err := SQLite.Interpolate(sql, args)
+
+	fmt.Println(query)
+	fmt.Println(err)
+
+	// Output:
+	// SELECT name FROM user WHERE id <> 1234 AND name = 'Charmy Liu' AND desc LIKE '%mother\'s day%'
+	// <nil>
+}
diff --git a/interpolate.go b/interpolate.go
@@ -16,6 +16,10 @@ import (
 // If there are more "?" than len(args), returns ErrMissingArgs.
 // Otherwise, if there are less "?" than len(args), the redundant args are omitted.
 func mysqlInterpolate(query string, args ...interface{}) (string, error) {
+	return mysqlLikeInterpolate(MySQL, query, args...)
+}
+
+func mysqlLikeInterpolate(flavor Flavor, query string, args ...interface{}) (string, error) {
 	// Roughly estimate the size to avoid useless memory allocation and copy.
 	buf := make([]byte, 0, len(query)+len(args)*20)
 
@@ -48,7 +52,7 @@ func mysqlInterpolate(query string, args ...interface{}) (string, error) {
 			}
 
 			buf = append(buf, query[:offset-sz]...)
-			buf, err = encodeValue(buf, args[cnt], MySQL)
+			buf, err = encodeValue(buf, args[cnt], flavor)
 
 			if err != nil {
 				return "", err
@@ -263,6 +267,11 @@ func postgresqlInterpolate(query string, args ...interface{}) (string, error) {
 	return *(*string)(unsafe.Pointer(&buf)), nil
 }
 
+// mysqlInterpolate works the same as MySQL interpolating.
+func sqliteInterpolate(query string, args ...interface{}) (string, error) {
+	return mysqlLikeInterpolate(SQLite, query, args...)
+}
+
 func encodeValue(buf []byte, arg interface{}, flavor Flavor) ([]byte, error) {
 	switch v := arg.(type) {
 	case nil:
@@ -323,16 +332,14 @@ func encodeValue(buf []byte, arg interface{}, flavor Flavor) ([]byte, error) {
 			buf = quoteStringValue(buf, *(*string)(unsafe.Pointer(&v)), flavor)
 
 		case PostgreSQL:
-			hex := make([]byte, 0, 2)
 			buf = append(buf, "E'\\\\x"...)
-
-			for _, b := range v {
-				runes := strconv.AppendInt(hex, int64(b), 16)
-				buf = append(buf, byte(unicode.ToUpper(rune(runes[0]))))
-				buf = append(buf, byte(unicode.ToUpper(rune(runes[1]))))
-			}
-
+			buf = appendHex(buf, v)
 			buf = append(buf, "'::bytea"...)
+
+		case SQLite:
+			buf = append(buf, "X'"...)
+			buf = appendHex(buf, v)
+			buf = append(buf, '\'')
 		}
 
 	case string:
@@ -355,6 +362,9 @@ func encodeValue(buf []byte, arg interface{}, flavor Flavor) ([]byte, error) {
 
 		case PostgreSQL:
 			buf = append(buf, v.Format("2006-01-02 15:04:05.999999 MST")...)
+
+		case SQLite:
+			buf = append(buf, v.Format("2006-01-02 15:04:05.000")...)
 		}
 
 		buf = append(buf, '\'')
@@ -369,6 +379,16 @@ func encodeValue(buf []byte, arg interface{}, flavor Flavor) ([]byte, error) {
 	return buf, nil
 }
 
+var hexDigits = [16]byte{'0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'}
+
+func appendHex(buf, v []byte) []byte {
+	for _, b := range v {
+		buf = append(buf, hexDigits[(b>>4)&0xF], hexDigits[b&0xF])
+	}
+
+	return buf
+}
+
 func quoteStringValue(buf []byte, s string, flavor Flavor) []byte {
 	if flavor == PostgreSQL {
 		buf = append(buf, 'E')