Repository: tomarrell/lbadd
Pull request #79 (2020-03-12 17:33:37)

implements CREATE INDEX statement
This PR completes the implementation of CREATE INDEX stmt (https://sqlite.org/lang_createindex.html) and is a part of #66 

This PR also fixes delimiting in scanning keywords - lets keywords to end on all non-alphanumeric values instead of only whitespaces.

Includes a parsing logic for separating `CREATE xxx` statements (with function skeletons for other create xxx functions).


Patch:
diff --git a/internal/parser/ast/statement.go b/internal/parser/ast/statement.go
index f326913..6c61153 100644
--- a/internal/parser/ast/statement.go
+++ b/internal/parser/ast/statement.go
@@ -104,7 +104,7 @@ type (
 		On             token.Token
 		TableName      token.Token
 		LeftParen      token.Token
-		IndexedColumns []token.Token
+		IndexedColumns []*IndexedColumn
 		RightParen     token.Token
 		Where          token.Token
 		Expr           *Expr
diff --git a/internal/parser/scanner/ruleset/ruleset_default.go b/internal/parser/scanner/ruleset/ruleset_default.go
index ddb30fd..5b473fa 100644
--- a/internal/parser/scanner/ruleset/ruleset_default.go
+++ b/internal/parser/scanner/ruleset/ruleset_default.go
@@ -71,7 +71,7 @@ func defaultKeywordsRule(s RuneScanner) (token.Type, bool) {
 	var buf bytes.Buffer
 	for {
 		next, ok := s.Lookahead()
-		if !ok || defaultWhitespaceDetector.Matches(next) {
+		if !ok || !defaultLiteral.Matches(next) {
 			break
 		}
 		_, _ = buf.WriteRune(next)
diff --git a/internal/parser/simple_parser_rules.go b/internal/parser/simple_parser_rules.go
index f6b269a..7d8d12e 100644
--- a/internal/parser/simple_parser_rules.go
+++ b/internal/parser/simple_parser_rules.go
@@ -50,6 +50,8 @@ func (p *simpleParser) parseSQLStatement(r reporter) (stmt *ast.SQLStmt) {
 		stmt.BeginStmt = p.parseBeginStmt(r)
 	case token.KeywordCommit:
 		stmt.CommitStmt = p.parseCommitStmt(r)
+	case token.KeywordCreate:
+		p.parseCreateStmt(stmt, r)
 	case token.KeywordDetach:
 		stmt.DetachStmt = p.parseDetachDatabaseStmt(r)
 	case token.KeywordEnd:
@@ -800,15 +802,23 @@ func (p *simpleParser) parseAnalyzeStmt(r reporter) (stmt *ast.AnalyzeStmt) {
 	if !ok || period.Type() == token.EOF {
 		return
 	}
-	stmt.Period = period
-	p.consumeToken()
+	// Since if there is a period, it means there is definitely an
+	// existance of a literal, we need a more restrictive condition.
+	// Thus we reject if we dont find a literal.
+	if period.Value() == "." {
+		stmt.Period = period
+		p.consumeToken()
 
-	next, ok = p.optionalLookahead(r)
-	if !ok || next.Type() == token.EOF {
-		return
+		next, ok = p.lookahead(r)
+		if !ok {
+			return
+		}
+		if next.Type() != token.Literal {
+			r.unexpectedToken(token.Literal)
+		}
+		stmt.TableOrIndexName = next
+		p.consumeToken()
 	}
-	stmt.TableOrIndexName = next
-	p.consumeToken()
 	return
 }
 
@@ -828,13 +838,14 @@ func (p *simpleParser) parseBeginStmt(r reporter) (stmt *ast.BeginStmt) {
 	if !ok || next.Type() == token.EOF {
 		return
 	}
-	if next.Type() == token.KeywordDeferred {
+	switch next.Type() {
+	case token.KeywordDeferred:
 		stmt.Deferred = next
 		p.consumeToken()
-	} else if next.Type() == token.KeywordImmediate {
+	case token.KeywordImmediate:
 		stmt.Immediate = next
 		p.consumeToken()
-	} else if next.Type() == token.KeywordExclusive {
+	case token.KeywordExclusive:
 		stmt.Exclusive = next
 		p.consumeToken()
 	}
@@ -929,3 +940,291 @@ func (p *simpleParser) parseRollbackStmt(r reporter) (stmt *ast.RollbackStmt) {
 	p.consumeToken()
 	return
 }
+
+// parseCreateStmt looks ahead for the tokens and decides which function gets to parse the statement
+func (p *simpleParser) parseCreateStmt(stmt *ast.SQLStmt, r reporter) {
+	p.searchNext(r, token.KeywordCreate)
+	createToken, ok := p.lookahead(r)
+	if !ok {
+		return
+	}
+	p.consumeToken()
+	next, ok := p.lookahead(r)
+	if !ok {
+		return
+	}
+	switch next.Type() {
+	case token.KeywordIndex:
+		stmt.CreateIndexStmt = p.parseCreateIndexStmt(createToken, r)
+	case token.KeywordUnique:
+		stmt.CreateIndexStmt = p.parseCreateIndexStmt(createToken, r)
+	case token.KeywordTable:
+		stmt.CreateTableStmt = p.parseCreateTableStmt(createToken, nil, nil, r)
+	case token.KeywordTrigger:
+		stmt.CreateTriggerStmt = p.parseCreateTriggerStmt(createToken, nil, nil, r)
+	case token.KeywordView:
+		stmt.CreateViewStmt = p.parseCreateViewStmt(createToken, nil, nil, r)
+	case token.KeywordTemp:
+		tempToken := next
+		p.consumeToken()
+		next, ok = p.lookahead(r)
+		if !ok {
+			return
+		}
+		switch next.Type() {
+		case token.KeywordTable:
+			stmt.CreateTableStmt = p.parseCreateTableStmt(createToken, tempToken, nil, r)
+		case token.KeywordTrigger:
+			stmt.CreateTriggerStmt = p.parseCreateTriggerStmt(createToken, tempToken, nil, r)
+		case token.KeywordView:
+			stmt.CreateViewStmt = p.parseCreateViewStmt(createToken, tempToken, nil, r)
+		}
+	case token.KeywordTemporary:
+		temporaryToken := next
+		p.consumeToken()
+		next, ok = p.lookahead(r)
+		if !ok {
+			return
+		}
+		switch next.Type() {
+		case token.KeywordTable:
+			stmt.CreateTableStmt = p.parseCreateTableStmt(createToken, nil, temporaryToken, r)
+		case token.KeywordTrigger:
+			stmt.CreateTriggerStmt = p.parseCreateTriggerStmt(createToken, nil, temporaryToken, r)
+		case token.KeywordView:
+			stmt.CreateViewStmt = p.parseCreateViewStmt(createToken, nil, temporaryToken, r)
+		}
+	case token.KeywordVirtual:
+		stmt.CreateVirtualTableStmt = p.parseCreateVirtualTableStmt(createToken, r)
+	}
+}
+
+// parseCreateIndexStmt parses a single CREATE INDEX statement as defined in the spec:
+// https://sqlite.org/lang_createindex.html
+func (p *simpleParser) parseCreateIndexStmt(createToken token.Token, r reporter) (stmt *ast.CreateIndexStmt) {
+	stmt = &ast.CreateIndexStmt{}
+	stmt.Create = createToken
+
+	next, ok := p.lookahead(r)
+	if !ok {
+		return
+	}
+	if next.Type() == token.KeywordUnique {
+		stmt.Unique = next
+		p.consumeToken()
+	}
+
+	next, ok = p.lookahead(r)
+	if !ok {
+		return
+	}
+	if next.Type() == token.KeywordIndex {
+		stmt.Index = next
+		p.consumeToken()
+	}
+
+	next, ok = p.lookahead(r)
+	if !ok {
+		return
+	}
+	if next.Type() == token.KeywordIf {
+		stmt.If = next
+		p.consumeToken()
+		next, ok = p.lookahead(r)
+		if !ok {
+			return
+		}
+		if next.Type() == token.KeywordNot {
+			stmt.Not = next
+			p.consumeToken()
+			next, ok = p.lookahead(r)
+			if !ok {
+				return
+			}
+			if next.Type() == token.KeywordExists {
+				stmt.Exists = next
+				p.consumeToken()
+			} else {
+				r.unexpectedToken(token.KeywordExists)
+			}
+		} else {
+			r.unexpectedToken(token.KeywordNot)
+		}
+	}
+
+	schemaNameOrIndexName, ok := p.lookahead(r)
+	if !ok {
+		return
+	}
+	if schemaNameOrIndexName.Type() == token.Literal {
+		// This is the case where there might not be a schemaName
+		// We assume that its the table name in the beginning and assign it
+		stmt.IndexName = schemaNameOrIndexName
+		p.consumeToken()
+		next, ok = p.lookahead(r)
+		if !ok {
+			return
+		}
+		// If we find the signs of there being a SchemaName,
+		// we over-write the previous value
+		if next.Value() == "." {
+			stmt.SchemaName = schemaNameOrIndexName
+			stmt.Period = next
+			p.consumeToken()
+			indexName, ok := p.lookahead(r)
+			if !ok {
+				return
+			}
+			stmt.IndexName = indexName
+			p.consumeToken()
+		}
+	}
+
+	next, ok = p.lookahead(r)
+	if !ok {
+		return
+	}
+	if next.Type() == token.KeywordOn {
+		stmt.On = next
+		p.consumeToken()
+	}
+
+	next, ok = p.lookahead(r)
+	if !ok {
+		return
+	}
+	if next.Type() == token.Literal {
+		stmt.TableName = next
+		p.consumeToken()
+	}
+
+	next, ok = p.lookahead(r)
+	if !ok {
+		return
+	}
+	if next.Value() == "(" {
+		stmt.LeftParen = next
+		p.consumeToken()
+		next, ok = p.lookahead(r)
+		if !ok {
+			return
+		}
+		for next.Value() != ")" {
+			stmt.IndexedColumns = append(stmt.IndexedColumns, p.parseIndexedColumn(r))
+			next, ok = p.lookahead(r)
+			if !ok {
+				return
+			}
+			if next.Value() == "," {
+				p.consumeToken()
+			}
+
+			next, ok = p.lookahead(r)
+			if !ok {
+				return
+			}
+		}
+		if next.Value() == ")" {
+			stmt.RightParen = next
+			p.consumeToken()
+		}
+	}
+
+	next, ok = p.optionalLookahead(r)
+	if !ok || next.Type() == token.EOF {
+		return
+	}
+	if next.Type() == token.KeywordWhere {
+		stmt.Where = next
+		p.consumeToken()
+		stmt.Expr = p.parseExpression(r)
+	}
+	return
+}
+
+func (p *simpleParser) parseIndexedColumn(r reporter) (stmt *ast.IndexedColumn) {
+	stmt = &ast.IndexedColumn{}
+	next, ok := p.lookahead(r)
+	if !ok {
+		return
+	}
+	if next.Type() == token.Literal {
+		stmt.ColumnName = next
+		p.consumeToken()
+	} else {
+		stmt.Expr = p.parseExpression(r)
+	}
+
+	next, ok = p.optionalLookahead(r)
+	if !ok || next.Type() == token.EOF {
+		return
+	}
+	if next.Type() == token.KeywordCollate {
+		stmt.Collate = next
+		p.consumeToken()
+		next, ok = p.lookahead(r)
+		if !ok {
+			return
+		}
+		if next.Type() == token.Literal {
+			stmt.CollationName = next
+			p.consumeToken()
+		} else {
+			r.unexpectedToken(token.Literal)
+		}
+	}
+
+	next, ok = p.optionalLookahead(r)
+	if !ok || next.Type() == token.EOF {
+		return
+	}
+	if next.Type() == token.KeywordAsc {
+		stmt.Asc = next
+		p.consumeToken()
+	}
+	if next.Type() == token.KeywordDesc {
+		stmt.Desc = next
+		p.consumeToken()
+	}
+	return
+}
+
+func (p *simpleParser) parseCreateTableStmt(createToken, tempToken, temporaryToken token.Token, r reporter) (stmt *ast.CreateTableStmt) {
+	next, ok := p.lookahead(r)
+	if !ok {
+		return
+	}
+	r.unsupportedConstruct(next)
+	p.searchNext(r, token.StatementSeparator, token.EOF)
+	return
+}
+
+func (p *simpleParser) parseCreateTriggerStmt(createToken, tempToken, temporaryToken token.Token, r reporter) (stmt *ast.CreateTriggerStmt) {
+	next, ok := p.lookahead(r)
+	if !ok {
+		return
+	}
+	r.unsupportedConstruct(next)
+	p.searchNext(r, token.StatementSeparator, token.EOF)
+	return
+}
+
+func (p *simpleParser) parseCreateViewStmt(createToken, tempToken, temporaryToken token.Token, r reporter) (stmt *ast.CreateViewStmt) {
+	next, ok := p.lookahead(r)
+	if !ok {
+		return
+	}
+	r.unsupportedConstruct(next)
+	p.searchNext(r, token.StatementSeparator, token.EOF)
+	return
+}
+
+func (p *simpleParser) parseCreateVirtualTableStmt(createToken token.Token, r reporter) (stmt *ast.CreateVirtualTableStmt) {
+	next, ok := p.lookahead(r)
+	if !ok {
+		return
+	}
+	r.unsupportedConstruct(next)
+	p.searchNext(r, token.StatementSeparator, token.EOF)
+	return
+}


Test patch:
diff --git a/internal/parser/parser_test.go b/internal/parser/parser_test.go
index 4c5b193..42bb013 100644
--- a/internal/parser/parser_test.go
+++ b/internal/parser/parser_test.go
@@ -452,6 +452,486 @@ func TestSingleStatementParse(t *testing.T) {
 				},
 			},
 		},
+		{
+			"create index",
+			"CREATE INDEX myIndex ON myTable (exprLiteral)",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:    token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Index:     token.New(1, 8, 7, 5, token.KeywordIndex, "INDEX"),
+					IndexName: token.New(1, 14, 13, 7, token.Literal, "myIndex"),
+					On:        token.New(1, 22, 21, 2, token.KeywordOn, "ON"),
+					TableName: token.New(1, 25, 24, 7, token.Literal, "myTable"),
+					LeftParen: token.New(1, 33, 32, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 34, 33, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 45, 44, 1, token.Delimiter, ")"),
+				},
+			},
+		},
+		{
+			"CREATE INDEX with UNIQUE",
+			"CREATE UNIQUE INDEX myIndex ON myTable (exprLiteral)",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:    token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Unique:    token.New(1, 8, 7, 6, token.KeywordUnique, "UNIQUE"),
+					Index:     token.New(1, 15, 14, 5, token.KeywordIndex, "INDEX"),
+					IndexName: token.New(1, 21, 20, 7, token.Literal, "myIndex"),
+					On:        token.New(1, 29, 28, 2, token.KeywordOn, "ON"),
+					TableName: token.New(1, 32, 31, 7, token.Literal, "myTable"),
+					LeftParen: token.New(1, 40, 39, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 41, 40, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 52, 51, 1, token.Delimiter, ")"),
+				},
+			},
+		},
+		{
+			"CREATE INDEX with IF NOT EXISTS",
+			"CREATE INDEX IF NOT EXISTS myIndex ON myTable (exprLiteral)",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:    token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Index:     token.New(1, 8, 7, 5, token.KeywordIndex, "INDEX"),
+					If:        token.New(1, 14, 13, 2, token.KeywordIf, "IF"),
+					Not:       token.New(1, 17, 16, 3, token.KeywordNot, "NOT"),
+					Exists:    token.New(1, 21, 20, 6, token.KeywordExists, "EXISTS"),
+					IndexName: token.New(1, 28, 27, 7, token.Literal, "myIndex"),
+					On:        token.New(1, 36, 35, 2, token.KeywordOn, "ON"),
+					TableName: token.New(1, 39, 38, 7, token.Literal, "myTable"),
+					LeftParen: token.New(1, 47, 46, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 48, 47, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 59, 58, 1, token.Delimiter, ")"),
+				},
+			},
+		},
+		{
+			"CREATE INDEX with UNIQUE and IF NOT EXISTS",
+			"CREATE UNIQUE INDEX IF NOT EXISTS myIndex ON myTable (exprLiteral)",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:    token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Unique:    token.New(1, 8, 7, 6, token.KeywordUnique, "UNIQUE"),
+					Index:     token.New(1, 15, 14, 5, token.KeywordIndex, "INDEX"),
+					If:        token.New(1, 21, 20, 2, token.KeywordIf, "IF"),
+					Not:       token.New(1, 24, 23, 3, token.KeywordNot, "NOT"),
+					Exists:    token.New(1, 28, 27, 6, token.KeywordExists, "EXISTS"),
+					IndexName: token.New(1, 35, 34, 7, token.Literal, "myIndex"),
+					On:        token.New(1, 43, 42, 2, token.KeywordOn, "ON"),
+					TableName: token.New(1, 46, 45, 7, token.Literal, "myTable"),
+					LeftParen: token.New(1, 54, 53, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 55, 54, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 66, 65, 1, token.Delimiter, ")"),
+				},
+			},
+		},
+		{
+			"create index with schema and index name",
+			"CREATE INDEX mySchema.myIndex ON myTable (exprLiteral)",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:     token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Index:      token.New(1, 8, 7, 5, token.KeywordIndex, "INDEX"),
+					SchemaName: token.New(1, 14, 13, 8, token.Literal, "mySchema"),
+					Period:     token.New(1, 22, 21, 1, token.Literal, "."),
+					IndexName:  token.New(1, 23, 22, 7, token.Literal, "myIndex"),
+					On:         token.New(1, 31, 30, 2, token.KeywordOn, "ON"),
+					TableName:  token.New(1, 34, 33, 7, token.Literal, "myTable"),
+					LeftParen:  token.New(1, 42, 41, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 43, 42, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 54, 53, 1, token.Delimiter, ")"),
+				},
+			},
+		},
+		{
+			"CREATE INDEX with UNIQUE with schema and index name",
+			"CREATE UNIQUE INDEX mySchema.myIndex ON myTable (exprLiteral)",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:     token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Unique:     token.New(1, 8, 7, 6, token.KeywordUnique, "UNIQUE"),
+					Index:      token.New(1, 15, 14, 5, token.KeywordIndex, "INDEX"),
+					SchemaName: token.New(1, 21, 20, 8, token.Literal, "mySchema"),
+					Period:     token.New(1, 29, 28, 1, token.Literal, "."),
+					IndexName:  token.New(1, 30, 29, 7, token.Literal, "myIndex"),
+					On:         token.New(1, 38, 37, 2, token.KeywordOn, "ON"),
+					TableName:  token.New(1, 41, 40, 7, token.Literal, "myTable"),
+					LeftParen:  token.New(1, 49, 48, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 50, 49, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 61, 60, 1, token.Delimiter, ")"),
+				},
+			},
+		},
+		{
+			"CREATE INDEX with IF NOT EXISTS with schema and index name",
+			"CREATE INDEX IF NOT EXISTS mySchema.myIndex ON myTable (exprLiteral)",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:     token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Index:      token.New(1, 8, 7, 5, token.KeywordIndex, "INDEX"),
+					If:         token.New(1, 14, 13, 2, token.KeywordIf, "IF"),
+					Not:        token.New(1, 17, 16, 3, token.KeywordNot, "NOT"),
+					Exists:     token.New(1, 21, 20, 6, token.KeywordExists, "EXISTS"),
+					SchemaName: token.New(1, 28, 27, 8, token.Literal, "mySchema"),
+					Period:     token.New(1, 36, 35, 1, token.Literal, "."),
+					IndexName:  token.New(1, 37, 36, 7, token.Literal, "myIndex"),
+					On:         token.New(1, 45, 44, 2, token.KeywordOn, "ON"),
+					TableName:  token.New(1, 48, 47, 7, token.Literal, "myTable"),
+					LeftParen:  token.New(1, 56, 55, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 57, 56, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 68, 67, 1, token.Delimiter, ")"),
+				},
+			},
+		},
+		{
+			"CREATE INDEX with UNIQUE and IF NOT EXISTS with schema and index name",
+			"CREATE UNIQUE INDEX IF NOT EXISTS mySchema.myIndex ON myTable (exprLiteral)",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:     token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Unique:     token.New(1, 8, 7, 6, token.KeywordUnique, "UNIQUE"),
+					Index:      token.New(1, 15, 14, 5, token.KeywordIndex, "INDEX"),
+					If:         token.New(1, 21, 20, 2, token.KeywordIf, "IF"),
+					Not:        token.New(1, 24, 23, 3, token.KeywordNot, "NOT"),
+					Exists:     token.New(1, 28, 27, 6, token.KeywordExists, "EXISTS"),
+					SchemaName: token.New(1, 35, 34, 8, token.Literal, "mySchema"),
+					Period:     token.New(1, 43, 42, 1, token.Literal, "."),
+					IndexName:  token.New(1, 44, 43, 7, token.Literal, "myIndex"),
+					On:         token.New(1, 52, 51, 2, token.KeywordOn, "ON"),
+					TableName:  token.New(1, 55, 54, 7, token.Literal, "myTable"),
+					LeftParen:  token.New(1, 63, 62, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 64, 63, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 75, 74, 1, token.Delimiter, ")"),
+				},
+			},
+		},
+		{
+			"CREATE INDEX with WHERE",
+			"CREATE INDEX myIndex ON myTable (exprLiteral) WHERE exprLiteral",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:    token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Index:     token.New(1, 8, 7, 5, token.KeywordIndex, "INDEX"),
+					IndexName: token.New(1, 14, 13, 7, token.Literal, "myIndex"),
+					On:        token.New(1, 22, 21, 2, token.KeywordOn, "ON"),
+					TableName: token.New(1, 25, 24, 7, token.Literal, "myTable"),
+					LeftParen: token.New(1, 33, 32, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 34, 33, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 45, 44, 1, token.Delimiter, ")"),
+					Where:      token.New(1, 47, 46, 5, token.KeywordWhere, "WHERE"),
+					Expr: &ast.Expr{
+						LiteralValue: token.New(1, 53, 52, 11, token.Literal, "exprLiteral"),
+					},
+				},
+			},
+		},
+		{
+			"CREATE INDEX with UNIQUE and WHERE",
+			"CREATE UNIQUE INDEX myIndex ON myTable (exprLiteral) WHERE exprLiteral",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:    token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Unique:    token.New(1, 8, 7, 6, token.KeywordUnique, "UNIQUE"),
+					Index:     token.New(1, 15, 14, 5, token.KeywordIndex, "INDEX"),
+					IndexName: token.New(1, 21, 20, 7, token.Literal, "myIndex"),
+					On:        token.New(1, 29, 28, 2, token.KeywordOn, "ON"),
+					TableName: token.New(1, 32, 31, 7, token.Literal, "myTable"),
+					LeftParen: token.New(1, 40, 39, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 41, 40, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 52, 51, 1, token.Delimiter, ")"),
+					Where:      token.New(1, 54, 53, 5, token.KeywordWhere, "WHERE"),
+					Expr: &ast.Expr{
+						LiteralValue: token.New(1, 60, 59, 11, token.Literal, "exprLiteral"),
+					},
+				},
+			},
+		},
+		{
+			"CREATE INDEX with IF NOT EXISTS and WHERE",
+			"CREATE INDEX IF NOT EXISTS myIndex ON myTable (exprLiteral) WHERE exprLiteral",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:    token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Index:     token.New(1, 8, 7, 5, token.KeywordIndex, "INDEX"),
+					If:        token.New(1, 14, 13, 2, token.KeywordIf, "IF"),
+					Not:       token.New(1, 17, 16, 3, token.KeywordNot, "NOT"),
+					Exists:    token.New(1, 21, 20, 6, token.KeywordExists, "EXISTS"),
+					IndexName: token.New(1, 28, 27, 7, token.Literal, "myIndex"),
+					On:        token.New(1, 36, 35, 2, token.KeywordOn, "ON"),
+					TableName: token.New(1, 39, 38, 7, token.Literal, "myTable"),
+					LeftParen: token.New(1, 47, 46, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 48, 47, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 59, 58, 1, token.Delimiter, ")"),
+					Where:      token.New(1, 61, 60, 5, token.KeywordWhere, "WHERE"),
+					Expr: &ast.Expr{
+						LiteralValue: token.New(1, 67, 66, 11, token.Literal, "exprLiteral"),
+					},
+				},
+			},
+		},
+		{
+			"CREATE INDEX with UNIQUE, IF NOT EXISTS and WHERE",
+			"CREATE UNIQUE INDEX IF NOT EXISTS myIndex ON myTable (exprLiteral) WHERE exprLiteral",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:    token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Unique:    token.New(1, 8, 7, 6, token.KeywordUnique, "UNIQUE"),
+					Index:     token.New(1, 15, 14, 5, token.KeywordIndex, "INDEX"),
+					If:        token.New(1, 21, 20, 2, token.KeywordIf, "IF"),
+					Not:       token.New(1, 24, 23, 3, token.KeywordNot, "NOT"),
+					Exists:    token.New(1, 28, 27, 6, token.KeywordExists, "EXISTS"),
+					IndexName: token.New(1, 35, 34, 7, token.Literal, "myIndex"),
+					On:        token.New(1, 43, 42, 2, token.KeywordOn, "ON"),
+					TableName: token.New(1, 46, 45, 7, token.Literal, "myTable"),
+					LeftParen: token.New(1, 54, 53, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 55, 54, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 66, 65, 1, token.Delimiter, ")"),
+					Where:      token.New(1, 68, 67, 5, token.KeywordWhere, "WHERE"),
+					Expr: &ast.Expr{
+						LiteralValue: token.New(1, 74, 73, 11, token.Literal, "exprLiteral"),
+					},
+				},
+			},
+		},
+		{
+			"create index with schema and index name and WHERE",
+			"CREATE INDEX mySchema.myIndex ON myTable (exprLiteral) WHERE exprLiteral",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:     token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Index:      token.New(1, 8, 7, 5, token.KeywordIndex, "INDEX"),
+					SchemaName: token.New(1, 14, 13, 8, token.Literal, "mySchema"),
+					Period:     token.New(1, 22, 21, 1, token.Literal, "."),
+					IndexName:  token.New(1, 23, 22, 7, token.Literal, "myIndex"),
+					On:         token.New(1, 31, 30, 2, token.KeywordOn, "ON"),
+					TableName:  token.New(1, 34, 33, 7, token.Literal, "myTable"),
+					LeftParen:  token.New(1, 42, 41, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 43, 42, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 54, 53, 1, token.Delimiter, ")"),
+					Where:      token.New(1, 56, 55, 5, token.KeywordWhere, "WHERE"),
+					Expr: &ast.Expr{
+						LiteralValue: token.New(1, 62, 61, 11, token.Literal, "exprLiteral"),
+					},
+				},
+			},
+		},
+		{
+			"CREATE INDEX with UNIQUE, schema name, index name and WHERE",
+			"CREATE UNIQUE INDEX mySchema.myIndex ON myTable (exprLiteral) WHERE exprLiteral",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:     token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Unique:     token.New(1, 8, 7, 6, token.KeywordUnique, "UNIQUE"),
+					Index:      token.New(1, 15, 14, 5, token.KeywordIndex, "INDEX"),
+					SchemaName: token.New(1, 21, 20, 8, token.Literal, "mySchema"),
+					Period:     token.New(1, 29, 28, 1, token.Literal, "."),
+					IndexName:  token.New(1, 30, 29, 7, token.Literal, "myIndex"),
+					On:         token.New(1, 38, 37, 2, token.KeywordOn, "ON"),
+					TableName:  token.New(1, 41, 40, 7, token.Literal, "myTable"),
+					LeftParen:  token.New(1, 49, 48, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 50, 49, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 61, 60, 1, token.Delimiter, ")"),
+					Where:      token.New(1, 63, 62, 5, token.KeywordWhere, "WHERE"),
+					Expr: &ast.Expr{
+						LiteralValue: token.New(1, 69, 68, 11, token.Literal, "exprLiteral"),
+					},
+				},
+			},
+		},
+		{
+			"CREATE INDEX with IF NOT EXISTS,schema name, index name and WHERE",
+			"CREATE INDEX IF NOT EXISTS mySchema.myIndex ON myTable (exprLiteral) WHERE exprLiteral",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:     token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Index:      token.New(1, 8, 7, 5, token.KeywordIndex, "INDEX"),
+					If:         token.New(1, 14, 13, 2, token.KeywordIf, "IF"),
+					Not:        token.New(1, 17, 16, 3, token.KeywordNot, "NOT"),
+					Exists:     token.New(1, 21, 20, 6, token.KeywordExists, "EXISTS"),
+					SchemaName: token.New(1, 28, 27, 8, token.Literal, "mySchema"),
+					Period:     token.New(1, 36, 35, 1, token.Literal, "."),
+					IndexName:  token.New(1, 37, 36, 7, token.Literal, "myIndex"),
+					On:         token.New(1, 45, 44, 2, token.KeywordOn, "ON"),
+					TableName:  token.New(1, 48, 47, 7, token.Literal, "myTable"),
+					LeftParen:  token.New(1, 56, 55, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 57, 56, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 68, 67, 1, token.Delimiter, ")"),
+					Where:      token.New(1, 70, 69, 5, token.KeywordWhere, "WHERE"),
+					Expr: &ast.Expr{
+						LiteralValue: token.New(1, 76, 75, 11, token.Literal, "exprLiteral"),
+					},
+				},
+			},
+		},
+		{
+			"CREATE INDEX with UNIQUE, IF NOT EXISTS, schema name, index name and WHERE",
+			"CREATE UNIQUE INDEX IF NOT EXISTS mySchema.myIndex ON myTable (exprLiteral) WHERE exprLiteral",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:     token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Unique:     token.New(1, 8, 7, 6, token.KeywordUnique, "UNIQUE"),
+					Index:      token.New(1, 15, 14, 5, token.KeywordIndex, "INDEX"),
+					If:         token.New(1, 21, 20, 2, token.KeywordIf, "IF"),
+					Not:        token.New(1, 24, 23, 3, token.KeywordNot, "NOT"),
+					Exists:     token.New(1, 28, 27, 6, token.KeywordExists, "EXISTS"),
+					SchemaName: token.New(1, 35, 34, 8, token.Literal, "mySchema"),
+					Period:     token.New(1, 43, 42, 1, token.Literal, "."),
+					IndexName:  token.New(1, 44, 43, 7, token.Literal, "myIndex"),
+					On:         token.New(1, 52, 51, 2, token.KeywordOn, "ON"),
+					TableName:  token.New(1, 55, 54, 7, token.Literal, "myTable"),
+					LeftParen:  token.New(1, 63, 62, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 64, 63, 11, token.Literal, "exprLiteral"),
+						},
+					},
+					RightParen: token.New(1, 75, 74, 1, token.Delimiter, ")"),
+					Where:      token.New(1, 77, 76, 5, token.KeywordWhere, "WHERE"),
+					Expr: &ast.Expr{
+						LiteralValue: token.New(1, 83, 82, 11, token.Literal, "exprLiteral"),
+					},
+				},
+			},
+		},
+		{
+			"CREATE INDEX with UNIQUE, IF NOT EXISTS, schema name, index name and WHERE with multiple indexedcolums",
+			"CREATE UNIQUE INDEX IF NOT EXISTS mySchema.myIndex ON myTable (exprLiteral1,exprLiteral2,exprLiteral3) WHERE exprLiteral",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:     token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Unique:     token.New(1, 8, 7, 6, token.KeywordUnique, "UNIQUE"),
+					Index:      token.New(1, 15, 14, 5, token.KeywordIndex, "INDEX"),
+					If:         token.New(1, 21, 20, 2, token.KeywordIf, "IF"),
+					Not:        token.New(1, 24, 23, 3, token.KeywordNot, "NOT"),
+					Exists:     token.New(1, 28, 27, 6, token.KeywordExists, "EXISTS"),
+					SchemaName: token.New(1, 35, 34, 8, token.Literal, "mySchema"),
+					Period:     token.New(1, 43, 42, 1, token.Literal, "."),
+					IndexName:  token.New(1, 44, 43, 7, token.Literal, "myIndex"),
+					On:         token.New(1, 52, 51, 2, token.KeywordOn, "ON"),
+					TableName:  token.New(1, 55, 54, 7, token.Literal, "myTable"),
+					LeftParen:  token.New(1, 63, 62, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 64, 63, 12, token.Literal, "exprLiteral1"),
+						},
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 77, 76, 12, token.Literal, "exprLiteral2"),
+						},
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 90, 89, 12, token.Literal, "exprLiteral3"),
+						},
+					},
+					RightParen: token.New(1, 102, 101, 1, token.Delimiter, ")"),
+					Where:      token.New(1, 104, 103, 5, token.KeywordWhere, "WHERE"),
+					Expr: &ast.Expr{
+						LiteralValue: token.New(1, 110, 109, 11, token.Literal, "exprLiteral"),
+					},
+				},
+			},
+		},
+		{
+			"CREATE INDEX with full fledged indexed columns and DESC",
+			"CREATE INDEX myIndex ON myTable (exprLiteral COLLATE myCollation DESC)",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:    token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Index:     token.New(1, 8, 7, 5, token.KeywordIndex, "INDEX"),
+					IndexName: token.New(1, 14, 13, 7, token.Literal, "myIndex"),
+					On:        token.New(1, 22, 21, 2, token.KeywordOn, "ON"),
+					TableName: token.New(1, 25, 24, 7, token.Literal, "myTable"),
+					LeftParen: token.New(1, 33, 32, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName:    token.New(1, 34, 33, 11, token.Literal, "exprLiteral"),
+							Collate:       token.New(1, 46, 45, 7, token.KeywordCollate, "COLLATE"),
+							CollationName: token.New(1, 54, 53, 11, token.Literal, "myCollation"),
+							Desc:          token.New(1, 66, 65, 4, token.KeywordDesc, "DESC"),
+						},
+					},
+					RightParen: token.New(1, 70, 69, 1, token.Delimiter, ")"),
+				},
+			},
+		},
+		{
+			"CREATE INDEX with indexed columns and ASC",
+			"CREATE INDEX myIndex ON myTable (exprLiteral ASC)",
+			&ast.SQLStmt{
+				CreateIndexStmt: &ast.CreateIndexStmt{
+					Create:    token.New(1, 1, 0, 6, token.KeywordCreate, "CREATE"),
+					Index:     token.New(1, 8, 7, 5, token.KeywordIndex, "INDEX"),
+					IndexName: token.New(1, 14, 13, 7, token.Literal, "myIndex"),
+					On:        token.New(1, 22, 21, 2, token.KeywordOn, "ON"),
+					TableName: token.New(1, 25, 24, 7, token.Literal, "myTable"),
+					LeftParen: token.New(1, 33, 32, 1, token.Delimiter, "("),
+					IndexedColumns: []*ast.IndexedColumn{
+						&ast.IndexedColumn{
+							ColumnName: token.New(1, 34, 33, 11, token.Literal, "exprLiteral"),
+							Asc:        token.New(1, 46, 45, 3, token.KeywordAsc, "ASC"),
+						},
+					},
+					RightParen: token.New(1, 49, 48, 1, token.Delimiter, ")"),
+				},
+			},
+		},
 	}
 	for _, input := range inputs {
 		t.Run(input.Name, func(t *testing.T) {
diff --git a/internal/parser/scanner/rule_based_scanner_test.go b/internal/parser/scanner/rule_based_scanner_test.go
index ac34999..fb0ef3b 100644
--- a/internal/parser/scanner/rule_based_scanner_test.go
+++ b/internal/parser/scanner/rule_based_scanner_test.go
@@ -197,6 +197,15 @@ func TestRuleBasedScanner(t *testing.T) {
 				token.New(1, 85, 84, 0, token.EOF, ""),
 			},
 		},
+		{
+			"ASC DESC",
+			ruleset.Default,
+			[]token.Token{
+				token.New(1, 1, 0, 3, token.KeywordAsc, "ASC"),
+				token.New(1, 5, 4, 4, token.KeywordDesc, "DESC"),
+				token.New(1, 9, 8, 0, token.EOF, ""),
+			},
+		},
 	}
 	for _, input := range inputs {
 		t.Run("ruleset=default/"+input.query, _TestRuleBasedScannerWithRuleset(input.query, input.ruleset, input.want))