MiOS - User contributions [en]

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-27T01:59:23Z

Mysticjay:

This is most likely not an elegant way to dothis, but in case someone wants something similar feel free to use this. Please suggest if there are better ways of accomplishing this and if you happen to know so, update this page :)

1. This will give you a single xml file you could load in .NET through Dataset.Readxml and you will get about 48tables.
 
 
2. Nested tables like Devices have states and ControlURLs - these become a separate table with a new attribute added to refer what Device ID a row in these tables would relate to the devices table.
 
 
[[Image:Parse.jpg]]

 
Using the functions below, a small .NET web app that shows Rooms/Scenes/Devices (Code for that below) - Also see the forum post:

http://forum.micasaverde.com/index.php?topic=7950.0 [http://forum.micasaverde.com/index.php?topic=7950.0]

Public Class _Default
Inherits System.Web.UI.Page

Dim UserDataset As DataSet
Dim ud As UserData
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
If Session("UserData") Is Nothing Then
ud = New UserData
Session("UserData") = ud.Parse_UserData_To_DataSet("d:\test")
End If
End If
UserDataset = Session("UserData")
GetData()
End Sub

Sub GetData()
Dim RoomsTable As DataTable = UserDataset.Tables("rooms")
Me.RoomsGrid.DataSource = RoomsTable
Me.RoomsGrid.DataBind()
RoomsGrid.ShowHeader = False
Dim ScenesTable As DataTable = UserDataset.Tables("scenes")
Me.ScenesGrid.DataSource = ScenesTable
Me.ScenesGrid.DataBind()
ScenesGrid.ShowHeader = False
Dim DevicesTable As DataTable = UserDataset.Tables("devices")
Dim drRemove As DataRow() = DevicesTable.Select("invisible=1")
For Each dr As DataRow In drRemove
DevicesTable.Rows.Remove(dr)
Next
Me.DevicesGrid.DataSource = DevicesTable
Me.DevicesGrid.DataBind()
DevicesGrid.ShowHeader = False
End Sub

End Class

 
So, This is more like it!!!
 

Dim masterDs As New DataSet

Sub Parse_UserData_To_DataSet(ByVal OutputFolder As String, _
Optional ByVal veraURL As String = "http://myhome:49451")
'Parse XML and create XML files to load in to dataset directly
Dim settings As New XmlReaderSettings()
settings.DtdProcessing = DtdProcessing.Parse
Dim reader As XmlReader = XmlReader.Create(veraURL & "/data_request?output_format=xml&id=user_data", settings)
Dim doc As New XmlDocument()
doc.Load(reader)
Dim root As XmlNode = doc.DocumentElement
Dim ParseNodeEnum As IEnumerator = root.GetEnumerator
While (ParseNodeEnum.MoveNext)
Dim NodeRow As XmlNode = ParseNodeEnum.Current
ParseElements(NodeRow, Nothing)
End While
End Sub

Sub ParseElements(ByVal ParseNode As XmlNode, ByVal ParentRow As DataRow)
Dim dt As DataTable = CreateTable(ParseNode, ParentRow)
Dim ParseNodeEnum As IEnumerator = ParseNode.GetEnumerator
While (ParseNodeEnum.MoveNext)
Dim NodeRow As XmlNode = ParseNodeEnum.Current
Dim dr As DataRow = dt.NewRow
'Add Attributes as columns to Datatable
Dim ienum As IEnumerator = NodeRow.Attributes.GetEnumerator()
While ienum.MoveNext
Dim atr As XmlAttribute = ienum.Current
If dt.Columns.Contains(atr.Name) = False Then dt.Columns.Add(atr.Name)
dr(atr.Name) = atr.Value
End While
If Not IsNothing(ParentRow) Then
Dim ParentColumn As String = ParentRow.Table.TableName & "_OurID"
If dt.Columns.Contains(ParentColumn) Then dr(ParentColumn) = ParentRow(ParentColumn)
End If
dt.Rows.Add(dr)
For Each x As XmlNode In NodeRow.ChildNodes
ParseElements(x, dr)
Next
End While
If Not masterDs.Tables.Contains(ParseNode.Name) Then masterDs.Tables.Add(dt)
End Sub

Function CreateTable(ByVal Node As XmlNode, ByVal ParentRow As DataRow) As DataTable
Dim dt As DataTable
If masterDs.Tables.Contains(Node.Name) Then
dt = masterDs.Tables(Node.Name)
Return dt
End If
dt = New DataTable(Node.Name)
'Add primary key col
Dim KeyColumn As String = Node.Name & "_OurID"
dt.Columns.Add(New DataColumn(KeyColumn, GetType(System.Int32)))
dt.Columns(0).AutoIncrement = True
dt.Constraints.Add(dt.TableName & "PrimaryID", dt.Columns(0), True)
'Add parent ref column
If Not IsNothing(ParentRow) Then
Dim ParentColumn As String = ParentRow.Table.TableName & "_OurID"
dt.Columns.Add(New DataColumn(ParentColumn, GetType(System.Int32)))
End If
Return dt
End Function

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-27T01:58:44Z

