DbSchema integrates a powerful and fully customizable Forms & Reports Engine. It can be used for:
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.
Next the report will show up, like here:
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.
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.
Click an empty cell to place a component inside.
Select two or more cells to merge them.
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.
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.
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.
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.
The 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.
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.
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.
We double-click the button to configure a form to be open when the button is pressed.
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.
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.
The forms applications can be deployed as Tomcat WAR applications. You can export the created application under any tomcat instance. You should:
<role rolename="tomcat"/> <user username="tomcat" password="tomcat" roles="manager-gui"/>