Valentina SQL TIPs

Conventions

In the SQL database world, it is a standard to represent SQL keywords in UPPER CASE.

In all other cases, you can use mixed case or lower case. As you can see in the example below, lower case makes the statements more readable.

NOTE: Many SQL textbooks show everything in upper case, but don't let that fool you.

Example:

SELECT 
   f1, f2, f1+f3
FROM 
   T1
WHERE 
   f1 > 100

TIP: Define names for Tables, Fields, ... with corresponded prefix

TIP: Define names for ObjectPtr and FK fields with suffix "Ptr"

SQL Query Formatting

Formatting FROM Clause

TIP: We suggest the following format for most queries that build JOINS:

Example:

SELECT 
   *
FROM 
   T1 JOIN T2 ON T1.ID = T2.PTR
WHERE
   f1 = 5

Example:

SELECT 
   *
FROM 
   T1 JOIN T2 ON T1.ID = T2.PTR
      JOIN T3 ON link_T2_T3
WHERE
   f1 = 5

Example:

SELECT 
   *
FROM 
   T1 INNER JOIN T2 ON T1.ID = T2.PTR
      OUTER JOIN T3 ON link_T2_T3
WHERE
   f1 = 5

Example:

SELECT 
   *
FROM 
   (T1 INNER JOIN T2 ON T1.ID = T2.PTR
       INNER JOIN T3 ON link_T2_T3)
       OUTER JOIN T4 ON T3.f5 = T4.f7 
WHERE
   f1 = 5

Formatting WHERE Clause

TIP: Put each condition on a separate line.

WHERE 
    T1.f1 = 55 AND 
    T1.f3 LIKE 'sfsdf' AND 
    T2.b1 > 17

TIP: Group the search conditions for the same table together.

Seriously, using another style will NOT improve the readability of your SQL code. Compare this bad-style query

WHERE 
        T1.f1 = 55  
    AND T2.b1 > 17
    AND T1.f3 LIKE 'sfsdf'

with this one: (Actually you can use even more explicit grouping style of the same tables conditions as illustrated in the next example)

WHERE 
    T1.f1 = 55 AND
    T1.f3 LIKE 'sfsdf'
AND 
    T2.b1 > 17

TIP: Make it easy to comment out each condition.

in the example of the bad style (above), you cannot simply comment out conditions because the AND keyword will also be commented out:

WHERE 
//    T1.f1 = 55 AND 
    T1.f3 LIKE 'sfsdf' AND 
    T2.b1 > 17

If you need this ability (usually during work in the SQL Editor of Valentina Studio) you can write query as shown below. Note that using this style you can easily comment out any condition and the query will continue to be valid.

WHERE 
        T1.f1 = 55  
//  AND T1.f3 LIKE 'sfsdf'
    AND T2.b1 > 17

SELECT Conditions

TIP: Differ Link And Search Conditions

TIP: Always prefer to specify a link condition in the FROM clause instead of WHERE clause

You can specify a Link Condition both in the WHERE clause and in the FROM clause:

SELECT * 
FROM T1, T2
WHERE T1.id = T2.ptr AND T1.f1 = 55

SELECT * 
FROM T1 JOIN T2 ON T1.id = T2.ptr
WHERE T1.f1 = 55

Always prefer to specify link conditions in the FROM clause. The reasons are:

this makes your query SQL92 standard compliant.
for queries with two or more OUTER JOINS this is the only way to specify it correctly.
this explicitly separates your link and search conditions visually.
link conditions in the FROM clause more explicitly show the path of join (see TIP: join path).

NOTE: Actually you can specify search conditions in the FROM clause, but this is not standard practice and makes a difference only for complex OUTER JOINs.

TIP: Write join paths using natural way

