Move to a new recorded data table

[clavay]

The idea is to create a new recorded data table that is more simple to store numbers and boolean (one field) and allows to store strings.

I made some test about the difference using one field for boolean, integer and floats or separated fields as currently, and a field for strings.

Here is the django app used to test :
dbTest.zip

in dbTest/models.py you have :

• RecordedData is the same as currently used in PyScada
• RecordedData2 that store everything in a FloatField
• RecordedData3 is as RecordedData2 with a CharField column
• RecordedData4 is the same as RecordedData3
• RecordedData5 is the same as RecordedData4 but the CharField is a TextField

in dbTest/a.py you have the result :

• RecordedData2 is quite the same as RecordedData : slightly faster and smaller
• RecordedData3 is quite the same as RecordedData2 : slightly slower and larger
• RecordedData4 store a text and is larger than RecordedData3
• RecordedData5 is quite the same as RecordedData4

My proposition is to use the RecordedData4 or RecordedData5 as the new table.

@trombastic, what do you think ?

[trombastic]

a float64 floatfield has only 52 bits for the number the rest is for the exponent, so only 32 bit integers can be stored, this is Okay for most values, but we have to drop INT64 and UINT64 data types. I'm not sure what would be the performance penalty for having a char or textfield as column in the DB, but i suspect for bigger queries it would be not so good. With the new option to have more than one data table/backend would it be possible to have a separate Table for variables that have to store data as str?

[clavay]

Ok, so we need 3 fields:

• BigIntegerField for integers (with the shift technique for unit64),
• FloatField for floats,
• CharField or TextField for strings.

I don't know if there is a performance penalty to have a char or text column for the variable not having data in it, I will try it.

One solution with the new data source architecture could be to separate this in 3 tables:

• RecordedDataInteger,
• RecordedDataFloat,
• RecordedDataString.

One question is what should the manager do if the variable value class is:

• string and the value to store is an integer or a float,
• float and the value to store is an integer or a string,
• integer and the value is a float or a string ?

[trombastic]

One question is what should the manager do if the variable value class is:

in my opinion either drop the value and throw an error or convert to the appropriate data type

[clavay]

Ok so finally all the values of a variable will be in only one table.