Working with big vDatabases

Valentina is a perfect choice to work with a big data. It employs vertical storage approach. It means – each column has own virtual file to be stored in. Most of db vendors use a horizontal storage – it stores a table data in record-by-record manner.

There are some vertical storage advantages:

Continue reading Working with big vDatabases

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.

Hierarchical (Recursive) Queries in Valentina SQL

There are three new kind of queries specially designed  for recursive (hierarchical) tables.

Such kind of tables are widely used. It could be human relationships, threaded forums and anything which might be described as tree-like data.

There are few common SQL ways to operate with such structures but all of them are too far from perfect.

Valentina introduces special kind of query which looks much more clear, natural and readable.

Syntax:
vext_recursive_table         -- v4.1
    :   {ANCESTORS | BROTHERS | DESCENDANTS} OF vext_root_objects
        [{TO|ON} LEVEL UINT] USING link_name [WITH {ROOT | ROOTS}]

vext_root_objects
    :   UINT
    |   variable_name
    |   ( search_condition )

Examples:

ANCESTORS OF (RecID IN(4,5)) ON LEVEL 1 USING l1
BROTHERS OF 4 ON LEVEL 2 USING l1
SELECT * FROM (ANCESTORS OF (RecID IN(4,5)) ON LEVEL 1 USING l1)  WHERE f1 = 'Branch2'

See also:

* WIKI: Valentina SQL Reference: Hierarchical (Recursive) Queries for description of exact syntax of these commands and examples.

* WIKI: Article Hierarchical (Recursive) Queries in Valentina SQL

Using of “Instead-of” triggers

Such kind of trigger may be applied to views only. It could be used for insert, update and delete operations as usual.

Example:

CREATE TRIGGER trigger1
INSTEAD OF INSERT ON v1
FOR EACH ROW
BEGIN
INSERT INTO t2 VALUES( 1 );
END ;

“trigger1” will be fired on attempt to insert record in the view “v1”.
Starting from v 4.1 you may use ‘NEW’ and ‘OLD’ descriptors within this kind of trigger.
Views are always read-only – so using the trigger is a nature way to overcome this restriction.

Example:

CREATE TABLE t1 (f1 LONG, f2 TEXT(1024));
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE TRIGGER trigger1
INSTEAD OF INSERT ON v1
FOR EACH ROW
BEGIN
INSERT INTO t1 VALUES( NEW.f1, NEW.f2 );
END ;


INSERT INTO v1 VALUES ( 1, ‘abc’);

SELECT * FROM v1;
————-
f1 f2
————-
1 abc
————-