Switch to: V9V8V7V6V5

ARRAY Field

[new in v8.0]

Valentina DB array fields define a table column as a FIXED-length 1-dimension array, according to SQL Standard. An array can be of any built-in base type. Internally, Array fields use storage very similar to the VARIANT field in Valentina DB.

Valentina DB array fields differ from arrays in PostgreSQL, which implement a variable-size array.

The array data type uses square brackets ([]) to the conform to standard SQL syntax for working with arrays.

TODO: Allow arrays of enums, user-defined types, domains, composite types.

Declaration of ARRAY Field

API

The field is represented at the API level of a Valentina ADK by VArray Class.

dim fldA as VArray
fldA = tbl.CreateArrayField( "fldArray", EVFieldType.kLongType, 10 )

SQL

At SQL level you can create this field using

-- SQL Standard:
CREATE TABLE T1( f1 Long ARRAY[10] );
-- Not SQL Standard: skip ARRAY keyword
CREATE TABLE T1( f1 Long[10] );

Array Value Input

API

VArray.Value allows you to assign the whole array from your programming language into a field then add a record.

tbl.SetBlank()
 
  fldA = tbl.ArrayField( "fldArray" )
  fld.value = Array(1, 2, 3)  // syntax of Xojo, other language should use own syntax to specify array.
 
tbl.AddRecord()

SQL

To insert a record with Array value, you should use SQL Standard syntax:

INSERT INTO T1(f1, f2) VALUES( 23, ARRAY[1,2,3,4,NULL,5+1,7*2,8,9,10] )

And of course you can use SQL Binding in both SqlExecute()/SqlSelect() and VSqlStatement. Read those sections for details.

Acessing Arrays

Now we can read from Table.

API

In API way, you again use VArray.Value, but now to read the whole array from the Array Field of the current record.

After that, you can use your programming language to work with Array items.

SQL

In SQL you can access a single array element using operator []

SELECT * 
FROM T
WHERE fa[1] = 545

An array subscript expression will return null if either the array itself or any of the subscript expressions is null. Also, null is returned if a subscript is outside the array bounds (this case does not raise any error).

Modifying Arrays

An array value can be replaced completely:

API

tbl.RecID = 545  // go to a record with RecID = 545
 
fldA = tbl.ArrayField( "fldArray" )
fld.value = Array(3, 2, 1) 
 
tbl.UpdateRecord()

SQL

UPDATE T SET fldA = ARRAY(3,2,1)
WHERE fldID = 545

or in SQL you can change only one element:

UPDATE T SET fldA[3] = 1
WHERE fldID = 545

Notice, that although in SQL it looks as a touch of a single element, the engine will read and uncompress the whole array at the background, then change one element, then compress and write the whole array again.

An array can also be constructed using the functions ARRAY_PREPEND(), ARRAY_APPEND(), ARRAY_CONCAT().

Searching in Arrays

SQL

You can search for a single element of ARRAY or even compare two elements:

SELECT * 
FROM T1
WHERE fldA[2] = 1000
SELECT * 
FROM T1
WHERE fldA[2] < fld[3]
SELECT * 
FROM T1
WHERE fldA[1] + fldA[2] < fld[3]

This search happens as a column scan in the ValentinaDB at v8.0.

You can also search for specific values in an array using the ARRAY_POSITION() and ARRAY_POSITIONS functions. The former returns the subscript of the first occurrence of a value in an array; the latter returns an array with the subscripts of all occurrences of the value in the array. For example:

SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
=> 2

Array Input and Output Syntax

The external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array's element type, plus decoration that indicates the array structure.

The decoration consists of square brackets [ and ] around the array value plus delimiter character between items. The delimiter character is a comma ”,”.

In the result, you get a string, which you can pass back into SQL.

For example, let you have ARRAYs with values as shown here:

INSERT INTO T1(fldA) VALUES( ARRAY[1,2,3] );
 
INSERT INTO T2(fldB) VALUES( ARRAY['aaa',NULL,'ccccc'] );

When you will output them into text format, you will get

  • [1,2,3] - for the first array.
  • ['aaa',NULL,'ccccc'] - for the second.

You may notice that individual element values are written the same way they would be written when not members of an array.