table and column names < 18 chars (max 17 chars)
all column names should end with _ (underscore) to prevent name clashes with reserved words for any of the dbs.
indexes... when and how much will we define in the default schema ?
I want that the database model is designed very carefully. To do that we'll need to have a way on producing graphical ER diagrams. Anyone got an idea on how to do that ?
Miguel proposed a plugin for eclipse (see http://www.jboss.com/index.html?module=bb&op=viewtopic&t=117241)
Topcased is a good UML modeler, but there are no entity relationship (ER) diagrams in there. we still need ER models or we need a way on how to use class diagrams for that.
We can try to use Hibernate Tools http://tools.hibernate.org.
Also for most of the common transactions, I want us to analyze the generated SQL to verify that it is optimal.
All descriptors in the same table -> WIRE_DESCRIPTOR
Use one column for each many-to-one relation (otherwise there can be problems with duplicate constraint on foreign keys)
Use the same column for long, int and short
Use the same column for float and double
Use the same column for char and String
Try to give a good name to string columns that are used by more than one descriptor
one table for ProcessDefinitionImpl -> PVM_PROCESS
one table for NodeImpl -> PVM_NODE
one table for TransitionImpl -> PVM_TRANSITION
ProcessElement is not mapped. This way ProcessDefinition, Node and Transition have their own inheritance hierarchies.
we want to persist the process definition
the persistence is cascaded to the nested nodes of the process definition
in the nodes
the nested nodes are cascaded
outgoing transitions are cascaded
incoming transitions are not cascaded (already cascaded from the source node)
in the transitions:
source node is already persisted (persistence is cascaded from source node)
destination node is already persisted (a node belongs to a process or to a parent node)
NodeBehaviour mapping (same for Action and Condition)
We want one table per extension language :
we need to mix inheritance
mixing <join> and <subclass> is not enough (only basic properties can be defined)
the <any> mapping is used for references to a NodeBehaviour (one column with the class name, another with the id of the object)
the extensions map their own inheritance hierarchy, using a <subclass> strategy. The interface NodeBehaviour is not mapped.
if we can, we define a shorter name (e.g. extension + name of the class without package) for the column containing the name of the class.
Mapping of the PVM extensions languages
We want to keep the same database model for the pvm tables. If a new process language is added, pvm related tables are not changed.
Each process language uses a subclass of : ProcessDefinition, Execution.
For these hierarchies, we use a <joined-subclass> strategy (fields added by the extension are in a new table).
Object identity and object equality
MAKE SURE TO USE ALWAYS .equals AND NEVER == comparisons in the Java code.
Typically, in the PVM Java objects, we want to use the default object identity as the object equality. But hibernate persistence breaks this (even within the scope of one hibernate session) because of the proxies. So we have to apply a trick in order to make the comparison between a persistent object and its proxy return true. See jBPM 3 class EqualsUtil for that trick.
DB specifics to watch out for
Sybase: trailing zeros in binaries:
Oracle: An empty string in oracle is interpreted as a null. So when you set an empty string in a column, you will read it back as a null value. Situation in jBPM3: A component (Access in jBPM 3) that has a single String field, which is an empty string, may translate into a null component object value after persistence.
Oracle: Identity id generation might clash with forced version increment (== locking with lockmode none). In case the object is created in the same tx as the version increment, hibernate might not insert it before the version update is done, resulting in a StaleObjectStateException