NF11 UUID Key Tables

WORK IN PROGRESS

Feature: Support for pure UUID key tables

Goal: Technical

Developer: Carlos Ruiz

Sponsor: FH

Description

Historically iDempiere has always required a primary key _ID (integer java type, numeric(10) database type) on tables to support many features.

Apart from the features not supported for non-ID tables, the integer _ID key has some inconvenients, like: - maximum value not big enough for very high volume environments (max ID is 2.147.483.647) - the need of a sequence to generate next numbers (AD_Sequence or native sequences) - difficulty to manage multiple sequences (f.e. different numbering per tenant) - difficulty to manage replication - for official dictionary we need a centralized provider that defines the IDs for every developer

UUID (Universally unique identifier) is the standard to solve this problem, and iDempiere has implemented UUIDs as alternative keys since version 1.0 but has never fully exploited it.

So, the goal of this ticket is to enable using the alternate UUID key as a completely functional key for iDempiere tables.

In order to implement this the following changes has been done:

New data types related to UUID

There are new datatypes to define UUID based relationships:

• UUID: the alternate UUID key
• Table (UU): defines a foreign key to an UUID key, equivalent to the Table data type
• TableDir (UU): defines a foreign key to an UUID key, equivalent to the TableDir data type
• Search (UU): defines a foreign key to an UUID key, equivalent to the Search data type
• Record UUID: associated to Record_UU columns, open the Record ID editor and saves the UUID of the selected record

Note that every iDempiere table has the UUID alternate key, although we implemented recently a way to disable it when required, so it will be very rare to find an iDempiere table without UUID.

The UUIDs has been historically implemented to manage transporting data with 2Pack, and to enable using features even when the ID changes (which is typical when installing the same feature in different environments)

The new datatypes are managed as String in java and VARCHAR(36) in database.

The format of an UUID string is composed by 32 hexadecimal numbers in five groups separated by hyphens, in the form 8-4-4-4-12 for a total of 36 characters, like 123e4567-e89b-12d3-a456-426614174000

Also important to note:

• Chosen Multiple Selection Table: this is not a new data type, but it works fine with the new Table/TableDir UU types

New Windows/Tables created

For testing purposes two new windows were created:

• Test UU Based Table: with master-detail relationship based on UUID keys
  • This is a maintenance window for the table "TestUU", which is a pure UUID table without _ID key or multi-parent key.
• Test UU Detail: to manage/test as master the detail table

Changes to "Create/Complete Table" process

The process CreateComplete Table accepts now to create a table without a KeyColumn.

Note that implicitly it ALWAYS create the UUID column, and this column has the new UUID data type, it also creates automatically the corresponding entry for the UUID index and constraint.

Changes to "Create Window, Tab & Field from Table" process

The process Create Window, Tab & Field from Table requires now to define the parent link column when adding a new tab to an existing window with tab level greater than zero.

Previously this was not required as the program automatically discovered a parent link, but because multi-parent links are not a requisite for tables, then is necessary to define explicitly which is the link column to parent tab when creating a new tab.

Changes to "Migrate ID" process

The process Migrate ID now can discover and update children UUID keys.

There are no changes visible to the user, everything is done automatically.

Changes to generated X_ model classes

New UUID based constructors

The X_ generated model classes implement now two new constructors based on the UUID column, additionally to the already existing two constructors based on the _ID column.

The new constructors look like this:

‎

public X_TestUU (Properties ctx, String TestUU_UU, String trxName)

public X_TestUU (Properties ctx, String TestUU_UU, String trxName, String ... virtualColumns)
‎

Additionally, when the table doesn't have _ID keys or multi-parent keys, then the two _ID based constructors are not generated.

Another change on the model generated classes is the generation of a getValueNamePair method instead of the getKeyNamePair method when the table is UUID based.

The usage of these two constructors are similar to the _ID constructor, this is:

Creating a new record

To create a new record with the _ID constructor, developers used to write:

MTest test = new MTest(ctx, 0, trxName);

With the new UUID based constructor, the zero is replaced with the empty string, like:

MTest test = new MTest(ctx, "", trxName);
‎

Reading an existing record

To read a record with the _ID constructor, developers used to write:

MTest test = new MTest(ctx, 103, trxName);
‎

With the new UUID based constructor, the ID is replaced with the UUID string, like:

MTest test = new MTest(ctx, "d08e9de9-39e5-485c-ad50-e8bc7ee0f575", trxName);

Using the UUID constructor in old Model classes

To use the new UUID constructor with old Model classes, these classes must implement the constructor, for example in MTest class:

public MTest(Properties ctx, String Test_UU, String trxName)
{
	super (ctx, Test_UU, trxName);
}

New URL shortcut Record_UU

It has been possible to Zoom for URL using URL parameters AD_Table_ID or TableName and Record_ID.

Additionally is possible now to zoom using the new parameter Record_UU, for example:

https://test.idempiere.org/webui/index.zul?Action=Zoom&AD_Table_ID=200383&Record_UU=4e148b89-bdd9-48a6-8a8a-7609092f965c

Note the Permalink is generated using the Record Info dialog.

SELECT query from button on Record Info dialog

Also using the Record Info dialog is possible to copy to clipboard a query to get the record in a SQL editor, this query can be based now on the UUID column when the table doesn't have an _ID key.

Technical Info: IDEMPIERE-5567