Re: Success: Scarab 0.21 with Oracle 10g

Great, exhaustive work, thank you very much, Joerg...I'll try to test it
(I've got access to a Oracle-XE 10.2 in EC2 for development purposes of one
project) so we can validate it and get it integrated in the next release.

_
Jorge



On Mon, Sep 8, 2008 at 4:02 PM, <[email protected]> wrote:

>
> 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]
>



-- 
Jorge Uriarte Aretxaga
http://www.gailen.es
http://www.linkedin.com/in/jorgeuriarte