View source | View content page | Page history | Printable version   

Projects:Data Access Layer/Functional Specification


Data Access Layer - Functional Specifications



The purpose of this project is to design and integrate or build a new java persistence mechanism to implement the Openbravo Data Access layer. Based on the metadata stored in Openbravo Application Dictionary the new layer will provide java methods to get data from the database and to modify (insert, update and delete) that information. It will take care of base requirements while accessing to the data such as locking mechanisms, security, logging and audit info. It should be based on open technologies to minimize the entry barriers to Openbravo.


This new data access layer will replace in the future current Sqlc, but they will coexist for some time to have a gradual and smooth migration.

In a future release Openbravo aims to replace all PL/Sql code by java methods implemented in the Service Layer and by this mean get database independence.



Design Considerations





Functional Requirements

User roles & profiles

Business process definition

User stories

Functional requirements based on business processes

Num Requirement Importance Status
1.1 Based on metadata information stored in the Application Dictionary (AD_Table, AD_Column, AD_Reference and x_Access) provide java methods to get and modify (insert, update and delete) information stored in Openbravo database. The data access layer should be independent from metadata related to UI such as AD_Window, AD_Tab and AD_Field. Must have To be started
1.2 Data sources provided should implement standard javabean interface to allow easy integration with other tools. Bean attributes should have specific java types (String, Integer, BigDecimal, Date, ...) Must have To be started
1.3 Support for localization Must have To be started
1.4 The data access methods should be provided by interpreting online (at execution time) the information stored in the AD, without any need to compilation (required for a SaaS operation) Must have To be started
1.5 Database independence. The persistence mechanism should not use any specific database stuff. Must have To be started

1.6 The persistence mechanism should be based on open technologies (JDO, JPA, Hibernate, iBatis,...) to minimize barrier entries. Must have To be started

Num Requirement Importance Status
2.1 Provided methods should have a syntax as compact as possible. It should support shortcuts based on embedded rules (implicit join and filtering, programatic behaviour based on parameters) Must have To be started
2.2 Accessor methods in two modes: process (just the data object) and view (identifiers resolved) Must have To be started
Num Requirement Importance Status
3.1 It has to be able to generate access methods to support current system requirements (record ranges, filtering options, orderBy clauses, ...). ***Prepare a list of sql statement types Must have To be started
3.2 WAD will be a intensive user of this layer and it has to provide easy and compact ways to invoke methods based on AD UI's. Must have To be started
3.3 Trigger support (refreshing the javabean after modifying it). ***Discussion: how to implement triggers in the Service/Data Access layer Must have To be started
Num Requirement Importance Status
4.1 Data access methods will implicitily retrieve context information (User, Role, Client, ...). Must have To be started
4.2 Based on context info the data access layer will ensure implicit/explicit security rules: access (read/write), filtering (client, organization, other,...) for read and write, field access? Must have To be started
4.3 Audit info support and other logging information (such as client) based on context info Must have To be started
Num Requirement Importance Status
5.1 Performance and resource management. Is cache useful? Associated tables/relationships? PreparedStatements vs. Statements Must have To be started
5.2 Locking mechanisms (optimistic/pessimistic) Must have To be started
5.3 Expose database through CRUD web services Must have To be started
5.4 Traceability Must have To be started
5.5 Search engine indexation Must have To be started

User Interface Mockups

Technical Requirements

Metadata for model description

All the information required for the model description is stored in Openbravo's application dictionary, this includes the description of the physical database objects (as tables and columns) as well as their relationships.

Physical description

Physical model is stored in the AD_Table and AD_Column application dictionary tables. These two tables map the actual database tables and columns in a direct manner: for each physical table (or view) there is a record in the AD_Table and for each of their columns there is another one in the AD_Column. The most important columns in these tables are for model description are:

Relationships description

Relationships between different database tables are also stored in application dictionary. When a column points to another table the primary key for the referred table is stored in the first one, but in the user interface it is not used the primary key value (which is a non human significant value) but the identifier. These relationships are defined by references in the AD_Reference_ID and AD_Refence_Value_ID columns in AD_Column table.


References are used to define data types and relationships between different tables.


The tables used to describe references with their main columns are:

=Relationship references=

Some references define tables relationships, the main references to do this are stored in AD_Column.AD_Reference_ID:


At user interface level whenever a relationship to another table is performed the value the user sees is not the primary key from the referenced record but its identifier values.

Identifiers are defined in AD_Column table using the IsIdentifier column to define all the columns that will participate in the identifier and SeqNo to define the order the will be in.

Note that in case a column that references to another table is included as identifier for a table, user will not see its actual value but the identifier for the referenced table.

Metadata for security description

Non-Functional Requirements

Open Discussion Items

Closed Discussion Items

Retrieved from ""

This page has been accessed 14,879 times. This page was last modified on 8 June 2012, at 05:27. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.