Projects/UUIDs Usage/Functional Specifications

UUID Identifiers - Functional Specifications

Overview

Purpose

Current implementation

Currently in Openbravo, database records are identified by one (for some tables it is two) numeric column which is known as primary key (PK). The value of a primary key cannot be repeated for the same table, or in other words once a primary key is taken it can't be used again in the same table while the record that identifies it is still in the table. Before any insertion in database a PK is requested and reserved. This PKs are sequential numbers: for each request the reserved number is the next to the last reserved one for the table where it is going to be used. Additionally for each table two ranges of primary keys are maintained: one is for the system entries and another one for the common data entries. System entries are the ones that can be used by the system (they define application dictionary data and information shared among all the users).

As system information is intended to be shared between different installations the PKs for this kind of data is generated in a way that do not provoke collisions inter-installations, making thus possible to work different developers in different projects inserting data in the same tables at once. The way numbers are generated for these identifiers is having a different range for each development environment (it is called development environment ID).

Problems

The current approach has two main problems:

• It is necessary to ask for a development environment ID before starting any development. Also, the developments are very difficult to be shared among installations.
• Non-system data cannot be shared in any case because the PKs range is the same for all installations.

These two problems (specially the second one) need to be solved before being possible to have an efficient way to develop modules that can include common data as well as dictionary modifications.

Another related problem is that even though most of the tables have as primary key a single numeric column there are some of them with two columns as primary key. This causes problems because it is not the Openbravo standard way to treat records.

Solution

The solution is the replacement of the current system to obtain PKs by a new system using Universally Unique Identifiers (UUID). Doing so it is guaranteed that every database object has an unique identifier regardless the environment it was created in.

Scope

This project will affect all the tables in the application modifying their structure to be able to store UUIDs as primary key as well as all the queries to these tables.

Glossary

• Primary key A primary key is a group of columns (currently in Openbravo 1 or 2) that identifies univocally a single record in a database table.
• UUID stands for Universally Unique Identifier, it consists in 128bits that is almost impossible to be repeated being possible thus to be used to identify objects univocally.

Functional Requirements

Business process definition

Before an insertion of any record in database a new UUID is obtained to identify it, the insertion is performed using this identifier as primary key.

User stories

Mike downloads Openbravo standard and he localizes it to his country including some specific processes and the taxes used in the country. Once he has finished the localization he packages it as a module and uploads it.

Maria downloads Openbravo standard and after working with it for a while she downloads the Mike's localization module and installs it without being bothered about the used PKs.

Functional requirements based on business processes

1st Iteration: Prepare UUID generation infrastructure

Requirement	Importance	Status	Estimated time	Comments
Create functions to generate new UUIDs in Oracle, PostgreSQL and Java	Must have	Complete	2d	In PostgreSQL it is necessary to have at least 8.3 with contrib/uuid-ossp installed.

2nd Iteration: Use UUID as PK identifier

Requirement	Importance	Status	Estimated time
Modify db structure and dictionary references to change all the primary key columns, foreing key columns and columns that store references but are not marked in db as so to a type suitable for UUIDs storage.	Must have	Complete	2d
Edit manual code to ensure it works with this new pk format. This includes xsql files and PL code	Must have	Complete	10d
Deprecate AD_Update_Sequence procedure and all the related stuff.	Must have	Complete	2h

3rd Iteration: Standardize double PK tables to single PK

Requirement	Importance	Status	Estimated time
Modify db structure and dictionary references for tables with double primary key to transform them into single primary key.	Should have	Complete	1d
Prepare an upgrade system for existing installations that transforms data in double primary key tables to single primary key.	Should have	Complete	2d

Known Issues

Once this project is applied support for PostgreSQL is for at least v8.3 and it will be necessary to have contrib/uuid-ossp module installed.