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 < > [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); > > -- 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.
