Field Type - SET (DRAFT !!!)

Column of this type is able contain set of distinct values of any basic numeric type supported by Valentina (bool, byte, short, ushort, medium, umedium, long, ulong, llong, ullong, float, double, date, time, datetime ).

To create in a table field of SET type you need specify:

• Name
• Item Type
• Storage Type
• Optional - Max Size. If Size is not specified and storage type is:
  • VarBinary then is used max size to get 4K total
  • FixedBinary then is used size = 20.
• Optional - Flags Nullable and Indexed.

API:

tbl.CreateSet( 
       "fldSet",          // filed name
        kTypeShort,       // item type
        kTypeFixedBinary, // storage type   
        20,               // max number of items
        fNullable + fIndexed
);

SQL:

CREATE TABLE T(
       fldSet SET OF SHORT[20] ON FIXED BINARY NULLS INDEXED
)

You can append one or more items into SET using the folowing API and SQL commands. If you will try insert duplicate values only one will be inserted.

API:

tbl.SetBlank(); // STEP 1: clears record buffer
 
    // STEP 2: assign field values 
    fldName = "Peter";
    fldSet.Append( v ); // appends one value to empty set
 
tbl.AddRecord(); // STEP 3: adds a new table record

NOTE: Also can be used such functions as:

    // appends few values from a given array 
    fldSet.Append( ArrayOfValues ); 
 
    // appends few values from a given cursor field
    fldSet.Append( cursor, [cursorFieldName] ); 

SQL:

INSERT INTO T(f1, f2, fldSet) VALUES( 1, 'abc', 4 );
INSERT INTO T(f1, f2, fldSet) VALUES( 1, 'abc', {4} );    
INSERT INTO T(f1, f2, fldSet) VALUES( 1, 'abc', {4,77,13,8} );
INSERT INTO T(f1, f2, fldSet) VALUES( 1, 'abc', (SELECT b4 FROM T2 WHERE b2>100) );

Here nothing special, we just delete record of table as we do this usually. SET is deleted also as part of record.

API:

VTable.DeleteRecord();

SQL:

DELETE FROM T WHERE ...

You can add one or more items into existed SET of existed record using the folowing API and SQL commands.

API:

// Step 1: assign field value:
fldSet.Append( v );
fldSet.Append( ArrayOfValues );
fldSet.Append( cursor, [cursorFieldName] );
 
// Step 2: update table record:
tbl.UpdateRecord();

To remove items from SET with help of SQL but not remove SET itself we need use UPDATE command in combination with remove() function:

SQL:

UPDATE T 
SET f1 = 1, f2 = 'abc', fldSet = append(fldSet, 4) 
WHERE recid = 55;
 
UPDATE T 
SET f1 = 1, f2 = 'abc', fldSet = append(fldSet, 4, 77, 13, 8) 
WHERE recid = 55;
 
UPDATE T 
SET f1 = 1, 
    f2 = 'abc', 
    fldSet = append(fldSet, (SELECT b4 FROM T2 WHERE b2>100)) 
WHERE recid = 55;

NOTE that function append(), as well as other functions of this group, can accept as first parameter set that is from other field of this table! i.e. you can write something as:

UPDATE T 
SET f1 = 1, 
    f2 = 'abc', 
    fldSet = append(fldSet22, (SELECT b4 FROM T2 WHERE b2>100)) 
WHERE recid = 55;

You can remove one or more items from existed SET of existed record using the folowing API and SQL commands.

API:

fldSet.Remove( v );
fldSet.Remove( ArrayOfValues );
fldSet.Remove( cursor, [cursorFieldName] );

To remove items from SET with help of SQL but not remove SET itself we need use UPDATE command in combination with remove() function:

SQL:

UPDATE T 
SET f1 = 1, f2 = 'abc', fldSet = remove(fldSet, 4) 
WHERE recid = 55;
 
UPDATE T 
SET f1 = 1, f2 = 'abc', fldSet = remove(fldSet, 4, 77, 13, 8) 
WHERE recid = 55;
 
UPDATE T 
SET f1 = 1, 
    f2 = 'abc', 
    fldSet = remove(fldSet, (SELECT b4 FROM T2 WHERE b2>100)) 
WHERE recid = 55;

You can do search on SET fields using API and SQL ways as always.

API:

SQL:

SELECT ...
FROM T
WHERE include(fldSet, 17 ) 

SELECT ...
FROM T
WHERE fldSet.include( 17 ) // OO notation