Database design

Pop your questions regarding Home automation software here.....

Re: Database design

Postby tickett » Mon Dec 19, 2011 1:01 am

Hi gjelsvik, your path was very similar to mine... starting with power consumption monitoring then "getting the bug" and wanting to monitor and control EVERYTHING :)

I'm currently using the home automation hub software to process and post to pachube but want to take it a step further and store in my own SQL database (still undecided whether to use SQL Server or mySQL).

Would you all be willing to share your database/table designs rather than me re-inventing the wheel?

Thanks
Lee
tickett
Starting Member
Starting Member
 
Posts: 7
Joined: December 2011

Re: Database design

Postby vanisher » Thu Apr 05, 2012 9:41 pm

I'm also interested in the scriping
-------------------------------------------------------------------
A Domotica starter, currently running: Plugwise, Z-Wave, Jeenods, Homeseer / DomotiGa
vanisher
Member
Member
 
Posts: 293
Joined: June 2011

Re: Database design

Postby Digit » Fri Apr 06, 2012 10:47 am

Here's my database; the script is based on MS SQL 2005.
Attachments
script.rar
(3.35 KiB) Downloaded 294 times
User avatar
Digit
Global Moderator
Global Moderator
 
Posts: 3388
Joined: March 2006
Location: Netherlands

Re: Database design

Postby vanisher » Fri Apr 06, 2012 7:11 pm

Thx!! Will look into it and trying to get in in mySQL.
-------------------------------------------------------------------
A Domotica starter, currently running: Plugwise, Z-Wave, Jeenods, Homeseer / DomotiGa
vanisher
Member
Member
 
Posts: 293
Joined: June 2011

Re: Database design

Postby jennirivera » Tue May 28, 2013 7:25 am

I use one record for every hour otherwise I get to much record which is not important to me. It is simple, once a reading comes in I check if for that date/hour a record is in the database.
jennirivera
Starting Member
Starting Member
 
Posts: 1
Joined: May 2013

Re: Database design

Postby Romac » Thu Mar 13, 2014 8:35 pm

Bringing up this topic as I have been working on a historical database for my energy reading. I'm using Homeseer 3 to load the changed values to a database table in SQL Server and used the approach outlined in this topic. I have for each hour a record, starting at the beginning of the hour and updating until the next hour starts. This works well and I can find all readings in the database. I'm storing at the moment just the smart meter readings every 10s when the reading comes in. I'm planning in optimising this when everything is working well.

The issue I see is that I'm missing data when the hour changes. I liked to idea to have the start and end value for every hour, but when changing to the next hour I loose one interval. I can solve this by calculating the difference between the end valvues, but then loose the advantage of the start and end value every hour.

How have others solved this? What am I missing?

2014-03-13 07.57.21 pm.png
2014-03-13 07.57.21 pm.png (20.13 KiB) Viewed 6355 times
HS3, Z-Wave Network, CommandFusion GUI
Romac
Starting Member
Starting Member
 
Posts: 47
Joined: May 2010
Location: Utrecht, Netherlands

Re: Database design

Postby Digit » Thu Mar 13, 2014 9:57 pm

Since you got this far already, I hope that showing how I construct my queries with some (JS) code will suffice.
I changed some things so that fieldnames are like yours etc. but left out the LastUpdate field.

The newMStimestamp variable is always filled with values like:
"20140313 19:00:00.000"
"20140313 20:00:00.000"
"20140313 21:00:00.000"
etc., since I store them each hour and minutes, sec and msec don't matter so I set those to 0.


Code: Select all
      // MS SQL query
     
      sqlw = "DEVID = '"+deviceid.toUpperCase()+"' AND TimeStamp = CAST('"+newMStimestamp+"' AS DATETIME)";     
      q = "";
      q += "IF NOT EXISTS (SELECT * FROM DATA WHERE "+sqlw+") ";
      q += "INSERT INTO DATA (TimeStamp, DevID, StartValue, EndValue) ";
      q += "VALUES ('"+newMStimestamp+"'";
      q += ", '"+deviceid.toUpperCase()+"'";
      q += ","+prvValue.toString();
      q += ","+curValue.toString();
      q += ")";
     
      msqueryQueue.push(q);

      q = "";
      q += "UPDATE DATA SET EndValue = "+curValue.toString();
      q += "' WHERE "+sqlw;
      msqueryQueue.push(q);


The INSERT will only happen once every hour and the trick is to keep track of the previous value (prvValue), the value you got 10 seconds ago.
This is the end result in my MS SQL Server:

