ERP/3.00/Platform Topic Description

Contents 1 Introduction 2 Separating the Physical and Logical Data Model 2.1 Entity 2.2 Property 2.3 Table/Column 2.4 Help, comments 2.5 Convenience functionality 3 From Reference to Domain 4 Validation 5 Storing the Physical Data Model in the Application Dictionary 5.1 Table 5.2 Column 6 Supporting Inheritance 6.1 Modeling Inheritance 6.2 Inheritance and the physical database schema 6.3 Inheritance and Business Object Java classes 7 Concept Entities and Concept Injection 7.1 Concept Entity to Java Interface 7.2 Concept Entity adds properties/columns to an Entity/Table 7.3 Concept Entity with a list property 8 Allowing custom code in generated business objects 9 Versioning, optimistic locking 10 Data translation, Translatable, a concept? 11 Rules Engines in Openbravo 12 Data Versioning 12.1 Historical trace 12.2 Allow rollback of a change or a set of changes 12.3 Override data, change data on multiple levels 13 Data Inheritance/Data Templating 13.1 Introducting a common type 14 Explicitly Model: Operations 15 Discussion 26th-28th May

Introduction

This page contains a short description of possible changes and improvements to the Openbravo platform.

It is a proposal, the goal of this page is to describe new ideas in some detail to identify challenges and opportunities.

Separating the Physical and Logical Data Model

In Openbravo 2.50 the logical and physical data model are combined in the AD_Table, AD_Column and AD_Reference (and related) tables. For the next release the idea is to separate the logical and physical in separate concepts. This separation has the following advantages:

• reflects the differences between the two models (conceptual and physical).
• makes it possible to work with more flexible relations between conceptual and physical data models (for example store an entity in multiple tables).
• makes the conceptual data model (and the code which depends on it) more robust for possible changes in the underlying database schema.

The proposal is the following:

• AD_Table will be split in an AD_Table which stores the physical table information and an AD_Entity (or Entity) which defines a logical entity.
• AD_Column will be split in an AD_Column which stores the physical column information and a logical concept: AD_Property (or Property).
• AD_Reference: the ad_reference will be re-designed to a new concept: domain which defines primitive data types.

A summary of the associations/fields of an Entity and Property and the remaing fields of AD_Table:

Entity

Most of the time an entity will have one table but for flexibility we can allow an entity to have zero tables or more than one table (secondary tables). An entity has the following fields:

• id/client/organization/active: fields also present in ad_table.
• name: corresponds to the current AD_Table.name, is globally unique and used for XML serialization (REST webservices)
• description
• help/comment
• classname: corresponds to the current AD_Table.classname
• datapackage: the package to which the entity belongs
• dataaccesslevel
• type: an entity can have be one of the following types:
  • standard: (the default) a normal type stored in a table
  • abstract: an abstract entity translates to an abstract java class, it can never be instantiated directly only subclassed (see inheritance below), it has its own table (see supporting inheritance below).
  • mappedSuperClass: an abstract entity (so translates to an abstract java class) which does not have its own table, the properties of the mapped super class are always stored in the tables of the sub entity. See here for more information on the JPA MappedSuperclass concept.
• deletable (same as current AD_Table field)
• security enabled (is still required?)
• volume: an enumerate with values (low, medium, high), is used by the user interface to determine default/optimal selection mechanisms.
• identifier pattern (to be discussed): a pattern defining how the entity is visualized, for example: $name ($id) or $name - $description. The concept of an identifier pattern is very flexible but also more performance intensive and it is not possible/difficult to identify the identifier properties as they are 'hidden' in the pattern. So it can make sense to continue the current approach (identifier properties in a pre-set order).
• Table: the reference to the table storing this entity. Can be null to support entities which are only used in memory.
• Secondary tables: the list of secondary tables storing additional properties for this entity (low-prio, nice-to-have)
• Super Entity: a reference to the super type (see discussion about inheritance below)
• list of Concept Entities (the concept of a concept entity is discussed below)
• list of properties

many of the above fields are currently present in AD_Table.

Note: the audit info fields are not defined here because they are assumed to be handled through concept entities (see later section).

Property

A property is the conceptual/logical representation of a column. A property normally is linked to a column but this is not strictly necessary (for example computed properties). A property has the following fields:

