Обсуждение: query manipulation

I'm trying to find a way to manipulate some poorly written queries in
which I don't have access to the application source.  I can't do it at
the Postgresql server, so I'm thinking of attacking it at the jdbc driver.

I was thinking of a wrapper that would look at and alter the SQL before
it went to the server.  Anyone know of something like this?
Specifically, I'm trying to drop "FOR UPDATE column name" off of some
queries.  They were written for Oracle, but Postgresql uses "FOR UPDATE
table name".  I'm not a Java programmer, but I won't let that stop me if
it is required.

Randall

Randall Smith wrote:
> I'm trying to find a way to manipulate some poorly written queries in
> which I don't have access to the application source.  I can't do it at
> the Postgresql server, so I'm thinking of attacking it at the jdbc driver.
>
> I was thinking of a wrapper that would look at and alter the SQL before
> it went to the server.  Anyone know of something like this?
> Specifically, I'm trying to drop "FOR UPDATE column name" off of some
> queries.  They were written for Oracle, but Postgresql uses "FOR UPDATE
> table name".  I'm not a Java programmer, but I won't let that stop me if
> it is required.
>
> Randall
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>

Sorry.  That's "FOR UPDATE OF column_name", not "FOR UPDATE column_name".

Randall

Randall,

Yeah, you would have to wrap the jdbc driver with a proxy driver.
It's not that hard to write one.

Dave
On 19-Mar-07, at 4:19 PM, Randall Smith wrote:

> I'm trying to find a way to manipulate some poorly written queries
> in which I don't have access to the application source.  I can't do
> it at the Postgresql server, so I'm thinking of attacking it at the
> jdbc driver.
>
> I was thinking of a wrapper that would look at and alter the SQL
> before it went to the server.  Anyone know of something like this?
> Specifically, I'm trying to drop "FOR UPDATE column name" off of
> some queries.  They were written for Oracle, but Postgresql uses
> "FOR UPDATE table name".  I'm not a Java programmer, but I won't
> let that stop me if it is required.
>
> Randall
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>

Randall,

I happen to have an example right here that might be useful for you.
The attached class will wrap a driver (in this case hard-coded to
"org.postgresql.Driver") and transparently implement prepared statement
caching.  Shouldn't be too hard to adapt it to your case.

Disclaimer: this is for demonstration only, not a production-quality
implementation.  Don't use this in your product without reading through
it, understanding the limitations and adding some sanity checking.
Also, this is based on a real class we use for testing here, but the
process of simplifying it for the list may have broken stuff.

-- Mark

