Map.Query to get only some values from a map table and save to a payload xml

[marcusvdt]

First of all I'd like to say that this is kind of an advanced topic related to creating an enhanced controller for My Sensors 100% based on python and VC. My code is based on the original controller by James/Dave/Kalle (thanks for starting this out!).
The job is 90% done and I'll share the result when it is done.
So let's go...

For each type of standard sensors that are supported by MySensors, I defined a control_type, like boolean, integer, string, etc. The purpose of this definition is to use this information to let VC dynamically know which sensors can receive each of the payloads. For example, BOOLEAN type of sensors, will accept payloads ON or OFF. Sensors that have been identified as INTEGER type, will accept payload range 1-100.

In short, once a new sensor is presented to the controller, the python script sends to VC what type of sensor is being presented and then this relationship between the sensor ID and the type of commands it will accept is created on the table Sensors_Control_Type for future reference.

At the same time, another table automatically saves an ALIAS for each sensor, relating the sensor ID with an ALIAS. That ALIAS will be used dynamically later as payload for commands (so that the user can say the ALIAS when requesting a command to a sensor, and consequently let VC build the command to send to a specific sensor accordingly).
The initial ALIAS is equal to the type of the sensor as defined in the MySensors API, for example a light actuator will be created on the table as LIGHT. The user will change this manually to anything that is desired like "LIVING ROOM LIGHT". This is the only thing that I'm planning to let to the user do manually.

So I have a table Sensors_Control_Type with the fromkey being composed of "nodeID|childID", and the tovalue field containing the control_type as I defined in the presentation phase (BOOLEAN, INTEGER, FLOAT, ETC). Each "nodeID|childID" represents an unique sensor in the system, hence I have one line in the table for each sensor. But the same control_type (value) may appear many times in this table because I may have many sensors of the same type (ie. many light actuators in the house).

Well, I want to have one command in VC for controlling BOOLEAN sensors, another command for controlling INTEGER sensors, and so on. I want these payloads to be generated dynamically and updated when a new sensor is presented.
This way the users can add new standard sensors to the system dynamically. When a sensor presents itself, the commands for it will be immediately available to the user because the payload xmls will be updated accordingly.

As an example, the command for BOOLEAN sensors is:

Code: [Select]

<?xml version="1.0" encoding="utf-16"?>
<!--VoxCommando 2.1.4.2-->
<command id="559" name="Toggle on/off" enabled="true" alwaysOn="False" confirm="False" requiredConfidence="0" loop="False" loopDelay="0" loopMax="0" description="This command receives the nodeID|childID from a payload xml called (MySensors Payloads\Sensors_BOOLEAN_sensors.xml) based on the ALIAS of the sensor you want to set the status for. The actual status that you want to set comes from (MySensors Payloads\Sensors_BOOLEAN_payloads.xml). In the end, this cmd builds the message to be sent to the sensor. The sub type field comes from the Sensors_Type map table. Hence the command is dynamically built.&#xD;&#xA;nodeID;childID;type=1;ack=1;;subtype={M:Sensors_Type.{1}};payload=1">
  <action>
    <cmdType>Results.SetLastResult</cmdType>
    <params>
      <param>{1}</param>
    </params>
    <cmdRepeat>1</cmdRepeat>
  </action>
  <action>
    <cmdType>Results.Replace</cmdType>
    <params>
      <param>|</param>
      <param>;</param>
    </params>
    <cmdRepeat>1</cmdRepeat>
  </action>
  <action>
    <cmdType>PY.ExecString</cmdType>
    <params>
      <param>MySensorsGatewayBuildWrite("{LastResult};1;1;{M:Sensors_Type.{1}};1\n")</param>
    </params>
    <cmdRepeat>1</cmdRepeat>
  </action>
  <phrase>Turn</phrase>
  <payloadFromXML phraseOnly="False" use2partPhrase="False" phraseConnector="by" Phrase2wildcard="anyone" optional="False">MySensors Payloads\Sensors_BOOLEAN_sensors.xml</payloadFromXML>
  <payloadFromXML phraseOnly="False" use2partPhrase="False" phraseConnector="by" Phrase2wildcard="anyone" optional="False">MySensors Payloads\Sensors_BOOLEAN_payloads.xml</payloadFromXML>
</command>


I have all this done and already working, EXCEPT the dynamic creation of the payload files like Sensors_BOOLEAN_sensors.xml

I would like to be able to do something like this:
select * from Sensors_Presentation where fromkey in (select fromkey from Sensors_Control_Type where tovalue='BOOLEAN')