• id/client/organization/active: set of fields also present in ad_column
• module
• entity: a property always belongs to at most one entity
• name
• description
• column: a property can have zero or one column
• help/comment
• type: primitive or reference
• targetentity: the entity to which a property refers (only applies if type is reference)
• filterClause: to filter the instances of the targetentity. Note that filterClause may use variables from the context or from the entity itself.
• opposite: the property on the other side (in the target entity), only applies if type is reference
• containment: defines if the reference is a reference from parent to child, only applies if the type is reference (replaces the isparent field in the child)
• validation: reference to a validation rule (see validation below)
• default value
• list: boolean, if true then this models a list property (note also lists of primitive values can be supported in this model)
• id: flags this property as being the id property
• identifier: flags this property as being part of the identifier
• mandatory
• updatable
• position: sorting within the entity
• domain: see below for discussion on domains, only applies if type is primitive
• transient and transientcondition: correspond to the current fields in AD_Column

Note:

• the AD_Column reference and reference search fields are replaced by the domain concept and by the targetentity/opposite fields.
• some fields (length, display (de-)encryption, min/max value, value format, maxlength and display format) are moved to domain.
• readonlylogic: regarding this field it has to be decided where and how it should be supported (in the ui in java, etc.)

The above field-list only contains fields which are required to support the new UI. It is possible that for backward-compatibility other current fields have to be added (for example callout).

Table/Column

The table and column concepts are discussed below in the section on storing the physical datamodel.

Help, comments

An entity and property can be used to attach different help texts and labels. It needs to be discussed/analyzed what makes sense here:

• label: default label or title to show in page titles and field labels
• Short help/comment: a short help text displayed as popup with mouse over.
• Longer help/comment: more concept help texts

Help texts should be wiki or web like in that help texts can link to other help texts.

All help/texts/labels should be translatable (as it is now). The current application element structure can be used for that.

Convenience functionality

The system should offer convenience functionality to easily create entities/properties from a table/column definition and vice versa.

From Reference to Domain

The type of a column is currently defined using the AD_Reference table and its related tables. The column type currently describes both the type and also user interface aspects (the visualization method). The proposal in this section is to re-design the current reference implementation to separate the user interface and data modeling concepts.

The proposal is to replace the reference concept with the new concept: domain (or an alternative name: data type). A domain is used to create named primitive types, for example: quantity, price, sequence number etc. A domain has the following main properties:

• name: a unique name
• type: date, date time, long, decimal, string, password (encrypted), list (enumerate)
• constraints: min, max, max-length, etc.
• validation (see below): for example email, credit card
• default visualization/edit pattern

A list domain definition uses an another entity (domainlist) to store the enumerate values.

The above domain definition fits to the current primitive type (incl. list) reference model. The tabledir, table and search references are not modeled in the domain definition. The following is proposed to handle these:

• the association from a property to another entity is modeled explicitly in the property, so a property will have two fields: target entity and opposite property. This covers the tabledir and table references.
• the search reference is replaced with two concepts: 1) an entity gets a new property: volume which can be high, medium, low (tbd), this property defines the most appropriate default selection mechanism in the UI: listbox, dynamic select list, separate popup, 2) in the user interface definition one can select/override the default selection by selecting the best selection edit dialog.

Validation

The validation defined in a domain and property should be based on a pluggable validation framework. The current AD_Val_Rule concept can be re-used for that. Although it can be extended in the following ways:

A validation can be implemented in java, javascript or in scripting language (to be defined). In addition it is possible that validation needs to be implemented differently for user interface and backend operation. This means that one validation can define multiple implementations: one for the user interface (in javascript) and one in java or a scripting language (to run server side). The interface of the client and server side validation needs to be determined (based on experience with prototypes etc.).

Storing the Physical Data Model in the Application Dictionary

The goal for the next Openbravo release is to make it possible to maintain the physical data model completely from the Openbravo user interface. There are multiple aspects to take into account:

• The physical database schema needs to be modeled completely in the application dictionary.
• The update.database logic has to be adapted to work with the physical data model as defined in the application dictionary.

This means that additional database schema aspects (than just table/column) need to be defined in the application dictionary:

• Multi-column unique constraints
• Foreign key constraints (question: can in principle also be defined on column level, assuming that all foreign keys are single columns)
• Check constraints
• Indexes
• Functions
• Triggers

It should be possible to initiate an update.database from the Openbravo user interface.

Some other aspects:

• After updating the database it is possible that the system has to be re-started.
• To minimize the initial work it can make sense to re-use the existing update.database logic which operates on XML files. In this scenario the physical data model in the application dictionary is converted to a XML representation which is then used to update the database.
• To support multiple developers working on one system and sharing of data models is very likely that the database model requires a XML representation (to be checked in to Mercurial).

The last two aspects can be covered by re-using the current XML model and the code using it to create and update databases.

Table

In the new concept the AD_Table will only store the physical database schema relevant data. The new AD_Table will hold the following fields:

• id, client/organization
• name: is the table name
• description
• view
• primary key name

Question: what to do with the following fields:

• ad_window_id
• importtable
• ischangelog (concept entity?)
• po_window_id
• isdefaultacct
• sql_record_identifier
• development status
• datapackage (is required, probably to link to module?)

Column

The column information can to a large extend be derived from the property definition. Therefore the column itself only stores additional physical db schema related information:

• column name
• table
• sql type (can be derived but possibly needed for specific overrides)
• length (relevant for some sql types)
• required: to support nullable in the database and mandatory in the business layer.
• position
• oncreate default

Question: what to do with these ad_column fields:

• callout function
• callout
• filter
• process
• development status
• stored in session

Supporting Inheritance

The proposal is to also support inheritance in the new application model. Supporting inheritance has the following advantages:

• allows for more fine-grained data models
• allows for additional customization (by inheriting from existing concepts)

In general inheritance should be applied with some caution, inheritance structures should not be too deep.

Modeling Inheritance

Inheritance is defined in an entity: an entity can have a reference to its super entity. One thing to note is that if an entity is a subtype then it is not allowed to have its own id and version properties. Id and version properties should always be defined in the root entity of the inheritance structure.

Inheritance and the physical database schema

Inheritance can be supported (by Hibernate) in three different ways in a physical database schema (see here and here for a more complete description of inheritance mapping strategies):

• single table: the complete inheritance tree is stored in one table. This is the most efficient approach but results in tables with many columns, columns of subtypes also need to be nullable.
• joined table: a table is created for each type (super and sub), the subtypes only have columns for the properties of the subtype, i.e. each table only has columns for the properties of the type stored in the table. Join-foreign-keys are created from subtype to supertype tables. The advantage of this approach is that the physical database schema reflects the inheritance structure, the disadvantage of this approach is that querying for subtypes always requires joins (less efficient)
• concrete: each concrete type has its own table which stores all the properties of the type (incl. the inherited ones). The advantage is that this approach is efficient, the disadvantage is that polymorphic querying is not possible.

In addition the JPA MappedsuperClass mapping exists. If a class is denoted as a MappedSuperclass then its properties are stored in the table of its subclasses, a MappedSuperclass does not have its own table. MappedSuperclass is similar to the concrete inheritance mapping, with the difference that MappedSuperclass allows much more fine-grained control.

Considering the advantages and disadvantages the proposal is to initialy use the joined-table strategy in Openbravo combined with supporting MappedSuperclass. If inheritance structures are not too deep then the performance penalty is not that much (expectation).

To support the joined-table mapping the following has to be taken into account:

• an entity has a link to at most one super entity
• if the super entity has its own table then the table of the sub entity should have a foreign key to the super table.
• if the super entity is a MappedSuperclass then the columns of the superclass should be created in the table of the sub-entity.

Inheritance and Business Object Java classes

Mapping inheritance to the generated business objects is straightforward: if an entity inherits from another entity then the business object Java class of the sub-entity should inherit from the business object Java class of the super entity.

Concept Entities and Concept Injection

When modeling an application there are concepts which are common and generic for all or many entities, these are so-called cross cutting concerns. Examples of such generic concepts are audit tracing and security. In most systems such cross-cutting concerns need to be implemented and defined for each entity separately. This results in a less flexible system and also does not allow explicit modeling of these cross-cutting concerns.