On Mon, 2007-03-19 at 16:37 -0400, Dave Cramer wrote:
> Randall,
>
> Yeah, you would have to wrap the jdbc driver with a proxy driver.
> It's not that hard to write one.
>
> Dave
> On 19-Mar-07, at 4:19 PM, Randall Smith wrote:
>
> > I'm trying to find a way to manipulate some poorly written queries
> > in which I don't have access to the application source.  I can't do
> > it at the Postgresql server, so I'm thinking of attacking it at the
> > jdbc driver.
> >
> > I was thinking of a wrapper that would look at and alter the SQL
> > before it went to the server.  Anyone know of something like this?
> > Specifically, I'm trying to drop "FOR UPDATE column name" off of
> > some queries.  They were written for Oracle, but Postgresql uses
> > "FOR UPDATE table name".  I'm not a Java programmer, but I won't
> > let that stop me if it is required.
> >
> > Randall
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 7: You can help support the PostgreSQL project by donating at
> >
> >                http://www.postgresql.org/about/donate
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Mark Lewis wrote:
> Randall,
>
> I happen to have an example right here that might be useful for you.
> The attached class will wrap a driver (in this case hard-coded to
> "org.postgresql.Driver") and transparently implement prepared statement
> caching.  Shouldn't be too hard to adapt it to your case.
>
> Disclaimer: this is for demonstration only, not a production-quality
> implementation.  Don't use this in your product without reading through
> it, understanding the limitations and adding some sanity checking.
> Also, this is based on a real class we use for testing here, but the
> process of simplifying it for the list may have broken stuff.
>
> -- Mark
>
> On Mon, 2007-03-19 at 16:37 -0400, Dave Cramer wrote:
>> Randall,
>>
>> Yeah, you would have to wrap the jdbc driver with a proxy driver.
>> It's not that hard to write one.
>>
>> Dave
>> On 19-Mar-07, at 4:19 PM, Randall Smith wrote:
>>
>>> I'm trying to find a way to manipulate some poorly written queries
>>> in which I don't have access to the application source.  I can't do
>>> it at the Postgresql server, so I'm thinking of attacking it at the
>>> jdbc driver.
>>>
>>> I was thinking of a wrapper that would look at and alter the SQL
>>> before it went to the server.  Anyone know of something like this?
>>> Specifically, I'm trying to drop "FOR UPDATE column name" off of
>>> some queries.  They were written for Oracle, but Postgresql uses
>>> "FOR UPDATE table name".  I'm not a Java programmer, but I won't
>>> let that stop me if it is required.
>>>
>>> Randall
>>>
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 7: You can help support the PostgreSQL project by donating at
>>>
>>>                http://www.postgresql.org/about/donate
>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>> ------------------------------------------------------------------------
>>
>> package com.mir3.sawtooth.service.piimpl;
>>
>> import java.lang.reflect.InvocationHandler;
>> import java.lang.reflect.InvocationTargetException;
>> import java.lang.reflect.Method;
>> import java.lang.reflect.Proxy;
>> import java.sql.Connection;
>> import java.sql.Driver;
>> import java.sql.DriverManager;
>> import java.sql.DriverPropertyInfo;
>> import java.sql.PreparedStatement;
>> import java.sql.SQLException;
>> import java.util.Enumeration;
>> import java.util.Properties;
>>
>> import org.apache.commons.collections.LRUMap;
>>
>> /**
>>  * Wraps a real JDBC driver to implement prepared statement pooling.
>>  * @author Mark Lewis
>>  * Mar 19, 2007
>>  */
>> public class PoolingDriver implements Driver {
>>
>>     public PoolingDriver(Driver realDriver) {
>>         this.realDriver = realDriver;
>>     }
>>
>>     public Connection connect(String url, Properties info) throws SQLException {
>>         return wrap(realDriver.connect(url, info));
>>     }
>>
>>     public boolean acceptsURL(String url) throws SQLException {
>>         return realDriver.acceptsURL(url);
>>     }
>>
>>     public DriverPropertyInfo[] getPropertyInfo(String url, Properties info) throws SQLException {
>>         return realDriver.getPropertyInfo(url, info);
>>     }
>>
>>     public int getMajorVersion() {
>>         return realDriver.getMajorVersion();
>>     }
>>
>>     public int getMinorVersion() {
>>         return realDriver.getMinorVersion();
>>     }
>>
>>     public boolean jdbcCompliant() {
>>         return realDriver.jdbcCompliant();
>>     }
>>
>>     private Connection wrap(final Connection con) {
>>         ClassLoader loader = getClass().getClassLoader();
>>         return (Connection)Proxy.newProxyInstance(loader, new Class[]{Connection.class}, new InvocationHandler() {
>>             public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
>>                 try {
>>                     if(method.getReturnType().isInstance(PreparedStatement.class)) {
>>                         String sql = (String)args[0];
>>                         PreparedStatement ps = (PreparedStatement) statementCache.get(sql);
>>                         if(ps == null) {
>>                             ps = (PreparedStatement) method.invoke(con, args);
>>                             statementCache.put(sql, ps);
>>                         }
>>                         return ps;
>>                     }
>>                     else {
>>                         return method.invoke(con, args);
>>                     }
>>                 }
>>                 catch(InvocationTargetException ex) {
>>                     throw ex.getCause();
>>                 }
>>             }
>>
>>             private LRUMap statementCache = new LRUMap(100);
>>         });
>>     }
>>
>>     private Driver realDriver;
>>
>>     static {
>>         try {
>>             // Load the real class, then deregister its driver and register mine in its place.
>>             Class realClass = Class.forName("org.postgresql.Driver");
>>             Driver realDriver = null;
>>             for(Enumeration e=DriverManager.getDrivers(); e.hasMoreElements(); ) {
>>                 Driver d = (Driver) e.nextElement();
>>                 if(realClass.isInstance(d)) {
>>                     realDriver = d;
>>                     DriverManager.deregisterDriver(d);
>>                     break;
>>                 }
>>             }
>>             PoolingDriver driver = new PoolingDriver(realDriver);
>>             DriverManager.registerDriver(driver);
>>         }
>>         catch (Exception ex) {
>>             ex.printStackTrace();
>>         }
>>     }
>> }
>>
>> ------------------------------------------------------------------------
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings

This particular statement:

     if(method.getReturnType().isInstance(PreparedStatement.class))

is returning false for my prepared statement.  I'm printing the type
like this:

     System.out.println(method.getReturnType());

and it shows type type is:

     interface java.sql.PreparedStatement

I'm using Jython to test, and my test program looks like so:

from java.lang import *
from java.sql import *
Class.forName("randall.PGProxyDriver")
con = DriverManager.getConnection('jdbc:postgresql:mydb','myuser','mypass')
stmt = con.prepareStatement('select * from mytablename where myfield = ?')
stmt.setString(1, myvalue)
rs = stmt.executeQuery()

How do I match "interface java.sql.PreparedStatement" ?

Randall

Why couldn't I subclass PreparedStatement, then override
PreparedStatement.prepareStatement?  When I proxy it, I get about 70 of
these:

The type PreparedStatementProxy must implement the inherited abstract
method Statement.getMaxRows()

Randall

Dave Cramer wrote:
> You don't want to do that you want proxy PreparedStatement, then
> override PreparedStatement.prepareStatement
>
> He is checking the return type of every method to see what is returning
> a prepared statement
>
> Dave

Randall Smith wrote:
> Why couldn't I subclass PreparedStatement, then override
> PreparedStatement.prepareStatement?

Because PreparedStatement is an interface, not a class.

> When I proxy it, I get about 70 of
> these:
>
> The type PreparedStatementProxy must implement the inherited abstract
> method Statement.getMaxRows()

