MySQL support (DELIMITER)

[nene]

Support the DELIMITER command used by the mysql client, as in:

delimiter //

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
   SET @x = 0;
   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END
//

delimiter ;

There are many other commands that the mysql client supports, but delimiter is one of the most frequently used ones. Unfortunately it's also the trickiest to support.

[TonyGravagno]

This could be difficult because the Peggy parser can't scan forward to a semi-colon if the point of Delimiter is to provide an alternate to the semi-colon.

For the opening of the block, how about just treating DELIMITER anything as a synonym for a comment? It'll be ignored like --. That will also handle the reset DELIMITER ;.

About the termination of the block, like where // is on a line by itself, Joe Hildebrand (Peggy) suggests something like this:

statement
  = assignment ";"
  / functionCall ";"
  / [^;]* ";" 

That will skip anything that is invalid up to the next terminator. This will allow the parser to process any .sql file in any dialect, simply ignoring whatever it doesn't understand and allowing it to terminate correctly without the PeggySyntaxError here.

I have zero experience with Peggy but that syntax seems to leave open the idea that a terminating delimiter like // and everything else will be skipped until the next semi-colon. That kind of globbing will also skip other valid code. I think it needs to only skip until the next EOL, so:
/ [^;]* "\n"

Anything good in there?

[nene]

As you're hinting, this approach is only useful for skipping the parsing of all the code that's using a different delimiter. I think it's better applied to the general ignoring of unsupported syntax.

My thinking about the handling of DELIMITER and other mysql client commands is that they should be parsed before the actual parsing of the SQL.

So that in first pass one would parse a stream of client commands:

[use my_database]
[SELECT * FROM tbl;]
[delimiter //]
[SELECT * FROM tbl1//]
[SELECT * FROM tbl2//]
[delimiter ;]

And then the SQL-commands would be fed to the actual SQL parser for further parsing. This process would mimic how the mysql client parses the commands and server parses the pure SQL statements.

This first phase of parsing might need to be done with something different than Peggy to be able to handle this changing delimiter character. I'm not 100% confident, but I have a strong feeling that this changing in the meaning of the delimiter character changes this into a context-dependent grammar (in contrast to context-free grammars that ordinary parser-generators tackle). Thankfully the syntax is pretty simple, so hand-rolling a parser for that shouldn't be that hard.