Gluing it all together

Hi all!

I am looking for some ideas as to how best to integrate Indigo with some other homebrew automation tools that I have already running on a Linux server. But first: kudos to Matt for putting together this phenomenal bit of software - this makes Insteon vastly more useful... and usable.

To give a little bit of background: I started work on my home automation system with the premise that there did not already exist a good way to rapidly glue together all the disparate scripts and tools that I expected I would need. To that end, I came up with a concept which I called "homefs" - basically, something just like the Linux procfs, but for your house instead of your kernel. So I could have one script which extracts data from TED (the energy detective), another which polls temperature and relay states from the HVAC system, another which keeps track of the AV equipment, another to receive state changes from the security system, another to dump data into rrdtool, and so on. All these scripts just independently dump their data into a directory tree under /homefs/. Then to implement any kind of smart control logic, all I have to do is make a trivial script which cats the appropriate files to get its input data/events, and then does whatever it wants with it. For example, if I want to know the temperature at thermostat #3, I would just read the file "/homefs/hvac/aprilaire/data/3/TEMP", and to get events I could use tail -F with select, or use inode monitoring. Such scripts could run continuously or be triggered by cron or whatever.

However, after seeing how Indigo supports dumping all its state data out to a mysql database, I am thinking that maybe this might be a better model than "/homefs" to use for all my other scripts too. The thing is, I'm pretty green when it comes to databases so I'm not really sure of what I'm getting into at this point.

So i have a few dumb beginner questions about the mysql feature, and mysql in general.

- Currently this is strictly a one-way data dump, right? So if I have some other tool which wants bidirectional access to Indigo, it should use mysql to query device states, and HTTP requests to send commands to Indigo?

- Would it make sense to, or is there any plan to make the mysql connection bidirectional?

- Would it make sense for Indigo to perhaps adopt MySQL at some point as its own internal data store, as well as a means of integrating with external tools?

- Could an event-driven system comprised of many separate processes be built around a mysql database? For example, could I make a tool which connects to the database and then waits for one of some set of values to be updated, and then take some action? I've heard of "triggers" in SQL but that's only for doing calculations internal to the database, right? I am wondering if mysql could effectively serve as the machinery for dispatching events, rather than everybody having to continuously poll the data they're interested in.

- Is there anything else on the roadmap for Indigo that I should be thinking about?

Basically, I expect I am going to need to implement a SQL interface anyway in order to communicate with Indigo, and I'm wondering if while I'm at it, I should just plan to adopt the same model for all of my tools. Or is there is some other model that I should consider? I'm really trying to keep it simple but if there is significant capability to be gained by introducing a database then I'll go for it... any thoughts or advice on how to approach this would be most welcome. Thanks![/b]

It looks like you could get events out of MySQL by using a triggered UDF which shoots off UDP packets: http://www.papablues.com/Software/myProcDbg1_2.pdf

That's a rather circuitous way of doing things, but I guess it could be a good generalized mechanism that would work for any kind of data you wanted to watch.

- Would it make sense to, or is there any plan to make the mysql connection bidirectional?

Having MSQL in Sync one way is nice, and for "normal"
remote web acces good enough. (MySQL means PHP, and PHP is good )
finding a way to "trigger" the client when a device value changes
so it can refresh itself would be of course extremely cool.
I dont know if its possible to get a trigger from the MySQl server,
(or from the scripts that fills the table)

I hope you find a way.

