Ultorg perspectives, with their hierarchical structure and operations such as filters, formulas, grouping, and joins, serve as a complete visual alternative to hand-typed SQL code. In the academic literature, this user interface is known as a visual query system.

Furthermore, Ultorg follows the principles of direct manipulation: The user initiates actions by interacting with the data itself, rather than a separate flow chart, form designer, or command prompt. Each action is incremental and reversible, and there is immediate, visual feedback.

Ultorg is the first visual query system to let users create and modify arbitrary database queries by direct manipulation of the data on the screen. See our SIGMOD paper[2] for details.

Below, we discuss the relationship between Ultorg perspectives and SQL. Finally, we show techniques you can use to express advanced queries, using features already described.

SQL Equivalence

Queries, Query Plans, and Results

In traditional database tools, the user types a SQL query in a text box, hits “Execute”, and receives a table of results to look at. The user adjusts the query, executes again, and so on.

Internally, the database engine converts each SQL query into an alternative representation known as a query plan. The query plan can be transformed and optimized in different ways, while still remaining equivalent to the original query.

In the example below, you can see a SQL query, its query plan, and its result:

The query plan consists of processing “nodes” such as Join and Aggregate, in a tree structure.

Summarized, the traditional approach is:

1. Type a SQL query, which is converted into a...
2. Query plan, which is optimized and executed to return a...
3. Query result

Ultorg's Approach

Ultorg skips the first step. Instead of a SQL query in a text box, Ultorg perspectives display an interactive visualization called a nested table, which mirrors the structure of a query plan.

But: The nested table shows not only the query, but also its result. The table header shows the query plan, while the content area shows the data that flows through it and comes out of it. This greatly improves usability, as it permits the user to edit queries without “flying blind” as one would e.g. when modifying a deeply nested WHERE clause in a large SQL query.

Here is an Ultorg perspective that is equivalent to the previous SQL query:

Conceptually, each Ultorg subquery (bold table header) in a perspective corresponds to a node in the query plan. The correspondence is not one-to-one, as real query plans have many kinds of node types that are not exactly equivalent to a subquery in an Ultorg perspective. But for each of the fundamental node types (relational algebra operators) in SQL-92, you can construct an equivalent subquery in an Ultorg perspective.[2]

SQL-92 is the standard set of SQL functionality that is taught in database courses, and which is supported by all major databases. It includes arbitrary combinations of joins, calculations, aggregations, and subqueries, while excluding certain later additions to the standard (e.g. PARTITION BY and WITH RECURSIVE).

We have focused on SELECT statements here, though Ultorg also lets you edit data.

Generated SQL

In practice, the “query plan” you see in Ultorg exists for visualization and interaction purposes only. For actual query execution, Ultorg compiles your perspective to SQL, which the database engine then parses and translates into a real query plan.

You can see generated SQL queries by clicking Help→Diagnostics→SQL/JSON Output, or by using the Compile to SQL feature.

For performance reasons, a perspective may be evaluated using multiple SQL queries.

Variations in SQL Dialects

While the SQL language is in theory defined by ANSI and ISO standards, there are substantial differences between the dialects of SQL that are actually implemented by each vendor.

For the above reason, the SQL generated by Ultorg is tailored for the specific brand of database server that you connect to. In particular, Ultorg has special support for the SQL dialects of PostgreSQL, MySQL/MariaDB, MS SQL Server (Transact-SQL), Oracle, DuckDB, and BigQuery.

Semantics and Testing

The translation of visual queries into SQL is designed to maintain consistent semantics across dialects, while allowing the server's query optimizer to produce a well-performing query plan.

Ultorg has a large suite of test queries that run to ensure that query results remain correct from release to release, and across different SQL dialects.

Injecting Raw SQL

Ultorg formulas support a standardized list of data types and functions, with built-in translations for each SQL dialect. These are sufficient for most query tasks.

Additionally, it is possible to include snippets of raw SQL code, in order to access dialect-specific or user-defined functions on the data source. This is done with the rawSQL* functions. See the documentation of each function for details.

For example, the formula below uses the rawSQLaggNum function to access the aggregate function percentile_cont on PostgreSQL:

The rawSQL… and rawSQLagg… functions are meant for scalar and aggregate expressions, respectively. In both cases the SQL code should represent a simple column expression only, returning one value per row, rather than an entire query or table expression.

Techniques & Recipes

In this section, we provide additional techniques and recipes, using query building functionality that has been described on the previous pages.

