Adding a column to a standard table

About this task

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

To add a column to a standard table:

Procedure

  1. Copy the install_dir/repository/entity/extensions/Extensions.xml.sample file as install_dir/extensions/global/entities/your_filename.xml file OR modify your existing extension XML file.
  2. Edit the your_filename.xml file to add a new entity tag as shown in the following example for each table you want to extend. If the tag already 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 using 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 which contains your dependent entities. The unnamed files will get implicitly named. Internally, the ".xml" portion is removed from the file name. You must include the path relatvie 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 will get 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 required after the decimal. Needed only for numeric fields.

    CompressionSupported

    Optional. Attribute used to indicate whether or not 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 should 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 should 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. Nullable=true is allowed for all columns except Primary Key Attributes and Entity Relationships.

    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 strongly recommended that you use the same convention for arriving at the XMLName as the Sterling Selling and Fulfillment Foundation base product does: Make each letter following the underscore in the column name upper case, and the rest lower case. Then, remove the underscores. Thus, Extn_Item_Id should 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 should be set to "True".

    If set to "True", the system converts the data entered in this field to uppercase.

  3. Create a new Attribute tag for each column 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.

Results

A special case of extending columns for adding unique tag identifiers or descriptors is explained in Adding unique tag identifiers and descriptors to a standard table.