0
# SQL Parsing
1

2
SQL parsing classes provide MySQL and MariaDB-specific SQL parsing capabilities, handling database-specific syntax, delimiters, comments, and statement types. They ensure proper parsing of MySQL/MariaDB SQL scripts during migrations.
3

4
## Capabilities
5

6
### MySQL Parser
7

8
MySQL-specific SQL parser that handles MySQL syntax, delimiters, comments, and stored procedures.
9

10
```java { .api }
11
/**
12
 * MySQL-specific SQL parser
13
 * Handles MySQL SQL syntax, delimiters, comments, and statement parsing
14
 */
15
public class MySQLParser extends Parser {
16
    
17
    /**
18
     * Creates a new MySQL parser instance
19
     * @param configuration Flyway configuration
20
     * @param parsingContext Context for parsing operations
21
     */
22
    public MySQLParser(Configuration configuration, ParsingContext parsingContext);
23
    
24
    /**
25
     * Handles DELIMITER command processing
26
     * MySQL allows changing statement delimiters with DELIMITER command
27
     * @param reader Source reader for SQL content
28
     * @param context Parser context and state
29
     * @param pos Current position in source
30
     * @param line Current line number
31
     * @param col Current column number
32
     * @param keyword The "DELIMITER" keyword
33
     * @return Token representing the new delimiter
34
     * @throws IOException if reading fails
35
     */
36
    @Override
37
    protected Token handleKeyword(PeekingReader reader, ParserContext context, int pos, int line, int col, String keyword) throws IOException;
38
    
39
    /**
40
     * Returns the identifier quote character for MySQL
41
     * @return '`' (backtick) for MySQL identifiers
42
     */
43
    @Override
44
    protected char getIdentifierQuote();
45
    
46
    /**
47
     * Returns the alternative string literal quote character
48
     * @return '"' (double quote) for alternative string literals
49
     */
50
    @Override
51
    protected char getAlternativeStringLiteralQuote();
52
    
53
    /**
54
     * Determines if a character sequence is a single-line comment
55
     * Supports both -- and # comment styles
56
     * @param peek Character sequence to check
57
     * @param context Parser context
58
     * @param col Current column position
59
     * @return true if sequence is a single-line comment
60
     */
61
    @Override
62
    protected boolean isSingleLineComment(String peek, ParserContext context, int col);
63
    
64
    /**
65
     * Handles string literal parsing with MySQL-specific escaping
66
     * @param reader Source reader
67
     * @param context Parser context
68
     * @param pos Current position
69
     * @param line Current line number
70
     * @param col Current column number
71
     * @return String literal token
72
     * @throws IOException if reading fails
73
     */
74
    @Override
75
    protected Token handleStringLiteral(PeekingReader reader, ParserContext context, int pos, int line, int col) throws IOException;
76
    
77
    /**
78
     * Handles alternative string literal parsing (double quotes)
79
     * @param reader Source reader
80
     * @param context Parser context
81
     * @param pos Current position
82
     * @param line Current line number
83
     * @param col Current column number
84
     * @return String literal token
85
     * @throws IOException if reading fails
86
     */
87
    @Override
88
    protected Token handleAlternativeStringLiteral(PeekingReader reader, ParserContext context, int pos, int line, int col) throws IOException;
89
    
90
    /**
91
     * Handles MySQL comment directives (/*! ... */)
92
     * @param reader Source reader
93
     * @param context Parser context
94
     * @param pos Current position
95
     * @param line Current line number
96
     * @param col Current column number
97
     * @return Comment directive token
98
     * @throws IOException if reading fails
99
     */
100
    @Override
101
    protected Token handleCommentDirective(PeekingReader reader, ParserContext context, int pos, int line, int col) throws IOException;
102
    
103
    /**
104
     * Determines if text is a MySQL comment directive
105
     * MySQL comment directives start with /*! and contain version-specific SQL
106
     * @param text Text to check
107
     * @return true if text is a comment directive
108
     */
109
    @Override
110
    protected boolean isCommentDirective(String text);
111
    
112
    /**
113
     * Detects MySQL statement types including stored procedures
114
     * @param simplifiedStatement Simplified statement text
115
     * @param context Parser context
116
     * @param reader Source reader for additional parsing
117
     * @return Statement type classification
118
     */
119
    @Override
120
    protected StatementType detectStatementType(String simplifiedStatement, ParserContext context, PeekingReader reader);
121
    
122
    /**
123
     * Determines if block depth should be adjusted for MySQL syntax
124
     * @param context Parser context
125
     * @param tokens Current token list
126
     * @param token Current token being processed
127
     * @return true if block depth adjustment needed
128
     */
129
    @Override
130
    protected boolean shouldAdjustBlockDepth(ParserContext context, List<Token> tokens, Token token);
131
    
