You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@ode.apache.org by Matheswaran Nachimuthu <ma...@infosys.com> on 2009/01/28 12:45:52 UTC

ode database structure

Hi,

We are using apache ode 1.1 version along with MySql.

we need the ode database structure to clean up the database once the process
is done.

Please guide me with the ode database structure so that it will be very
useful for us to write the clean up scripts. Also please point me to the
clean up scripts if it already exists

Thanks in Advance!

Regards,
Matheswaran N
-- 
View this message in context: http://www.nabble.com/ode-database-structure-tp21704232p21704232.html
Sent from the Apache Ode Dev mailing list archive at Nabble.com.

Re: ode database structure

Posted by Matheswaran Nachimuthu <ma...@infosys.com>.

Hi Matthieu,

Thanks for the scripts but it doesnt contain the few tables which we use for
mysql.
ode_activity_recovery;
ode_correlation_set;
ode_correlator;
ode_corset_prop;
ode_event;
ode_fault;
ode_job;
ode_message;
ode_message_exchange;
ode_message_route;
ode_mex_prop;
ode_partner_link;
ode_process;
ode_process_instance;
ode_scope;
ode_xml_data;
ode_xml_data_prop;
openjpa_sequence_table;
store_du;
store_proc_to_prop;
store_process;
store_process_prop;
store_versions;

I do understand that the table name ode_activity_recovery is named as
BPEL_ACTIVITY_RECOVERY  in your script but few tables like BPEL_UNMATCHED,
BPEL_SELECTORS and LARGE_DATA are not present in my list. It would be great
if you can help

Thanks in Advance!

Regards,
Matheswaran N



