After this brief introduction, it’s time to create your first sample applications. We’ll show you a set of basic apps which will guide you through the process. Every application will allow you to dive deeper into CYPEX and to learn more about its features.

Let’s dive headlong into the first CYPEX application. The goal is to create a form, as well as a dashboard showing a report.

The first step is to create an SQL model. In our example, we’ll create a “sales” schema with couple of tables, initially populated with some of sample data:

To demonstrate how CYPEX works, we have compiled a data set. We’ll use the following tables and permissions to define queries:

cypex=# GRANT USAGE ON SCHEMA public TO cypex_user;
cypex=# GRANT whoever TO authenticator;
cypex=# SET SESSION AUTHORIZATION cypex_admin;

cypex=# CREATE TABLE t_currency (
        id 			serial 	PRIMARY KEY,
            currency_name 	text 	  	NOT NULL
);
CREATE TABLE
cypex=# INSERT INTO t_currency (currency_name)
    VALUES ('USD'), ('EUR'), ('CHF'), ('GBP');
INSERT 0 4
cypex=# SELECT * FROM t_currency;
 id | currency_name
----+---------------
  1 | USD
  2 | EUR
  3 | CHF
  4 | GBP
(4 rows)

The second table shows sales amounts::

cypex=# CREATE TABLE t_sales (
    id 			serial 	PRIMARY KEY,
        currency_id 	int 		REFERENCES t_currency (id),
        t 			date,
        amount 		numeric(10, 2)
);
CREATE TABLE

cypex=# INSERT INTO t_sales (currency_id, t, amount)
    VALUES	(1, '2022-01-04', 3243.45),
         	(1, '2022-01-05', 4324.43),
            (2, '2022-01-09', 1242.98),
                (2, '2022-01-10', 985.34),
                (2, '2022-01-11', 684.32);
INSERT 0 5
cypex=# SELECT * FROM t_sales;
 id | currency_id |     t      | amount
----+-------------+------------+---------
  1 |           1 | 2022-01-04 | 3243.45
  2 |           1 | 2022-01-05 | 4324.43
  3 |           2 | 2022-01-09 | 1242.98
  4 |           2 | 2022-01-10 |  985.34
  5 |           2 | 2022-01-11 |  684.32
(5 rows)

This is a 1:n relationship. The model builder will display the new model:

You see above that currency_id references t_currency.id.

NOTE: It‘s important to grant permissions to “authenticator”, otherwise the login process won’t work as desired.

CYPEX supports “default lookups”. What does that mean? In a relational model, foreign keys are quite common. The problem is: If you want to display a table containing a foreign key, you might not be interested in seeing every single type of ID displayed. Let’s take a look at our “t_sales” table as shown in the model builder:

What we really want to display in our form is the ID, the name of the currency, a timestamp and the amount. To make sure that the GUI easily resolves the key in the manner desired, click on the currency table and select “default lookup”. There you can define how to resolve an ID pointing to this table. CYPEX will reverse-engineer the model, and will always display the name instead of the plain ID.

Here’s what the lookup form looks like:

Once you’ve saved this info, the model knows how to resolve ID’s in an elegant way. Note that this default resolution happens at the model level.

In the end, the application generated will display the content of the default lookup table:

After defining default lookups to make life easier, let’s move forward and define a query. To create a query click on the “+” icon in the queries menu on the right side of the page:

What you’ll find is an SQL editor as well as options to test your queries.

The second half of this form is all about permissions:

You can visually define who is allowed to perform which operation(s) on this query. Note that in some cases, a trigger will be needed to handle insertions. Therefore, read-only queries such as reports should only have SELECT permissions.

Now that you’ve defined your first data model, you can create your first application. Go to the model builder and choose “Applications” in the menu on the left. Then press the “Generate” button.

CYPEX will open the app generation form:

Your application needs a name - that name is going to be the title of the entire application. What’s also important is the owner: you’ll render the app for this GUI, so make sure the owner of the GUI has all the permissions needed to handle the underlying data.

Finally, decide which queries default pages will be generated for, when rendering the application. What is the logic here? Suppose you have 10 queries. You might produce 3 applications (each of them using 5 queries). Keep in mind: you can build as many CYPEX apps as you want on top of those queries.

After hitting the “generate” button, you have your first CYPEX app:

Click on the button in the middle and execute the app. You’ll see one menu entry, and one table which has been generated for us by CYPEX:

Welcome to CYPEX.

You have just built your first application.

Congratulations!

So far, the default rendering process has created a menu entry and a table. However, what we really want is a dashboard. The goal is to modify the application and add some charts.

Click the “edit application” button in the application. The application will then be in edit-mode which allows you to change all graphical elements:

In this case, we’ve used drag & drop to add a pie chart to the app. The important part is the configuration of the data sources: Select the “query” in “source query” to tell CYPEX which data source to use.

Then select the axis needed by the pie chart. You can decide which titles to use, what type of chart you want, and a whole lot more. The basic idea is the same for all the types of GUI elements which can be added.

Once the changes are done, save them:

The “save” function will tell you what has been changed and release a new version of the GUI.

You can add as many widgets as you want. Pie charts, line charts, bar charts - CYPEX has them all. The underlying concept explaining how to configure things is the same for almost all charts. Only maps require a different infrastructure - (geo JSONs) but more on that later.

It’s easy to create a form for an entity. CYPEX allows you to create a “default query” for this purpose.

The main question is: What is a default query? In CYPEX you don’t usually change tables directly, rather you create a view behind the scenes. What happens here is that the default query will be a “SELECT * FROM tab”. The advantage is that you can nicely abstract user permissions that way and separate the underlying data from the access layer.

