Hi Fernando,
Setting up another database should be pretty much a three step process:
1. Load the database schema in Oracle. I've attached the schema file to this
e-mail. You can also get it by building ODE.
2. Configure ODE to use a server-provided datasource by setting the
following properties in ode-axis2.properties
ode-axis2.db.mode=EXTERNAL
ode-axis2.db.ext.dataSource=java:comp/env/jdbc/DatasourceName
ode-axis2.tx.factory.class=org.apache.ode.axis2.util.JBossFactory
3. Setup the datasource in JBoss to use your Oracle database (see the JBoss
documentation if you don't know how to do this).
If you can take detailed notes about setting up ODE with JBoss+Oracle we
would be *very* interested in adding them in the ODE documentation, so that
others can benefit from it. Let me know how it goes.
Cheers,
Matthieu
On 7/31/07, Fernando Lozano <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> I´ve managed to integrate Apache Ode with JBoss 3.2.7. It all runs fine by
> just copying thw WAR file to the deploy directory. Now I`m onto using
> Oracle
> Database instead of using Derby. The problem is I don´t know where to
> start.
>
>
> I´ve seen a guide in the Apache Ode site in the reference section, but it
> just talks about PXE with MySQL.
>
>
>
> Can anyone help me changing my Ode database from Derby to Oracle?
>
>
>
> I`m really interested in doing this, but I´m kind of a newbie in this so I
>
> need some help.
>
> Thanks in advance,
> Fernando Lozano.
>
>
>
>
CREATE TABLE ODE_ACTIVITY_RECOVERY (ID NUMBER NOT NULL, ACTIONS VARCHAR2(255), ACTIVITY_ID NUMBER, CHANNEL VARCHAR2(255), DATE_TIME TIMESTAMP, DETAILS CLOB, REASON VARCHAR2(255), RETRIES NUMBER, INSTANCE_ID NUMBER, PRIMARY KEY (ID));
CREATE TABLE ODE_CORRELATION_SET (CORRELATION_SET_ID NUMBER NOT NULL, CORRELATION_KEY VARCHAR2(255), NAME VARCHAR2(255), SCOPE_ID NUMBER, PRIMARY KEY (CORRELATION_SET_ID));
CREATE TABLE ODE_CORRELATOR (CORRELATOR_ID NUMBER NOT NULL, CORRELATOR_KEY VARCHAR2(255), PROC_ID NUMBER, PRIMARY KEY (CORRELATOR_ID));
CREATE TABLE ODE_CORSET_PROP (ID NUMBER NOT NULL, PROP_KEY VARCHAR2(255), PROP_VALUE VARCHAR2(255), CORRSET_ID NUMBER, PRIMARY KEY (ID));
CREATE TABLE ODE_EVENT (EVENT_ID NUMBER NOT NULL, DETAIL VARCHAR2(255), DATA BLOB, SCOPE_ID NUMBER, TSTAMP TIMESTAMP, TYPE VARCHAR2(255), INSTANCE_ID NUMBER, PROCESS_ID NUMBER, PRIMARY KEY (EVENT_ID));
CREATE TABLE ODE_FAULT (FAULT_ID NUMBER NOT NULL, ACTIVITY_ID NUMBER, DATA CLOB, MESSAGE VARCHAR2(255), LINE_NUMBER NUMBER, NAME VARCHAR2(255), PRIMARY KEY (FAULT_ID));
CREATE TABLE ODE_MESSAGE (MESSAGE_ID NUMBER NOT NULL, DATA CLOB, TYPE VARCHAR2(255), MESSAGE_EXCHANGE_ID VARCHAR2(255), PRIMARY KEY (MESSAGE_ID));
CREATE TABLE ODE_MESSAGE_EXCHANGE (MESSAGE_EXCHANGE_ID VARCHAR2(255) NOT NULL, CALLEE VARCHAR2(255), CHANNEL VARCHAR2(255), CORRELATION_ID VARCHAR2(255), CORRELATION_KEYS VARCHAR2(255), CORRELATION_STATUS VARCHAR2(255), CREATE_TIME TIMESTAMP, DIRECTION NUMBER, EPR CLOB, FAULT VARCHAR2(255), FAULT_EXPLANATION VARCHAR2(255), OPERATION VARCHAR2(255), PARTNER_LINK_MODEL_ID NUMBER, PATTERN VARCHAR2(255), PIPED_ID VARCHAR2(255), PORT_TYPE VARCHAR2(255), PROPAGATE_TRANS NUMBER, STATUS VARCHAR2(255), CORR_ID NUMBER, PARTNER_LINK_ID NUMBER, PROCESS_ID NUMBER, PROCESS_INSTANCE_ID NUMBER, REQUEST_MESSAGE_ID NUMBER, RESPONSE_MESSAGE_ID NUMBER, PRIMARY KEY (MESSAGE_EXCHANGE_ID));
CREATE TABLE ODE_MESSAGE_ROUTE (MESSAGE_ROUTE_ID NUMBER NOT NULL, CORRELATION_KEY VARCHAR2(255), GROUP_ID VARCHAR2(255), ROUTE_INDEX NUMBER, CORR_ID NUMBER, PROCESS_INSTANCE_ID NUMBER, PRIMARY KEY (MESSAGE_ROUTE_ID));
CREATE TABLE ODE_MEX_PROP (ID NUMBER NOT NULL, PROP_KEY VARCHAR2(255), PROP_VALUE VARCHAR2(255), MEX_ID VARCHAR2(255), PRIMARY KEY (ID));
CREATE TABLE ODE_PARTNER_LINK (PARTNER_LINK_ID NUMBER NOT NULL, MY_EPR CLOB, MY_ROLE_NAME VARCHAR2(255), MY_ROLE_SERVICE_NAME VARCHAR2(255), MY_SESSION_ID VARCHAR2(255), PARTNER_EPR CLOB, PARTNER_LINK_MODEL_ID NUMBER, PARTNER_LINK_NAME VARCHAR2(255), PARTNER_ROLE_NAME VARCHAR2(255), PARTNER_SESSION_ID VARCHAR2(255), SCOPE_ID NUMBER, PRIMARY KEY (PARTNER_LINK_ID));
CREATE TABLE ODE_PROCESS (ID NUMBER NOT NULL, GUID VARCHAR2(255), NUMBER_OF_INSTANCES NUMBER, PROCESS_ID VARCHAR2(255), PROCESS_TYPE VARCHAR2(255), VERSION NUMBER, PRIMARY KEY (ID));
CREATE TABLE ODE_PROCESS_INSTANCE (ID NUMBER NOT NULL, DATE_CREATED TIMESTAMP, EXECUTION_STATE BLOB, LAST_ACTIVE_TIME TIMESTAMP, LAST_RECOVERY_DATE TIMESTAMP, PREVIOUS_STATE NUMBER, SEQUENCE NUMBER, INSTANCE_STATE NUMBER, FAULT_ID NUMBER, INSTANTIATING_CORRELATOR_ID NUMBER, PROCESS_ID NUMBER, ROOT_SCOPE_ID NUMBER, PRIMARY KEY (ID));
CREATE TABLE ODE_SCOPE (SCOPE_ID NUMBER NOT NULL, MODEL_ID NUMBER, SCOPE_NAME VARCHAR2(255), SCOPE_STATE VARCHAR2(255), PARENT_SCOPE_ID NUMBER, PROCESS_INSTANCE_ID NUMBER, PRIMARY KEY (SCOPE_ID));
CREATE TABLE ODE_XML_DATA (XML_DATA_ID NUMBER NOT NULL, DATA CLOB, IS_SIMPLE_TYPE NUMBER, NAME VARCHAR2(255), SCOPE_ID NUMBER, PRIMARY KEY (XML_DATA_ID));
CREATE TABLE ODE_XML_DATA_PROP (ID NUMBER NOT NULL, PROP_KEY VARCHAR2(255), PROP_VALUE VARCHAR2(255), XML_DATA_ID NUMBER, PRIMARY KEY (ID));
CREATE TABLE OPENJPA_SEQUENCE_TABLE (ID NUMBER NOT NULL, SEQUENCE_VALUE NUMBER, PRIMARY KEY (ID));
CREATE TABLE STORE_DU (NAME VARCHAR2(255) NOT NULL, DEPLOYDT TIMESTAMP, DEPLOYER VARCHAR2(255), DIR VARCHAR2(255), PRIMARY KEY (NAME));
CREATE TABLE STORE_PROCESS (PID VARCHAR2(255) NOT NULL, STATE VARCHAR2(255), TYPE VARCHAR2(255), VERSION NUMBER, DU VARCHAR2(255), PRIMARY KEY (PID));
CREATE TABLE STORE_PROCESS_PROP (id NUMBER NOT NULL, PROP_KEY VARCHAR2(255), PROP_VAL VARCHAR2(255), PRIMARY KEY (id));
CREATE TABLE STORE_PROC_TO_PROP (ProcessConfDaoImpl_PID VARCHAR2(255), element_id NUMBER);
CREATE TABLE STORE_VERSIONS (id NUMBER NOT NULL, VERSION NUMBER, PRIMARY KEY (id));
CREATE INDEX I_D_CTVRY_INSTANCE ON ODE_ACTIVITY_RECOVERY (INSTANCE_ID);
CREATE INDEX I_D_CR_ST_SCOPE ON ODE_CORRELATION_SET (SCOPE_ID);
CREATE INDEX I_D_CRLTR_PROCESS ON ODE_CORRELATOR (PROC_ID);
CREATE INDEX I_D_CRPRP_CORRSET ON ODE_CORSET_PROP (CORRSET_ID);
CREATE INDEX I_OD_VENT_INSTANCE ON ODE_EVENT (INSTANCE_ID);
CREATE INDEX I_OD_VENT_PROCESS ON ODE_EVENT (PROCESS_ID);
CREATE INDEX I_OD_MSSG_MESSAGEEXCHANGE ON ODE_MESSAGE (MESSAGE_EXCHANGE_ID);
CREATE INDEX I_D_MSHNG_CORRELATOR ON ODE_MESSAGE_EXCHANGE (CORR_ID);
CREATE INDEX I_D_MSHNG_PARTNERLINK ON ODE_MESSAGE_EXCHANGE (PARTNER_LINK_ID);
CREATE INDEX I_D_MSHNG_PROCESS ON ODE_MESSAGE_EXCHANGE (PROCESS_ID);
CREATE INDEX I_D_MSHNG_PROCESSINST ON ODE_MESSAGE_EXCHANGE (PROCESS_INSTANCE_ID);
CREATE INDEX I_D_MSHNG_REQUEST ON ODE_MESSAGE_EXCHANGE (REQUEST_MESSAGE_ID);
CREATE INDEX I_D_MSHNG_RESPONSE ON ODE_MESSAGE_EXCHANGE (RESPONSE_MESSAGE_ID);
CREATE INDEX I_D_MS_RT_CORRELATOR ON ODE_MESSAGE_ROUTE (CORR_ID);
CREATE INDEX I_D_MS_RT_PROCESSINST ON ODE_MESSAGE_ROUTE (PROCESS_INSTANCE_ID);
CREATE INDEX I_D_MXPRP_MEX ON ODE_MEX_PROP (MEX_ID);
CREATE INDEX I_D_PRLNK_SCOPE ON ODE_PARTNER_LINK (SCOPE_ID);
CREATE INDEX I_D_PRTNC_FAULT ON ODE_PROCESS_INSTANCE (FAULT_ID);
CREATE INDEX I_D_PRTNC_INSTANTIATINGCORRELA ON ODE_PROCESS_INSTANCE (INSTANTIATING_CORRELATOR_ID);
CREATE INDEX I_D_PRTNC_PROCESS ON ODE_PROCESS_INSTANCE (PROCESS_ID);
CREATE INDEX I_D_PRTNC_ROOTSCOPE ON ODE_PROCESS_INSTANCE (ROOT_SCOPE_ID);
CREATE INDEX I_OD_SCOP_PARENTSCOPE ON ODE_SCOPE (PARENT_SCOPE_ID);
CREATE INDEX I_OD_SCOP_PROCESSINSTANCE ON ODE_SCOPE (PROCESS_INSTANCE_ID);
CREATE INDEX I_D_XM_DT_SCOPE ON ODE_XML_DATA (SCOPE_ID);
CREATE INDEX I_D_XMPRP_XMLDATA ON ODE_XML_DATA_PROP (XML_DATA_ID);
CREATE INDEX I_STR_CSS_DU ON STORE_PROCESS (DU);
CREATE INDEX I_STR_PRP_ELEMENT ON STORE_PROC_TO_PROP (element_id);
CREATE INDEX I_STR_PRP_PROCESSCONFDAOIMPL_P ON STORE_PROC_TO_PROP (ProcessConfDaoImpl_PID);
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);
