Need recommendation on Database

[Trax]

I have been out of circulation for a long time  I have a little AutoIT program I wrote that reads a serial port and collects reading from that serial port. I just found out today that the owner wants to expound on this in a major, major way. Need to keep data going back 10 years. This is going to require storing these reading in a database. Hence the question. What database would you recommend and which UDF would you use to read and write data to that UDF? Here is my calculation on storage storing everything as strings (worst case). Doesn't seem like a lot.

Per Device One reading every 15 minutes:

                     Date: 10 bytes (MM/DD/YYYY)
                     Time: 5 bytes  (HH:MM) 
  Device Number: 1 byte   (1-9) 
       Temperature: 4 bytes  (XXXX)

20 bytes every 15 minutes 
80 bytes per hour
1920 bytes per day
700,800 bytes per year

3 devices = 2,102,400 bytes per year

10 years = 21, 024,000 bytes or 21MB per year
 

[Musashi]

Just one word : SQLite

Very well integrated in AutoIt (and many other languages). There are also a huge number of examples, tutorials etc.

Edited February 28, 2020 by Musashi

[jchd]

That's a very small database!

I'd highly recommend using SQLite.

First, AutoIt comes with a ready to use UDF for it.
Next, it's the most widely used and stable piece of software ever, along with zlib possibly.
Any SQLite DB can be used verbatim from any hardware/software platform you can dream of.
There are SQLite DBs managing Tb of data.
SQlite is free and open-source.
You already use many SQLite DBs under the hood of your devices (smartphone, tablet, smart TV, router, GPS, browser, OS, car, ...).

[jchd]

Refering to your post, a sample schema could be along this one:

CREATE TABLE "Data" (
  "Id" INTEGER NOT NULL PRIMARY KEY, 
  "Device" INT NOT NULL, 
  "Stamp" CHAR NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  "Value" INT NOT NULL);

CREATE INDEX "ixDataDeviceStamp" ON "Data" ("Device", "Stamp");

CREATE INDEX "ixDataStampDevice" ON "Data" ("Stamp", "Device");

Of course details will depend on the requirements about querying the data. But with this schema, you can query by device and period, per period and device, per device alone, per period alone. Unless your target is an embedded device with only scarce storage, don't worry about the size needed for maintaining indices, they'll be useful when you query your DB.

However I question the temperature being an integer, as a real would make more sense in most contexts.

SQLite itself comes with only very basic statistical functions but readily available loadable extensions complement that with more useful functions.

[Trax]

Just so I am clear we aren't talking Microsoft SQL Server Express right? SQLite is something totally different? Where is it available?

[jchd]

SQLite has nothing to do with MS, nor Oracle, nor any client-server engine. As already said, you have it in the AutoIt distribution!

Go to https://www.sqlite.org/index.html

 and go download the binaries of your choice (Win or not, X86 or X64).

[Nine]

Google is your friend you know :

https://www.sqlite.org/download.html

[jchd]

Is sampling the temperature data synchronous among devices or not?

If yes, then we can store all devices' readings every 15'. If not, then the proposed schema is correct.

[Trax]

The sampling is on demand. I have to send a string to make the request then read the reply. It is RS485. The AutoIT program I wrote works great. I just have to add the step to store. Thanks Gents. As always your input is invaluable.

[jchd]

Some questions remain:

Do you read all devices at the same time or do you read devices individually at different times?
Put otherwise, will you store data like this (synchronously):

Stamp                             Dev1    Dev2    Dev3 .....
2020-02-28 19:54:58        17         18         19 .....
2020-02-28 19:59:01        18         19         17 .....

or like that (asynchronously):

Id    Device    Stamp                           Value
1         1         2020-02-28 19:02:57    17
2         4         2020-02-28 19:58:02    18
3         7         2020-02-28 19:58:13    18
4         3         2020-02-28 19:58:22    19
5         2         2020-02-28 19:58:31    17
6         5         2020-02-28 19:58:40    20

It makes an important difference, both for needed storage and for flexibility of querying.

in SQL you can't parametrize schema names. That means that in sync version, you'll need to query for a device number explicitely because this is the name of a column (part of the schema). In async version, you can query devices parametrically since device numbers are stored data, not schema names.

For instance and in async mode, if you need the min, max and average temperatures of devices # 2 to 7 in the last 20 days, sorted by decreasing average you'd do:

select Device min(value) MinT, max(value) MaxT, avg(value) AvgT from data where device between 2 and 7 and stamp between datetime('now', '-20 days') and datetime('now') group by device order by AvgT desc;

