Friday, September 25, 2009

A Search Engine for the HA System

As I've mentioned many times before, all system status (server status, starCOMUltra variables, TimeCommander+ devices, etc) is mirrored in a MySQL database. Many times when I'm debugging stuff, I sift through a logfile to find values or open up an ASP page containing flags, variables, etc. As a twist on this, I decided to make a search engine of sorts, where I could specify a portion of a name in the database and have all matches and their values returned. My initial try focused on writing some JScript to work with our IM interface and that's what I'm going to write about.

Each table in the Autohouse database is a category, like flags and variables.

mysql> show tables;
| Tables_in_autohouse |
| ai |
| calendar |
| debug |
| di |
| flag |

Typically, each table consists of a device and its corresponding value:

mysql> select * from locals;
| device | value |
| AirQualityO | 27 |
| AirQualityP | 46 |
| BWDownDaily | 0.246 |
| BWDownMonthly | 46.297 |

Not all the column names are the same, so we have to parse them out. For example, the database records the past 10 temperature readings for all temp sensors:

mysql> select * from lrtem
| id | value |
| 10 | 73.625 |
| 9 | 73.5125 |
| 8 | 73.5125 |
| 7 | 73.625 |

In scripting this up, we'll need the MySQL commands to get the data we want. First off, show tables gives the table names. Next, we'll iterate through each of the tables and grab their column names with show columns from <table name>. Finally, we'll do a case insensitive search in that table for the matching device: select * from <table name> where device like '%<name to match>%'.

Here's the code snippet I've implemented in my xPL scripting engine to handle queries via the IM interface. This sets up the MySQL access from JScript:

mysql=new ActiveXObject("ADODB.Connection");
strConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" + "SERVER=localhost; \
DATABASE=autohouse; UID=user;PWD=password; OPTION=3";

And this is the actual code to handle everything:
} else if (Mycmd[0] == "query") {
// Mycmd[0] is the command, Mycmd[1] is what we're looking for
txtBody = "\n";
var fieldArray=new Array();
// get all table names
query="show tables";
while (!rs.EOF) {
// iterate through each table finding their column names
query2="show columns from "+rs('Tables_in_autohouse');
var i=0;
while (!rs2.EOF) {
fieldArray[i] = ""+rs2('Field');
// now search the table for a matching name in the first column
query3="select * from "+rs('Tables_in_autohouse')+" \
where "+fieldArray[0]+" like '%"+Mycmd[1]+"%'";
while (!rs3.EOF) {
// build the output text
txtBody = txtBody + " " + rs('Tables_in_autohouse') +
" - " + rs3(fieldArray[0]) + "=" +


The text gets passed back to the IM interface and the result is something like this:

me: query ring
di - Ring=0
flag - RingFlag=0
flag - RingFlag2=1
timer - ring_timer=0
var - ring_cnt=226

The resulting search returned the names and values of a digital input, two flags, a timer and a variable. This can be easily extended to a webpage so you can build your own Google for your home automation system.

No comments:

Post a Comment