Projects:Operate Database/Specifications
Contents |
Operate Database - Functional Specifications
Overview
Purpose
The purpose of this project is to enable Openbravo's interface to operate database structure directly, this is, once structure is defined in Application Dictionary it should be possible to persist it in DB.
This will make easier the administrator user experience because it will not be necessary to use an external database client in order to create objects in database.
Anyway, current workflow will still available: it will be possible to create object in DB and afterwards import them into Openbravo.
Additionally wizards should be added in oder to facilitate the most commons actions in database objects modelization.
Scope
It affects Openbravo's Application dictionary, the way objects are created there and how they are defined.
References
Design Considerations
Assumptions
Dependencies
Constraints
Glossary
- Application Dictionary Openbravo stores metadata referring Openbravo's structure. This metadata corresponds to real Database Objects.
- Wizard A wizard is a group of windows that suggest the most usual values for different elements. Its purpose is to ease elements edition.
Functional Requirements
User roles & profiles
Business process definition
The workflow is as follows:
- Object edition It is performed using Openbravo's interface. Ideally wizards would be created to make this edition easier. This wizards would suggest the most usual elements (for example, for a table creation it would automatically create primary key and auditory fields). There are two possible approaches for edition:
- Approach 1: Application dictionary defines completely all required data to generate database objects. With this approach XML files could be transparent to user.
- Approach 2: Application dictionary does not define completely database objects. In this case, it would be necessary to be able to edit manually the generated XML files before they are passed to database.
- XML model files creation and Database synchronization This two steps are achieved at once (in a transparent way for user) by just clicking a button. First, Openbravo's model is persisted in XML files and afterwards database is updated with these modified files.
User stories
Functional requirements based on business processes
User Interface Mockups
Technical Requirements
XML model files creation
A process, callable from Application Dictionary interface, creates the required XML files to save the model. This is performed creating DBSourceManger objects and persisting them.
If approach 2 is implemented, when editing an existing object, the XML file should be the one from the model plus the non-standard elements. This non-standard elements are those that cannot be saved in application dictionary.
Database synchronization
Once XML files have been created, the actual DB objects are modified to fit the new model.
With approach 1, this process could be called automatically after the XML files creation.
With approach 2, before synchronizing database it would be necessary to allow user to edit manually this file in order to add/modify non-standard elements.
Application dictionary synchronization
Current synchronization processes from database to application dictionary must be reviewed in order to adapt them to the new requirements.
Wizards
Ideally wizards should be created to help user edit/create objects.
There are two possible approaches for these wizards:
- Manual windows They can be implemented as manual windows.
- New wizard element It could be standardized by adding a new element to Openbravo dictionary. This wizard would allow to define multi-purpose wizards.
At least a wizard for tables should be created, additionally it could be useful to have wizards for triggers, functions and procedures.
Table wizard
This wizard will be used for table creation/modification.
It will automatically:
- Create primary key based on the table name primary key column will be created.
- Create auditory fields created, createdBy, updated and updatedBy fields are created
- Create isActive field
Data Requirements
In order to be able to create database objects from application dictionary following approach 1, it is necessary to save in application dictionary all required data, currently it is not done.
For approach 2 it should be decided which data is the standard that must be in dictionary and which is not.
Anyway, the elements to take into account are:
Tables
Tables are defined in the AD_Table and AD_Column dictionary tables. This is what currently is not defined (or at least not completely).
AD_Table
- Primary key name a new column is needed to save this.
AD_Column
- Foreign keys currently foreign keys are implicitly defined using references, but no name is assigned. 3 possible approaches:
- This name could be created by the process (following some simple rules). Doing this it would be necessary to modify the current model to fit names to these rules. In addition it would be risky to edit DB using other client because it could not fit this patterns.
- Create a new table to store the foreign keys of a table. This would be more flexible but it would add complexity to the user experience.
- Add a new field to ad_Column to store the foreign key name (if created), this field should suggest its value depending on the reference.
- Uniqueness A new field should be added to set a column as unique. TBD: should a sequence be added in order to create uniqueness in a defined order?
- Order It is necessary to add a new field to indicate the column position within the table. Notice that current seqNo field is used to order the identifier columns.
Additionally is required to ensure that current values that are supposed to match with db values actually match, this includes these fields: isMandatory, defaultValue...
- defaultValue All the application default values that use variables (defined with @) are not set in DB, just in dictionary. Apart of that, there are currently 208 default values differing between dictionary and database.
- isMandatory It should be the same as not nullable in database, but there are 197 columns where it is not matched.
- Size fieldLength is supposed to have the data length, but currently it is not matching in all cases with actual database size. The differences are these ones:
- Numeric values There are 4014 different fields and 519 equals.
- Character values (varchar, nvarchar and char) There are only 9 differences which should be fixed.
- Dates Length is meaningless in this case.
- Type Openbravo's references are the way dictionary saves data types but they do not match always to the same type. The following table shows which references are linked to which database types and how many times:
NAME | AD_REFERENCE_ID | DATA_TYPE | COUNT(*) |
---|---|---|---|
Amount | 12 | NUMBER | 262 |
Assignment | 33 | NUMBER | 3 |
Binary | 23 | BLOB | 2 |
Button | 28 | CHAR | 149 |
Button | 28 | NUMBER | 8 |
Button | 28 | VARCHAR2 | 1 |
Color | 27 | NUMBER | 2 |
Date | 15 | DATE | 222 |
DateTime | 16 | DATE | 1053 |
General Quantity | 800019 | NUMBER | 1 |
ID | 13 | NUMBER | 454 |
Image | 32 | NUMBER | 3 |
Integer | 11 | NUMBER | 313 |
Link | 800101 | VARCHAR2 | 1 |
Link | 800101 | NVARCHAR2 | 1 |
List | 17 | CHAR | 273 |
List | 17 | VARCHAR2 | 8 |
List | 17 | NVARCHAR2 | 1 |
Memo | 34 | NVARCHAR2 | 5 |
Memo | 34 | VARCHAR2 | 2 |
Number | 22 | NUMBER | 206 |
PAttribute | 35 | NUMBER | 16 |
Price | 800008 | NUMBER | 58 |
Quantity | 29 | NUMBER | 131 |
Search | 30 | NUMBER | 509 |
String | 10 | NVARCHAR2 | 1029 |
String | 10 | VARCHAR2 | 84 |
String | 10 | CHAR | 28 |
String | 10 | NCHAR | 5 |
Table | 18 | NUMBER | 1273 |
Table | 18 | VARCHAR2 | 41 |
Table | 18 | CHAR | 2 |
TableDir | 19 | NUMBER | 2013 |
Text | 14 | NVARCHAR2 | 202 |
Text | 14 | VARCHAR2 | 14 |
Text | 14 | CHAR | 1 |
Text | 14 | CLOB | 1 |
Time | 24 | DATE | 12 |
YesNo | 20 | CHAR | 1144 |
YesNo | 20 | VARCHAR2 | 1 |
Indexes
Currently indexes are not defined in application dictionary. It would be necessary a new pair of tables (ad_index, ad_index_column) depending on ad_table to manage them. Notice that both tables are necessary because in current model there are indexes with more than one column.
Functions/Procedures
Currently these elements are not defined in the dictionary.
Triggers
Currently these elements are not defined in the dictionary.
Views
Currently these elements are not defined in the dictionary.
Non-Functional Requirements
Open Discussion Items
Closed Discussion Items
Known Issues
- In case database is modified manually and application dictionary is not synchronized with it, or XML files have been edited but they have not been passed to database and to dictionary, and the database synchronization is executed it might cause lose of manual changes.