DB Design - Query Builder

Flying Fleet offers full (read only) access to all the tables containing both the setup and the data of the instance. While we are currently well over 100 tables, we designed tools to help you visualize and query data as you may want even without having high technical skills. You may experiment without being scared of breaking anything. Due to security / privacy reasons, only super-admins have access to this feature.

The entry point is the "DB Design" page found in the "System" menu.

The tool is split in two tabs (due to the massive size).

Tables diagram

The first tab of the page allows to simply browse all the tables contained by your instance. You may mouse over a table and see the links between the tables, as well as click on the table header to expand / contract it and then see the fields contained within a table.

A small description of the goal of the selected table is shown while hovering over a table.

The small "picker" icon on the top left of each table allows to pick that table as starting point for the query builder.

Query Builder

The query builder allows to either directly type (in SQL) the query you want to perform (only SELECT queries are allowed), or build the query by using the builder found under it.

Tables

In the middle of the builder you will find the active table (From: ...) and the optionally linked tables. A link between a table is done over a so called foreign key, which is fully known and handled by Flying Fleet. To add a link to another table, for example to get all the movements and the pilot information, expand the "Link to" section and select the wished tables to add to the selection. You may further select child tables like in a chain of links.

Column selection

By default Flying Fleet will select all the columns available (using the keyword * in the SQL statement), but you may choose which column to read and apply grouping or transformation on the selected column. To do so, expand the "Fields" block, and start selecting the columns you want to add.

The first drop down allows you to group and apply grouping functions to the column, while the second drop down allows to transform the data and the last one allows to sort the column. Currently the order of the sort is defined by the order of the columns appearing in the list.

Conditions

You may restrict the data you extract by applying conditions, to do so expand the "Conditions" block and select as many fields as you want and apply operations.

Premade queries

At the bottom of the query builder, we prepared a selection of pre-made queries you may find useful at least as training material. You will see that some of those queries correlate multiple tables to get the data, as well as all of them use the grouping feature.

SQL and Results

While working with the query builder, the SQL is constantly updating, and the query is run on the server showing the results. If for some reason the query is incorrect, an error will be displayed. Only the first 500 rows will be displayed (for performance reasons).