[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-4732) Update Teradata Dialect for Teradata 13.0

[David Repshas (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=36319#action_36319 ] 

David Repshas commented on HHH-4732:
------------------------------------

Strong,

I tried running org.hibernate.test.unionsubclass2.UnionSubclassTest to recreate your problem and did not hit the problem you encountered. Specifically, I didn't get an error for "HIBERNATE.UCustomer_0' already exists" though I did see the "Create Index" error which is a known limitation as Teradata requires an index field for this command. 

In the process of running this test I do see the following:
- the use of constraints in Teradata does create the following two tables:
UCUSTOMER_0 and UEMPLOYEE_0

- at the end of the test, I see this on the call stack:
  FunctionalTestClassTestSuite.tearDown:140
  ExecutionEnvironment.complete:166
  SessionFactoryImpl.close:887
  SchemaExport:drop: 221
  SchemaExport.execute:258
  SchemaExport.drop:363
  TeradataDialect.performDropPreProcess:326

1) The TeradataDialect:performDropPreProcess() method is passed the SQL:
  "alter table UCustomer drop constraint FK62B4BF5398311FBC"

  It then runs an executeQuery with this sql:
sel IndexId, ChildTable, IndexName from dbc.RI_Distinct_ChildrenV where IndexName = 'FK62B4BF5398311FBC'

  It then returns the string:
	drop table "UCustomer_0"

2) Now after SchemaExport.drop() executes:
    alter table UCustomer drop constraint FK62B4BF5398311FBC"

3) the next call is to dialect.performDropPostProcess()
   which is passed the String:
        drop table "UCustomer_0"

4) this is run via executeUpdate and removes that table

After this, a similar process occurs which removes the "UEMPLOYEE_0" table.


I also ran the test until it created these tables:
UCustomer, UCustomer_0, UEmployee and Uemployee_0 
and then halted it. 

When I restarted the test, the tables UCustomer_0 and UCustomer_0 were
successfully removed and the test ran to completion. 


If you delete the UCustomer_0 table and UEmployee_0 tables, you should be able 
to re-run this test. Could you set some breakpoints in 
TeradataDialect:performDropPreProcess() and 
TeradataDialect:performDropPostProcess() 
to ensure they are being called ??
At the end of a successful run, neither UCustomer_0 or UEmployee_0
should be present. 

Also, as noted above, the new dialect methods should generate and execute SQL to drop the tables that are causing the problems. My best guess right now is that:
- the routines are not being called
- the routines have a bug
- the SQL they run is not working correctly

Here's the official documentation on the error that was returned. It 
can be found in the Teradata Database Messages Manual:

5303 Error table '%VSTR' already exists.
Explanation: Teradata system creates an error table to
store target data rows that violate the specified referential
constraint when the constraint is added to an existing
table via ALTER TABLE statement or an
inconsistent referential constraint is revalidated via
REVALIDATE REFERENCES statement. The name of
the error table is the target table name appended by the
corresponding reference index number. If a table with
that name has already existed in the system, then this
error is returned.
Generated By: RES modules.
For Whom: End User.
Remedy: Drop the table in conflict, and resubmit
request.

							Dave














> Update Teradata Dialect for Teradata 13.0
> -----------------------------------------
>
>                 Key: HHH-4732
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732
>             Project: Hibernate Core
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 3.3.2
>         Environment: Hibernate 3.3.2/ Teradata JDBC Driver 13.0, Teradata Database 13.0
>            Reporter: David Repshas
>            Assignee: Strong Liu
>         Attachments: teradata.zip, teradataDiff.zip, testsuiteMods.zip
>
>
> Would like to update Teradata Dialect to work with Teradata Database 13.0.
> I've include the following files with changes as noted:
> org.hibernate.dialect.dialect.java
> - added include of java.sql.statement
> - added code to support the removal of temporary
>   files created when using referential integrity
>   The three new methods are:
> 	public boolean supportsDropPreProcess() 
>         public String performDropPreProcess(Statement stmt, String dropSql) throws SQLException
>         public void performDropPostProcess(Statement stmt, String dropSql) throws SQLException 
> ==================================
> org.hibernate.dialect.TeradataDialect.java
> - added new include files
> - modified registerFunction calls for "current_time" and "current_date"
> - put registerKeyword list in alphabetical order and added keywords:
>     column,map,password,summary, type, value and year
> - changed "getAddColumnString()" to return "Add"
> - added new methods:
>    public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter()
>    public String extractConstraintName(SQLException sqlex) 
>    public boolean supportsNotNullUnique()
>    public boolean supportsExpectedLobUsagePattern()
>    public boolean supportsUnboundedLobLocatorMaterialization() 
>    public boolean supportsDropPreProcess() 
>    public String performDropPreProcess(Statement stmt, String dropSql) throws SQLException
>    public void performDropPostProcess(Statement stmt, String dropSql) throws SQLException 
>    
> ==================================
> org.hibernate.tool.hbm2ddl.SchemaExport
> - added hooks for new methods:
> 	supportsDropPreProcess,performDropPreProcess and performDropPostProcess 
> ==================================
> Also made some changes to some of the files in testsuite:
> =============================================
> test/hql/astParserLoadingTest.java
>    - Add instance check of TeradataDialect to get correct behavior
> =============================================
> test/legacy/foobartest.java
>  - Add instance check of TeradataDialect to get correct behavior
>  - modified SQL: Teradata requires "<>" rather than "!="
>   
> =============================================
> test/typeParameters\typeparametertest.java
>  -needed to disable these tests as Teradata locking was causing 
> a deadlock situation when they run
> =============================================

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira