I'm looking to write a "SQL Pretty Printer" editor action (which I think would be a useful core feature).
One approach is to write my own hard-coded parser, supporting a preset list of SQL statements and one or two different dialects. But rather than go down the hard-coded path, I'm wondering if anyone has suggestions as to a more generic way. I'm new to Eclipse, so I'm not sure if the editor classes in there already have a parser that I can leverage (or if SQLExplorer is already doing this).
Any recommendations and/or pointers to code would be greatly appreciated!
mobiGeek.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Here's another possible approach:
- include an xml configuration file in the core project that covers standard sql
- allow additional xml configuration files in every one of the extension projects
This way, every extension project could include it's own dialect specific statements.
There is no good parser in the SQL Explorer project, but there must be something out there in the open source world that could be reused.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
How far did you get with the parser, mobiGeek? I want to implement a parser to accurately split the text into individual SQL statements (IE instead of scanning for a character separator), maybe we could collaborate?
John
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Wow...timing is everything. I got pulled away from this (personal) project and just yesterday started thinking about it again (cleaning up my horribly abused to-do list).
I have not gone beyond emailing the ZQL author and asking about his project. He did email me the JavaCC parser (for which I am very grateful!), but mentions that ZQL itself is not OSS.
So we have a starting point for a SQL parser. Feel free to email me directly to discuss what you are looking. I just don't know when I'll have time to actually move this forward.
Thanks,
mG.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Ah todo lists - my no.1 item always seems to be "do todo list".... :)
I had a look at Zql just now, but when I downloaded it the most recent file modification change was Oct 2004.
I'm after a parser with just enough knowledge to figure out when a statement ends, but this becomes a proper pain with stored procedures; I actually wrote one about 6 or 7 years ago in C++ for Oracle's PL/SQL so I have a starting place, but a) it's not a proper parser, it just looks for certain keywords, b) it can break on some keywords that have come along since Oracle 7 (we're on v10 now), and c) I've lost the editor component it uses (doh!) so I can't recompile it without a major reworking (and it's in C++ etc).
I was looking around and discovered that both the JavaCC and ANTLR projects archive grammars for Oracle; ANTLR has other SQL grammars too. But the clincher might be that ANTLR also has a great-looking visual grammar development environment, complete with testing and debugging which could be a massive help.
From my POV (i.e. Oracle) JavaCC has the most recently maintained grammars, but OTOH ANTLR has more grammars and that development environment (maybe that's why there *are* more grammars). Here's the summary of what I've found:
JavaCC
------
Oracle 9i PL/SQL Aug 2006
PL/SQL used in OracleForms May 2005; claims to be the complete PL/SQL grammar
ANTLR
-----
Oracle PL/SQL Mar 2007 - comment says "...but still far from complete"
MSSQL SELECT statement Aug 2003
DmlSQL Jul 2003 comment says: "Standard ISO/ANSI SQL2 grammar; DML subset of the Full SQL Level"
SqlSQL2 Jul 2003 comment says: "Standard ISO/ANSI SQL2 grammar; The Full SQL Level extending DmlSQL2 grammar"
Oracle 7 SQL Junj 2003
Something I noticed with the JavaCC grammars is that they seem to be *just* the PL/SQL grammar, and not SQL so they don't include any of the DDL or administration constructs.
My inclination would be to go for ANTLR and maybe visually read the JavaCC grammar and use it to update the ANTLR stuff? Certainly if new database dialects are to be introduced we might be more likely to get contributions from people if there's a IDE. What do you reckon?
John
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am not partial either way. It has been a LONG, LONG time since I've looked at ANTLR...and back then JavaCC seemed to be in the lead technology wise. Looking at http://antlr.org/ I am apparently way behind the times...
Yes, ANTLR seems to be the way to go. I've become partial in three short sentences :-)
mG.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Me too - I think the last time I looked at compilers was using yacc and bison/flex ... not a pleasant memory. I read some comment on antlr.org about "..eliminating non-deterministic..." and it gave me a mental retch!
Do you have an idea about what kind of database you'd like to start on? Obviously if I had a choice to push something on you it'd be Oracle :) but that's just me. I do have a whole pile of real scripts I can try it out on, and I have a customer who'd be using it all day too so there'd be a reasonable test environment; in case you were up for it and don't have the software there's a free personal edition you can download from oracle.com and I'd be glad to help you get it set up if needs be.
I'm also thinking about how we can share the work - whether we'd need to sketch out some sort of API so we can both get the best out of it. I'm going to need a crude parser pretty soon, so I'm debating whether to just hack something together in the short term, or maybe we could make something that would be the basis for a proper parser between us.now. I think I'll do some reading over the weekend about compiler writing before I make my mind up. How are you fixed for time at the moment?
John.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
My time is horribly squeezed. If I was to add anything, I'd be leaning towards the TSQL servers (MS and Sybase), though I also have a medium-strong need for Oracle, DB2 and others to follow.
If you do move on anything in the next while, let's keep this thread alive.
Thanks and good weekend!
mG.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
My too - I've been flat out all week trying to get everything done in what little time I have. The good news is that I'm making good progress - I've written a generic tokenizer that should be suitable for most databases and I'm working on a rudimentary query parser; the code's abstracted so that the core parser is separate from database-specific language. Unfortunately while the tokenizer might be useful to you, I don't think the same would be true about the query parser. All I really need to look for is a few specific statements like "create" DDL statements and begin/end pairs so it's nowhere near a true grammar.
Have a look anyway; I'll be checking in a whole series of updates next week, I'm basically waiting to see if anyone has any objections to my changes to things like the JSE version and the editor before just rushing in and making an *rse of myself!
John
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm looking to write a "SQL Pretty Printer" editor action (which I think would be a useful core feature).
One approach is to write my own hard-coded parser, supporting a preset list of SQL statements and one or two different dialects. But rather than go down the hard-coded path, I'm wondering if anyone has suggestions as to a more generic way. I'm new to Eclipse, so I'm not sure if the editor classes in there already have a parser that I can leverage (or if SQLExplorer is already doing this).
Any recommendations and/or pointers to code would be greatly appreciated!
mobiGeek.
Sounds like a good idea.
Here's another possible approach:
- include an xml configuration file in the core project that covers standard sql
- allow additional xml configuration files in every one of the extension projects
This way, every extension project could include it's own dialect specific statements.
There is no good parser in the SQL Explorer project, but there must be something out there in the open source world that could be reused.
What is currently doing the syntax highlighting in the editor?
Where are those SQL keywords defined?
mobiGeek.
net.sourceforge.sqlexplorer.sqleditor.SQLCodeScanner is a good starting point..
Something like Zql?
http://www.experlog.com/gibello/zql/
mobiGeek.
How far did you get with the parser, mobiGeek? I want to implement a parser to accurately split the text into individual SQL statements (IE instead of scanning for a character separator), maybe we could collaborate?
John
Wow...timing is everything. I got pulled away from this (personal) project and just yesterday started thinking about it again (cleaning up my horribly abused to-do list).
I have not gone beyond emailing the ZQL author and asking about his project. He did email me the JavaCC parser (for which I am very grateful!), but mentions that ZQL itself is not OSS.
So we have a starting point for a SQL parser. Feel free to email me directly to discuss what you are looking. I just don't know when I'll have time to actually move this forward.
Thanks,
mG.
Hi mG,
Ah todo lists - my no.1 item always seems to be "do todo list".... :)
I had a look at Zql just now, but when I downloaded it the most recent file modification change was Oct 2004.
I'm after a parser with just enough knowledge to figure out when a statement ends, but this becomes a proper pain with stored procedures; I actually wrote one about 6 or 7 years ago in C++ for Oracle's PL/SQL so I have a starting place, but a) it's not a proper parser, it just looks for certain keywords, b) it can break on some keywords that have come along since Oracle 7 (we're on v10 now), and c) I've lost the editor component it uses (doh!) so I can't recompile it without a major reworking (and it's in C++ etc).
I was looking around and discovered that both the JavaCC and ANTLR projects archive grammars for Oracle; ANTLR has other SQL grammars too. But the clincher might be that ANTLR also has a great-looking visual grammar development environment, complete with testing and debugging which could be a massive help.
From my POV (i.e. Oracle) JavaCC has the most recently maintained grammars, but OTOH ANTLR has more grammars and that development environment (maybe that's why there *are* more grammars). Here's the summary of what I've found:
JavaCC
------
Oracle 9i PL/SQL Aug 2006
PL/SQL used in OracleForms May 2005; claims to be the complete PL/SQL grammar
ANTLR
-----
Oracle PL/SQL Mar 2007 - comment says "...but still far from complete"
MSSQL SELECT statement Aug 2003
DmlSQL Jul 2003 comment says: "Standard ISO/ANSI SQL2 grammar; DML subset of the Full SQL Level"
SqlSQL2 Jul 2003 comment says: "Standard ISO/ANSI SQL2 grammar; The Full SQL Level extending DmlSQL2 grammar"
Oracle 7 SQL Junj 2003
Something I noticed with the JavaCC grammars is that they seem to be *just* the PL/SQL grammar, and not SQL so they don't include any of the DDL or administration constructs.
My inclination would be to go for ANTLR and maybe visually read the JavaCC grammar and use it to update the ANTLR stuff? Certainly if new database dialects are to be introduced we might be more likely to get contributions from people if there's a IDE. What do you reckon?
John
I am not partial either way. It has been a LONG, LONG time since I've looked at ANTLR...and back then JavaCC seemed to be in the lead technology wise. Looking at http://antlr.org/ I am apparently way behind the times...
Yes, ANTLR seems to be the way to go. I've become partial in three short sentences :-)
mG.
Me too - I think the last time I looked at compilers was using yacc and bison/flex ... not a pleasant memory. I read some comment on antlr.org about "..eliminating non-deterministic..." and it gave me a mental retch!
Do you have an idea about what kind of database you'd like to start on? Obviously if I had a choice to push something on you it'd be Oracle :) but that's just me. I do have a whole pile of real scripts I can try it out on, and I have a customer who'd be using it all day too so there'd be a reasonable test environment; in case you were up for it and don't have the software there's a free personal edition you can download from oracle.com and I'd be glad to help you get it set up if needs be.
I'm also thinking about how we can share the work - whether we'd need to sketch out some sort of API so we can both get the best out of it. I'm going to need a crude parser pretty soon, so I'm debating whether to just hack something together in the short term, or maybe we could make something that would be the basis for a proper parser between us.now. I think I'll do some reading over the weekend about compiler writing before I make my mind up. How are you fixed for time at the moment?
John.
My time is horribly squeezed. If I was to add anything, I'd be leaning towards the TSQL servers (MS and Sybase), though I also have a medium-strong need for Oracle, DB2 and others to follow.
If you do move on anything in the next while, let's keep this thread alive.
Thanks and good weekend!
mG.
My too - I've been flat out all week trying to get everything done in what little time I have. The good news is that I'm making good progress - I've written a generic tokenizer that should be suitable for most databases and I'm working on a rudimentary query parser; the code's abstracted so that the core parser is separate from database-specific language. Unfortunately while the tokenizer might be useful to you, I don't think the same would be true about the query parser. All I really need to look for is a few specific statements like "create" DDL statements and begin/end pairs so it's nowhere near a true grammar.
Have a look anyway; I'll be checking in a whole series of updates next week, I'm basically waiting to see if anyone has any objections to my changes to things like the JSE version and the editor before just rushing in and making an *rse of myself!
John