tracking db changes / comparing databases

[a***@xs4all.nl]

Hello Group,

This question has been brought up on various mailing lists, but without
any definite answers if I am correct.

I am working on a system that can be used to rapidly create simple to
medium complex applications in PHP. It basically features a forms-system,
a reporting system, and database-user-administration. Thus, when someone
wants to create a simple (up to, say, 10 tables) application, (s)he can
create the forms, create a little PHP-logic (may even be unnecesery), et
voila. Something like access, but webbased and smaller in its feature-set.
PostgreSQL is the only backend at this moment.

On of the problems I'm having is keeping a development/test-session
seperate from a production-system. I *Want* to have a seperate
production-installation, develop on my test-installation and when a
feature is complete and tested push the modifications to the production
session. To do that I'd need to be able to produce:
1. data-definition changes
2. data added/modified to the tables

You might be tempted to argue "If a users upgrades your software, he
should create a new install and *you* should create a tool that converts
his existing data" and you'd probably be right in the case of
version-upgrades for users that only want to use "stable" releases, and
upgrade only sporadically.

But in the case of online colaboration (and for my own personal sessions)
it would be very nice if new/changed data-definitions and data could be
shared instantly (in stead of a full dump of the test-db).

Last of all, I have a script that fills the database for the user at
install-time. It asks a few questions and then fills the database using
the user provided input. That script needs to be updated after new
features are introduced, and thus I need to know what to add. Creating a
new pg_dump for the entire database and script that one for each and every
version would encompass and effort a lot greater than implementing the
features.

I did find an old pgdiff util on sourceforge, but development has has been
idle for quite some time now (2 years IIRC). Don't recon it will support
the most recent pg features. Would a standard diff work on the dumps?

How do you share the data-definitions of your project(s) when they change
(template1) ? Do you create all system-tables at pg_init runtime, and thus
diff the C-source?

I might be able to code a (not so large) project but am not sufficiently
able in C. Perl would be okay. But I'd need a good plan to start from.

Thank you for any thoughts,
Sincerely,
Arian Prins.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Michael Adler]

Post by a***@xs4all.nl
How do you share the data-definitions of your project(s) when they
change (template1) ? Do you create all system-tables at pg_init
runtime, and thus diff the C-source?

We track all DDL in CVS and manually create "patches" to upgrade all
installations of our product. It works for us.

-Mike


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Mitch Pirtle]

Post by a***@xs4all.nl
How do you share the data-definitions of your project(s) when they change
(template1) ? Do you create all system-tables at pg_init runtime, and thus
diff the C-source?

The best approach IMHO is to use cvs and diff. For a more PHP-centric
approach, you could look at ADOdb:

http://adodb.sf.net/

Does a lot more than just abstract the database, also provides caching
of query results, handy utilities for building form elements, etc. But
the most topical feature is the inclusion of the data dictionary and
ADOdb XML Schema (AXMLS):

http://phplens.com/lens/adodb/docs-datadict.htm

Here you can use database-agnostic XML markup (and also reverse-engineer
schemas). You can basically use this to migrate your schemas. I am
looking into using this for the next major release of Mambo CMS for
upgrades and installs.

HTH,

-- Mitch


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend