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

Guillaume,

I appreciate you taking the time to clarify some points. As for the GsOC
project it is exactly what I had in mind, is there any code for that
project?
Is there a 'project lead' I can contact?

As to the plugin architecture, here are the reasons why I wanted to
extend the current framework
1) The 'VCS-plugin' I want to build needs to know much about the schema,
to be able to compare 2+ schemas

2) Pgadmin 'knows' quite a bit about the DB schema in very fine details,
especially the DB entities such as pgTable, pgRole, pgFunction etc...
This information is needed by the 'VCS-plugin' ...

3) However there is not proper way to pass in memory representations of
various DB entities from PGadmin to the plugin (as I understand it at
the moment)
At the moment the only way I see the plugins receiving info from
PGadmin is through the command line (plugins are launched as processes).

4) With the current PGadmin plugin architecture, here are my
alternatives (please feel free to correct me or suggest any more
scenarios you see)
a) Serialize DB entities into an XML file , pass the generated XML
file path to plugin
b) Add some form of interprocess communication scheme between
plugin and PGadmin, to allow querying/marshalling of information
concerning DB Entities
c) Compile PGAdmin into a library, and reuse the code to query
Schemas and have the plugin use this library
d) Allow a plugin to be loaded as a Dynamic lib into PGadmin, and
define a Plugin interface for communication to occur between the two

Please feel free to comment on any points I have elaborated on here ...

Cheers!

On 15/12/2011 5:44 AM, Guillaume Lelarge wrote:
> 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.
>
>