What I need is to get the result of this query to output from the tables to a payload xml. Before I go and research how can I do that using python, I thought I should ask for a confirmation that a command like Map.Query can do that or not.
Maybe you guys at VC can create a new command in VC like Map.QueryToXML, what do you think? ;-)
I've tested Map.Query with the above sql query but it returns only the first result. Either way I would not be able to export to a payload xml only with the Map.Query

By the way, my controller works like a charm, but I'm trying to eliminate any manual setups so the thing runs smoothly and dynamically.

[PegLegTV]

when you do a Map.Query does it return as multiple matches? if so then you could change the amount of times that action repeats from "1" to "{#M}" (found at the end of the action, in your LCB)

or if you can get the information into a map there is an action called  "Map.ExportPayloadXML" that will export to a payload.xml of your choice

with both of the above you can then add "Group.Rebuild" action with the name of the group that has the payloads in them so that way the commands see the new payloads automatically, and you will be able to use the new alias after updating the payload

hope this helps

[jitterjames]

Going to have to put you on the payroll soon PegLeg... 

[marcusvdt]

when you do a Map.Query does it return as multiple matches? if so then you could change the amount of times that action repeats from "1" to "{#M}" (found at the end of the action, in your LCB)

How do I know if it returns multiple matches? I'm looking on the VC's history panel and I see only one match when I place the mouse over the result, where it should return 2 matches.

or if you can get the information into a map there is an action called  "Map.ExportPayloadXML" that will export to a payload.xml of your choice

Yes, I could get the information on a map, but then I would have to create a separate map table for each defined control_type, containing the ALIAS as the tovalue and the ID (nodeID|childID) as the fromkey field. After that, I would use the action "Map.ExportPayloadXML" to create and update the payload xml for each control_type that I have defined.
It would work perfectly, but with an inconvenience for the user: the ALIAS would need to be edited in two tables, leading for confusion and eventual mistakes. The priority is to simplify the controller.
If I can't get James to add a new action like I suggested, I will prefer going through the python script, creating the new payload files by querying the tables directly so I can keep priority on letting the least effort to the end user.
I bet my pants that it can be done not so hardly in python, I just didn't have the time to research yet.

with both of the above you can then add "Group.Rebuild" action with the name of the group that has the payloads in them so that way the commands see the new payloads automatically, and you will be able to use the new alias after updating the payload

Yes, this is the plan after I find a way of dynamically creating the payload xmls.

[PegLegTV]

if I'm following correctly, it looks like all "light" sensors show up in the Map as "LIGHT" in the fromkey field, with Maptables, if the fromKey fields match a current entry  it will only allow one entry because it is seen as a duplicate, I'm not sure if this is the problem you are running into, but this would cause only one entry to the map table for two light sensors, you could possibly add a counter to your sensors so it would be "Light01", "Light02".... and so on, so that way it never shows up as a duplicate and ignored by the map.table,

I don't know if this is the problem you are running into as I don't know which sensors your are trying to add to the map or what sensor(s) are being added to the map when you try and use the Map.Query,



It Sounds like your maptable looks like this:

Code: [Select]

FromKey:                    ToValue:
--------------------|----------------------------
Alias 1             |          NodeID/ChildID
--------------------|----------------------------
Alias 2             |          NodeID/ChildID
--------------------|---------------------------

is this correct?

[marcusvdt]

No, it is not. Ok, Let's go in more details...

Sensors_Presentation is the table that registers the existence of a new sensor. It uses fromkey as a primary key in a database. The fromkey field is uniquely exclusive.

Sensors_Control_Type is the table that has the purpose of categorizing each sensor by type of phrase it is going to accept.

These tables are created and updated automatically during the presentation of a new sensor. Obviously I have other tables for saving other data like the current status of a sensor. All of them follow the same key nodeID|childID.
That way I can get different details about a sensor by always using the same key.

Code: [Select]

Sensors_Presentation
    FromKey:       |      ToValue:
-------------------|------------------------------
     51|99         |     LIGHT HT
-------------------|------------------------------
     52|99         |     LIGHT BEDROOM
-------------------|------------------------------

Sensors_Control_Type
    FromKey:       |      ToValue:
-------------------|------------------------------
     51|99         |     BOOLEAN
-------------------|------------------------------
     52|99         |     BOOLEAN
-------------------|------------------------------

To be able to take advantage of the categorization created on table Sensors_Control_Type, I need to be able to dinamically create a payload xml.
The command that I pasted in the original post is the one that turns sensors ON/OFF, hence only the sensors that will accept ON/OFF control should exist in this payload xml file.

