[jira] Updated: (JS2-49) Database dependent sql scripts breaks quickStart

[ji...@apache.org]

The following issue has been updated:

    Updater: Ate Douma (mailto:ate@douma.nu)
       Date: Sun, 23 May 2004 11:57 AM
    Changes:
             Attachment changed to JS2-49-patch.txt
    ---------------------------------------------------------------------
For a full history of the issue, see:

  http://issues.apache.org/jira/browse/JS2-49?page=history

---------------------------------------------------------------------
View the issue:
  http://issues.apache.org/jira/browse/JS2-49

Here is an overview of the issue:
---------------------------------------------------------------------
        Key: JS2-49
    Summary: Database dependent sql scripts breaks quickStart
       Type: Bug

     Status: Unassigned
   Priority: Blocker

    Project: Jetspeed 2
 Components: 
             Deployment
   Versions:
             2.0-a1

   Assignee: 
   Reporter: Ate Douma

    Created: Sun, 23 May 2004 11:40 AM
    Updated: Sun, 23 May 2004 11:57 AM
Environment: WindowsXP, Oracle 9.2.0.1, Tomcat 4.1.29, J2SE1.4.2_03

Description:
The newly added src/sql/populate-userinfo-for-default-psml.sql cannot be run against an Oracle database (and probably others).
The problem is that it contains database specific sql which won't work on Oracle. It contains insert statements with string value for date columns. The format used isn't compatible with Oracle.
Example:
  INSERT INTO PREFS_NODE VALUES(1,NULL,'',0,'/','2004-05-22 14:57:53.586','2004-05-22 14:57:53.586');

Oracle requires the sql function to_date(value,format) to be used if using non-default formatted date strings. What the default is cannot easily be determined: its configurable.
So, for Oracle the sql function *must* be used in this case.

Another issue with this example is the usage of miliseconds in the date value. Oracle doesn't support that. Its resolution for date columns goes no further than seconds.

Getting this example inserted in Oracle thus requires:
  INSERT INTO PREFS_NODE VALUES(1,NULL,'',0,'/',to_date('2004-05-22 14:57:54','YYYY-MM-DD HH24:MI:SS'),to_date('2004-05-22 14:57:53','YYYY-MM-DD HH24:MI:SS'));

Another column type problem is with boolean. Hsql does understand boolean columns, Oracle doesn't. Torque generates number(1) columns for those in which 0 means false and 1 means true.

I've created a new version of the sql script for oracle and put it into a new oracle subfolder.

I will attach a patch shortly which fixes the deploy for oracle and in which I also reordered the execution of this script to be done from the portal/maven.xml db.entities goal like the others.

I haven't mysql running so I'm not sure if the default script will work for mysql. Maybe David Sean Taylor can test that.

I don't think this is the road to go though and I will start a discussion on the dev list concerning these database dependent sql scripts.


---------------------------------------------------------------------
JIRA INFORMATION:
This message is automatically generated by JIRA.

If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


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