Mysticjay:

This is most likely not an elegant way to dothis, but in case someone wants something similar feel free to use this. Please suggest if there are better ways of accomplishing this and if you happen to know so, update this page :)

1. This will give you a single xml file you could load in .NET through Dataset.Readxml and you will get about 48tables.
 
 
2. Nested tables like Devices have states and ControlURLs - these become a separate table with a new attribute added to refer what Device ID a row in these tables would relate to the devices table.
 
 
[[Image:Parse.jpg]]

 
Using the functions below, a small .NET web app that shows Rooms/Scenes/Devices (Code for that below) - Also see the forum post:

[http://forum.micasaverde.com/index.php?topic=7950.0]

Public Class _Default
Inherits System.Web.UI.Page

Dim UserDataset As DataSet
Dim ud As UserData
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
If Session("UserData") Is Nothing Then
ud = New UserData
Session("UserData") = ud.Parse_UserData_To_DataSet("d:\test")
End If
End If
UserDataset = Session("UserData")
GetData()
End Sub

Sub GetData()
Dim RoomsTable As DataTable = UserDataset.Tables("rooms")
Me.RoomsGrid.DataSource = RoomsTable
Me.RoomsGrid.DataBind()
RoomsGrid.ShowHeader = False
Dim ScenesTable As DataTable = UserDataset.Tables("scenes")
Me.ScenesGrid.DataSource = ScenesTable
Me.ScenesGrid.DataBind()
ScenesGrid.ShowHeader = False
Dim DevicesTable As DataTable = UserDataset.Tables("devices")
Dim drRemove As DataRow() = DevicesTable.Select("invisible=1")
For Each dr As DataRow In drRemove
DevicesTable.Rows.Remove(dr)
Next
Me.DevicesGrid.DataSource = DevicesTable
Me.DevicesGrid.DataBind()
DevicesGrid.ShowHeader = False
End Sub

End Class

 
So, This is more like it!!!
 

Dim masterDs As New DataSet

Sub Parse_UserData_To_DataSet(ByVal OutputFolder As String, _
Optional ByVal veraURL As String = "http://myhome:49451")
'Parse XML and create XML files to load in to dataset directly
Dim settings As New XmlReaderSettings()
settings.DtdProcessing = DtdProcessing.Parse
Dim reader As XmlReader = XmlReader.Create(veraURL & "/data_request?output_format=xml&id=user_data", settings)
Dim doc As New XmlDocument()
doc.Load(reader)
Dim root As XmlNode = doc.DocumentElement
Dim ParseNodeEnum As IEnumerator = root.GetEnumerator
While (ParseNodeEnum.MoveNext)
Dim NodeRow As XmlNode = ParseNodeEnum.Current
ParseElements(NodeRow, Nothing)
End While
End Sub

Sub ParseElements(ByVal ParseNode As XmlNode, ByVal ParentRow As DataRow)
Dim dt As DataTable = CreateTable(ParseNode, ParentRow)
Dim ParseNodeEnum As IEnumerator = ParseNode.GetEnumerator
While (ParseNodeEnum.MoveNext)
Dim NodeRow As XmlNode = ParseNodeEnum.Current
Dim dr As DataRow = dt.NewRow
'Add Attributes as columns to Datatable
Dim ienum As IEnumerator = NodeRow.Attributes.GetEnumerator()
While ienum.MoveNext
Dim atr As XmlAttribute = ienum.Current
If dt.Columns.Contains(atr.Name) = False Then dt.Columns.Add(atr.Name)
dr(atr.Name) = atr.Value
End While
If Not IsNothing(ParentRow) Then
Dim ParentColumn As String = ParentRow.Table.TableName & "_OurID"
If dt.Columns.Contains(ParentColumn) Then dr(ParentColumn) = ParentRow(ParentColumn)
End If
dt.Rows.Add(dr)
For Each x As XmlNode In NodeRow.ChildNodes
ParseElements(x, dr)
Next
End While
If Not masterDs.Tables.Contains(ParseNode.Name) Then masterDs.Tables.Add(dt)
End Sub

Function CreateTable(ByVal Node As XmlNode, ByVal ParentRow As DataRow) As DataTable
Dim dt As DataTable
If masterDs.Tables.Contains(Node.Name) Then
dt = masterDs.Tables(Node.Name)
Return dt
End If
dt = New DataTable(Node.Name)
'Add primary key col
Dim KeyColumn As String = Node.Name & "_OurID"
dt.Columns.Add(New DataColumn(KeyColumn, GetType(System.Int32)))
dt.Columns(0).AutoIncrement = True
dt.Constraints.Add(dt.TableName & "PrimaryID", dt.Columns(0), True)
'Add parent ref column
If Not IsNothing(ParentRow) Then
Dim ParentColumn As String = ParentRow.Table.TableName & "_OurID"
dt.Columns.Add(New DataColumn(ParentColumn, GetType(System.Int32)))
End If
Return dt
End Function

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-25T18:49:09Z

Mysticjay:

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-25T18:48:40Z

Mysticjay:

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-25T17:58:13Z

Mysticjay:

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-25T14:49:06Z

Mysticjay:

This is most likely not an elegant way to dothis, but in case someone wants something similar feel free to use this. Please suggest if there are better ways of accomplishing this and if you happen to know so, update this page :)