132
    /**
133
     * Adjusts block depth for MySQL control structures
134
     * @param context Parser context
135
     * @param tokens Current token list
136
     * @param keyword Current keyword token
137
     * @param reader Source reader for lookahead
138
     */
139
    @Override
140
    protected void adjustBlockDepth(ParserContext context, List<Token> tokens, Token keyword, PeekingReader reader);
141
    
142
    /**
143
     * Resets delimiter state (MySQL preserves delimiters across statements)
144
     * @param context Parser context
145
     */
146
    @Override
147
    protected void resetDelimiter(ParserContext context);
148
}
149
```
150

151
**Constants:**
152

153
```java { .api }
154
// Comment and parsing constants
155
private static final char ALTERNATIVE_SINGLE_LINE_COMMENT = '#';
156
private static final Pattern STORED_PROGRAM_REGEX = Pattern.compile(
157
    "^CREATE\\s(((DEFINER\\s(\\w+\\s)?@\\s(\\w+\\s)?)?(PROCEDURE|FUNCTION|EVENT))|TRIGGER)", 
158
    Pattern.CASE_INSENSITIVE);
159
private static final StatementType STORED_PROGRAM_STATEMENT = new StatementType();
160
}
161
```
162

163
**Usage Examples:**
164

165
```java
166
// Parser creation via DatabaseType
167
MySQLDatabaseType dbType = new MySQLDatabaseType();
168
MySQLParser parser = (MySQLParser) dbType.createParser(configuration, resourceProvider, parsingContext);
169

170
// Parse MySQL script with DELIMITER changes
171
String sql = """
172
    DELIMITER $$
173
    CREATE PROCEDURE test_proc()
174
    BEGIN
175
        SELECT 'Hello World';
176
    END$$
177
    DELIMITER ;
178
    """;
179

180
List<SqlStatement> statements = parser.parse(sql);
181
```
182

183
### MariaDB Parser
184

185
MariaDB-specific SQL parser extending MySQL parser with MariaDB-specific syntax support.
186

187
```java { .api }
188
/**
189
 * MariaDB-specific SQL parser
190
 * Extends MySQLParser with MariaDB-specific syntax and features
191
 */
192
public class MariaDBParser extends MySQLParser {
193
    
194
    /**
195
     * Creates a new MariaDB parser instance
196
     * @param configuration Flyway configuration
197
     * @param parsingContext Context for parsing operations
198
     */
199
    public MariaDBParser(Configuration configuration, ParsingContext parsingContext);
200
    
201
    /**
202
     * Detects MariaDB-specific statement types
203
     * Includes support for BEGIN NOT ATOMIC and other MariaDB extensions
204
     * @param simplifiedStatement Simplified statement text
205
     * @param context Parser context
206
     * @param reader Source reader for additional parsing
207
     * @return Statement type classification
208
     */
209
    @Override
210
    protected StatementType detectStatementType(String simplifiedStatement, ParserContext context, PeekingReader reader);
211
    
212
    /**
213
     * Adjusts block depth for MariaDB-specific control structures
214
     * Handles BEGIN NOT ATOMIC and other MariaDB syntax
215
     * @param context Parser context
216
     * @param tokens Current token list
217
     * @param keyword Current keyword token
218
     * @param reader Source reader for lookahead
219
     */
220
    @Override
221
    protected void adjustBlockDepth(ParserContext context, List<Token> tokens, Token keyword, PeekingReader reader);
222
}
223
```
224

225
**Usage Example:**
226

227
```java
228
// MariaDB parser creation
229
MariaDBDatabaseType dbType = new MariaDBDatabaseType();
230
MariaDBParser parser = (MariaDBParser) dbType.createParser(configuration, resourceProvider, parsingContext);
231

232
// Parse MariaDB-specific syntax
233
String mariadbSql = """
234
    BEGIN NOT ATOMIC
235
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
236
        SELECT 'MariaDB specific syntax';
237
    END;
238
    """;
239

240
List<SqlStatement> statements = parser.parse(mariadbSql);
241
```
242

243
## MySQL Syntax Features
244

245
### Delimiter Handling
246

247
MySQL supports changing statement delimiters:
248

249
```sql
250
-- Default delimiter is semicolon
251
SELECT * FROM users;
252

253
-- Change delimiter for stored procedures
254
DELIMITER $$
255

256
CREATE PROCEDURE get_users()
257
BEGIN
258
    SELECT * FROM users;
259
END$$
260

261
-- Reset delimiter
262
DELIMITER ;
263
```
264

265
**Parser Behavior:**
266
- Recognizes `DELIMITER` command
267
- Updates delimiter context dynamically
268
- Preserves delimiter changes across statements
269
- Handles nested delimiter scenarios
270

271
### Comment Support
272

273
#### Single-Line Comments
274

275
Two formats supported:
276
```sql
277
-- Standard SQL comment
278
SELECT * FROM users; -- End of line comment
279

