ode-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Matthieu Riou" <matth...@offthelip.org>
Subject Re: Hibernate mappings
Date Mon, 28 Apr 2008 18:13:57 GMT
Cool, thanks a lot for contributing that back! I'll see how to add it to our
doc and maybe update the scheduler.

Cheers,
Matthieu

On Mon, Apr 28, 2008 at 7:04 AM, Stefan Jakoubi <
sjakoubi@securityresearch.at> wrote:

>
> Hi Mathieu,
>
> I think we have found the solution - in fact 2 major are required to use
> ODE
> with DB2:
> - Update of the simple scheduler
> - Update of DB script
>
> Below, you can find a little HowTo for JBoss & ODE & DB2 - I hope
> everything
> works finde ;-)
>
> Cheers and thanks for your support,
> Simon & Stefan
>
>
> +++++++++++++++++++++++++++++++
> +++++++++++++++++++++++++++++++
> ++ HOW TO: JBOSS & ODE & DB2 ++
> +++++++++++++++++++++++++++++++
> +++++++++++++++++++++++++++++++
>
> *****************
> * PREREQUISITES *
> *****************
>
>  - JBoss (tested on JBoss-4.0.3SP1)
>  - deployed ODE (tested with version 1.1.1)
>  - DB2 (tested with version 9.x)
>
>
> *********************************
> * MODIFYING SIMPLE SCHEDULER JAR *
> * *******************************
>
> - There is a bug using the scalar function MOD within a prepared statement
> - Modify the JdbcDelegate class (see also end of this HowTo)
> - Compile the code and make the jar
>
>
> *****************
> * CONFIGURATION *
> *****************
>
> -------------------------
> ### DB2 configuration ###
> -------------------------
>
> (1) create database for ODE usage (e.g. testode)
>
> (2) execute the following statements
>
> -- Apache ODE - SimpleScheduler Database Schema
> --
> -- Apache Derby scripts by Maciej Szefler.
> --
> -- Scripts modified for use with DB2 by Stefan Jakoubi and Simon Tjoa
> -- (-) All primary key ID columns modified for auto increment purposes ->
> ID
> bigint GENERATED ALWAYS AS IDENTITY not null
> -- (-) Altered table LARGE_DATA -> BIN_DATA blob(2000M) NOT LOGGED -> as
> DB2
> only supports logging up to 1GB
> --     Furthermore, DB2 only allows blob sizes up to 2GB - 1 Byte!!!
>
> CREATE TABLE ode_job (
>  jobid CHAR(64)  NOT NULL DEFAULT '',
>  ts BIGINT  NOT NULL DEFAULT 0,
>  nodeid char(64),
>  scheduled int  NOT NULL DEFAULT 0,
>  transacted int  NOT NULL DEFAULT 0,
>  details blob(4096),
>  PRIMARY KEY(jobid));
>
> CREATE INDEX IDX_ODE_JOB_TS ON ode_job(ts);
> CREATE INDEX IDX_ODE_JOB_NODEID ON ode_job(nodeid);
>
>
> create table BPEL_ACTIVITY_RECOVERY (ID bigint GENERATED ALWAYS AS
> IDENTITY
> not null, PIID bigint, AID bigint, CHANNEL varchar(255), REASON
> varchar(255), DATE_TIME timestamp, LDATA_ID bigint, ACTIONS varchar(255),
> RETRIES integer, INSERT_TIME timestamp, MLOCK integer not null, primary
> key
> (ID));
> create table BPEL_CORRELATION_PROP (ID bigint GENERATED ALWAYS AS IDENTITY
> not null, NAME varchar(255), NAMESPACE varchar(255), VALUE varchar(255),
> CORR_SET_ID bigint, INSERT_TIME timestamp, MLOCK integer not null, primary
> key (ID));
> create table BPEL_CORRELATION_SET (ID bigint GENERATED ALWAYS AS IDENTITY
> not null, VALUE varchar(255), CORR_SET_NAME varchar(255), SCOPE_ID bigint,
> PIID bigint, PROCESS_ID bigint, INSERT_TIME timestamp, MLOCK integer not
> null, primary key (ID));
> create table BPEL_CORRELATOR (ID bigint GENERATED ALWAYS AS IDENTITY not
> null, CID varchar(255), PROCESS_ID bigint, INSERT_TIME timestamp, MLOCK
> integer not null, primary key (ID));
> create table BPEL_CORRELATOR_MESSAGE_CKEY (ID bigint GENERATED ALWAYS AS
> IDENTITY not null, CKEY varchar(255), CORRELATOR_MESSAGE_ID bigint,
> INSERT_TIME timestamp, MLOCK integer not null, primary key (ID));
> create table BPEL_EVENT (ID bigint GENERATED ALWAYS AS IDENTITY not null,
> IID bigint, PID bigint, TSTAMP timestamp, TYPE varchar(255), DETAIL
> clob(32000), LDATA_ID bigint, SID bigint, INSERT_TIME timestamp, MLOCK
> integer not null, primary key (ID));
> create table BPEL_FAULT (ID bigint GENERATED ALWAYS AS IDENTITY not null,
> FAULTNAME varchar(255), LDATA_ID bigint, EXPLANATION varchar(4000),
> LINE_NUM
> integer, AID integer, INSERT_TIME timestamp, MLOCK integer not null,
> primary
> key (ID));
> create table BPEL_INSTANCE (ID bigint GENERATED ALWAYS AS IDENTITY not
> null,
> INSTANTIATING_CORRELATOR bigint, FAULT bigint, JACOB_STATE bigint,
> PREVIOUS_STATE smallint, PROCESS_ID bigint, STATE smallint, LAST_ACTIVE_DT
> timestamp, SEQUENCE bigint, FAILURE_COUNT integer, FAILURE_DT timestamp,
> INSERT_TIME timestamp, MLOCK integer not null, primary key (ID));
> create table BPEL_MESSAGE (ID bigint GENERATED ALWAYS AS IDENTITY not
> null,
> MEX bigint, TYPE varchar(255), DATA bigint, INSERT_TIME timestamp, MLOCK
> integer not null, primary key (ID));
> create table BPEL_MESSAGE_EXCHANGE (ID bigint GENERATED ALWAYS AS IDENTITY
> not null, PORT_TYPE varchar(255), CHANNEL_NAME varchar(255), CLIENTKEY
> varchar(255), LDATA_EPR_ID bigint, LDATA_CEPR_ID bigint, REQUEST bigint,
> RESPONSE bigint, INSERT_DT timestamp, OPERATION varchar(255), STATE
> varchar(255), PROCESS bigint, PIID bigint, DIR char(1), PLINK_MODELID
> integer, PATTERN varchar(255), CORR_STATUS varchar(255), FAULT_TYPE
> varchar(255), FAULT_EXPL varchar(255), CALLEE varchar(255), PARTNERLINK
> bigint, PIPED_ID varchar(255), INSERT_TIME timestamp, MLOCK integer not
> null, primary key (ID));
> create table BPEL_MEX_PROPS (MEX bigint not null, VALUE varchar(8000),
> NAME
> varchar(255) not null, primary key (MEX, NAME));
> create table BPEL_PLINK_VAL (ID bigint GENERATED ALWAYS AS IDENTITY not
> null, PARTNER_LINK varchar(100) not null, PARTNERROLE varchar(100),
> MYROLE_EPR bigint, PARTNERROLE_EPR bigint, PROCESS bigint, SCOPE bigint,
> SVCNAME varchar(255), MYROLE varchar(100), MODELID integer, MYSESSIONID
> varchar(255), PARTNERSESSIONID varchar(255), INSERT_TIME timestamp, MLOCK
> integer not null, primary key (ID));
> create table BPEL_PROCESS (ID bigint GENERATED ALWAYS AS IDENTITY not
> null,
> PROCID varchar(255) not null unique, deployer varchar(255), deploydate
> timestamp, type_name varchar(255), type_ns varchar(255), version bigint,
> ACTIVE_ smallint, guid varchar(255), INSERT_TIME timestamp, MLOCK integer
> not null, primary key (ID));
> create table BPEL_SCOPE (ID bigint GENERATED ALWAYS AS IDENTITY not null,
> PIID bigint, PARENT_SCOPE_ID bigint, STATE varchar(255) not null, NAME
> varchar(255) not null, MODELID integer, INSERT_TIME timestamp, MLOCK
> integer
> not null, primary key (ID));
> create table BPEL_SELECTORS (ID bigint GENERATED ALWAYS AS IDENTITY not
> null, PIID bigint not null, SELGRPID varchar(255) not null, IDX integer
> not
> null, CORRELATION_KEY varchar(255) not null, CORRELATOR bigint not null,
> INSERT_TIME timestamp, MLOCK integer not null, primary key (ID), unique
> (CORRELATION_KEY, CORRELATOR));
> create table BPEL_UNMATCHED (ID bigint GENERATED ALWAYS AS IDENTITY not
> null, MEX bigint, CORRELATION_KEY varchar(255), CORRELATOR bigint not
> null,
> INSERT_TIME timestamp, MLOCK integer not null, primary key (ID));
> create table BPEL_XML_DATA (ID bigint GENERATED ALWAYS AS IDENTITY not
> null,
> LDATA_ID bigint, NAME varchar(255) not null, SCOPE_ID bigint, PIID bigint,
> IS_SIMPLE_TYPE smallint, INSERT_TIME timestamp, MLOCK integer not null,
> primary key (ID));
> create table LARGE_DATA (ID bigint GENERATED ALWAYS AS IDENTITY not null,
> BIN_DATA blob(2000M) NOT LOGGED, INSERT_TIME timestamp, MLOCK integer not
> null, primary key (ID));
> create table VAR_PROPERTY (ID bigint GENERATED ALWAYS AS IDENTITY not
> null,
> XML_DATA_ID bigint, PROP_VALUE varchar(255), PROP_NAME varchar(255) not
> null, INSERT_TIME timestamp, MLOCK integer not null, primary key (ID));
> alter table BPEL_ACTIVITY_RECOVERY add constraint FK94D24A7B14020712
> foreign
> key (LDATA_ID) references LARGE_DATA;
> alter table BPEL_ACTIVITY_RECOVERY add constraint FK94D24A7BA821BBE1
> foreign
> key (PIID) references BPEL_INSTANCE;
> alter table BPEL_CORRELATION_PROP add constraint FK4EC9DDAA4D0B7982
> foreign
> key (CORR_SET_ID) references BPEL_CORRELATION_SET;
> alter table BPEL_CORRELATION_SET add constraint FKB838191BA821BBE1 foreign
> key (PIID) references BPEL_INSTANCE;
> alter table BPEL_CORRELATION_SET add constraint FKB838191B6D49C363 foreign
> key (SCOPE_ID) references BPEL_SCOPE;
> alter table BPEL_CORRELATION_SET add constraint FKB838191BE15A2343 foreign
> key (PROCESS_ID) references BPEL_PROCESS;
> create index IDX_CORRELATOR_CID on BPEL_CORRELATOR (CID);
> alter table BPEL_CORRELATOR add constraint FKF50EFA33E15A2343 foreign key
> (PROCESS_ID) references BPEL_PROCESS;
> create index IDX_BPEL_CORRELATOR_MESSAGE_CKEY on
> BPEL_CORRELATOR_MESSAGE_CKEY (CKEY);
> alter table BPEL_CORRELATOR_MESSAGE_CKEY add constraint FK8997F700EEFA7470
> foreign key (CORRELATOR_MESSAGE_ID) references BPEL_UNMATCHED;
> alter table BPEL_EVENT add constraint FKAA6D673014020712 foreign key
> (LDATA_ID) references LARGE_DATA;
> alter table BPEL_EVENT add constraint FKAA6D6730A7EED251 foreign key (IID)
> references BPEL_INSTANCE;
> alter table BPEL_EVENT add constraint FKAA6D6730C831CBE3 foreign key (PID)
> references BPEL_PROCESS;
> alter table BPEL_FAULT add constraint FKAA722EB814020712 foreign key
> (LDATA_ID) references LARGE_DATA;
> alter table BPEL_INSTANCE add constraint FKE1DED41FDD43DBE1 foreign key
> (INSTANTIATING_CORRELATOR) references BPEL_CORRELATOR;
> alter table BPEL_INSTANCE add constraint FKE1DED41F6B66C85F foreign key
> (JACOB_STATE) references LARGE_DATA;
> alter table BPEL_INSTANCE add constraint FKE1DED41F1F3C9CB7 foreign key
> (FAULT) references BPEL_FAULT;
> alter table BPEL_INSTANCE add constraint FKE1DED41FE15A2343 foreign key
> (PROCESS_ID) references BPEL_PROCESS;
> alter table BPEL_MESSAGE add constraint FK4FA7231DCA00A413 foreign key
> (MEX)
> references BPEL_MESSAGE_EXCHANGE;
> alter table BPEL_MESSAGE add constraint FK4FA7231DEB2C9ED8 foreign key
> (DATA) references LARGE_DATA;
> alter table BPEL_MESSAGE_EXCHANGE add constraint FKBDA6BD0589D7A75B
> foreign
> key (LDATA_CEPR_ID) references LARGE_DATA;
> alter table BPEL_MESSAGE_EXCHANGE add constraint FKBDA6BD05BBA250D foreign
> key (PARTNERLINK) references BPEL_PLINK_VAL;
> alter table BPEL_MESSAGE_EXCHANGE add constraint FKBDA6BD05A821BBE1
> foreign
> key (PIID) references BPEL_INSTANCE;
> alter table BPEL_MESSAGE_EXCHANGE add constraint FKBDA6BD05C29670AA
> foreign
> key (LDATA_EPR_ID) references LARGE_DATA;
> alter table BPEL_MESSAGE_EXCHANGE add constraint FKBDA6BD05B5BD38C7
> foreign
> key (PROCESS) references BPEL_PROCESS;
> alter table BPEL_MESSAGE_EXCHANGE add constraint FKBDA6BD05FF451031
> foreign
> key (RESPONSE) references BPEL_MESSAGE;
> alter table BPEL_MESSAGE_EXCHANGE add constraint FKBDA6BD0554DCEE7F
> foreign
> key (REQUEST) references BPEL_MESSAGE;
> alter table BPEL_MEX_PROPS add constraint FK203CAFC7CA00A413 foreign key
> (MEX) references BPEL_MESSAGE_EXCHANGE;
> alter table BPEL_PLINK_VAL add constraint FK7D71E742F2BC5EB8 foreign key
> (MYROLE_EPR) references LARGE_DATA;
> alter table BPEL_PLINK_VAL add constraint FK7D71E74290D95ED1 foreign key
> (SCOPE) references BPEL_SCOPE;
> alter table BPEL_PLINK_VAL add constraint FK7D71E742B5BD38C7 foreign key
> (PROCESS) references BPEL_PROCESS;
> alter table BPEL_PLINK_VAL add constraint FK7D71E742D4B651D4 foreign key
> (PARTNERROLE_EPR) references LARGE_DATA;
> alter table BPEL_SCOPE add constraint FKAB2A32EAA821BBE1 foreign key
> (PIID)
> references BPEL_INSTANCE;
> alter table BPEL_SCOPE add constraint FKAB2A32EA42B20B58 foreign key
> (PARENT_SCOPE_ID) references BPEL_SCOPE;
> create index IDX_SELECTOR_CORRELATOR on BPEL_SELECTORS (CORRELATOR);
> create index IDX_SELECTOR_CKEY on BPEL_SELECTORS (CORRELATION_KEY);
> create index IDX_SELECTOR_SELGRPID on BPEL_SELECTORS (SELGRPID);
> alter table BPEL_SELECTORS add constraint FKF1F83A0AA821BBE1 foreign key
> (PIID) references BPEL_INSTANCE;
> alter table BPEL_SELECTORS add constraint FKF1F83A0A875201C9 foreign key
> (CORRELATOR) references BPEL_CORRELATOR;
> create index IDX_UNMATCHED_CKEY on BPEL_UNMATCHED (CORRELATION_KEY);
> create index IDX_UNMATCHED_CORRELATOR on BPEL_UNMATCHED (CORRELATOR);
> alter table BPEL_UNMATCHED add constraint FKF0663E01CA00A413 foreign key
> (MEX) references BPEL_MESSAGE_EXCHANGE;
> alter table BPEL_UNMATCHED add constraint FKF0663E01875201C9 foreign key
> (CORRELATOR) references BPEL_CORRELATOR;
> alter table BPEL_XML_DATA add constraint FKB7D47E7C14020712 foreign key
> (LDATA_ID) references LARGE_DATA;
> alter table BPEL_XML_DATA add constraint FKB7D47E7CA821BBE1 foreign key
> (PIID) references BPEL_INSTANCE;
> alter table BPEL_XML_DATA add constraint FKB7D47E7C6D49C363 foreign key
> (SCOPE_ID) references BPEL_SCOPE;
> alter table VAR_PROPERTY add constraint FK9C1E2C0DA48E25F2 foreign key
> (XML_DATA_ID) references BPEL_XML_DATA;
> create table hibernate_unique_key ( next_hi integer );
> insert into hibernate_unique_key values ( 0 );
> create table STORE_DU (NAME varchar(255) not null, deployer varchar(255),
> DEPLOYDT timestamp, DIR varchar(255), primary key (NAME));
> create table STORE_PROCESS (PID varchar(255) not null, DU varchar(255),
> TYPE
> varchar(255), version bigint, STATE varchar(255), primary key (PID));
> create table STORE_PROCESS_PROP (propId varchar(255) not null, value
> varchar(255), name varchar(255) not null, primary key (propId, name));
> create table STORE_VERSIONS (ID integer not null, VERSION bigint, primary
> key (ID));
> alter table STORE_PROCESS add constraint FKA83900D1BFFFC58C foreign key
> (DU)
> references STORE_DU;
> alter table STORE_PROCESS_PROP add constraint FKFD6C2E119ADDA5CB foreign
> key
> (propId) references STORE_PROCESS;
>
>
> (3) configure a DB2 user
>
> - there should already be a user configured (e.g. username)
> - set the password of this user within the windows user management (e.g.
> password)
>
>
> ------------------
> ### db2jcc.jar ###
> ------------------
>
> (4) copy db2jcc.jar to [JBOSS_HOME]\server\[server]\lib\db2jcc.jar
>
>
> ----------------------------
> ### ode-axis2.properties ###
> ----------------------------
>
> (5) create a file named ode-axis2.properties within the directory
> [JBOSS_HOME]\server\[server]\deploy\ode.war\WEB-INF\conf
>
> (6) edit ode-axis2.properties
>
> ode-axis2.db.mode=EXTERNAL
> ode-axis2.db.ext.dataSource=java:[ODE JNDI NAME, e.g. ODEDS]
>
>
> ------------------
> ### db2-ds.xml ###
> ------------------
>
> (7) copy [JBOSS_HOME]\docs\examples\jca\db2-ds.xml to
> [JBOSS_HOME]\server\[server]\deploy\db2-ds.xml
>
> (8) edit db2-ds.xml
>
> <datasources>
>  <local-tx-datasource>
>    <jndi-name>[ODE JNDI NAME, e.g. ODEDS]</jndi-name>
>    <connection-url>jdbc:db2://localhost:50000/[DATABASENAME, e.g.
> testode]</connection-url>
>    <driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
>    <user-name>[DBUSER, e.g. username - you must not use
> db2admin]</user-name>
>    <password>[DBUSERPASSWORD, e.g. password - it must not be
> blank]</password>
>    <min-pool-size>10</min-pool-size>
>        <!-- sql to call when connection is created
>        <new-connection-sql>some arbitrary sql</new-connection-sql>
>        -->
>
>        <!-- sql to call on an existing pooled connection when it is
> obtained from pool
>        <check-valid-connection-sql>some arbitrary
> sql</check-valid-connection-sql>
>        -->
>
>      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml
> (optional) -->
>      <metadata>
>         <type-mapping>DB2</type-mapping>
>      </metadata>
>  </local-tx-datasource>
>
> </datasources>
>
>
> -----------------------
> ### simple scheduler ###
> -----------------------
>
> (9) copy the modified simple scheduler jar to
> [JBOSS_HOME]\server\[server]\deploy\ode.war\WEB-INF\lib
>
> - in our case: ode-scheduler-simple-1.1.1-SNAPSHOT.jar
>
>
> ----------------------------------------------
> ### Modification within class JdbcDelegate ###
> ----------------------------------------------
>
> /*
>  * Licensed to the Apache Software Foundation (ASF) under one
>  * or more contributor license agreements.  See the NOTICE file
>  * distributed with this work for additional information
>  * regarding copyright ownership.  The ASF licenses this file
>  * to you under the Apache License, Version 2.0 (the
>  * "License"); you may not use this file except in compliance
>  * with the License.  You may obtain a copy of the License at
>  *
>  *    http://www.apache.org/licenses/LICENSE-2.0
>  *
>  * Unless required by applicable law or agreed to in writing,
>  * software distributed under the License is distributed on an
>  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
>  * KIND, either express or implied.  See the License for the
>  * specific language governing permissions and limitations
>  * under the License.
>  *
>  * UPDATED by Stefan Jakoubi and Simon Tjoa
>  * PERFORMED CHANGES:
>  * This class has been modified by Stefan Jakoubi and Simon Tjoa
>  *  (Secure Business Austria) to enable the use of the scalar function MOD
>  *  within prepared statements. Thus, the scalar function MOD has been
>  *  replaced with the internally DB2 formula used for determining MOD
> results:
>  *  MOD(x, y) = x - y * (x / y)
>  */
>
> package org.apache.ode.scheduler.simple;
>
> import java.io.ByteArrayOutputStream;
> import java.io.ObjectInputStream;
> import java.io.Serializable;
> import java.sql.Connection;
> import java.sql.DatabaseMetaData;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.util.ArrayList;
> import java.util.List;
> import java.util.Map;
>
> import javax.sql.DataSource;
>
> import org.apache.commons.logging.Log;
> import org.apache.commons.logging.LogFactory;
> import org.apache.ode.utils.StreamUtils;
>
> /**
>  * JDBC-based implementation of the {@link DatabaseDelegate} interface.
> Should work with most
>  * reasonably behaved databases.
>  *
>  * @author Maciej Szefler ( m s z e f l e r @ g m a i l . c o m ) modified
> by by Stefan Jakoubi and Simon Tjoa
>  */
> public class JdbcDelegate implements DatabaseDelegate {
>
>    private static final Log __log = LogFactory.getLog(JdbcDelegate.class);
>
>    private static final String DELETE_JOB = "delete from ODE_JOB where
> jobid = ? and nodeid = ?";
>
>    private static final String UPDATE_REASSIGN = "update ODE_JOB set
> nodeid
> = ?, scheduled = 0 where nodeid = ?";
>
>    private static final String UPGRADE_JOB_DEFAULT = "update ODE_JOB set
> nodeid = ? where nodeid is null and scheduled = 0 "
>            + "and mod(ts,?) = ? and ts < ?";
>
>    private static final String UPGRADE_JOB_SQLSERVER = "update ODE_JOB set
> nodeid = ? where nodeid is null and scheduled = 0 "
>        + "and (ts % ?) = ? and ts < ?";
>
>    // UPDATED by Stefan Jakoubi and Simon Tjoa
>    private static final String UPGRADE_JOB_DB2 = "update ODE_JOB set
> nodeid
> = ? where nodeid is null and scheduled = 0 " +
>        " and (BIGINT(ts - ? * BIGINT(ts/?)) = ?) and ts < ? ";
>
>    private static final String SAVE_JOB = "insert into ODE_JOB "
>            + " (jobid, nodeid, ts, scheduled, transacted, details)
> values(?, ?, ?, ?, ?, ?)";
>
>    private static final String GET_NODEIDS = "select distinct nodeid from
> ODE_JOB";
>
>    private static final String SCHEDULE_IMMEDIATE = "select jobid, ts,
> transacted, scheduled, details from ODE_JOB "
>            + "where nodeid = ? and scheduled = 0 and ts < ? order by ts";
>
>    private static final String UPDATE_SCHEDULED = "update ODE_JOB set
> scheduled = 1 where jobid in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
>
>    private static final int UPDATE_SCHEDULED_SLOTS = 10;
>
>    private DataSource _ds;
>
>    private Dialect _dialect;
>
>    public JdbcDelegate(DataSource ds) {
>        _ds = ds;
>        _dialect = guessDialect();
>    }
>
>    public boolean deleteJob(String jobid, String nodeId) throws
> DatabaseException {
>        if (__log.isDebugEnabled())
>            __log.debug("deleteJob " + jobid + " on node " + nodeId);
>
>        Connection con = null;
>        PreparedStatement ps = null;
>        try {
>            con = getConnection();
>            ps = con.prepareStatement(DELETE_JOB);
>            ps.setString(1, jobid);
>            ps.setString(2, nodeId);
>            return ps.executeUpdate() == 1;
>        } catch (SQLException se) {
>            throw new DatabaseException(se);
>        } finally {
>            close(ps);
>            close(con);
>        }
>    }
>
>    public List<String> getNodeIds() throws DatabaseException {
>        Connection con = null;
>        PreparedStatement ps = null;
>        try {
>            con = getConnection();
>            ps = con.prepareStatement(GET_NODEIDS,
> ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
>            ResultSet rs = ps.executeQuery();
>            ArrayList<String> nodes = new ArrayList<String>();
>            while (rs.next()) {
>                String nodeId = rs.getString(1);
>                if (nodeId != null)
>                    nodes.add(rs.getString(1));
>            }
>            if (__log.isDebugEnabled())
>                __log.debug("getNodeIds: " + nodes);
>            return nodes;
>        } catch (SQLException se) {
>            throw new DatabaseException(se);
>        } finally {
>            close(ps);
>            close(con);
>        }
>    }
>
>    public boolean insertJob(Job job, String nodeId, boolean loaded) throws
> DatabaseException {
>        if (__log.isDebugEnabled())
>            __log.debug("insertJob " + job.jobId + " on node " + nodeId + "
> loaded=" + loaded);
>
>        Connection con = null;
>        PreparedStatement ps = null;
>        try {
>            con = getConnection();
>            ps = con.prepareStatement(SAVE_JOB);
>            ps.setString(1, job.jobId);
>            ps.setString(2, nodeId);
>            ps.setLong(3, job.schedDate);
>            ps.setInt(4, asInteger(loaded));
>            ps.setInt(5, asInteger(job.transacted));
>            ByteArrayOutputStream bos = new ByteArrayOutputStream();
>            try {
>                StreamUtils.write(bos, (Serializable) job.detail);
>            } catch (Exception ex) {
>                __log.error("Error serializing job detail: " + job.detail);
>                throw new DatabaseException(ex);
>            }
>            ps.setBytes(6, bos.toByteArray());
>            return ps.executeUpdate() == 1;
>        } catch (SQLException se) {
>            throw new DatabaseException(se);
>        } finally {
>            close(ps);
>            close(con);
>        }
>    }
>
>    @SuppressWarnings("unchecked")
>    public List<Job> dequeueImmediate(String nodeId, long maxtime, int
> maxjobs) throws DatabaseException {
>        ArrayList<Job> ret = new ArrayList<Job>(maxjobs);
>        Connection con = null;
>        PreparedStatement ps = null;
>        try {
>            con = getConnection();
>            ps = con.prepareStatement(SCHEDULE_IMMEDIATE);
>            ps.setString(1, nodeId);
>            ps.setLong(2, maxtime);
>            ps.setMaxRows(maxjobs);
>            ResultSet rs = ps.executeQuery();
>            while (rs.next()) {
>                Map<String, Object> details;
>                try {
>                    ObjectInputStream is = new
> ObjectInputStream(rs.getBinaryStream(5));
>                    details = (Map<String, Object>) is.readObject();
>                    is.close();
>                } catch (Exception e) {
>                    throw new DatabaseException("Error deserializing job
> details", e);
>                }
>                Job job = new Job(rs.getLong(2), rs.getString(1),
> asBoolean(rs.getInt(3)), details);
>                ret.add(job);
>            }
>            rs.close();
>            ps.close();
>
>            // mark jobs as scheduled, UPDATE_SCHEDULED_SLOTS at a time
>            int j = 0;
>            int updateCount = 0;
>            ps = con.prepareStatement(UPDATE_SCHEDULED);
>            for (int updates = 1; updates <= (ret.size() /
> UPDATE_SCHEDULED_SLOTS) + 1; updates++) {
>                for (int i = 1; i <= UPDATE_SCHEDULED_SLOTS; i++) {
>                    ps.setString(i, j < ret.size() ? ret.get(j).jobId :
> "");
>                    j++;
>                }
>                ps.execute();
>                updateCount += ps.getUpdateCount();
>            }
>            if (updateCount != ret.size()) {
>                throw new DatabaseException(
>                        "Updating scheduled jobs failed to update all jobs;
> expected=" + ret.size()
>                                + " actual=" + updateCount);
>            }
>        } catch (SQLException se) {
>            throw new DatabaseException(se);
>        } finally {
>            close(ps);
>            close(con);
>        }
>        return ret;
>    }
>
>    public int updateReassign(String oldnode, String newnode) throws
> DatabaseException {
>        if (__log.isDebugEnabled())
>            __log.debug("updateReassign from " + oldnode + " ---> " +
> newnode);
>        Connection con = null;
>        PreparedStatement ps = null;
>        try {
>            con = getConnection();
>            ps = con.prepareStatement(UPDATE_REASSIGN);
>            ps.setString(1, newnode);
>            ps.setString(2, oldnode);
>            return ps.executeUpdate();
>        } catch (SQLException se) {
>            throw new DatabaseException(se);
>        } finally {
>            close(ps);
>            close(con);
>        }
>    }
>
>    public int updateAssignToNode(String node, int i, int numNodes, long
> maxtime) throws DatabaseException {
>        if (__log.isDebugEnabled())
>            __log.debug("updateAsssignToNode node=" + node + " " + i + "/"
> +
> numNodes + " maxtime=" + maxtime);
>        Connection con = null;
>        PreparedStatement ps = null;
>        try {
>            con = getConnection();
>            // UPDATED by Stefan Jakoubi and Simon Tjoa
>            if (_dialect == Dialect.DB2) {
>               ps = con.prepareStatement(UPGRADE_JOB_DB2);
>               ps.setString(1, node);
>               ps.setInt(2, numNodes);
>               ps.setInt(3, numNodes);
>               ps.setInt(4, i);
>               ps.setLong(5, maxtime);
>            } else
>            if (_dialect == Dialect.SQLSERVER) {
>                // Herausgezogen um neues PS für DB2 einführen zu können
>                ps = con.prepareStatement(UPGRADE_JOB_SQLSERVER);
>                ps.setString(1, node);
>                ps.setInt(2, numNodes);
>                ps.setInt(3, i);
>                ps.setLong(4, maxtime);
>            } else {
>                // Herausgezogen um neues PS für DB2 einführen zu können
>                ps = con.prepareStatement(UPGRADE_JOB_DEFAULT);
>                ps.setString(1, node);
>                ps.setInt(2, numNodes);
>                ps.setInt(3, i);
>                ps.setLong(4, maxtime);
>            }
>
>             return ps.executeUpdate();
>        } catch (SQLException se) {
>            throw new DatabaseException(se);
>        } finally {
>            close(ps);
>            close(con);
>        }
>    }
>
>    private Connection getConnection() throws SQLException {
>        return _ds.getConnection();
>    }
>
>    private int asInteger(boolean value) {
>        return (value ? 1 : 0);
>    }
>
>    private boolean asBoolean(int value) {
>        return (value != 0);
>    }
>
>    private void close(PreparedStatement ps) {
>        if (ps != null) {
>            try {
>                ps.close();
>            } catch (Exception e) {
>                __log.warn("Exception while closing prepared statement",
> e);
>            }
>        }
>    }
>
>    private void close(Connection con) {
>        if (con != null) {
>            try {
>                con.close();
>            } catch (Exception e) {
>                __log.warn("Exception while closing connection", e);
>            }
>        }
>    }
>
>    private Dialect guessDialect() {
>        Dialect d = Dialect.UNKNOWN;
>        Connection con = null;
>        try {
>            con = getConnection();
>            DatabaseMetaData metaData = con.getMetaData();
>            if (metaData != null) {
>                String dbProductName = metaData.getDatabaseProductName();
>                int dbMajorVer = metaData.getDatabaseMajorVersion();
>                __log.debug("Using database " + dbProductName + " major
> version " + dbMajorVer);
>                if (dbProductName.indexOf("DB2") >= 0) {
>                    d = Dialect.DB2;
>                } else if (dbProductName.indexOf("Derby") >= 0) {
>                    d = Dialect.DERBY;
>                } else if (dbProductName.indexOf("Firebird") >= 0) {
>                    d = Dialect.FIREBIRD;
>                } else if (dbProductName.indexOf("HSQL") >= 0) {
>                    d = Dialect.HSQL;
>                } else if (dbProductName.indexOf("Microsoft SQL") >= 0) {
>                    d = Dialect.SQLSERVER;
>                } else if (dbProductName.indexOf("MySQL") >= 0) {
>                    d = Dialect.MYSQL;
>                } else if (dbProductName.indexOf("Sybase") >= 0) {
>                    d = Dialect.SYBASE;
>                }
>            }
>        } catch (SQLException e) {
>            __log.warn("Unable to determine database dialect", e);
>        } finally {
>            close(con);
>        }
>        __log.info("Using database dialect: " + d);
>        return d;
>    }
>
>    enum Dialect {
>        DB2, DERBY, FIREBIRD, HSQL, MYSQL, ORACLE, SQLSERVER, SYBASE,
> UNKNOWN
>     }
>
> }
>
>
>
>
>
>
> Stefan Jakoubi wrote:
> >
> > Hi Mathieu,
> >
> > thanks for your fast reply!
> >
> >> We use XDoclet annotations in the source code to generate the mapping.
> So
> >> you'll have to change those if you want to alter it. Alternatively you
> >> can
> >> run on the generated mappings but that's probably a pain (updates). But
> >> why
> >> do you want to change the mapping?
> >
> > Furthermore, thank you for outlining the "where to find" - this helps us
> a
> > lot!!!
> >
> > We unfortunately have to alter the tables to conform our partner's
> > requirements:
> > (1) project specific tablename prefix
> > (2) "ID" column not allowed -> "tablename_ID"
> > (3) Reserved words (DB2) such as "TYPE" are not allowed as column name
> >
> >
> > Question: shall I post/upload the changes we have made within the simple
> > scheduler?
> >
> > Thanks,
> > Stefan
> >
> >
> >
> >
> >
> >
> > Matthieu Riou-5 wrote:
> >>
> >> On Fri, Apr 25, 2008 at 4:26 AM, Stefan Jakoubi <
> >> sjakoubi@securityresearch.at> wrote:
> >>
> >>> Dear ODE community,
> >>>
> >>>
> >>>
> >>> in this mail I (a) follow up a topic raised from a colleague
> >>> (http://ode.markmail.org/message/gyoxq5cz3uruizoc?q=tjoa) of mine and
> >>> (b) raise myself a question concerning configuring ODE for DB2.
> >>>
> >>>
> >>>
> >>> @ (a):
> >>>
> >>> When migrating ODE to DB2 there is a problem within the simple
> >>> scheduler. In particular, the scalar function MOD(x, y) within a
> >>> prepared statement throws an awful exception.
> >>>
> >>> The solution is to replace the MOD(x, y) within the prepared statement
> >>> with: x - y * (x / y). This is according to how DB2 internally
> >>> determines the result for the scalar function MOD.
> >>>
> >>>
> >> Thanks for letting us know.
> >>
> >>
> >>>
> >>>
> >>> @ (b):
> >>>
> >>> Can anybody please provide a complete table schema ODE requires?
> >>>
> >>
> >> You'll get several ones for different databases in
> >> dao-hibernate-db/target
> >> after a build.
> >>
> >>
> >>>
> >>> Does anybody know where to configure all required Hibernate mappings
> so
> >>> that ODE table names may be altered?
> >>>
> >>
> >> We use XDoclet annotations in the source code to generate the mapping.
> So
> >> you'll have to change those if you want to alter it. Alternatively you
> >> can
> >> run on the generated mappings but that's probably a pain (updates). But
> >> why
> >> do you want to change the mapping?
> >>
> >>
> >>>
> >>> When switching persistence to Hibernate, is it true that openjpa is
> not
> >>> used any more (and thus, no jpa mappings have to be modified in case
> >>> that I alter table names?)
> >>>
> >>>
> >> Right, it's either Hibernate of OpenJPA, they don't coexist.
> >>
> >>
> >>>
> >>>
> >>> Finally I want to tell that as soon as we get a solution for
> >>> JBOSS/ODE/DB2 we will contribute at Jira!
> >>>
> >>>
> >> Thanks!
> >>
> >> Matthieu
> >>
> >>
> >>>
> >>>
> >>> Thanks in advance & cheers,
> >>>
> >>> Stefan
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/Hibernate-mappings-tp16899339p16939634.html
> Sent from the Apache Ode User mailing list archive at Nabble.com.
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message