1. Documentation
  2. Reference Manual
  3. Administration
  4. Tables

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

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:

  1. Name: the column name
  2. Property: the property to which the column is mapped. If blank, the lower-case column name will ne used.  
  3. Class: the Java class of the property. See below.
  4. Type: the native and JDBC type.
  5. Length: the length or precision.
  6. Scale: the scale (number of digits to the right of the decimal point)
  7. 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.