Patch for jdbc escaped functions - Mailing list pgsql-jdbc

Hi,
I saw that escaped functions were on the TODO list.
Here is a patch that prepare for adding escaped functions.
It supports nested escapes.
As example I have coded the concat function.
Tests and metadata updates are included.
If the patch is accepted, I will add more functions.
Any comments are welcome.

Index: jdbc2/AbstractJdbc2DatabaseMetaData.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v
retrieving revision 1.16
diff -u -r1.16 AbstractJdbc2DatabaseMetaData.java
--- jdbc2/AbstractJdbc2DatabaseMetaData.java    9 Nov 2004 08:48:31 -0000    1.16
+++ jdbc2/AbstractJdbc2DatabaseMetaData.java    19 Nov 2004 10:17:47 -0000
@@ -475,7 +475,7 @@
     {
         if (Driver.logDebug)
             Driver.debug("getStringFunctions");
-        return "";
+        return EscapedFunctions.CONCAT;
     }

     public String getSystemFunctions() throws SQLException
Index: jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.50
diff -u -r1.50 AbstractJdbc2Statement.java
--- jdbc2/AbstractJdbc2Statement.java    17 Nov 2004 02:43:49 -0000    1.50
+++ jdbc2/AbstractJdbc2Statement.java    19 Nov 2004 10:17:53 -0000
@@ -655,8 +655,9 @@
      *
      * Currently implemented Escape clauses are those mentioned in 11.3
      * in the specification. Basically we look through the sql string for
-     * {d xxx}, {t xxx} or {ts xxx} in non-string sql code. When we find
-     * them, we just strip the escape part leaving only the xxx part.
+     * {d xxx}, {t xxx}, {ts xxx}, {oj xxx} or {fn xxx} in non-string sql
+     * code. When we find them, we just strip the escape part leaving only
+     * the xxx part.
      * So, something like "select * from x where d={d '2001-10-09'}" would
      * return "select * from x where d= '2001-10-09'".
      */
@@ -728,8 +729,33 @@
                     newsql.append(c);
                     break;

-                case ESC_TIMEDATE:
                 case ESC_FUNCTION:
+                    // extract function name
+                    String functionName;
+                    int posArgs = p_sql.indexOf('(',i);
+                    if (posArgs!=-1){
+                        functionName=p_sql.substring(i,posArgs).trim();
+                        // extract arguments
+                        i= posArgs+1;// we start the scan after the first (
+                        int nestedCount=0;
+                        while (i<len && !(p_sql.charAt(i)==')' && 0==nestedCount )){
+                            if ('('==p_sql.charAt(i))
+                                nestedCount++;
+                            else if (')'==p_sql.charAt(i))
+                                nestedCount--;
+                            i++;
+                        }
+                        String args=p_sql.substring(posArgs+1,i);
+                        // translate the function and parse arguments
+                        newsql.append(escapeFunction(functionName,args));
+                    }
+                    // go to the end of the function copying anything found
+                    i++;
+                    while (i<len && p_sql.charAt(i)!='}')
+                        newsql.append(p_sql.charAt(i));
+                    state = IN_SQLCODE; // end of escaped function (or query)
+                    break;
+                case ESC_TIMEDATE:
                 case ESC_OUTERJOIN:
                     if (c == '}')
                         state = IN_SQLCODE;    // end of escape code.
@@ -746,6 +772,54 @@
             return p_sql;
         }
     }
+    /**
+     * generate sql for escaped functions
+     * @param functionName the escaped function name
+     * @param args the arguments for this functin
+     * @return the right postgreSql sql
+     */
+    protected String escapeFunction(String functionName, String args){
+        // parse function arguments
+        int len = args.length();
+        int i=0;
+        ArrayList parsedArgs = new ArrayList();
+        while (i<len){
+            int nestedCount=0;
+            int lastPos=i;
+            while (i<len && !(args.charAt(i)==',' && 0==nestedCount )){
+                if ('('==args.charAt(i))
+                    nestedCount++;
+                else if (')'==args.charAt(i))
+                    nestedCount--;
+                i++;
+            }
+            if (lastPos!=i){
+                parsedArgs.add(replaceProcessing(args.substring(lastPos,i)));
+            }
+            i++;
+        }
+        // we can now tranlate escape functions
+        if (EscapedFunctions.CONCAT.equalsIgnoreCase(functionName)){
+            StringBuffer buf = new StringBuffer();
+            for (int iArg = 0;iArg<parsedArgs.size();iArg++){
+                buf.append(parsedArgs.get(iArg));
+                if (iArg!=(parsedArgs.size()-1))
+                    buf.append(" || ");
+            }
+            return buf.toString();
+        }else{
+            // by default the function name is kept unchanged
+            StringBuffer buf = new StringBuffer();
+            buf.append(functionName).append('(');
+            for (int iArg = 0;iArg<parsedArgs.size();iArg++){
+                buf.append(parsedArgs.get(iArg));
+                if (iArg!=(parsedArgs.size()-1))
+                    buf.append(',');
+            }
+            buf.append(')');
+            return buf.toString();
+        }
+    }

     /*
      *
Index: test/jdbc2/StatementTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/StatementTest.java,v
retrieving revision 1.9
diff -u -r1.9 StatementTest.java
--- test/jdbc2/StatementTest.java    9 Nov 2004 08:55:11 -0000    1.9
+++ test/jdbc2/StatementTest.java    19 Nov 2004 10:17:54 -0000
@@ -39,6 +39,7 @@
     {
         super.tearDown();
         TestUtil.dropTable( con, "test_statement" );
+        TestUtil.dropTable( con, "escapetest" );
         con.close();
     }

@@ -116,10 +117,23 @@
         count = stmt.executeUpdate("insert into escapetest (t) values ({t '00:00:00'})");
         assertEquals(1, count);

-
         ResultSet rs = stmt.executeQuery( "select {fn version()} as version" );
         assertTrue(rs.next());
-
+
+        // check default replacement as-is
+        rs = stmt.executeQuery( "select {fn log(2,3)} as log" );
+        assertTrue(rs.next());
+
+        // check nested and multiple escaped functions
+        rs = stmt.executeQuery( "select {fn version()} as version, {fn log({fn log(3,2)},3)} as log" );
+        assertTrue(rs.next());
+
+        // check concat
+        rs = stmt.executeQuery( "select {fn concat('ab','cd')} as concat" );
+        assertTrue(rs.next());
+        assertEquals(rs.getString(1),"abcd");
+
+
         count = stmt.executeUpdate( "create temp table b (i int)" );
         assertEquals(0, count);

Index: jdbc2/EscapedFunctions.java
===================================================================
RCS file: jdbc2/EscapedFunctions.java
diff -N jdbc2/EscapedFunctions.java
--- /dev/null    1 Jan 1970 00:00:00 -0000
+++ jdbc2/EscapedFunctions.java    1 Jan 1970 00:00:00 -0000
@@ -0,0 +1,16 @@
+/*-------------------------------------------------------------------------
+*
+* Copyright (c) 2004, PostgreSQL Global Development Group
+*
+* IDENTIFICATION
+*
+*-------------------------------------------------------------------------
+*/
+package org.postgresql.jdbc2;
+
+/**
+ * this class stores supported escaped function
+ */
+public class EscapedFunctions {
+    public final static String CONCAT="concat";
+}