When thinking about cross-cutting concerns there are different aspects which need to be taken into account:

• business objects should be taggable that a certain cross cutting concern applies to them
• a concern may require additional data to be registered for an entity
• a concern can also require business logic to be executed at specific points in the business object's life cycle.

To solve this in a better and more explicit way in Openbravo the proposal is to introduce the so-called 'Concept Entity'. A concept entity is very much like a normal entity, it has property and a table and columns definition. However, there are also differences:

• a concept entity will be translated to an interface in Java, not to a class
• a concept entity can be attached/linked to multiple other (non-concept) entities
• a non-concept entity can have zero or more concept entities

To illustrate the usage let's do an example. In the next release the idea is not to add explicit audit info fields to each entity but to introduce a new Concept Entity: Traceable, which has four properties:

• createdBy
• ceationDate
• updatedBy
• updated

This concept entity is linked to most entities in Openbravo. This results in 4 additional properties/columns in each of the linked entities. The generated business objects for these entities will implement the Traceable interface (which is generated from the Traceable Concept Entity). The data access layer code performs specific actions when inserting or updating a business object if the business object implements the Traceable interface. The advantage is that by defining audit info like this there is one central location where this definition can be maintained.

Note that

• it is also possible to define concept entities without properties. The purpose of a concept entity is than solely to tag an entity.
• in the above description a concept entity only has primitive type properties or single references (so no list references). This is discussed separately below.

Concept Entity to Java Interface

An entity is normally generated as a Java class to represent it in Java. Concept entities however translate to interfaces on Java level:

• a concept entity is generated as a Java interface defining accessors for each of its properties
• the Java class for an entity which 'has' one or more concept entities will implement the interface of the concept entities including all its accessors.

By representing concept entities as Java interfaces it is possible to develop specific business logic that makes use of these interfaces.

Concept Entity adds properties/columns to an Entity/Table

The concept entity can add new columns/properties to an entity/table. This has as consequence that special attention needs to be paid to name clashes in both property and column names. The name clash check should be done multi level over an inheritance tree.

Concept Entity with a list property

The introduction of this section only discussed concept entities which add new primitive typed or single reference properties/columns. There are however also concepts which requires a list property.

For example: in Openbravo and in many other systems it is possible to attach files to an object. The idea would be to introduce a concept: Attacheable. The attacheable concept has a property attachments which is a list of attachments. Attachments are stored in a separate table. Then every entity which requires attachments gets the concept entity 'Attachable'.

The difficulty with this model is that different entities can have attachments which has the following consequences:

• it is not possible to define a foreign key constraint from the attachment table to all the other entities.
• from an attachment it is not possible to identify the container (holder) of the attachments. At least not without storing more information.

There are two solutions for this situation:

• introduce a join table for each relation between an entity and its attachments
• use a special foreign key definition in the child table, this foreign key is a combination of an id and entity name. This combination is always unique.

The advantage of the first approach is that the datamodel has real foreign key constraints. The disadvantage is that it results in many more tables. The second approach results in no extra tables but has as large disadvantage that no foreign key constraints are maintained. These need to be maintained programmatically, in addition the hibernate mapping is more complex.

A choice has to be made which of the above solution strategies is supported.

Allowing custom code in generated business objects

Although this page is mainly about changes in the Application Dictionary there is also a related topic which can be covered in the next release of Openbravo: custom code in generated business objects.

Currently it is not possible to include hand-written code in the generated business objects. This because in the next generation run the complete class is regenerated. The current business objects are therefore 'only' data holders and have no behavior implemented. Although there is a tendency to implement a lot of business logic in service classes it still makes sense to have some code in the business object itself.

Therefore the proposal is make it possible to allow hand-written code in the generated business objects. The templating language used in Openbravo (XPand/XText) provides some possibilities for this with protected regions. It needs to be studied in more detail how protected regions can be generated into the business objects.

Also the generation logic has to be changed in that it currently removes all files in src-gen before regenerating. To support manual code this is not possible anymore, so files are regenerated individually. One thing to take into account is that in this approach files which are not related to an entity anymore (because of a name change) should be deleted explicitly.

