db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Extracting dependencies from SQL statements
Date Tue, 09 Mar 2010 20:39:25 GMT
Hi Pavel,

Some comments inline...

Pavel Bortnovskiy wrote:
>
> Rick:
>
> your second point grossly understimated the amount of time it took me 
> to get frustrated... it was not quick - it was instanteneous - as soon 
> as I opened sqlgrammar.jj....  ;-D
>
> Last question, then, on the subject. Is there any way to easily 
> simplify sqlgrammar.jj, so that it frees itself of all the derby 
> dependencies and so that
> javacc'ed code can just take a string and give me some kind of lexical 
> tree?
This sounds like a frustrating approach too. If you are willing to hack 
the Derby code and create your owned forked version, then I would 
recommend going into GenericStatement.java and hacking the prepMinion() 
method so that you can control the short-circuiting yourself. Look for 
the string StopAfterParsing.
>
> Alternatively, javacc project has various .jj files in its repository, 
> two of which are PL/SQL-based (there is one for Oracle 9i).
> Perhaps using those .jj files may work for my purposes?.. Your 
> thoughts would be appreciated.
I have never looked at those grammars so I can't advise you here. This 
approach also will be peppered with frustrations if what you want to 
achieve is a Derby-powered app which performs some extra dependency 
tracking. Since the javacc-supplied grammars aren't the Derby grammar, 
statements which parse in one grammar may not parse in the other.

So let's step back for a moment. What do you need to achieve here:

o Are you writing a Derby-powered app or just trying to use the Derby 
parser against statements which actually run in some other database?

o If this is a Derby-powered app, do you need to track the dependencies 
before the statement is executed?

Thanks,
-Rick
>
> Thank you for your time and attention.
>
> Pavel.
>
>
>
>
>
> *Rick Hillegas <Richard.Hillegas@Sun.COM>*
> Sent by: Richard.Hillegas@Sun.COM
>
> 03/09/2010 02:45 PM
> Please respond to
> "Derby Discussion" <derby-user@db.apache.org>
>
>
> 	
> To
> 	Derby Discussion <derby-user@db.apache.org>
> cc
> 	
> Subject
> 	Re: Extracting dependencies from SQL statements
>
>
>
> 	
>
>
>
>
>
> Hi Pavel,
>
> Some comments inline...
>
> Pavel Bortnovskiy wrote:
> >
> > Rick (while I am reading  and trying DERBY-4415), that seems like
> > there is no other way to use prod jars and yet stop the parser from
> > binding?
>
> It looks to me as though the shortcircuiting logic is guarded by a check
> for whether the engine is the debug version.
> >
> > Would it be at all possible to use the javacc compiler with
> > sqlgrammar.jj (that are probably bundled in derby.jar) to do the work?
> I think you will discover that the grammar relies on a fair amount of
> session context supplied by the rest of the Derby SQL interpreter. I
> think you will be frustrated very quickly if you try this approach.
>
> Hope this helps,
> -Rick
> >
> > Pavel.
> >
> >
> >
> >
> >
> > *Rick Hillegas <Richard.Hillegas@Sun.COM>*
> > Sent by: Richard.Hillegas@Sun.COM
> >
> > 03/09/2010 01:13 PM
> > Please respond to
> > "Derby Discussion" <derby-user@db.apache.org>
> >
> >
> >                  
> > To
> >                  Derby Discussion <derby-user@db.apache.org>
> > cc
> >                  
> > Subject
> >                  Re: Extracting dependencies from SQL statements
> >
> >
> >
> >                  
> >
> >
> >
> >
> >
> > Hi Pavel,
> >
> > You are right, the XmlTreeWalker class also tries to stop the compiler
> > after parsing. This can only be done with a debug engine. The error you
> > are seeing comes from the bind() phase of compilation. Derby is
> > complaining that the query doesn't make sense: the objects it mentions
> > don't exist.
> >
> > There's another issue linked from the XmlTreeWalker issue. Take a look
> > at DERBY-4415. This is a slightly more involved solution but may get you
> > closer. This solution assumes that you are trying to examine the various
> > phases of the AST for a query against real objects in your database.
> > This should work with the production jars.
> >
> > Hope this is more useful,
> > -Rick
> >
> > Pavel Bortnovskiy wrote:
> > >
> > > Once again, Rick, thanks for your prompt response. To answer your
> > > questions:
> > >
> > > (1) The compilation errors initially had to do with the SanityManager
> > > and ContextId not available in prod jars.
> > >
> > > (2) Since I am trying to get this code to work in our application,
> > > using debug jars may not be desirable.
> > > More over, our app is using Derby's in-memory tables as well as its
> > > NetworkServerController.
> > > So, this parsing/tree-walking code should not conflict with the other
> > > two uses.
> > >
> > > In the comments for DERBY-791, I did notice "We should provide some
> > > mechanism for printing these trees in production (non-debug) servers".
> > > So, I ran XmlTreeWalker linking only to
> > > db-derby-10.5.3.0-bin/lib/derby.jar, but got the same exception:
> > >
> > > <?xml version="1.0" encoding="UTF-8"?>
> > > <queryTree>
> > >     <queryText>select a from t, s where t.a = s.a</queryText>
> > > Exception in thread "main" java.sql.SQLSyntaxErrorException:
> > > Table/View 'T' does not exist.
> > >         at
> > >
> > org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> > >         at
> > >
> > 
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
>
> >
> > > Source)
> > >         at
> > >
> > 
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
>
> >
> > > Source)
> > >         at
> > > org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown 
> Source)
> > >         at
> > > org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown 
> Source)
> > >         at
> > > org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> > > Source)
> > >         at XmlTreeWalker.execute(XmlTreeWalker.java:130)
> > >         at XmlTreeWalker.main(XmlTreeWalker.java:111)
> > >         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > >         at
> > >
> > 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 
>
> >
> > >
> > >         at
> > >
> > 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

