Category: Valentina SQL

[NEW] Generating Test/Bench Data for Data Focused Apps (Part 2)

We have implement Stored Procedure ‘GenerateDataFor()’, which solves the task described in the part1 of this article.

We have added into Valentina Wiki new section, where we will keep this and future other Stored Procedures By Paradigma Software.

On this page you can find link to WIKI page, which describes GenerateDataFor() procedure and contains download link.

[NEW] SQL expression function RAND_REGEXP()

We added a new SQL function RAND_REGEXP  into v5.5.6 (available in the night_build or upcoming beta). This is very powerful function that can generate random strings of any kind, following some regular expression.

This function is useful for test and bench data generation features we will be talking more about.

Our wiki has coverage of this new function: RAND_REGEXP

 

[NEW] Generating Test/Bench Data for Data Focused Apps (Part 1)

There are several database tools available for generating records for table T with some random data. Usually these tools can…

  • generate the test data itself;
  • format the data for replication of some bug

Yes, both very useful.  But as speed junkies and test pilots, we also want to use this feature to

  • generate data for use in benchmarks

The difference between test and bench data, is that for benchmarking today, tomorrow and months or years later, we should generate the same records into a Table. Otherwise how we can compare results of a benchmarks as computer scientists? For tests it is okay to use random values in records, but benchmarks require exactness.

We were going to add such feature into Valentina Studio, but then we started thinking about benchmarking the Valentina engine (made in C++).  It is clear then that we need such a feature right in the engine.  So how to implement it?
Continue reading [NEW] Generating Test/Bench Data for Data Focused Apps (Part 1)

[NEW] Valentina DB engine – SELECT … FOR JSON

We made the first step in the direction to popular JSON format.

Valentina SQL already did have extentions

  • SELECT … FOR XML
  • SELECT … FOR REPORT

Now we adding one more:  SELECT … FOR JSON.

We have upload 5.5b21 build where this feature is introduced. It works in same way as FOR XML. In the result you get cursor with a single record and a single TEXT field.

Example:

SQL query ‘SELECT * FROM tblCustomer FOR JSON’ returns

{
      "name": "tblCustomer",
      "fields": ["fldFirstName","fldLastName","fldCountry","fldPhone"],
      "records": [
           ["Peter","Thomas","Germany","111111"],
           ["Brian","Hill","USA","222222"],
           ["Simon","Smith","Italy","333333"],
           ["Chris","Maxwell","France","444444"],
           ["Greg","Silver","France","555555"],
           ["Jerry","Lucas","USA","666666"],
           ["Mark","Lord","Canada","777777"]
      ]
}

[NEW][VSQL] MAIL Command

We have add a new command for Valentina SQL.

The main goal is to be able use Valentina Server as generator of PDF and/or HTML reports and sending them by email directly from VSERVER’s stored procedure. Besides, this command can be called by Event Scheduler of VSERVER or by a database or table trigger.

vext_mail
 : __MAIL
   __FROM character_string_literal_or_var 
   __TO character_string_literal_or_var 
   __SUBJECT character_string_literal_or_var
   __BODY character_string_literal_or_var
  [__ATTACH vext_attach_list]
      __SMTP character_string_literal_or_var
      __PORT character_string_literal_or_var
     [__USER character_string_literal_or_var,
      __PASSWORD character_string_literal_or_var]
     [__SSL truth_value_or_var]

vext_attach_list
 : character_string_literal_or_var AS character_string_literal_or_var , ...

character_string_literal_or_var
 : character_string_literal
 | variable_name

uint_or_var
 : UINT
 | variable_name

truth_value_or_var
 : truth_value
 | variable_name

truth_value
 : TRUE
 | FALSE

Continue reading [NEW][VSQL] MAIL Command

[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.