mriou wrote:
> 
> On Wed, Jan 28, 2009 at 3:45 AM, Matheswaran Nachimuthu <
> matheswaran_n@infosys.com> wrote:
> 
>>
>> Hi,
>>
>> We are using apache ode 1.1 version along with MySql.
>>
>> we need the ode database structure to clean up the database once the
>> process
>> is done.
>>
>> Please guide me with the ode database structure so that it will be very
>> useful for us to write the clean up scripts. Also please point me to the
>> clean up scripts if it already exists
>>
> 
> I've pasted what I have below. It's a bit nuclear as it deletes all
> instances that have been failed / completed. But you should be able to
> adapt
> it to your needs easily. Also note that ODE now automatically cleans up:
> 
> http://ode.apache.org/user-guide.html#UserGuide-InstanceDataCleanup
> 
> But at this point, you will have to build the 1.X branch to get this
> feature.
> 
> Matthieu
> 
> -----
> 
> delete from BPEL_ACTIVITY_RECOVERY where PIID in (select ID from
> BPEL_INSTANCE where STATE in (30,40,60));
> 
> delete from BPEL_CORRELATION_PROP where CORR_SET_ID in (select ID from
> BPEL_CORRELATION_SET where PIID in (select ID from BPEL_INSTANCE where
> STATE
> in (30,40,60)));
> delete from BPEL_CORRELATION_SET where PIID in (select ID from
> BPEL_INSTANCE
> where STATE in (30,40,60));
> 
> delete from LARGE_DATA where ID in (select LDATA_EPR_ID from
> BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
> STATE in (30,40,60)));
> delete from LARGE_DATA where ID in (select LDATA_CEPR_ID from
> BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
> STATE in (30,40,60)));
> delete from LARGE_DATA where ID in (select DATA from BPEL_MESSAGE where
> MEX
> in (select REQUEST from BPEL_MESSAGE_EXCHANGE where PIID in (select ID
> from
> BPEL_INSTANCE where STATE in (30,40,60))));
> delete from LARGE_DATA where ID in (select DATA from BPEL_MESSAGE where
> MEX
> in (select RESPONSE from BPEL_MESSAGE_EXCHANGE where PIID in (select ID
> from
> BPEL_INSTANCE where STATE in (30,40,60))));
> delete from BPEL_MESSAGE where MEX in (select REQUEST from
> BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
> STATE in (30,40,60)));
> delete from BPEL_MESSAGE where MEX in (select RESPONSE from
> BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
> STATE in (30,40,60)));
> delete from BPEL_CORRELATOR_MESSAGE_CKEY where CORRELATOR_MESSAGE_ID in
> (select ID from BPEL_UNMATCHED where MEX in (select ID from
> BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
> STATE in (30,40,60))));
> delete from BPEL_UNMATCHED where MEX in (select ID from
> BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
> STATE in (30,40,60)));
> delete from BPEL_MEX_PROPS where MEX in (select ID from
> BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
> STATE in (30,40,60)));
> delete from BPEL_MESSAGE_EXCHANGE where PIID in (select ID from
> BPEL_INSTANCE where STATE in (30,40,60));
> 
> delete from LARGE_DATA where ID in (select LDATA_ID from BPEL_FAULT where
> ID
> in (select FAULT from BPEL_INSTANCE where STATE in (30,40,60)));
> delete from BPEL_FAULT where ID in (select FAULT from BPEL_INSTANCE where
> STATE in (30,40,60));
> 
> delete from LARGE_DATA where ID in (select LDATA_ID from BPEL_XML_DATA
> where
> PIID in (select ID from BPEL_INSTANCE where STATE in (30,40,60)));
> delete from VAR_PROPERTY where XML_DATA_ID in (select ID from
> BPEL_XML_DATA
> where PIID in (select ID from BPEL_INSTANCE where STATE in (30,40,60)));
> delete from BPEL_XML_DATA where PIID in (select ID from BPEL_INSTANCE
> where
> STATE in (30,40,60));
> 
> delete from BPEL_SELECTORS where PIID in (select ID from BPEL_INSTANCE
> where
> STATE in (30,40,60));
> 
> delete from LARGE_DATA where ID in (select MYROLE_EPR from BPEL_PLINK_VAL
> where SCOPE in (select ID from BPEL_SCOPE where PIID in (select ID from
> BPEL_INSTANCE where STATE in (30,40,60))));
> delete from LARGE_DATA where ID in (select PARTNERROLE_EPR from
> BPEL_PLINK_VAL where SCOPE in (select ID from BPEL_SCOPE where PIID in
> (select ID from BPEL_INSTANCE where STATE in (30,40,60))));
> delete from BPEL_PLINK_VAL where SCOPE in (select ID from BPEL_SCOPE where
> PIID in (select ID from BPEL_INSTANCE where STATE in (30,40,60)));
> delete from BPEL_SCOPE where PIID in (select ID from BPEL_INSTANCE where
> STATE in (30,40,60));
> 
> delete from LARGE_DATA where ID in (select LDATA_ID from BPEL_EVENT);
> delete from BPEL_EVENT;
> 
> delete from BPEL_INSTANCE where STATE in (30,40,60);
> 
> 

-- 
View this message in context: http://www.nabble.com/ode-database-structure-tp21704232p21722901.html
Sent from the Apache Ode Dev mailing list archive at Nabble.com.

Re: ode database structure

Posted by Matthieu Riou <ma...@gmail.com>.

On Wed, Jan 28, 2009 at 3:45 AM, Matheswaran Nachimuthu <
matheswaran_n@infosys.com> wrote:

>
> Hi,
>
> We are using apache ode 1.1 version along with MySql.
>
> we need the ode database structure to clean up the database once the
> process
> is done.
>
> Please guide me with the ode database structure so that it will be very
> useful for us to write the clean up scripts. Also please point me to the
> clean up scripts if it already exists
>

I've pasted what I have below. It's a bit nuclear as it deletes all
instances that have been failed / completed. But you should be able to adapt
it to your needs easily. Also note that ODE now automatically cleans up:

http://ode.apache.org/user-guide.html#UserGuide-InstanceDataCleanup

But at this point, you will have to build the 1.X branch to get this
feature.

Matthieu

-----

delete from BPEL_ACTIVITY_RECOVERY where PIID in (select ID from
BPEL_INSTANCE where STATE in (30,40,60));

