Category: Valentina SQL

[SQL][REPORTS] Improved work with FORMAT()

Work on Valentina Reports have push us to look more deeply on our FORMAT() function of Valentina SQL.

Keep in mind, that Valentina Reports DLL is tightly integrated with Valentina Kernel DLL and use its power. In particular, formatting in Valentina Reports is based on this FORMAT() function, which, in its turn, is based on IMB ICU library features.

Keep in mind, that even if Valentina Reports use as datasource e.g. mySQL or PostgreSQL server, then it still use Valentina Kernel to do many job, including this formatting.

Problem was found that if function FORMAT() is used over DATE or TIME columns then in result ICU produce “garbage” time and date values correspondently. After some research we have found way how to cut off this garbage. So now reports and SQL function itself produce expected results.

Will be available for after 5.0b41 build and newer.

[Imp] VSQL: SHOW commands now can accept variables instead of e.g. db_name

To support our own development of admin task for vserver, we have extend SHOW commands to accept variables instead of db_name, tbl_name and so on.

When variable starts with @ or @@ symbols, engine easy can recognize this. But you can also use a local variable of Stored Procedure. Algorithm at first checks if IDENT is the name of a local variable. If no,  then consider this as db_name or tbl_name according to grammar of a SHOW command.

Example:

Some_Stored_Procedure_Text()
…
set CurrentDbName = ‘accounting’
execute ‘SHOW EVENTS FROM CurrentDbName’
…
end

[NEW] SHOW [VSERVER] LOG command

We have added a new SQL command that works with VServer only to show tail of VServer.log file. (5.0b30)

TODO Valentina Studio now have a special panel where user can easy see this log using new command.

All together this simplify a lots work with remote VServer, especially if you do not have SSH access to its file system.

[NEW] VServer + VStudio now can show NOT registered dbs

We have extend SHOW DATABASES command to show also not registered databases located in the VServer/databases folder. To implement this VServer now is able iterate this folder to see files with .vdb extension.

Valentina Studio now take this in attention and show such not registered databases with a special icon. Also you get contextual menu with command “Register database”.

Refresh button of VStudio is able issue SHOW DATABASE command to check folder for new files if needed.

[Imp] LINK/UNLINK commands now have optional suffix to suppress errors

Thorsten have point us that for his task it is not comfortable when command

LINK RECORD (1) OF T1 TO RECORDS(3, 7, 9) OF T2

throws error and stops, if link 1-7 already exists.

We have agree and improve these commands adding suffix

LINK … [IF NOT EXISTS]
UNLINK … [IF EXISTS]

This suffix is similar to CREATE/DROP commands. If it is specified, then error is not fired and command continue work for other pairs of links.

[Imp] CREATE BINARY LINK now does not accept SET NULL, SET DEFAULT by grammar. NO CASCADE added

Thorsten have point us that WIKI page about this command looks strange, because uses

Referential_action
: NO ACTION  (alias to RESTRICT)
| RESTRICT
| SET NULL
| SET DEFAULT
| CASCADE

Now SQL grammar have one more rule vext_referential_action_for_link without “SET NULL”, “SET DEFAULT” and added “NO CADCADE” for Binary Links.

Continue reading [Imp] CREATE BINARY LINK now does not accept SET NULL, SET DEFAULT by grammar. NO CASCADE added

[IMP] Recursive query now can use RDB-link defined on the fly

While Valentina have very cool syntax for recursive queries, one Valentina developer have point us that it cannot be used in his task, because he do not have predefined link with name. Taking this into account, for v5.0 we have extend syntax of recursive query

from    USING link_name
to        USING (link_name | search_condtiion)

Continue reading [IMP] Recursive query now can use RDB-link defined on the fly

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

[NEW] ORDER BY in aggregative functions First()/Last()

SQL Standard allows to SELECT only fields mentioned in the GROUP BY and expressions based on aggregative functions. You cannot SELECT a normal field. But sometimes you may very want to do this. Question is what to do in this case.

In this new Valentina Wiki article, we have describe in detail this problem and gave THREE solutions. The third solution is new for v5.0 and it works x400/ x50 times faster of the first two correspondently in tests on the database of our customer.

The third solution, uses idea from ORACLE database actually: FIRST()/LAST() functions with own ORDER BY to be used inside of each group. It seems mySQL and PostgreSQL do not have any way to resolve this task in such effective way.

[NEW] Valentina 5.0 adds Sequences.

Postgre/Oracle users are used to use Sequences to generate values of Primary Key of tables. mySQL/Access users are used to use auto increment flag on a PK field for the same task. Generally speaking Sequences are more powerful and flexible, but more verbose.

We have add Sequences into Valentina 5.0 in 99% same way as they work in PostgreSQL, so now Valentina developer can choose between auto-increment and sequences if they develop true Relational model.

You can read details here:

Reminder: If developer uses modern ObjectPtr and Binary links of Valentina DB, then he can avoid PK-FK headache at all.