Many years ago we defined a pair of tables in the database as
follows:

/*
 * Valid categories for control values.
 *
 * NOTE: Not currently used.
 *
 *       id  primary key for the control group table
 *     name  label used to identify all control values in a given group;
 *           e.g., CNTRY
 *  comment  optional text description of the purpose of the group; e.g.
 *           ISO country code values
 */
CREATE TABLE ctl_grp
         (id INTEGER IDENTITY PRIMARY KEY,
        name VARCHAR(32) NOT NULL UNIQUE,
     comment VARCHAR(255)    NULL)
GO

/*
 * Named and typed values used to control various run-time settings.
 *
 * NOTE: Not currently used.
 *
 *      grp  identifies which control group this value belongs to
 *     name  mnemonic label for value; e.g., US
 *      val  value associated with this name; e.g., United States
 *  comment  optional text description of the use of this value
 */
CREATE TABLE ctl
        (grp INTEGER     NOT NULL REFERENCES ctl_grp,
        name VARCHAR(32) NOT NULL,
         val NVARCHAR(255)   NULL,
     comment VARCHAR(255)    NULL,
 PRIMARY KEY (grp, name))
GO



We've never used these, but they're looking more and more handy
because they could enable us to control some activities without
recompiling the CdrServer or re-installing it or any Python
scripts.

I'm thinking of these right now because I want to be able to turn
connection logging on or off, and control the level, without
having to compile a new CdrServer each time and get CBIIT to
install it for us and restart everything.

Here's what I have in mind:


                      Changes to the tables
                      ---------------------

I no longer see any need at all for the ctl_grp table.  I'd be
inclined to get rid of it and modify the definition of ctl.grp by
replacing:

  grp INTEGER     NOT NULL REFERENCES ctl_grp,

with:

  grp VARCHAR(32) NOT NULL,

I'd also like to add the following columns

  created       DATETIME NOT NULL,
  inactivated   DATETIME NULL

and a view:

  CREATE VIEW active_ctl AS
     SELECT * FROM ctl WHERE inactivated IS NULL

We will also have to eliminate the definition:

  PRIMARY KEY (grp, name)

We can replace it with an integer identity if desired.


                       New CdrServer code
                       ------------------

To go along with the ctl table I'd propose the following new
CdrServer functions:

  setCtlValue()

    Purpose:
      Insert a row in the ctl table.  If another row already
      exists with the same grp + key, that row is inactivated.
      After inserting and/or inactivating a row, it calls
      buildCtlMap() - see below.

    Pass:
      Group name
      Key name
      Value
        If no value, the row is inactivated.
      Optional comment

    Return:
      Success or failure (with message).

  buildCtlMap()

    Purpose:
      Load all active ctl rows into a global in-memory map.

           Key = grp + "/" + key.
           Value = value.

      Just loads everything.  No need for optimization
      here, we're probably talking about tens of active rows, not
      thousands.

    Pass:
      Void.

  getCtlValue()

    Purpose:
      Get an active value from the map.

    Pass:
      Group name
      Key name

    Return:
      Value, or NULL if not found in active rows.

The idea here is that we don't have to constantly check the
database to see whether values have changed.  If they have, the
change is automatically propagated to the CdrServer.  We might
even be able to do it without worrying about a mutex - maybe.

We'd also need a new CDR transaction:

     CdrSetCtlValue

          It would invoke setCtlValue().


                       New Python scripts
                       ------------------

We'd need a new Python CGI script interface to the setCtlValue()
function.  It would have to do the following:

     Display existing values, sorted by group and key.

     If we have hundreds of these, we could have a search
     feature, but we may not need that.  It's icing on the cake.

     We may or may not want to allow inactive rows to be
     displayed.  More icing on the cake.

     Allow the user to send in a Cdr transaction that invokes
     setCtlValue().

The script would need to check security.  We might want a new
developers group that is more narrowly defined than systems
administrators, to just include programmers.

We probably also want a new function in cdr.py to get a control
table value.  It could access the database directly since it will
be read-only.

Estimated level of effort:

     Database changes: 1/2 day.

     CdrServer development: 3 days.

     Python script development: 3 days.