Dave meant using java.lang.reflect.Proxy not implementing the whole
class yourself (which you could do, it's just tedious)

-O

Thanks.

I promise I'm not a raving lunatic.  I've actually done this before in
Python, http://pydal.sourceforge.net/, but Java is new territory for me.

Randall

Oliver Jowett wrote:
> Randall Smith wrote:
>> Why couldn't I subclass PreparedStatement, then override
>> PreparedStatement.prepareStatement?
>
> Because PreparedStatement is an interface, not a class.
>
>> When I proxy it, I get about 70 of these:
>>
>> The type PreparedStatementProxy must implement the inherited abstract
>> method Statement.getMaxRows()
>
> Dave meant using java.lang.reflect.Proxy not implementing the whole
> class yourself (which you could do, it's just tedious)
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

Would this be a good tool to use here or would it create unnecessary work?

http://jakarta.apache.org/commons/dbutils/apidocs/org/apache/commons/dbutils/ProxyFactory.html

public java.sql.PreparedStatement
createPreparedStatement(java.lang.reflect.InvocationHandler handler)

Randall


Randall Smith wrote:
> I'm trying to find a way to manipulate some poorly written queries in
> which I don't have access to the application source.  I can't do it at
> the Postgresql server, so I'm thinking of attacking it at the jdbc driver.
>
> I was thinking of a wrapper that would look at and alter the SQL before
> it went to the server.  Anyone know of something like this?
> Specifically, I'm trying to drop "FOR UPDATE column name" off of some
> queries.  They were written for Oracle, but Postgresql uses "FOR UPDATE
> table name".  I'm not a Java programmer, but I won't let that stop me if
> it is required.
>
> Randall
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>

Randall,

It would work, but it appears the utility here is to make the
differences between JDBC versions transparent.

Dave
On 21-Mar-07, at 1:29 PM, Randall Smith wrote:

> Would this be a good tool to use here or would it create
> unnecessary work?
>
> http://jakarta.apache.org/commons/dbutils/apidocs/org/apache/
> commons/dbutils/ProxyFactory.html
>
> public java.sql.PreparedStatement createPreparedStatement
> (java.lang.reflect.InvocationHandler handler)
>
> Randall
>
>
> Randall Smith wrote:
>> I'm trying to find a way to manipulate some poorly written queries
>> in which I don't have access to the application source.  I can't
>> do it at the Postgresql server, so I'm thinking of attacking it at
>> the jdbc driver.
>> I was thinking of a wrapper that would look at and alter the SQL
>> before it went to the server.  Anyone know of something like this?
>> Specifically, I'm trying to drop "FOR UPDATE column name" off of
>> some queries.  They were written for Oracle, but Postgresql uses
>> "FOR UPDATE table name".  I'm not a Java programmer, but I won't
>> let that stop me if it is required.
>> Randall
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 7: You can help support the PostgreSQL project by donating at
>>                http://www.postgresql.org/about/donate
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

Turns out I was on the right track here.  Got to trust my intuition
sometimes.  It was merely a matter of changing this:

     if(method.getReturnType().isInstance(PreparedStatement.class)) {

to this:

     if(method.getReturnType() == PreparedStatement.class) {

and altering args.

Thanks to everyone and especially Mark for the code contribution.

-Randall

Randall Smith wrote:
> Mark Lewis wrote:
>> Randall,
>>
>> I happen to have an example right here that might be useful for you.
>> The attached class will wrap a driver (in this case hard-coded to
>> "org.postgresql.Driver") and transparently implement prepared statement
>> caching.  Shouldn't be too hard to adapt it to your case.
>>
>> Disclaimer: this is for demonstration only, not a production-quality
>> implementation.  Don't use this in your product without reading through
>> it, understanding the limitations and adding some sanity checking.
>> Also, this is based on a real class we use for testing here, but the
>> process of simplifying it for the list may have broken stuff.
>>
>> -- Mark
>>
>> On Mon, 2007-03-19 at 16:37 -0400, Dave Cramer wrote:
>>> Randall,
>>>
>>> Yeah, you would have to wrap the jdbc driver with a proxy driver.
>>> It's not that hard to write one.
>>>
>>> Dave
>>> On 19-Mar-07, at 4:19 PM, Randall Smith wrote:
>>>
>>>> I'm trying to find a way to manipulate some poorly written queries
>>>> in which I don't have access to the application source.  I can't do
>>>> it at the Postgresql server, so I'm thinking of attacking it at the
>>>> jdbc driver.
>>>>
>>>> I was thinking of a wrapper that would look at and alter the SQL
>>>> before it went to the server.  Anyone know of something like this?
>>>> Specifically, I'm trying to drop "FOR UPDATE column name" off of
>>>> some queries.  They were written for Oracle, but Postgresql uses
>>>> "FOR UPDATE table name".  I'm not a Java programmer, but I won't
>>>> let that stop me if it is required.
>>>>
>>>> Randall
>>>>
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 7: You can help support the PostgreSQL project by donating at
>>>>
>>>>                http://www.postgresql.org/about/donate
>>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 6: explain analyze is your friend
>>>
>>> ------------------------------------------------------------------------
>>>
>>> package com.mir3.sawtooth.service.piimpl;
>>>
>>> import java.lang.reflect.InvocationHandler;
>>> import java.lang.reflect.InvocationTargetException;
>>> import java.lang.reflect.Method;
>>> import java.lang.reflect.Proxy;
>>> import java.sql.Connection;
>>> import java.sql.Driver;
>>> import java.sql.DriverManager;
>>> import java.sql.DriverPropertyInfo;
>>> import java.sql.PreparedStatement;
>>> import java.sql.SQLException;
>>> import java.util.Enumeration;
>>> import java.util.Properties;
>>>
>>> import org.apache.commons.collections.LRUMap;
>>>
>>> /**
>>>  * Wraps a real JDBC driver to implement prepared statement pooling.
>>>  * @author Mark Lewis
>>>  * Mar 19, 2007
>>>  */
>>> public class PoolingDriver implements Driver {
>>>
>>>     public PoolingDriver(Driver realDriver) {
>>>         this.realDriver = realDriver;
>>>     }
>>>
>>>     public Connection connect(String url, Properties info) throws
>>> SQLException {
>>>         return wrap(realDriver.connect(url, info));
>>>     }
>>>
>>>     public boolean acceptsURL(String url) throws SQLException {
>>>         return realDriver.acceptsURL(url);
>>>     }
>>>
>>>     public DriverPropertyInfo[] getPropertyInfo(String url,
>>> Properties info) throws SQLException {
>>>         return realDriver.getPropertyInfo(url, info);
>>>     }
>>>
>>>     public int getMajorVersion() {
>>>         return realDriver.getMajorVersion();
>>>     }
>>>
>>>     public int getMinorVersion() {
>>>         return realDriver.getMinorVersion();
>>>     }
>>>
>>>     public boolean jdbcCompliant() {
>>>         return realDriver.jdbcCompliant();
>>>     }
>>>
>>>     private Connection wrap(final Connection con) {
>>>         ClassLoader loader = getClass().getClassLoader();
>>>         return (Connection)Proxy.newProxyInstance(loader, new
>>> Class[]{Connection.class}, new InvocationHandler() {
>>>             public Object invoke(Object proxy, Method method,
>>> Object[] args) throws Throwable {
>>>                 try {
>>>
>>> if(method.getReturnType().isInstance(PreparedStatement.class)) {
>>>                         String sql = (String)args[0];
>>>                         PreparedStatement ps = (PreparedStatement)
>>> statementCache.get(sql);
>>>                         if(ps == null) {
>>>                             ps = (PreparedStatement)
>>> method.invoke(con, args);
>>>                             statementCache.put(sql, ps);
>>>                         }
>>>                         return ps;
>>>                     }
>>>                     else {
>>>                         return method.invoke(con, args);
>>>                     }
>>>                 }
>>>                 catch(InvocationTargetException ex) {
>>>                     throw ex.getCause();
>>>                 }
>>>             }
>>>
>>>             private LRUMap statementCache = new LRUMap(100);
>>>         });
>>>     }
>>>
>>>     private Driver realDriver;
>>>
>>>     static {
>>>         try {
>>>             // Load the real class, then deregister its driver and
>>> register mine in its place.
>>>             Class realClass = Class.forName("org.postgresql.Driver");
>>>             Driver realDriver = null;
>>>             for(Enumeration e=DriverManager.getDrivers();
>>> e.hasMoreElements(); ) {
>>>                 Driver d = (Driver) e.nextElement();
>>>                 if(realClass.isInstance(d)) {
>>>                     realDriver = d;
>>>                     DriverManager.deregisterDriver(d);
>>>                     break;
>>>                 }
>>>             }
>>>             PoolingDriver driver = new PoolingDriver(realDriver);
>>>             DriverManager.registerDriver(driver);
>>>         }
>>>         catch (Exception ex) {
>>>             ex.printStackTrace();
>>>         }
>>>     }
>>> }
>>>
>>> ------------------------------------------------------------------------
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 5: don't forget to increase your free space map settings
>
> This particular statement:
>
>     if(method.getReturnType().isInstance(PreparedStatement.class))
>
> is returning false for my prepared statement.  I'm printing the type
> like this:
>
>     System.out.println(method.getReturnType());
>
> and it shows type type is:
>
>     interface java.sql.PreparedStatement
>
> I'm using Jython to test, and my test program looks like so:
>
> from java.lang import *
> from java.sql import *
> Class.forName("randall.PGProxyDriver")
> con = DriverManager.getConnection('jdbc:postgresql:mydb','myuser','mypass')
> stmt = con.prepareStatement('select * from mytablename where myfield = ?')
> stmt.setString(1, myvalue)
> rs = stmt.executeQuery()
>
> How do I match "interface java.sql.PreparedStatement" ?
>
> Randall
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>