[OGo-Users] Migration of ogo

[Peter Brueckner]

Hello all,

this week one of out customers wants to have a new
machine for his ogo-installation.

Suse 9.2->10.3
RealWare -> VMWware
Postgres 7.4 -> 8.2
Ogo1.0Beta(r1412) -> OGo1.1(trunc) (r2051)

so we decided to do an export of data:

pg_dump -aD -f ogo.sql -U OGo OGo
and files:
tar cvf ogo-files.tar .=20
  in /var/lib/opengroupware.org

then we installed an empty ogo-instance:

rpm -Uvh *.rpm=20
with sope4.7 and ogo-1.1 in the trunc version

we looked at ogo.sql:
oh :) table_version and object_model have to
be removed!! OK first step

then: the standard objects are in ogo.sql and
in the database so:
=2D---------------------------------------------
#!/bin/bash
su postgres -c "dropdb OGo"
su postgres -c "dropuser OGo"
/usr/local/share/opengroupware.org-1.1/dbsetup/database_setup_psql.sh initi=
al
psql OGo OGo  <<EOF
delete from news_article;
delete from company_info;
delete from staff;
delete from team;
delete from person;
EOF
=2D--------------------------------------------
gives an empty database.

after that we installed the constraints. Very bad!
Nearly no record f.e. telephone can be inserted!

All constraints that refer company are unusable
because of limitations in postgres with inherited
tables (also for document/doc/note)

so we removed all such constraints and imported
the data.=20

after that we wrote some scripts for manual check:
=2D-----------------------------------------------------------------
#!/bin/bash

CTABS=3D"company_info company_value company_assignment date_company_assignm=
ent=20
project_company_assignment address telephone staff"
CSUBTABS=3D"company_assignment"
DOCTABS=3D"document_version document_editing"

cleantab()
{
  tab1=3D$1
  tab2=3D$2
  field=3D$3
  echo "delete from ${tab1} where ${tab1}_id in ( select ${tab1}.${tab1}_id=
=20
from ${tab1} left outer join ${tab2} on (${tab1}.${field}=3D${tab2}.${tab2}=
_id)=20
where ${tab2}.${tab2}_id is null)" |psql OGo OGo
}

for t in $CTABS
do
  cleantab $t company company_id
done

for t in $CSUBTABS
do
  cleantab $t company sub_company_id
done

for t in $DOCTABS
do
  cleantab $t document document_id
done

echo "delete from date_info where date_id in (select date_id from date_x le=
ft=20
outer join company on (date_x.owner_id =3D company.company_id) where=20
company.company_id is null)" | psql OGo OGo

echo "delete from date_company_assignment where date_id in (select date_id=
=20
from date_x left outer join company on (date_x.owner_id =3D company.company=
_id)=20
where company.company_id is null)" | psql OGo OGo

echo "delete from date_x where date_id in (select date_id from date_x left=
=20
outer join company on (date_x.owner_id =3D company.company_id) where=20
company.company_id is null)" | psql OGo OGo
=2D------------------------------------------------------------------------=
=2D---
Than we created the obj_info table:
psql OGo OGo=20
</usr/local/share/opengroupware.org-1.1/dbsetup/PostgreSQL/pg-fill-objinfo.=
psql=20

then unpack the data in ~ogo:

tar xvf ogo-files.tar

and after some fiddeling ..... it works (2 days later).
There are some questions:
1. Nobody uses the constraints? With the actual postgres
release the inherits-feature is not usable with constraints!
see: http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html
  paragraph: 5.8.1 caveats
2. Is there a regular way for upgrading?
3. Is there a way for checking the constraints (possibly
  other things) in the database.
4. We are working with OGO/Skyrix for years and are very
comfortable but every upgrade is like hell so:
   Who wants to do an upgrade-script with some checks?
   (of course for money)


thanks to all

 =20
=2D-=20
mit freundlichen Gr=FC=DFen / Kind regards
Peter Br=FCckner
=46ON +49=3D361-21240.12

Br=FCckner & Jarosch Ingenieurgesellschaft mbH
Germany, 99084 Erfurt, Nonnengasse 5a=20
=46ON +49=3D361-21240.0 FAX +49=3D361-21240.19
WEB <http://www.bj-ig.de> MAIL <mailto:info@bj-ig.de>
Sitz Erfurt Register HRB 3134 Amtsgericht Erfurt
Steuer 151/106/02585 UID DE150092958=20
Gesch=E4ftsf=FChrer Dipl.-Ing. Wolfgang Jarosch, Dipl.-Ing. Peter Br=FCckner