dbdKnipsel.PNG
dbdKnipsel.PNG (16.36 KiB) Viewed 6349 times
User avatar
Digit
Global Moderator
Global Moderator
 
Posts: 3388
Joined: March 2006
Location: Netherlands

Re: Database design

Postby mlommers » Thu Mar 13, 2014 10:06 pm

isn't it redundant to have for each hour the start and end value's? As the start value is equal to the end value of the previous hour?
mlommers
Starting Member
Starting Member
 
Posts: 12
Joined: July 2010

Re: Database design

Postby Romac » Thu Mar 13, 2014 10:21 pm

Thanks Digit. This is much appreciated. I will have a look at your query and try to implement in my system. The query is definitely different and more complex then what I am running now.
HS3, Z-Wave Network, CommandFusion GUI
Romac
Starting Member
Starting Member
 
Posts: 47
Joined: May 2010
Location: Utrecht, Netherlands

Re: Database design

Postby Digit » Thu Mar 13, 2014 10:55 pm

mlommers wrote:isn't it redundant


Doing things this way is a bit of a trade-off, it's not necessary to store both values, that's correct.
But for example, a simple query result is instantly column chart ready.

And in fact it's not really redundant - that's when data is repeated in 2 or more tables (like copying a customers' VAT number to the sales order table) which is not the case here. In this case, they do have a different meaning. :wink:
User avatar
Digit
Global Moderator
Global Moderator
 
Posts: 3388
Joined: March 2006
Location: Netherlands

Re: Database design

Postby Bwired » Fri Mar 14, 2014 10:18 am

vanisher wrote:Thx!! Will look into it and trying to get in in mySQL.

this is the Mysql variant, i have much more fields (yes redundant i like), but discard that
the on duplicate is handy for mysql. discard also some nodejs lines like pushing the query
check also http://blog.bwired.nl/?p=1049

Code: Select all
var q = '';
    var s = (new Date()).toMySQLDateFormat() //+ '/'+ s;   
    var d = new Date();
    var n = d.getHours();
      q = "";
      q += "INSERT INTO smartmeter1 (datum, tijd, daguur, aantal, hour_consumed, hour_produced, consumed_rate1, consumed_rate2, produced_rate1, produced_rate2, actual_consumed, actual_produced, gas) ";
      q += "VALUES ('"+s+"'";
      q += ", '"+s+"'";
      q += ", '"+n.toString()+"'";
      q += ", '1'";
      q += ", '0'";
      q += ", '0'";
      q += ", '"+res.ConsumedPowerRate1+"'";
      q += ", '"+res.ConsumedPowerRate2+"'";
      q += ", '"+res.ProducedPowerRate1+"'";
      q += ", '"+res.ProducedPowerRate2+"'";
      q += ", '"+res.ActualConsumedPower+"'";
      q += ", '"+res.ActualProducedPower+"'";
      q += ", "+res.ConsumedGas;     
          q += ")";
          q += " ON DUPLICATE KEY UPDATE"
          q += " tijd='"+s+"',";
      q += " hour_consumed=hour_consumed+('"+res.ConsumedPowerRate1+"'-consumed_rate1)+('"+res.ConsumedPowerRate2+"'-consumed_rate2),";
      q += " hour_produced=hour_produced+('"+res.ProducedPowerRate1+"'-produced_rate1)+('"+res.ProducedPowerRate2+"'-produced_rate2),";
      q += " consumed_rate1='"+res.ConsumedPowerRate1+"',";
      q += " consumed_rate2='"+res.ConsumedPowerRate2+"',";
      q += " produced_rate1='"+res.ProducedPowerRate1+"',";
      q += " produced_rate2='"+res.ProducedPowerRate2+"',";
      q += " actual_consumed='"+res.ActualConsumedPower+"',";
      q += " actual_produced='"+res.ActualProducedPower+"',";
      q += " gas='"+res.ConsumedGas+"',";
          q += " aantal=aantal+1;";
     
    queryQueue.push(q);
    processQueryQueue();
User avatar
Bwired
Administrator
Administrator
 
Posts: 5304
Joined: March 2006
Location: Netherlands

Re: Database design

Postby Romac » Sun Mar 16, 2014 8:43 am

It working!! :) I ended up doing it differently but the solutions posted here pointed me in the right direction.

For anybody struggling to get this to work, this is what I did (using HS3).

I run the below script every time a device value changes.
Code: Select all
'Log to Database for HS3
'WR. Muller
'08/03/2014
'Version: 1.0

'VB.Net script to write Homeseer 3 values to SQL Server Express Historical Database