If you want to do the same in the sync mode, you'll need to get min, max and avg data for every Dev* column explicitely and one at a time. When and if you need flexibility in querying statistical data, you'll find that very cumbersome, slow and error prone.

I recommend storing timestamps in Julian date format due to it's short size. You can convert to/from regular human-readable datetime format in a snap. At any rate, always store dates in YYYY-MM-DD format if you choose text: it's a format SQLite (and AutoIt) understand and it sorts naturally.

Another question is the format of the temperature data: isn't it rather a real?

[Trax]

I Poll in a loop:

While 1

   ReadMeter1

   ReadMeter2

   ReadMeter3

   SaveData

   Wend

[Trax]

This is kind of onteresting. Here is a "Getting Started" link: https://www.sqlite.org/quickstart.html

It says to download the binaries, open a command prompt and start using. Of course it doesn't work. I am sure you have to do something with the binaries after you download them but they don't mention that.....

Is all the documentation that lacking?

 

 

[jchd]

Leave the CLI (command-line utility) alone for now. I'm gathering something to get you started.

In the meantime, download SQLite Expert free version (no ads nor any nastyware) and familiarize yourself with the examples that come along it. Prefer the 32-bit version for now.

 

Edited March 2, 2020 by jchd

[jchd]

Here are 3 code snippets.

The first will create a sample DB with 10 years of recordings for 3 devices polled every 15' and varying tempratures. It takes about 3 minutes to run, produce about 1M rows (~59Mb DB).

expandcollapse popup

#include <SQLite.au3>

Local Const $SQLITE_DLL = "C:\SQLite\bin\sqlite3.dll" ;<-- Change to the location of your sqlite dll

; Init sqlite
_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
ConsoleWrite("SQlite version " & _SQLite_LibVersion() & @LF)

Local $hDB = _SQLite_Open("temper.sq3")

Local $s = "CREATE TABLE if not exists Data (Id INTEGER PRIMARY KEY not null, Stamp int not null default (strftime('%s','now')), Device int not null, Value real not null);" & _
           "CREATE INDEX if not exists ixDataDeviceStamp ON Data (Stamp, Device);" & _
           "CREATE INDEX if not exists ixDataStampDevice ON Data (Device, Stamp);"
_SQLite_Exec($hDB, $s)

_SQLite_SetTimeout($hDB, 3600000)

Local Const $iDevices = 3
Local $Stamp = "2010-01-01 00:00:00", $Stop = "2020-03-01 00:00:00"
Local $aRefTemp[$iDevices] = [23.2, 39.4, 46.1], $aTemp = $aRefTemp

_SQLite_Exec($hDB, "begin immediate")
Do
    _SQLite_Exec($hDB, "insert into Data (Stamp, Device, Value) values " & _
                        "(strftime('%s', '" & $Stamp & "'), 1, " & $aTemp[0] & "), " & _
                        "(strftime('%s', '" & $Stamp & "'), 2, " & $aTemp[1] & "), " & _
                        "(strftime('%s', '" & $Stamp & "'), 3, " & $aTemp[2] & ");")
    ; progress in time
    $Stamp = StringReplace(_DateAdd("n", 15 , $Stamp), '/', '-')
    ; make temperatures more or less randomly change a bit
    For $i = 0 To $iDevices - 1
        $aTemp[$i] = Round($aRefTemp[$i]  * (1.0 + Sin(Random(0, 2 * 3.141592653589793)) / 15.0), 2)
    Next
Until $Stamp > $Stop
_SQLite_Exec($hDB, "commit")

_SQLite_Close($hDB)
_SQLite_Shutdown()

The second is an example on how you would use that schema to insert new data in such a DB.

expandcollapse popup

#include <SQLite.au3>

; include for RS485 communication

Local Const $SQLITE_DLL = "C:\SQLite\bin\sqlite3.dll" ;<-- Change to the location of your sqlite dll

; Init sqlite
_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
ConsoleWrite("SQlite version " & _SQLite_LibVersion() & @LF)

Global $hDB = _SQLite_Open("temper.sq3")

Local $s = "CREATE TABLE if not exists Data (Id INTEGER PRIMARY KEY not null, Stamp int not null default (strftime('%s','now')), Device int not null, Value real not null);" & _
           "CREATE INDEX if not exists ixDataDeviceStamp ON Data (Stamp, Device);" & _
           "CREATE INDEX if not exists ixDataStampDevice ON Data (Device, Stamp);"
