Projects:Data Access Layer/Functional Specification
Contents |
Data Access Layer - Functional Specifications
Overview
Purpose
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.
Scope
References
Design Considerations
Assumptions
Dependencies
Constraints
Glossary
Functional Requirements
User roles & profiles
- Openbravo Developer: This type of users have a high level of professional education (computer science in most of the cases) and have a deep knowledge of the core, product, and business processes. They need DAL to offer a versatile way of manipulate business objects and database access. In some cases they need to write complex queries and DAL must support it.
- Consultant: This type of users have a high level of professional education, have a deep knowledge of the business processes. They need DAL to offer a simple and productive way of access business objects and database.
- WAD: Wizard for Application Dictionary. Is a computer program that automatically generates Java source code based on the Application Dictionary.
Business process definition
User stories
- Story 1:
- John is a Openbravo developer. He wants to develop a new manual window that is not achievable by describing the window in the Application Dictionary.
- John needs to access the database through a complex query but he wants to do it in Standard SQL.
- Story 2:
- Mike is a Consultant and he wants to develop a new report for his customer
- He opens iReport to build the JasperReport template
- First of all he choose the data source of the available ones:
- JavaBeans set data source
- Hibernate connection
- Spring loaded Hibernate connection
- Others ...
- He needs a easy and productive way of develop the template
- Story 3:
- WAD is the computer program that generates code based on the Application Dictionary.
- WAD needs to generate code without compilation
Functional requirements based on business processes
General
| 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 |
Productivity
| 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 |
Versatility
| 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 |
Security
| 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 |
Other
| 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:
- AD_Table
- TableName: It maps the physical table name in the database.
- AD_Column
- ColumnName: It maps the physical column name in the database.
- IsKey: Indicates whether the column is primary key. Each table should only have one primary key column. Currently some tables have more than one (this is set with IsSecondaryKey column) but this is going to be modified in order to standardize to a single primary key.
- AD_Reference_ID: Defines the column data type. Data types can include simple data types as Text, Integer, etc. or other reference types as Search, List or Table.
- AD_Reference_Value_ID: This column is used for complex references, it is explained more in detail below. in the Relationships description chapter.
- FieldLength: Maps the data size in database.
- IsIdentifier and SeqNo: Define the identifier for the record. It is explained below.
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
References are used to define data types and relationships between different tables.
=Model=
The tables used to describe references with their main columns are:
- AD_Reference: This table contains the description of all the references in the dictionary.
- ValidationType: Defines which is the type for the reference. It can be:
- DataType, meaning that is a main reference which can be directly used in AD_Column.AD_Reference_ID.
- Table which are defined within AD_Ref_Table table.
- Search which are defined within AD_Ref_Search table.
- List which are defined within AD_Ref_List table.
- ValidationType: Defines which is the type for the reference. It can be:
- AD_Ref_Table. Used for Table references description.
- AD_Table_ID. Is the table that is going to be referenced using this reference.
- AD_Key_ID. Is the column from the referred table which value will be stored in the column using the reference.
- AD_Display. Is the column that will be shown in the user interface to identify a single record.
- WhereClause. SQL where statement that limits the records that participate in the reference.
- AD_Ref_Search. Used for Search references description. Additionally to this table AD_Ref_Search_Column table is used to define the in/out columns.
- AD_Table_ID Is the table referenced by this reference.
- AD_Column_ID Is the column in the referenced table that will be taken as value for the column with this reference type.
- AD_Ref_List Defines List references which actually are lists of values.
- Value Is the actual value that the column will take.
- Name Is the name the user will see when selecting values. Note that this name is translated to different languages in the AD_Ref_List_Trl table.
=Relationship references=
Some references define tables relationships, the main references to do this are stored in AD_Column.AD_Reference_ID:
- TableDir: In case the column follows the naming rules this is the way to define direct relationship with another table. The rule is that the column must be named as the referenced table plus "_ID". Thus to make a TableDir reference to C_BPartner table there must be a column named C_BPartner_ID
- Table: This reference is used to make reference to another table without the need following the previously defined naming rules, it is also used to enable references not to a whole table but to a subset of it. When it is used a table reference must be set to AD_Column.AD_Reference_Value_ID to indicate which of the table references defined is going to be used.
- Search: The main difference between this reference and the two previous ones is in the user interface, but internally to obtain the referenced value works in a similar way. While the other two are shown as a combo selector, this one invokes a selector which allows more complex search of elements.
Identifiers
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.