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 19:45:57 GMT
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. 


Mime
View raw message