Wednesday, June 27, 2007

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.

No comments: