|
You can specify that a domain must generate unique values. The result is that, in each column based on the domain, for each new record, the application will automatically generate a numerical value that is unique for that record in that column.
Unique values may be generated by an Oracle sequence, a USoft sequence, or an external component. The default setup for unique values is that the domain has Generate Unique Values = Database. With that setting, values are generated by an Oracle sequence (if the RDBMS is Oracle) or a USoft sequence.
Generated unique values are meaningless key values that act as a handle for recognising each individual record. Generated unique values are thus appropriate for primary key columns.
Unique values in primary keys are system-generated, not user-defined. Users are not allowed to enter or change generated values in on-screen fields: these fields are query-only.
In a USoft Relationship, you must use the same domain for the primary key column(s) and for the foreign key column(s) that refer to it (them). If the primary key has generated unique values, these will be unique across the parent table. However, corresponding foreign key values in the child table will typically NOT be unique across the child table.
If a column based on a domain with generated unique values is PART of a primary key, in other words, if it is one of the columns of a multi-column primary key, then unique values will be generated accordingly. In this case, it is the multi-column value combination that will be unique, not (or not typically) the individual column values.
|
The Generate Unique Value domain attribute allow unique values to be generated, but uniqueness of values in a column is ultimately checked not by this attribute but by unique indexes.
USoft Definer automatically creates a unique index for each primary key that you define. In addition, you can manually add additional indexes for which you set Unique = Yes.
|
For columns based on a given domain, to have unique values generated by an Oracle sequence:
| 1. | In a USoft application running against Oracle, open the Domains window for the domain for which you want unique value generation. |
| 2. | Set Generate Unique Values = Database. |
By default, sequence numbers start with 1, and increment by 1. In an Oracle environment, however, you may change these settings:
| 3. | In the "Sequence Definitions (Oracle Only)" box, specify the increment, the starting value, the maximum and minimum values, and the cycle, cache, and order values. |
|
If you have unique values generated by a USoft sequence, USoft will simulate an RDBMS sequence in the TABLE_SEQUENCE_NUMBERS table.
To have unique values generated by an Oracle sequence:
| 1. | In a USoft application running against a different RDBMS than Oracle, open the Domains window for the domain for which you want unique value generation. |
| 2. | Set Generate Unique Values = Database. |
|
You can have unique values generated by an RDMI component that you write yourself and declare in USoft Definer. The advantage is that you can determine all the details of unique value generation yourself. The disadvantage is that this option requires more work, especially to maintain the solution in the long run.
For each primary key column based on the domain, your component needs to have a separate method called "<Table Name>GETSEQUENCENUMBER", for example: "PERSONGETSEQUENCENUMBER". The corresponding implementation, eg., "PersonGetSequenceNumber()", must executed the desired behavior.
To have unique values generated by an RDMI component:
| 1. | Make a first implementation of the component and declare the component in the RDMI Components window or tab. |
| 2. | In the Domains window, set Generate Unique Values = Component. |
| 3. | in the dropdown box immediately to the right of the Generate Unique Values field, using the small lookup button to its right, identify the component that you want to use. |
Now, each time you base a primary key column on this domain, a <TableName>GETSEQUENCENUMBER method is added to the component definition. Parameters are added for the other primary key columns.
| 4. | (Re-)write the <TableName>GetSequenceNumber() method in the component code, and let this method provide the sequence value. This method is invoked when inserting a record. |
NOTE: You can have a component generate the unique values for the primary key of a database table, but also for the primary key of a component table.
|
 Getting the last generated unique value
If a table contains a key column based on a domain that has Generate Unique Value = Database (or: = Component), then each time a new row is created in the table the Rules Engine automatically generates a new unique value for that key column.
If you are in client/server mode, you can see this new value directly after inserting the row. But if you are using the Remote Rules Service API or the Rules Service, the underlying protocol (ODBC or JDBC) which allows you to communicate with the Rules Engine does not provide an 'update notify' mechanism, so that you cannot see the new value for your key. It is then quite difficult to retrieve the row that you have just inserted since you do not know its key.
To work around this problem, USoft provides a simple mechanism. The Rules Engine keeps the last unique value it has generated, and this value can be retrieved by the statement:
invoke RulesEngine.getLastGeneratedUniqueValue
The Rules Engine only retains the generated value of the LAST USER-DEFINED record. It does not heed INSERTs performed by non-interactive constraint action.
|
See Also
Domains
|
