Tables

Overview

The Tables administration area is where you can view and edit the database metadata. This information is stored in the portofino-model.xml file and is aligned with the underlying databases using the synchronize function.

The database-schema-table hierarchy
Table and colum summary
Mapping JDBC types to Java types
Changing the order of columns
Foreign keys
Selection providers

The database-schema-table hierarchy

The main page of this section is a hierarchical view of databases, schemas and tables. A database is made of schemas, a schema is made of tables.

Click on a table to view its summary.

Notice that there can be a Liquibase icon next to the schema name to signify that a Liquibase script is available and in use for that schema.

Liquibase is an excellent database refactoring tool that can be used to develop a database schema incrementally.

Table and columns summary

This pages appears as shown in the screenshot.

The table details are the following:

Entity name: the Hibernate entity name. If blank, the lower-case table name will be used.
Java class: the Java class for the Hibernate entity. If blank, the entity will be mapped to a HashMap.
Short name: an OGNL template to generate short names for objects of this table. E.g., for a table "person" with columns "firstname", "lastname" and "email", the template could be "%{firstname} %{lastname} - email: %{email}". If blank, the short name will be constructed from the primary key.
Hql query: an example query. Useful to confirm the entity name and the query syntax to use in

The column details are shown in tabular form:

Name: the column name
Property: the property to which the column is mapped. If blank, the lower-case column name will ne used.
Class: the Java class of the property. See below.
Type: the native and JDBC type.
Length: the length or precision.
Scale: the scale (number of digits to the right of the decimal point)
Null: is the column nullable? If not, the column is required.

Column details and annotations

If you click on the name of a column in the "table and columns summary", you will be able to see and edit the column details.

In the upper part you can see/edit the common properties:

Name: the column name.
Property name: the property to which the column is mapped.
Class: the Java class of the property. See below.
Type: the native and JDBC type.
Length: the length or precision.
Scale: the scale (number of digits to the right of the decimal point)
Null: is the column nullable? If not, the column is required.
Autoincrement: is the column of an autoincrement type (SERIAL, AUTO_INCREMENT, etc)?
In primary key: does the column belong to the primary key?

In the lower part, for certain column types (text, numeric and date), columns can have specific annotations.

Annotations for text columns:

Field size: the size of the input field.
Type of content: 'plain' for a single line of text, 'multiline' for a text area, and 'RichText' for html.
String format: common validations than can be applied: email, password, CAP (Italian postal code), PartitaIVA (Italian VAT number), CodiceFiscale (Italian social security number), Phone.
Regexp: a custom regular expression for input validation.

Annotations for numeric columns:

Field size: the size of the input field.
Min value: minimum allowed value, inclusive.
Max value: maximum allowed value, inclusive.
Decimal format: the decimal format to be used for output and validation.

Annotations for date columns:

Field size: the size of the input field.
Date format: the date format to be used for output and validation.

Mapping JDBC types to Java types

JDBC types are an abstraction for database column types such as VARCHAR, NUMERIC, etc. For a list of JDBC types see java.sql.Types.

In some cases a JDBC type maps uniquely to a Java type. For example a BOOLEAN clearly maps to a Boolean.

However in other cases the mapping is not unique and you, as the application developer, must decide the most appropriate mapping.

All the mappings are summarized in the following table.

JDBC type	Java type(s)
ARRAY	java.sql.Array
BIGINT	Integer, Long, Byte, Short, Float, Double, BigInteger, BigDecimal, Boolean
BINARY	byte[]
BIT	Boolean
BLOB	java.sql.Blob
BOOLEAN	Boolean
CHAR	String, Boolean
CLOB	String
DATALINK	java.net.URL
DATE	java.sql.Date, java.sql.Timestamp
DECIMAL	Integer, Long, Byte, Short, Float, Double, BigInteger, BigDecimal, Boolean
DISTINCT	Object
DOUBLE	Integer, Long, Byte, Short, Float, Double, BigInteger, BigDecimal, Boolean
FLOAT	Integer, Long, Byte, Short, Float, Double, BigInteger, BigDecimal, Boolean
INTEGER	Integer, Long, Byte, Short, Float, Double, BigInteger, BigDecimal, Boolean
JAVA_OBJECT	Object
LONGNVARCHAR	String
LONGVARBINARY	byte[]
LONGVARCHAR	String
NCHAR	String, Boolean
NCLOB
NULL	java.sql.Ref
NUMERIC	Integer, Long, Byte, Short, Float, Double, BigInteger, BigDecimal, Boolean
NVARCHAR	String, Boolean
OTHER	java.sql.Ref
REAL	Integer, Long, Byte, Short, Float, Double, BigInteger, BigDecimal, Boolean
REF	java.sql.Ref
ROWID
SMALLINT	Integer, Long, Byte, Short, Float, Double, BigInteger, BigDecimal, Boolean
SQLXML
STRUCT	java.sql.Struct
TIME	java.sql.Time
TIMESTAMP	Timestamp, Date
TINYINT	Integer, Long, Byte, Short, Float, Double, BigInteger, BigDecimal, Boolean
VARBINARY	byte[]
VARCHAR	String, Boolean

Changing the order of columns

To change the order of the columns, click on "change order". Drag and drop the column names, then click Ok to confirm.

Foreign keys

The "Foreign keys and selection providers" tab display the foreign keys that are defined on the current table that reference primary keys on other tables. In JDBC terms, these are the "imported keys".

The foreign key properties are the following:

Name: the name of the foreign key.
Property name (one side): for Hibernate, the property name of the relationship (at the "one" side of the one-to-many relationship). If blank, the foreign key name will be used.
Property name (many side): for Hibernate, the property name of the relationship (at the "many" side of the one-to-many relationship). If blank, the foreign key name will be used.
Columns: the list of foreign key columns.
Referenced table: the referenced table.
Referenced columns: the referenced columns.

Selection providers

Selection providers are similar to foreign keys, but more flexible.

Like foreign keys, a selection provider:

defines the allowed values for a set of columns of a table,
can take the allowed values from another table (referenced table).

Unlike foreign keys, a selection provider:

can reference a table on a different database
can take the allowed values by applying filter criteria
can be applicable only in some parts of the application.

Consider the following simple example:

An ORDER_ITEM table references a PRODUCT table. Each order item has an associated product. Products can be marked as active (i.e. they can be sold in new orders) or inactive (i.e. no longer in stock or in production). When creating a new order, the order items should reference only active products. For old, archived orders, the order items can reference any products, either active or inactive.

New orders could use a selection provider based on the following HQL query:

FROM PRODUCTS WHERE ACTIVE = TRUE

While older orders could use:

FROM PRODUCTS

The latter query is equivalent to using a plain foreign key.

Selection providers are characterized by the following properties:

Name: a name of your choice.
To database: the database of the referenced table.
Hql and Sql: the query to fetch the allowed values. Use only one of these two fields.
Columns: the columns of the current table that will take values from this selection provider.

See the screenshot below.

If you use SQL, the query should return the following columns:

value1
label1
value2
label2
...
valueN
labelN

Where N is the number of columns listed in the Columns field.

NOTICE: unlike foreign keys, selection providers are not mapped as relationships in Hibernate.