[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] Strong Liu commented on HHH-4732: --------------------------------- David, would you please add a *diff* format files? from the file you attached, i cannot easyly see what's you changed thanks -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] David Repshas updated HHH-4732: ------------------------------- Attachment: testsuiteMods.zip I've attached testsuiteMods.zip which has the changes needed to enable Teradata to run the test cases. -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] David Repshas commented on HHH-4732: ------------------------------------ Strong, Have org.hibernate.dialect.dialect.java org.hibernate.dialect.TeradataDialect.java and org.hibernate.tool.hbm2ddl.SchemaExport been updated with my changes ? Those were the hooks that run some additional SQL when you issue "alter table xxx drop constraint ..." They generate new SQL to remove the tables with the "_0", "_1" etc suffixes that were added when you previously added a constraint to a table. The new dialect methods: "public String performDropPreProcess(Statement stmt, String dropSql) throws SQLException" creates the SQL to drop those tables. "public void performDropPostProcess(Statement stmt, String dropSql) throws SQLException" runs the SQL Dave -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] Strong Liu commented on HHH-4732: --------------------------------- drop table UPerson 23:46:10,426 DEBUG SchemaExport:370 - Unsuccessful: drop table UPerson 23:46:10,427 DEBUG SchemaExport:371 - [Teradata Database] [TeraJDBC 13.00.00.20] [Error 3807] [SQLState 42S02] Object 'UPerson' does not exist. 23:46:10,428 DEBUG SchemaExport:385 - create table UCustomer ( person_id NUMERIC(18,0) not null, name VARCHAR(80) not null, sex CHAR(1) not null, height_centimeters DOUBLE PRECISION not null, address VARCHAR(255), zip VARCHAR(255), country VARCHAR(255), comments VARCHAR(255), salesperson NUMERIC(18,0), primary key (person_id), unique (name) ) 23:46:10,469 DEBUG SchemaExport:385 - create table UEmployee ( person_id NUMERIC(18,0) not null, name VARCHAR(80) not null, sex CHAR(1) not null, height_centimeters DOUBLE PRECISION not null, address VARCHAR(255), zip VARCHAR(255), country VARCHAR(255), "title" VARCHAR(20) not null, salary NUMERIC(18,1), pwd_expiry_weeks DOUBLE PRECISION not null, manager NUMERIC(18,0), primary key (person_id), unique (name) ) 23:46:10,511 DEBUG SchemaExport:385 - create table UPerson ( person_id NUMERIC(18,0) not null, name VARCHAR(80) not null, sex CHAR(1) not null, height_centimeters DOUBLE PRECISION not null, address VARCHAR(255), zip VARCHAR(255), country VARCHAR(255), primary key (person_id), unique (name) ) 23:46:10,543 DEBUG SchemaExport:385 - create index UCustomerAddressIndex on UCustomer (address, zip) 23:46:10,547 ERROR SchemaExport:348 - Unsuccessful: create index UCustomerAddressIndex on UCustomer (address, zip) 23:46:10,547 ERROR SchemaExport:349 - [Teradata Database] [TeraJDBC 13.00.00.20] [Error 3706] [SQLState 42000] Syntax error: Must specify index field(s) for CREATE INDEX. 23:46:10,547 DEBUG SchemaExport:385 - alter table UCustomer add constraint FK62B4BF5398311FBC foreign key (salesperson) references UEmployee 23:46:10,554 ERROR SchemaExport:348 - Unsuccessful: alter table UCustomer add constraint FK62B4BF5398311FBC foreign key (salesperson) references UEmployee 23:46:10,554 ERROR SchemaExport:349 - [Teradata Database] [TeraJDBC 13.00.00.20] [Error 5303] [SQLState HY000] Error table 'HIBERNATE.UCustomer_0' already exists. 23:46:10,554 DEBUG SchemaExport:385 - create index UEmployeeAddressIndex on UEmployee (address, zip) 23:46:10,559 ERROR SchemaExport:348 - Unsuccessful: create index UEmployeeAddressIndex on UEmployee (address, zip) 23:46:10,559 ERROR SchemaExport:349 - [Teradata Database] [TeraJDBC 13.00.00.20] [Error 3706] [SQLState 42000] Syntax error: Must specify index field(s) for CREATE INDEX. 23:46:10,559 DEBUG SchemaExport:385 - alter table UEmployee add constraint FK85B6262346644408 foreign key (manager) references UEmployee 23:46:10,564 ERROR SchemaExport:348 - Unsuccessful: alter table UEmployee add constraint FK85B6262346644408 foreign key (manager) references UEmployee 23:46:10,564 ERROR SchemaExport:349 - [Teradata Database] [TeraJDBC 13.00.00.20] [Error 5303] [SQLState HY000] Error table 'HIBERNATE.UEmployee_0' already exists. 23:46:10,564 DEBUG SchemaExport:385 - create index AddressIndex on UPerson (address, zip) 23:46:10,569 ERROR SchemaExport:348 - Unsuccessful: create index AddressIndex on UPerson (address, zip) 23:46:10,569 ERROR SchemaExport:349 - [Teradata Database] [TeraJDBC 13.00.00.20] [Error 3706] [SQLState 42000] Syntax error: Must specify index field(s) for CREATE INDEX. -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] Strong Liu commented on HHH-4732: --------------------------------- Dave, i need talk to Steve to see if it is acceptable to introduce this new api method into Dialect in 3.5.x, this branch now is under maintains stage, and the new feature development now is in trunk which target to hib 3.6. and also, considering this schema generation or dropping is not critical for users, they can always do it by manually, so, i'm going to open another issue for the dropping problem (which maybe we can documented as a won't fix), and try to find if there're other issues in Teradata Dialect. WDYT? -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] Strong Liu commented on HHH-4732: --------------------------------- 1) those are some of teradata limitation, maybe David can explain more. 2) the thing is that we can't get the real table name that to be dropped without perform a query ("sel IndexId, ChildTable, IndexName from dbc.RI_Distinct_ChildrenV where IndexName = '" + constraintName + "'";), see above David's comment, and I don't think it is a good idea that we get the db connection in the Configuration 3) yes, it is a teradata specific problem, and i haven't see other db has this requirement yet. but dont' think we have other choice to support this teradata's odd behavior :D -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] Steve Ebersole commented on HHH-4732: ------------------------------------- 1) Yes I understand these indicate some form of "teradata limitation". The point being, in regards to what? That is the whole reason we add these informational methods to Dialect and use them from the testsuite. Not only is it self-documenting ("oh we are skipping this test of CLOB support because this Dialect does not support CLOBs"...) but exposes the fact to users for their use as well. 2) Well I don't think this is a good idea at all, but here we are :) Y'all do realize that the suggested solution completely misses out on the case where the script is written to file instead of executed right? 3) I did not suggest otherwise. What I said was that I think a general purpose "here is some random bit of SQL that needs to be executed after we drop any kind of database object" is just not needed. -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] Steve Ebersole commented on HHH-4732: ------------------------------------- 1) Yes I understand there are existing usages of this pattern. In fact that is how it was always done historically. Changing these all at once is not feasible, so we change them as targets of opportunity. And here we are... 2) So the SQL cannot be dynamic? Not sure I follow your last point... where did you see me make any reference to {{TeradataDialect}} in my example code? Overall the problem with doing this in {{SchemaExport}} instead of in {{Configuration}} as called from {{SchemaExport}} is that {{SchemaExport}} has no idea what these statements are about, which is why you end up filtering drop statements for tables, indexes, etc to find the ones pertaining to foreign keys. If that is how it has to be because Teradata does not support some form of dynamic sql to achieve this then ok, its how it needs to be. However, the thing we want to make sure happens is that this happens even if they select to write the scrip to file instead of (or in addition to) executing it (providing we have a connection of course). -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] David Repshas commented on HHH-4732: ------------------------------------ We have an alternate way to deal with the constraint problems. Instead of dropping the additional tables when the constraint is dropped, we could drop them when the constraint is created. This method has less impact on the Hibernate core code and uses commands that can be added to a script file. I've attached the diff files (constraintDiffs.zip), which were diffed against Hibernate 3.5.3. Here's the overview of what was done: - Configuration.java:generateSchemaCreationScript When generating the "alter table" SQL for a constraint, we call dialect.constraintRequiresFunction() to determine if special processing is needed. This would return false by default and be set to true in the TeradataDialect. The "alter table" SQL is created. "generateConstraintFunctionSql" is called to create two SQL commands. The first will create a function which will handle both the creation of the constraint and the removal of the additional error tables caused by constraint creation. The second SQL command will invoke the function we just created. These commands are then added to the create script. - Dialect.java contains two new functions: public boolean constraintRequiresFunction() { return false;} public String[] generateConstraintFunctionSql(String alter, String tableName, String constraint) {return null;} - TeradataDialect: - contains implementation of the new functions: constraintRequiresFunction() and generateConstraintFunctionSql -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] David Repshas commented on HHH-4732: ------------------------------------ I've attached the diffs for my latest version of TeradataDialect.java which was tested against Hibernate 3.5.3. This includes the previous constraint changes and the following changes: - changed Column type for BIGINT, LONGVARBINARY and LONGVARCHAR - new definitions for current_time & current_date - change to "addColumnString()" - getTypeName - change in scale/precision - added getViolatedConstraintNameExtracter() - override dialect functions: supportsNotNullUnique, supportsExpectedLobUsagePattern, supportsUnboundedLobLocatorMaterialization, supportsTupleCounts and supportsTupleDistinctCounts - new function doesCreateIndexRequireColumnNames() - new functions for constraint name processing (these were included in the ConstraintDiffs attachment). -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] Strong Liu commented on HHH-4732: --------------------------------- David, this week I have something more urgent, i will work on this next week, thx for the patch -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4732?page=c... ] David Repshas commented on HHH-4732: ------------------------------------ Strong, It does support identity. I believe you want to add this to TeradataDialect.java: public String getIdentityColumnString() { return "generated by default as identity not null"; } Dave -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira