Simplerulebuilder - now private

SmartApps & Automations
163

Hi Joe. Got a primary key error last night and I cannot use the engine anymore.

Violation of PRIMARY KEY constraint ‘PK_LocationDataOptions’. Cannot insert duplicate key in object ‘dbo.LocationDataOptions’.
The statement has been terminated.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint ‘PK_LocationDataOptions’. Cannot insert duplicate key in object ‘dbo.LocationDataOptions’.
The statement has been terminated.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Violation of PRIMARY KEY constraint ‘PK_LocationDataOptions’. Cannot insert duplicate key in object ‘dbo.LocationDataOptions’.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +1767866 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +5352418
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +269
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1406
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +205
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +160
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) in e:\Builds\EntLib\Latest\Source\Blocks\Data\Src\Data\Database.cs:443
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command) in e:\Builds\EntLib\Latest\Source\Blocks\Data\Src\Data\Database.cs:714
SmartThingsRuleBuilder.App_Code.Location.saveLocationDataOptionValue(Int32 AccountId, String DataOptionId, String Name, DataOptionTypes Type) in c:\Users\Joe\Desktop\Projects\SmartThingsRuleBuilder\SmartThingsRuleBuilder\App_Code\Location.cs:259
SmartThingsRuleBuilder.App_Code.Location.updateLocalLocationData() in c:\Users\Joe\Desktop\Projects\SmartThingsRuleBuilder\SmartThingsRuleBuilder\App_Code\Location.cs:177
SmartThingsRuleBuilder.RuleBuilder.Page_Load(Object sender, EventArgs e) in c:\Users\Joe\Desktop\Projects\SmartThingsRuleBuilder\SmartThingsRuleBuilder\RuleBuilder.aspx.cs:52
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51
System.Web.UI.Control.OnLoad(EventArgs e) +92
SmartThingsRuleBuilder.App_Code.BasePage.OnLoad(EventArgs e) in c:\Users\Joe\Desktop\Projects\SmartThingsRuleBuilder\SmartThingsRuleBuilder\App_Code\BasePage.cs:14
System.Web.UI.Control.LoadRecursive() +54
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +772

164

Can you try again now?

165

Hi Joe.
Im getting the same thing.
It started happening after I added more sensors and a zwave thermostat.
Alun

166

Can’t imagine why not.

1 Like
167

Can’t thank you enough for making my life a lot easier. I hope Smartthings institutes this method going forward or at least learns form it. You solved a problem that was driving me crazy in seconds.

Now you just need a pretty Favicon:)

2 Likes
168

Joe - this is absolutely fabulous… I had started writing something similar when I found this so I stopped since you have done such a great job. I signed up online at the website… you might want to change the password field to a web form password type so it isn’t displayed and is encrypted.

169

After struggling with unreliable rules on ST, unreliable presence statuses, I moved the bulk of my logic to rules built on SimpleRuleBuilder. JoeC, your efforts are a godsend and have worked reliably to get around many of the constraints I have run into with ST.

I use a collection of ST virtual switches to record various states, including presence. Coupled with that is IFTTT to set some of those virtual switches (such as reliable presence) and reliably control my Wemo devices, then use SimpleRulesBuilder (SRB) to act on and control what ST should be doing.

I really hope ST can learn from flexibility that SRB has given us.

2 Likes
170

Great project @JoeC, and tremendous effort accommodating the requests and suggestions from users!

I skimmed all the posts to catch up …

Do you have a description of the overall architecture / design of the system? i.e., what runs on your server, what lives in a SmartApp, how they interact, etc.? … There’s a lot I’d love to learn; but I guess this is not an open-source project, right?

Thanks!

171

@kewashi @Paulo @Avron - Thanks! It’s always fun to hear about how people are putting SRB to use.

@tgauchat - Correct, not open source. Primarily because I’ve never run an open source project and I’m afraid of the effort it would entail. I’m more than happy to share any details about the project and overall architecture though.

Here we go, let’s see how well I can do here.

High Level
The Simple Rule Builder consists of a SmartApp that runs in the SmartThings cloud, a web application written in asp.net/C#/Javascript, and a MS SQL server for the database. A user creates an account on the website (https://simplerulebuilder.com) and is then prompted to install the SmartApp and choose which devices will have permission to use the app.

SmartApp
The SmartApp’s only job is to route information and commands. When a device’s state changes (i.e. a lock is unlocked) the SmartApp will send that notification to the SRB server. The notification tells the server the device id, the event, and the new value. It uses an HTTP POST to send the data in a JSON format. This is for device state changes, mode changes, and hello home actions being executed.

The SmartApp is also responsible for receiving commands from the SRB. I have a single endpoint setup in the app for all of the commands that the SRB might send to the SmartApp. Then a case statement to handle the different device types. This makes it easy to add device types going forward.

The Simple Rule Builder Web App and Server
The SRB web app is responsible for providing a UI for creating and managing your rules. Putting this together was sort of an experiment with building a non-trivial javascript app without using a framework. It went OK :smile:

The web server is always listening for device updates from the SmartThings cloud. This is done using a very simple .ashx event handler. When an update is received the app checks to see if there are any rules that have the just updated device as a trigger. If there are, it will evaluate those rules. It will also save the devices current state in the database. If the rule evaluates to true, the server will send the corresponding ‘acitons’ as commands to the SmartThings cloud.

