[Chasers-devel] Moving report CFG files to the DB

[Kenneth T. <ken...@gm...>]

I've been doing some work on making reports storable in the DB, rather 
than as separate CFG files.

I've currently got a version that will kind of run a report from a 
table, but doesn't handle "small" details like user-selected variables, 
multiple SQL statements, and so on.  I can do really hackish 
implementations of these things as text blobs in the report table, but I 
think it would really be better to have for example each variable live 
in a separate table, and have multiple variable-records linked to each 
report.  The same for SQL statements, although I'm not as sure given the 
complications discussed below.

To continue working on this part, it would be really helpful to get 
access to the child-child table stuff that has already been done at 
DESC.  Sarah or Nathan--if you could provide that stuff, it would be 
great, or if there's a problem or complication I'm not thinking of, 
let's touch base and figure out how to proceed.

Now for the complications.  The main one I see is that currently the CFG 
files have a defined order for both user-variables and for SQL 
statements, namely the order in which you include them in the config 
file.  If these moved to child tables, how would they be ordered?  
Although this may only be a cosmetic issue for user-variables, it's 
rather critical for SQL statements.  They could be ordered by their ID 
#s, but that would be ugly and extremely limiting.  There could be some 
kind of ordering field in the SQL table, either to stand alone, or to 
override order-by-id, but that seems pretty clunky too.  I suppose there 
could be a list of SQL IDs as a field in the report table.  This could 
be an array field, but I don't remember off hand if array fields keep 
their order or not, and I think you also lose enforcement of foreign keys.

In short, I'm not really happy with any of those options, and am hoping 
someone else has some great ideas, or at least some thoughts about 
this.  Backtracking a bit, although it seems cleaner, I don't suppose 
either of these things _have_ to move to child tables.  A couple of 
advantages, though, might be:

*  For SQL queries, you could add a nice clean boolean field to 
"suppress output", if you were merely creating a temp table.  OTOH, you 
could have a simple hack like having your SQL statements start with a * 
(or something) if you wanted to supress output.

* Having them in a table might facilitate the sharing of variables and 
SQL statements.  Currently we can use the "Include <file>" option, but 
without files, that option seems to go away.

* User variables in a table would allow creation of lookup tables for 
variable types, and make it easier to create a report through the GUI.

That's all I got for now.  Any and all thoughts and ideas appreciated!

Ken