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:14:56 GMT
Oh, almost forgot: could you create a Jira task for this and attach
everything as a text file? We need some sort of IP clearance and Jira makes
it easier to track this down.

Thanks!

On Mon, Apr 28, 2008 at 11:13 AM, Matthieu Riou <matthieu@offthelip.org>
wrote:

> 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