feat(sqlschema): add more index tests from docs

pkg/sqlschema/index.go

@@ -1,8 +1,6 @@
 package sqlschema
 
 import (
-	"fmt"
-	"hash/fnv"
 	"slices"
 	"strings"
 
@@ -162,10 +160,7 @@ func (index *PartialUniqueIndex) Name() string {
 		b.WriteString(string(column))
 	}
 	b.WriteString("_")
-	hasher := fnv.New32a()
-	_, _ = hasher.Write([]byte(normalizePartialIndexWhere(index.Where)))
-
-	b.WriteString(fmt.Sprintf("%08x", hasher.Sum32()))
+	b.WriteString((&whereNormalizer{input: index.Where}).hash())
 	return b.String()
 }
 
@@ -203,7 +198,7 @@ func (index *PartialUniqueIndex) Equals(other Index) bool {
 		return false
 	}
 
-	return index.Name() == other.Name() && slices.Equal(index.Columns(), other.Columns()) && normalizePartialIndexWhere(index.Where) == normalizePartialIndexWhere(otherPartial.Where)
+	return index.Name() == other.Name() && slices.Equal(index.Columns(), other.Columns()) && (&whereNormalizer{input: index.Where}).normalize() == (&whereNormalizer{input: otherPartial.Where}).normalize()
 }
 
 func (index *PartialUniqueIndex) ToCreateSQL(fmter SQLFormatter) []byte {
@@ -238,147 +233,3 @@ func (index *PartialUniqueIndex) ToDropSQL(fmter SQLFormatter) []byte {
 	return sql
 }
 
-func normalizePartialIndexWhere(where string) string {
-	where = strings.TrimSpace(where)
-	where = stripOuterParentheses(where)
-
-	var b strings.Builder
-	b.Grow(len(where))
-
-	for i := 0; i < len(where); i++ {
-		switch where[i] {
-		case ' ', '\t', '\n', '\r':
-			if b.Len() > 0 {
-				last := b.String()[b.Len()-1]
-				if last != ' ' {
-					b.WriteByte(' ')
-				}
-			}
-		case '\'':
-			end := consumeSingleQuotedLiteral(where, i, &b)
-			i = end
-		case '"':
-			token, end := consumeDoubleQuotedToken(where, i)
-			b.WriteString(token)
-			i = end
-		default:
-			b.WriteByte(where[i])
-		}
-	}
-
-	return strings.TrimSpace(b.String())
-}
-
-func stripOuterParentheses(s string) string {
-	for {
-		s = strings.TrimSpace(s)
-		if len(s) < 2 || s[0] != '(' || s[len(s)-1] != ')' || !hasWrappingParentheses(s) {
-			return s
-		}
-		s = s[1 : len(s)-1]
-	}
-}
-
-func hasWrappingParentheses(s string) bool {
-	depth := 0
-	inSingleQuotedLiteral := false
-	inDoubleQuotedToken := false
-
-	for i := 0; i < len(s); i++ {
-		switch s[i] {
-		case '\'':
-			if inDoubleQuotedToken {
-				continue
-			}
-			if inSingleQuotedLiteral && i+1 < len(s) && s[i+1] == '\'' {
-				i++
-				continue
-			}
-			inSingleQuotedLiteral = !inSingleQuotedLiteral
-		case '"':
-			if inSingleQuotedLiteral {
-				continue
-			}
-			if inDoubleQuotedToken && i+1 < len(s) && s[i+1] == '"' {
-				i++
-				continue
-			}
-			inDoubleQuotedToken = !inDoubleQuotedToken
-		case '(':
-			if inSingleQuotedLiteral || inDoubleQuotedToken {
-				continue
-			}
-			depth++
-		case ')':
-			if inSingleQuotedLiteral || inDoubleQuotedToken {
-				continue
-			}
-			depth--
-			if depth == 0 && i != len(s)-1 {
-				return false
-			}
-		}
-	}
-
-	return depth == 0
-}
-
-func consumeSingleQuotedLiteral(s string, start int, b *strings.Builder) int {
-	b.WriteByte(s[start])
-	for i := start + 1; i < len(s); i++ {
-		b.WriteByte(s[i])
-		if s[i] == '\'' {
-			if i+1 < len(s) && s[i+1] == '\'' {
-				i++
-				b.WriteByte(s[i])
-				continue
-			}
-			return i
-		}
-	}
-
-	return len(s) - 1
-}
-
-func consumeDoubleQuotedToken(s string, start int) (string, int) {
-	var ident strings.Builder
-
-	for i := start + 1; i < len(s); i++ {
-		if s[i] == '"' {
-			if i+1 < len(s) && s[i+1] == '"' {
-				ident.WriteByte('"')
-				i++
-				continue
-			}
-
-			if isSimpleUnquotedIdentifier(ident.String()) {
-				return ident.String(), i
-			}
-
-			return s[start : i+1], i
-		}
-
-		ident.WriteByte(s[i])
-	}
-
-	return s[start:], len(s) - 1
-}
-
-func isSimpleUnquotedIdentifier(s string) bool {
-	if s == "" || strings.ToLower(s) != s {
-		return false
-	}
-
-	for i := 0; i < len(s); i++ {
-		ch := s[i]
-		if (ch >= 'a' && ch <= 'z') || ch == '_' {
-			continue
-		}
-		if i > 0 && ch >= '0' && ch <= '9' {
-			continue
-		}
-		return false
-	}
-
-	return true
-}

pkg/sqlschema/index_test.go

@@ -102,6 +102,46 @@ func TestIndexToCreateSQL(t *testing.T) {
 			},
 			sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_users_email_status_e70e78c3" ON "users" ("email", "status") WHERE email = 'test@example.com' AND status = 'active'`,
 		},
