Category: Valentina SQL

[NEW] DEFAULT clause extended by METHOD(‘const_expr’)

We have extend Valentina SQL by non standard feature. DEFAULT clause now has form DEFAULT METHOD(‘const_expr’).

This step increases declarative power of DDL part of VSQL and, therefore, allows you do less job later working with inserts and updates.

You can use in the expression built-in Valentina functions and UDFs that not depends on other fields. The most useful examples are:

* now()
* UUID()
* nextval( sequence_name )
* current_user_name()

Compatibility:
* this is not standard syntax.
* PostgreSQL have similar syntax and behavior, but it specify expression just in the literal: DEFAULT STRING_LITERAL. This cause ambiguity.

[NEW] Localisable ENUM Type

Ladies and Gentlemen!  
The first time in the world!  🙂
Localizable Enum Type in DBMS!  

We already many months have working ENUM type in 5.0 branch of Valentina. Let me remind that ENUM type is not from SQL standard, so different DBs implement it in different way if at all implement. We have implemented it using CREATE TYPE command of SQL Standard. And we have implement ENUM in way similar to PostgreSQL, because it is the most correct: you just CREATE TYPE ENUM once and later using it in all places of your database.

mySQL, in contrast, defines ENUM as part of a particular Table, right in the CREATE TABLE command. This is not good of course, because then you cannot use this type in other tables or for variables of Stored Procedures.


CREATE TABLE sizes (
name ENUM('small', 'medium', 'large')
);

It is interesting that such mature database as Oracle do not have ENUM type.

All these existed implementations have one big problem from our point of view: such enum types contains string values of only one language. Below we will describe our solution.

Continue reading [NEW] Localisable ENUM Type

[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