>
> >
> > >
> > >         at java.lang.reflect.Method.invoke(Method.java:597)
> > >         at
> > > com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)
> > > Caused by: java.sql.SQLException: Table/View 'T' does not exist.
> > >         at
> > > org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> > > Source)
> > >         at
> > >
> > 
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown

>
> >
> > > Source)
> > >         ... 20 more
> > > Caused by: ERROR 42X05: Table/View 'T' does not exist.
> > >         at
> > > org.apache.derby.iapi.error.StandardException.newException(Unknown
> > > Source)
> > >         at
> > >
> > 
> org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown 
>
> >
> > > Source)
> > >         at
> > >
> > org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
> > >         at
> > > org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown 
> Source)
> > >         at
> > > org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown
> > > Source)
> > >         at
> > > org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> > >         at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown
> > > Source)
> > >         at
> > >
> > 
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown

>
> >
> > > Source)
> > >         ... 14 more
> > >
> > > Regards,
> > > Pavel.
> > >
> > >
> > >
> > >
> > >
> > > *Rick Hillegas <Richard.Hillegas@Sun.COM>*
> > > Sent by: Richard.Hillegas@Sun.COM
> > >
> > > 03/09/2010 11:22 AM
> > > Please respond to
> > > "Derby Discussion" <derby-user@db.apache.org>
> > >
> > >
> > >                  
> > > To
> > >                  Derby Discussion <derby-user@db.apache.org>
> > > cc
> > >                  
> > > Subject
> > >                  Re: Extracting dependencies from SQL statements
> > >
> > >
> > >
> > >                  
> > >
> > >
> > >
> > >
> > >
> > > Hi Pavel,
> > >
> > > These programs should be run using the Derby debug jars. I have edited
> > > the introductory comment to try to make this clear. The debug 
> logic must
> > > be compiled into Derby in order for compilation to stop after parsing.
> > > If you need a solution which runs against the production jars, take a
> > > look at the related issue DERBY-791.
> > >
> > > Hope this helps,
> > > -Rick
> > >
> > > Pavel Bortnovskiy wrote:
> > > >
> > > > I was able to compile both files by disabling SanityManager method
> > > > invocation and replacing ContextId.LANG_CONNECTION with its string
> > > > "LanguageConnectionContext".
> > > > No other changes to ASTParser and TreeWalker have been done, 
> however,
> > > > when running them the following exception is thrown:
> > > >
> > > > Parsing:
> > > > select a from t, s where t.a = s.a
> > > > Exception in thread "main" java.sql.SQLSyntaxErrorException:
> > > > Table/View 'T' does not exist.
> > > >         at
> > > >
> > > 
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> > > > Source)
> > > >         at
> > > > org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown 
> Source)
> > > >         at
> > > >
> > >
> > 
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
>
> >
> > >
> > > > Source)
> > > >         at
> > > >
> > >
> > 
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
>
> >
> > >
> > > > Source)
> > > >         at
> > > > org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
> > > > Source)
> > > >         at
> > > > org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
> > > > Source)
> > > >         at
> > > > org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
> > Source)
> > > >         at
> > > > org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
> > > > Source)
> > > >         at
> > > > org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
> > > > Source)
> > > >         at
> > > > org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
> > > > Source)
> > > >         at
> > > > org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
> > Source)
> > > >         at
> > > > org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> > > > Source)
> > > >         at
> > > > org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> > > > Source)
> > > >         at TreeWalker.execute(TreeWalker.java:95)
> > > >         at TreeWalker.main(TreeWalker.java:79)
> > > >         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native 
> Method)
> > > >         at
> > > >
> > >
> > 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 
>
> >
> > >
> > > >
> > > >         at
> > > >
> > >
> > 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

