ode-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From René Bos <r....@pagelink.nl>
Subject RE: Upgrade from Ode 1.0 to 1.1
Date Tue, 11 Dec 2007 16:13:51 GMT
Hi,

PostgreSQL does not have BLOB or CLOB datatypes. It uses bytea and character varying for that.

The file was attached while sending it (it is attached in my sent mail folder), I will post
it below. (Beware, its long ;))

Is it true that there is no place to find any DDL for the database? I got it from Derby myself..

Rene

create table "public"."ode_activity_recovery"
(
   id bigint primary key not null,
   actions varchar(255),
   activity_id bigint,
   channel varchar(255),
   date_time timestamp,
   details character varying,
   reason varchar(255),
   retries int,
   instance_id bigint
)
;
create table "public"."ode_correlation_set"
(
   correlation_set_id bigint primary key not null,
   correlation_key varchar(255),
   name varchar(255),
   scope_id bigint
)
;
create table "public"."ode_correlator"
(
   correlator_id bigint primary key not null,
   correlator_key varchar(255),
   proc_id bigint
)
;
create table "public"."ode_corset_prop"
(
   id bigint primary key not null,
   prop_key varchar(255),
   prop_value varchar(255),
   corrset_id bigint
)
;
create table "public"."ode_event"
(
   event_id bigint primary key not null,
   detail varchar(255),
   data bytea,
   scope_id bigint,
   tstamp timestamp,
   type varchar(255),
   instance_id bigint,
   process_id bigint
)
;
create table "public"."ode_fault"
(
   fault_id bigint primary key not null,
   activity_id int,
   data character varying,
   message varchar(4000),
   line_number int,
   name varchar(255)
)
;
create table "public"."ode_job"
(
   jobid char(64) primary key not null,
   ts bigint not null,
   nodeid char(64),
   scheduled int not null,
   transacted int not null,
   details bytea
)
;
create table "public"."ode_message"
(
   message_id bigint primary key not null,
   data character varying,
   type varchar(255),
   message_exchange_id varchar(255)
)
;
create table "public"."ode_message_exchange"
(
   message_exchange_id varchar(255) primary key not null,
   callee varchar(255),
   channel varchar(255),
   correlation_id varchar(255),
   correlation_keys varchar(255),
   correlation_status varchar(255),
   create_time timestamp,
   direction int,
   epr character varying,
   fault varchar(255),
   fault_explanation varchar(255),
   operation varchar(255),
   partner_link_model_id int,
   pattern varchar(255),
   piped_id varchar(255),
   port_type varchar(255),
   propagate_trans boolean,
   status varchar(255),
   corr_id bigint,
   partner_link_id bigint,
   process_id bigint,
   process_instance_id bigint,
   request_message_id bigint,
   response_message_id bigint
)
;
create table "public"."ode_message_route"
(
   message_route_id bigint primary key not null,
   correlation_key varchar(255),
   group_id varchar(255),
   route_index int,
   corr_id bigint,
   process_instance_id bigint
)
;
create table "public"."ode_mex_prop"
(
   id bigint primary key not null,
   prop_key varchar(255),
   prop_value varchar(255),
   mex_id varchar(255)
)
;
create table "public"."ode_partner_link"
(
   partner_link_id bigint primary key not null,
   my_epr character varying,
   my_role_name varchar(255),
   my_role_service_name varchar(255),
   my_session_id varchar(255),
   partner_epr character varying,
   partner_link_model_id int,
   partner_link_name varchar(255),
   partner_role_name varchar(255),
   partner_session_id varchar(255),
   scope_id bigint
)
;
create table "public"."ode_process"
(
   id bigint primary key not null,
   guid varchar(255),
   number_of_instances int,
   process_id varchar(255),
   process_type varchar(255),
   version bigint
)
;
create table "public"."ode_process_instance"
(
   id bigint primary key not null,
   date_created timestamp,
   execution_state bytea,
   last_active_time timestamp,
   last_recovery_date timestamp,
   previous_state smallint,
   sequence bigint,
   instance_state smallint,
   fault_id bigint,
   instantiating_correlator_id bigint,
   process_id bigint,
   root_scope_id bigint
)
;
create table "public"."ode_scope"
(
   scope_id bigint primary key not null,
   model_id int,
   scope_name varchar(255),
   scope_state varchar(255),
   parent_scope_id bigint,
   process_instance_id bigint
)
;
create table "public"."ode_xml_data"
(
   xml_data_id bigint primary key not null,
   data character varying,
   is_simple_type boolean,
   name varchar(255),
   scope_id bigint
)
;
create table "public"."ode_xml_data_prop"
(
   id bigint primary key not null,
   prop_key varchar(255),
   prop_value varchar(255),
   xml_data_id bigint
)
;
create table "public"."openjpa_sequence_table"
(
   id smallint primary key not null,
   sequence_value bigint
)
;
create table "public"."store_du"
(
   name varchar(255) primary key not null,
   deploydt timestamp,
   deployer varchar(255),
   dir varchar(255)
)
;
create table "public"."store_proc_to_prop"
(
   processconfdaoimpl_pid varchar(255),
   element_id bigint
)
;
create table "public"."store_process"
(
   pid varchar(255) primary key not null,
   state varchar(255),
   type varchar(255),
   version bigint,
   du varchar(255)
)
;
create table "public"."store_process_prop"
(
   id bigint primary key not null,
   prop_key varchar(255),
   prop_val varchar(255)
)
;
create table "public"."store_versions"
(
   id bigint primary key not null,
   version bigint
)
;
create unique index sql070827115905700 on ode_activity_recovery(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 unique index sql070827115905840 on ode_correlation_set(correlation_set_id)
;
create index i_d_crltr_process on ode_correlator(proc_id)
;
create unique index sql070827115905880 on ode_correlator(correlator_id)
;
create index i_d_crprp_corrset on ode_corset_prop(corrset_id)
;
create unique index sql070827115905970 on ode_corset_prop(id)
;
create index i_od_vent_instance on ode_event(instance_id)
;
create index i_od_vent_process on ode_event(process_id)
;
create unique index sql070827115906010 on ode_event(event_id)
;
create unique index sql070827115906060 on ode_fault(fault_id)
;
create unique index sql070827115907470 on ode_job(jobid)
;
create index idx_ode_job_ts on ode_job(ts)
;
create index idx_ode_job_nodeid on ode_job(nodeid)
;
create unique index sql070827115906140 on ode_message(message_id)
;
create index i_od_mssg_messagee on ode_message(message_exchange_id)
;
create index i_d_mshng_response on ode_message_exchange(response_message_id)
;
create index i_d_mshng_partnerl on ode_message_exchange(partner_link_id)
;
create index i_d_mshng_correlat on ode_message_exchange(corr_id)
;
create index i_d_mshng_processi 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_process on ode_message_exchange(process_id)
;
create unique index sql070827115906190 on ode_message_exchange(message_exchange_id)
;
create index i_d_ms_rt_correlat on ode_message_route(corr_id)
;
create index i_d_ms_rt_processi on ode_message_route(process_instance_id)
;
create unique index sql070827115906240 on ode_message_route(message_route_id)
;
create unique index sql070827115906260 on ode_mex_prop(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 unique index sql070827115906300 on ode_partner_link(partner_link_id)
;
create unique index sql070827115906340 on ode_process(id)
;
create index i_d_prtnc_rootscop on ode_process_instance(root_scope_id)
;
create index i_d_prtnc_fault on ode_process_instance(fault_id)
;
create unique index sql070827115906380 on ode_process_instance(id)
;
create index i_d_prtnc_instanti on ode_process_instance(instantiating_correlator_id)
;
create index i_d_prtnc_process on ode_process_instance(process_id)
;
create index i_od_scop_processi on ode_scope(process_instance_id)
;
create index i_od_scop_parentsc on ode_scope(parent_scope_id)
;
create unique index sql070827115906420 on ode_scope(scope_id)
;
create unique index sql070827115906460 on ode_xml_data(xml_data_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 unique index sql070827115906480 on ode_xml_data_prop(id)
;
create unique index sql070827115906540 on openjpa_sequence_table(id)
;
create unique index sql070827115906560 on store_du(name)
;
create index i_str_prp_processc on store_proc_to_prop(processconfdaoimpl_pid)
;
create index i_str_prp_element on store_proc_to_prop(element_id)
;
create index i_str_css_du on store_process(du)
;
create unique index sql070827115906590 on store_process(pid)
;
create unique index sql070827115906610 on store_process_prop(id)
;
create unique index sql070827115906650 on store_versions(id)
;

-----Original Message-----
From: Maciej Szefler [mailto:mbs@intalio.com] 
Sent: dinsdag 11 december 2007 17:07
To: user@ode.apache.org
Subject: Re: Upgrade from Ode 1.0 to 1.1

Rene,

thanks, i think I just assumed that the postgres would have the same DDL as
derby. I'd like to check in your version, but it appears that you did not
attach it.

-mbs

On 12/11/07, René Bos <r.bos@pagelink.nl> wrote:
>
> Hello!
>
> I now have Ode 1.1 running in PostgreSQL. I first tried to migrate 1.0 to
> 1.1, and fixed the database schema and all. But than I decided not to do a
> migration but to re-execute all processes into Ode 1.1. This all worked
> out! The only difference I can remember is in ode_message_route the change
> from index to route_index. And somewhere a column made larger.
>
> I have attached the DDL, maybe you can use post it somewhere on the site
> or in the distro.
>
> I saw something strange in the SVN repro. In
> http://svn.apache.org/viewvc/ode/trunk/dao-hibernate-db/src/main/sql/simplesched-postgre.sql?view=markup
> and
>
> http://svn.apache.org/viewvc/ode/trunk/dao-hibernate-db/src/main/sql/simplesched-postgres.sql?view=markup
> there is no PostgreSQL queries but for derby. For other schemas this is
> also the case.
>
> Thanks!
>
> Rene
>
>
>
> -----Original Message-----
> From: matthieu.riou@gmail.com [mailto:matthieu.riou@gmail.com] On Behalf
> Of Matthieu Riou
> Sent: maandag 10 december 2007 16:40
> To: user@ode.apache.org
> Subject: Re: Upgrade from Ode 1.0 to 1.1
>
> On Dec 10, 2007 12:01 AM, René Bos <r.bos@pagelink.nl> wrote:
>
> > Hello!
> >
> > First I want to thank you for your answer (right on time, after
> 2.5months? ;)). At the moment I have the time (and need) to do the upgrade
> from
> > Ode 1.0 to 1.1. Because I also want to use a PostgreSQL database with
> Ode
> > I did some research. I found out from the database dump that I don't
> have
> > anything in the quartz tables (Except from the QRTZ_LOCKS one). I think
> this
> > is because I don't use scheduled activities like wait. Now is my
> question,
> > should it be possible in this situation to use the same database in Ode
> > 1.1 as with Ode 1.0?
> >
>
> There have been some column additions/removal between 1.0 and 1.1 but I
> don't think there's been many of them. If you diff your schema with the
> one
> we generate in ODE 1.1 and post it here we can probably tell you how to
> handle all the differences.
>
> Also do you want to migrate running instances or just the history with
> process definitions? I believe we changed the serialized representations
> of
> instances a bit so migrating running instances could be challenging. We're
> currently implementing a fix for this so that migration from 1.1 to 1.2will
> be easier.
>
> Cheers,
> Matthieu
>
>
> >
> > Rene
> >
> > -----Original Message-----
> > From: Alex Boisvert [mailto:boisvert@intalio.com]
> > Sent: woensdag 26 september 2007 16:17
> > To: user@ode.apache.org
> > Subject: Re: Upgrade from Ode 1.0 to 1.1
> >
> > On 9/25/07, René Bos <r.bos@pagelink.nl> wrote:
> > >
> > > I have Ode 1.0 running in production for about a month now, and its
> > doing
> > > very good! But I want to upgrade to Ode 1.1 and I was wondering how to
> > do
> > > that without having to stop all my processes. Normally I can do this,
> > but I
> > > think you guys changed the database structure for the new scheduler,
> > isn't
> > > it? Is there an upgrade script or something?
> >
> >
> > Hi René,
> >
> > There are no upgrade scripts to upgrade from Ode 1.0 to 1.1 although we
> > can
> > definitely help.   Beyond schema changes, I believe there are
> differences
> > between serialized objects (Quartz vs SimpleScheduler objects) so I
> think
> > there would be some coding involved.  At Intalio we were fortunate
> enough
> > that customers have either migrated early to the SimpleScheduler, or
> used
> > co-existing instances of Ode until all older processes completed and the
> > data archived.   Depending on your environment, running coexisting Ode
> > instances might be a simpler bet.   And I can assure you that moving
> > forward, we'll have migration scripts from 1.1 onwards.
> >
> > cheers,
> > alex
> >
>

Mime
View raw message