280
# MySQL hash comment
281
SELECT * FROM users; # Alternative comment style
282
```
283

284
**Note:** Hash comments (`#`) are disabled when `#` is set as delimiter.
285

286
#### Multi-Line Comments
287

288
```sql
289
/* Standard multi-line comment */
290
SELECT * FROM users;
291

292
/*! MySQL-specific comment directive */
293
/*!50717 SELECT * FROM users */; -- Only execute on MySQL 5.7.17+
294
```
295

296
### String Literals
297

298
#### Single Quotes (Standard)
299
```sql
300
SELECT 'Hello World';
301
SELECT 'Don\'t worry'; -- Escaped quote
302
```
303

304
#### Double Quotes (Alternative)
305
```sql
306
SELECT "Hello World";
307
SELECT "Don\"t worry"; -- Escaped quote
308
```
309

310
**Parser Features:**
311
- Handles backslash escaping (`\'`, `\"`, `\\`)
312
- Supports both quote styles
313
- Proper escape sequence processing
314

315
### Identifier Quoting
316

317
MySQL uses backticks for identifiers:
318
```sql
319
SELECT `column name with spaces` FROM `table name`;
320
CREATE TABLE `order` (`index` INT); -- Reserved word quoting
321
```
322

323
## Statement Type Detection
324

325
### Stored Programs
326

327
Detects MySQL stored procedures, functions, events, and triggers:
328

329
```java
330
// Pattern for stored program detection
331
"^CREATE\\s(((DEFINER\\s(\\w+\\s)?@\\s(\\w+\\s)?)?(PROCEDURE|FUNCTION|EVENT))|TRIGGER)"
332
```
333

334
**Supported Patterns:**
335
```sql
336
CREATE PROCEDURE proc_name() ...
337
CREATE FUNCTION func_name() RETURNS INT ...
338
CREATE EVENT event_name ...
339
CREATE TRIGGER trigger_name ...
340
CREATE DEFINER=`user`@`host` PROCEDURE ...
341
```
342

343
### Control Structures
344

345
Block depth tracking for MySQL control structures:
346
- `BEGIN...END` blocks
347
- `IF...END IF` statements
348
- `WHILE...END WHILE` loops
349
- `REPEAT...UNTIL` loops
350
- `CASE...END CASE` statements
351

352
## MariaDB-Specific Features
353

354
### BEGIN NOT ATOMIC
355

356
MariaDB supports `BEGIN NOT ATOMIC` for compound statements:
357

358
```sql
359
BEGIN NOT ATOMIC
360
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
361
    INSERT INTO logs VALUES ('Starting operation');
362
    -- More statements
363
END;
364
```
365

366
**Parser Behavior:**
367
- Recognizes `BEGIN NOT ATOMIC` pattern
368
- Handles block depth correctly
369
- Supports MariaDB-specific error handling
370

371
### Statement Detection
372

373
MariaDB parser extends MySQL detection with:
374
- `BEGIN NOT ATOMIC` compound statements
375
- MariaDB-specific syntax variations
376
- Enhanced error handling constructs
377

378
## Comment Directives
379

380
MySQL comment directives provide version-specific SQL:
381

382
### Version-Specific Comments
383
```sql
384
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
385
/*!50717 SELECT JSON_EXTRACT(data, '$.key') FROM table1 */;
386
```
387

388
### Parser Processing
389
- Extracts version number from directive
390
- Preserves directive content
391
- Enables conditional SQL execution based on version
392

393
## Parser Configuration
394

395
### Block Depth Tracking
396

397
Parsers maintain block depth for:
398
- Stored procedure parsing
399
- Control structure nesting
400
- Transaction block handling
401
- Error handling blocks
402

403
### Context Management
404

405
Parser context tracks:
406
- Current delimiter setting
407
- Block nesting level
408
- Statement boundaries
409
- Token stream position
410

411
## Error Handling
412

413
### Parsing Errors
414

415
Common parsing scenarios:
416
- Malformed DELIMITER commands
417
- Unmatched quotes in string literals
418
- Invalid comment directive syntax
419
- Unterminated block structures
420

421
### Recovery Strategies
422

423
Parsers implement recovery for:
424
- Missing delimiter reset
425
- Incomplete string literals  
426
- Nested comment issues
427
- Block structure mismatches
428

429
## Performance Considerations
430

431
### Lookahead Optimization
432

433
Parsers use efficient lookahead for:
434
- Keyword detection
435
- Statement type classification
436
- Block structure analysis
437
- Comment boundary detection
438

439
### Token Stream Processing
440

441
Optimized processing for:
442
- Large SQL script files
443
- Complex stored procedures
444
- Deeply nested structures
445
- Multiple delimiter changes