Forum OpenACS Development: Response to A query writing tool.

Collapse
Posted by Tom Jackson on

I tried to checkout what tables might have metadata suitable for building the function definitions (in acs-subsite). I found a little pgplsql for getting data out of acs_function_args. But I didn't find a table by that name.

To build up the function prototypes for oracle and pg, I created two new packages for the package builder. One is pb_functions, and the other is pb_fn_attrs. Here is the datamodel for the packages:

create table pb_functions (
  function_id integer not null    
    constraint pf_function_id_fk references acs_objects(object_id)
    constraint pf_function_id_pk primary key,
  oracle_pkg_id integer 
    constraint pf_oracle_pkg_id_fk references pb_oracle_pkg,
  function_name varchar(30) not null,
  owner_id  integer 
    constraint pf_owner_id_fk references users(user_id),
  return_type varchar(100) default 'integer' not null,
  return_column integer 
    constraint pf_return_column_fk references pb_columns(column_id)
);
create table pb_fn_attrs (
  fn_attr_id integer not null
   constraint pfa_fn_attr_id_fk references acs_objects(object_id)
   constraint pfa_fn_attr_id_pk primary key,
  function_id integer not null
   constraint pfa_function_id_fk references pb_functions,
  column_id integer 
   constraint pfa_column_id_fk references pb_columns,
  -- Attribute Default Value if null, just enter null, 
  -- otherwise quote chars, but not functions like now()
  default_value varchar(4000),
  -- Attribute type if not from table column
  attr_type varchar(100), 
  -- Order of attribute in input list 
  attr_order integer not null,
  in_out varchar(10) default 'in' not null
);

The immediate use could be in providing a place for function metadata. Second, these packages can be used to group columns from more than one table togeather. To actually write the function, I am proposing that the developer create a tcl/adp page that would take as input the function_id and produce the full package text.

Also, for current function metadata, I wonder if it might be possible to get the data out of Oracle and get it into pg. It couldn't work in all cases, but it might in most.