In order to create a default query, click on a relation and select the first entry (“Generate Query”).

All you have to configure in this case are the title and the permissions. Note that in order to create a new form, the user needs INSERT or UPDATE permissions. Otherwise, those forms will not be generated by default.

In our example, we’ve created default queries for both tables:

As you can see, the new queries aren’t accessible when rendering the new application. Incremental rendering is also possible. Alternatively, you can add new queries and build their forms manually. However, it’s generally easier to use the rendering infrastructure which automatically creates all necessary forms.

After generating the application, you see additional menu entries:

It’s important to notice that the permissions set before ensured that the table including the edit buttons was generated. Modify a row:

What’s important to note here is that the default currency is displayed in the drop-down menu. The reason is that we’ve defined a default resolution for this column. Therefore CYPEX already knows how to handle this field.

So far, you’ve seen how to generate simple forms. Each input field is represented as a text field. However, this might not be desirable at all. Let’s take a look at the following sample data structure:

BEGIN;

CREATE TABLE t_vendor (
	id	serial		PRIMARY KEY,
	name	text		NOT NULL
);

INSERT INTO t_vendor (name)
	VALUES ('Mercedes'), ('Opel'), ('Tesla');

CREATE TABLE t_car (
	id		serial		PRIMARY KEY,
	model		text		NOT NULL,
	horsepower	int		CHECK (horsepower > 0),
	vendor_id	int		REFERENCES t_vendor (id)
						ON UPDATE CASCADE
						ON DELETE CASCADE
);

INSERT INTO t_car (model, horsepower, vendor_id)
	VALUES ('A180', 136, 1), ('A200', 163, 1),
	       ('Mokka', 96, 2), ('Insignia', 174, 2);

GRANT ALL ON t_vendor, t_car TO authenticator;

COMMIT;

What we have here are vendors and cars. The parts to focus on are: a.) the “horsepower” field as well as b.) the foreign key. You’ve already learned that in order to build smarter forms, you can use default resolutions. In order to achieve that, go to the model builder, then click in “Default lookup” on the vendor table. Select the name column. Then, generate the default queries for both entities. Now CYPEX knows that it has to ask for text input rather than ID’s.

Now let’s render the application:

Select both queries you want to render and click the “Generate” button. Start the application, select any car you want to modify, and start the edit mode. What you’ll see is a text field for the model (which is fine), a text field for the horsepower value and a drop-down created by the default resolution. Now, replace the text field for the horsepower entry with a slider. The way to do this is to select a slider from those elements, drag them in and voilà, you’re ready to configure the element. You need to assign the same data sources to the slider. Select the same field as in the old horsepower field and configure the remaining variables you want to see:

In my example, the slider will range from 0 to 1000. Then you can delete the old element and arrange these elements exactly the way you want them to be arranged:

Save the application, and the form is ready to use:

You have successfully replaced a simple text input with a more advanced element.

Sometimes you want input forms or tables to be in tabs. CYPEX provides this feature and allows you to easily add tabs . Drag and drop a “Tabs” element into your WYSIWYG editor.

By default, your element will be empty so you have to add tabs to it:

To do that, check out the configuration menu and add tabs. Once the tabs are created, you can fill them with elements of your choosing:

In this case, a table has been added to the GUI.

Data models can change over time, which means default rendering can become a problem. Imagine that you have an existing application and you want to extend it with additional entities, queries, and so on.

CYPEX supports changing data structures. Let’s outline two relevant cases:

• Incremental rendering
• Changing query definitions

Building forms by hand after default rendering is done can be quite cumbersome and slow.

CYPEX supports incremental rendering. Once an application is done, you can easily create new queries:

Once you have created the query, you can jump to the applications overview in the model builder. Select the second icon (“Generate and add pages”):

Then tell CYPEX which queries you want to add to the application:

In this case the “horsepower” query will be added as a new menu entry and thus a new table:

Note that the data source will be available for other elements as well.

There are other changes which have to be addressed. It often happens that a query definition has to be changed. Such changes are partially supported.

Let’s take a more detailed look.

Adding a column to a query is always possible. Go to the model builder and modify the query accordingly. Note that the query editor uses the “real” PostgreSQL parser to check if the syntax is correct. You can therefore rely on the fact that the query is OK, as long as you can actually save it.

Here’s a possible modification which works for the previous example:

Make sure that you do not change column names, because doing so may break your frontend application. Instead, add columns.

If you want to change a query, life is a bit more complicated. Again, it’s not recommended to change column names.

However, what is possible is changing the definition of a field without changing the data type. What does that mean? Consider:

    (count(*) + 1) AS count

It’s perfectly feasible to change “count()” to “count() + 1”. It does not change the data type, nor does it change the column definition. However, the following change will result in an error:

    count(*)::numeric(100, 10) AS count

Note that the query is correct from an SQL point of view, but the data type will change, which isn’t allowed:

cannot change data type of view column "count"
from bigint to numeric(100,10)

Instead of changing view definitions, it might make sense to create a new view, providing the data you need. It has the advantage of not breaking your existing application.

Dropping a query is easy and can be done in the model builder directly. However, it will have implications and it might indeed break your application. The following screenshot shows what happens when a query is dropped:

Changing the application becomes necessary in this case, as the underlying data source is lost.

Sometimes you might want to add a new page to an application. To do that, go to edit mode and add a new page:

CYPEX will produce an empty page, which you can then use to add elements later on:

In this case, you can see two elements have been added: a markdown field, as well as a code window.