AW: Success: Scarab 0.21 with Oracle 10g

Hello Joerg,
 
thank´s a lot ! I was trying to fix scarab for Oracle 10g as well but had not
yet the time to remove the obstacles.
Now I can give it a new try. As our intention is similiar to yours (no sample
data, production environment) I´m now eager to test.
 
Best regards,
Norbert
 
Mit freundlichen Grüßen
 
Norbert Hirneisen
 
science4you Online-Monitoring
http://www.science4you.org <http://www.science4you.org/> 
email: [email protected]
 
Die Falternacht: www.falternacht.de <http://www.falternacht.de/> 
Werden Sie Falterzähler: www.falterfunde.de <http://www.falterfunde.de/> 
Wanderfalter: www.science4you.org/platform/monitoring/index.do
Tagfalter-Monitoring Deutschland:
www.science4you.org/platform/tmd/tmd-top/index.do
 <http://www.orchideenfunde.de/> 
Infos über geschützte Arten ? http://www.wisia.de <http://www.wisia.de/> 
 
Norbert Hirneisen
Science & Communications
 
von-Müllenark-Str. 19
53179 Bonn
Tel. 0228/6194930

-----Ursprüngliche Nachricht-----
Von: [email protected] [mailto:[email protected]] 
Gesendet: Montag, 8. September 2008 16:02
An: [email protected]
Betreff: 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.data
base.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