Forms & Reports

DbSchema integrates a powerful and fully customizable Forms & Reports Engine. It can be used for:

Create a Form or Report using the Wizard

You can practice the next tutorial using the Sample Model accessible from the DbSchema Welcome pane, the first which shows up after starting the application.

You can start a new form and report by right-clicking the table header.

In the first dialog, you can choose a data source. This can be the table, the Query Builder, or the SQL Editor.

In the next screen, you can choose a component to use for each table column. We choose labels.

Forms and Reports Wizard

Next the report will show up, like here:

Report generated using the Forms and Reports Wizard

Designer Structure

The designer contains a panel with cells. In one cell, there is a table. This cell has been merged with the cells from its right side.

Form Designer

The cell size is computed automatically. The panels will be represented as a HTML bootstrap grid, with all columns equally-sized.

Tables will be represented as HTML tables, where the column size depends on the table content.

An empty form looks like this.

An empty form

Click an empty cell to place a component inside.

Place component in empty cell

Select two or more cells to merge them.

Merge cells

Data Source Scripts

Panels and tables can have data source scripts. These are usually SQL queries or Java Groovy scripts. Groovy is a pure Java Language with closures.

Click the table link in the previously generated report to open the Table Dialog. Clicking the Data Source link will open the SQL Editor with the query used to get the table data from the database.

Forms Designer Table Data Source

The data source is generating variables with the same name as the table column.

If we look more carefully at the table labels, we can see thy use ${...} expressions.

Evaluated Expressions

The data source is generating variables 'city_id', 'city','country_id', 'last_update' that are evaluated in by labels ${city_id}, ${city}, ${country_id}, ${last_update}.

Labels can use a combination of text and expressions, as for example: City ${city} is ${inhabitants<1000?'small':'large'}. or <% print "something" %>

Data Source Scripts can also make use of Groovy Scripts. Examples and an introduction into the Groovy language is available in the Code Samples dialog.

Code Samples

Master-Detail Reports

This is a Master-Detail report. In the 'Country' table we have added one row on the bottom, placed a table inside, and merged the cells on the right.

Master-Detail Report

The report preview:

Master-Detail Report Preview

The 'Countries' table data source is:
select * from public."country" where country='Argentina'

and the 'Cities' table data source is:
select city_id, city from city where country_id='${country_id}'

The embedded table 'Cities' will receive one variable 'country_id' from the parent table, which is replaced in the query text. As you can see, the variable got also quotes.

Component Template and CSS

Each component, including labels and tables, can have a template and CSS. Different templates will result in different representations. For example, a label can be plain text, header1, header2, etc. Tables have a configurable number of header or footer rows. These rows will be generated only one time in the output, while the table body rows will show one time for each record from the data source.

CSS is the associated stylesheet setting, from the Bootstrap Framework.

Table CSS

Data Input Forms

Forms can be used to insert or update records in the database tables. Here we have created an input form with labels, text fields, and a button. We will use it to add another record in the 'actor' table.

Input Form

We double-click the button to configure a form to be open when the button is pressed.

Button will open another Form

For this we have created another form 'Save Actor', which has an Initialization Script:
sql.execute("INSERT INTO actor (FIRST_NAME, LAST_NAME) values ('${firstname}', '${lastname}')");

This script is configured to use Groovy language. All scripts are receiving the 'sql' variable as the Java connection to the database. Form Initialization Script

In the 'Save Actor' form, click the form title to configure the Initialization Script, which is called one time when the form is open. We can also configure the Input Variables that are used to check if the caller form has delivered the expected data.

Form Input Variables

Deploy Tomcat WAR

The forms applications can be deployed as Tomcat WAR applications. You can export the created application under any tomcat instance. You should: