persistence@glassfish.java.net

Re: Mapping of CURRENT_DATE, CURRENT_TIMESTAMP, CURRENT_TIME functions of JPA

From: Tom Ware <tom.ware_at_oracle.com>
Date: Fri, 12 Jan 2007 15:20:01 -0500

Hi Michael,

  I think I did a poor job or explaining what I was hoping for in my
last email. I appologize. Here's what I was hoping for. Please let me
know if I am missing something as far as what is possible or what makes
sense to you.

1. Expression.today() should result in the same thing as currentDateDate
(CURRENT_DATE, I think)
2. We should add a.currentTimestamp() to the Expression class.

Other than that, everything looks good.

-Tom

Michael Bouschen wrote:

>Hi Tom,
>
>attached you find a new version of my updates.
>
>I added a new method currentTimeStamp() to ExpressionOperator and
>ExpressionOperator.today() simply calls that new method. The attached
>jar also includes the tests I plan to add to entity-persistence-tests.
>
>Thanks!
>
>Regards Michael
>
>
>
>
>>Hi Michael,
>>
>>Michael Bouschen wrote:
>>
>>
>>
>>>Hi Tom,
>>>
>>>
>>>
>>>
>>>>Hi Michael,
>>>>
>>>>Some comments:
>>>>
>>>>- can we map today() to the same things as currentDate() and add a
>>>>currentTimeStampMethod()?
>>>>
>>>>
>>>>
>>>Do you mean the methods in class Expression or the constants in
>>>ExpressionOperator? In the current code the Expression method
>>>currentDate() uses ExpressionOperator.Today and represents
>>>CURRENT_TIMESTAMP.
>>>
>>>
>>Lets just change the ExpressionOperator code. Since
>>ExpressionOperator.today() already exists, I am hesitant to remove it,
>>and I think that in order to change Expression, we would have to do
>>just that.
>>
>>
>>
>>>Method currentDateDate() uses ExpressionOperator.currentDate and
>>>represents CURRENT_DATE.
>>>
>>>I agree it would be cleaner to have a method currentTimeStamp()
>>>representing CURRENT_TIMESTAMP, so I can add this. Do you also
>>>propose to change the implementation of currentDate() to represent
>>>CURRENT_DATE and then remove method currentDateDate()?
>>>
>>>
>>>
>>>
>>I am a little concerned about changing the way those methods work for
>>backwards compatibility reasons. My vote is to keep them as they are.
>>
>>
>>
>>>
>>>
>>>
>>>
>>>>- In DateFunctionNode, there are some if statements without braces
>>>>around the contents. Our current coding convention requires the
>>>>braces. Could you please add them.
>>>>
>>>>
>>>>
>>>OK.
>>>
>>>
>>>
>>>
>>>>I am working on running the tests on DB2. I will let you know the
>>>>results, but I think you can go ahead and check-in.
>>>>
>>>>
>>>>
>>>>
>>>OK.
>>>
>>>
>>>
>>>
>>>>Are you adding a test?
>>>>
>>>>
>>>>
>>>Yes, I will add test cases that use the different JPQL date functions
>>>in a bulk update.
>>>
>>>Thanks!
>>>
>>>Regards Michael
>>>
>>>
>>>
>>>
>>>>-Tom
>>>>
>>>>Michael Bouschen wrote:
>>>>
>>>>
>>>>
>>>>
>>>>>Hi Tom,
>>>>>
>>>>>unfortunately derby does not accept assigning a timestamp to a time
>>>>>column. When using the currentDate Expression for CURRENT_TIME my
>>>>>test runs into similar exception as Mitesh mentioned below:
>>>>>SqlException: Columns of type 'TIME' cannot hold values of type
>>>>>'TIMESTAMP'. Error Code: -1
>>>>>Call:UPDATE EMPLOYEE_TBL SET TESTTIME = CURRENT TIMESTAMP
>>>>>
>>>>>I propose to introduce a new method currentTime to the Expression
>>>>>class and use this for the JPQL date function CURRENT_TIME. Class
>>>>>ExpressionOperator implements the default SQL for the date/time
>>>>>ExpressionOperator methods today(), currentDate() and
>>>>>currentTime(). I propose to change this default to the SQL 92 date
>>>>>functions CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP. I
>>>>>adapted some of the DatabasePlatform subclasses accordingly, but I
>>>>>was not able to do this for all the platforms. It would be great if
>>>>>you could take a look.
>>>>>
>>>>>I was able to successfully run a modified version of Mitesh's test
>>>>>case running all the date functions (CURRENT_DATE, CURRENT_TIME and
>>>>>CURRENT_TIMESTAMP) for the databases: Derby, Oracle 9, SQL Server
>>>>>2000 and MySQL 5.
>>>>>
>>>>>You find my changes attached, thanks!
>>>>>
>>>>>Regards Michael
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Hi Michael,
>>>>>>
>>>>>>I think currentDate() should work for CURRENT_TIME.
>>>>>>
>>>>>>-Tom
>>>>>>
>>>>>>Michael Bouschen wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Hi Mitesh, hi Tom,
>>>>>>>
>>>>>>>good catch!
>>>>>>>
>>>>>>>I'll take a look at changing the query compiler. What is the
>>>>>>>correct mapping for the JPQL function CURRENT_TIME? Can I use
>>>>>>>currentDate() since currentDateDate() does not include the time
>>>>>>>portion?
>>>>>>>
>>>>>>>Mitesh, could you do a me favor and file an issue for this?
>>>>>>>Please assign it to me.
>>>>>>>
>>>>>>>Regards Michael
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>Hi Mitesh,
>>>>>>>>
>>>>>>>>You are correct. To get the current timestamp, currentDate()
>>>>>>>>should be used.
>>>>>>>>
>>>>>>>>-Tom
>>>>>>>>
>>>>>>>>Mitesh Meswani wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>Hi Michael,
>>>>>>>>>The current code for query compiler maps all of these functions
>>>>>>>>>to Expression#currentDateDate(). Some databases(for example
>>>>>>>>>derby) distinguish between a TIMESTAMP type and DATE type.
>>>>>>>>>For example the attached program which tries to update a
>>>>>>>>>TIMESTAMP field on derby using "Update Employee e set
>>>>>>>>>e.testTimeStamp = CURRENT_TIMESTAMP" generates sql "UPDATE
>>>>>>>>>EMPLOYEE_TBL SET TESTTIMESTAMP = CURRENT DATE" which is not
>>>>>>>>>accepted by Derby. It is obvious that we will need to map this
>>>>>>>>>functions to different expressions
>>>>>>>>>
>>>>>>>>>Tom,
>>>>>>>>>>From javadocs in Expression.java, it seems that CURRENT_DATE
>>>>>>>>>should be mapped to Expression#currentDateDate()
>>>>>>>>>(ExpressionOperator.currentDate) and CURRENT_TIMESTAMP should
>>>>>>>>>be mapped to Expression#currentDate()
>>>>>>>>>(ExpressionOperator.Today) . Is that correct?
>>>>>>>>>
>>>>>>>>>Thanks,
>>>>>>>>>Mitesh
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>>>
>
>
>

-- 
Tom Ware
Principal Software Engineer
Oracle Canada Inc.
Direct: (613) 783-4598
Email: tom.ware_at_oracle.com