Boolean array to SQLite

Databases
1

Which is the best way to save an array() of booleans to SQLite?

myArray(2) as boolean
kHQNorth, kHQCenter, etc. are constants 0, 1, 2
I used three fields on the table:

row.Column("North").BooleanValue = myArray(kHQNorth)
row.Column("Center").BooleanValue = myArray(kHQCenter)
row.Column("South").BooleanValue = myArray(kHQSouth

This is possible for an array of only a few items.

Is there a way to save all booleans on a string separated by (for example) “,” ?
row.Column(“allBooleans”).StringValue = “1,0,0,1,1,0” etc. or (“true,false,false,…”
Other than manually loop the array (myArray) and create the string?

2

I’m more of a MySQL or MS SQL guy. My thought is that with only 3 values it doesn’t seem to be worth the effort to combine them. But if you do, make sure to add something to indicate a “version”. So if you add a 4th value at some time you can detect the change.

3

I would use a binary enum to hold the data & save it to the database as an integer.

1 Like
4

yes as @Wayne_Golding said: use an integer as a binary. each bit represents a boolean value
so you can store 8 booleans in one uint8
but it may become harder to search for some value after that.

5

Using binary is an option but not very different to do with a string

Var myArray(5) as Boolean
Var s as string

myArray(0)= true
myArray(1)= true
myArray(2)= false
myArray(3)= true
myArray(4)= false
myArray(5)= true

For each b as Boolean in myArray
  s = s + if(b,"1","0")
Next b

MessageBox S

//=====

Var row As New DatabaseRow
row.Column("BoolArray").StringValue = s

…
6

it will take 8x more room than an uint8 ?

7

Write extension methods for the Column, in the setter convert the array to a JSON and save it as a text, in the getter, retrieve the StringValue and parse the JSON to return an array.

8

Gone are the days when we needed to pack as much information into as small a space as possible. I would even suggest to store the array as json, so you have name/value pairs. It is both backward compatible and future proof. Takes a lot more space, but is very worth it.