Graphical Query Builder for pgAdmin III

Student Name: Christian J. Wong Cruz

Current Location: Arequipa, PERU
Location for Summer 2008: Arequipa, PERU

.E-mail: cjd.wong <@t> gmail
.IM: cjdwong
.Skype: cjd.wong

Phone number: +51 54 959810307

Project Name: Graphical Query Builder for pgAdmin III
Project Summary: I will develop a Graphical Query Builder for pgAdmin in order to build complex SQL queries from the database structure.

Please explain in one paragraph what you expect to accomplish with this project:
PgAdmin is the most used interface to PostgreSQL database, and I expect to develop this Graphical Query Builder to bring pgAdmin with this new feature. I also expect to learn how the development of free-software occurs. And, with this project I could get started as a contributor for the PostgreSQL and pgAdmin projects.

Have you already done any work on this project?
Yes, in my course work I have developed, as part of a team, a CASE tool for designing database models complemented by two restrictions languages: (i) Transactional Language and, (ii) Restrictions Language. It was developed in Java.

CASE tool for designing database models complemented by two restrictions languages

Why do you feel that you are the correct person to work on this project?
As mentioned above, I have experience developing a CASE tool, so I’m familiar with the developing of this kind of applications. I am also familiar with some GUI libraries (like: gtk, QT, Swing, Java AWT, MS Visual Studio 6 and .NET); for this project currently I’m reviewing wxWidgets, and it doesn’t seems to be very different.

I am a Freebsd and MS Windows user, and as a developer in MS Windows Environment I prefer the use of use multiplatform tools (gcc, vim, etc.), libraries (opencv, opengl, etc.) and GUI libraries (gtk, QT, etc.), so I think I match with pgAdmin Multiplatform idea.

I was an ICPC-ACM contestant in the South American Region in the last two years. My preferred programming languages are C, C++ and Java.

Another reason I feel I’m the correct person to work on this project is that I will be a full time developer for the Summer of Code, because I’m a last year undergraduated student and my studies goes until end of April, so I can participate in this program; and I would like to continue contributing in the pgAdmin project after the end of the program.

Why would a PostgreSQL user, administrator or developer care about this project?
The Graphical Query Builder is an idea listed in the PostgreSQL ideas page, and I think this project will be interesting for the facility on building SQL queries by a visual interface. It will be useful inside pgAdmin because the user will be able to build complex SQL queries in a shorter time. PostgreSQL administrators will be able to retrieve different information faster each time it is needed and developers will be able to build queries taking care on the information wanted without syntax or naming errors.

Technical Specification:

It is required a Graphical Query Builder for pgAdmin III, so the specifications for my project are:

  • It can be integrated to the “pgAdmin III Query window”.
  • The tables and views could be dragged from the objects tree view (left side of the main window) to the “Drawing Area” in order to add them to the diagram, the Window also will have a button to add tables and views.
  • Some Query builders forces the user to redefine the relations between two objects; but this information is stored in the PostgreSQL Server and could be used in order to allow the user concentrate in the data to be retrieved.
  • Each table will be represented as a rectangle with its name at the top, and the attributes will be shown at the bottom of the rectangle.
  • The user will be able to select the attributes (or columns) to be retrieved, the alias to be used and if the attribute retrieved will be unique or not. The columns retrieved could also be aggregate functions about an attribute.
  • The user will be able to use subqueries. A subquery will be represented as a table with a diagram inside it and it will be edited in another pane.
  • When selecting restrictions (where clause), it will be possible to use nested restrictions and each restriction will be according to the attribute type. It will be possible with a tree organization of the restrictions, i.e.:
    . user_age > 18
    . OR
    . user_type = 2
    . user_type = 3
    . user_real_name is not null
  • The user will be able to group and sort the retrieved data.

In conclusion there will be a pane for the “Drawing Area” (with tables, views and subqueries), a dialog “Table Properties” (table properties, columns list, constraints list, grouping and sorting criteria) and a dialog “Column Properties” (column alias, unique or distinct, aggregate function)

Steps I will take to complete the project, milestones and approximate timeline.

