Database Schema diff GSoC 2010

From PostgreSQL wiki
Jump to navigationJump to search

Abstract

The idea is to make enhancement to pgAdmin,which would provide the user with an intuitive graphical comparer and a synchronizing feature. The user could know difference between two selected database(objects), and this would enable user to visualize the comparison (structural) before applying(synchronizing) the seen difference on the source/target db's or at both ends. This feature would enhance the developer/dba ability to not only apply all changes to the production environment with just few clicks, but to be sure what would happen to the data base in question. User could also apply selective changes. This would remove users from the existing way of data script creation and application which is both tedious and boring and is less flexible.

Project Scope

  For the GSoC Project i have limited the scope to work only on the one way comparing/synchronizing(source to destination/destination   to source) with the visualizing capability. The posgres objects i would look for schema difference include tables(constraints (primary key, foreign key, not null, unique indexes)) and views.

Project Details

The enhancement feature would have the following functionalities.

  • The feature would first ask user with details for authenticating user for different data base servers.
  • The feature would only be functional, when the user is at least connected to single source. The icon for the feature would remain gray until then.
  • As the user connects to any Postgres server using the built in facility of pgAdmin, the synchronization feature icon would get enabled to be used.
  • As the user clicks on the icon , the feature would show the user with a tree view, with all the registered servers(connected/disconnected) in pgAdmin asking user for selecting the remote/target server. http://sites.google.com/site/loaderboot/ideasscreenshots (see figure 1)
  • If the server is in unconnected state, the feature pop ups a dialog, asking for user name or password to connect to it and would eventually select the target server. http://sites.google.com/site/loaderboot/ideasscreenshots (see figure 2)
  • This would eventually authenticate the users, and would now have the option to select the db of his/her choice
  • After data base selection, user would be provided with a split view pan each having a tree structure. The left hand pan of the split view would show any one of the source db, while the right hand would show the other one. http://sites.google.com/site/loaderboot/ideasscreenshots (see figure 3)
  • The tree would be populated with the tables of the database.
  • As one clicks on any table, the two tables if present on both sides would get selected and two images icon showing structural differences would be visible in the lower part of the GUI to show what type of difference exist
  • Each image (structural) would have corresponding buttons to view or apply the selected changes. In the bottom there is a button named "Synchronize All", that would make the two db comes in sync with a single click.
  • Now if the user clicks on the structural button/image icon, a new window would get spawned showing the difference between the two structures.
  • This window for structural changes would show the schema as a grid with its changes represented using various color highlighting , e.g green= attribute added, red=attribute removed, yellow=other changes corresponding to changed data type, index etc.http://sites.google.com/site/loaderboot/ideasscreenshots ( see figure 4 )
  • This window would show three types of views, one for source sync, one for target sync or one at both end each with corresponding apply buttons.
  • As the user would press the apply button, the visualizer would report any conflicts and would the ask the user to resolve it. As the various conflicts get resolved the renderer would change the visualization to support the result.
  • Finally the user would be intimated when the synchronization is done successfully with the tables showing no differences or conflicts between them.
  • Similarly a new window is spawned for views differences.
Retrieved from "https://wiki.postgresql.org/index.php?title=Database_Schema_diff_GSoC_2010&oldid=17044"