Bug 780263 (SOA-2660)

Summary: modeshape ddl scripts statement delimiter
Product: [JBoss] JBoss Enterprise SOA Platform 5 Reporter: Aleksandar Kostadinov <akostadinov>
Component: EDSAssignee: Van Halbert <vhalbert>
Status: CLOSED NEXTRELEASE QA Contact:
Severity: high Docs Contact:
Priority: high    
Version: 5.1.0.ER5CC: akostadinov, vhalbert
Target Milestone: ---   
Target Release: 5.1.0.CR1   
Hardware: Unspecified   
OS: Unspecified   
URL: http://jira.jboss.org/jira/browse/SOA-2660
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2011-02-08 16:02:43 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Attachments:
Description Flags
schemas.zip none

Description Aleksandar Kostadinov 2010-12-09 19:36:15 UTC 
Workaround: Workaround Exists
project_key: SOA

I'm going through the supported set of databases trying to execute the modeshape ddl scripts with the native database administrative utilities. I'm looking at DB2 right now and wondering why '%' was chosen as statement delimiter.

`db2 -vf create.modeshape-jpa-connector.ddl` results in errors like:

create table MODE_SIMPLE_LARGE_VALUES (SHA1 varchar(40) not null, COMPRESSED smallint, DATA blob(255) not null, LENGTH bigint not null, TYPE varchar(255) not nu%l, primary key (SHA1))
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "%" was found following "primary key (SHA1)) ".
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

One can workaround this by adding the -td% option but why not use ';' as a delimiter given there is a -t option that. Or even why not use simply new line as a separator given db2 utility uses that by default? I mean it makes most sense to use the default delimiter or ';' because of its wide use.

I'll update this issue if I see other glitches. IMHO the \r before statement delimiter should be removed. In the case of mssql and sybase I have yet to check how it works though.
Comment 1 Van Halbert 2010-12-09 20:11:29 UTC 
the create table script above is strange.   I'm looking at the created script from SOA:

create table MODE_SIMPLE_LARGE_VALUES (SHA1 varchar(40) not null, COMPRESSED smallint, DATA blob(255) not null, LENGTH bigint not null, TYPE varchar(255) not null, primary key (SHA1))

and it has "null"  where "nu%l% is.
Comment 2 Van Halbert 2010-12-09 20:14:36 UTC 
but we can go ahead and change all delimiters to ";"
Comment 3 Aleksandar Kostadinov 2010-12-09 20:28:59 UTC 
I didn't quiet understand your first comment but please let me check mssql and sybase tomorrow. I'm almost sure mssql takes ';' but not so about sybase.
Comment 5 Aleksandar Kostadinov 2010-12-11 07:38:05 UTC 
btw IIRC MS SQL server is using "go" as commit transaction but given we shouldn't really need these statement in separate transactions, ';' should be working fine there
Comment 6 Van Halbert 2010-12-13 14:37:08 UTC 
While we're at renaming directories, lets rename MySQL5InnoDB to MySQL.
Comment 7 Aleksandar Kostadinov 2010-12-13 19:11:04 UTC 
Why not.. hopefully there are not too many people running *the last good MySQL version* - 3.x :)
Comment 8 Van Halbert 2010-12-13 20:27:39 UTC 
Link: Added: This issue is a dependency of MODE-1022
Comment 9 Van Halbert 2010-12-13 20:42:14 UTC 
Here are the new version of the scripts creates from a soa build.   Unzip / replace the schema's directory under modeshape.
Comment 10 Van Halbert 2010-12-13 20:42:14 UTC 
Attachment: Added: schemas.zip
Comment 11 Van Halbert 2010-12-14 19:32:09 UTC 
The changes have been checked into ModeShape and into the soa build to handle producing ddl scripts with the requested delimiter and handle Sybase.
Comment 12 Aleksandar Kostadinov 2010-12-16 11:18:41 UTC 
I see one issue with sybase - between statements I see \rGO\n instead of \nGO\n
Thanks!
Comment 13 Aleksandar Kostadinov 2011-02-08 16:02:43 UTC 
Everything looks fine to me now. We do not ship sybase DDL any more.