Defining a User Defined Language on top of SQL

Question

Ok, so our basic idea is to construct a language on top of SQL. The main goal is for us to pre-process a string containing SQL mixed up with different stuff, parse our "invented" language, produce SQL on the other end and eventually execute that SQL string.

It would be something similar to what ASP/PHP do over HTML (I know this is semantically incorrect, I'm just looking for the syntax part)

We already have a parser which works like a charm, but as queries keep getting longer and longer, and we keep on adding new functionality into the "language", I'd be glad if I got Notepad++ to highlight my syntax properly.

Code Sample:

WHERE PARENT_ID = {GETVAR|PARENT_ID**'}

What this essentially does is get the variable named PARENT_ID, and it stuffs it in that place surrounded by two '. It would look like

WHERE PARENT_ID = 'a parent'

The thing I keep struggling with is that single quotation mark, which is unmatched, and SHOULD NOT be recognized as an SQL string, since it's inside our litle "function". How to correctly tell notepad++ to avoid such single quotation marks if inside { and }?

Thank you very much.

Answer 1

You could create a user-defined language by starting with the PL-SQL example and adding your extensions. It's not as powerful as an internal parser but it would allow you:

1. add ' as a new keyword, for example,
2. add * to the operators,
3. and create a new delimiter {} that allows operators and the new keyword inside of it.

This would exclude the '...' delimiter and allow a ' keyword, which is what you intended.

Note that keywords need to be separated and ' will only be a keyword if * is an operator (thus working as a separator). Also you may need to write \' instead of just '.