Development of improvements in pgAdmin physical database designer module

Development of improvements in pgAdmin physical database designer module GSOC 2011

The main benefit that this project introduces in PostgreSQL community is not afford to lose the effort made at last year’s gsoc in the creation of the physical database designer and its graphical modeling framework. Accomplishing it by using database designer and modeling framework source code as a starting point for another iteration of development, that will print a new life at physical database designer source code making it commitable and improve their usability by adding new needed features that allows its routine use in the PostgreSQL community.

Derivate benefits of having a physical database designer at PostgreSQL administration tools are:

• Increase productivity of databases administrators because they can create new databases (models) in a graphical way where they can introduce columns, tables, restrictions and more only dragging components or providing few clicks and little type of information . Saved time can be use to optimize database design by example.
• Data entry, updates and deletions can be optimized because now database designers will have a big picture of the model.
• Data retrieval, summarization and reporting will also be efficient for same reasons.
• Since the database follows a well-formulated model, it behaves predictably.
• The model helps in somewhat to self-documenting the database.
• Changes to the database schema will be easy to make in a graphical way.

Because above reasons physical database modeler (and its graphical framework) has been created especially to postgreSQL database, we are able to personalize it as we desire or as new features will be added to the database. And we can have now a specify set of code (the graphic library created) that allow to create future modelers like datawarehouse modeler, data migration, and others or simply to eliminate dependency of wxOGL with a better modeling framework.

Inch-stones

• Initial Commit of database designer into pgAdmin repository or at least get status of commitable from pgAdmin developers.
• Addition of Save/Load feature for models.
• Addition of improvements: support of views, support to user defined types, overview panel, allow personalization of drawing for objects.
• Addition of model partitioning feature.
• Addition of update instead create feature for models (some form of scheme binding needed as pre-requisite)
• Addition of Data Definition Language import to models.

Quantifiable results

Main quantifiable results at this project proposal in order of consecution are:

1. A database designer source code created at last year’s GSoC will be ready to commit at pgAdmin development tree at source code repository (this will require a list of things to do this from pgAdmin development team, but not adding new features, this will be done later).
2. Will be add a feature to save and load database models.
3. Will be add a feature to import SQL’s DDL sentences into database models (will require at least the creation of a scanner and parser for DDL sentences) and reverse engineering existing tables/database or model to database synchronization (with some restrictions initially).
4. Will be add the following list of items:
   1. Support to creation of Views (SQL sentence from user if valid will create a view, wizard related probably), Notes (Text), Colors Region (decorative feature).
   2. Support to user defined types.
   3. Overview panel.
   4. Ability to change individual object (tables, relationship) properties to objects, like line width, line color, font color, font size, background colors and others.
5. Support to modify previously created tables at database, this feature will require binding of model to a scheme ( if scheme not exist when opening model default behavior will be create table instead of update ).
6. Model partitioning in several submodels (or multiple models support), this feature will allow to reuse same table across multiple models and any change at a shared table in a model will update it at the others submodel (this feature will help to split (or divide) big database models into several submodels, like for example a supermarket model can be split into sales, hr, warehouses and others, where probable some tables will be shared between submodels but not all foreign key tables will be available at that particular submodel (relationship will be automatic created if table source of fk exists if not, only columns will be shown with fk modifier).
7. Bonus features (if time is enough but mostly won’t be in that way):
   1. Zoom.
   2. Undo/Redo.

Project Schedule

Project schedule show here is only for coding phase, others task like research, studying and others will be done starting from project acceptance:

Project Schedule

Initial Date	Ending Date	Ending Week	Deliverable	Url to try it [Published when available (git tag)]
May-24	June-5	2	Patch of Database Designer with commitable candidate status.	not available yet
June-6	June-26	5	Patch with Addition of improvements: support of views, support to user defined types, overview panel, allow personalization of drawing for objects	not available yet
June-27	July-10	7	Patch with Save / Load feature	not available yet
July-11	July-24	9	Patch with model partitioning feature	not available yet
July-25	August-03	11	Patch with sync tables feature	not available yet
August-04	August-14	12	Patch with DDL import to models feature.	not available yet
August-15	August-22	13	Final patch for pgAdmin after bug hunting	not available yet

Contact Info

ziul1979 [at the email symbol] google mail (gmail.com) or http://twitter.com/xiul for daily updates details

Report of weekly work at monday in pgadmin-hackers list.