DBmacs: editor for SQL dummies

Li Yan 0150254
CSE 544


Motivation:

Database is an excellent tool in organizing and query large
volumes of data. SQL is the stardard query language for
database, but it is not necessarily the favorite of every
user. The motivation of this project is to provide a more
accessible, by accessible I mean easy to use, easy to learn
and have about the same power as SQL would do for those SQL
nerds. 

Platform: PostgresQL and JDBC is chosen primarily because
both are readily available at cubist server, so there is no
need to set up and configure a DBMS. Additional merits for
PostgresQL are its compatibility with Linux programming
environment, and its availability to the public for
free. JDBC is the choice of database connection because the
intimate interface with Java programming language, and well
written documentation. JDBC 2.0 has a lot more functionality
compared with 1.0 version, which greatly relieved the burden
of application, e.g. the ResultSet interface now supports
both scroll and update, which is very convenient for
building a database editor.

The application:

As an overview, this application's primary job is to parse
user command and then construct a SQL command out of it. An
internal table is maintained to store temporary changes made
by user, and these changes are committed upon next ListTable
command. The changes are visible to user by returning
internal table content if there is a match. Currently
supported operations are 
	  browsing commands: Begin/End, Prev/Next, Goto
	  data maniputation commands: Insert/Delete/Update and Copy/Paste

The main method acts as the driver for this program, accepts user
command and dispatch to the appropriate handler. Each
command is handled by a public method, a few help functions
such as matchPrimaryKey, printRecord are supplied. Three
lists were maintained with the inserted/deleted/updated records.

Experience and Lessons:

Because the bidirectional traversal support from JDBC 2.0,
the original browsing part is easy, with ResultSet as a
cursor moving around, although there is a potentially
performance issue if we issue a command that is going to
fetch the whole table. But given the fact that JDBC is doing
something smart by returning only around 30-40 tuples as a
cached result, and will do another fetch only if cache
exhausts. Things started to get complicated when
insertion/deletion is added. My current approach is simply
add all inserted records to the end of current record list,
and skip the deleted records during record access. Since
there is an internal deleted record table, I was able to
identify a record is marked deleted by comparing the primary
key of current record with those in the deleted list. But
due to some off-by-one errors, I haven't get the traversal
implemented correctly. The update functionality is easier,
by maintaining an internal list of updated records, we can
just read the updated version of a given record by comparing
primary keys.

The changes were committed upon next call to ListTable
command, although a special Commit command would be more
appropriate.

As an editor, the above functionality should be a good
minimal set. It would be nice to display the records around
current record, to go to the first record that satisfies a
predicate, to support highlight record/field and do editing
in place, maintain our own buffer if performance is a great
concern, it is also worthwhile to port to other DBMS.
We could also do the following:
	Browse/Edit mode switch, thus gives the semantics of
	read-only and edit
	GUI interface, it will be much more pleasant for
	user with buttons, menus and toolbars available and
	they can do mouse click
	Import/Export table, so user can set up a table from
	another application, say, a spread sheet
	application, with little effort and start working on
	it. They might also want to export their temporary
	records into a text file or somethingelse. 
This tool can be very helpful even to those who knows SQL,
because it is fast and handy.

An invaluable experience to pick up JDBC, since this is my
first experience programming a database application with
JDBC connection. The documentation is extremely well written
and it is surprisingly easy to get the right information
about a certain Class or method.

Like any application, the most messy part is to handle user
input, because the unpredictable nature of human. And it
adds a significant complexity by allowing user to input
string with space in between, handlding quotation mark, and
interestingly enough, at one time the application broke very
similar to the way SQL injection does to fool the online
catalog system.