Note: if we allow hand-written code then it is also possible to design and implement a new type of property: volatile property. A volatile property is a so-called computed property which is not stored in the database. It is computed on the fly. A separate extension (future) is to allow volatile properties to be implemented using a scripting language.

Versioning, optimistic locking

Although a small topic it influences the datamodel. Each type should have a version property and column. This is a numeric value starting from 0. The version property should be mapped as such in the hibernate mapping.

Data translation, Translatable, a concept?

Openbravo 2.50 has the concept of Trl tables which contain translated versions of data. For example, the name of a country can be translated. This is mainly used to show values of listboxes/comboboxes in the language of the user and to show translated data in reports. The Trl tables are defined explicitly for specific entities in Openbravo 2.50.

In Openbravo 3.00 the question is if we can move to a more generic concept which would allow to 'tag' any entity as being translatable. It can even be imaginable to specify translatability on property level.

If the choice is to do translatability on Entity level then it can be possibly be modeled as a concept-entity. The translatable concept can use a generic table to store the translated texts. This table has the following properties:

• a generic id referring to the object which is being translated
• a reference to the property that is translated
• the language (reference to the language entity)
• the translation

The value in the object itself then holds the default value.

To support maintenance of translations there are several options:

• the edit field/textbox of a field of a translatable property (or property of a translatable entity) will have a button/popup to specify values per language, or
• an entity has a separate 'translate' entity page which displays the properties which can be translated with a language listbox and the translated values. The translate entity page can be a generic page which builds its UI dynamically on the basis of the entity.

In addition, Openbravo core should offer easy api's to get a translated version of an object.

Rules Engines in Openbravo

TBD: summary of aspects of adding rules engines and scripting engines in Openbravo.

Data Versioning

Data versioning is a concept which allows keeping multiple versions of the same object in the database. The system should offer a specific view on this historic data. Data versioning can be used for different purposes:

• keep a detailed historical trace of an object
• allow rollback of a change or a set of changes
• make it possible to explicitly override data while keeping a track record of the old version so that when the previous version gets updated this can be analyzed and reported. Or going even further to let a system operate in specific versions.

Saving a new version of an object (actually creating a copy for the archive)

Historical trace

This concept can be achieved by having a separate history table, a common one or a separate history table for each entity. This history table should not have unique constraints and should be able to handle references to non-historized information.

Allow rollback of a change or a set of changes

The same history table as outlined above can be used. Some attention needs to be paid to rolling back as it is only possible to rollback changes in the same order as they were applied. The versioning mechanism should be tightly integrated with the database transaction, all changed/updated objects in a transaction should be versioned.

Override data, change data on multiple levels

This last purpose is very similar to code revisioning systems whereby branches are explicitly named, independently changed and merged (with conflict identification and solution). In this case the version is not automatically created when objects are changed or updated. The version is an explicit concept. Versions can inherit from eachother.

A client (to use an Openbravo term) operates in a certain version and sees all the data defined for this version and the non-overridden data in higher/older versions. As an example:

• a system has two explicit versions defined: version1 and version2, version2 is the newest and it inherits from version1.
• assume that an object A exists in two versions A-version1 and A-version2.

another object B exists only in version1.

• and a third object C exists in both version1 and version2.
• a client operating in version1 sees the following objects: A-version1, B-version1, C-version1.
• a client operating in version2 sees the following objects: A-version2, B-version1 and C-version2.
• Regarding references between objects, some examples:
  • A refers to B, meaning A-version1 refers to B-version1 and A-version2 also refers to B-version1 (as B only exists in that version).
  • A refers to C, meaning A-version1 refers to C-version1 and A-version2 refers to C-version2.

Refers to means that if A-version2 is retrieved and the code calls A.getC() that C-version2 is returned, and if A.getB() is called that B-version1 is returned.

Then when C-version2 gets removed then A.getC() will return C-version1. Note that removing C-version1 is not allowed as A-version1 refers to it and there is no C-version0.

Now how can this concept be used? There are two very usefull scenarios:

• Defining data on different levels allowing override: with the above it is possible to define data on for example client level and let it be overridable and extendable on organization level (so both client and organization can have an explicit version defined), a user works in a specific organization and therefore a specific version.
• Support customization and delivery of maintenance patches: in this scenario customizations (for example page definitions, application dictionary in general, etc.) happen in a version-2 while openbravo delivers maintenance patches in version-1. So the maintenance patch is allowed to update data in version-1 and will not overwrite changes made in version-2. The nice thing is that it is possible to analyze changes/installs made in version-1 against the changes which have been made in version-2. So if a maintenance pack is installed the system administrator can analyze which customizations need to be checked further (as the maintenance pack changed it).

The difference with the current Openbravo organization-natural-tree approach is that this versioning approach allows overriding and changing objects on a lower level. The Openbravo approach 'only' allows adding new instances on a lower level but not overriding existing instances.

Data Inheritance/Data Templating

The main purpose of an ERP system is to be able to register and maintain data. The requirements for defining data differ for the type of data. Data maintenance requirements for the system differ for example depending on the type of data (master or transactional) and the volume of data. Some data is simple other data is more complex and allows more advanced structures.

One of the more advanced requirements for a data maintenance system is to define data on different granularity levels. A good example is product data. With large product sets it is possible to divide the product set in different groups. A common characteristic of a group is that product have the same data for specific properties. Now assuming that one group can hold thousands of products it is clear that it makes sense to have some form of efficient maintenance or definition of the value of common properties (=properties that have the same value within a group).

There are different ways to do this:

• Offer a batch updating dialog for each entity. This dialog allows selecting one or more occurences of an entity and for a set of properties define the new value (and the previous value as an extra filter). Then when clicking the 'process' option in the dialog the system first analyses the data which will be updated, reports this, asks for confirmation and then updates the data. This batch updating dialog can be generated for each entity.
• Offer a form of data templating. For each entity we can allow defining one or more data templates. A data template defines values for properties of an entity. One entity can have multiple data templates, an instance (record) of that entity can at most have one data template.
• Offer a form of data inheritance. An instance of an entity can define a super instance for inheriting its data. In this case the question is which data is inherited and how to define only a limited set of data in the super instance. This because the super-instance must be a valid instance (i.e. all its mandatory fields need to be set for example). Because of the complexities around this topic, this variant is not discussed further.

Let's see how data templating works in practice using the product example. A user clicks on the new-on-tab button in a grid to create a new product. The product entity has been flagged with the data template concept and a data template field has been added to the product page. The page with the product is opened and the user sees several fields. Now assume that he/she selects the data template: high volume. This data template defines values for the order-quantity and the minimum stock level fields. By selecting the data template for these fields, these fields are disabled (not-editable) and the values are set from the data template. The same can be done on coding level, the system should offer an easy api to apply a data template to an object.

Introducting a common type

Instead of letting the user explicitly select a data template it is also possible to introduce another concept: a common type field, and let the data template be defined on type level. Assume that in the system we introduce a type entity which allows to define types for all entities by entity. So there is one type entity which has the following properties:

• name
• entity (for which the type applies)
• type values

and a type value:

• name
• label
• data template

Note a type can be implemented as a concept-entity.

When an entity has the concept: type. Then when a type is selected for an instance the system should automatically select the data template, fill the properties and make the fields in the UI non-editable.

Explicitly Model: Operations

to prevent changing generated code

Discussion 26th-28th May

• Do not separate entity and table and property and column, add entity type and property type.
• Is client/organization a concept entity --> Yes, have flexible structuring/hierarchical of information
• Active column is also concept
• Think about how concept entities can add unique keys, constraints etc.
• In the next release move to a modularized core, smaller kernel. To implement this start with new tables, start with a clean design, with clear conversion.
• Single uuid --> has many benefits, one obstacle is that mapping to existing tables with multiple keys is not possible

• combine entity and table, skip secondary table idea for now
• concept entity adds extendability in data, we also need to think about extensibility in behavior. Extensibility for example in filter in DAL or in OBInterceptor. Need to take into account that different modules can add behavior without clashing.
• concepts should be addable to an entity through a module
• the kernel module itself should only have
• concepts can be added to an entity at runtime, for example concepts should define defaults
• kernel is the module that defines the model and its visualization, it only contains the application dictionary models
• core is the module that will add current Openbravo functionality, it adds client/organization for example. This means that client/organization is not part of kernel, the kernel logic does not know about client and organization.