Gsoc08-querybuilder

Proposal

Abstract

A graphical Interface improvement at pgAdmin III that will allow users to visually build a complete SQL statement, without typing any clause (type only when really needed). Building complex SQL queries in an easy way, reducing debugging, syntax and logic errors.

Detailed Info

The main idea of the project is to create a new way of query postgreSQL database that will be very efficient in terms of time of creation of query and with the minimum overhead at learning curve to create the queries. Because when a database is used by user, the main process done with it, will be queries to data that this databases manages, and this must be done in a specifically language, like SQL, but there are some troubles that difficult learning of this language, by example[1]:

• User must have a good knowledge of the structure and names of databases objects to query.
• User may have erroneous concepts of the SQL language and the database data model, that difficult their understanding of how to create an effective SQL sentence that meets their needs.
• Is difficult for beginners to work with unfriendly command lines interface or an IDE that allow constructing SQL sentences by typing commands, because of the lack of good explanation of semantic errors and inadequate feedback of errors when creating SQL sentences.

For that reasons, the main goal is to visually query the database with the minimum user interaction in terms of typing code or knowledge of database model structure (Not need to deeply known the structure of database model like Entity Relationship to query it), because of this the project have at least four main areas to Develop:

1. Communication with postgreSQL database for inverse engineering of database tables to use. This probably can be accomplished reutilizing pgadmin source code.
2. Graphical representation of workspace where user can drag components and construct SQL sentence in a visually way, this include translate user commands on this workspace to Internal Representation Structure of Query.
3. Internal Representation of Query created visually for user, and generation of SQL sentences based on this representation.
4. SQL sentences retrieve data displaying.

All this four parts working together get a complete solution to query database in a visually way or in other words a graphical query builder.

Benefits to the PostgreSQL Community

The main benefits that this project introduces in PostgreSQL community are:

1. Increase productivity of databases administrators because they don’t need to have great knowledge of database tables structure of an application to create a query for that tables, all that they need will be available in 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 by example.
2. Eliminate limitation of use of pgAdmin query interface to people who actually knows SQL language. Allowing to very rookie people the creation of “complex” SQL sentences without really knowing the SQL language. This means that people learning how to use postgreSQL database and with little technical knowledge can query database stored data in tables in way that will be impossible for them because their lack of knowledge of SQL. Because of this postgreSQL will be more friendly with people by example who uses applications like excel or access (or equivalents in open office) and they can use it without worrying for learning SQL to access stored data.
3. This improvements can be extended over the time to allow the creation of reports in different formats (like a reporting tool that uses this visually query way) , base for an entity relationship modeler, or even create database application source code based on user specifications in a future.

Deliverables

The project deliverables of the project can be divided in several parts that are going to grow in time, because an iterative & incremental methodology will be used:

• Deliverable 1: Base Improvement source code for pgAdmin.
  • Include all classes needed to store internally the database structure of tables (or study how to use the structure and source code of pgAdmin to do this task) that can be included in query (user have access right).
• Deliverable 2: Initial graphical workspace.
  • This will be the space where table components can be dragged.
  • Include the representation of Tables in this space with her columns.
• Deliverable 3: Internal classes for basic query statement representation created by user, Including:
  • Source code needed to represent in the application the query create by the user graphically in a very basic way that only allow to add a single table with columns selections and selection of columns on user event at workspace(mouse click by example).
  • Generation of basic SQL statements that the user introduces in workspace.
• Deliverable 4: Improvements of graphical workspace.
  • Allowing joining tables visually (Equality Joins).
  • Include improvements of internal classes used by query statement representation created by user.
  • Improvements of graphic workspace allowing Joins Representation.
• Deliverable 5: Improvements of classes to allow addition of single row functions to query columns and restrictions in where clause and allow other types of join queries like outer joins or non equality joins.
• Deliverable 6: Improvements of query statements allowing using order by clause.
• Deliverable 7: Improvements of query statements allowing using group by, having statements and multiple row functions.

Estimated timeline

• May 26 – 31 Jun 1 - 7 week 1,2 Deliverable 1
• Jun 8 - 14,15 - 21 week 3,4 Deliverable 2
• Jun 22 - 28,Jun 29 - Jul 5 week 5,6 Deliverable 3, 4 (partially)
• Jul 6 - 12,Jul 13 - 19 week 7,8 Deliverable 4
• Jul 20 - 26 week 9 Deliverable 5
• Jul 27 - Aug 2 week 10 Deliverable 6, 7 (partially)
• Aug 3 - 9 week 11 Deliverable 7
• Aug 10 – 17 week 12 Test and hunt down bugs

Additional resources

• You can also look at the original proposal submitted to Google at http://code.google.com/soc/2008/postgres/appinfo.html?csaid=8BB11B408209C799
• pgAdmin Internals
• Luis' GSoC Blog
• Gsoc08-querybuilder-Internals

References

[1] Mitrovic, A: "Learning SQL with a computerized tutor", Proceedings of the twenty-ninth SIGCSE technical symposium on Computer science education, p. 307