Success: Scarab 0.21 with Oracle 10g

Hello,

there are several thread dealing with Scarab 0.21 and Oracle 10g but I 
haven't found a complete list of steps to do joining Scarab with Oracle, 
so I had some days of work to bring them together. Some of these points 
are documented in the Scarab doc or can be found on the mailing list. I 
would like to provide you these steps, so you may save some time. There 
may be fixes in the trunk version, but I was'nt able to download the trunk 
with svn because of some security restrictions of our company. Also I want 
to use Scarab for a kind of "production", so it may be better to use a 
stable version.

I have used Ant to build Scarab and Eclipse for managing the files and 
executing Ant.

---------------------------------------------------
1. build.properties for Oracle

scarab.database.type=oracle
scarab.database.host=<hostname>
scarab.database.port=1521
scarab.database.url=jdbc:oracle:thin:@<hostname>:1521:<dbname>
scarab.database.admin.url=jdbc:oracle:thin:@<hostname>:1521:<dbname>
scarab.database.username=scarab
scarab.database.password=xxxxxxx
scarab.database.admin.username=scarab
scarab.database.admin.password=xxxxxxx

---------------------------------------------------
2. Copy the Oracle JDBC-driver into the path $SCARAB_ROOT/lib. Your DB 
administrator probably can provide you the JDBC-driver.

---------------------------------------------------
3. One table name is defined with 34 characters. Oracle doesn't support 
this so you have to fix the table name in two files:

$SCARAB_ROOT/src/schema/scarab-schema.xml:
old:
    <table name="SCARAB_DEFAULT_NOTIFICATION_FILTER" 
         idMethod="none" 
         javaName="NotificationDefaultFilter" >
new:
    <table name="SCARAB_DEF_NOTIFICATION_FILTER" 
         idMethod="none" 
         javaName="NotificationDefaultFilter" >


$SCARAB_ROOT/src/sql/oracle/prepare-oracle-dummy-tables.sql:
old:
        create table SCARAB_DEFAULT_NOTIFICATION_FILTER (A char(1));
new:
        create table SCARAB_DEF_NOTIFICATION_FILTER (A char(1));

---------------------------------------------------
4. There is a BLOB compatibility issue with Oracle, so you have to replace 
one column type

$SCARAB_ROOT/src/schema/turbine-schema.xml line 75 (table TURBINE_USER):

old:
        <column name="OBJECTDATA" type="VARBINARY"/>
new:
        <column name="OBJECTDATA" size="4000" type="VARCHAR"/>

---------------------------------------------------
5. Make target create-db-schema working with oracle. You cannot fix the 
db-type-check target, because Ant will then try to create Oracle's tables 
twice, leading into a failure. This is just a workaround, not a "good" fix 
for the Ant script: Remove the if-condition in the create-db-schema 
target.

$SCARAB_ROOT/src/conf/conf/build.xml:
old:
        <target name="create-db-schema" 
depends="scarab-welcome,db-type-check, sql-current-db" 
                if="auto.create.db">

new:
        <target name="create-db-schema" 
depends="scarab-welcome,db-type-check, sql-current-db">


---------------------------------------------------
5. Our Oracle instances are configured not to use ISO timestamps but a 
local format, so I have to fix the init sql script to use ISO format.

$SCARAB_ROOT/src/sql/oracle/standard-oracle-init.sql (add line):
        alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS';

If you want to fill the scarab db with the JIRA templates, do the 
following fix (this target doesn't execute the above init script):

$SCARAB_ROOT_PATH/src/conf/conf/build.xml (add init.sql to the 
jira-target):

    <!-- 
================================================================== -->
    <!-- Load jira templates data  -->
    <!-- 
================================================================== -->
    <target name="load-jira-templates-data" 
unless="skip.jira.templates.data">
        <sql
            driver="${scarab.database.jdbc.driver}"
            url="${scarab.database.url}"
            userid="${scarab.database.admin.username}"
            password="${scarab.database.admin.password}">
            <classpath refid="classpath"/>
            <transaction 
src="${database.instance.sql.dir}/${scarab.database.type}/standard-${scarab.database.type}-init.sql"/>
            <transaction 
src="${database.instance.sql.dir}/${project}-jira-templates-data.sql"/>
        </sql>
    </target>

---------------------------------------------------
6. Finallye we don't want to use the sample data and the jira templates, 
so we have set these properties (in Eclipse's Ant configuration, setting 
them in the build.properties was not successful):

skip.seed.data=true
skip.jira.templates.data=true

In addition, I made a sql script to drop the whole scarab database in 
Oracle (see attached drop_tables.sql), so I had an easy way to clean up 
while experimenting with the Oracle integration.

regards,

Jörg Florin
Techniker Krankenkasse
Hauptverwaltung
IT-Systeme (ITS.4 - Systemframeworks)
Bramfelder Straße 140, D-22305 Hamburg
 
Tel: +49 (40) 69 09 - 25 75
Fax: +49 (40) 69 09 - 8 25 75
E-Mail: [email protected]
TK-Website: www.tk-online.de

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]