'Add this line to Homeseer\Config\Settings.ini
'    ScriptingReferences=System.Data;System.Data.Dll

' Add this line to Homeseer\Scipts\Startup.ini
'    hs.RegisterStatusChangeCB("L2DH.vb","Main")

Imports System.Data.SqlClient

Sub Main(ByVal Parms As Object)

    Dim dev_address As String
    Dim device_valuenew, device_valueprv As Double
    Dim cn As New SqlConnection("Server=192.168.2.25,1433;Database=HS3Test;User ID=sa;Password=rm7800;Trusted_Connection=False;")

    hs.WriteLog("L2DH", "Script running")
    dev_address = Parms(1) 'address of device
    device_valuenew = Parms(2) 'new value of device
    device_valueprv = Parms(3) 'previous value of device

    Try
        cn.Open()
        hs.WriteLog("L2DB", "Connection is open")
        Dim SQLComm As New SqlCommand("UPDATE dbo.Data SET EndValue='" & device_valuenew & "' WHERE DevID='" & dev_address & "' AND DateDiff(hh, GetDate(),TimeStamp) = 0" & _
                                      "IF @@ROWCOUNT=0" & _
                                      "INSERT INTO dbo.Data(DevID, StartValue, EndValue) VALUES('" & dev_address & "','" & device_valueprv & "','" & device_valuenew & "')", cn)
        SQLComm.ExecuteNonQuery()
        hs.WriteLog("L2DH", "Logged to Historical Database: Address: " & dev_address & ", New Value: " & device_valuenew)
        cn.Close()
        hs.WriteLog("L2DH", "Connection is closed")
        'End If

    Catch ex As Exception
        hs.WriteLogEx("L2DH", "Error: " & ex.Message)
    End Try

End Sub


The database is set-up in SQLServer. The structure is like this:
Code: Select all
USE [HS3Test]
GO

/****** Object:  Table [dbo].[Data]    Script Date: 16-3-2014 8:19:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Data](
   [TimeStamp] [smalldatetime] NOT NULL,
   [LastUpdate] [datetime] NULL,
   [DevID] [nchar](15) NOT NULL,
   [StartValue] [float] NULL,
   [EndValue] [float] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Data] ADD  CONSTRAINT [DF_Data_timestamp]  DEFAULT (dateadd(hour,datediff(hour,(0),getdate()),(0))) FOR [TimeStamp]
GO


The constraint at the end is used to truncate the Timestamp value to a full hour. The way I have the INSERT statement written in the script, it always adds a new record when a new hour has started, by just deleting the minutes and seconds, you end up with the hour value.

The data looks like this when everything is working well.
2014-03-16 08.19.29 am.png
2014-03-16 08.19.29 am.png (24.65 KiB) Viewed 6224 times


There are still a few things I need to do:
1. Make the logging device specific; now every device value change is logged
2. Making interval a device value is logged device specific. Now everything is logged at an interval of an hour which is fine for energy, but other devices may require something different.
HS3, Z-Wave Network, CommandFusion GUI
Romac
Starting Member
Starting Member
 
Posts: 47
Joined: May 2010
Location: Utrecht, Netherlands

Re: Database design

Postby Romac » Sun Feb 07, 2016 9:15 pm

Finally back on this topic and trying to make it more visable. Having now lots of data, I'm trying to make some nice graphs. For this I'm using Highcharts which is working quite well for data I can read directly from the database. I just struggle to setup a query to calculate the delta per day or per week. Can somebody help me in the right direction to get me going?
HS3, Z-Wave Network, CommandFusion GUI
Romac
Starting Member
Starting Member
 
Posts: 47
Joined: May 2010
Location: Utrecht, Netherlands

Re: Database design

Postby Rene » Sun Feb 07, 2016 9:18 pm

Have a look at influxdb.
Rene.
User avatar
Rene
Global Moderator
Global Moderator
 
Posts: 1689
Joined: October 2008
Location: Netherlands

Re: Database design

Postby Romac » Sun Feb 07, 2016 11:12 pm

Thanks Rene. At the moment all my data is in SQL Server and would like to stay with this solution. Just looking for a SQL query to group the data by day/week/month/year...


Sent from my iPhone using Tapatalk
HS3, Z-Wave Network, CommandFusion GUI
Romac
Starting Member
Starting Member
 
Posts: 47
Joined: May 2010
Location: Utrecht, Netherlands

PreviousNext

Return to Questions & Discussions Forum

Who is online

Users browsing this forum: No registered users and 1 guest