The steps I will take to complete this project will be:

  1. Get familiar with the specific pgAdmin internals I require for my work, and the integration of the empty Drawing Area inside the “pgAdmin III Query window”. Milestone: I should present a report about pgAdmin internals and the first prototype before May 26 to my mentor.
  2. Complete the Drawing Area with Tables, Views and Relations. Milestone: The second prototype with the Drawing area complete until end of June.
  3. Complete the diagram with relationships. Milestone: The third prototype considering relationships until mid of July.
  4. The Table Properties dialog by end of July.
  5. The Column Properties dialog by mid of August.

An example of the Table Properties dialog:

Table Properties

I will try to implement things so that they can be used despite they are not completely finished, after the second prototype, the Query builder must be able to generate SQL Queries.

  • The second prototype will output queries like: “SELECT * FROM tableA, tableB”.
  • The third prototype will output queries like: “SELECT * FROM tableA INNER JOIN tableB ON tableA.attrA1 = tableB.attrB1”.
  • The fourth prototype will output queries like “SELECT a.attrA1, a.attr A2, b.attrB2 FROM tableA as a INNER JOIN tableB as b ON a.attrA1 = b.attrB1”.
  • The fifth prototype will output queries like: “SELECT a.attrA1 AS aa1, a.attr A2 AS aa2, b.attrB2 AS bb2 FROM tableA as a INNER JOIN tableB as b ON a.attrA1 = b.attrB1 GROUP BY aa2 ORDER BY aa2, aa1”.

An example of the Drawing Area here:

Drawing Area

What are the main risks which might prevent the completion of this project? If it does not get completed, will it be usable in an incomplete state?

  1. The low knowledge of the pgAdmin source code. There is a “Community Bonding Period” for getting involved in this topic, so I think this will be a hard-working period. In order to demonstrate the understanding of pgAdmin internals, I will add a Drawing pane in the pgAdmin III Query window.
  2. Relationships between tables I will need fully understanding of PostgreSQL tables and relationships internal management.
  3. The attribute’s restriction declaration because PostgreSQL allows several different types of objects it could be difficult to implement a fully compatible interface. If this problem is not solved or delimited, this declaration of restrictions could be done manually by the user.
  4. The drag and drop option for the tree objects list to the Drawing Area could not work properly. Another way for adding tables could be through a “Add Table” button. It would need another dialog which allows to select the table to be added. The same solution will apply to views.

Please list any references to your programming ability and the project’s feasibility, in the PostgreSQL Project, in other open source projects, or at school.
I’m a coder: My main programming languages are C, C++ and Java, but I also program in Perl, PHP, and other scripting languages. I have fully understanding my the three main languages. I was an ICPC-ACM contestant in the South American Region in the last two years. When I’m developing an application, I usually program in sessions of 6 hours.

I’m a developer: As was mentioned above I have developed a CASE tool, also several parsers (a HTML-LATEX parser, a Math expressions Latex analyzer, a SQL parser, a Fuzzy SQL parser and a Jay analyzer (Jay is a subset of the C programming language). As part of my course work I have developed a mini Database server for indexing basic data types, including data structures and a SQL parser. I’m quite familiar with the PostgreSQL because of the Data Structures Course where I’ve added another data structure for indexing data.

I’m a researcher: I’m a beginner researcher, I’ve published three papers:
. “Fuzzy Queries, A framework for parsing fuzzy queries for PostgreSQL” in the Peruvian Computer Week at Trujillo, PERU (November 2007).
. “Hybrid model for the control of medicines for Parkinson patients” in the Computer Science Week at Trujillo, PERU (September 2007), and
. “Texture-based faces codification” in the Computer Science Week at Trujillo, PERU (September, 2007).

I’ve also participated on a Project Contest at El Alto, BOLIVIA (November 2007) obtaining the second place my project was “Integrating MYPES to export by the Web”.

I’m a student: In my country the program is System Engineering but is oriented to Computer Science, I’m a last year undergraduated student and I’m placed second in the global ranking of my school for the last four years.

This entry was published on March 30, 2008 at 9:25 pm. It’s filed under Ciencias de la Computaci√≥n, Google Summer of Code, GQB for pgAdmin III and tagged , , , , , . Bookmark the permalink. Follow any comments here with the RSS feed for this post.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: