Re: [pgsql-jdbc] dollar-quoted CREATE FUNCTION statement fails

Michael Paesold wrote:
> Dave Cramer wrote:
>  >> I guess it's just a matter of coding, right? Would the JDBC
>  >> maintainers accept a patch for the 8.2 release of the driver?
>  > Absolutely!
>
> Ok, I am going to write support for this... [snip]

Here is a first version of the patch. It works well for me so far, so I
am asking for comments now.

This patch only adds knowledge about dollar-quotes to
v3.QueryExecutorImpl.parseQuery() for now, but there are other possible
candidates:
- V2Query: for people connecting to postgres >= 8.0 using
   ?protocolVersion=2
- AbstractJdbc2Statement.modifyJdbcCall() for prepareCall: I would not
   expect someone to use dollar-quoting here, but single-quotes are
   supported, so adding dollar-quoting does not seem completely off
   track.

I have tried to make the patch rather non-invasive. I thought about
improving parseQuery to be more efficient, but without having done any
profiling, I did not want to considerably change the existing code
paths. All JUnit tests still pass running against an 8.1 server.

p1-remove-unused-var.patch:

org/postgresql/core/v3/QueryExecutorImpl.java:
- Remove an unused variable from parseQuery

p2-v3protocol-dolquot.patch:

org/postgresql/core/Utils.java:
- Add static methods isDollarQuoteStartChar(char) and
   isDollarQuoteContChar(char)

org/postgresql/core/v3/QueryExecutorImpl.java:
- Add support for dollar-quoting in parseQuery

org/postgresql/test/jdbc3/Jdbc3DollarQuotingTest.java:
- New file with JUnit tests for new code

org/postgresql/test/jdbc3/Jdbc3TestSuite.java:
- Add the new JUnit TestCase to the test suite if server >= 8.0 and
   protocolVersion == 3

Best Regards
Michael Paesold
diff -cr pgjdbc-orig/org/postgresql/core/v3/QueryExecutorImpl.java pgjdbc/org/postgresql/core/v3/QueryExecutorImpl.java
*** pgjdbc-orig/org/postgresql/core/v3/QueryExecutorImpl.java    Sat Sep 30 19:45:14 2006
--- pgjdbc/org/postgresql/core/v3/QueryExecutorImpl.java    Sat Sep 30 19:37:41 2006
***************
*** 63,69 ****
          ArrayList statementList = new ArrayList();
          ArrayList fragmentList = new ArrayList(15);

-         boolean inQuotes = false;
          int fragmentStart = 0;

          boolean inSingleQuotes = false;
--- 63,68 ----
diff -crN pgjdbc-orig/org/postgresql/core/Utils.java pgjdbc/org/postgresql/core/Utils.java
*** pgjdbc-orig/org/postgresql/core/Utils.java    Tue Jan 11 09:25:43 2005
--- pgjdbc/org/postgresql/core/Utils.java    Sun Oct  1 14:59:57 2006
***************
*** 53,56 ****
--- 53,81 ----
              throw new RuntimeException("Unexpected exception: UTF-8 charset not supported: " + e);
          }
      }
