Emeraldjb Patterns

<PATTERN NAME="ddl.oracle.owner" VALUE="the_schema_name"/>
<PATTERN NAME="ddl.oracle.role.admin" VALUE="admin_role"/>
<PATTERN NAME="ddl.oracle.role.read" VALUE="read_role"/>
<PATTERN NAME="ddl.oracle.role.update" VALUE="update_role"/>
<PATTERN NAME="ddl.oracle.role.dev" VALUE="developer_role"/>

This pattern allows the java classes to replace null with "" when marshalling to or from the database. A very simple mechanism which avoid potential null ptr exceptions in your code.

The second part of the pattern allows the java code to auto truncate strings before insert. It may be that your project has certain description columns which need not be complete, and auto truncation may be preferable to a database exception, or client side validation.

This truncation will also create constants for the string columns widths, allowing you to use the constants in any client side validation that you add.

There are many cases where you do not want to use the in-built sequence mechanisms of the database.

Your function must be static and must take a single string parameter. It must return an int value, and will possibly throw a SQL exception. Take a look at the implementation shipped with the generator to see.

A good example with mySql could be a dual primary key, one of which is to be a sequenced number.

The code generator will split this string into an import statement and also into a function call within the insert function for the DAO implementation.

#import com.emeraldjb.runtime.patternSeqNum.SequenceGetter;
public theTableNameValues insert(theTableNameValues values, 
         Connection conn) throws SQLException
         {
         values.getTheColName(
             SequenceGetter.getSequenceValue(table_name));
         ...
 

<PATTERN NAME="ddl.use_sequence_func" 
VALUE="com.emeraldjb.runtime.
patternSeqNum.SequenceGetter.getSequenceValue"/>

<ENTITY>
  <NAME>PKEY_SEQS<NAME>
  <MEMBER TYPE="String" COL_LEN=254 >seq_name<MEMBER>
  <MEMBER TYPE="int" NULL_ALLOWED="FALSE" ><next_val<MEMBER>
  <PRIMARY_KEY COLS="seq_name">sequence_pk<PRIMARY_KEY>
<ENTITY>

The date columns are auto populated by the insert and update code, whereas the created_by and updated_by must be set by your application code.

The dates will only be set at point of execution if they are currently null. If you have columns of the same name within an entity then the pattern may generate broken code.

To disable the pattern for an entity and leave it live for the rest of the project, specify the pattern again within the entity, with a value of 0 (anything <1 will do).

Certain data in your system MUST be archived, with dates, who changed it, and this data can be mined to determine values at fixed times. This pattern created the archive tables, and adds code to all inserts, updates and deletes to record the new values, the times and the action. You could write all historic reports to mine these tables using sql similar to:

 select * from ARCHIVE_FAULT f
    WHERE f.ACTION<>'delete'
    AND f.FAULT_ID=$P(id) AND f.VERSION IN (
         SELECT f.FAULT_ID,MAX(f.VERSION) FROM ARCHIVE_FAULT 
         WHERE
         f.FAULT_ID=$P(id)
         AND f.DBUPDATED_AT<P(date)
         GROUP BY f.FAULT_ID
    )
    

This pattern requires that the entity also uses the optimistic locking and audit patterns.

A new table will be created for any archived table, named ARCHIVE_XXX where XXX is the table name. This table will have an additional column called DBAUDIT_ACTION. The table's primary key will be extended to include the optimistic locking column. The archive table will not have indexes or constraints on it, nor will it have an auto-incrementing primary key.

Every successful insert, update or delete will insert into the archive table, populating the action field. An alternative is to use Emeraldjb to create the tables, and then write your own triggers to populate the archive tables.

<PATTERN NAME="ddl.locking.optimistic"  VALUE="version" />
<PATTERN NAME="ddl.audit" VALUE="48" /> 
<PATTERN NAME="ddl.archive" VALUE="TRUE" />