Monday, November 27, 2006

Introducing PgWorksheet

PgWorksheet is a simple GUI frontend to PostgreSQL for executing SQL queries and psql commands without using the psql command line tool.

Development of this tool have started at the E-CML Computer Science school for SQL and PL/SQL courses when Oracle was replaced by PostgreSQL for this courses.
PostgreSQL was choosen in place of Oracle for several reasons :
  • Minimal server administration compared to the mammoth-style administration tasks requiered by Oracle
  • Minimal CPU & memory footprint : Oracle needed a single server only for the courses. With PostgreSQL, the database was installed on the school main NFS/Samba/DNS/DHCP/VPN/... server.
  • No need to install a mammoth-sized client to have a simple GUI client to execute simples SQL queries.
This last point is also an important problem with PostgreSQL at the time of the switch : the total lack of a simple, light, easy to install, open source, free GUI client.
While PostgreSQL offers some great administration tools (PgAdmin, phpPgAdmin) there are administration tools, not just SQL scratch pads.

Since the main GUI tool used during SQL courses with Oracle was SQLWorksheet, we started to explore the web to find an identical tool for PostgreSQL. We found only one application fullfiling our requirements, pgst. But pgst have an important problem : despite the fact that is was written in Python, pgst was not portable and needed some important modifications to run under Microsoft Windows.

PgWorksheet started as a re-implementation of some ideas of pgst, written from scratch, but with portability in mind since we have 3 different operating system used during the courses : FreeBSD, Linux Fedora and Microsoft Windows.

Now PgWorksheet version 1.8 offers several, but limited to provide easy of use, fonctionnalities :
  • portability (runs on *BSD, all Linux distribs and Microsoft Windows XP)
  • syntax coloring
  • load/save SQL queries
  • SQL queries history
  • unlimited undo/redo
  • display of SQL queries results separated (one tab for one query)
  • server connection history
The PgWorksheet project is hosted by PgFoundry and the PgWorksheet sources are hosted by SourceForge (you can browse source code)

2 comments:

Chris Jones said...

The free lightweight Oracle XE database and Oracle's free SQL
Developer tool are viable solutions. SQL Developer may not be quite
as thin as PgWorksheet but at least it is a one bundle download.

x-tech team said...

Right, but our switch from Oracle to PostgreSQL started before (mid-2003) the first public release of these tools.