pgdbvu

What is pgdbvu?

pgdbvu is a web application which enables you to view any JDBC compliant databases structure, browse the database in tables and issue SQL commands in an internet web browser. pgdbvu uses Struts a Jakarta Project to provide a framework for building web applications. All installation is performed on the server machine, the client machine only needs a browser which meets the necessary stipulated requirements. Full pgdbvu installation instructions and requirements are detailed in the Installation Instructions.

It is free software, and you are welcome to redistribute it under the terms of the BSD License, which is approved by the Open Source Initiative (OSI). The objective in using this license is to promote the use of this software with as few restrictions as the author deems possible.

How it came to be written (waffle)

I was writing a general Java database viewer application which had, what I thought, were some fairly useful features. The objective of this was ease of use, it had become a bit of a monster, since invariably by trying to make it easy to use it had become some what restrictive. I noted that there were a number of these packages about and that they took a more flexible route by letting the user write their own SQL. The application I had been writing attempted to create the SQL without the user being aware of it.

Whilst I was out talking to a friend about software I made the possibly, probably, stupid statement that I thought every piece of software should work on any platform and anywhere and should be done through the use of internet technology (i.e. the client machine should only run a browser and require no other piece of software). The no other special software was critical since he was having difficulty distributing software to client machines with all the headaches this involves when new versions etc. become available. Taking this onboard it was obvious that what I had been preaching, as is so often the case, was not what I had been practicing. Okay the software could run on nearly any platform through the use of Java but it was not an internet application. I decided to halt work on the aforementioned application and try to meet the requirements I had decided all applications should meet. (Note when I say all applications this is obviously not the case, I actually meant all applications that fall into a particular category, which I decided my application was one). I took some of the features that I thought were good from the first database and ditched the ones that were restrictive and more trouble than they were worth. These were replaced with the "make up for any short falls" SQL execution option.

I decided to use Struts from Jakarta, rightly or wrongly, because it seemed to be the standard frame work for producing web applications, this view being enhanced by the fact that the web application tutorials of the time on Sun's Java website ended with the use of Struts.

One of the features that I thought important to the application was the use of a tree to display the database structure. I believe this paradigm has become widespread since people are able to cope well with a tree structure to represent quite complicated hierarchical relationships (hence object oriented programming and numerous other examples). My first attempt at this was written using Struts's nested tags as outlined in Tree Structures by Arron Bates referenced on the Struts resources/tutorials.html page. This method of constructing the tree had some draw backs. I found that it only worked with certain version 4's of Jakarta Tomcat. The tree Action had to be in session scope which placed load on the server which I didn't really want to do. When expanding the tree the server was called to update it which incurred a significant delay. If the tree was large enough to require scrollbars a call to the server refreshes the page. The scrollbars are repositioned at the top of the page and the part of the tree you were viewing can no longer be seen until you have scrolled down to it. This became frightfully tedious and I could see that an end user would become very frustrated. The decision was therefore made to take the javascript route, which I had loathed to do as it restricted which browsers could be used to view the application. This I decided was not such a great sacrifice as browsers which have the capabilities to run the javascript version used are common and easy to acquire for any platform. The construction of the tree was performed with a request scope action and all subsequent tree view actions are handled by the client machines browser thus freeing up the server.

The tree not only shows the structure of the database but also enables the construction of simple select queries without the user being aware of this. The "TABLE" branch in the tree holds the user defined tables that exist in the database. Expansion of this branch shows the tables and expansion of a tables branch shows its fields. These fields can be "ticked" or "crossed" thus selecting and removing them respectively from the subsequent select query which is issued to the database when viewing the table. It is also possible to move the fields up or down which will affect the order in which they appear when viewed.

This small feature is what I believe makes pgdbvu easy to use as a database viewer, whether it sets it apart is another thing altogether.

pgdbvu is a small simple application missing a number of features which are necessary for a real world large scale database management client. It makes no attempt to compete in this arena but is as stated a simple web based database viewer. It uses certain features provided by a browser to augment its lack of features. An example of this is where printouts are concerned, normally called reports in database management systems (DBMS's). The browser may be used to enlarge or reduce the size of text and the printout will have the colours in the original page removed, dependant on the client machine configuration.

What's Next (more waffle)

I have enjoyed writing this and hope that someone out there will use it hence the reason for making it available. The license selected as previously stated is very liberal this will hopefully make it available to as wide an audience as possible. It is a small simple application with an objective to make database browsing easy. I think it's most important feature is the database tree which provides a visual representation of the database structure and helps in the creation of simple SQL queries.

I have written other database frontends with a view to building in ease of use which as they have evolved have become more and more specific which obviously is not the objective of a generic application. As pgdbvu stands it is reasonably flexible and parts may be taken and incorporated into other larger applications. In many ways it could be looked upon as a starting point with some hopefully useful ideas. Feel free to take the source and butcher it to meet your own needs.

Continuing with the theme of simplicity there is only one download WAR file. This includes the source and the compiled code. This also reduces the amount of duplication on this website.

There are many improvements that could be made to this application and I am sure that after some people have looked at the source may laugh (hopefully with a smile on their face and not a sneer). It is there to be used and hopefully improved, if it teaches someone anything even better. If you have made any modifications that you think may be worth putting into pgdbvu feel free to drop me a line with the suggested changes.

If enough interest is shown and people are experiencing problems with the structure of pgdbvu when trying to make changes to the source I may write development notes.

I appologise for the name but after a web search, as would be expected, my choice for a meaningful unique name became restricted and not having much of an imagination ended up with "pgdbvu".

Acknowledgements

This is a general acknowledgement to everyone who has written any software and made it available for general use (whatever form that takes). I have been using "free" software for many years and appreciate what these people have done. This application has been made possible by their efforts through both the use of and learning ("borrowing") from their software and ideas. One reason for making this available was a vain attempt to put something back.