Re: Fwd: PGadmin Schema/DDL VCS plugin ...

On Wed, 2011-12-14 at 13:27 -0500, David Vaillancourt wrote:
> Hi,
>
> I would like to start implementing a version control system to be used
> to track Schema/DDL changes for a DB.
> I have compiled and run Pgadmin iii on my computer and figured the
> current plugin architecture would not be a perfect fit for this new feature.
> I would like to describe briefly what I would like to achieve, and then
> the obstacles I face.
>
> I. The functionality I would like to implement would be:
> ------------------------------------------------------------------
> 1) Able to compare 2 DBs (Schema and/or Data).
> One DB would be the 'Development' DB and the other would be a
> 'Release' DB
>
> 2) Able to generate .sql files to sync the DevDB to RelDB (and vice-versa).
> The .sql file would allow:
> a) The DevDB to get updated to the current RelDB
> b) Once some modifications are in place on DevDB, 'commit' them to
> RelDB
> c) Once RelDB 'sees' a new commit, it could place the .sql under
> source control (BZR, GIT, SVN ...)
> d) RelDB could also place a whole bunch of entities under source
> control if necessary
>
> 3) Able to textually merge code in Stored procedures.
>
> II. Architecture issues
> ---------------------------
> I think PGadmin is the best starting point for a postgresql specific VCS
> tool, since it already is able to track all entities within a DB. And
> the GUI is really well done.
> However, the difficulty I know find is that the current plugin
> functionality is somewhat limited and only allows limited exchange with
> an external process via command line (specified in plugins.ini file)
>
> Of cours, in order for the VCS system to work a tighter integration is
> needed, so here are a couple of questions:
>
> * Has anyone been working on a plugin or extensibility mechanism for
> Pgadmin?

Nope.

> * Any suggestions as to the best way to integrate this? Revamp plugin
> architecture? Add it directly in existing code?

Adding a VCS dependency to pgAdmin is not something I would like us to
have. But I also don't see how revamping the plugin architecture would
help you in any way.

> * Any other issues I might have overlooked in order to have this
> functionality shared so others can use it?

Well, I don't see that as really useful in a GUI software. If I had to
do that, I would do it in a CLI tool.

Moreover, you should get a look at check_postgres code, it's pretty
close to that with its same_schema action. It doesn't generate SQL, but
it finds structure differences between two or three databases.

You can also get a look at the 2010 GSoC project for pgAdmin:
http://wiki.postgresql.org/wiki/Database_Schema_diff_GSoC_2010

It wasn't commited, I don't remind why right now. But it still could
help you if you really want to go the pgAdmin road.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com