Let`s assume you have three tables T1, T2, T3, linked as T1 to T2 and T2 to T3.

Assume you want to make a JOIN of these three tables. Now ask yourself what is the most natural PATH to think about this join? Right, it is:

join T1 and T2 using T1.id = T2.ptr, as result we get T12 join table.
join T12 with T3 using T2.id = T3.ptr, as result we get final T123 join table.

So the natural form of this SQL query is:

SELECT * 
FROM 
    T1 JOIN T2 ON T1.id = T2.ptr
       JOIN T3 ON T2.id = T3.ptr

Note, that the line T1 JOIN T2 ON T2.ptr = T1.id does not look natural, because it breaks the order “left to right”.

When you write your FROM clause you can keep your finger on T1, then move it to T2, then move it to T3, and write join in the same PATH.

TIP: Custom Error Code for Stored Procedure

This TIP is the result of a discussion on the Valentina list with Bart Pietercil about a way to add own error codes into Valentina kernel

Hi Bart,

We do not see any need to provide third XML. This may bring lots of mess. For example:

vserver works for 2 companies on ISP. Working with XML Admin of vserver needs taking care about location of this XML file then info vserver about it. But each company may want to have its own XML file, so we get need to make engine quite complex to handle this.

if you open your db with procedures without vserver, but directly by vstudio, then ops, you do not have your error codes. Right? This is BAD. So you need somehow to direct Vstudio to that XML file? Bad.

The next our idea was

Okay, lets store custom Error Codes into .vdb file as we do with tables, triggers, SP, … Wow, looks not bad!
This way automatically solves the problems with:

parallel work of 2 companies on single vserver
direct open of db by vstudio – and error codes are here!
move .vdb file to another computer and error codes are still here!!

Nice Nice Nice.
And for this we could add 3 new SQL commands

ADD USER_ERROR errNumber, errString, errLang
DROP USER_ERROR errNumber
SHOW USER_ERRORS

You can easily get these errors into PHP and RB to build and fill structures of these languages to show strings to end user. Really, what is the difference where you get info from to fill structures? From XML or from cursor as a result of SHOW USER_ERRORS ?

Finally we have to think:

Why should WE do this inside the engine? You have ALL TOOLs to solve this task in your solution with practically THE SAME EFFECTIVENESS. Look.

CREATE SYSTEM TABLE BartErrorCodes
(
    errNumber       INTEGER INDEXED,
    errString       VARCHAR(2044) 
    errLanguage     String(20) OR USHORT        // name/code OF LANGUAGE
)

Then you can do just

SELECT * 
FROM BartErrorCodes
WHERE errLanguage = 'French'

Fill structure of PHP or RB and … your task is solved.

TIP: Hierarchical query and object-pointer operator.

Mixing hierarchical query and object-pointer operator you might be confused getting an error

0x81502 Link with name "xxx" not found.

Let's see what happens using this example:

CREATE TABLE t1 ( f1 long );
CREATE TABLE t2 ( id long, ptr long, ptr2 ObjectPtr CONSTRAINT l2 REFERENCES t1 ON DELETE SET NULL );
 
INSERT INTO t1 VALUES( 1 );
INSERT INTO t1 VALUES( 10 );
INSERT INTO t1 VALUES( 2);
 
INSERT INTO t2 VALUES( 1, NULL, 1 );
INSERT INTO t2 VALUES( 2, 1, 2 );
INSERT INTO t2 VALUES( 3, 1, 3 );
 
ALTER TABLE t2 ADD CONSTRAINT PK PRIMARY KEY (id);
ALTER TABLE t2 ADD CONSTRAINT l1 FOREIGN KEY (ptr)  REFERENCES t2(id) ON DELETE CASCADE;

In other words:

t1	t2
1	⇐ root
10	⇐ –item1
2	⇐ –item2

Now we want to get t1.f1 values for all descendants of 'root' record (t2 table).

This solution seems to be correct:

  SELECT 
    ptr2->f1
  FROM
    (DESCENDANTS OF 1 USING l1)

But it does not work - you get an error “unable to find a link named ptr2”.

Actually “FROM (descendants…)” is a “FROM subquery”. And it produces some indirect table which can not have a pointer or any other link types. The result is some snapshot of the original table. So ptr2 there is not an ObjectPtr but simply ulong field. And you get an error trying to treat it as an ObjectPtr.

Instead you may choose any of the following ways:

Redesign the query to usual joins.
Subquery.

Something like this for “join” approach:

  SELECT
    ptrTable.ptr2->f1
  FROM
    t2 keyTable JOIN t2 ptrTable ON keyTable.id = ptrTable.ptr AND keyTable.id = 1

And something like this for “subquery” approach:

  SELECT
    ptr2->f1
  FROM
    t2
  WHERE
    RecID IN ( SELECT RecID FROM (DESCENDANTS OF 1 USING l1) )

Table of Contents

Valentina SQL TIPs

Conventions

SQL Query Formatting

Formatting FROM Clause

Formatting WHERE Clause

SELECT Conditions