Variable Table

This page describes a general variable table in the database.  The purpose of this table is to allow pieces of data to be stored without having to add additional tables or columns to the database.

The table will have the following columns:

• id
  • variable.id contains a unique integer value
  • The id column is consistent with most other columns in the VCL database
• name
  • variable.name contains a string representing the variable name
  • variable.name values must be unique
  • variable.name provides a human-friendly means of specifying a variable to set or retrieve
• value
  • variable.value contains a string which is a YAML serialized representation of the data
  • The string stored in variable.value is programatically serialized before it is stored, and unserialized when it is retrieved
  • Various programming languages have the ability to transform data to/from YAML
  • Serialization allows multiple languages to share the same data structures
• setby
  • variable.setby contains a string which indicates where and who last set the data
  • variable.setby is mainly used for debugging purposes
• timestamp
  • variable.timestamp contains the date and time when the variable was last set
  • variable.timestamp should always be updated whenever a variable row is altered

Here is the table definition:

CREATE TABLE IF NOT EXISTS `variable` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `value` longtext NOT NULL,
  `setby` varchar(40) default NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

my %kms_configuration = (
 'ECU' => '192.168.22.33:1688',
 'NCSU' => 'kms-server.ncsu.edu',
);

my @contacts = (
 {
  'firstname' => 'Joe',
  'lastname' => 'Doe',
  'email' => ['joe@somewhere.org', 'jdoe22@unity.ncsu.edu'],
  'employee_id' => 3342
 },
 {
  'firstname' => 'Jane',
  'lastname' => 'Doe',
  'email' => ['jane@somewhere.org'],
  'employee_id' => 7865
 }
);