The “Devices” data dictionary
I’m always talking about the database this, database that,
So here is the layout of what I have. This main database ‘Devices table’ that holds all the mac address of each device I have in my home. This makes easy to call devices by number and not by mac.
As mentioned before its a SQL database, you can use what ever you want, This is included in the free version of VB 2008 express. I recommend you download it.
Here is what each field means,
Marker= is setup as a sequential number which is automatically set so it increments automatically, this is the # of my device, since there can only be one.
Name= The name of device, for example I try to use a standard name, this makes it easier if there is more than one device in a room, for example “Kitchen MainLamp” or “Kitchen Sink lamp”
Mac = The Insteon address, including the dots, for example “01.41.5F” all in caps
Type = “L” for lamp, “F” for fixed I dont know why but I dont use this yet.
Protocol= “I” for Insteon or “X” for X10
Dimmable=”True or False”
Status = Current status of device, if its ON, or OFF, and even % of device such as 50%
Poll = True or false, when I want to check the status of all devices, setting this to True will include it. For example I don’t want to poll a device which isn’t on line since it will eventually retry 3 times and time out the others. This is specifically for Christmas decorations.
Last_seen = Last time any change was done to the device, on,off, etc.
randompoll = Sometimes some devices can get pesky so I poll a device every couple of minutes, but only one devices, that is where setting this to True means it will include this on the mix
That’s it, that’s my ‘devices’ database… once they are loaded into memory I reference them by number!
Loading the Insteon Database into Memory
In my code I reference them by using VB structures like this, below is the definition which is defined as public so its available to everyone.
'----------------------------------- maint structure for insteon devices Public Structure Insteon_Device Dim MAC As String Dim MacSP As String Dim MacNP As String Dim Name As String Dim Type As String Dim Protocol As String Dim Dimmable As String End Structure
Then I call it as a type of variable like this;
'---- I set this to 40 to make space for more. This is set way on top of the program as public so its accessible in all the program. Public InD(40) As Insteon_Device
This allows me to call the variables like this
debug.print (Ind(1).Mac) ' This will give me the mac of the device by #.. Neat!
Since i come from VB6 Im used to the old classic SQL connection definitions, like all my code there is many ways of doing this so yes you can use the table-adapters and other .NET goodies since I only want Raw power not any fancy tables or colors, I got with my trusty code.
Here is how I load them into the variables itself calling my public function.
Public Function Load_Insteon_devices() Dim marker As Integer Dim TotalDevices As Integer '----------------- SETUP THE SQL CONNECTION AND DATA-READER COMPONENTS Dim sqlConnection2 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString) Dim cmd2 As New System.Data.SqlClient.SqlCommand Dim counter As Integer Dim Reader As SqlClient.SqlDataReader '---------------- FIRST QUERY CHECKS THE AMOUNT OF DEVICES I HAVE cmd2.CommandType = System.Data.CommandType.Text cmd2.Connection = sqlConnection2 cmd2.CommandText = "SELECT COUNT(*) AS CNT FROM Devices" sqlConnection2.Open() : Reader = cmd2.ExecuteReader() : Reader.Read() TotalDevices = Reader.Item("CNT") '---- TotalDevices will grab to # of devices I have :) sqlConnection2.Close() '-------------------- NOW THAT I GOT THE AMOUNT GO AHEAD AND READ ALL THE FIELDS cmd2.CommandText = "SELECT * FROM Devices" sqlConnection2.Open() : Reader = cmd2.ExecuteReader() : Reader.Read() '------------------ SCROLL THRU THE DEVICES AND UPDATED EACH STRUCTURE PROPERTY For marker = 1 To TotalDevices counter = Reader.Item("Marker") InD(counter).MAC = Reader.Item("MAC") '-------------- THE VARIABLES MACSP IS THE SAME AS THE MAC ONLY REPLACING THE DOTS FOR SPACES InD(counter).MacSP = Trim(InD(counter).MAC.Replace(".", " ")) '-------------- THE VARIABLE MAPNP IS THE SMAE AS THE MAC ONLY THAT THERE IS NO SPACES InD(counter).MacNP = Trim(InD(counter).MAC.Replace(".", "")) InD(counter).Name = Reader.Item("Name") InD(counter).Protocol = Reader.Item("Protocol") InD(counter).Type = Reader.Item("Type") InD(counter).Dimmable = Reader.Item("Dimmable") InD(counter).Mastermapping = Reader.Item("Mastermapping") Reader.Read() Next DEBUG.PRINT ("FINISHED LOADING DEVICES") End Function
How I update my Devices Database via code
For my own fancy, Here is the code I use to update a device by #. Like mentioned before when you call a INSTEON group which affects more than one device you dont get individual response from each item so I like to update the DB manually.
So I call another custom function that will update the device using the mac address, and pass the status, such as on or off. For example;
Lg_Update_Device(InD(20).MacNP, "OFF") Public Function Lg_Update_Device(ByVal ID As String, ByVal value As String) As Boolean '------------------------ SETUP CONNECTION AND OTHER GOODIES BEFORE UPDATING ANYTHING Dim sqlConnection34 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString) Dim cmd34 As New System.Data.SqlClient.SqlCommand cmd34.CommandType = System.Data.CommandType.Text cmd34.Connection = sqlConnection34 '------ THIS IS THE ACTUAL SQL COMMAND TO UPDATE THE DEVICE USING THE MAC AS THE KEY. cmd34.CommandText = "UPDATE Devices SET status= '" + value + "', last_seen='" + Now().ToString + "' WHERE MAC='" + ID + "'" If sqlConnection34.State = ConnectionState.Open Then sqlConnection34.Close() sqlConnection34.Open() cmd34.ExecuteNonQuery() '---------------- CLOSE CONNECTION AND EXIT FUNCTION sqlConnection34.Close() Update_TimeStamp() End Function
My Sql database runs on the same box as the ‘server’ and is really fast, SQL caches information in memory so the more you have the better. It looks like a lot of code but it flyes!. Hopefully this example will help you on creating your own DB.