Re: Patch implementing escaped functions timestampadd and

Here are the updated patchs.

Dave Cramer a écrit :
> Xavier,
>
> So at this point do you want to remove some of the less correct  escapes ?
>
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    24 Mar 2006 12:00:11 -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    24 Mar 2006 12:00:11 -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,114 ----
      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
!     // timestampdiff is very partially supported
!     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 ****
--- 495,583 ----
          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    24 Mar 2006 12:00:11 -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
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    24 Mar 2006 12:00:39 -0000
***************
*** 2040,2045 ****
--- 2040,2057 ----
         <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>not</entry>
+        <entry>extract((interval according to argIntervalType) from argTimeStamp2-argTimeStamp1  )</entry>
+        <entry>only an argIntervalType value of
<classname>SQL_TSI_FRAC_SECOND</classname>,<classname>SQL_TSI_FRAC_MINUTE</classname>,<classname>SQL_TSI_FRAC_HOUR</classname>
or<classname>SQL_TSI_FRAC_DAY</classname> is supported </entry> 
+       </row>
       </tbody>
      </tgroup>
     </table>