_SQLite_Exec($hDB, $s)

_SQLite_SetTimeout($hDB, 3600000)

;
; here init RS485 UDF as needed
;

; # of devices and storage for polling a period
Global Const $iDevices = 3
Global $aTemp[$iDevices]

AdlibRegister(LogTemp, 15 * 60 * 1000)

While 1
    ; have something here to exit this endless loop
    Sleep(100)
WEnd

;
; close RS485 link if needed
;

_SQLite_Close($hDB)
_SQLite_Shutdown()


Func LogTemp()
    ; get current datetime stamp
    Local $Stamp = _NowCalc_Check()

    ;
    ; poll devices and store read values in $aTemp
    ;

    Local $sSQL = "insert into Data (Stamp, Device, Value) values (strftime('%s', '" & $Stamp & "'), 1, " & $aTemp[0] & ")"
    For $i = 1 To UBound($aTemp) - 1
        $sSQL &= ", (strftime('%s', '" & $Stamp & "'), " & $i + 1 & ", " & $aTemp[$i] & ")"
    Next

    _SQLite_Exec($hDB, $sSQL)
    ; optionally, test @error and deal with it if != 0
EndFunc

; Melba23 contribution for stable stamp reading
Func _NowCalc_Check()
    Local $sRet, $sCheck
    Do
        $sCheck = @SEC
        $sRet = @YEAR & "/" & @MON & "/" & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC
    Until @SEC = $sCheck
    Return $sRet
EndFunc   ;==>_NowCalc_Check

The third shows how to query the DB for some queries.

#include <SQLite.au3>

Const $SQLITE_DLL = "C:\SQLite\bin\sqlite3.dll" ;<-- Change to the location of your sqlite dll

; Init sqlite
_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
ConsoleWrite("SQlite version " & _SQLite_LibVersion() & @LF)

Local $hDB = _SQLite_Open("temper.sq3")

Local $aRows, $iRows, $iCols

; minimum,maximum and average temperatures of each device during the year 2019
Local $s = "select device, min(value) Min, max(value) Max, round(avg(value), 2) Average from data where Stamp between strftime('%s', '2019-01-01') and strftime('%s', '2019-12-31') group by device"
_SQLite_GetTable2d($hDB, $s, $aRows, $iRows, $iCols)
_SQLite_Display2DResult($aRows)

; 1 hour before and 1 hour after the max temp of device #2
$s = "with dt (st) as (select stamp from data where device = 2 and value = (select max(value) from data where device = 2) limit 1) " & _
    "select datetime(stamp, 'unixepoch') Period, Value from data, dt where device = 2 and stamp between dt.st - 3600 and dt.st + 3600"
_SQLite_GetTable2d($hDB, $s, $aRows, $iRows, $iCols)
_SQLite_Display2DResult($aRows)

; history of temperature from device #3 during Jan 17 2019
$s = "select datetime(stamp, 'unixepoch') Period, value from data where device = 3 and date(stamp, 'unixepoch') like '2019-01-17%'"
_SQLite_GetTable2d($hDB, $s, $aRows, $iRows, $iCols)
_SQLite_Display2DResult($aRows)

_SQLite_Close($hDB)
_SQLite_Shutdown()

As you'll see in the SCiTe console, querying such a DB is very fast, even if storing timestamps as Unix epoch requires conversion to a human-readable form for computing time differences easily or just display.

Of course, all of this is just a toy which may not be correct to fit all of your requirements.

[Trax]

@jchd you are a life saver. A million thanks!

Edited March 4, 2020 by Trax

[argumentum]

On 3/2/2020 at 12:47 PM, jchd said:

; Melba23 contribution for stable stamp reading Func _NowCalc_Check()     Local $sRet, $sCheck     Do         $sCheck = @SEC         $sRet = @YEAR & "/" & @MON & "/" & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC     Until @SEC = $sCheck     Return $sRet EndFunc   ;==>_NowCalc_Check

Am curious about this function.  Why is it better than just @YEAR & "/" & @MON & "/" & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC ?

[jchd]

Because that ensures there is no overflow from 59s to anything, which can be up to one year shift!

[Trax]

You are pre-populating the Database right? Is this for speed during normal operation?

[jchd]

No it's just for experimenting with about 10-years worth of data.

[Trax]

Off to a rip-roaring start. It can't find the dll. It is there. I have quadruple checked:

Local Const $SQLITE_DLL = "C:\SQLite\sqlite3.dll"
_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then
    MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
    Quit()
EndIf