Parse SQL for comments
lazarus / 16-Databases
----------------------------------------------------------------------
aradeonas · 2016-05-17 20:18
Hi,
I got some SQL code and I want to parse for comments and I want to
know is there any library that already implement such functionality
that I can use?
for example :
Code: [Select]select 1;
-- select 2;
select 3;
/* select 4
select 5;
*/
select 6;
----------------------------------------------------------------------
Edson · 2016-05-17 21:04
You can use my syntax highlighter:
https://github.com/t-edson/SynFacilSyn
(who is a good parser too).
and some SQL syntax definition like: https://github.com/t-edson/Tito-
s-Terminal/blob/master/lenguajes/SQLOracle.xml
Although, the lines to parse teh comments are just:
Quote
----------------------------------------------------------------------
aradeonas · 2016-05-17 21:14
Very good but how can I get these comments and uncommented text?
----------------------------------------------------------------------
Edson · 2016-05-17 21:44
Well, to extract tokens of a string from a SynEdit, you will need some
code like this:
Code: Pascal [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //defines
the syntax ... //explores xLex.ResetRange; //to initiate "frange"
for lin in SynEdit1.lines do begin xLex.SetLine(lin, 0); //xLex is
the highlighter while not xLex.GetEol do begin //do something
with the current token ... xLex.Next; //passes to the next
end; end;
This is described in the Documentation, Chapter 5.19: https://github.c
om/t-edson/SynFacilSyn/blob/1.15/Docs/Technical%20documentation%20-
%20Rev1.odt
Porbably the sample project:
https://github.com/t-edson/SynFacilSyn/tree/1.15/Sample_lexer
can help more.
----------------------------------------------------------------------
aradeonas · 2016-05-17 22:03
I thought about a simpler solution with SQL related codes like using
SQLDB or Zeos becuase I dont need Syn related stuff for such not
complicated job but if there isn't I will go with your way.
You made an interesting lib, asking out of curiosity: I dont know
SynEdit well but what your lib add to it?
----------------------------------------------------------------------
Edson · 2016-05-17 22:19
Quote from: aradeonas on May 18, 2016, 12:03:59 amI thought about a
simpler solution with SQL related codes like using SQLDB or Zeos
becuase I dont need Syn related stuff
Using SynFacilSyn, is simple (considering the lot of features it
includes). The syntax can be configured using an XML file or directly
by code. The library is just two units. No need for package.
Maybe if you explain more, what do you want to achieve, I can help
more.
Quote from: aradeonas on May 18, 2016, 12:03:59 amYou made an
interesting lib, asking out of curiosity: I dont know SynEdit well but
what your lib add to it?
SynEdit is a component than implements a text Editor, that supports
syntax highlighting. It come with some simple highlighters.
SynFacilSyn is a full configurable highlighter-lexer, designed to work
with SynEdit. It makes easy to implement fast and powerfull
highlighters (including code-folding), for several languages.
----------------------------------------------------------------------
aradeonas · 2016-05-18 07:31
Thanks.
As I said I want to simply parse SQL parts to commands and comments so
:
Code: [Select]select 1;
-- select 2;
select 3;
/* select 4
select 5;
*/
select 6I can get select 1; and select 3; and select 6 as commands and
others as comment. I dont want a complicated thin I just want to have
my SQL usefull parts.
----------------------------------------------------------------------
aradeonas · 2016-05-18 07:33
Nice work by the way but just a quick question and it seems it is not
really about your work and its about SynEdit, why scroll of component
seems slow and not smooth as Lazarus editor itself?
----------------------------------------------------------------------
marcov · 2016-05-18 07:48
iirc there is a separate sql parser on sf.net called jansql. I'm not
entirely sure, but maybe the sqldb parser is based on it.
----------------------------------------------------------------------
Edson · 2016-05-18 14:47
Quote from: aradeonas on May 18, 2016, 09:31:57 amI can get select 1;
and select 3; and select 6 as commands and others as comment.
This give me some questions:
1. When you say: "you can get "select 1" as command", Do you refer to
obtain all this text in a variable?
2. Does the command "select 1" always have one-line command or it
could be a multi-line command, like a common SQL sentence?
3. Are SQL commands, always separated by comments? This means, it
could never be two consecutive SQL commands without a comment between
them?
4. SQL commands are always the form: "SELECT ..." or they could be
"DELETE FROM...", "INSERT ..."?
----------------------------------------------------------------------
aradeonas · 2016-05-18 15:12
Thanks.
In one answer : I just want all sql commands and comments no matter
count or or anything so I can run sql commands and show comments.
now answers:
1- yes these are sample commands and in real cases sql commands will
be much more complicated.
2- no.
3- no. it is just an example.
4- maybe all of them. if the system can find out what is comment there
should be no problem.
I need a system to find out what is comment and what is not and
separate them by ";".
----------------------------------------------------------------------
Edson · 2016-05-18 18:49
I think you will need a full SQL lexer-parser, for that.
However, if you just want to focus on comments and commands (anything
between comments), the attachment can help you.
This sample is using SynFacilSyn to extract tokens usign an XML syntax
definition for SQL.
----------------------------------------------------------------------
marcov · 2016-05-18 18:52
A normal lexer/parser throws away comments already in the lexer stage
----------------------------------------------------------------------
Edson · 2016-05-18 18:59
Quote from: marcov on May 18, 2016, 08:52:24 pmA normal lexer/parser
throws away comments already in the lexer stage
Yes. That's the usual, and that's what I would do.
I think the main function of a SQL parser would be to extract
individually the SQL sentences and it's parts.
----------------------------------------------------------------------
aradeonas · 2016-05-18 19:00
Thank you very much for the demo.
It seems maybe Im missing something but how can I find out what is
these commands that it will show is comment or not, it now make it
parted very good but how can I find out which one of them is runable
and with one is comment?
@marcov: how?
----------------------------------------------------------------------
Edson · 2016-05-18 19:09
Quote from: aradeonas on May 18, 2016, 09:00:03 pmhow can I find out
what is these commands that it will show is comment or not
It's easy. Just replace the line:
Code: Pascal [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
ShowMessage(txt);by:
Code: Pascal [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if
lastIsComment then ShowMessage('Comment:' + txt) else
ShowMessage('Command:' + txt);
Consider that according this code, everything between comments are
considered commands, no matter if it's one or multiple SQL sentences.
----------------------------------------------------------------------
Edson · 2016-05-18 19:20
Quote from: aradeonas on May 18, 2016, 09:33:11 ama quick question and
it seems it is not really about your work and its about SynEdit, why
scroll of component seems slow and not smooth as Lazarus editor
itself?
I don't notice the difference. Maybe Martin can give us some
additional information.
----------------------------------------------------------------------
marcov · 2016-05-18 19:43
Quote from: aradeonas on May 18, 2016, 09:00:03 pmThank you very much
for the demo.
It seems maybe Im missing something but how can I find out what is
these commands that it will show is comment or not, it now make it
parted very good but how can I find out which one of them is runable
and with one is comment?
@marcov: how?
My point was that a standard SQL parser won't be any good for parsing
sql statements separated by comments only, since that is not valid
SQL, and the way you want to fix it (valid SQL separated by commas)
happens in two different layers (comments are killed already in the
lexer but valid sql is only determined in the parser)
So you will need something custom you probably won't find ready-made.
Smartest is to try to find some own set of heuristics to fix it (if
comment on new line, and line before is like ... then insert ; or
something like it). Parsing theory won't really help you here.
----------------------------------------------------------------------
aradeonas · 2016-05-18 20:22
Thanks to you two especially Edson for the demo, now I have something
that I can work with.
@marcov, Thank you so much for helping me everyday, I always use your
helps