Blog @ SIB Visions » 2011 » December

Mysql and PostgreSql enum/set support

MySql and PostgreSql have some useful custom datatypes. Enum is one of them. MySql has a Set datatype too.

With an Enum, you define a list of allowed values, but only one value is possible for the column (same behaviour as check constraints in other databases)

With a Set, you define a list of allowed values and multiple values are possible for the column (more like a n:m relation). But you should not use Set datatypes, because it makes your data model unreadable and everyone wants clean data models?

With JVx 1.0 it is possible to use Enum or Set datatypes. On UI side you get a link cell editor (dropdown) or choice cell editor (e.g. checkbox).


Look at the image on the right. You see 3 columns. The first is a simple text column, the second is a linked cell editor (dropdown) and the third is a checkbox. Nothing special? Not until you see the database table.

The table and types, in PostgreSQL, are defined as:

CREATE TYPE mood AS ENUM ('sad','ok','happy');
CREATE TYPE yesno AS ENUM ('Y','N');
CREATE TABLE person 

(

   name text,

   current_mood mood,

   active yesno

);

The server-side storage handles the enum/set detection automatically and sets the linked cell editor and allowed values into the MetaData. You don't have additional work on UI side to get above table!
And this is really cool, isn't it!

But we recommend a new creation style for your storages in your life-cycle objects, to get the new features:

Old style:

DBStorage dbs = new DBStorage();

dbs.setDBAccess(getDBAccess());

dbs.open();

New style:

DBStorage dbs = getDBAccess().createStorage();

dbs.open();

Why we recommend this?

With 1.0 we introduce db specific storages like MySqlDBStorage and PostgreSQLDBStorage. This storages support database specific features that are not standardized.
We avoid/reduce boilerplate code whenever possible and who wants to change source code if the database is changed?

With the new style, you get the best storage available for your database, automatically. Of course it is possible to create e.g. a MySqlDBStorage() manually, but keep in mind that you are fixed to MySql.

After you changed your life-cycle objects, you get linked cell editors for your enum columns. But in many cases you use enum datatypes as check constraints e.g. Yes/No columns. In that case, a linked cell editor is not very cool - you need a choice cell editor.

JVx defines some default choice cell editors, via

UIChoiceCellEditor.addDefaultChoiceCellEditor(ApplicationUtil.YESNO_EDITOR);

in the Application class. With this mechanism it is very easy to define choice cell editors for your enum types.

Set datatypes are very special and generally, we don't like unreadable data models. But JVx should support it as good as possible.

We handle Set datatypes like enum datatypes and we show a link or choice cell editor. The user can only select one value. A developer can set multiple values via API (as comma separated list).

Have you ever seen this comfort in other frameworks? Let us know