B4J Question Best way in capturing event

[aaronk]

Hi,

I am looking at the best way in capturing the following in my B4J app.

There is 500+ locations sending incoming data to my B4J app.

Each location has 40 temperature sensors.

As the temperature changes it will send the new temperature value to my B4J app.

I am trying to work out the best way in saving this temperature in a SQLite database in my B4J app.

I would need to know:
- Timestamp
- Temperature received
- Which sensor it was
- The location

I then need to receive the data and display it to the user based on the location.

So I was planning on having 1 single database table, and have 4 columns: Timestamp, temp, sensor, location.

Then I could do a SQL request like, Select all temp from location, then somehow make a JSON message with the temp, sensor and timestamp for each record.

I plan to only capture 10,000 temps per location and then begin to overwrite old temperatures.

What would be the best way in doing this ?

Open to suggestions.

 

[Erel]

How do the sensors send the data? Not sure that SQLite is the best option here.

 

[MarkusR]

i think object serialization would be better than json, u can put the data into a list with a type timestamp,Temperature,sensor,location
serialize the list and push it into other app, deserialize it there for visualisation as chart.

 

[aaronk]

How do the sensors send the data?

They are sending UDP messages to my B4J app. (currently working fine).
I don't have control over how it's sent to my B4J app.

Just trying to work out the best way in saving the data once I receive it and then later read it.

Not sure that SQLite is the best option here.

What would be the better way ?

object serialization would be better than json

I will look into that. I can't say I have used that before, but will look at it once I work out how to save it.

 

[MarkusR]

I will look into that. I can't say I have used that before, but will look at it once I work out how to save it.

'jServer Handler Class
Sub Handle(req As ServletRequest, resp As ServletResponse)

    Log(req.FullRequestURI)
    Log(req.ContentType)
 
    Dim ser As B4XSerializator
   
    Select req.GetParameter("cmd")
        Case "list"
            Dim filter As String = req.GetParameter("filter")
            Dim List1 As List
            List1 = ... your List here
                Dim buffer() As Byte
                buffer = ser.ConvertObjectToBytes(List1)
                resp.ContentType="application/octet-stream"
                resp.OutputStream.WriteBytes(buffer,0,buffer.Length)
         Case Else
    End Select
   
End Sub

your class

Sub Class_Globals
    'Private fx As JFX
    Type TemperatureData(timestamp as long,Temperature as float,sensor as int,location as string)

End Sub

a sub that return a list with type TemperatureData

client side, it need the same class name and your Type in the project

Sub ListRequest(Filter As String) As ResumableSub
  
   Dim Job As HttpJob
   Job.Initialize("",Me)
   Job.Username = Main.Username
   Job.Password = Main.Password
   Job.Download2("https://" & Main.Server & "/request", Array As String("cmd", "list", "filter", Filter))
   Dim ser As B4XSerializator
   Dim List1 As List
   List1.Initialize
  
   Wait For (Job) JobDone(Job As HttpJob)
   If Job.Success Then
       Dim buffer(Job.GetInputStream.BytesAvailable) As Byte
  
       Job.GetInputStream.ReadBytes(buffer, 0, buffer.length)
       List1 = ser.ConvertBytesToObject(buffer)
   End If
   Job.Release
 
   Return List1
 
End Sub

 

[aaronk]

That code looks interesting but will look at it later on when I need to send the data.

I am mainly interested in how I am going to save it for now.

 

[MarkusR]

I am mainly interested in how I am going to save it for now.

you can try SQLite and see if it is fast enough.

i create a database layout with this tool http://sqlitebrowser.org/

my simple database class (b4j) used jSQL library

Sub Class_Globals
    Private SQL1 As SQL
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize
       
End Sub

Public Sub Open() As SQL

    Log(File.DirApp)
    SQL1.InitializeSQLite(File.DirApp, "Temperature.db", False)
   
    Return SQL1
   
End Sub

Public Sub Close
   
    SQL1.Close
   
End Sub

