Sterling OMS Custom Table Creation

Sterling OMS Custom Table Creation

In this post we are going to see how to do Sterling OMS Custom Table Creation

Sterling OMS Custom Table Creation

Requirement: Create custom table with given below data structure

Other Table Creation Requirements
1) Table should have history table
2) Table should have audit records for insert, update, delete operations
3) Table should generate API’s (CRUD Operations) automatically.

What is difference between Entitydeployer vs Dbverify?

Entitydeployer: Use this tool to build database extensions. The entitydeployer calls dbverify, applies all the SQLs generated by dbverify, and then rebuilds the entities.jar file.

Command : <Install Directiry>/bin/deployer.cmd -t entitydeployer

Note : Quick Question what does -t means in above line: Ant script target name from <Install Directory>\properties\deployer.xml

DBVerify: Dbverify performs database schema creation, verification, and correction. Dbverify is used to ensure database schema integrity. Dbverify is used during the following tasks:

• Installation
• Upgrade
• Customization

Command : <Install Directiry>/bin/dbverify.cmd

Lets learn about few installation properties values related to database

sandbox.cfg Parameter	What happens when entitydeployer command called	Remarks
NO_DBVERIFY=false REINIT_DB=false	New resources.jar file gets created DB Verify program called and DDL statements gets created New entities.jar gets created DB will NOT be updated with newly added column/changes	From Development point of view this option is good. Make change in XML file get the DDL by running entitydeployer command and validate the changes and manually run the DDL on database.
NO_DBVERIFY=false REINIT_DB=true	New resources.jar file gets created DB Verify program called and DDL statements gets created New entities.jar gets created DB will be directly updated with DDL created part of DB verify program	This option is good with controlled environment (Quality testing, User Acceptance testing)
NO_DBVERIFY=true REINIT_DB=false	New resources.jar file gets created DB Verify program NOT called and DDL statements not created New entities.jar gets created No change to DB because DDL statements not created	Manually need to run dbverify.cmd to generate DDL statements and manually execute in the database.

How to modify database related properties in sandbox.cfg?

1. From <Install Directory>/properties folder take a copy of sandbox.cfg as sandbox.cfg.original
2. Search for following values in sandbox.cfg
   • LOAD_FACTORY_SETUP=true
   • NO_DBVERIFY=false
   • REINIT_DB=true

3. First make change in sandbox.cfg file
   • LOAD_FACTORY_SETUP=true (no change)
   • NO_DBVERIFY=false (DDL scripts created automatically)
   • REINIT_DB=false (Database update will not happen automatically; Have to run the DDL script manually)

4. Go to <Install Directory>/bin in command prompt
5. Run <Install Directory>/bin/setupfiles.cmd file

How to add new custom table

• Create new file install_dir/extensions/global/etc/datatypes.xml with below content

<?xml version="1.0" encoding="UTF-8"?>
<DataTypes>	
	<DataType Name="PersonRoleName" Size="20" Type="NVARCHAR">
            <Enumeration>
                <Enum Description="" Value="Admin"/>
                <Enum Description="" Value="Developer"/>
                <Enum Description="" Value="Manager"/>
                <Enum Description="" Value="Team Lead"/>
                <Enum Description="" Value="Business Analyst"/>
            </Enumeration>
	</DataType>

    <DataType Name="Boolean" Size="1" Type="NCHAR">
        <Enumeration>
            <Enum Description="" Value="Y"/>
            <Enum Description="" Value="N"/>
        </Enumeration>
    </DataType>

	<DataType Size="5" Name="Pincode" Type="NUMBER">
	   <Validation>
		 <Regex MaxLength="200" MinLength="3" JavaPattern="^[a-zA-Z0-9.,!\-/+=:]*$"              JSPattern="^[a-zA-Z0-9.,!\-/+=_ :]*$" allowNull="true"/>
	   </Validation> 
	</DataType>

</DataTypes>

• Create new file install_dir/extensions/global/entities/Custom_Table_Demo.xml with below content