Arbitrary Filter Conditions

If you need to express filter conditions other than those listed in the Filter popup, you can filter on the result of a formula instead. In the most general case, you can put a logical condition in the formula, and filter on it to include only rows where the value is true.

For example, we can create a formula to see whether either the Title or Description field in the Courses table contains the word "intro", and filter on it:

Arbitrary Join Conditions

For most queries involving a join between tables, the join condition is a simple equality check (“columnA = columnB”). Such conditions can be set directly with the Custom Join action.

For other conditions, you can define the join condition in a formula, and filter on the formula:

1. Use the Custom Join action on the relevant subquery, and select a child table, but click OK without selecting any join constraints. Click OK again when prompted to confirm.
   
2. Insert a formula in the new child subquery.
3. Edit the formula to define the join condition, as a logical condition. You can reference fields in both the parent and the child subquery.
4. Filter on the formula to include only rows for which the value is true, as shown previously.

For example, we can join a table of academic courses with a table of rooms on campus, and for each course, show rooms that can accommodate all students:

In many cases you can join on a formula instead of using the fully general approach above.

Multi-Table Foreign Keys

Some database tables may have a foreign keys column that can point to IDs in more than one table. These are sometimes called “generic” or “polymorphic” foreign keys (in Django and Rails).

You can traverse these relationships in Ultorg by using a Custom Join for each referenced table.

If the IDs are unique across the different tables, simply join on the same column multiple times. Right-click the foreign key to join on, click Custom Join, and pick the other table and its ID.

In the example below, Resource ID can point to globally unique IDs in either the Users or Billing Accounts tables, and joins have been added to both tables via the Custom Join action:

In Ultorg, joins work like left joins by default, so the parent row under Log Entries won't disappear just because there exists a join without a match.

If the IDs are not unique across tables, but the referenced table is identified by a separate field, you can use a formula for each referenced table to include only relevant IDs:

In the example above, a given OtherEntityID can contain either an officer ID or an intermediary ID, depending on the contents of the OtherEntityType column. A calculated field, OfficerID, uses a formula to yield officer IDs only. We could subsequently use the Custom Join action on OfficerID to join against e.g. the IDs of an “Officers” table.

Conditional Aggregates (“Sum-If” etc.)

In a formula with an aggregate function, you can use the if function with a logical condition to include only certain values. When the condition does not hold, return null.

For example, we can show the highest enrollment limit of any section in a course, but include only open sections:

As previously discussed, the same effect could be achieved with a filter (on the Status field). The main difference is that the alternative above leaves the non-matching rows visible.

For conditional row counting, the countif function is provided as a shortcut:

Anti-Joins

Suppose you have two tables joined in a one-to-many relationship, and want to show only parent rows for which there are no matching child rows. This is sometimes called an “anti-join”.

To express this kind of query, count the rows from the child subquery, and filter the parent subquery to show only rows for which the count is zero:

Universal Quantifiers (“Where All”)

Suppose you have a one-to-many relationship, and wish to show only parent rows for which all of the child rows match some condition. You can do this by counting how many rows do not satisfy the condition, and filtering for zero.

For example, suppose we have a list of academic courses, and wish to show only courses for which every meeting starts at noon or later. To do this, we count the number of meetings that start before noon, and then filter to show only courses for which the count is zero:

In mathematical logic, this kind of condition is called a “universal quantifier”.

If, by contrast, you wanted to find courses having “at least one meeting starting before noon”, you could use the same formula, but filtering to exclude counts of zero. Or you could just filter on the Start Time field, which will activate the Hide Parent If Empty setting automatically. This kind of condition is called an “existential quantifier”.

Note that null values are ignored by the countif function. If you have null values in your condition, you may need to place the aggregate function and the negated condition in separate formulas, and test for null values explicitly in the inner subquery.

For example, a claustrophobic student may wish to avoid meetings in small rooms, or in rooms for which no room data is available:

The final step, as before, would be to invoke Filter Only 0 on the outer aggregate formula.

Filter Relative to Current Date

When filtering on dates and timestamps, you can enter e.g. “30” as a shortcut for the date 30 days in the past:

A filter set this way will be defined with respect to the specific constant date that is shown in the list. To create a filter that change depending on the actual time that the query is executed, you can filter on a formula that uses the now or today functions:

In the example above, a formula is added that calculates the number of days since each log event occurred. You can then set a filter on the calculated field, e.g. to show log events that occurred in the last 14 days.