+
+     /**
+      * Checks if a character is valid as the start of a dollar quoting tag.
+      *
+      * @param c the character to check
+      * @return true if valid as first character of a dollar quoting tag; false if not
+      */
+     public static boolean isDollarQuoteStartChar(char c) {
+         return (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z')
+                 || c == '_' || c > 127;
+     }
+
+     /**
+      * Checks if a character is valid as the second or latter character of a
+      * dollar quoting tag.
+      *
+      * @param c the character to check
+      * @return true if valid as second or later character of a dollar quoting tag;
+      *         false if not
+      */
+     public static boolean isDollarQuoteContChar(char c) {
+         return (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z')
+                 || c == '_' || c > 127
+                 || (c >= '0' && c <= '9');
+     }
  }
diff -crN pgjdbc-orig/org/postgresql/core/v3/QueryExecutorImpl.java
pgjdbc/org/postgresql/core/v3/QueryExecutorImpl.java
*** pgjdbc-orig/org/postgresql/core/v3/QueryExecutorImpl.java    Sat Sep 30 21:30:26 2006
--- pgjdbc/org/postgresql/core/v3/QueryExecutorImpl.java    Sun Oct  1 14:56:18 2006
***************
*** 67,72 ****
--- 67,74 ----

          boolean inSingleQuotes = false;
          boolean inDoubleQuotes = false;
+         boolean inDollarQuotes = false;
+         String dollarQuoteTag = null;
          int inParen = 0;

          char []aChars = query.toCharArray();
***************
*** 83,97 ****
                  break;

              case '\'':
!                 inSingleQuotes = !inDoubleQuotes && !inSingleQuotes;
                  break;

              case '"':
!                 inDoubleQuotes = !inSingleQuotes && !inDoubleQuotes;
                  break;

              case '?':
!                 if (withParameters && !inSingleQuotes && !inDoubleQuotes)
                  {
                      fragmentList.add(query.substring(fragmentStart, i));
                      fragmentStart = i + 1;
--- 85,99 ----
                  break;

              case '\'':
!                 inSingleQuotes = !inDoubleQuotes && !inDollarQuotes && !inSingleQuotes;
                  break;

              case '"':
!                 inDoubleQuotes = !inSingleQuotes && !inDollarQuotes && !inDoubleQuotes;
                  break;

              case '?':
!                 if (withParameters && !inSingleQuotes && !inDoubleQuotes && !inDollarQuotes)
                  {
                      fragmentList.add(query.substring(fragmentStart, i));
                      fragmentStart = i + 1;
***************
*** 99,115 ****
                  break;

              case '(':
!                 if (!inSingleQuotes && !inDoubleQuotes)
                          inParen++;
                  break;

              case ')':
!                 if (!inSingleQuotes && !inDoubleQuotes)
                          inParen--;
                  break;

              case ';':
!                 if (!inSingleQuotes && !inDoubleQuotes && inParen == 0)
                  {
                      fragmentList.add(query.substring(fragmentStart, i));
                      fragmentStart = i + 1;
--- 101,164 ----
                  break;

              case '(':
!                 if (!inSingleQuotes && !inDoubleQuotes && !inDollarQuotes)
                          inParen++;
                  break;

              case ')':
!                 if (!inSingleQuotes && !inDoubleQuotes && !inDollarQuotes)
                          inParen--;
                  break;

+             case '$':
+                 if (!inSingleQuotes && !inDoubleQuotes) {
+                     if (!inDollarQuotes)
+                     {
+                         // possible dollar quote start
+                         if (i + 1 < aChars.length)
+                         {
+                             int endIdx = -1;
+                             if (aChars[i + 1] == '$')
+                                 endIdx = i + 1;
+                             else if (Utils.isDollarQuoteStartChar(aChars[i + 1]))
+                             {
+                                 for (int d = i + 2; d < aChars.length; ++d)
+                                 {
+                                     if (aChars[d] == '$')
+                                     {
+                                         endIdx = d;
+                                         break;
+                                     }
+                                     else if (!Utils.isDollarQuoteContChar(aChars[d]))
+                                         break;
+                                 }
+                             }
+                             if (endIdx > 0)
+                             {
+                                 inDollarQuotes = true;
+                                 // dollarQuoteTag includes start and end $ character
+                                 dollarQuoteTag = query.substring(i, endIdx + 1);
+                                 i = endIdx; // main loop continues at endIdx + 1
+                             }
+                         }
+                     }
+                     else
+                     {
+                         // possible dollar quote end
+                         if (i + dollarQuoteTag.length() <= aChars.length &&
+                                 query.substring(i, i + dollarQuoteTag.length()).equals(
+                                         dollarQuoteTag))
+                         {
+                             inDollarQuotes = false;
+                             i += dollarQuoteTag.length() - 1;
+                             dollarQuoteTag = null;
+                         }
+                     }
+                 }
+                 break;
+
              case ';':
!                 if (!inSingleQuotes && !inDoubleQuotes && !inDollarQuotes && inParen == 0)
                  {
                      fragmentList.add(query.substring(fragmentStart, i));
                      fragmentStart = i + 1;
diff -crN pgjdbc-orig/org/postgresql/test/jdbc3/Jdbc3DollarQuotingTest.java
pgjdbc/org/postgresql/test/jdbc3/Jdbc3DollarQuotingTest.java
*** pgjdbc-orig/org/postgresql/test/jdbc3/Jdbc3DollarQuotingTest.java    Thu Jan  1 01:00:00 1970
--- pgjdbc/org/postgresql/test/jdbc3/Jdbc3DollarQuotingTest.java    Sun Oct  1 14:46:54 2006
***************
*** 0 ****
--- 1,139 ----
+ /*-------------------------------------------------------------------------
+  *
+  * Copyright (c) 2006, PostgreSQL Global Development Group
+  *
+  * IDENTIFICATION
+  *   $PostgreSQL$
+  *
+  *-------------------------------------------------------------------------
+  */
+ package org.postgresql.test.jdbc3;
+
+ import org.postgresql.test.TestUtil;
+
+ import java.sql.*;
+
+ import junit.framework.TestCase;
+
+ /*
+  * Test for dollar-quoting
+  */
+ public class Jdbc3DollarQuotingTest extends TestCase {
+
+     private Connection con;
+
+     public Jdbc3DollarQuotingTest(String name) {
+         super(name);
+     }
+
+     protected void setUp() throws Exception
+     {
+         con = TestUtil.openDB();
+         TestUtil.createTable(con, "testdquot", "t text");
+         Statement stmt = con.createStatement();
+
+         stmt.executeUpdate(TestUtil.insertSQL("testdquot", "';'"));
+         stmt.executeUpdate(TestUtil.insertSQL("testdquot", "'?'"));
+         stmt.executeUpdate(TestUtil.insertSQL("testdquot", "'$a$ $a$'"));
+         stmt.executeUpdate(TestUtil.insertSQL("testdquot", "'$a$''$b$a$'"));
+         stmt.executeUpdate(TestUtil.insertSQL("testdquot", "';$b'"));
+         stmt.executeUpdate(TestUtil.insertSQL("testdquot", "'c$;'"));
+
+         stmt.close();
+     }
+
+     protected void tearDown() throws Exception
+     {
+         TestUtil.dropTable(con, "testdquot");
+         TestUtil.closeDB(con);
+     }
+
+     public void testSimple() throws SQLException
+     {
+         Statement st = con.createStatement();
+         ResultSet rs;
+
+         rs = st.executeQuery("SELECT t FROM testdquot WHERE t='$a$ $a$'");
+         assertTrue(rs.next());
+         assertEquals("$a$ $a$", rs.getObject(1));
+         rs.close();
+
+         rs = st.executeQuery("SELECT t FROM testdquot WHERE t=$$;$$");
+         assertTrue(rs.next());
+         assertEquals(";", rs.getObject(1));
+         rs.close();
+
+         rs = st.executeQuery("SELECT t FROM testdquot WHERE t=$OR$$a$'$b$a$$OR$OR t=';;'");
+         assertTrue(rs.next());
+         assertEquals("$a$'$b$a$", rs.getObject(1));
+         assertFalse(rs.next());
+         rs.close();
+
+         rs = st.executeQuery("SELECT t FROM testdquot WHERE t=$B$;$b$B$");
+         assertTrue(rs.next());
+         assertEquals(";$b", rs.getObject(1));
+         rs.close();
+
+         rs = st.executeQuery("SELECT t FROM testdquot WHERE t=$c$c$;$c$");
+         assertTrue(rs.next());
+         assertEquals("c$;", rs.getObject(1));
+         rs.close();
+
+         rs = st.executeQuery("SELECT t FROM testdquot WHERE t=$t$t$t$ OR t=';'");
+         assertTrue(rs.next());
+         assertEquals(";", rs.getObject(1));
+         assertFalse(rs.next());
+         rs.close();
+
+         st.executeUpdate("UPDATE testdquot SET t=';;' WHERE t=$q0$;$q0$;"
+                 + "UPDATE testdquot SET t='' WHERE t=';;'");
+         rs = st.executeQuery("SELECT t FROM testdquot WHERE t=''");
+         assertTrue(rs.next());
+         assertEquals("", rs.getObject(1));
+         assertFalse(rs.next());
+         rs.close();
+
+         st.close();
+     }
+
+     public void testParameterized() throws SQLException
+     {
+         PreparedStatement st;
+         ResultSet rs;
+
+         st = con.prepareStatement("SELECT t FROM testdquot WHERE t=$$?$$ AND t=?");
+         st.setString(1, "?");
+         rs = st.executeQuery();
+         assertTrue(rs.next());
+         assertEquals("?", rs.getString(1));
+         assertFalse(rs.next());
+         st.close();
+
+         st = con.prepareStatement("SELECT t FROM testdquot WHERE t=$q_1$'$q_1$ OR t=?;"
+                 + "SELECT t FROM testdquot WHERE t=? OR t=$c$c$;$c$;"
+                 + "SELECT t FROM testdquot WHERE t=?");
+         st.setString(1, ";");
+         st.setString(2, ";");
+         st.setString(3, "$a$ $a$");
+
+         assertTrue(st.execute());
+         rs = st.getResultSet();
+         assertTrue(rs.next());
+         assertEquals(";", rs.getString(1));
+         assertFalse(rs.next());
+
+         assertTrue(st.getMoreResults());
+         rs = st.getResultSet();
+         assertTrue(rs.next());
+         assertTrue(rs.next());
+         assertFalse(rs.next());
+
+         assertTrue(st.getMoreResults());
+         rs = st.getResultSet();
+         assertTrue(rs.next());
+         assertEquals("$a$ $a$", rs.getString(1));
+         assertFalse(rs.next());
+
+     }
+
+ }
diff -crN pgjdbc-orig/org/postgresql/test/jdbc3/Jdbc3TestSuite.java
pgjdbc/org/postgresql/test/jdbc3/Jdbc3TestSuite.java
*** pgjdbc-orig/org/postgresql/test/jdbc3/Jdbc3TestSuite.java    Mon May 15 11:35:57 2006
--- pgjdbc/org/postgresql/test/jdbc3/Jdbc3TestSuite.java    Sun Oct  1 15:17:30 2006
***************
*** 34,39 ****
--- 34,43 ----
              {
                  suite.addTestSuite(Jdbc3CallableStatementTest.class);
              }
+             if ( TestUtil.haveMinimumServerVersion(con, "8.0") && TestUtil.isProtocolVersion(con, 3))
+             {
+                 suite.addTestSuite(Jdbc3DollarQuotingTest.class);
+             }
              con.close();
          }
          catch (Exception ex )