This Technote describes in ACC:

  1. how works the feature "Sequence auto-generation"
  2. how works the new API GenCreateOrUpdateSchemaSequenceDDL
  3. the change of sequence use for some OOB schemas

Background

Actually, in ACC, schema ID values could be automatically generated by setting autopk="true" in the root element.

Without this attribute (or its value is set to false), the ID values will be fed manually by using Javascript or workflow.

In case of autopk="true", the ID will use the sequence name passed in the attribute pkSequence. In case of absence of pkSequence, by default, ACC uses OOB XtkNewId sequence.

To sum up :

screen_shot_2018-04-18at111737

The main purpose of this feature is to avoid, as much as possible, to use XtkNewId sequence, especially for custom schemas and for some OOB schemas (XtkWorkflowLog, etc.). Instead, we will generate dedicated sequences for them. 

Description

Sequence auto-generation for custom autopk sequence*

This feature contains the following functionalities:

  1. During PostUpgrade, create an option XtkSequence_AutoGeneration of type date/time (date/time of PostUpgrade)

  2. If a custom autopk schema is created BEFORE the value of XtkSequence_AutoGeneration (by checking schema's createdDate), by default, we don't make any change.

  3. If a custom autopk schema is created AFTER the value of XtkSequence_AutoGeneration, a dedicated sequence will be auto-generated during the generation of the schema. This sequence will have, in general**, a name as auto_<nameSpace><schemaName>_seq

  4. There's a possibility to disable this feature by setting option XtkSequence_AutoGeneration to a future date (for example 01/01/2999)

  5. Databases supported: PostgreSQL, Oracle and MSSQL

  6. This feature is not for FDA schemas

  7. Extended schemas will continue to use their parent schemas' sequences.

Note:

* custom autopk schema: a schema not having OOB namespace (i.e. nl, nms, xtk, crm or ncm), with autopk="true" and without specified pkSequence

** if the auto-generated sequence name has more than 30 characters, we will moderate the part <nameSpace><schemaName> using CRC32

New "GenCreateOrUpdateSchemaSequenceDDL" API

This feature is implemented thanks to introducing a new API, which generates the SQL script used to create or update autopk schema sequence:

GenCreateOrUpdateSchemaSequenceDDL(schemaID, newSequenceName, oldSequenceName)
 
  schemaID (mandatory): "<nameSpace>:<schemaName>"
  newSequenceName (mandatory): new sequence name
  oldSequenceName (optional): old sequence name

Generally, we call this API to create autopk schema sequence, in which case "oldSequenceName" is empty. For example, when creating a custom autopk schema, we call internally this API with its schemaID and the auto-generated sequence name, then update the database structure to create the sequence in the database.

This new API can be also used to update an autopk schema sequence, by providing "oldSequenceName". For example: there was a custom autopk schema "cus:mySchema" using "XtkNewId" as its sequence. And now we would like to use a new sequence "mySequence". It can be done by calling the follow in javascript and execute the output DDL statements in database. 

GenCreateOrUpdateSchemaSequenceDDL("cus:mySchema", "mySequence", "XtkNewId")

In this case, the new sequence will begin from the largest Id of the schema.

XtkWorkflowLog Schema - Switch to a dedicated sequence (from XtkNewId one)

The XtkWorkflowLog schema could overuse the XtkNewID sequence. From ACC 18.4 release, the schema XtkWorkflowLog will use a new dedicated sequence XtkWorkflowLogId instead of XtkNewId.  The switch will be done automatically at build upgrade to ACC18.4 for already existing instances. For new 18.4 instances, the XtkWorkflowLogId sequence will be used by default.

Note:

Some other schema that could over consumed id will be switch to a dedicated sequence in next releases.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy