Repository: ariga/atlas
Pull request #629 (2022-03-06 17:02:45)

sql/sqlite: initial support for index expression scanning
In the near future, I should add support for parsing expression, because it does support
expressions with comma atm (e.g. f(c,d))


Patch:
diff --git a/sql/sqlite/inspect.go b/sql/sqlite/inspect.go
index 6f599a38..17446399 100644
--- a/sql/sqlite/inspect.go
+++ b/sql/sqlite/inspect.go
@@ -214,8 +214,14 @@ func (i *inspect) addIndexes(t *schema.Table, rows *sql.Rows) error {
 	return nil
 }
 
+// A regexp to extract index parts.
+var reIdxParts = regexp.MustCompile("(?i)ON\\s+[\"`]*(?:\\w+)[\"`]*\\s*\\((.+)\\)")
+
 func (i *inspect) indexInfo(ctx context.Context, t *schema.Table, idx *schema.Index) error {
-	rows, err := i.QueryContext(ctx, fmt.Sprintf(indexColumnsQuery, idx.Name))
+	var (
+		hasExpr   bool
+		rows, err = i.QueryContext(ctx, fmt.Sprintf(indexColumnsQuery, idx.Name))
+	)
 	if err != nil {
 		return fmt.Errorf("sqlite: querying %q indexes: %w", t.Name, err)
 	}
@@ -238,12 +244,30 @@ func (i *inspect) indexInfo(ctx context.Context, t *schema.Table, idx *schema.In
 		// NULL name indicates that the index-part is an expression and we
 		// should extract it from the `CREATE INDEX` statement (not supported atm).
 		case !sqlx.ValidString(name):
+			hasExpr = true
 			part.X = &schema.RawExpr{X: "<unsupported>"}
 		default:
 			return fmt.Errorf("sqlite: column %q was not found for index %q", name.String, idx.Name)
 		}
 		idx.Parts = append(idx.Parts, part)
 	}
+	if !hasExpr {
+		return nil
+	}
+	var c CreateStmt
+	if !sqlx.Has(idx.Attrs, &c) || !reIdxParts.MatchString(c.S) {
+		return nil
+	}
+	parts := strings.Split(reIdxParts.FindStringSubmatch(c.S)[1], ",")
+	// Unable to parse index parts correctly.
+	if len(parts) != len(idx.Parts) {
+		return nil
+	}
+	for i, p := range idx.Parts {
+		if p.X != nil {
+			p.X.(*schema.RawExpr).X = strings.TrimSpace(parts[i])
+		}
+	}
 	return nil
 }
 


Test patch:
diff --git a/sql/sqlite/inspect_test.go b/sql/sqlite/inspect_test.go
index 1435b1a6..24042d58 100644
--- a/sql/sqlite/inspect_test.go
+++ b/sql/sqlite/inspect_test.go
@@ -88,6 +88,7 @@ func TestDriver_InspectTable(t *testing.T) {
 -------+--------------+--------+----------+-------------------------------------------------------
  c1u   |  1           |  c     |  0       | CREATE UNIQUE INDEX c1u on users(c1, c2)
  c1_c2 |  0           |  c     |  1       | CREATE INDEX c1_c2 on users(c1, c2*2) WHERE c1 <> NULL
+ c1_x  |  0           |  c     |  0       | CREATE INDEX c1_x ON users (f(c1))
 `))
 				m.ExpectQuery(sqltest.Escape(fmt.Sprintf(indexColumnsQuery, "c1u"))).
 					WillReturnRows(sqltest.Rows(`
@@ -102,6 +103,12 @@ func TestDriver_InspectTable(t *testing.T) {
 -------+--------+     
  c1    |  0     |     
  nil   |  0     |     
+`))
+				m.ExpectQuery(sqltest.Escape(fmt.Sprintf(indexColumnsQuery, "c1_x"))).
+					WillReturnRows(sqltest.Rows(`
+ name  |   desc |
+-------+--------+
+ nil   |  0     |
 `))
 				m.noFKs("users")
 			},
@@ -130,7 +137,7 @@ func TestDriver_InspectTable(t *testing.T) {
 						Table: t,
 						Parts: []*schema.IndexPart{
 							{SeqNo: 1, C: columns[0]},
-							{SeqNo: 2, X: &schema.RawExpr{X: "<unsupported>"}},
+							{SeqNo: 2, X: &schema.RawExpr{X: "c2*2"}},
 						},
 						Attrs: []schema.Attr{
 							&CreateStmt{S: "CREATE INDEX c1_c2 on users(c1, c2*2) WHERE c1 <> NULL"},
@@ -138,6 +145,17 @@ func TestDriver_InspectTable(t *testing.T) {
 							&IndexPredicate{P: "c1 <> NULL"},
 						},
 					},
+					{
+						Name:  "c1_x",
+						Table: t,
+						Parts: []*schema.IndexPart{
+							{SeqNo: 1, X: &schema.RawExpr{X: "f(c1)"}},
+						},
+						Attrs: []schema.Attr{
+							&CreateStmt{S: "CREATE INDEX c1_x ON users (f(c1))"},
+							&IndexOrigin{O: "c"},
+						},
+					},
 				}
 				require.Equal(t.Columns, columns)
 				require.Equal(t.Indexes, indexes)