Database Designer for pgadmin GSoC 2010
From PostgreSQL wiki
Database Designer for pgAdmin
Main idea is to build a physical database modeler for pgAdmin that improve developers productivity. I propose to implement this modeler accomplishing with the Model Driven Application (MDA) architecture, and always thinking in the way to create a shared rendering mini-engine that can be use in the future, under the philosophy of the model view controller (MVC) pattern, to allow introduction of new features at a near future.
This project main goal is to build a physical database modeler for the pgAdmin tool, and I propose to implement this modeler accomplishing with the Model Driven Application (MDA) architecture and always thinking in the way to create a shared rendering mini-engine that can be use in the future, under the philosophy of the model view controller (MVC) pattern, to allow introduction of new features at a near future.
Now I'm going to describe in details this idea, focusing in what I want to do and not how I'm going to do it (this will be explained later).
From my point of view the pgAdmin database modeler should be a tool that help developers in the work of create or modify a database, allowing to visually edit/create tables, relationships, constraints, index, trigger and other information important in database physical model development but highly integrate with the special features of postgreSQL database like types or hierarchy.
My idea is to create a canvas where the user (developer) with the fewer clicks and hot keys can be able to edit or modified the database meta-data, offering functions like this:
- Main graphic object will be tables but you can hide/show some parts of table like columns, indexes, restrictions (little button + or - at top of each part by example), sometimes tables information displayed are bigger than screen, because this some kind of scroll mechanism should be implemented.
- Add tables or views with just a click.
- Creating a relationship between two tables by just drag and drop from source to destination table and selecting kind of relationship in some way.
- With a simple click or right button click change kind of relationship (Many, One) and optionality (mandatory or optional) or hierarchy.
- Edit columns names just by click on the column and directly write on the canvas.
- Add with just a click or right menu action new columns, constraints, index, triggers.
- Change tables properties like name in case of be able to do it, and inform user about how their changes affect the data stored on the database in case of modification of a reverse engineering database that have been used before to store data or if not possible to apply changes inform user too.
- Allow two modes of work with the database modeler, first one for database creation/modification, second one for database model observation.
- Allow Different notations for models representation (allow but only one implemented if not enough time), given independency between database modeler internal model and graphical representation.
- One tables repository but multiple views with the use of different diagrams, by example in one user (or schema) we can have multiple tables that can be used in more than one diagram or model (depending on how persistence will be implemented: inside database or flat files in some format).
- Integration with all features of pgAdmin allowing doing some duties in a fast way.
- Add others functions that will be considered important for community and time restrictions allow to do it if possible.
This modeler should generate & execute database creation scripts and to do reverse engineering of databases (but for time reason reverse engineering part can get only the basis that allow their later development if not enough time is available at gsoc). And because of this all the database meta-data should be abstracted in a way that allow to use a single model for multiple databases respecting as much as possible their difference but being flexible at the same time.
Project Status at 26/06/2010
- pgAdmin graphic library using same design patterns (when possible) as hotdraw for database designer done, just needing some minor bugs and memory leaks hunting.
Finish simple table memory structures and graphical representation using created library.
ziul1979 [at the email symbol] google mail (gmail.com) or http://twitter.com/xiul for daily updates details