DB Adapter

The purpose of DB adapter is to act as an interface to a (MySQL) database storing parameters and variables for the Home Automation project. In an early stage is was expanded to also include a log.

Skärmavbild 2013-12-28 kl. 23.10.17

The database as such is created with the following script

    1: CREATE DATABASE  IF NOT EXISTS `ha` /*!40100 DEFAULT CHARACTER SET latin1 */;
    2: USE `ha`;



Attribute table is used to store parameter values for the system and also to act as an integration point to other sub systems

    1: DROP TABLE IF EXISTS `attribute`;
    2: /*!40101 SET @saved_cs_client     = @@character_set_client */;
    3: /*!40101 SET character_set_client = utf8 */;
    4: CREATE TABLE `attribute` (
    5:   `attributeid` int(11) NOT NULL AUTO_INCREMENT,
    6:   `attribute_name` varchar(255) NOT NULL,
    7:   `attribute_desc` varchar(255) NOT NULL,
    8:   `attribute_unit` varchar(255) NOT NULL,
    9:   `attribute_type` varchar(255) NOT NULL,
   10:   `attribute_value` varchar(255) NOT NULL,
   11:   `attribute_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   12:   `attribute_origin` varchar(255) NOT NULL,
   13:   `attribute_seqno` varchar(255) NOT NULL,
   14:   PRIMARY KEY (`attributeid`)
   15: ) ENGINE=MyISAM AUTO_INCREMENT=4696 DEFAULT CHARSET=latin1;



Log table is used to store momentary values at certain intervals.

    1: DROP TABLE IF EXISTS `log`;
    2: /*!40101 SET @saved_cs_client     = @@character_set_client */;
    3: /*!40101 SET character_set_client = utf8 */;
    4: CREATE TABLE `log` (
    5:   `logid` int(11) NOT NULL AUTO_INCREMENT,
    6:   `log_name` varchar(255) NOT NULL,
    7:   `log_value` varchar(255) NOT NULL,
    8:   `log_date` varchar(255) NOT NULL,
    9:   `log_type` varchar(255) NOT NULL,
   10:   PRIMARY KEY (`logid`)
   11: ) ENGINE=MyISAM AUTO_INCREMENT=215868 DEFAULT CHARSET=latin1;
   12:

Perl interface for the attribute table

    1:
    2:
    3: sub setvalue
    4: {
    5:         # Get input parameters and parse URL and retrieve
    6:         # Param 1 - Attribute Name
    7:         # Param 2 - Attributy Value
    8:
    9:         my $url;
   10:         $url =  $ha_url_setvalue;
   11:         $url =~ s/_name_/$_[0]/;
   12:         $url =~ s/_value_/$_[1]/;
   13:         get $url;
   14: }
   15:
   16: sub getvalue
   17: {
   18:         # Get input parameters and parse URL and retrieve and return value
   19:         # Param 1 - Attribute Name
   20:         # Return - Attribute Value
   21:
   22:         my $url;
   23:         my $content;
   24:         my $value;
   25:         my $start;
   26:         my $length;
   27:
   28:         $url =  $ha_url_getvalue;
   29:         $url =~ s/_name_/$_[0]/;
   30:         $content = get $url;
   31:         $start = index($content, '<A_V>') + 5;
   32:         $length = index($content, '</A_V>') - $start;
   33:         $value = substr($content, $start, $length);
   34:         $value;
   35: }
   36:

Perl interface for the log table

    1:
    2:
    3: sub writelog
    4: {
    5:         # Get input parameters and parse URL and retrieve
    6:         # Param 1 - Log Name
    7:         # Param 2 - Log Type
    8:         # Param 3 - Log Value
    9:
   10:         my $url;
   11:         $url =  $ha_url_log;
   12:         $url =~ s/_name_/$_[0]/;
   13:         $url =~ s/_type_/$_[1]/;
   14:                 ##      #If double replace . with ,
   15:                 ##      if ($_[1] eq "double"){
   16:                 ##              $_[2] =~ tr/./,/;
   17:                 ##      }
   18:         $url =~ s/_value_/$_[2]/;
   19:         get $url;
   20: }