>
> >
> > >
> > > >
> > > >         at java.lang.reflect.Method.invoke(Method.java:597)
> > > >         at
> > > > com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > *Rick Hillegas <Richard.Hillegas@Sun.COM>*
> > > > Sent by: Richard.Hillegas@Sun.COM
> > > >
> > > > 03/09/2010 08:35 AM
> > > > Please respond to
> > > > "Derby Discussion" <derby-user@db.apache.org>
> > > >
> > > >
> > > >                  
> > > > To
> > > >                  Derby Discussion <derby-user@db.apache.org>
> > > > cc
> > > >                  
> > > > Subject
> > > >                  Re: Extracting dependencies from SQL statements
> > > >
> > > >
> > > >
> > > >                  
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Hi Pavel,
> > > >
> > > > I don't know of an easy way to do this. You could run the statement
> > > > through the Derby parser to get the parsed representation, the
> > Abstract
> > > > Syntax Tree. Then you could write a Visitor to walk the AST,
> > looking for
> > > > the nodes which represent tables. See the following JIRAs for some
> > > > pointers on how to produce and walk the AST: DERBY-3946 and 
> DERBY-791.
> > > >
> > > > Unfortunately, there is no systematic primer on the AST nodes
> > > > themselves. All we have is the javadoc for the package
> > > > org.apache.derby.impl.sql.compile.
> > > >
> > > > Hope this helps,
> > > > -Rick
> > > >
> > > > Pavel Bortnovskiy wrote:
> > > > >
> > > > > Hello:
> > > > >
> > > > > is it possible to use Derby's SQL parser to "extract" dependencies
> > > > > from a given SQL statement?
> > > > > (or access the parser once the statement has been parsed).
> > > > >
> > > > > Whether it's a simple SELECT or a JOIN, UNION or a more complex
> > > > > statement, I would like to get a list of tables that this 
> statement
> > > > > would depend on.
> > > > > Looking for FROM clauses and attempting to do the parsing myself
> > seems
> > > > > like a difficult, error prone and impractical way to approach 
> this.
> > > > >
> > > > > Any suggestions, please?
> > > > >
> > > > > Thanks,
> > > > > Pavel.
> > > > >
> > > > >
> > > > >
> > > > > Jefferies archives and monitors outgoing and incoming e-mail. The
> > > > > contents of this email, including any attachments, are
> > confidential to
> > > > > the ordinary user of the email address to which it was 
> addressed. If
> > > > > you are not the addressee of this email you may not copy, forward,
> > > > > disclose or otherwise use it or any part of it in any form
> > whatsoever.
> > > > > This email may be produced at the request of regulators or in
> > > > > connection with civil litigation. Jefferies accepts no 
> liability for
> > > > > any errors or omissions arising as a result of transmission. 
> Use by
> > > > > other than intended recipients is prohibited.  In the United
> > Kingdom,
> > > > > Jefferies operates as Jefferies International Limited; 
> registered in
> > > > > England: no. 1978621; registered office: Vintners Place, 68 Upper
> > > > > Thames Street, London EC4V 3BJ.  Jefferies International 
> Limited is
> > > > > authorised and regulated by the Financial Services Authority.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Jefferies archives and monitors outgoing and incoming e-mail. The
> > > > contents of this email, including any attachments, are 
> confidential to
> > > > the ordinary user of the email address to which it was addressed. If
> > > > you are not the addressee of this email you may not copy, forward,
> > > > disclose or otherwise use it or any part of it in any form 
> whatsoever.
> > > > This email may be produced at the request of regulators or in
> > > > connection with civil litigation. Jefferies accepts no liability for
> > > > any errors or omissions arising as a result of transmission. Use by
> > > > other than intended recipients is prohibited.  In the United 
> Kingdom,
> > > > Jefferies operates as Jefferies International Limited; registered in
> > > > England: no. 1978621; registered office: Vintners Place, 68 Upper
> > > > Thames Street, London EC4V 3BJ.  Jefferies International Limited is
> > > > authorised and regulated by the Financial Services Authority.
> > >
> > >
> > >
> > >
> > >
> > >
> > > Jefferies archives and monitors outgoing and incoming e-mail. The
> > > contents of this email, including any attachments, are confidential to
> > > the ordinary user of the email address to which it was addressed. If
> > > you are not the addressee of this email you may not copy, forward,
> > > disclose or otherwise use it or any part of it in any form whatsoever.
> > > This email may be produced at the request of regulators or in
> > > connection with civil litigation. Jefferies accepts no liability for
> > > any errors or omissions arising as a result of transmission. Use by
> > > other than intended recipients is prohibited.  In the United Kingdom,
> > > Jefferies operates as Jefferies International Limited; registered in
> > > England: no. 1978621; registered office: Vintners Place, 68 Upper
> > > Thames Street, London EC4V 3BJ.  Jefferies International Limited is
> > > authorised and regulated by the Financial Services Authority.
> >
> >
> >
> >
> >
> >
> > Jefferies archives and monitors outgoing and incoming e-mail. The
> > contents of this email, including any attachments, are confidential to
> > the ordinary user of the email address to which it was addressed. If
> > you are not the addressee of this email you may not copy, forward,
> > disclose or otherwise use it or any part of it in any form whatsoever.
> > This email may be produced at the request of regulators or in
> > connection with civil litigation. Jefferies accepts no liability for
> > any errors or omissions arising as a result of transmission. Use by
> > other than intended recipients is prohibited.  In the United Kingdom,
> > Jefferies operates as Jefferies International Limited; registered in
> > England: no. 1978621; registered office: Vintners Place, 68 Upper
> > Thames Street, London EC4V 3BJ.  Jefferies International Limited is
> > authorised and regulated by the Financial Services Authority.
>
>
>
>
>
>
> Jefferies archives and monitors outgoing and incoming e-mail. The 
> contents of this email, including any attachments, are confidential to 
> the ordinary user of the email address to which it was addressed. If 
> you are not the addressee of this email you may not copy, forward, 
> disclose or otherwise use it or any part of it in any form whatsoever. 
> This email may be produced at the request of regulators or in 
> connection with civil litigation. Jefferies accepts no liability for 
> any errors or omissions arising as a result of transmission. Use by 
> other than intended recipients is prohibited.  In the United Kingdom, 
> Jefferies operates as Jefferies International Limited; registered in 
> England: no. 1978621; registered office: Vintners Place, 68 Upper 
> Thames Street, London EC4V 3BJ.  Jefferies International Limited is 
> authorised and regulated by the Financial Services Authority. 


Mime
View raw message