Index: openacs-4/packages/accounts-finance/sql/postgresql/accounts-finance-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-finance/sql/postgresql/accounts-finance-create.sql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/accounts-finance/sql/postgresql/accounts-finance-create.sql	14 Aug 2010 01:30:24 -0000	1.2
+++ openacs-4/packages/accounts-finance/sql/postgresql/accounts-finance-create.sql	23 Aug 2010 22:00:49 -0000	1.3
@@ -13,99 +13,136 @@
 -- other case inputs etc etc.
 -- think calculator wiki with revisions
 
-CREATE TABLE qaf_case (
-    id int DEFAULT nextval ( 'qaf_id' ),  
-    code varchar(30),
-    title varchar(30),
-    description text,
-    init_condition_id int,
-    model_id int,
-    log_points_id int,
-    postcompute_process_id int,
-    model_output_id int,
-    iterations_requested int,
-    iterations_completed int,
-    instance_id integer,
-        -- object_id of mounted instance (context_id)
-    user_id integer,
-    time_created timestamptz,
-    last_modified timestamptz,
-    trashed_p boolean default 'f',
-    time_closed timestamptz
+CREATE TABLE qaf_file (
+    id integer primary key DEFAULT nextval ( 'qaf_id' ),  
+    title varchar(60)
 );
 
+
+
 -- this table associates old ids with cases
 -- multiple cases may be associated with various ids
 -- no type is set for old id, since this will likely be joined with
 -- another table
 CREATE TABLE qaf_case_log (
-    case_id int,
-    other_qaf_id int
+    case_id integer,
+    other_qaf_id integer
+ -- log ids, old case model init_condition log_points post_calcs ids
 );
 
 
 CREATE TABLE qaf_initial_conditions (
-    id int DEFAULT nextval ( 'qaf_id' ),  
+    id integer primary key DEFAULT nextval ( 'qaf_id' ),  
     code varchar(30),
     title varchar(30),
-    user_id integer,
-    time_created timestamptz,
+    user_id integer not null,
+    time_created timestamptz not null DEFAULT now(),
     trashed_p boolean default 'f',
     description text
 );
 
 CREATE TABLE qaf_model (
-    id int DEFAULT nextval ( 'qaf_id' ),  
+    id integer primary key DEFAULT nextval ( 'qaf_id' ),  
     code varchar(30),
     title varchar(30),
-    user_id integer,
-    time_created timestamptz,
+    user_id integer not null,
+    time_created timestamptz not null DEFAULT now(),
     trashed_p boolean default 'f',
     description text,
     program text
 );
 
 
 CREATE TABLE qaf_log_points (
-    id int DEFAULT nextval ( 'qaf_id' ),  
+    id integer primary key DEFAULT nextval ( 'qaf_id' ),  
     code varchar(30),
     title varchar(30),
-    user_id integer,
-    time_created timestamptz,
+    user_id integer not null,
+    time_created timestamptz not null DEFAULT now(),
     trashed_p boolean default 'f',
     description text
 );
 
-CREATE TABLE qaf_postcompute_process (
-    id int DEFAULT nextval ( 'qaf_id' ),  
+CREATE TABLE qaf_post_calcs (
+    id integer primary key DEFAULT nextval ( 'qaf_id' ),  
+    log_id integer,
+        -- id of qaf_log_point associated with process
     code varchar(30),
     title varchar(30),
-    user_id integer,
-    time_created timestamptz,
+    user_id integer not null,
+    time_created timestamptz not null DEFAULT now(),
     trashed_p boolean default 'f',
-    description text
+    calculations text
 );
 
--- qaf_model_output.compute_log will contain a tcl list of lists
+
+CREATE TABLE qaf_case (
+    id integer primary key DEFAULT nextval ( 'qaf_id' ),  
+    file_id integer,  
+    -- file_id does not change when case_id changes
+    code varchar(30),
+    title varchar(30),
+    description text,
+    -- create a new case when changing any of the following ids
+    init_condition_id integer not null,
+    model_id integer not null,
+    log_points_id integer not null,
+    post_calcs_id integer not null,
+    -- most recent results of calculations for this case at
+    log_id integer,
+    post_calc_log_id integer,
+     -- following are attributes for utility use
+    instance_id integer,
+        -- object_id of mounted instance (context_id)
+    user_id integer not null,
+    time_created timestamptz not null DEFAULT now(),
+    last_modified timestamptz not null DEFAULT now(),
+    trashed_p boolean default 'f',
+    time_closed timestamptz not null DEFAULT now()
+);
+
+
+-- qaf_log.compute_log will contain a tcl list of lists
 -- until we can reference a spreadsheet table, and
 -- insert there.
-CREATE TABLE qaf_model_output (
-    id int DEFAULT nextval ( 'qaf_id' ),
+CREATE TABLE qaf_log (
+    id integer primary key DEFAULT nextval ( 'qaf_id' ),
     code varchar(30),
     title varchar(30),
-    user_id integer,
-    time_created timestamptz,
+    user_id integer not null,
+    time_created timestamptz not null DEFAULT now(),
+    iterations_requested integer,
+    iterations_completed integer,
     trashed_p boolean default 'f',
     description text,
     compute_log text,
     notes text
 );
 
+-- whereas log_points tracks model variables
+-- post_calc_log automatically tracks all post_calc_variables
+-- post_calc_variables can be filtered when aggregated into
+-- another case using log_points
+CREATE TABLE qaf_post_calc_log (
+    id integer primary key DEFAULT nextval ( 'qaf_id' ),
+    code varchar(30),
+    title varchar(30),
+    user_id integer not null,
+    time_created timestamptz not null DEFAULT now(),
+    trashed_p boolean default 'f',
+    description text,
+    compute_log text,
+    notes text
+);
+
+CREATE index qaf_file_id_key on qaf_file(id);
 CREATE index qaf_case_id_key on qaf_case(id);
 CREATE index qaf_case_log_case_id_key on qaf_case_log(case_id);
 CREATE index qaf_case_log_other_qaf_id_key on qaf_case_log(other_qaf_id);
 CREATE index qaf_initial_conditions_id_key on qaf_initial_conditions(id);
 CREATE index qaf_model_id_key on qaf_model(id);
 CREATE index qaf_log_points_id_key on qaf_log_points(id);
-CREATE index qaf_postcompute_process_id_key on qaf_postcompute_process(id);
-CREATE index qaf_model_output_id_key on qaf_model_output(id);
+CREATE index qaf_post_calcs_id_key on qaf_post_calcs(id);
+CREATE index qaf_log_id_key on qaf_log(id);
+CREATE index qaf_post_calc_log_id_key on qaf_post_calc_log(id);
+