Table of Contents
Valentina allows you to create in any Table Virtual Fields also known as Calculated Fields or Table Methods.
Table Methods do not store their values on the disk. Instead, values are calculated only when needed. Table method, although, still can have an index that is stored on disk.
Table Method works and looks exactly like regular Table Field. You can use them everywhere you can use Fields in almost the same way. Almost because there are some limitations for Methods. In particular, you cannot assign a value to a Method, so you can consider them to be read-only fields.
NOTE: It is similar to most computer languages that contain VARIABLES and FUNCTIONS. You can read the value of variable and assign a new value to it:
k = var var = k
But you can only read the value of function:
k = sin(x) sin(x) = 0.123456 = 0.123456 // ERROR
In Object-Oriented languages, data members represent what a class KNOWS, and methods represent what a class CAN DO. You should think about Table Methods as a Function/Method/Code attached to a Table and which can be called to calculate a value.
Since Methods and Fields are elements of a Table, they share the same namespace. You cannot have Fields and Methods with the same name in the same Table.
Table Methods have advantages over just CREATE INDEX way that uses most of DBMS, because it allows for developer specify in the SQL query that Method by its name directly without need is in hope for SQL resolver/optimizer. Also, you can SELECT such fields while you browse table. You can think about Table Methods as small stored procedures of the Table scope.
Method result can be of any supported Field type (except BLOB types). When you specify the result type of Method, you force Valentina do type casting.
if you specify “sin(fld)” returns SHORT then you will get only the integer values -1, 0, 1. If the result size of a calculation is bigger than the specified result type, the result will be truncated.
Indexing of Method
Methods can have the flags ‘Indexed’ , ‘Unique’ and ‘IndexByWords’. This allows you to create several different indexes for one real field, although it will look like one Field has one Index.
For example, you can have the field «Name». If you set an index for this field then the index will be case sensitive (i.e., it will consider Jon and JON as different names). Very often you may want to have a case-insensitive index for this field. To resolve this task, you can define an indexed Method with the name, for example, 'NameUpr' and specify its text as “Upper(Name)”.
As a result, an index will be built which gets data from the real field «Name» converted to uppercase. So you can do a case-insensitive search by this index while retaining the original data as case sensitive.
Note that a Method can use several fields of a Table for calculation of a new value. This means that Methods allow you to have COMPOUND indexes also (i.e., an index composed of more than one field).
You can use Methods to create optimized indexes. For example, if you have a String but want search only by the first 4 letters, then you can make an indexed Method: “LEFT(fld,4)”.
- An Indexed Method represents 100% of the functionality of this feature of standard SQL or the SET INDEX feature of FoxPro. At the same time, a Method has more power because we can use it to read the values that comprise the index.
Methods and NULL Value
If the value of a real Field is NULL, then the result of the Method is also NULL. The result will also be NULL if a calculation meets some prohibited case, such as division by zero.
Methods can have the flag ‘Nullable’ like real fields. In this case, information about if a value is NULL will be stored on disk - taking 1 bit per record. On the other hand, you get the ability to do a very fast search of NULL or NOT NULL values without recalculating all of the records.
This is another advantage of Methods over the RDBMS feature SET INDEX.
Access of Table Methods
Since a Method looks like a COLUMN of a Table when we display it, logically we work with them like with Table Fields. So real Fields and virtual Fields are kept in one common array. The order of Fields and Methods in this array is the order of creation. You can specify any order using a Cursor.
Using Table Methods
Table Methods significantly extend flexibility of database kernel. They can be used for the following tasks
Several Indexes On Field
Let you have field “f1” and you want to have 2 or more different indexes for its data. To do this you define INDEXED BaseObject Methods. Method does not store own values on disk, but its index will be stored on disk, so in fact, we have one field column and several index files.
You can use this if you need:
- have an index for a field as in original so in upper case.
- have index by words and in the same time NOT by words to be able to sort strings.
i.e. index which is built using data from SEVERAL fields. To do this you need to define INDEXED Method that calculates its value using data of 2 or more fields.
Optimization of index
Let you have a string field “F1” with length 150 bytes, but want index only first 7 bytes. To do this you define Method = “left(f1, 7)” and make it indexed. Field F1“ must not be indexed.
Case With String Fields
Very often you need be able to search (sort) string field as case-insensitive or case-sensitive.
For this, the database should have an index for the field in UPPER or LOWER case.
So you define a Method M1 with the expression “UPPER(f1)” and make it indexed. Now in the query, you can use M1 for searches and sorting, and F1 to display strings in the original case.
s = "test STRING" sUpr = toUpper( s ) SqlString = "SELECT f1 FROM T WHERE M1 = '" + sUpr + "'" res = db.SqlSelect( SqlString )
Note, that the match string must be converted to upper case before you will use it in SQL query.