Bug 778016 (SOA-541)

Summary:	Extend /jboss-as/tools/schema to support MS-SQL (tier2 database) - and add other server config files
Product:	[JBoss] JBoss Enterprise SOA Platform 4	Reporter:	Len DiMaggio <ldimaggi>
Component:	Tooling	Assignee:	Jervis Liu <jliu>
Status:	CLOSED NEXTRELEASE	QA Contact:
Severity:	urgent	Docs Contact:
Priority:	urgent
Version:	4.2 CP01	CC:	mproctor
Target Milestone:	---
Target Release:	4.3 CP01
Hardware:	Unspecified
OS:	Unspecified
URL:	http://jira.jboss.org/jira/browse/SOA-541
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2009-03-11 17:02:48 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:
Embargoed:
Bug Depends On:	778019, 778372
Bug Blocks:

Description Len DiMaggio 2008-05-06 20:19:20 UTC

Date of First Response: 2008-10-01 07:29:43
project_key: SOA

In order to support MS-SQL - a tier2 database for 4.2.0 release - we'll need to extend the /jboss-as/tools/schema tool - and add other server config files

[ldimaggi@ldimaggi jboss-soa-p-standalone.4.2.0]$ find . -name "*mssql*" -print | grep -v doc
./jbpm-jpdl/db/jbpm.jpdl.mssql.sql
./jboss-as/server/default/deploy/jbpm.esb/jbpm-sql/jbpm.jpdl.mssql.sql
./jboss-as/tools/schema/common/mssql-persistence-service.xml

In comparison - we have these files for MySQL: 

[ldimaggi@ldimaggi jboss-soa-p-standalone.4.2.0]$ find . -name "*mysql*" -print | grep -v doc
./jbpm-jpdl/db/jbpm.jpdl.mysql.sql
./jboss-as/server/default/deploy/jbossesb.esb/message-store-sql/mysql
./jboss-as/server/default/deploy/jbossesb.sar/juddi-sql/mysql
./jboss-as/server/default/deploy/jbpm.esb/jbpm-sql/jbpm.jpdl.mysql.sql
./jboss-as/tools/schema/common/mysql-persistence-service.xml
./jboss-as/tools/schema/mysql
./jboss-as/tools/schema/mysql/template/mysql-ds.xml
./jboss-as/tools/schema/mysql/template/mysql-persistence-service.xml
./jboss-as/tools/schema/mysql/driver/mysql-connector-java-5.0.7-bin.jar

We will have to at least have sample files for each DB:

 -> <database>-ds.xml (needed for DB2, Sybase, MS-SQL)
 -> message-store-sql/<database>/create_database.sql, drop_database.sql (needed for DB2, Sybase, MS-SQL)
 -> <database>-persistence-service.xml (needed for DB2)

Comment 1 Len DiMaggio 2008-05-07 13:21:23 UTC

Changed JIRA type from feature request to bug as MS-SQL Server is a supported tier2 DB.

Comment 2 Len DiMaggio 2008-05-07 13:22:24 UTC

Link: Added: This issue is related to SOA-531

Comment 3 Len DiMaggio 2008-06-23 15:27:27 UTC

Link: Added: This issue is a dependency of SOA-604

Comment 4 Len DiMaggio 2008-07-14 17:46:12 UTC

I'll log a separate JIRA for DB2 - and link it to an EAP JIRA task for the EAP certifcation of DB2.

MS-SQL is certified now (with EAP 4.3 GA and CP01).

Sybase will be later on .

Comment 5 Len DiMaggio 2008-07-14 17:49:18 UTC

Link: Added: This issue related SOA-630

Comment 6 Len DiMaggio 2008-08-24 01:25:51 UTC

This is not implemented in 4.3/IR1:

[ldimaggi@ldimaggi schema]$ pwd
/opt/43_IR1/jboss-soa-p-standalone.4.3.0/jboss-esb/tools/schema
[ldimaggi@ldimaggi schema]$ ll
total 92
-rw-r--r-- 1 ldimaggi ldimaggi  1329 Aug  9 05:29 build.properties
-rw-r--r-- 1 ldimaggi ldimaggi 14305 Aug  9 05:29 build.xml
drwxr-xr-x 2 ldimaggi ldimaggi  4096 Aug  9 05:29 common
drwxr-xr-x 5 ldimaggi ldimaggi  4096 Aug  9 05:29 db2
drwxr-xr-x 5 ldimaggi ldimaggi  4096 Aug  9 05:29 mssql
drwxr-xr-x 5 ldimaggi ldimaggi  4096 Aug  9 05:29 mysql
drwxr-xr-x 5 ldimaggi ldimaggi  4096 Aug  9 05:29 oracle10g
drwxr-xr-x 5 ldimaggi ldimaggi  4096 Aug  9 05:29 oracle9i
drwxr-xr-x 5 ldimaggi ldimaggi  4096 Aug  9 05:29 postgresql
drwxr-xr-x 5 ldimaggi ldimaggi  4096 Aug  9 05:29 sybase
[ldimaggi@ldimaggi schema]$ ant
Buildfile: build.xml

