On Wed, Jan 28, 2009 at 3:45 AM, Matheswaran Nachimuthu < [email protected]> 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);
