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

Dave Cramer a écrit :
> Xavier,
>
> Thanks, any chance we could get a context diff instead of a plain diff ?

I should be better.

>
> Also I looked at it briefly, can you change the if (SQL_TSI_DAY. ....
> checks to check for "SQL_TSI" , before you even create the  stringbuffer
> in sqltimestampdiff, and sqltimestampadd

done.

>
> Ideally it would be good to verify all of the functions in the test .

That's a good point since I found several unexpected problems with the
backend implementation of extract which makes severals SQL_TSI
unavailable for timestampdiff :

 select extract(month from interval '92 days');
 date_part
-----------
         0
(1 row)

test=>  select extract(year from interval '900 days');
 date_part
-----------
         0
(1 row)

test=>  select extract(quarter from interval '900 days');
 date_part
-----------
         1
(1 row)


>
> Dave
>


Index: pgjdbc.xml
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/doc/pgjdbc.xml,v
retrieving revision 1.27
diff -c -r1.27 pgjdbc.xml
*** pgjdbc.xml    24 Nov 2005 06:36:03 -0000    1.27
--- pgjdbc.xml    22 Mar 2006 14:43:27 -0000
***************
*** 2040,2045 ****
--- 2040,2058 ----
         <entry>extract(year from arg1)</entry>
         <entry></entry>
        </row>
+       <row>
+        <entry>timestampadd(argIntervalType,argCount,argTimeStamp)</entry>
+        <entry>yes</entry>
+        <entry>('(interval according to argIntervalType and argCount)'+argTimeStamp)</entry>
+        <entry>an argIntervalType value of <classname>SQL_TSI_FRAC_SECOND</classname> is not implemented since backend
doesnot support it</entry> 
+       </row>
+       <row>
+        <entry>timestampdiff(argIntervalType,argTimeStamp1,argTimeStamp2)</entry>
+        <entry>yes</entry>
+        <entry>extract((interval according to argIntervalType) from argTimeStamp2-argTimeStamp1  )</entry>
+        <entry>an argIntervalType value of <classname>SQL_TSI_FRAC_SECOND</classname> or
<classname>SQL_TSI_WEEK</classname>are not implemented since backend does not support it. 
+        You may note that SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR are giving unexpected results since the backend
assumesthat there are 0 year in 900 days, 1 quarter in 900 days or 0 month in 92 days. </entry> 
+       </row>
       </tbody>
      </tgroup>
     </table>
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 14:47:44 -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 14:47:44 -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,112 ----
      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_DAY="SQL_TSI_DAY";
!     public final static String SQL_TSI_FRAC_SECOND="SQL_TSI_FRAC_SECOND";
!     public final static String SQL_TSI_HOUR="SQL_TSI_HOUR";
!     public final static String SQL_TSI_MINUTE="SQL_TSI_MINUTE";
!     public final static String SQL_TSI_MONTH="SQL_TSI_MONTH";
!     public final static String SQL_TSI_QUARTER="SQL_TSI_QUARTER";
!     public final static String SQL_TSI_SECOND="SQL_TSI_SECOND";
!     public final static String SQL_TSI_WEEK="SQL_TSI_WEEK";
!     public final static String SQL_TSI_YEAR="SQL_TSI_YEAR";

+
      // system functions
      public final static String DATABASE="database";
      public final static String IFNULL="ifnull";
***************
*** 478,483 ****
--- 493,573 ----
          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 (SQL_TSI_DAY.equalsIgnoreCase(type))
+             return "'"+value+" day'";
+         else if (SQL_TSI_SECOND.equalsIgnoreCase(type))
+             return "'"+value+" second'";
+         else if (SQL_TSI_HOUR.equalsIgnoreCase(type))
+             return "'"+value+" hour'";
+         else if (SQL_TSI_MINUTE.equalsIgnoreCase(type))
+             return "'"+value+" minute'";
+         else if (SQL_TSI_MONTH.equalsIgnoreCase(type))
+             return "'"+value+" month'";
+         else if (SQL_TSI_QUARTER.equalsIgnoreCase(type))
+             return "'"+ Integer.valueOf(value).intValue()*3+" month'";
+         else if (SQL_TSI_WEEK.equalsIgnoreCase(type))
+             return "'"+value+" week'";
+         else if (SQL_TSI_YEAR.equalsIgnoreCase(type))
+             return "'"+value+" year'";
+         else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(type))
+             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 (SQL_TSI_DAY.equalsIgnoreCase(type))
+             return "day";
+         else if (SQL_TSI_SECOND.equalsIgnoreCase(type))
+             return "second";
+         else if (SQL_TSI_HOUR.equalsIgnoreCase(type))
+             return "hour";
+         else if (SQL_TSI_MINUTE.equalsIgnoreCase(type))
+             return "minute";
+         else if (SQL_TSI_MONTH.equalsIgnoreCase(type))
+             return "month";
+         else if (SQL_TSI_QUARTER.equalsIgnoreCase(type))
+             return "quarter";
+         else if (SQL_TSI_WEEK.equalsIgnoreCase(type))
+             return "week";
+         else if (SQL_TSI_YEAR.equalsIgnoreCase(type))
+             return "year";
+         else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(type))
+             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 14:47:45 -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