<DBSchema>
   <Entities>
      <Entity ApiNeeded="Y" AuditRequired="Y" Description="Custom Person Info" Prefix="XYZ" TableName="XYZ_PERSON_INFO" XMLName="PersonInfo" HasHistory="Y" KeyType="Random" >
        <Attributes>

          <Attribute XMLName="PersonInfoKey" ColumnName="XYZ_PERSON_INFO_KEY" DataType="Key" 
	           Nullable="false" DefaultValue=" " Description="Person Info Key" />
           
          <Attribute XMLName="FirstName" ColumnName="FIRST_NAME" DataType="UserName" 
	           Nullable="false"  Description="First Name" />
           
           <Attribute XMLName="LastName" ColumnName="LAST_NAME" DataType="UserName" 
	           Nullable="false" Description="Last Name" />
           
	   <Attribute XMLName="EmpID" ColumnName="EMP_ID" DataType="Integer" 
	           Nullable="false" Description="Employee ID" />
	           
	   <Attribute XMLName="Salary" ColumnName="SALARY" DataType="Money" 
	           Nullable="true" DefaultValue="0" Description="Employee Salary" />
           
           <Attribute XMLName="RoleName" ColumnName="ROLE_NM" DataType="PersonRoleName" 
	           Nullable="false" DefaultValue=" " Description="Employee Role" />

           <Attribute XMLName="DateOfBirth" ColumnName="DOB" DataType="Date" 
	           Nullable="false" Description="Employee DOB" />

           <Attribute XMLName="Active" ColumnName="ACTIVE" DataType="Boolean" 
	           DefaultValue="Y" Description="Employee Status" />
	           
           <Attribute ColumnName="CREATETS" DataType="TimeStamp"  
                DefaultValue="sysdate" Description="Create TimeStamp" />
                
           <Attribute ColumnName="MODIFYTS" DataType="TimeStamp" 
		 DefaultValue="sysdate" Description="Modify TimeStamp" /> 
		 
           <Attribute ColumnName="CREATEUSERID" DataType="UserId" 
		 DefaultValue="&apos; &apos;" Description="Creating User ID" /> 
		 
           <Attribute ColumnName="MODIFYUSERID" DataType="UserId" 
		 DefaultValue="&apos; &apos;" Description="Modifying User ID" /> 
		 
           <Attribute ColumnName="CREATEPROGID" DataType="ProgramID" 
		 DefaultValue="&apos; &apos;" Description="Creating Program ID" /> 
		 
           <Attribute ColumnName="MODIFYPROGID" DataType="ProgramID" 
		 DefaultValue="&apos; &apos;" Description="Modifying Program ID" /> 
		 
           <Attribute ColumnName="LOCKID" DataType="Lockid" 
           	 DefaultValue="0" Description="Lock ID" />
           
        </Attributes>
        
        <PrimaryKey Name="XYZ_PERSON_IN_PK"> 
                 <Attribute ColumnName="XYZ_PERSON_INFO_KEY" />                  
      </PrimaryKey>
      
      <Indices> 
                <Index Name="XYZ_PERSON_INFO_I1" Unique="True"> 
                    <Column Name="EMP_ID" />                     
                 </Index>     
       </Indices> 
      
      </Entity>           
    </Entities>
</DBSchema>

• Run command from bin folder : deployer.cmd -t entitydeployer
• Query your database to see if column added? (No; because we updated the sandbox properties updated with REINIT_DB=false)
• Open file \repository\scripts\EFrame_TableChanges.sql

CREATE TABLE XYZ_PERSON_INFO_H (
		XYZ_PERSON_INFO_KEY CHAR(24)   NOT NULL 
		,FIRST_NAME VARCHAR2(50)   NOT NULL 
		,LAST_NAME VARCHAR2(50)   NOT NULL 
		,EMP_ID NUMBER(9)   NOT NULL 
		,SALARY NUMBER(15,2)   DEFAULT 0  NULL 
		,ROLE_NM VARCHAR2(20)   NOT NULL 
		,DOB DATE  NOT NULL 
		,ACTIVE CHAR(1)   DEFAULT 'Y'  NOT NULL 
		,CREATETS DATE  DEFAULT sysdate  NOT NULL 
		,MODIFYTS DATE  DEFAULT sysdate  NOT NULL 
		,CREATEUSERID VARCHAR2(40)   DEFAULT ' '  NOT NULL 
		,MODIFYUSERID VARCHAR2(40)   DEFAULT ' '  NOT NULL 
		,CREATEPROGID VARCHAR2(40)   DEFAULT ' '  NOT NULL 
		,MODIFYPROGID VARCHAR2(40)   DEFAULT ' '  NOT NULL 
		,LOCKID NUMBER(5)   DEFAULT 0  NOT NULL 
 )  
