Re: Patch implementing escaped functions timestampadd and - Mailing list pgsql-jdbc

Dave Cramer a écrit :
> Xavier,
>
> Sorry I wasn't clear what I was trying to avoid is this
>
> you have 10 (didn't count) or so compares for SQL_TSI_*
>
> so what I am suggesting is that you compare for SQL_TSI_ and then
> compare for DAY, HOUR, etc in the sqltimestampdiff/sqltimestampadd
>
> You can determine quickly before creating the StringBuffer if you
> should even check for DAY,HOUR, etc.
>
> Plus the overhead of redundant checking of SQL_TSI_ is avoided.
>
> I realize these are nitpicky kinds of things, but I've recently  become
> aware of certain java performance issues
>

The optimized version ...
Index: jdbc2/AbstractJdbc2DatabaseMetaData.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v
retrieving revision 1.29
diff -c -r1.29 AbstractJdbc2DatabaseMetaData.java
*** jdbc2/AbstractJdbc2DatabaseMetaData.java    3 Feb 2006 21:10:15 -0000    1.29
--- jdbc2/AbstractJdbc2DatabaseMetaData.java    22 Mar 2006 16:16:54 -0000
***************
*** 477,483 ****
          ','+EscapedFunctions.MONTH+
          ','+EscapedFunctions.MONTHNAME+','+EscapedFunctions.NOW+
          ','+EscapedFunctions.QUARTER+','+EscapedFunctions.SECOND+
!         ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEAR;
      }

      /*
--- 477,484 ----
          ','+EscapedFunctions.MONTH+
          ','+EscapedFunctions.MONTHNAME+','+EscapedFunctions.NOW+
          ','+EscapedFunctions.QUARTER+','+EscapedFunctions.SECOND+
!         ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEAR+
!         ','+EscapedFunctions.TIMESTAMPADD+','+EscapedFunctions.TIMESTAMPDIFF;
      }

      /*
Index: jdbc2/EscapedFunctions.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/EscapedFunctions.java,v
retrieving revision 1.6
diff -c -r1.6 EscapedFunctions.java
*** jdbc2/EscapedFunctions.java    4 Jun 2005 18:24:08 -0000    1.6
--- jdbc2/EscapedFunctions.java    22 Mar 2006 16:16:54 -0000
***************
*** 90,97 ****
      public final static String SECOND="second";
      public final static String WEEK="week";
      public final static String YEAR="year";
!     // TODO : timestampadd and timestampdiff

      // system functions
      public final static String DATABASE="database";
      public final static String IFNULL="ifnull";
--- 90,113 ----
      public final static String SECOND="second";
      public final static String WEEK="week";
      public final static String YEAR="year";
!     // for timestampadd and timestampdiff the fractional part of second is not supported
!     // by the backend
!     public final static String TIMESTAMPADD="timestampadd";
!     public final static String TIMESTAMPDIFF="timestampdiff";
!
!     // constants for timestampadd and timestampdiff
!     public final static String SQL_TSI_ROOT="SQL_TSI_";
!     public final static String SQL_TSI_DAY="DAY";
!     public final static String SQL_TSI_FRAC_SECOND="FRAC_SECOND";
!     public final static String SQL_TSI_HOUR="HOUR";
!     public final static String SQL_TSI_MINUTE="MINUTE";
!     public final static String SQL_TSI_MONTH="MONTH";
!     public final static String SQL_TSI_QUARTER="QUARTER";
!     public final static String SQL_TSI_SECOND="SECOND";
!     public final static String SQL_TSI_WEEK="WEEK";
!     public final static String SQL_TSI_YEAR="YEAR";

+
      // system functions
      public final static String DATABASE="database";
      public final static String IFNULL="ifnull";
***************
*** 478,483 ****
--- 494,582 ----
          return "extract(year from "+parsedArgs.get(0)+")";
      }

+     /** time stamp add */
+     public static String sqltimestampadd(List parsedArgs) throws SQLException{
+         if (parsedArgs.size()!=3){
+             throw new PSQLException(GT.tr("{0} function takes three and only three arguments.","timestampadd"),
+                                     PSQLState.SYNTAX_ERROR);
+         }
+         String interval =
EscapedFunctions.constantToInterval(parsedArgs.get(0).toString(),parsedArgs.get(1).toString());
+         StringBuffer buf = new StringBuffer();
+         buf.append("(interval ").append(interval)
+             .append("+").append(parsedArgs.get(2)).append(")");
+         return buf.toString();
+     }
+
+     private final static String constantToInterval(String type,String value)throws SQLException{
+         if (!type.startsWith(SQL_TSI_ROOT))
+             throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+                     PSQLState.SYNTAX_ERROR);
+         String shortType = type.substring(SQL_TSI_ROOT.length());
+         if (SQL_TSI_DAY.equalsIgnoreCase(shortType))
+             return "'"+value+" day'";
+         else if (SQL_TSI_SECOND.equalsIgnoreCase(shortType))
+             return "'"+value+" second'";
+         else if (SQL_TSI_HOUR.equalsIgnoreCase(shortType))
+             return "'"+value+" hour'";
+         else if (SQL_TSI_MINUTE.equalsIgnoreCase(shortType))
+             return "'"+value+" minute'";
+         else if (SQL_TSI_MONTH.equalsIgnoreCase(shortType))
+             return "'"+value+" month'";
+         else if (SQL_TSI_QUARTER.equalsIgnoreCase(shortType))
+             return "'"+ Integer.valueOf(value).intValue()*3+" month'";
+         else if (SQL_TSI_WEEK.equalsIgnoreCase(shortType))
+             return "'"+value+" week'";
+         else if (SQL_TSI_YEAR.equalsIgnoreCase(shortType))
+             return "'"+value+" year'";
+         else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(shortType))
+             throw new PSQLException(GT.tr("Interval {0} not yet implemented","SQL_TSI_FRAC_SECOND"),
+                     PSQLState.SYNTAX_ERROR);
+         else throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+                 PSQLState.SYNTAX_ERROR);
+     }
+
+
+     /** time stamp diff */
+     public static String sqltimestampdiff(List parsedArgs) throws SQLException{
+         if (parsedArgs.size()!=3){
+             throw new PSQLException(GT.tr("{0} function takes three and only three arguments.","timestampdiff"),
+                                     PSQLState.SYNTAX_ERROR);
+         }
+         String datePart = EscapedFunctions.constantToDatePart(parsedArgs.get(0).toString());
+         StringBuffer buf = new StringBuffer();
+         buf.append("extract( ").append(datePart)
+         .append(" from (").append(parsedArgs.get(2)).append("-").append(parsedArgs.get(1)).append("))");
+         return buf.toString();
+     }
+
+     private final static String constantToDatePart(String type)throws SQLException{
+         if (!type.startsWith(SQL_TSI_ROOT))
+             throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+                     PSQLState.SYNTAX_ERROR);
+         String shortType = type.substring(SQL_TSI_ROOT.length());
+         if (SQL_TSI_DAY.equalsIgnoreCase(shortType))
+             return "day";
+         else if (SQL_TSI_SECOND.equalsIgnoreCase(shortType))
+             return "second";
+         else if (SQL_TSI_HOUR.equalsIgnoreCase(shortType))
+             return "hour";
+         else if (SQL_TSI_MINUTE.equalsIgnoreCase(shortType))
+             return "minute";
+         else if (SQL_TSI_MONTH.equalsIgnoreCase(shortType))
+             return "month";
+         else if (SQL_TSI_QUARTER.equalsIgnoreCase(shortType))
+             return "quarter";
+         else if (SQL_TSI_WEEK.equalsIgnoreCase(shortType))
+             return "week";
+         else if (SQL_TSI_YEAR.equalsIgnoreCase(shortType))
+             return "year";
+         else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(shortType))
+             throw new PSQLException(GT.tr("Interval {0} not yet implemented","SQL_TSI_FRAC_SECOND"),
+                     PSQLState.SYNTAX_ERROR);
+         else throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+                 PSQLState.SYNTAX_ERROR);
+     }
+
      /** database translation */
      public static String sqldatabase(List parsedArgs) throws SQLException{
          if (parsedArgs.size()!=0){
Index: test/jdbc2/StatementTest.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/StatementTest.java,v
retrieving revision 1.19
diff -c -r1.19 StatementTest.java
*** test/jdbc2/StatementTest.java    1 Feb 2006 18:52:13 -0000    1.19
--- test/jdbc2/StatementTest.java    22 Mar 2006 16:16:54 -0000
***************
*** 297,302 ****
--- 297,334 ----
          assertTrue(rs.next());
          // ensure sunday =>1 and monday =>2
          assertEquals(2,rs.getInt(5));
+         // second
+         rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_SECOND,{fn now()},{fn
timestampadd(SQL_TSI_SECOND,3,{fnnow()})})} "); 
+         assertTrue(rs.next());
+         assertEquals(3,rs.getInt(1));
+         //      MINUTE
+         rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_MINUTE,{fn now()},{fn
timestampadd(SQL_TSI_MINUTE,3,{fnnow()})})} "); 
+         assertTrue(rs.next());
+         assertEquals(3,rs.getInt(1));
+         //      HOUR
+         rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_HOUR,{fn now()},{fn timestampadd(SQL_TSI_HOUR,3,{fn
now()})})}"); 
+         assertTrue(rs.next());
+         assertEquals(3,rs.getInt(1));
+         //      day
+         rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_DAY,{fn now()},{fn timestampadd(SQL_TSI_DAY,3,{fn
now()})})}"); 
+         assertTrue(rs.next());
+         assertEquals(3,rs.getInt(1));
+         //      WEEK => extract week from interval is not supported by backend
+         //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_WEEK,{fn now()},{fn
timestampadd(SQL_TSI_WEEK,3,{fnnow()})})} "); 
+         //assertTrue(rs.next());
+         //assertEquals(3,rs.getInt(1));
+         //      MONTH => backend assume there are 0 month in an interval of 92 days...
+         //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_MONTH,{fn now()},{fn
timestampadd(SQL_TSI_MONTH,3,{fnnow()})})} "); 
+         //assertTrue(rs.next());
+         //assertEquals(3,rs.getInt(1));
+         //      QUARTER => backend assume there are 1 quater even in 270 days...
+         //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_QUARTER,{fn now()},{fn
timestampadd(SQL_TSI_QUARTER,3,{fnnow()})})} "); 
+         //assertTrue(rs.next());
+         //assertEquals(3,rs.getInt(1));
+         //      YEAR
+         //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_YEAR,{fn now()},{fn
timestampadd(SQL_TSI_YEAR,3,{fnnow()})})} "); 
+         //assertTrue(rs.next());
+         //assertEquals(3,rs.getInt(1));
      }

      public void testSystemFunctions() throws SQLException