Switch to: V12V11V10V9V8V7V6V5

KeyValue For Table

Overview

KeyValue for Table is a special case of KeyValue With Key intended to be integrated with a Table of Valentina Database.

We have the following inheritance:

Therefore you should read the description of parent classes also.

As you know each Valentina Table have RecID field to identify a record.

Observation: Such structure as “ULONG, VARCHAR” allows to add any number of key-value pairs to any record of the table!

This fact totally kills “anti-reason”, which you could hear about Relational Tables: Tables have fixed number of columns. Indeed, with such solution, we get Table, which can have virtually 10, 100, 300 fields for different records.

Integration With Table

KeyValue for Table is integrated into Table in the following way:

  • Table owns KeyValue for Tables
    • If Table is deleted then KeyValue also will be deleted.
    • Table knows the count of such KeyValues.
    • Table is able to return you such KeyValue by its index or name.
  • If the record of Table is deleted, then related key-value pairs also are deleted.
  • When you insert key-value pair, then engine checks that RecID, specified in the Key, does exist in the Table.

CREATE

To create KeyValue for Table you should use

Notice, that you can create many KeyValues around single Table if you want.

DROP

To drop KeyValue for Table you should use

WRITE DATA

Let we have inserted into empty table T1 its first record, which get RecID = 1:

INSERT INTO T1 VALUES(1, 2, ..., 10 )

Now we can insert into its paired keyvalue-object:

KEYVALUE kv_T1 INSERT( '1.prop1':'value1', '1.prop2':'value2', '1.prop3':'value3',  ...   '1.prop500':'value500' )

Easy to see that this 500 pairs will be perfectly associated with the record with RecID = 1.

We can say the same in more short way (syntax sugar):

KEYVALUE kv_T1 INSERT( 'prop1':'value1', 'prop2':'value2', 'prop3':'value3',  ...   'prop500':'value500' ) FOR RECORD 1;
 
-- RECORD keyword is optional:
KEYVALUE kv_T1 INSERT( 'prop1':'value1', 'prop2':'value2', 'prop3':'value3',  ...   'prop500':'value500' ) FOR 1
1) Realize that Valentina DB can do this syntax sugar only because it is aware of this special kind of KeyValue, so it is able to provide special forms of SQL command.
2) Realize that inside you see a nice list of key: value pairs when using such syntax.

LastRecID() Usage

You can use LastRecID() also:

KEYVALUE kv_T1 INSERT( 'prop1':'value1', 'prop2':'value2', 'prop3':'value3',  ...   'prop500':'value500' ) FOR LastRecID() 

Please notice that we allow here to specify LastRecID() without parameters, but KeyValue knows its Table, so it will be able to execute this as Last_RecID_Of_Table( 'T1' );

READ DATA

Since KeyValue For Table is extension of KeyValue With Key, you can use GET LIKE search in Valentina SQL.

When you know RecID of some Table Record, you can do just

-- think about it as Find in KeyValue all keys of kind: '1.*'
KEYVALUE t1_kv1 GET LIKE ('1')