I know I could have a series of IFs inside the command, allowing the command itself to process this. I could have the entire Sensors_Presentation transformed into a payload xml, and I could have another payload xml with all the possible controls that any sensor can accept, like ON, OFF, 0-100, etc.
And then the IF logic inside the command would be able to tell the user that a boolean sensor don't accept a number as a payload for example. But, in this case I would have a more complex command that possibily would take more resources to process and make the recognition of the payload more likely to be misunderstood by VC.
By separating each command with their respective sensors and payloads, I'm making the system more likely to work 99% of the time.

[jitterjames]

I will take a look at Map.Query which was never fully fleshed out I think, and I'll also consider adding an optional parameter to add a where filter on the export to xml action.

If it is not too difficult for me to implement, I think map query should generate multiple matches with {Match.1.1} being the "fromKey" and {Match.1.2} being the "toValue".  Then you could use Results.MatchToXML to create the payloads file.

I'll let you know.

[marcusvdt]

Thanks James!

In this meantime, I've tested Map.Query again, and {#M} returns 0. The {LastResult} contains only the first match.

If you implement this, then I'll be done with the controller pretty soon.
Thank you very much!


FYI, and probably subject to another thread:
I was already trying to query the sqlite file directly from Python, but I received a weird error during the import sqlite3

As per what I quickly researched on the internet, this seems to be a 32 bit/64 bit compatibility issue with the DLL System.Data.SQLite.dll. I'm using Windows 8.1 64 bit.

Code: [Select]

