Adaplet Case Sensitivity

Adaplets generate SQL independently of 3DSpace, and can be configured to be case-sensitive or case-insensitive when working with 3DSpace.

To make an adaplet operate in case-insensitive mode, add the following line to the adaplet mapping file:

item casesensitive false

Without this line, adaplets behave in case-sensitive mode. Adding this line to an adaplet mapping file causes 3DSpace to generate SQL against the foreign database that wraps all arguments inside of an upper() expression. This does not assure the foreign database was designed and indexed in a way that makes the case-insensitive operation viable.

The foreign database might need its own configuration to run in case-insensitive mode.

If the adaplet has case-sensitivity turned on and is in extend or migrate mode, then types must use mapped ids. Queries on types without mapped ids will produce Oracle constraint errors. Also, you need to be absolutely sure that there are no entries in the adaplet tables with case-sensitive data. In Oracle, this means that the tables must have function-based indices added if they do not already exist.

As an example, consider the following table:

TableName: Item 
	Columns:
	  ITEMID   : VARCHAR2(9)
	  ITEMNAME : VARCHAR2(30)
	  UOM      : VARCHAR2(10)
	  LOCKER   : VARCHAR2(15)

The mapping file has a type ITEM defined as the following:

type ITEM {
	    id             ITEM(ITEMID) mapped;
	    name           ITEMNAME;
	    description    DESCRIP;
	    attribute      "UOM-u" UOM;
	    locker         LOCKER;
	    default policy PART-u;
	    default owner  mbom-u;
       }

This definition means that the ITEMID column in the Item table is considered unique. After the 3DSpace configuration for case-insensitivity is done (including the system setting and mapping file updates), you need to modify the table in SQL with the following:

drop constraint ITEM_itemid_UK on ITEM(ITEMID);

If a unique constraint already exists on the table, execute this SQL:

create unique index ITEM_itemid_UK on ITEM(upper(ITEMID));

This command ensures that the table cannot have two rows with case-sensitive data for the column ITEMID.

In the same table above, if the mapping file defines the type ITEM as below:

type ITEM {
	    id             ITEM(ITEMID,ITEMNAME) mapped;
  	  name           ITEMNAME;
 	   attribute      "UOM-u" UOM;
	    locker         LOCKER;
	    default policy PART-u;
	    default owner  mbom-u;
	  }

then the unique index that gets added should be:

create unique index ITEM_itemid_UK on ITEM(upper(ITEMID),upper(ITEMNAME))

For case-insensitivity to work on adaplet vaults, any string columns that need to be unique in a table must be added to the unique index with the upper clause wrapped around them.

When an asterisk is not included in the name field of a find operation on an adaplet vault (that is, if the name is explicitly specified), objects are shown with the name in the case as entered by the user, and not necessarily as it exists in the database. The same is true when explicitly specifying object names to be exported: the object is referenced in the export file with the name in the case as entered.