---

title: Variable Table

Overview

This page describes the variable table in the database.  The purpose of this table is to allow pieces of data to be easily stored in the database without having to manipulate the 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 increasingly modularized.  This table will allow any VCL component to access the variable data without having 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|http://en.wikipedia.org/wiki/Yaml] is used to serialize the data.  YAML is a human friendly data serialization standard for all programming languages ([http://yaml.org/]).  YAML modules are available for Perl, PHP, and many other languages:

• Perl: http://search.cpan.org/~ingy/YAML-0.68/lib/YAML.pm
• PHP: http://code.google.com/p/spyc

Because the data is serialized into a highly compatible format, the backend and frontend can utilize common variables.  It will be easy for the frontend to provide a means to configure variables by capturing the data entered into a web page, constructing a data structure, serializing it via a PHP YAML module, and then saving the data structure in the variable table.  The backend can then access the identical data structure using a Perl YAML module to deserialize the data.

Backend Interface

The variable table is utilized by backend code via functions provided by the DataStructure.pm module:

• get_variable($name)
• set_variable($name, $value)

Database Table Structure

The variable has the following columns:

unique | longtext | varchar(40) | timestamp |

• id ** variable.id contains a 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 ** variable.name values must be unique ** variable.name provides a human-friendly means of identifying a variable
• value ** variable.value contains 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 deserialized when it is retrieved
• setby ** variable.setby contains a string which indicates who/where 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 is automatically updated whenever a row is inserted or altered because the “ON UPDATE CURRENT_TIMESTAMP” column attribute is set

SQL table definition

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

Examples

Example 1:

The following hash is created in Perl:

my %kms_configuration = (
�"ECU" => "192.168.22.33:1688",
�"NCSU" => "kms-server.ncsu.edu",
);
$self->data->set_variable("kms-configuration", \%kms_configuration);

The keys correspond to the affiliation.name column in the database.  The values represent the addresses (phony) of Windows Vista/2008 KMS activation servers.

This hash is serialized by DataStructure.pm::set_variable() using the YAML module's Dump function which transforms the hash into:

---
ECU: 192.168.22.33:1688
NCSU: kms-server.ncsu.edu

The following row is saved in the variable table by the DataStructure.pm::set_variable() subroutine:

NCSU: kms-server.ncsu.edu | new.pm:139 | 2009-05-26 11:35:36 | To retrieve the data:

my $kms_configuration = $self->data->get_variable("kms-configuration");
my $kms_address = $kms_configuration->{$affiliation_id};

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 } );�� $self->data->set_variable(“contacts”, @contacts);

DataStructure.pm::set_variable() uses YAML::Dump to transform this data structure into:

---
- email:
��� - joe@somewhere.org
��� - jdoe22@unity.ncsu.edu
� employee_id: 3342
� firstname: Joe
� lastname: Doe
- email:
��� - jane@somewhere.org
� employee_id: 7865
� firstname: Jane
� lastname: Doe

The following row is then added to the variable table:

  lastname: Doe | DataStructure.pm:554 | 2009-05-26 12:35:36 | To retrieve the data:

my @returned_contacts = @{$self->data->get_variable('contacts')};
for my $contact (@returned_contacts) {
   print "Name: $contact->{firstname} $contact->{lastname}\n";
   for my $email_address (@{$contact->{email}}) {
      print "Email: $email_address\n";
   }
   print "---\n";
}