Projects:Operate Database/Specifications

Contents 1 Operate Database - Functional Specifications 1.1 Overview 1.1.1 Purpose 1.1.2 Scope 1.1.3 References 1.2 Design Considerations 1.2.1 Assumptions 1.2.2 Dependencies 1.2.3 Constraints 1.3 Glossary 1.4 Functional Requirements 1.4.1 User roles & profiles 1.4.2 Business process definition 1.4.3 User stories 1.4.4 Functional requirements based on business processes 1.4.5 User Interface Mockups 1.5 Technical Requirements 1.5.1 XML model files creation 1.5.2 Database synchronization 1.5.3 Application dictionary synchronization 1.5.4 Wizards 1.5.4.1 Table wizard 1.6 Data Requirements 1.6.1 Tables 1.6.1.1 AD_Table 1.6.1.2 AD_Column 1.6.1.3 Indexes 1.6.2 Functions/Procedures 1.6.3 Triggers 1.6.4 Views 1.7 Non-Functional Requirements 1.8 Open Discussion Items 1.9 Closed Discussion Items 1.10 Known Issues

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.