New Ideas

I've been thinking about synpl and applications of a SQL and PL/SQL source code analyzer for some time now.

Here's some of the ideas that I had (some of them don't require a parser):

• build a visual representation of the database; have the triggers/constraints listed next to each table - I understand Visual Studio offers something like this, even for Oracle, I need to look into this;
• look into the code and find out what indexes should exist (analyze WHERE clauses and INSERTs, try to find a balance between them)
• analyze where clauses and discover undeclared foreign keys
• build a measure of the connection between tables (strongly connected vs. weakly connected) and then build sets of closely related tables - could help people trying to understand vast databases with lots of tables by pointing out which tables are most closely related; trying to understand a group of 10 tables that are mostly connected to one another and have a few connections to other tables is much easier than trying to understand a group of 50 tables;
  
• build a Django admin-like app with Java hooks (load classes for validation, preprocessing, postprocessing, custom actions, reporting); Application Express does this already? need to look into it;
• analyze INSERTs and UPDATEs and try to infer if some fields only have a limited number of values (missing restrictions?)
  

Parsing the SQL queries to discover connections between tables should go beyond SQL queries in PL/SQL (i.e. use SQL queries in other languages) but the source code isn't always available. It's very rarely available, in fact.

New synpl demo

I've prepared a new demo of synpl.

It's probably broken in many places as I didn't get a chance to test it properly.

What's new:

• the ability to 'correct' slightly incorrect syntax (such as missing a ';' or an 'if' in 'end if')
• it can show the corrected version in a separate window; the corrections are highlighted
• it can load/save samples

Plans

I'm thinking of switching Synpl to Java, so that I can write a JDeveloper extension - and later a SqlDeveloper one. The problem is that the JDeveloper extension APIs aren't that well documented. There are even two competing APIs, one that is specific to Oracle and the other which is a JSR (198, to be more precise).

There are illuminating code samples for the Oracle API (which I hope can be used to develop an extension if combined with the API's JavaDoc). On the other hand, the JSR 198 seems more restrictive. One one forum discussion someone was complaining about the fact that it's impossible to write a code formatter with the JSR 198 API. I'm not sure that's true, but from what I've seen in the JavaDoc it's certainly a difficult task.

Hmmm. Back to the GIS application and paper ;)

Why?

What's synpl? A simple PL/SQL parser (for now) and analyzer (in the near future) written in an attempt to provide friendly compile/parse error messages and warnings about problems that the compiler is silent about.

Since I need to show synpl to people and email seems to be a tedious way to do it, I've started this blog to help me communicate with users willing to test synpl and provide feedback.

The first available demo is here. You need Windows and .NET framework 2 to run it (the GUI seems to be running fine under Mono 1.2.4 on Debian, but it may break in the future).