Switch to: V9V8V7V6V5

Fields of Collection types (DRAFT)

We can say that Valentina have Collections of Objects with help of Links. Really, one object (parent) have collection of child-objects. But using links we cannot put into table T array of ints of size 20.

Valentina, starting from 2.5 release, supports special collection Field types - Set and Array, that provide functionality of collection for basic types.

Levels of implementation

Support of this column types implemented on both levels of course: API and SQL.

Storage implementation

Internally SET and ARRAY columns can be based on one of the following:

  • Fixed Binary field
  • Var Binary field
  • BLOB field
  • SubTable

When you create a new column of SET or ARRAY type you need specify which underline storage should be used for it. Choice is quite easy:

  • if you want array that have fixed N and always or in most case all items will exists then it is good idea to choose storage on Fixed Binary type.
  • if you want array that can have different number of items from zero to N you can choose storage on VarBinary. Note, that maximal size of such array is 4Kb.
  • if you want array that can have different number of items but without limit of maximal size then you can choose storage on BLOB.

Keep of Sort Order

Valentina always keep items if SET sorted to be able refuse non-distinct values.

ARRAY can keep sort on request of user. When ARRAY is sorted it has practically the same functionality as SET just can keep non-distinct values. On default ARRAY is not sorted, because sorting require additional efforts.

When ARRAY is not sorted you can use additional set of methods listed below. These methods allow you keep items in some special order if you need that.

  • InsertAt( pos, v )
  • RemoveAt( pos )
  • FindPosition( v )

Indexing of Collections

One of the main advantage of collections supported on the kernel level is ability to index them. Since Valentina knows type of items it can correctly split array on items and append them to index.

It is easy see that behavior of such index is similar to IndexByWords for string fields. If you have 100 records, each have its own array and records 5, 15, 25 contain value 99 in arrays, then search on this index will return us {5, 15, 25}.

Comparison of SET and ARRAY

Operation SET ARRAY (sorted) ARRAY (not sorted)
Duplicates no yes yes
Is Sorted yes yes no
Append(v) log(N) log(N) 0
Remove(v) log(N) log(N) N
Include(v) log(N) log(N) N
InsertAt(pos,v) - - 0
RemoveAt(pos) - - 0
FindPos(v) - - N

Read Also