1. This will give you a single xml file you could load in .NET through Dataset.Readxml and you will get about 14 tables.
 
 
2. Devices have states and ControlURLs , these become a separate table with a new attribute added to refer what Device ID a row in these tables would relate to the devices table.
 
 
[[Image:Parse.jpg]]

 

Sub Parse_UserData_To_DataSet(ByVal OutputFolder As String, _
Optional ByVal veraURL As String = "http://myhome:49451")

'Parse XML and create XML files to load in to dataset directly
Dim ControlURL As New StringBuilder("<ControlURLs>" & vbCrLf)
Dim States As New StringBuilder("<states>" & vbCrLf)
Dim Entries As New ArrayList

Dim settings As New XmlReaderSettings()
settings.DtdProcessing = DtdProcessing.Parse
Dim reader As XmlReader = XmlReader.Create(veraURL & "/data_request?output_format=xml&id=user_data", settings)
Dim doc As New XmlDocument()
doc.Load(reader)
Dim root As XmlNode = doc.DocumentElement
Dim fout As New StreamWriter(Path.Combine(OutputFolder, root.Name & ".xml"))
Dim ienumroot As IEnumerator = root.Attributes.GetEnumerator()
fout.Write("<root ")
While ienumroot.MoveNext
Dim atr As XmlAttribute = ienumroot.Current
fout.Write(atr.Name & "=" & """" & atr.Value & """ ")
End While
fout.Write("></root>" & vbCrLf)
fout.Close()
Entries.Add(Path.Combine(OutputFolder, root.Name & ".xml"))

Dim ienum As IEnumerator = root.GetEnumerator()
Dim ParseNode As XmlNode
While (ienum.MoveNext())
ParseNode = CType(ienum.Current, XmlNode)
fout = New StreamWriter(Path.Combine(OutputFolder, ParseNode.Name & ".xml"))
fout.Write(ParseNode.OuterXml)
fout.Close()
Entries.Add(Path.Combine(OutputFolder, ParseNode.Name & ".xml"))
If ParseNode.Name <> "devices" Then Continue While

'Get States / ControlURLS also as seperate table
Dim ServiceID As Integer = 1
Dim DeviceDetails As XmlNode
Dim devenum As IEnumerator = ParseNode.GetEnumerator
'-------Devices Loop
While (devenum.MoveNext)
DeviceDetails = CType(devenum.Current, XmlNode)
Dim DeviceID As String = DeviceDetails.Attributes.GetNamedItem("id").InnerText

Dim devChildNode As XmlNode
Dim devChildNodeInner As XmlNode
Dim childEnum As IEnumerator = DeviceDetails.GetEnumerator
While childEnum.MoveNext
devChildNode = CType(childEnum.Current, XmlNode)
Dim childEnumInner As IEnumerator = devChildNode.GetEnumerator
While childEnumInner.MoveNext
devChildNodeInner = childEnumInner.Current

Dim DeviceIDAttr As XmlAttribute = doc.CreateAttribute("DeviceID")
DeviceIDAttr.InnerText = DeviceID
devChildNodeInner.Attributes.Append(DeviceIDAttr)

If devChildNode.OuterXml.StartsWith("<state") Then
States.Append(devChildNodeInner.OuterXml & vbCrLf)
Else
Dim serviceIDstr As String = "<service_" & ServiceID
Dim strtoWrite As String = devChildNodeInner.OuterXml.Replace(serviceIDstr, "<service ID=""" & ServiceID & """ ")
strtoWrite = strtoWrite.Replace("</service_" & ServiceID & ">", "</service>")
ControlURL.Append(strtoWrite & vbCrLf)
ServiceID += 1
End If
End While
End While
End While
'------Devices Loop

End While

States.Append("</states>" & vbCrLf)
fout = New StreamWriter(Path.Combine(OutputFolder, "states.xml"))
fout.Write(States.ToString)
fout.Close()

ControlURL.Append("</ControlURLs>" & vbCrLf)
fout = New StreamWriter(Path.Combine(OutputFolder, "ControlURL.xml"))
fout.Write(ControlURL.ToString)
fout.Close()
Entries.Add(Path.Combine(OutputFolder, "states.xml"))
Entries.Add(Path.Combine(OutputFolder, "ControlURL.xml"))
'Parsing Done

'Create a Single XML file so it can be loaded in to dataset with Dataset.ReadXML
Dim finalDS As New DataSet
For Each fname As String In Entries

Dim ds As New DataSet
ds.ReadXml(fname)
Try
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = ds.Tables(0).TableName
Catch ex As Exception
End Try
Next
finalDS.WriteXml(Path.Combine(OutputFolder, "UserData.xml"))

End Sub

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-25T14:10:15Z

Mysticjay:

This is most likely not an elegant way to dothis, but in case someone wants something similar feel free to use this. Please suggest if there are better ways of accomplishing this and if you happen to know so, update this page :)

1. This will give you a single xml file you could load in .NET through Dataset.Readxml and you will get about 14 tables.
 
 
2. Devices have states and ControlURLs , these become a separate table with a new attribute added to refer what Device ID a row in these tables would relate to the devices table.
 
 
[[Image:Parse.jpg]]

 

Sub Parse_UserData_To_DataSet(ByVal OutputFolder As String, _
Optional ByVal veraURL As String = "http://myhome:49451")

'Parse XML and create XML files to load in to dataset directly
Dim ControlURL As New StringBuilder("<ControlURLs>" & vbCrLf)
Dim States As New StringBuilder("<states>" & vbCrLf)
Dim Entries As New ArrayList

Dim settings As New XmlReaderSettings()
settings.DtdProcessing = DtdProcessing.Parse
Dim reader As XmlReader = XmlReader.Create(veraURL & "/data_request?output_format=xml&id=user_data", settings)
Dim doc As New XmlDocument()
doc.Load(reader)
Dim root As XmlNode = doc.DocumentElement
Dim fout As New StreamWriter(Path.Combine(OutputFolder, root.Name & ".xml"))
Dim ienumroot As IEnumerator = root.Attributes.GetEnumerator()
fout.Write("<root ")
While ienumroot.MoveNext
Dim atr As XmlAttribute = ienumroot.Current
fout.Write(atr.Name & "=" & """" & atr.Value & """ ")
End While
fout.Write("></root>" & vbCrLf)
fout.Close()
Entries.Add(Path.Combine(OutputFolder, root.Name & ".xml"))

Dim ienum As IEnumerator = root.GetEnumerator()
Dim ParseNode As XmlNode
While (ienum.MoveNext())
ParseNode = CType(ienum.Current, XmlNode)
fout = New StreamWriter(Path.Combine(OutputFolder, ParseNode.Name & ".xml"))
fout.Write(ParseNode.OuterXml)
fout.Close()
Entries.Add(Path.Combine(OutputFolder, ParseNode.Name & ".xml"))
If ParseNode.Name <> "devices" Then Continue While

'Get States / ControlURLS also as seperate table
Dim ServiceID As Integer = 1
Dim DeviceDetails As XmlNode
Dim devenum As IEnumerator = ParseNode.GetEnumerator
'-------Devices Loop
While (devenum.MoveNext)
DeviceDetails = CType(devenum.Current, XmlNode)
Dim DeviceID As String = DeviceDetails.Attributes.GetNamedItem("id").InnerText

Dim devChildNode As XmlNode
Dim childEnum As IEnumerator = DeviceDetails.GetEnumerator
While childEnum.MoveNext
devChildNode = CType(childEnum.Current, XmlNode)
Dim DeviceIDAttr As XmlAttribute = doc.CreateAttribute("DeviceID")
DeviceIDAttr.InnerText = DeviceID
devChildNode.Attributes.Append(DeviceIDAttr)
If devChildNode.OuterXml.StartsWith("<state") Then
States.Append(devChildNode.OuterXml & vbCrLf)
Else
Dim serviceIDstr As String = "<service_" & ServiceID
Dim strtoWrite As String = devChildNode.OuterXml.Replace(serviceIDstr, "<service ID=""" & ServiceID & """ ")
strtoWrite = strtoWrite.Replace("</service_" & ServiceID & ">", "</service>")
ControlURL.Append(strtoWrite & vbCrLf)
ServiceID += 1
End If
End While

End While
'------Devices Loop

End While

States.Append("</states>" & vbCrLf)
fout = New StreamWriter(Path.Combine(OutputFolder, "states.xml"))
fout.Write(States.ToString)
fout.Close()

ControlURL.Append("</ControlURLs>" & vbCrLf)
fout = New StreamWriter(Path.Combine(OutputFolder, "ControlURL.xml"))
fout.Write(ControlURL.ToString)
fout.Close()
Entries.Add(Path.Combine(OutputFolder, "states.xml"))
Entries.Add(Path.Combine(OutputFolder, "ControlURL.xml"))
'Parsing Done

'Create a Single XML file so it can be loaded in to dataset with Dataset.ReadXML
Dim finalDS As New DataSet
For Each fname As String In Entries

Dim ds As New DataSet
ds.ReadXml(fname)
Try
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = ds.Tables(0).TableName
Catch ex As Exception
End Try
Next
finalDS.WriteXml(Path.Combine(OutputFolder, "UserData.xml"))

End Sub

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-25T13:11:30Z

Mysticjay:

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-24T18:53:48Z

Mysticjay:

This is most likely not an elegant way to dothis, but in case someone wants something similar feel free to use this. Please suggest if there are better ways of accomplishing this and if you happen to know so, update this page :)

1. This will give you a single xml file you could load in .NET through Dataset.Readxml and you will get about 14 tables.
 
 
2. Devices have states and ControlURLs , these become a separate table with a new attribute added to refer what Device ID a row in these tables would relate to the devices table.
 
 
[[Image:Parse.jpg]]

 

Note: For the JSONObject used here: 'Credits: http://www.vbforums.com/showthread.php?t=645037
----

Private Sub Parse_UserData_To_DataSet(ByVal OutputFolder As String, _
Optional ByVal veraURL As String = "http://myhome:49451")

Dim Entries As New ArrayList
Dim ds As New DataSet
Dim userDatajson As String = GetWebPageContent(veraURL & "/data_request?output_format=json&id=user_data")
Dim userData As String = GetWebPageContent(veraURL & "/data_request?output_format=xml&id=user_data")

'Create Root Data file
Dim si As Integer = userData.IndexOf("<root")
Dim ei As Integer = userData.IndexOf(">", si)
Dim rootstr As String = userData.Substring(si, ei - si + 1)
rootstr = rootstr.Replace(vbLf, "") & "</root>"
Dim fo As New IO.StreamWriter(OutputFolder & "\root.xml")
fo.WriteLine(rootstr)
fo.Close()

Entries.Add("root")
'create rest of it!
Dim jo As New JSONObject(userDatajson)
For Each s As KeyValuePair(Of String, String) In jo._properties
si = userData.IndexOf("<" & s.Key & ">")
ei = userData.IndexOf("</" & s.Key & ">")
If si >= 0 AndAlso ei > 0 Then
Dim thisEntry As String = userData.Substring(si, ei - si + ("</" & s.Key & ">").Length)
Dim fname As String = OutputFolder & "\" & s.Key & ".xml"
Dim fo1 As New IO.StreamWriter(fname)
fo1.WriteLine(thisEntry)
fo1.Close()
Entries.Add(s.Key)
End If
Next

'Put all the tables in a single dataset
Dim finalDS As New DataSet
For Each s As String In Entries
Dim fname As String = OutputFolder & "\" & s & ".xml"
ds = New DataSet
ds.ReadXml(fname)
Try
ds.Tables(0).TableName = s
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = s
Catch ex As Exception
End Try

Next

'devices have controlURL & states
DeviceParse(OutputFolder)

ds = New DataSet
ds.ReadXml(OutputFolder & "\states.xml")
Try
ds.Tables(0).TableName = "states"
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = "states"
Catch ex As Exception
End Try
ds = New DataSet
ds.ReadXml(OutputFolder & "\ControlURLs.xml")
Try
ds.Tables(0).TableName = "ControlURLs"
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = "ControlURLs"
Catch ex As Exception
MsgBox(ex.Message)
End Try

finalDS.WriteXml(OutputFolder & "\UserData.xml")

End Sub

Sub DeviceParse(ByVal OutputFolder As String)
'ControlURLs
'states
Dim filename As String = OutputFolder & "\devices.xml"
Dim ControlURL As String = "<ControlURLs>" & vbCrLf
Dim States As String = "<states>" & vbCrLf

Dim ServiceID As Integer = 1

Dim settings As New XmlReaderSettings()
settings.DtdProcessing = DtdProcessing.Parse

Dim reader As XmlReader = XmlReader.Create(filename, settings)
Dim doc As New XmlDocument()
doc.Load(reader)
Dim root As XmlNode = doc.DocumentElement
Dim ienum As IEnumerator = root.GetEnumerator()
Dim device As XmlNode
While (ienum.MoveNext())
device = CType(ienum.Current, XmlNode)
Dim DeviceID As String = device.Attributes.GetNamedItem("id").InnerText

Dim DeviceDetails As XmlNode
Dim devenum As IEnumerator = device.GetEnumerator
While (devenum.MoveNext)
DeviceDetails = CType(devenum.Current, XmlNode)

Dim devChildNode As XmlNode
Dim childEnum As IEnumerator = DeviceDetails.GetEnumerator
While childEnum.MoveNext
devChildNode = CType(childEnum.Current, XmlNode)
Dim DeviceIDAttr As XmlAttribute = doc.CreateAttribute("DeviceID")
DeviceIDAttr.InnerText = DeviceID
devChildNode.Attributes.Append(DeviceIDAttr)
If devChildNode.OuterXml.StartsWith("<state") Then
States &= devChildNode.OuterXml & vbCrLf
Else
Dim serviceIDstr As String = "<service_" & ServiceID
Dim strtoWrite As String = devChildNode.OuterXml.Replace(serviceIDstr, "<service ID=""" & ServiceID & """ ")
strtoWrite = strtoWrite.Replace("</service_" & ServiceID & ">", "</service>")
ControlURL &= strtoWrite & vbCrLf
ServiceID += 1
End If
End While

End While
End While

States &= "</states>"
ControlURL &= "</ControlURLs>"

Dim fout As New IO.StreamWriter(OutputFolder & "\states.xml")
fout.Write(States)
fout.Close()
fout = New IO.StreamWriter(OutputFolder & "\controlurls.xml")
fout.Write(ControlURL)
fout.Close()

End Sub

Private Function GetWebPageContent(ByVal strURL As String) As String
Dim retVal As String = ""
Try
Dim srRead As System.IO.StreamReader = New System.IO.StreamReader(System.Net.WebRequest.Create(strURL).GetResponse.GetResponseStream)
retVal = srRead.ReadToEnd
Catch ex As Exception
retVal = "Hmm.."
End Try
Return retVal
End Function

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-24T18:51:57Z

Mysticjay:

This is most likely not an elegant way to dothis, but in case someone wants something similar feel free to use this. Please suggest if there are better ways of accomplishing this and if you happen to know so, update this page :)

1. This will give you a single xml file you could load in .NET through Dataset.Readxml and you will get about 14 tables.
 
 
2. Devices have states and ControlURLs , these become a separate table with a new attribute added to refer what Device ID a row in these tables would relate to the devices table.
 
 
[[Image:Parse.jpg]]

 

----

Private Sub Parse_UserData_To_DataSet(ByVal OutputFolder As String, _
Optional ByVal veraURL As String = "http://myhome:49451")

Dim Entries As New ArrayList
Dim ds As New DataSet
Dim userDatajson As String = GetWebPageContent(veraURL & "/data_request?output_format=json&id=user_data")
Dim userData As String = GetWebPageContent(veraURL & "/data_request?output_format=xml&id=user_data")

'Create Root Data file
Dim si As Integer = userData.IndexOf("<root")
Dim ei As Integer = userData.IndexOf(">", si)
Dim rootstr As String = userData.Substring(si, ei - si + 1)
rootstr = rootstr.Replace(vbLf, "") & "</root>"
Dim fo As New IO.StreamWriter(OutputFolder & "\root.xml")
fo.WriteLine(rootstr)
fo.Close()

Entries.Add("root")
'create rest of it!
Dim jo As New JSONObject(userDatajson)
For Each s As KeyValuePair(Of String, String) In jo._properties
si = userData.IndexOf("<" & s.Key & ">")
ei = userData.IndexOf("</" & s.Key & ">")
If si >= 0 AndAlso ei > 0 Then
Dim thisEntry As String = userData.Substring(si, ei - si + ("</" & s.Key & ">").Length)
Dim fname As String = OutputFolder & "\" & s.Key & ".xml"
Dim fo1 As New IO.StreamWriter(fname)
fo1.WriteLine(thisEntry)
fo1.Close()
Entries.Add(s.Key)
End If
Next

'Put all the tables in a single dataset
Dim finalDS As New DataSet
For Each s As String In Entries
Dim fname As String = OutputFolder & "\" & s & ".xml"
ds = New DataSet
ds.ReadXml(fname)
Try
ds.Tables(0).TableName = s
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = s
Catch ex As Exception
End Try

Next

'devices have controlURL & states
DeviceParse(OutputFolder)

ds = New DataSet
ds.ReadXml(OutputFolder & "\states.xml")
Try
ds.Tables(0).TableName = "states"
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = "states"
Catch ex As Exception
End Try
ds = New DataSet
ds.ReadXml(OutputFolder & "\ControlURLs.xml")
Try
ds.Tables(0).TableName = "ControlURLs"
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = "ControlURLs"
Catch ex As Exception
MsgBox(ex.Message)
End Try

finalDS.WriteXml(OutputFolder & "\UserData.xml")

End Sub

Sub DeviceParse(ByVal OutputFolder As String)
'ControlURLs
'states
Dim filename As String = OutputFolder & "\devices.xml"
Dim ControlURL As String = "<ControlURLs>" & vbCrLf
Dim States As String = "<states>" & vbCrLf

Dim ServiceID As Integer = 1

Dim settings As New XmlReaderSettings()
settings.DtdProcessing = DtdProcessing.Parse

Dim reader As XmlReader = XmlReader.Create(filename, settings)
Dim doc As New XmlDocument()
doc.Load(reader)
Dim root As XmlNode = doc.DocumentElement
Dim ienum As IEnumerator = root.GetEnumerator()
Dim device As XmlNode
While (ienum.MoveNext())
device = CType(ienum.Current, XmlNode)
Dim DeviceID As String = device.Attributes.GetNamedItem("id").InnerText

Dim DeviceDetails As XmlNode
Dim devenum As IEnumerator = device.GetEnumerator
While (devenum.MoveNext)
DeviceDetails = CType(devenum.Current, XmlNode)

Dim devChildNode As XmlNode
Dim childEnum As IEnumerator = DeviceDetails.GetEnumerator
While childEnum.MoveNext
devChildNode = CType(childEnum.Current, XmlNode)
Dim DeviceIDAttr As XmlAttribute = doc.CreateAttribute("DeviceID")
DeviceIDAttr.InnerText = DeviceID
devChildNode.Attributes.Append(DeviceIDAttr)
If devChildNode.OuterXml.StartsWith("<state") Then
States &= devChildNode.OuterXml & vbCrLf
Else
Dim serviceIDstr As String = "<service_" & ServiceID
Dim strtoWrite As String = devChildNode.OuterXml.Replace(serviceIDstr, "<service ID=""" & ServiceID & """ ")
strtoWrite = strtoWrite.Replace("</service_" & ServiceID & ">", "</service>")
ControlURL &= strtoWrite & vbCrLf
ServiceID += 1
End If
End While

End While
End While

States &= "</states>"
ControlURL &= "</ControlURLs>"

Dim fout As New IO.StreamWriter(OutputFolder & "\states.xml")
fout.Write(States)
fout.Close()
fout = New IO.StreamWriter(OutputFolder & "\controlurls.xml")
fout.Write(ControlURL)
fout.Close()

End Sub

Private Function GetWebPageContent(ByVal strURL As String) As String
Dim retVal As String = ""
Try
Dim srRead As System.IO.StreamReader = New System.IO.StreamReader(System.Net.WebRequest.Create(strURL).GetResponse.GetResponseStream)
retVal = srRead.ReadToEnd
Catch ex As Exception
retVal = "Hmm.."
End Try
Return retVal
End Function

Parsing Vera User Data - Loadable as .NET Dataset

2011-09-24T18:39:17Z

Mysticjay: New page: There is most likely not an elegant way to dothis, but in case someone wants something similar feel free to use this. Please suggest if there are better ways of accomplishing this and if y...

There is most likely not an elegant way to dothis, but in case someone wants something similar feel free to use this. Please suggest if there are better ways of accomplishing this and if you happen to know so, update this page :)

1. This will give you a single xml file you could load in .NET through Dataset.Readxml and you will get about 14 tables.
 
 
2. Devices have states and ControlURLs , these become a separate table with a new attribute added to refer what Device ID a row in these tables would relate to the devices table.
 
 
[[Image:Parse.jpg]]

 

----

Private Sub Parse_UserData_To_DataSet(ByVal OutputFolder As String, _
Optional ByVal veraURL As String = "http://myhome:49451")

Dim Entries As New ArrayList
Dim ds As New DataSet
Dim userDatajson As String = GetWebPageContent(veraURL & "/data_request?output_format=json&id=user_data")
Dim userData As String = GetWebPageContent(veraURL & "/data_request?output_format=xml&id=user_data")

'Create Root Data file
Dim si As Integer = userData.IndexOf("<root")
Dim ei As Integer = userData.IndexOf(">", si)
Dim rootstr As String = userData.Substring(si, ei - si + 1)
rootstr = rootstr.Replace(vbLf, "") & "</root>"
Dim fo As New IO.StreamWriter(OutputFolder & "\root.xml")
fo.WriteLine(rootstr)
fo.Close()

Entries.Add("root")
'create rest of it!
Dim jo As New JSONObject(userDatajson)
For Each s As KeyValuePair(Of String, String) In jo._properties
si = userData.IndexOf("<" & s.Key & ">")
ei = userData.IndexOf("</" & s.Key & ">")
If si >= 0 AndAlso ei > 0 Then
Dim thisEntry As String = userData.Substring(si, ei - si + ("</" & s.Key & ">").Length)
Dim fname As String = OutputFolder & "\" & s.Key & ".xml"
Dim fo1 As New IO.StreamWriter(fname)
fo1.WriteLine(thisEntry)
fo1.Close()
Entries.Add(s.Key)
End If
Next

'Put all the tables in a single dataset
Dim finalDS As New DataSet
For Each s As String In Entries
Dim fname As String = OutputFolder & "\" & s & ".xml"
ds = New DataSet
ds.ReadXml(fname)
Try
ds.Tables(0).TableName = s
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = s
Catch ex As Exception
End Try

Next

'devices have controlURL & states
DeviceParse(OutputFolder)

ds = New DataSet
ds.ReadXml(OutputFolder & "\states.xml")
Try
ds.Tables(0).TableName = "states"
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = "states"
Catch ex As Exception
End Try
ds = New DataSet
ds.ReadXml(OutputFolder & "\ControlURLs.xml")
Try
ds.Tables(0).TableName = "ControlURLs"
finalDS.Tables.Add(ds.Tables(0).Copy)
finalDS.Tables(finalDS.Tables.Count - 1).TableName = "ControlURLs"
Catch ex As Exception
MsgBox(ex.Message)
End Try

finalDS.WriteXml(OutputFolder & "\UserData.xml")

End Sub

Sub DeviceParse(ByVal OutputFolder As String)
'ControlURLs
'states
Dim filename As String = OutputFolder & "\devices.xml"
Dim ControlURL As String = "<ControlURLs>" & vbCrLf
Dim States As String = "<states>" & vbCrLf

Dim ServiceID As Integer = 1

Dim settings As New XmlReaderSettings()
settings.DtdProcessing = DtdProcessing.Parse

Dim reader As XmlReader = XmlReader.Create(filename, settings)
Dim doc As New XmlDocument()
doc.Load(reader)
Dim root As XmlNode = doc.DocumentElement
Dim ienum As IEnumerator = root.GetEnumerator()
Dim device As XmlNode
While (ienum.MoveNext())
device = CType(ienum.Current, XmlNode)
Dim DeviceID As String = device.Attributes.GetNamedItem("id").InnerText

Dim DeviceDetails As XmlNode
Dim devenum As IEnumerator = device.GetEnumerator
While (devenum.MoveNext)
DeviceDetails = CType(devenum.Current, XmlNode)

Dim devChildNode As XmlNode
Dim childEnum As IEnumerator = DeviceDetails.GetEnumerator
While childEnum.MoveNext
devChildNode = CType(childEnum.Current, XmlNode)
Dim DeviceIDAttr As XmlAttribute = doc.CreateAttribute("DeviceID")
DeviceIDAttr.InnerText = DeviceID
devChildNode.Attributes.Append(DeviceIDAttr)
If devChildNode.OuterXml.StartsWith("<state") Then
States &= devChildNode.OuterXml & vbCrLf
Else
Dim serviceIDstr As String = "<service_" & ServiceID
Dim strtoWrite As String = devChildNode.OuterXml.Replace(serviceIDstr, "<service ID=""" & ServiceID & """ ")
strtoWrite = strtoWrite.Replace("</service_" & ServiceID & ">", "</service>")
ControlURL &= strtoWrite & vbCrLf
ServiceID += 1
End If
End While

End While
End While

States &= "</states>"
ControlURL &= "</ControlURLs>"

Dim fout As New IO.StreamWriter(OutputFolder & "\states.xml")
fout.Write(States)
fout.Close()
fout = New IO.StreamWriter(OutputFolder & "\controlurls.xml")
fout.Write(ControlURL)
fout.Close()

End Sub

Private Function GetWebPageContent(ByVal strURL As String) As String
Dim retVal As String = ""
Try
Dim srRead As System.IO.StreamReader = New System.IO.StreamReader(System.Net.WebRequest.Create(strURL).GetResponse.GetResponseStream)
retVal = srRead.ReadToEnd
Catch ex As Exception
retVal = "Hmm.."
End Try
Return retVal
End Function

File:Parse.jpg

2011-09-24T18:34:41Z

Mysticjay:

User:Mysticjay

2011-09-24T18:30:08Z

Mysticjay: New page: Parsing Vera User Data - Loadable as .NET Dataset

[[Parsing Vera User Data - Loadable as .NET Dataset]]