Page History

Overview

This page describes a general the variable table in the database.  The purpose of this table is to allow pieces of data to be stored without having to manipulate the database schema.  The need for such a table has grown over time as more and more features are added to VCL.  This table will become very useful as VCL becomes more and more modularized.  Any VCL component will be able to easily set and retrieve the data they require without having to add additional tables or columns to the database.to interact directly with the database.

Variable Names

A variable stored in this table can be thought just like any other programatic variable -- it has a unique name and value.  The name can be any string and is chosen by the developer utilizing the table.  Since the name must be unique and the variable table is shared by all facets of VCL (frontend, backend, modules), the developer should choose a name that isn't too general.  It is advisable to include something like the module name in the variable name in order to prevent other components from overwriting the variable.

Variable Values & Serialization

The variable value is completely flexible.  It can be a simple integer or a complex data structure.  This is accomplished by serializing the value before saving it to the database.  YAML will be used to serialize the data.  YAML is a human friendly data serialization standard for all programming languages (http://yaml.org/).&nbsp^{Image Added}; YAML modules are available for Perl, PHP, and many other languages.

Database Table Structure

The variable The table will have the following columns:

• id
  • variable.id contains a unique integer unique auto-incremented unsigned 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 identifying a variable to set or retrieve
• value
  • variable.value contains a string an encoded string which is a YAML serialized representation of the data
  • The string stored in variable.value is programatically serialized before it is stored , and programatically unserialized when it is retrieved
  • Various programming languages have the ability to transform data to/from YAML
  • Serialization allows multiple languages to share the and modify 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
  • variable.setby can be NULL but should be set whenever a row is altered
• timestamp
  • variable.timestamp contains the date and time when the variable was last set
  • variable.timestamp should always be updated timestamp is automatically updated whenever a variable row is inserted or altered

...

• • because the "ON UPDATE CURRENT_TIMESTAMP" column attribute is set

SQL table definition

...

Code Block

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` datetimetimestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,   PRIMARY KEY  (`id`),   UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Examples

Example 1: A hash is created in Perl containing 2 keys with scalar values:

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

This hash is serialized using the YAML module's Dump function, and then a row is saved in the variable table:

Example 2: A more elaborate data structure is created in Perl containing an array of hashes. One of the hash values (email) can be multivalued because the value is an anonymous array:

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
 }
);

YAML::Dump transforms this data structure into the value stored in the value column in the following row: