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.

Cheers,