✎ Considering Database Default Values

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
~~NOTRANS~~
~~Title: Considering Database Default Values~~

Behind a well-designed database application there is always a well-designed data model. This model ideally fulfills the 3rd NF, or at least a reasonable medium between 2nd and 3rd NF. In the standard case, the user interface visualizes the data modell and allows the collection of master and transaction data.

However, the database offers many advantages that we can apply to the user interface without wasting much time. One of these advantages is the "default value" of columns in tables. 

Frequently so called flag columns exist, which has a default value configured in the database. An example here are Y/N (= Yes/No) columns. The default value can be N (= No).

During the creation of a dataset, the developer would have to consider these default values in the user interface and pre-set them for the user. This is a repetitive and error-prone activity. Besides, the developer would rather spend time on challenging problems that on boring tasks.

This is where JVx comes in handy. The framework recognizes the default values of columns automatically and assumes the defined values during the creation of new datasets, directly in the user interface. It should be noted, however, that only constant default values are considered, since function calls are always database-dependent and could contain logic.

== Example ==

Our Application contains user administration for the creation and administration of application users. The underlying database table was created as follows (Oracle Syntax):

<file sql create.sql>
create table USERS
(
  ID              NUMBER(16) not null,
  USERNAME        VARCHAR2(200) not null,
  PASSWORD        VARCHAR2(200),
  CHANGE_PASSWORD CHAR(1) default 'N' not null,
  ACTIVE          CHAR(1) default 'Y' not null,
  VALID_FROM      DATE,
  VALID_TO        DATE,
  CREATED_BY      VARCHAR2(200) not null,
  CREATED_ON      DATE default sysdate not null,
  CHANGED_BY      VARCHAR2(200),
  CHANGED_ON      DATE,
  TITLE           VARCHAR2(64),
  FIRST_NAME      VARCHAR2(200),
  LAST_NAME       VARCHAR2(200),
  EMAIL           VARCHAR2(200),
  PHONE           VARCHAR2(200),
  MOBILE          VARCHAR2(200)
);

-- Create/Recreate primary, unique and foreign key constraints 
alter table USERS
  add constraint USER_PK primary key (ID);
  
alter table USERS
  add constraint USER_UK unique (USERNAME);
</file>

We now need a server objekt for access to the database or table:

<file java>
public DBStorage getUsers() throws Exception
{
    DBStorage users = (DBStorage)get("users");
    if (users == null)
    {
        users = new DBStorage();
        users.setDBAccess(getDBAccess());
        users.setWritebackTable("USERS");
        users.setDefaultSort(new SortDefinition("USERNAME"));
        users.open();
        
        put("users", users);
    }
    return users;
}
</file>

At the client, we now create an object for server access:

<file java>
rdbUsers.setDataSource(dataSource);
rdbUsers.setName("users");
rdbUsers.open();
rdbUsers.getRowDefinition().setTableColumnNames(
                            new String[] {"USERNAME", "ACTIVE", "CHANGE_PASSWORD"});
</file>

If we now create a new user, the fields "ACTIVE" and "CHANGE_PASSWORD" are automatically filled with the default values from the database. The field "ACTIVE" gets the value "Y" and "CHANGE_PASSWORD" gets the value "N".

The following methods can be used to ignore the default values:

<file java>
//per instance
users.setDefaultValue(false)

//for all instances (static)
DBStorage.setDefaultDefaultValue(false)
</file>

If no default values were defined in the database, they can also be set via the API:

<file java>
users.open();

//sets the default value to "X"
users.getMetaData().getColumnMetaData("ACTIVE").setDefaultValue("X");
</file>

If default values are not used, we can achieve the default setting via the use of client side events, such as: 

<file java>
rdbUsers.eventAfterInserting().addListener(this, "afterInserting");
...
public void afterInserting(DataBookEvent pEvent) throws Exception
{
   pEvent.getChangedDataBook().setValue(COLUMN, VALUE);
}
</file>