The expression editor

Result of expression

Although it is possible to write any SQL expression with extended subexpressions, the final result returned by the expression must be a value. Or in other words, a single record with single column. It is obvious when you remember, that the result must be presented as a single value in a record of a table.

A common mistake is an expression which usually returns a single value, but under condition can return more than one record. It can be easily solved by adding "LIMIT 1" to the end of the expression.

Syntax, keywords, built-in functions

You may use in the almost every function or a keyword known from MySQL. Some functions are not fully compatible, but we still work on it to minimize the problem. But there are also some differences we want to keep:

• JOIN keyword is not supported, but join operations are allowed. For example construction SELECT COUNT(1) FROM `A`, `B` is allowed. Moreover InstaDB provides arrow (->) syntax, which can replace and simplify many join constructions. Arrow syntax is described below.

• asterisk (*) is banned, as there is no need for using it, when the final result of expression must be a single value.

Column names

It is required to type fully qualified column names in the expressions. It means, that instead of writing SELECT column FROM table you have to write SELECT `table`.`column` FROM `table`.

As you probably noticed, column and table names in the above example are surrounded by grave accent marks (`). Unless you use only ASCII alphanumerical characters, these marks are not required, but InstaDB will surround the names by grave accents anyway when the expression is saved. It is necessary, because column and table names may contain any unicode characters, including spaces.

Note that you don't need to type ` characters by hand. Editor has a feature which helps to enter correct table and column names. It is described further.

Selects and non-select expressions

Except standard "select" expression other form of expressions are allowed in InstaDB. Particularly you may write mathematical expressions without using SELECT syntax. For example you may write:

2 + 2.

It is also possible to use values from columns of the same table:

`table`.`a` + `table`.`b`

or from other tables:

`table_a`.`reference_x` -> `table_x`.`value_n` + `table_a`.`reference_y` ->`table_y`.`value_m` .

The above expression uses arrow syntax described in details further.

Tables structure

The expression editor makes the correct typing of the tables and columns names a lot easier thanks to the panel at the right side of the expression text area.

This panel represents the current structure of the database tables. Except the PARAM section described further, every position on the list represents a single table. All of them are folded (except the one you are currently edit), but you can click the mark to unfold them and see the list of columns. Note that the expression columns are also listed, as you can use them the same way as value columns.

When you click a any table name or any column, the fully qualified and grave accents surrounded name will appear in the expression text area. Using this feature make assure that no typo is be introduced.

PARAM keywords

At the top of the table structure panel a PARAM section is located. It contains session specific variables. Currently there are two of them:

• PARAM.user_id is a user ID unique for the InstaDB server; i.e. it will be the same for a user in all of his/her databases;

• PARAM.user_login is a user login, usually his/her address e-mail.

These two parameters can be used in expressions which depends on individual permissions (we suggest to use PARAM.user_login). The following idiom is frequently used in many expressions in different circumstances:

SELECT `Users`.internal_id FROM `Users` WHERE `Users`.`E-mail` = PARAM.user_login

The arrow syntax (->)

Let's say you have a database with two tables: Users and Offices. Of course every user is assigned (by a reference column) to one of the offices. Every department has an address.

Your goal is to add an expression column showing the address (let's call it Office address) to a user. You can do it using standard SQL select:

SELECT `Offices`.`Address` FROM `Offices` WHERE `Offices`.internal_id = `Users`.`Office`

InstaDB provides the arrow syntax (->), which can simplify the above expression:

`Users`.`Office`->`Offices`.`Address`

The arrow syntax let you to follow a reference. You can put the arrow expressions in a row to go along the references as deep as you need. It makes very large expression much easier to write and read. Just try to guess what is the result of the following expression:

`Orders`.`Account`->`Users`.`Division`
->`Divisions`.`Head`->`Users`.`Phone number`

And now imagine that you have to write it using standard SELECT syntax.

Split the complex expressions

As it was already mentioned above, an expression column may use other expression columns to calculate the result. It is really worth to remember. This feature lets you (in many cases) to split the complex expressions into smaller pieces and store them in the separate expression columns.

It is useful for two main reasons. First of all it increase readability of the code and make it easier to understand and modify. The second reason is a code reuse. Sometimes it is possible to write an expression which can be used independently by two or more different columns.

So our tip is: when editing really big expression, consider splitting it into smaller chunks. It is not always possible, but it can really be helpful.