+		// postgres docs example
+		{
+			name: "PartialUnique_WhereWithPostgresDocsExample_1",
+			index: &PartialUniqueIndex{
+				TableName:   "access_log",
+				ColumnNames: []ColumnName{"client_ip"},
+				Where:       `NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255')`,
+			},
+			sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_access_log_client_ip_5a596410" ON "access_log" ("client_ip") WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255')`,
+		},
+		// postgres docs example
+		{
+			name: "PartialUnique_WhereWithPostgresDocsExample_2",
+			index: &PartialUniqueIndex{
+				TableName:   "orders",
+				ColumnNames: []ColumnName{"order_nr"},
+				Where:       `billed is not true`,
+			},
+			sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_orders_order_nr_6d31bb0e" ON "orders" ("order_nr") WHERE billed is not true`,
+		},
+		// sqlite docs example
+		{
+			name: "PartialUnique_WhereWithSqliteDocsExample_1",
+			index: &PartialUniqueIndex{
+				TableName:   "person",
+				ColumnNames: []ColumnName{"team_id"},
+				Where:       `is_team_leader`,
+			},
+			sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_person_team_id_c8604a29" ON "person" ("team_id") WHERE is_team_leader`,
+		},
+		// sqlite docs example
+		{
+			name: "PartialUnique_WhereWithSqliteDocsExample_2",
+			index: &PartialUniqueIndex{
+				TableName:   "purchaseorder",
+				ColumnNames: []ColumnName{"parent_po"},
+				Where:       `parent_po IS NOT NULL`,
+			},
+			sql: `CREATE UNIQUE INDEX IF NOT EXISTS "puq_purchaseorder_parent_po_dbe2929d" ON "purchaseorder" ("parent_po") WHERE parent_po IS NOT NULL`,
+		},
 	}
 
 	for _, testCase := range testCases {

pkg/sqlschema/normalizer.go

@@ -0,0 +1,162 @@
+package sqlschema
+
+import (
+	"fmt"
+	"hash/fnv"
+	"strings"
+)
+
+type whereNormalizer struct {
+	input string
+}
+
+func (n *whereNormalizer) hash() string {
+	hasher := fnv.New32a()
+	_, _ = hasher.Write([]byte(n.normalize()))
+	return fmt.Sprintf("%08x", hasher.Sum32())
+}
+
+func (n *whereNormalizer) normalize() string {
+	where := strings.TrimSpace(n.input)
+	where = n.stripOuterParentheses(where)
+
+	var output strings.Builder
+	output.Grow(len(where))
+
+	for i := 0; i < len(where); i++ {
+		switch where[i] {
+		case ' ', '\t', '\n', '\r':
+			if output.Len() > 0 {
+				last := output.String()[output.Len()-1]
+				if last != ' ' {
+					output.WriteByte(' ')
+				}
+			}
+		case '\'':
+			end := n.consumeSingleQuotedLiteral(where, i, &output)
+			i = end
+		case '"':
+			token, end := n.consumeDoubleQuotedToken(where, i)
+			output.WriteString(token)
+			i = end
+		default:
+			output.WriteByte(where[i])
+		}
+	}
+
+	return strings.TrimSpace(output.String())
+}
+
+func (n *whereNormalizer) stripOuterParentheses(s string) string {
+	for {
+		s = strings.TrimSpace(s)
+		if len(s) < 2 || s[0] != '(' || s[len(s)-1] != ')' || !n.hasWrappingParentheses(s) {
+			return s
+		}
+		s = s[1 : len(s)-1]
+	}
+}
+
+func (n *whereNormalizer) hasWrappingParentheses(s string) bool {
+	depth := 0
+	inSingleQuotedLiteral := false
+	inDoubleQuotedToken := false
+
+	for i := 0; i < len(s); i++ {
+		switch s[i] {
+		case '\'':
+			if inDoubleQuotedToken {
+				continue
+			}
+			if inSingleQuotedLiteral && i+1 < len(s) && s[i+1] == '\'' {
+				i++
+				continue
+			}
+			inSingleQuotedLiteral = !inSingleQuotedLiteral
+		case '"':
+			if inSingleQuotedLiteral {
+				continue
+			}
+			if inDoubleQuotedToken && i+1 < len(s) && s[i+1] == '"' {
+				i++
+				continue
+			}
+			inDoubleQuotedToken = !inDoubleQuotedToken
+		case '(':
+			if inSingleQuotedLiteral || inDoubleQuotedToken {
+				continue
+			}
+			depth++
+		case ')':
+			if inSingleQuotedLiteral || inDoubleQuotedToken {
+				continue
+			}
+			depth--
+			if depth == 0 && i != len(s)-1 {
+				return false
+			}
+		}
+	}
+
+	return depth == 0
+}
+
+func (n *whereNormalizer) consumeSingleQuotedLiteral(s string, start int, output *strings.Builder) int {
+	output.WriteByte(s[start])
+	for i := start + 1; i < len(s); i++ {
+		output.WriteByte(s[i])
+		if s[i] == '\'' {
+			if i+1 < len(s) && s[i+1] == '\'' {
+				i++
+				output.WriteByte(s[i])
+				continue
+			}
+			return i
+		}
+	}
+
+	return len(s) - 1
+}
+
+func (n *whereNormalizer) consumeDoubleQuotedToken(s string, start int) (string, int) {
+	var ident strings.Builder
+
+	for i := start + 1; i < len(s); i++ {
+		if s[i] == '"' {
+			if i+1 < len(s) && s[i+1] == '"' {
+				ident.WriteByte('"')
+				i++
+				continue
+			}
+
+			if n.isSimpleUnquotedIdentifier(ident.String()) {
+				return ident.String(), i
+			}
+
+			return s[start : i+1], i
+		}
+
+		ident.WriteByte(s[i])
+	}
+
+	return s[start:], len(s) - 1
+}
+
+func (n *whereNormalizer) isSimpleUnquotedIdentifier(s string) bool {
+	if s == "" || strings.ToLower(s) != s {
+		return false
+	}
+
+	for i := 0; i < len(s); i++ {
+		ch := s[i]
+		if (ch >= 'a' && ch <= 'z') || ch == '_' {
+			continue
+		}
+		if i > 0 && ch >= '0' && ch <= '9' {
+			continue
+		}
+		return false
+	}
+
+	return true
+}

pkg/sqlschema/normalizer_test.go

@@ -0,0 +1,57 @@
+package sqlschema
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/assert"
+)
+
+func TestWhereNormalizerNormalize(t *testing.T) {
+	testCases := []struct {
+		name   string
+		input  string
+		output string
+	}{
+		{
+			name:   "BooleanComparison",
+			input:  `"active" = true`,
+			output: `active = true`,
+		},
+		{
+			name:   "QuotedStringLiteralPreserved",
+			input:  `status = 'somewhere'`,
+			output: `status = 'somewhere'`,
+		},
+		{
+			name:   "EscapedStringLiteralPreserved",
+			input:  `status = 'it''s active'`,
+			output: `status = 'it''s active'`,
+		},
+		{
+			name:   "OuterParenthesesRemoved",
+			input:  `(("deleted_at" IS NULL))`,
+			output: `deleted_at IS NULL`,
+		},
+		{
+			name:   "InnerParenthesesPreserved",
+			input:  `("deleted_at" IS NULL OR ("active" = true AND "status" = 'open'))`,
+			output: `deleted_at IS NULL OR (active = true AND status = 'open')`,
+		},
+		{
+			name:   "MultipleClausesWhitespaceCollapsed",
+			input:  "  (  \"deleted_at\" IS NULL  \n AND\t\"active\" = true  AND status = 'open' )  ",
+			output: `deleted_at IS NULL AND active = true AND status = 'open'`,
+		},
+		{
+			name:   "ComplexBooleanClauses",
+			input:  `NOT ("deleted_at" IS NOT NULL AND ("active" = false OR "status" = 'archived'))`,
+			output: `NOT (deleted_at IS NOT NULL AND (active = false OR status = 'archived'))`,
+		},
+	}
+
+	for _, testCase := range testCases {
+		t.Run(testCase.name, func(t *testing.T) {
+			assert.Equal(t, testCase.output, (&whereNormalizer{input: testCase.input}).normalize())
+		})
+	}
+}

pkg/sqlschema/sqlitesqlschema/provider_test.go

@@ -37,6 +37,11 @@ func TestExtractWhereClause(t *testing.T) {
 			sql:   `CREATE UNIQUE INDEX "idx" ON "users" ("email") WHERE status = 'somewhere'`,
 			where: `status = 'somewhere'`,
 		},
+		{
+			name:  "BooleanLiteral",
+			sql:   `CREATE UNIQUE INDEX "idx" ON "users" ("email") WHERE active = true`,
+			where: `active = true`,
+		},
 	}
 
 	for _, testCase := range testCases {