db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brendan Miller" <bmil...@dotster.com>
Subject Re: Village Oracle DATE<=>Timestamp patch?
Date Mon, 26 Nov 2007 20:27:11 GMT

I think I mostly agree with your thoughts--a change like this deep in
Village would certainly create unexpected behavior for Oracle users.

The real problem as I see it is that Torque relies on its XML schema
definition, and Village uses this ResultSetMetaData introspection to 
determine column types.  Given the history of Village as a separate
project with lackluster support/improvement over the last few years,
it's understandable.  At the same time, it does cause me (and likely
other Oracle >9.2 users) a disconnect between the Torque and Village

I actually don't care what Java types are used *anywhere* (java.util.Date 
seems most appropriate most places if you don't need the sub-millisecond 
expression), just so long as they're treated consistenyly.  For an Oracle 
table defined (in Oracle) as type DATE, it doesn't matter whether Torque 
calls it DATE or TIMESTAMP--Village will treat it as a date, sans time.  
I haven't looked at the capabilities of other databases' DATE type--it 
they only store the date, and if the SQL standard is that DATE is just
that, then it makes sense to translate DATE to Date.  But there should 
still be some workaround for legacy Oracle tables of type DATE where you
want to map them to a Timestamp or something with time details.

The abstraction to be able to treat databases in the same fashion is
important, but there should be a better way to configure Torque/Village
to the particular idiosyncracies of a particular date than the current
lowest common denominator method.

Any thoughts in the interim?  We have DATE fields.  We cannot change
them to TIMESTAMP.  And we *need* hours/minutes/seconds.


On Mon, Nov 26, 2007 at 01:59:57PM -0500, Greg Monroe wrote:
> First, I don't use Oracle and haven't followed the 
> specific problem you're trying to solve.  That said...
> One concern that comes to mind is how this might effect
> cross DB code.  I.e. a developer uses Torque with the 
> assumption that the same SQL standard rules are used 
> across all DBAdaptors and migrating from one to another
> is relatively painless.
> To do this, Torque trys to enforce the official and 
> defacto SQL standards.
> In this case, the SQL standard clearly defines that a
> DATE column is just that... a date with no time part.  
> The TimeStamp column is the SQL standard column for a 
> Date and Time (storing at least seconds but decimals of 
> seconds allowed).
> At first glance, you will be changing Torque so that
> columns defined in the XML as Date will be handled 
> in a non SQL standard way for Oracle.  IMHO, this can 
> lead to problems in keeping your application DB server
> independent.  E.g., writing it under Oracle and 
> assuming that Date always has a time component.
> That said, there are always issues where existing DB
> design has taken advantage of server specific enhancements.
> As in the case of using a Oracle Date column instead of
> the standards correct Timestamp column.
> I wonder if the proper solution is to define modify 
> Village to return a java Date object for all Date and
> Timestamp fields instead of the SQL types.  Then convert 
> this to the standard format in the get/set record methods.  
> E.g, if the XML column type is Date. The time is stripped 
> off.  If it's Timestamp, the time is kept.  Comparison of
> values returned will be based on the SQL Standard definitions
> in all DB servers.
> > -----Original Message-----
> > From: Brendan Miller [mailto:bmiller@dotster.com] 
> > Sent: Monday, November 26, 2007 1:00 PM
> > To: torque-dev@db.apache.org
> > Subject: Village Oracle DATE<=>Timestamp patch?
> > 
> > 
> > Thanks Greg, for getting on that last one so fast.  I'm on my 
> > way with this Oracle DATE/TIMESTAMP issue.  I still cannot 
> > find the Village patch that is mentioned in mailing list 
> > archives, so I am rolling my own.  I'm still not sure if 
> > patching Village is the way to go, or if I should just use 
> > the -Doracle.jdbc.V8Compatible=true JVM paramter mentioned in 
> > the Wiki.
> > 
> > If Village was to be patched, is this totally crazy?
> > 
> > Index: src/java/com/workingdogs/village/Column.java
> > ===================================================================
> > --- src/java/com/workingdogs/village/Column.java    (revision 21344)
> > +++ src/java/com/workingdogs/village/Column.java    (working copy)
> > @@ -139,6 +139,17 @@
> >          this.nullAllowed = rsmd.isNullable(columnNumber) == 1;
> >          this.autoIncrement = rsmd.isAutoIncrement(columnNumber);
> >  
> > +       // ORACLE DATE BUGFIX / HACK
> > +       // Oracle 9.2+ returns DATE SQL type for DATE 
> > columns, even though it
> > +       // can store hour/minute/second info.  Bend Oracle 
> > DATE columns to
> > +       // make Village believe they are timestamp fields
> > +       if (rsmd.getClass().getName().equals(
> > +                   "oracle.jdbc.driver.OracleResultSetMetaData") && 
> > +               this.columnType == Types.DATE) {
> > +           this.columnTypeName = "TIMESTAMP";
> > +           this.columnType = Types.TIMESTAMP;
> > +       }
> > +
> >          // The JDBC spec is VERY unclear about what this 
> > means and as 
> >          // such, it should be ignored.  Derby returns true 
> > all the time.
> >          // Sybase and Informix say it's unsupported (and false).
> > 
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-dev-help@db.apache.org
> > 
> > 
> DukeCE Privacy Statement:
> Please be advised that this e-mail and any files transmitted with
> it are confidential communication or may otherwise be privileged or
> confidential and are intended solely for the individual or entity
> to whom they are addressed. If you are not the intended recipient
> you may not rely on the contents of this email or any attachments,
> and we ask that you please not read, copy or retransmit this
> communication, but reply to the sender and destroy the email, its
> contents, and all copies thereof immediately. Any unauthorized
> dissemination, distribution or copying of this communication is
> strictly prohibited.
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org

To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org

View raw message