Adding columns to a default table

You can add columns to tables by modifying the entity database extension XML files and then rebuilding the application database and JAR files. After the application is rebuilt, the APIs recognize these added columns and use them while storing and retrieving data.

1. Copy the <runtime_sandbox>/repository/entity/extensions/Extensions.xml.sample file as <runtime_sandbox>/extensions/global/entities/your_custom_filename.xml file OR modify your existing extension XML file.
2. Edit the your_custom_filename.xml file to add an entity tag as shown in the following example for each table you want to extend. If the tag exists, use the existing one. For a description of the XML attributes, see the table that follows the example.

   <!-- element exposed to create a column --> 
   <DBSchema Depends="" Name=""> 
     <Entities> 
       <Entity TableName="REQUIRED"> 
       <Attributes> 
         <Attribute ColumnName="REQUIRED" DataType="" DecimalDigits="" 
          CompressionSupported="false"
          UseCompression="false" QueryAllowed="false" DefaultValue="" Description=""
          Nullable="false" Size="1" Type="REQUIRED" XMLName="OPTIONAL" Name="OPTIONAL" XMLGroup=""
          SqlServerDataType="" /> 
       </Attributes> 
       </Entity> 
     </Entities> 
   </DBSchema>

   Note: If ColumnName is specified that uses a non-ASCII character set, the attributes XMLName and Name are required.

   The following sample extension XML file depicts an attribute or index when added to a table column:

   Attribute added to a table column

   <DBSchema Name="FunctionalExtensions"> 
     <Entities> 
       <Entity TableName="REQUIRED"> 
       <Attributes> 
         <Attribute ColumnName="REQUIRED" DataType="" DecimalDigits="" 
          CompressionSupported="false"
          UseCompression="false" QueryAllowed="false" DefaultValue="" Description=""
          Nullable="false" Size="1" Type="REQUIRED" XMLName="OPTIONAL" Name="OPTIONAL" XMLGroup=""
          SqlServerDataType="" /> 
       </Attributes> 
       </Entity> 
     </Entities> 
   </DBSchema>

   Index added to a table column

   <DBSchema Depends="FunctionalExtensions" Name="SystestIndexes"> 
     <Entities> 
       <Entity TableName="REQUIRED"> 
       <Indices> 
         <Index Name="ExtnIndex_1"/> 
            <Column Name="REQUIRED"/>
         </Index> 
       </Indices>
       </Entity> 
     </Entities> 
   </DBSchema>

   Attribute	Description
   Depends	Optional. Name of the extensions file that contains your dependent entities. The unnamed files are implicitly named. Internally, the ".xml" portion is removed from the file name. You must include the path relative to the repository entity dir. For example, in this case, the FunctionalExtensions.xml file resides in resources.jar in the database/entities/upgradeextensions/ directory.
   Name	Optional, unless you are using a non-ASCII character set, in which case it is required, as is XMLName. A name for this entity extensions file that identifies the extension file in case another entity depends upon this file. The unnamed files are implicitly named. Internally, the ".xml" portion is removed from the file name. You must include the path relative to the repository entity directory. For example, in this case, the SystestIndexes.xml file resides in resources.jar in the database/entities/upgradeextensions/ directory.
   ColumnName	Required. Name of the column added to this table. The ColumnName must start with EXTN_. XMLName and Name are required if this attribute is non-ASCII.
   DataType	Optional. Valid values are available in the install_dir /repository/datatypes/datatypes.xml file. Note: BLOB datatype is not supported.
   DecimalDigits	Optional. Number of digits of precision that is required after the decimal. Needed only for numeric fields.
   CompressionSupported	Optional. Attribute used to indicate whether the data compression is supported for this column. Valid values are True or False. If True, compression support is enabled. Note: If you have inserted data into a column and set both the CompressionSupported and UseCompression attributes to True, do not set the CompressionSupported attribute back to False. Doing so would result in all compressed data being retrieved without any decompression.
   UseCompression	Optional. Attribute used to compress data for this column. Valid values are True or False. If True data is compressed. Note: The value of this attribute must be set to True only if the CompressionSupported attribute is set to True.
   QueryAllowed	Optional. Attribute used to enable a compressible column to be used for queries in a List API. Valid values are True or False. If True the column can be used for queries in the List APIs. Note: If the value of the CompressionSupported attribute is set to true, then the value of this attribute must also be set to True.
   DefaultValue	Required. Used as is for the defaults clause in your database.
   Description	Optional. Description of column usage.
   Nullable	Optional. Attribute used to describe the nullable value of a field. Default is false. It is recommended to set it to true, unless it is the primary key column.
   Size	Size of the database column.
   Type	Required. Data type of the database column. This attribute also determines the type of attribute in the Java™ classes that are generated and the format of the attribute in the XML. The valid types are CHAR, VARCHAR2, NUMBER, DATE, and TIMESTAMP. Note: If DATE is specified, only the calendar date is stored. If TIMESTAMP is specified, the calendar date and time are stored.
   XMLName	XML name of the attribute, if it is different from the name of the attribute. Along with Name, this attribute is required if you are using a non-ASCII character set. Choose a name that does not conflict with the base extension. It is recommended that you use Extn as a prefix. It is also recommended that you use the same convention for arriving at the XMLName as the Sterling Order Management System base product does: Make each letter follow the underscore in the column name uppercase, and the rest lowercase. Then, remove the underscores. Thus, Extn_Item_Id must be ExtnItemId.
   XMLGroup	If present, indicates the child tag in which the attribute is present. If the attribute is not present in the XML, use the NOT_SHOWN string. The XMLGroup must be Extn. Thus, the data for the extended columns is in a separate element in the API XML output.
   VirtualDefaultValue	Optional. This attribute is applicable to Null Columns. When a null is returned from the database, it is stored in memory as the virtual default value.
   ForceUpperCase	Optional. If a "case-insensitive" search is required for a text column, this field must be set to "True". If set to "True", the system converts the data that is entered in this field to uppercase.
   ResizeForMultibyte	Some of the master data entity localization tables have a column size that does not support multibyte characters. Use this attribute when you want to increase the character size of such a column or an existing column. If the attribute is set to Y, it helps to resize the length of a column when the SUPPORT_MULTIBYTE property is set to true in the sandbox.cfg file. For more information about the feature, see Support for multibyte characters in the database

3. Create an Attribute tag for each column that you want to add to the table.
4. Extend the corresponding API templates by following the steps described in "About Extending API Templates."
5. Build and deploy your extensions. Building database extensions runs the dbverify tool, which applies dbverify-generated SQLs to the database.