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";
mysql.Open(strConnectString);

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";
rs=mysql.execute(query);
while (!rs.EOF) {
// iterate through each table finding their column names
query2="show columns from "+rs('Tables_in_autohouse');
rs2=mysql.execute(query2);
var i=0;
while (!rs2.EOF) {
fieldArray[i] = ""+rs2('Field');
rs2.MoveNext;
i++;
}
// 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]+"%'";
rs3=mysql.execute(query3);
while (!rs3.EOF) {
// build the output text
txtBody = txtBody + " " + rs('Tables_in_autohouse') +
" - " + rs3(fieldArray[0]) + "=" +
rs3(fieldArray[1])+"\n";
rs3.MoveNext;
}

rs.MoveNext;
}

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

me: query ring
autohouse:
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.

Wednesday, September 23, 2009

Excellent, Free MS SAPI Voice for XP

Check out this CT thread on how to get the Microsoft Anna voice running on XP. The tip about Universal Extractor is also a great idea.

And don't forget to get the latest Picasa, which has auto face tagging.

Saturday, September 19, 2009

A Little Lull

I haven't been working on too much the last couple weeks. I think I've run out of things I want to do. One thing I've contemplated is automating opening/closing the vertical blinds in our family room, but it's not used very much anyway. I also thought about adding a magnetic lock on a gate, but they are a little pricey and I haven't found any that would default to locked on a power outage (can they even do that without power?). In the meantime, I'm just kicking back doing a little reading and watching some football. I think I deserve a break ;)

Monday, September 14, 2009

First Rain Brings Death (Of UPS)

Last night, we had our first rain of the fall (well it's almost fall) and it was enough to cause brownouts. This proved fatal for the UPS in the wiring closet. Fortunately, the TimeCommander+, a couple routers & switches and the NSLU2, which were on the UPS, were spared. The NSLU2 doesn't power itself back on when power is restored so I just realized it's resting quietly in the wiring closet instead of serving up http://www.doghouselabs.com. That's now fixed and a temp UPS has been installed. I'm not having luck with power related gear lately.

Wednesday, September 9, 2009

Building the W800 Antenna

I decided it's time to get rid of the original whip antenna that came with my W800 and build this antenna. Acquiring the parts became a treasure hunt. I picked up the nuts, bolts, washers and crimp lugs at Home Depot then headed over to Radio Shack for the SO-239 parts. Turns out the F to SO-239 Adapter isn't a regularly stocked item around here. I could order it or go to a Shack 30 minutes away and get it. Instead, I left empty handed and headed over to Fry's. There I found the Chassis Mount UHF SO-239 Coax Connector for a mere $1.49 vs. $3.69 at the Shack, but they didn't have the F to SO-239 adapter. They did have a BNC Female to SO-239 adapter and I happened to have a spare BNC Male to F connector at home.



I had still one more stop as I couldn't find the 1/16" brass rod at Home Depot. I stopped by my local Orchard Supply Hardware and found 3 packs of 1/16" x 12" brass rods for $1.79. Good enough.



Finally, I was able to start the project. Instead of a hacksaw to cut the rods, I used diagonal pliers. I also crimped the lugs after I inserted the rods - then I soldered them. Other than that, I followed the directions in the write up. I've got it temporarily hanging in my office but plan to put it in the attic this weekend. The whip antenna is in the attic but it can't see a Hawkeye motion sensor I just put in the backyard. The new antenna, on the other side of the house, can pick it up sometimes, so it should be a lot better in the middle of the attic.

Update: I've hooked it up to the RFID reader via a coax splitter and I have not had any dropouts! The antenna is still sitting in my office.

Tuesday, September 8, 2009

CCTV Server Power Supply Croaked

My HA server's power supply died back in March and the CCTV server's power supply died yesterday. It wasn't even 2 years old (the supply started off in the kitchen PC which was sporadically used. It wasn't used 24x7 until the CCTV server was created less than a year ago. Very disappointing. I've installed my backup ATX power supply, which has an intake fan over the CPU. The old supply didn't and rarely turned on its fan. It was a very quiet server, with just a slow case fan running. You can see the CPU temp is dramatically lower with the backup supply. The D201GLY2 got pretty hot at times, but was within specs. Now I need to hunt for a new 80Plus power supply.



Sunday, September 6, 2009

A Quiet Long Weekend Without Automation Projects

I took off work Friday and got started on my latest project - mortaring down a flagstone walkway on the side of our house. In 3 days, I've finished about two-thirds of it, but I'm thinking of taking tomorrow off since my knees are killing me. Maybe it's time to go back to doing some automation projects ;) Here's a peek at it from the dog cam which is connected to the 9100a.



Thursday, September 3, 2009

AJAX Cross Site Scripting Problem...Solved

As I've mentioned, our HA GUI is built with AJAX, and I thought that I was able to easily integrate the Aviosys 9100A. Turns out some browsers and/or operating systems weren't so willing to allow access to the 9100A since it has a different IP address than the web server. It works great on Firefox on XP, but for some reason, won't on Vista. Opera on Wii wasn't too keen on it either.

Since our GUI runs on the Apache web server, I can take advantage of mod_rewrite, which rewrites the requested URL on the fly. Let's assume my server runs on 192.168.100.100. I've got this code in an html file:

<div class=camera style="z-index:5; position: absolute; left: 49; top: 355px; cursor: pointer;">
<img src=images/camera_left.png border=0 onmousedown="javascript:selectCam('SideCam');" id=SideCam>
</div>

The selectCam function:

function selectCam(camera) {
var newurl,xyz;
if (camera=="SideCam") {
// set channel on 9100A
newurl="192.168.100.200/SetChannel.cgi?Channel=0";
// AJAX call to load URL
xyz=loadXMLDoc(newurl);
}
}

What I need to do is make the AJAX call look like it's going from the same address as the web server. I just need to edit the httpd.conf file for Apache and enabled mod_rewrite and proxy modules:

LoadModule rewrite_module modules/mod_rewrite.so
LoadModule proxy_module modules/mod_proxy.so
LoadModule proxy_http_module modules/mod_proxy_http.so

Then, I need to turn on the rewrite engine and give it a rule to rewrite certain URLs:

RewriteEngine On
RewriteRule ^/9100a/(.*) $1 [P]

The above rule will rewrite any URL containing /9100a/ with the text following /9100a/. Now, I can change the URL in the selectCam function above as follows:

newurl="/9100a/http://192.168.100.200/SetChannel.cgi?Channel=0";

This will trick the cross site scripting checks since the URL appears to be local. The URL is rewritten by Apache to http://192.168.100.200/SetChannel.cgi?Channel=0, Apache will serve as a proxy to that URL and everyone's happy!

Internet Channel Now Free on Wii

You can now install the Internet Channel free on your Wii. This lets you browse the Internet on your Wii with the Opera browser. It's a little cumbersome typing in URLs with your Wiimote, but the cool thing is our AJAX based floorplan GUI works great on the Wii! Bet you can't do that with HSTouch, CQC or Main Lobby ;)

Tuesday, September 1, 2009

New Toy: Aviosys 9100A Video Server

As I upgrade cameras, I keep the old ones around just in case I find some use for them. The main cameras are connected to an Avermedia NV3000. The leftovers are connected to modulators so we can watch our dog on Slingbox. I decided to get an Aviosys 9100A to get those cameras web enabled. It was easy to install and I've got 2 cameras running right now. I've integrated it into our Floorplan GUI using the device's special URLs described here. It works well, but the camera switching is a tad slow.