IOError: System.IO.IOException: Could not add reference to assembly IronPython.SQLite
   at IronPython.Runtime.ClrModule.AddReference(CodeContext context, String name)
   at IronPython.Runtime.ClrModule.AddReference(CodeContext context, Object reference)
   at IronPython.Runtime.ClrModule.AddReference(CodeContext context, Object[] references)
   at Microsoft.Scripting.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.LightLambda.Run4[T0,T1,T2,T3,TRet](T0 arg0, T1 arg1, T2 arg2, T3 arg3)
   at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
   at Microsoft.Scripting.Interpreter.FuncCallInstruction`6.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.LightLambda.Run4[T0,T1,T2,T3,TRet](T0 arg0, T1 arg1, T2 arg2, T3 arg3)
   at IronPython.Compiler.Ast.CallExpression.Invoke1Instruction.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.LightLambda.Run1[T0,TRet](T0 arg0)
   at IronPython.Compiler.PythonCallTargets.OriginalCallTarget0(PythonFunction function)
   at IronPython.Runtime.FunctionCaller.Call0(CallSite site, CodeContext context, Object func)
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at Microsoft.Scripting.Interpreter.FuncCallInstruction`5.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.LightLambda.Run3[T0,T1,T2,TRet](T0 arg0, T1 arg1, T2 arg2)
   at IronPython.Compiler.Ast.CallExpression.Invoke0Instruction.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.LightLambda.Run1[T0,TRet](T0 arg0)
   at IronPython.Compiler.RuntimeScriptCode.InvokeTarget(Scope scope)
   at IronPython.Compiler.RuntimeScriptCode.Run(Scope scope)
   at IronPython.Runtime.PythonContext.InitializeModule(String fileName, ModuleContext moduleContext, ScriptCode scriptCode, ModuleOptions options)
   at IronPython.Runtime.PythonContext.CompileModule(String fileName, String moduleName, SourceUnit sourceCode, ModuleOptions options, ScriptCode& scriptCode)
   at IronPython.Runtime.Importer.LoadModuleFromSource(CodeContext context, String name, String path)
   at IronPython.Runtime.Importer.LoadPackageFromSource(CodeContext context, String name, String path)
   at IronPython.Runtime.Importer.LoadFromDisk(CodeContext context, String name, String fullName, String str)
   at IronPython.Runtime.Importer.ImportFromPathHook(CodeContext context, String name, String fullName, List path, Func`5 defaultLoader)
   at IronPython.Runtime.Importer.ImportFromPath(CodeContext context, String name, String fullName, List path)
   at IronPython.Runtime.Importer.ImportTopAbsolute(CodeContext context, String name)
   at IronPython.Runtime.Importer.ImportModule(CodeContext context, Object globals, String modName, Boolean bottom, Int32 level)
   at IronPython.Modules.Builtin.__import__(CodeContext context, String name, Object globals, Object locals, Object fromlist, Int32 level)
   at Microsoft.Scripting.Interpreter.FuncCallInstruction`7.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.LightLambda.Run7[T0,T1,T2,T3,T4,T5,T6,TRet](T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4, T5 arg5, T6 arg6)
   at IronPython.Runtime.Importer.ImportLightThrow(CodeContext context, String fullName, PythonTuple from, Int32 level)
   at IronPython.Runtime.Operations.PythonOps.ImportTop(CodeContext context, String fullName, Int32 level)
   at Microsoft.Scripting.Interpreter.FuncCallInstruction`4.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.LightLambda.Run2[T0,T1,TRet](T0 arg0, T1 arg1)
   at IronPython.Compiler.PythonScriptCode.RunWorker(CodeContext ctx)
   at IronPython.Compiler.PythonScriptCode.Run(Scope scope)
   at IronPython.Compiler.RuntimeScriptCode.InvokeTarget(Scope scope)
   at IronPython.Compiler.RuntimeScriptCode.Run(Scope scope)
   at Microsoft.Scripting.Hosting.CompiledCode.Execute(ScriptScope scope)
   at vcPlugin.iPy.execString(String strCode)


[jitterjames]

I don't know if it will help, but VoxCommando is a 32 bit application so you should probably use a 32 bit dll.

[marcusvdt]

I don't know if it will help, but VoxCommando is a 32 bit application so you should probably use a 32 bit dll.

Hi James. I'm using the one that came with VC, which is inside VC's dll directory. In fact I don't have the option to choose another one, but maybe I'll try to download another one and replace the original one that is inside the VC's dll directory with the new one and see how it goes.
Can't test now, but I'm looking at this site, and they say there are compatibility issues. I'm probably using a 32 bit dll where a 64 bit dll is required. But this is just a guess.
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

The whole issue with this dll is not my priority for now since you told me you are going to look at Map.Query and that will support the only remaining feature of the new MySensors controller that I'm building.
Anyway, If I get any conclusions regarding this issue, I'll share on another thread for the benefit of others.

Thanks!

[jitterjames]

Hi James. I'm using the one that came with VC, which is inside VC's dll directory. In fact I don't have the option to choose another one, but maybe I'll try to download another one and replace the original one that is inside the VC's dll directory with the new one and see how it goes.

Don't do that!

I will implement these changes but there are quite a few things I'm working on  as well and some are not finished so I'm not sure when I will be able to release a new version.  I'm also going on vacation in about a week, so it probably won't be before mid June.  If I can I'll try to do a test release with this and a few other changes so you can play with it, before I leave.

[marcusvdt]

Don't do that!

I will implement these changes but there are quite a few things I'm working on  as well and some are not finished so I'm not sure when I will be able to release a new version.  I'm also going on vacation in about a week, so it probably won't be before mid June.  If I can I'll try to do a test release with this and a few other changes so you can play with it, before I leave.

Wow, I got scary now! Just kidding... But seriously, what happens if I do replace the file with another one? I was thinking about saving a backup copy of the original dll of course.

As I said, I prefer to use the Map.Query (when it is fixed to return matches) followed by  Results.MatchToXML to get the payload file created, followed by Group.Rebuild for the group to allow for immediate use of the new payload file.

But in the meantime I was playing just to know if it is possible to build the xml from the python script directly. That's how I discovered I can use sqlite from the IronPython plugin right now because of that error that I posted earlier.
For the record, the problem with sqlite has nothing to do with the current development of the MySensors controller. This thread has the sole purpose of asking for help with Map.Query and I'm very excited to see that fix that you mentioned implemented. That's enough for finishing the enhanced MySensors controller that I'm developing.

In the future, I would like to let the a python script, through the PY plugin to make updates, inserts and deletes directly to the tables to allow for a lighter controller on VC side. All the hard work would be performed on python script and  VC would then be responsible only for having the command structure and dealing with execution and feedback.
But this is another story, to another thread.

Thanks!

[jitterjames]

Wow, I got scary now! Just kidding... But seriously, what happens if I do replace the file with another one? I was thinking about saving a backup copy of the original dll of course.

Probably your maps will stop working.  You can always try it but if you don't get anywhere with it just remember to replace the original.

In any case the python wants to use a different dll file completely so I don't think this is going to help.

VC uses System.Data.SQLite.dll and the python script wants to use ironpython sqlite.dll but I can't get it to load this either.

[Haddood]

it will be great if we can have a complete support of SQL query ... Especially selecting across multiple tables ... So the matches will be 1.1, 1.2,1.3 ...etc.

It took me a bit to figure that it is the way map.query returns only one result ... since the normal behaviour of SQL query with a wild card is to return all matches ...

[marcusvdt]

I got the sql through python to work. Thanks for reminding me the error was with another dll.
I just created a new thread to share an example.

But I'm still waiting for the Map.Query huh!

Thanks.