Category: Valentina SQL

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

 

New Docs About ENUM Type in v5.0

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:

[TIP] Protection from SQL Injection Attack.

This tip is expired by article two-steps-forward-to-more-secure-applications. The First part of the article discusses  SQL Injection Attack and how to develop code protected from it.

After read of the article you will know that to easy way for protection – is usage of SQL binding. Wow? One more reason to ALWAYS use SQL binding!  Other well known reason are

  • reduces CPU time as for client so for server, because no string concatenations on the client side, and less job for parser on the server side.
  • enables usage of Query Pool, because SQL command is the same byte to byte.

Altering ObjectPtr

ObjectPtr field contains RecID values of some “target” table. If you need to change ObjectPtr in order to point another table those values are not valid anymore – so default behavior is to nullify them.  But sometime it looks to be too strong. For instance – changing  field or link name or on-delete policy should not cause such data zeroing.

There are two ways to keep existed values altering ObjectPtr:

  1. There is a global property – OnChangeTypeZeroPtr. It is set to true by default. But you may change it with following statement – “SET PROPERTY OnChangeTypeZeroPtr TO false”. After this altering ObjectPtr will prevent zeroing data (sure it will be nulls for records which are absent in the new target table).  This property is not “schema-storable” – so you should set it up on each run if you need it.
  2. The first approach is a global setting – so you will be able to keep some ObjectPtr values even changing “target” table. So we implement one more way – much more simple because the only thing you should keep in your mind is – the values will be preserved until you touch “target table” ObjectPtr property (because all values will be valid for the same target anyway).

[NEW] REGEX_REPLACE() function.

In 4.5.1 release we have add a new REGEX_REPLACE() function  to Valentina Expresions, which can be used in Table Methods, SQL and therefore, in Reports.

Up to now, we was able to do searches with the help of REGEX operator:

… WHERE fld REGEX ‘ab.[cdf]*’

New function brings the awesome power of REGEX to manipulate by strings stored in the database! Now we can not only search using REGEX, but modify strings! You can do that in the

  • SELECT EXPR, …
  • UPDATE TABLE T SET fld = EXPR

where EXPR is REGEX_REPLACE() function or contains it as a part.

Read details about this function here.

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);