/
CREATE TABLE XYZ_PERSON_INFO (
		XYZ_PERSON_INFO_KEY CHAR(24)   NOT NULL 
		,FIRST_NAME VARCHAR2(50)   NOT NULL 
		,LAST_NAME VARCHAR2(50)   NOT NULL 
		,EMP_ID NUMBER(9)   NOT NULL 
		,SALARY NUMBER(15,2)   DEFAULT 0  NULL 
		,ROLE_NM VARCHAR2(20)   NOT NULL 
		,DOB DATE  NOT NULL 
		,ACTIVE CHAR(1)   DEFAULT 'Y'  NOT NULL 
		,CREATETS DATE  DEFAULT sysdate  NOT NULL 
		,MODIFYTS DATE  DEFAULT sysdate  NOT NULL 
		,CREATEUSERID VARCHAR2(40)   DEFAULT ' '  NOT NULL 
		,MODIFYUSERID VARCHAR2(40)   DEFAULT ' '  NOT NULL 
		,CREATEPROGID VARCHAR2(40)   DEFAULT ' '  NOT NULL 
		,MODIFYPROGID VARCHAR2(40)   DEFAULT ' '  NOT NULL 
		,LOCKID NUMBER(5)   DEFAULT 0  NOT NULL 
 )  
/

ALTER TABLE XYZ_PERSON_INFO_H ADD ( CONSTRAINT XYZ_PERSON_IN_H_PK PRIMARY KEY 
	(XYZ_PERSON_INFO_KEY ) ) 
/

ALTER TABLE XYZ_PERSON_INFO ADD ( CONSTRAINT XYZ_PERSON_IN_PK PRIMARY KEY 
	(XYZ_PERSON_INFO_KEY ) ) 
/

• Run the above script in database and make sure new table created successfully
• Perform build and deploy the ear

How to test ?

Need to create 5 different services to perform database operations.

Insert Record : Using HTTP API tester call createXYZPersonInfo Service with below input

<PersonInfo FirstName="FirstName1" LastName="LastName1" EmpID="1" Salary="500" RoleName="Admin" DateOfBirth="20180210" Active="Y" />

Fetch Record : Using HTTP API tester call below Services with below input and template xml.

Service Name	Input XML	Template XML
getXYZPersonInfo	<PersonInfo EmpID=’1′ />	<PersonInfo />
getXYZPersonInfoList	<PersonInfoList />	<PersonInfoList TotalNumberOfRecords=”> <PersonInfo /> </PersonInfoList>

Interview Questions Related to Custom Table Creation

1. Why extended database API can not be called directly ? Why to create services to access extended database API ? Answer : YIFApi interface does not extend APIs for custom/hang-off tables
2. How to generate Extended API’s automatically for custom table ? Answer : ApiNeeded=Y
3. How to make sure audit records gets generated for any activity on custom table ? Answer : AuditRequired=Y
4. How to generate history table (table_name_h) ? Answer : HasHistory=Y
5. How to generate primary key for column ? Which datatype has to be used ? Answer : DataType=Key
6. How to override or create new datatype ? Answer : Add new file  install_dir/extensions/global/etc/datatypes.xml
7. How to create Unique Index ?

<Indices> 
<Index Name='XYZ_PERSON_INFO_I1' Unique='True'> 
<Column Name='EMP_ID' /> 
</Index> 
</Indices>

Conclusion : This post helps to create new custom table into existing OMS database easily. Please share your feedback at support@activekite.com OR comment in the post.

Register with us to get more OMS updates

OMS Interview Questions