One of the key things to making all of this work quickly and reliably is storing all of the devices’ current states in the database. This means that when a rule needs to be evaluated no additional API calls need to be made to the SmartThings cloud. It also means that the rule evaluation can happen as a dynamically generated SQL query, which is also pretty efficient.

For time based triggers (at 5:00 PM do this… ) I have a simple windows console application that is scheduled to run every minute via the windows task scheduler. This just submits a command to the main web app that then looks for any rules that have time of day as a trigger and evaluates those.

–
Hope that was informative, let me know if there is anything else you’d like more detail or clarification on!

3 Likes
172

Avron - is there a thread somewhere on here that describes how you used your virtual switch setup to enhance presence sensing? I found one that talked about using Tasker to do it, but I’m finding Tasker a little wonky.

Could you describe how you’re doing it?

1 Like
173

Really nice Joe - thanks for taking the time to explain your architecture. My code was headed in much the same direction. I might finish it anyway for the learning experience. We’ll see.

1 Like
174

I just swapped out my ST presence sensors for virtual switches as you’ve described, here is my setup (two person household):

  • Two virtual switches for myself and my wife
  • “ON” status of switch changes mode to Home, “OFF” does nothing but signifies we are away (for reasons to be explained)
  • A few IFTTT rules to detect location of our phones; I use the recipe “if device connects to home wifi then change virtual switch to ON” and vice versa for when I disconnect. You could also use the Android Location channel, a Foursquare check-in or whatever works best for you.
  • A rule in Simplerulebuilder that activates my “Goodbye” phrase when both virtual switches are set to OFF. This phrase sets the mode to away and turns off a few other switches (lights, heaters etc).

If I tied the switches to change the mode to Away when a person left the area (off) this would cause issues if one of us was still at home, setting them up with no status for “off” then using Simplerulebuilder to activate the desired phrase sorts this out.

Set this up last night so still a few kinks to iron out. My wife’s phone didn’t activate the IFTTT recipe I set up so her virtual switch didn’t turn off this morning when she left. I left later so was able to manually change her status, one of the benefits of having virtual switches instead of flaky presence sensors.

Hope that all makes sense!

175

Curious… why couldn’t you use the built in if person has left area feature and select both people? I use this and it works great. Both of us have to be gone for it to trigger…

176

I had some issues with mobile presences too (had to reset my Hub to add a Quirky Tripper which ended up breaking a lot of stuff, don’t ask!) so I removed all mobile presences and presence sensor fobs from my network, hence I’m trialling these virtual presence switches.

1 Like
177

This looks like such a cool bridge for ST’s minimal rules…I was looking for something with more conditions!! What’s the support like for ST’s multiSense? I was hoping to use change in axis measurements/rotation as a trigger…
Unless I’m missing it, this just doesn’t seem to be something ST apps deal with, for some reason?

Any chance?

178

I think you’re referring to capability.threeAxis for the multiSense. Does this capability deal with motion/acceleration? I haven’t built support for that because I don’t have any devices with that capability and I’ve had a tough time wrapping my head around how to construct a rule for it.

179

Yup. It uses the three axis numbers to create triggers. I’ve got no idea how it works…there are a couple of threads on here about it. Hello Home Cube is one, and there’s at least one more. No idea how they’re pulling the info, as I’m not a coder, but I’m sure somewhere out there understands it! Seems like it could be a very cool addition to the rules builder…lots of options!

180

3 axis measurement can be used as a tilt sensor or in an accelerometer. Surge, heave, and yaw.

And the physics:

http://physics.rutgers.edu/~aatish/teach/srr/workshop3.pdf

Typical use cases:

Garage door angle
Human fall detection
Somebody moved an object the sensor is attached to

Typical rules engine setup:

Trigger based on either:

a specified change or
Within a specified range

For each axis

Depending on how the sensor reports.

That is, the sensor returns

x value, y value, z value or

X change, y change, z change

But user might be only interested in y value. (Or y change.)

So from a rules engine design standpoint, the simplest case is really the same as a humidity or lux sensor except the sensor returns 3 values each time. Your user wants either a delta or a specific range to be the trigger for some other event. Some users will want all 3 values, some will only care about 1 and ignore the other 2.

I don’t know whether the Multisensor reports measured value or delta.

But the simple rule is just:

If x is between min and max, then…

If y is between min and max, then…

If z is between min and max, then…

With the ability to and/or those.

Where these get tricky from a rules engine design standpoint is people always end up wanting to stack a bunch of very specific possibilities. If x is between min and max and y is between min and max and z is not between min and max… Or if x is between min2 and max2 and z is above min2. For example, monitoring someone who falls out of a wheelchair ends up with a really complex set of ands, ors, and nots.

Anyway…the simple case should be pretty simple. But be prepared for lots of requests for much more complex rule sets.

181

This is a pretty useful thing you’ve created!

Do you have any plans on adding Sunset / Sunrise events?

Also, I like the “And” and “Or” options, but do you plan on adding an “Unless” option?

182

For cloudy morning or rainy mornings, a lux sensor would not yield desired results.