delete from BPEL_CORRELATION_PROP where CORR_SET_ID in (select ID from
BPEL_CORRELATION_SET where PIID in (select ID from BPEL_INSTANCE where STATE
in (30,40,60)));
delete from BPEL_CORRELATION_SET where PIID in (select ID from BPEL_INSTANCE
where STATE in (30,40,60));

delete from LARGE_DATA where ID in (select LDATA_EPR_ID from
BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
STATE in (30,40,60)));
delete from LARGE_DATA where ID in (select LDATA_CEPR_ID from
BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
STATE in (30,40,60)));
delete from LARGE_DATA where ID in (select DATA from BPEL_MESSAGE where MEX
in (select REQUEST from BPEL_MESSAGE_EXCHANGE where PIID in (select ID from
BPEL_INSTANCE where STATE in (30,40,60))));
delete from LARGE_DATA where ID in (select DATA from BPEL_MESSAGE where MEX
in (select RESPONSE from BPEL_MESSAGE_EXCHANGE where PIID in (select ID from
BPEL_INSTANCE where STATE in (30,40,60))));
delete from BPEL_MESSAGE where MEX in (select REQUEST from
BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
STATE in (30,40,60)));
delete from BPEL_MESSAGE where MEX in (select RESPONSE from
BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
STATE in (30,40,60)));
delete from BPEL_CORRELATOR_MESSAGE_CKEY where CORRELATOR_MESSAGE_ID in
(select ID from BPEL_UNMATCHED where MEX in (select ID from
BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
STATE in (30,40,60))));
delete from BPEL_UNMATCHED where MEX in (select ID from
BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
STATE in (30,40,60)));
delete from BPEL_MEX_PROPS where MEX in (select ID from
BPEL_MESSAGE_EXCHANGE where PIID in (select ID from BPEL_INSTANCE where
STATE in (30,40,60)));
delete from BPEL_MESSAGE_EXCHANGE where PIID in (select ID from
BPEL_INSTANCE where STATE in (30,40,60));

delete from LARGE_DATA where ID in (select LDATA_ID from BPEL_FAULT where ID
in (select FAULT from BPEL_INSTANCE where STATE in (30,40,60)));
delete from BPEL_FAULT where ID in (select FAULT from BPEL_INSTANCE where
STATE in (30,40,60));

delete from LARGE_DATA where ID in (select LDATA_ID from BPEL_XML_DATA where
PIID in (select ID from BPEL_INSTANCE where STATE in (30,40,60)));
delete from VAR_PROPERTY where XML_DATA_ID in (select ID from BPEL_XML_DATA
where PIID in (select ID from BPEL_INSTANCE where STATE in (30,40,60)));
delete from BPEL_XML_DATA where PIID in (select ID from BPEL_INSTANCE where
STATE in (30,40,60));

delete from BPEL_SELECTORS where PIID in (select ID from BPEL_INSTANCE where
STATE in (30,40,60));

delete from LARGE_DATA where ID in (select MYROLE_EPR from BPEL_PLINK_VAL
where SCOPE in (select ID from BPEL_SCOPE where PIID in (select ID from
BPEL_INSTANCE where STATE in (30,40,60))));
delete from LARGE_DATA where ID in (select PARTNERROLE_EPR from
BPEL_PLINK_VAL where SCOPE in (select ID from BPEL_SCOPE where PIID in
(select ID from BPEL_INSTANCE where STATE in (30,40,60))));
delete from BPEL_PLINK_VAL where SCOPE in (select ID from BPEL_SCOPE where
PIID in (select ID from BPEL_INSTANCE where STATE in (30,40,60)));
delete from BPEL_SCOPE where PIID in (select ID from BPEL_INSTANCE where
STATE in (30,40,60));

delete from LARGE_DATA where ID in (select LDATA_ID from BPEL_EVENT);
delete from BPEL_EVENT;

delete from BPEL_INSTANCE where STATE in (30,40,60);