(I saw Cocoa classes for connecting to MySQL, jippy
I just don't know where to start, its like Christmas time
with all these new possibility's)

So is there currently _any_ supported way of obtaining Indigo events, short of setting up triggers for each thing in Indigo?

Obviously I could tail the event log file, but that's pretty kludgy, requiring some process running to watch and parse the files and keep track of log rotations.

I'm thinking something more along the lines of being able to connect directly to the indigo process via a socket, and then receiving a message each time any event happens.

Matt has hinted that there will be other plugin mechanisms in the future and I'm wondering if that's what I'll need to wait for...

I'm thinking something more along the lines of being able to connect directly to the indigo process via a socket,

the socket connection wprks just great.

[matt (support)]

Hello Sean,

Currently this is strictly a one-way data dump, right? So if I have some other tool which wants bidirectional access to Indigo, it should use mysql to query device states, and HTTP requests to send commands to Indigo?

Yes and no. The MySQL client, which is a client to the main Indigo Server, currently takes the TCP socket messages (which are XML) broadcasted from the Indigo Server and uses those to write out data to the MySQL database. The client does not try to install change notification functions (or whatever MySQL calls those) to go the other direction. But the client could send the XML messages (again via its TCP socket with the Indigo Server) to update device states. So the current version of IndigoMySqlClient doesn't do it, but it is possible for an Indigo client to update Device (and Variable) states.

Would it make sense to, or is there any plan to make the mysql connection bidirectional?

I don't have plans to do this at this point, but the IndigoMySqlClient is open source. I do still need to modify the license terms, but the source is on your system. The file that does the writing to the database whenever it receives Indigo Server ReplacedDeviceElem or ReplacedVariableElem messages is:

/Library/Application Support/Perceptive Automation/Indigo 2/IndigoMySqlClient/indigopy/indigohooks.py

Note I'm also thinking about changing the client over to use Postgresql instead of MySQL. The MySQL license is more restrictive and what the IndigoMySqlClient is doing here, although I believe it to be in compliance with the GPL license, is bit of a headache.

Would it make sense for Indigo to perhaps adopt MySQL at some point as its own internal data store, as well as a means of integrating with external tools?

At this point I prefer an internal more tightly coupled database so that everything isn't public and I can make changes on a whim without worrying about breaking any future 3rd party software.

Could an event-driven system comprised of many separate processes be built around a mysql database?[/b] For example, could I make a tool which connects to the database and then waits for one of some set of values to be updated, and then take some action? I've heard of "triggers" in SQL but that's only for doing calculations internal to the database, right?

I'm not sure. I haven't really read up on MySQL triggers, but I had presumed that I could use them to execute code somehow (not just for a calculation).

Is there anything else on the roadmap for Indigo that I should be thinking about?

Definitely look at the sample Python code used by IndigoMySqlClient (and IndigoWebServer). You can do pretty much anything via a native TCP socket client. The Indigo 2 Cocoa client application uses the same TCP socket for communicating with the server, as does the IndigoWebServer (the web server is actually a client to the Indigo Server).

The next major version of Indigo (not 2.5) should have a plug-in architecture for adding new hardware support. For now the only way to extend Indigo is via AppleScript (which has some limitations) or by creating a full blown TCP socket client (like IndigoMySqlClient, IndigoWebServer, or the Indigo 2.0 Cocoa app). I want to provide an API for easily defining additional hardware, that will let the hardware state fire Trigger Actions and be inspected on Control Pages.

Regards,
Matt

Awesome... it sounds like the TCP interface will do exactly what I need then. Is it documented? I have been waiting all day for WireShark to install via Mac ports so I can have a look...

I might roll some kind of general-purpose library in Perl, in case there are others like me who prefer scripting with punctuation instead of nouns and verbs.

BTW I'm just curious if you had some applications in mind for the MySQL feature. The ability to query device states in a database is interesting I suppose, but without some means of event notification it seems a bit limited.

[matt (support)]

Awesome... it sounds like the TCP interface will do exactly what I need then. Is it documented?

Email me (indigo-support at perceptiveautomation.com) and I'll send you a rough communication protocol document. The Python indigodb.py and indigoconn.py files are also very helpful for figuring out the basics. There is a bit of documentation in the source and they are pretty easy to follow.

I might roll some kind of general-purpose library in Perl, in case there are others like me who prefer scripting with punctuation instead of nouns and verbs.

One advantage to using the Python library -- as the application evolves the main python classes (indigoconn.py, indigodb.py) that handle the communication and XML -> object conversion will be updated and maintained as they are used by the IndigoWebServer and IndigoMySQLClient. Point being, you'll get some of the basic functionality for free and it will easily be updated should the protocol or XML structure change.

That said, I definitely won't discourage you from creating a Perl client. I imagine quite a few more folks know Perl than Python.

BTW I'm just curious if you had some applications in mind for the MySQL feature. The ability to query device states in a database is interesting I suppose, but without some means of event notification it seems a bit limited.

We've had a couple of requests for it. One from a user working on a custom project, and also from folks that want to save historical state information. It is good if you need to make a graph of HVAC temperature or create reports based on device usage, for example.

Regards,
Matt