parts of my data management class for insert,update,delete,search

Sub Class_Globals
    'Private fx As JFX
    Type SinglePassword(Id As Int,Name As String,User As String,Url As String,Password() As Byte,Changed As Long,Source As String,Mail As String,Note As String)

End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize
   
End Sub

public Sub LoadAll(Filter As String) As List
   
    If Filter="" Then
        Filter="%"
    Else
        Filter="%" & Filter & "%"
    End If
   
    Dim List1 As List
    List1.Initialize
   
    Dim DB1 As DB
    DB1.Initialize
   
    Dim sql1 As SQL = DB1.Open
   
    Dim rs As ResultSet = sql1.ExecQuery2("SELECT * FROM PASSWORDS WHERE NAME LIKE ? OR USER LIKE ? OR MAIL LIKE ? OR URL LIKE ? OR NOTE LIKE ? OR SOURCE LIKE ? ORDER BY NAME",Array As String(Filter,Filter,Filter,Filter,Filter,Filter))
    Do While rs.NextRow
        Dim Item11 As SinglePassword
        Item11 = FromDB(rs)
       
        List1.Add(Item11)
       
    Loop
    rs.Close
   
    DB1.Close
   
    Return List1
   
End Sub

Sub FromDB(rs As ResultSet) As SinglePassword
   
    'Log("FromDB")
   
    Dim Item As SinglePassword
    Item.Initialize
   
    Item.Id = rs.GetString("Id")
    Item.Name = rs.GetString("Name")
    Item.User = rs.GetString("User")
    Item.Password = rs.GetBlob("Password")
    Item.Changed = rs.GetLong("Changed")
    Item.Mail = rs.GetString("Mail")
    Item.Source = rs.GetString("Source")
    Item.Url = rs.GetString("Url")
    Item.Note = rs.GetString("Note")

    Return Item
   
End Sub

Sub Delete(Item As SinglePassword)
   
    Dim db1 As DB
    db1.Initialize
   
    Dim sql1 As SQL = db1.Open
    sql1.ExecNonQuery2("DELETE FROM Passwords WHERE Id=?", Array As Object( Item.Id))

    db1.Close
   
End Sub

Sub Save(Item As SinglePassword)
   
    Dim db1 As DB
    db1.Initialize
   
    Dim sql1 As SQL = db1.Open

    Dim arg As List
    arg = Array As Object(Item.Name,Item.User,Item.Password,Item.Changed,Item.Url,Item.Source,Item.Mail,Item.Note)
   
    If Item.Id=0 Then
       
        Log("INSERT")
       
        'Autokey
        sql1.ExecNonQuery2("INSERT INTO Passwords (Name,User,Password,Changed,Url,Source,Mail,Note) VALUES (?,?,?,?,?,?,?,?);", arg)
       
    Else
        Log("UPDATE")

        ' without '' at strings!!! just ? as parameter !!!
        sql1.ExecNonQuery2("UPDATE Passwords SET Name=?,User=?,Password=?,Changed=?,Url=?,Source=?,Mail=?,Note=? WHERE Id=" & Item.Id, arg)

    End If
   
    db1.Close

End Sub

 

[Erel]

What would be the better way ?

It depends on the frequency. If it is no more than a few messages per second then SQLite will be fine. A database such as MySQL is more suitable for concurrent access.

 

[aaronk]

I think SQLite might work as it will be only a few events happening every few minutes.

Would you use one table and load it into the one table or create a table per location ?

I want to limit 10,000 temps per location, somehow I will need to work out how many records there are per location and delete the oldest first and if it’s one table, I am not really sure on how to do that. If there is a way then I might be able to use the one table.

 

[MarkusR]

Would you use one table and load it into the one table or create a table per location ?

if there is a adress (struct) behind the location typically u would habe two tables , data linked to a location adress table.
split into tables like germanydata,spaindata,francedata make no sense.

per location and delete the oldest first

a delete query by time stamp would be easier. (something like current date - 30 days)