One use of 4.9 Valentina ADK have told us that many his users have databases on DVD in 4,9 format. So if he will try change his app to Valentina 5.0, then how users will be able work with this read-only databases?
To resolve this, we have improve Valentina engine. Now it will make attempt to work with read-only databases in older format as is, without conversion.
Important to note, that this will work fine for 5.x engine opening 4.x databases.
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
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.
Frank have contact us with request add ability for V4CC (Valentina for Cocoa) developers to specify the location of journal of a database. This is important for sandboxed applications, which on default can access only their sandbox folder and a file(s) that user specify explicitly.
PROBLEM is that if a user choose somedb.vdb file in the SelectFile dialog , then Valentina engine needs yet to create a journal file near to .vdb file. But for a sandboxed application this is prohibited by OS X. This is why developer want to be able specify another location for journal file.
Btw, this problem exists more of year for SQLite database that is used e.g. in CoreData of Apple, when it is used by a sandboxed app. Strange, but the only advice from Apple is – disable journal file.
We have spend couple of days to add into C++ level and into V4CC ADK this feature. Rest ADKs soon.
Now you can write the following in V4CC:
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.
On request of users we have add 3 new SQL functions:
* UNIX_TIMESTAMP() – read more …
* FROM_UNIX_TIMESTAMP() – read more …
* MURMURHASH() — read more …
After some benches we have discover that loop on small joins (when only one record on the left and few on the right) is not fast enough.
We have two major algorithms internally and we have discover that the first takes only 50 seconds on 10000 loops, while the second takes hundreds of seconds…So problem presents in the second algorithm.
Improved.
Now the second algorithm takes 120 second. And we can add yet choice condition to choose the first for such case of small joins…
I think we will be able to improve the second alg yet to 70-80 sec. And may be with more complex changes it is possible to speed both after that …
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
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.
Today I added new reference pages to the wiki of e.g. SQL grammar about a new feature of Valentina DB for 5.0: the ENUM type.
This is the first step towards a CREATE TYPE command based on the SQL 2003 standard. Next we can add composite types, collection types and inheritance.
We think that Valentina can have a more effective and flexible ENUM implementation compared to PostgreSQL or mySQL. One example is that Valentina can use 1 byte to store an enum value, while mySQL – 2, PostgreSQL – 4.
We are finishing now integration of this feature into Valentina Studio for both Valentina DB and PostgreSQL plugins. Database types in Valentina Studio are supported through a plugin architecture.
These are major entry points in the Valentina Wiki to check:
We have realize problem of unicode text normalization and have implement tools into 4.9.1 to work with normalization issue. Detailed description of this issue is given on the following wiki page.
We have note that put_ID() should not be in the public interfaces of Schema Objects, because developer should not be able change ID of a scheme object.
We have check our sources, and found that only I_Field interface did have such method. So we move it into internal I_FieldEx interface. In the C++ header this method marked as DEPRECATED, and do nothing now. We believe that nobody from C++ developers have used it.
We will consider future ability to do “SET PROPERTY ID of Object name” via SQL, but this will be recommended for use by our own SQL scripts only.
We have discover that interface of VLink miss Count property to easy ask VLink how much pairs it contains.
Now this property is implemented in the vkernel. It works for all kinds of links (Binary, ObjectPtr and ForeignKey). It is implemented in effective way — only header of index is asked actually.
Adding of this property do minor extension of VSERVER protocol also.
We have realize that if to add simple header to Warnings.log that describes what this log contains, when it should be used, and how it can be disabled for RELEASE then this will help to developers and remove often questions about this.
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”