Thread: Fwd: PGadmin Schema/DDL VCS plugin ...
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?
* Any suggestions as to the best way to integrate this? Revamp plugin
architecture? Add it directly in existing code?
* Any other issues I might have overlooked in order to have this
functionality shared so others can use it?
Thank you,
David Vaillancourt
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
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.
>
>
On Wed, Dec 14, 2011 at 6:27 PM, David Vaillancourt <david_v@sympatico.ca> 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. We had version control for database objects in pgAdmin II - when we started on pgAdmin III, a survey revealed it was a feature that literally no one (except me at the time) wanted. I'd be very wary of accepting code to implement such functionality again, unless there were others who saw benefit in it, and it was going to be relatively low maintenance in the long term. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Thu, Dec 15, 2011 at 10:44 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > > 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. Basically it didn't get finished. There was still a lot of work to do, which I didn't have the time for, and the student had no interest in completing post-GSoC. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Thu, Dec 15, 2011 at 3:36 PM, David Vaillancourt <david_v@sympatico.ca> wrote: > Dave, > > Thanks for the quick feedback. I'm really surprised that people who use VCS > for their 'normal' code, wouldn't see a use for the same when it comes to DB > Schema/DDL versions. > In any case, my goal is not to add the functionality directly to PGAdmin, > but rather create a plugin for PGadmin to use. Yeah, the current plugin architecture really isn't suited to that I doubt - its pretty simplistic. I certainly have objections to that being enhanced though. > As I mentionned earlier, the extent of the modifications to PGadmin would be > to extend the plugin framework to allow a tighter coupling between plugins > and PGadmin. > I guess maintaining a plugin is different from maintaining yet another > feature in the existing PGAdmin code, correct me if i'm wrong. Currently a "plugin" is really just an external application that pgAdmin can fire up and pass command line options to. > Finally, since no one wanted to use the VCS feature in PGadmin, what do > teams of developers use to track versions of Schemas (Tables, Views, > Procedures...)? > Do most developers simply not use any? Or do the 'hack' their own? I'd be > surprised if they used proprietary tools ... Well, views may have changed now - the original code was probably written nearly 10 years ago. However, the PostgreSQL infrastructure team just tend to keep SQL scripts in GIT and update them when necessary. For the Postgres Enterprise Manager product that I work on in my day job (which happens to be based on pgAdmin), we maintain one script which creates a fresh database from scratch (pemserver.sql), and incremental scripts to upgrade an existing database from one version to another (eg. 2_0_0-2_0_1.sql, 2_0_1-2_0_2.sql and so one). All of those are version controlled in GIT as well. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Dave, Thanks for the quick feedback. I'm really surprised that people who use VCS for their 'normal' code, wouldn't see a use for the same when it comes to DB Schema/DDL versions. In any case, my goal is not to add the functionality directly to PGAdmin, but rather create a plugin for PGadmin to use. As I mentionned earlier, the extent of the modifications to PGadmin would be to extend the plugin framework to allow a tighter coupling between plugins and PGadmin. I guess maintaining a plugin is different from maintaining yet another feature in the existing PGAdmin code, correct me if i'm wrong. Finally, since no one wanted to use the VCS feature in PGadmin, what do teams of developers use to track versions of Schemas (Tables, Views, Procedures...)? Do most developers simply not use any? Or do the 'hack' their own? I'd be surprised if they used proprietary tools ... On 15/12/2011 10:17 AM, Dave Page wrote: > On Wed, Dec 14, 2011 at 6:27 PM, David Vaillancourt > <david_v@sympatico.ca> 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. > We had version control for database objects in pgAdmin II - when we > started on pgAdmin III, a survey revealed it was a feature that > literally no one (except me at the time) wanted. > > I'd be very wary of accepting code to implement such functionality > again, unless there were others who saw benefit in it, and it was > going to be relatively low maintenance in the long term. >
On Thu, 2011-12-15 at 15:43 +0000, Dave Page wrote: > On Thu, Dec 15, 2011 at 3:36 PM, David Vaillancourt > <david_v@sympatico.ca> wrote: > > Dave, > > > > Thanks for the quick feedback. I'm really surprised that people who use VCS > > for their 'normal' code, wouldn't see a use for the same when it comes to DB > > Schema/DDL versions. > > In any case, my goal is not to add the functionality directly to PGAdmin, > > but rather create a plugin for PGadmin to use. > > Yeah, the current plugin architecture really isn't suited to that I > doubt - its pretty simplistic. I certainly have objections to that > being enhanced though. > Which objections? just to know, because I don't see any reasons to object on enhancing the plugin architecture without knowing the proposed enhancements. > > As I mentionned earlier, the extent of the modifications to PGadmin would be > > to extend the plugin framework to allow a tighter coupling between plugins > > and PGadmin. > > I guess maintaining a plugin is different from maintaining yet another > > feature in the existing PGAdmin code, correct me if i'm wrong. > > Currently a "plugin" is really just an external application that > pgAdmin can fire up and pass command line options to. > +1 > > Finally, since no one wanted to use the VCS feature in PGadmin, what do > > teams of developers use to track versions of Schemas (Tables, Views, > > Procedures...)? > > Do most developers simply not use any? Or do the 'hack' their own? I'd be > > surprised if they used proprietary tools ... > > Well, views may have changed now - the original code was probably > written nearly 10 years ago. However, the PostgreSQL infrastructure > team just tend to keep SQL scripts in GIT and update them when > necessary. For the Postgres Enterprise Manager product that I work on > in my day job (which happens to be based on pgAdmin), we maintain one > script which creates a fresh database from scratch (pemserver.sql), > and incremental scripts to upgrade an existing database from one > version to another (eg. 2_0_0-2_0_1.sql, 2_0_1-2_0_2.sql and so one). > All of those are version controlled in GIT as well. > Looks like a PostgreSQL extension to me :) -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com PostgreSQL Sessions #3: http://www.postgresql-sessions.org
On Thu, 2011-12-15 at 10:36 -0500, David Vaillancourt wrote: > Dave, > > Thanks for the quick feedback. I'm really surprised that people who use > VCS for their 'normal' code, wouldn't see a use for the same when it > comes to DB Schema/DDL versions. Oh I think we do. But I guess it would be better to use the VCS directly, and not through pgAdmin. > In any case, my goal is not to add the functionality directly to > PGAdmin, but rather create a plugin for PGadmin to use. > OK. No problem with that. > As I mentionned earlier, the extent of the modifications to PGadmin > would be to extend the plugin framework to allow a tighter coupling > between plugins and PGadmin. Depends on what you really want to do. I'm afraid it'll be a lot of work. > I guess maintaining a plugin is different from maintaining yet another > feature in the existing PGAdmin code, correct me if i'm wrong. > Yeah, but you're on both sides here. To be able to code your plugin, you need to enhance the plugin architecture. Meaning more code into pgAdmin. I'm not opposed to it, just say that it's not only writing a plugin. > Finally, since no one wanted to use the VCS feature in PGadmin, what do > teams of developers use to track versions of Schemas (Tables, Views, > Procedures...)? VCS directly. > Do most developers simply not use any? Or do the 'hack' their own? I'd > be surprised if they used proprietary tools ... > Well, I'm afraid most developers/DBAs don't use any VCS for their schema. After seeing lots of them through my day work, I'll only see one or two using a VCS. Which means a really low percentage. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com PostgreSQL Sessions #3: http://www.postgresql-sessions.org
On Thu, Dec 15, 2011 at 3:53 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > On Thu, 2011-12-15 at 15:43 +0000, Dave Page wrote: >> On Thu, Dec 15, 2011 at 3:36 PM, David Vaillancourt >> <david_v@sympatico.ca> wrote: >> > Dave, >> > >> > Thanks for the quick feedback. I'm really surprised that people who use VCS >> > for their 'normal' code, wouldn't see a use for the same when it comes to DB >> > Schema/DDL versions. >> > In any case, my goal is not to add the functionality directly to PGAdmin, >> > but rather create a plugin for PGadmin to use. >> >> Yeah, the current plugin architecture really isn't suited to that I >> doubt - its pretty simplistic. I certainly have objections to that >> being enhanced though. >> > > Which objections? just to know, because I don't see any reasons to > object on enhancing the plugin architecture without knowing the proposed > enhancements. Sorry - typo. "I certainly don't have objections..." -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Thu, Dec 15, 2011 at 4:18 PM, David Vaillancourt <david_v@sympatico.ca> wrote: > The changes to the plugin framework I intend to implement would keep the > existing functionality, but add to it also. > Please let me know if this is an avenue I should drop, as I will find an > alternative ... It do not want to work against the PGadmin team. That's fine with me, assuming the changes you wish to make will fit into that model cleanly. > As for the workflow you describe to track Schema versions, this is the way > we do things right now. > So here are my questions: > > * How do you manage incremental scripts numbering for multiple developers > working on the same DB Schema? The same way as any of the other code in the repo. When a developers work is finished, reviewed and then committed, the guys working in parallel will just do a pull and rebase their work over the latest changes. That may not be practical with hundreds of developers, but there typically aren't more than 7 or 8 developers at most working on this project, so there's little coordination required beyond git push/pull/rebase. > * Once a developer creates an incremental script, who/how do you modify the > original creating script to reflect these new changes (ex: pemserver.sql)? Manually. If they've added some new columns to a table the upgrade script will contain ALTER TABLE statements, whilst the CREATE TABLE statements in pemserver.sql will get the additional columns added. On a couple of occasions (for example, when updating 237 SQL objects we call probes) that gets a touch tedious, but the vast majority of the time it's a non-issue. > * If developer A has pending modifications but another developer B has > commited changes to the schema since, how does developer A make sure he is > not going to overwrite changes made by A? This especially problematic for > stored procedures code that is modified by developers (for us anyways) Typically developer A will just do a "git pull". If there are merge conflicts then he may need to do a "git stash" first, and then "git stash apply" to re-apply his changes, fixing any conflicts manually. If he's already committed to his local repo, git detects the conflicts and A can manually fixup them up and commit the result, then push it back to the central repo when he's done. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
The changes to the plugin framework I intend to implement would keep the existing functionality, but add to it also. Please let me know if this is an avenue I should drop, as I will find an alternative ... It do not want to work against the PGadmin team. As for the workflow you describe to track Schema versions, this is the way we do things right now. So here are my questions: * How do you manage incremental scripts numbering for multiple developers working on the same DB Schema? * Once a developer creates an incremental script, who/how do you modify the original creating script to reflect these new changes (ex: pemserver.sql)? * If developer A has pending modifications but another developer B has commited changes to the schema since, how does developer A make sure he is not going to overwrite changes made by A? This especially problematic for stored procedures code that is modified by developers (for us anyways) I am genuinely interested by your answers, since it may well be that we adopt your workflow if it improves ours. Cheers! On 15/12/2011 10:43 AM, Dave Page wrote: > On Thu, Dec 15, 2011 at 3:36 PM, David Vaillancourt > <david_v@sympatico.ca> wrote: >> Dave, >> >> Thanks for the quick feedback. I'm really surprised that people who use VCS >> for their 'normal' code, wouldn't see a use for the same when it comes to DB >> Schema/DDL versions. >> In any case, my goal is not to add the functionality directly to PGAdmin, >> but rather create a plugin for PGadmin to use. > Yeah, the current plugin architecture really isn't suited to that I > doubt - its pretty simplistic. I certainly have objections to that > being enhanced though. > >> As I mentionned earlier, the extent of the modifications to PGadmin would be >> to extend the plugin framework to allow a tighter coupling between plugins >> and PGadmin. >> I guess maintaining a plugin is different from maintaining yet another >> feature in the existing PGAdmin code, correct me if i'm wrong. > Currently a "plugin" is really just an external application that > pgAdmin can fire up and pass command line options to. > >> Finally, since no one wanted to use the VCS feature in PGadmin, what do >> teams of developers use to track versions of Schemas (Tables, Views, >> Procedures...)? >> Do most developers simply not use any? Or do the 'hack' their own? I'd be >> surprised if they used proprietary tools ... > Well, views may have changed now - the original code was probably > written nearly 10 years ago. However, the PostgreSQL infrastructure > team just tend to keep SQL scripts in GIT and update them when > necessary. For the Postgres Enterprise Manager product that I work on > in my day job (which happens to be based on pgAdmin), we maintain one > script which creates a fresh database from scratch (pemserver.sql), > and incremental scripts to upgrade an existing database from one > version to another (eg. 2_0_0-2_0_1.sql, 2_0_1-2_0_2.sql and so one). > All of those are version controlled in GIT as well. >