execute:
     [echo] JBoss SOA Platform
     [echo] ------------------
     [echo] Database Configuration Script
     [echo]  
     [echo] This script is used to configure the SOA platform and all it's constituent components
     [echo] against a new RDBMS. Deployment scripts are currently available for the following
     [echo] databases: MySQL, Oracle9i, PostgresSQL, Oracle10g
     [echo] ------------------
     [echo] NOTICE: THIS SCRIPT MAY NOT WORK CORRECTLY IF YOU HAVE MADE MANUAL CHANGES TO THE
     [echo]         DATABASE CONFIGURATION.
     [echo]  
    [input] Please enter the database you wish to configure [mysql, postgresql, oracle9i, oracle10g]:
mssql

BUILD FAILED
/opt/43_IR1/jboss-soa-p-standalone.4.3.0/jboss-esb/tools/schema/build.xml:21: You must specify a valid database type.

Total time: 2 seconds

Comment 7 nwallace 2008-09-25 19:39:54 UTC

Link: Added: This issue is related to SOA-856

Comment 8 nwallace 2008-10-01 11:29:43 UTC

Emailed Julian for comments

Comment 9 Julian Coleman 2008-10-23 10:19:40 UTC

We will not add this in a 4.2CP.
We will make a decision about 4.3CP01 later.

Comment 10 Julian Coleman 2009-01-14 14:07:01 UTC

Link: Removed: This issue is a dependency of SOA-604

Comment 11 Julian Coleman 2009-01-14 14:13:53 UTC

Added to the 4.3 branch in revision 2540 of:
  build-tools/resource/schema/tooltool/esb/build.xml
  build-tools/resource/schema/tooltool/esb/db2/template/antsql.xml
  build-tools/resource/schema/tooltool/esb/db2/template/db2-ds.xml
  build-tools/resource/schema/tooltool/esb/db2/template/setup.sql
  build-tools/resource/schema/tooltool/esb/mssql/driver/sqljdbc.jar
  build-tools/resource/schema/tooltool/esb/mssql/template/antsql.xml
  build-tools/resource/schema/tooltool/esb/mssql/template/initial.sql
  build-tools/resource/schema/tooltool/esb/mssql/template/mssql-ds.xml
  build-tools/resource/schema/tooltool/esb/mssql/template/setup.sql
  build-tools/resource/schema/tooltool/esb/mssql/template/uuid-jboss-service.xml
  build-tools/resource/schema/tooltool/esb/mysql/template/antsql.xml
  build-tools/resource/schema/tooltool/esb/oracle10g/template/antsql.xml
  build-tools/resource/schema/tooltool/esb/oracle9i/template/antsql.xml
  build-tools/resource/schema/tooltool/esb/oracle9i/template/setup.sql
  build-tools/resource/schema/tooltool/esb/postgresql/template/antsql.xml
  build-tools/resource/schema/tooltool/esb/sybase/template/antsql.xml

Commit message:
  Pull up revisions 2421, 2442, 2443 and 2476 from trunk for JIRA's SOA-541,
  SOA-630 and SOA-630.
  
    r2421
    (SOA-1060)Added DB2 setup script; (SOA-630)Extended /tools/schema to support DB2
  
    r2442
    add MYSQL support: SOA-541
  
    r2443
    support mssql
  
    r2476
    add db setup scripts for mssql

Comment 12 Len DiMaggio 2009-03-05 03:05:05 UTC

A basic smoke test - helloworld quickstart - was successful - leaving the JIRA open until we can run more extensive tests.

Comment 13 Len DiMaggio 2009-03-07 03:32:08 UTC

Link: Added: This issue related SOA-1214

Comment 14 Len DiMaggio 2009-03-11 17:02:48 UTC

Verified in CP01 CR2 - the results are consistent with other DBs:

http://hudson.qa.jboss.com/hudson/view/SOA-Release/job/soa-it-db-jdk15/DATABASE=mssql,jdk=java15,label=RHEL4_x86/

Comment 15 Dana Mison 2009-03-13 03:22:16 UTC

Release Note:
SOA-541
Support for Microsoft SQL Server has been added to the Database Schema Tool, ${SOA_ROOT}/tools/schema

Comment 16 Len DiMaggio 2009-03-17 14:17:38 UTC

Link: Added: This issue is related to SOA-1236

Comment 17 Dana Mison 2009-06-16 08:41:39 UTC

release note item was removed by request