Table of Contents
Valentina SQL TIPs
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.
SELECT f1, f2, f1+f3 FROM T1 WHERE f1 > 100
When you develop your database structure, it is considered good practice to use a special prefix that helps you to make the distinction between names of Tables, Fields, Links, Procedures, …
- TABLES: tblPerson, tblPhones, …
- FIELDS: fldFirstName, fldLastName, …
- LINKS: link_T1_T2, linkPersonToPhone, …
- you simplify a lot the READABILITY of your SQL.
- you avoid possible conflicts with SQL keywords. For example, people often try to use as a fieldname “user”, “reference”, … while these names are keywords and as such have a special meaning in SQL.
- working in the SQL Editor of Valentina Studio (Pro) auto-completion will be more accurate. Its enough to type “tbl” and you will see only tables (as long as you used “tbl” as prefix of course), but not fields or links.
Assume you have two tables T1 and T2. Table T1 has a key field “ID”. And you want to establish a Foreign KEY link from T2 table. So you need to create in table T2 a field of the same type (usually) as ID and name it as ????
Well, in most SQL books, and between users of other RDBMS, you can meet this convention. They propose to name it as “T1_ID”.
On this page Pointer Links we explain that a Foreign Key has a POINTER nature, so the most natural name for this field would be “T1_Ptr”, or other (more concrete) examples: PersonPtr (pointing to tbl_Persons), CustomerPtr (pointing to tbl_Customers), …, as such helping the reader of your SQL or Diagrams understand the structure of your database.
SQL Query Formatting
Formatting FROM Clause
SELECT * FROM T1 JOIN T2 ON T1.ID = T2.PTR WHERE f1 = 5
SELECT * FROM T1 JOIN T2 ON T1.ID = T2.PTR JOIN T3 ON link_T2_T3 WHERE f1 = 5
SELECT * FROM T1 INNER JOIN T2 ON T1.ID = T2.PTR OUTER JOIN T3 ON link_T2_T3 WHERE f1 = 5
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
WHERE T1.f1 = 55 AND T1.f3 LIKE 'sfsdf' AND T2.b1 > 17
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 an 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
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
When you build a SQL SELECT query on a few tables, keep in mind that in the WHERE clause you can have two types of conditions: Link Conditions and Search Conditions.
Link Conditions are conditions that allow a DBMS to join two tables. We name them link conditions because, by their very nature, they are based on a Foreign Key link, or an ObjectPointer link.
- Foreign Key: WHERE T1.id = T2.ptr
- ObjectPtr Key: WHERE T1.recID = T2.ptr
- BinaryLink: cannot be used in the WHERE clause. Only in the FROM.
Search Conditions are conditions that play the role of a filter for records. For example:
- WHERE f1 = 'aaa'
- WHERE f1 BETWEEN 1 AND 10
Is there a significant difference between these types of conditions? The answer is, not surprisingly, YES.
1) The main difference is that the DBMS uses Link conditions to build JOINs of two or more tables.
In general you can understand the work of the DBMS as:
- at first the DBMS builds a Join Table using the link conditions.
- then the DBMS applies the SEARCH conditions to filter the records from the build Join Table.
In reality, a smart DBMS can apply some search conditions before a join is made in order to reduce (often significantly) the number of records used in the JOIN and as such optimize the speed of the query.
2) Another difference is that you can specify Link Conditions both in the WHERE clause and in the FROM clause. In order to obtain the best results you should always prefer to specify Link Conditions in the FROM clause. See details in the TIP: join vs where
3) Often you can distinguish Link conditions from Search conditions by the syntax they use. You can identify link conditions by the fact that they specify on one side a KEY field of T1 table, and on the second side a PTR field of T2.
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. 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.
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.
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
We do not see any need to provide third XML. This may bring a lots of mess. For example:
- vserver works for 2 companies on ISP. Working with XML Admin of vserver need take care about location of this XML file then info vserver about it. But each company may want to have own XML file, so we get need 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 direct Vstudio to that XML file? Bad.
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 solves automatically 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 errors code 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
And 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 difference where you get info from to fill structures? From XML or from cursor as result of SHOW USER_ERRORS ?
Finally we have to think:
Why WE should do this inside of engine? You have ALL TOOLs on hands to resolve 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.
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:
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.
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) )