Author: Ivan Smahin

Choosing appropriate database’s segment size

Each Valentina’s volume consists of set of segments, even internal service-data placed in such segments.

Database storage is implemented similar to some file system. There are volumes (.vdb, .dat, .blb, .ind, .tmp) and there are some embedded files on that volumes (all the data like field-data, indexes and so on go to that files). Each volume operates with own segment map – so we can find (and allocate new) segments for particular embedded file easy and fast.

Continue reading Choosing appropriate database’s segment size

Index search warnings

There are many find-like methods which are able to employ indexes to speed up the searching (FindLike, FindRegEx, FindContains, and so on). Of course, the field must be  indexed and search preference is set to kPreferIndexed.

But currently there are two issues when index search may bring a wrong result:

1.Index keeps only first 256 symbols of the value – so rest of the symbols are just ignored in such a search.

2. Index-By-Words index and multi-word searching pattern.

Some of the methods like FindRegEx don’t use an index-search but scan the table instead if any of the above occurred. The rest of the methods are less strict choosing the searching algorithm. Now you will get the warning in such cases and you can switch algorithm to not-index search (kPreferNotIndexed) if it is relevant (less speedy but more accurate). Also you should consider to change index type of such a lengthly fields to the index-by-words.
Warning examples:
“Index search in the first 256 symbols only : FindContains t1:f1”
“Index-By-Words search : FindContains t1:f1”

vClient becomes thread-safe (!)

Now you should not synch vClient API calls  – vClient do it self and you are free to access and operates with vClient’s remote objects from different threads similiangly.

In particular, vStudio operates with remote connections via multiple threads  and it was a source of some glitches and unexpected errors especially with a long queries.  Now there are no more errors like “EOF reached…” or “Broken packet received…” which were result of incorrect thread synch.

Altering ObjectPtr

ObjectPtr field contains RecID values of some “target” table. If you need to change ObjectPtr in order to point another table those values are not valid anymore – so default behavior is to nullify them.  But sometime it looks to be too strong. For instance – changing  field or link name or on-delete policy should not cause such data zeroing.

There are two ways to keep existed values altering ObjectPtr:

  1. There is a global property – OnChangeTypeZeroPtr. It is set to true by default. But you may change it with following statement – “SET PROPERTY OnChangeTypeZeroPtr TO false”. After this altering ObjectPtr will prevent zeroing data (sure it will be nulls for records which are absent in the new target table).  This property is not “schema-storable” – so you should set it up on each run if you need it.
  2. The first approach is a global setting – so you will be able to keep some ObjectPtr values even changing “target” table. So we implement one more way – much more simple because the only thing you should keep in your mind is – the values will be preserved until you touch “target table” ObjectPtr property (because all values will be valid for the same target anyway).

SNMP and Valentina Server

There is a new feature in Valentina Server – SNMP agent.

Currently we support only get() and get_next() SNMP requests for the following oids:
sysDescr(“1.3.6.1.2.1.1.1.0” )
sysUpTime(“1.3.6.1.2.1.1.3.0” )
Not much info but it makes a possibility to monitor vServer over SNMP.

For now, SNMP facilities are available only for Windows.

You should specify SNMP port in the server ini-file (default port is 161, 0 means turn off SNMP agent):

…

[SNMP]
Port_SNMP=161;             SNMP port to listen by VServer.
;                                             0 – do not use a SNMP port.

…

Internal optimization for views and sub-queries.

In some cases we can avoid copying sub-query result to the temporary table. We can even use sub-query cursor “as is” – so it could be used as a result of the whole query. In other words there  is almost zero overhead against a simple query.

For now such optimization can be applied if a query:

  1. contains only “*” item in the select list clause
  2. has no “where” clause
  3. has no “group by” clause
  4. has no “having” clause
  5. has no “distinct” clause
  6. is not a part of some join operation
  7. has no “order by” clause
  8. has no binding values
  9. sub-query is not a “union/except/intersect” result

Examples:

SELECT * FROM (SELECT * FROM t1 WHERE f1 > 5);

SELECT * FROM (SELECT t1.f1, t2 f1 FROM t1 JOIN t2 ON l1 WHERE t1.f1 > 5 AND t2.f1 <3);

Improvements for stored procedures and binded queries

It was a huge improvement to solve the problem with a “second run” in stored procedures and binded queries.  Sometimes there was an incorrect behavior because of “over-prepared” nodes. There was a workaround with “EXECUTE” statement (which is actually developed  to prepare any SQL statement and execute one from another SQL statement). So now this workaround could be omitted.