Add Resource Scripts through Database

1. Knowledge of Unix Commands and SQL Queries
2. Postgres database Details
   • Database Server IP
   • Server Root user Credentials
   • Database Username and Password
3. Application Server Details
   • App Server IP
   • Server Root User Credentials
4. Permission to access the Application Server and Database Server

1. Click here to see a list of existing resource types, the resource type that you insert should be different from the one already available.
2. Alternatively the following select query can also be executed to check the uniqueness of the resource type:
   • select count(*) from JCO_RESOURCE_TYPE where type=’Resource type’;
   • If the result is 0 – The resource type entered is unique.
   • If the result is 1 – The resource type already exists.

   <!-- Change sets starts -->

   <!-- To insert a new resource type to the DB starts -->

   <changeSet author="7.12.6.2" id="ResourceType_route53_adding_201809041200"> <!—Replace with unique Resource type and timestamp--> 
    <insert tableName="JCO_RESOURCE_TYPE">
    <column name="ID" valueComputed="(SELECT MAX(ID)+1 FROM JCO_RESOURCE_TYPE)"/>
    <column name="TYPE" value="route53"/> <!—Replace with Resource type-- >
    <column name="DESCRIPTION" value="route53"/> <!—Replace with Resource type-- >
    <column name="CLASSNAME" value="com.jamcracker.commons.entities.iaas.Generic"/>
    </insert>
   </changeSet> 
   <changeSet author="7.12.6.2" id="ResourceType_route53_msg_201809041200"> <!—Replace with unique Resource type and timestamp-->
    <insert tableName="JCP_UI_METADATA">
    <column name="RESOURCE_KEY" value="jsdn.store.resourcescript.resourcetype.route53"/> <!—Replace with Resource type, Mandatory should be same as the one mentioned in the above changeset-- >
    <column name="ORGANIZATION_ID" valueNumeric="1000"/>
    <column name="LANGUAGE_CODE" value="en_US"/>
    <column name="RESOURCE_TYPE" value="M"/>
    <column name="RESOURCE_VALUE" value="Route53"/> <!—Replace with Resource type, Value that appears in the UI-- >
    </insert>
   </changeSet>

   <!-- Change sets ends -->

   <!-- Insert a new resource type to the DB Ends -->

   <!-- To update JCI_PROVIDER_RESOURCE_TEMPLATE_S sequence starts -->

   <changeSet id="RESOURCE_TEMPLATE_SEQ_UPDATE_201809071110" author="7.12.2" dbms="postgresql"> <!—Replace with the time stamp--> 
    <comment>JCI_PROVIDER_RESOURCE_TEMPLATE.JCI_PROVIDER_RESOURCE_TEMPLATE_S update</comment>
    <sql>select setval('JCI_PROVIDER_RESOURCE_TEMPLATE_S', cast((select max(PROVIDER_RESOURCE_TEMPLATE_ID)+1 from JCI_PROVIDER_RESOURCE_TEMPLATE) as bigint))</sql>
    </changeSet>

   <!-- To update JCI_PROVIDER_RESOURCE_TEMPLATE_S sequence ends -->

   <!-- Change sets starts-->

   <!-- To add new resource template into the DB Starts -->

3. Click here to see a list of existing resource types templates, the resource template code that you insert should be different from the one already available.
4. Alternatively the following select query can also be executed to check the uniqueness of the template code.

   • select count(*) from JCI_PROVIDER_RESOURCE_TEMPLATE where TEMPLATE_NAME=’Template Code’;

   • If the result is 0 – The resource type entered is unique.

   • If the result is 1 – The resource type already exists.

<changeSet author="7.12.6.2" id="Template_createZone_adding_201809041200"> <!—Replace with unique Resource template name and timestamp-->
 <insert tableName="JCI_PROVIDER_RESOURCE_TEMPLATE">
 <column name="PROVIDER_RESOURCE_TEMPLATE_ID" valueComputed="${nextvalue_JCI_PROVIDER_RESOURCE_TEMPLATE_S}"/>
 <column name="RESOURCE_TEMPLATE_TYPE_ID" valueComputed="(SELECT resource_template_type_id FROM jci_resource_template_type WHERE resource_template_type_code='ansible')"/>
 <column name="PROVIDER_CODE" value="aws"/> <!—Replace with the provider code, an ondemand provider needs to exist on JSDN, if the provider does not exist please onboard a ondemand provider-->
 <column name="ACTOR_ID" value="1000"/>
 <column name="RESOURCE_TYPE_ID" valueComputed="(SELECT ID FROM JCO_RESOURCE_TYPE WHERE TYPE='route53')"/> <!—Replace with the Resource type-- > 
 <column name="TEMPLATE_ACTION_CODE" value="create"/>
 <column name="TEMPLATE_NAME" value="createZone"/> <!-–Replace with Template Name-->
 <column name="TEMPLATE_CONTENT" value="Create Zone"/> <!-–Replace with Template Name-->
 <column name="STATUS" value="A"/>
 <column name="CREATED_DATE" valueDate="${SYSTEMDATE}"/>
 <column name="CREATED_BY" value="1002"/>
 <column name="UPDATED_DATE" valueDate="${SYSTEMDATE}"/>
 <column name="UPDATED_BY" value="1002"/> 
 </insert>
</changeSet>

<!-- Adding new resource template into the DB Ends -->

<changeSet id="Template_createZone_update_script_201809041200" author="7.12.6.2"> <!—
Replace  with unique Resource template name and timestamp provided above in the previous query-->
	<comment>updating JCI_PROVIDER_RESOURCE_TEMPLATE  records . . .</comment>
    	<sql splitStatements="false">
update JCI_PROVIDER_RESOURCE_TEMPLATE set TEMPLATE_CONTENT='- hosts: localhost
  connection: local
  vars:
   zone: ""
  tasks:
   - name: Creating {{zone}} zone
     route53_zone:
      aws_access_key: "{{ accessKey }}"
      aws_secret_key: "{{ secretKey }}"
      zone: "{{ zone }}"
      state: 'present'
      comment: This zone created for {{zone}}'  <!—Replace with the Custom template script --> 
where PROVIDER_CODE='aws' and ACTOR_ID =1000 and TEMPLATE_ACTION_CODE='create' and TEMPLATE_NAME='createZone' <! – Replace with the template name, mentioned above-->
    </sql>
</changeSet>

<!—Update resource script to existing template into DB ends -->

<changeSet id="Template_createZone_update_variables_201809041200" author="7.12.6.2"> <!—Replace with the template name and timestamp-->
        <comment>updating JCI_PROVIDER_RESOURCE_TEMPLATE for Azure stack tenant mapping Stub</comment> 
<sql splitStatements="false">
update JCI_PROVIDER_RESOURCE_TEMPLATE set SCRIPT_INPUT_JSON='{"optional":[],"layout":"double","required":[{"help":"jsdn.resourcescript.help.zone","validation":"jsdn.resourcescript.validation.zone","value":"","label":"jsdn.resourcescript.lable.zone","key":"zone","validationmsg":"jsdn.resourcescript.validationmsg.zone"}]}' 
<!—Construct a JSON with Optional or Required fields-->
where PROVIDER_CODE='aws' and ACTOR_ID =1000 and TEMPLATE_ACTION_CODE='create' and TEMPLATE_NAME='createZone' <!—Replace with the Provider Code and Template Name defined above-->
    </sql>
</changeSet>

<!-- Updating resource script variables in json format to existing template into DB ends -->

<changeSet author="7.12.6.2" id="Template_createZone_msges_201809041200"> <!—Replace with the template name and timestamp-->

    <insert tableName="JCP_UI_METADATA">
		    <column name="RESOURCE_KEY" value="jsdn.store.resourcescript.template.createZone"/> <!—Replace with the template name defined above-->
		    <column name="ORGANIZATION_ID" valueNumeric="1000"/>
		    <column name="LANGUAGE_CODE" value="en_US"/>
		    <column name="RESOURCE_TYPE" value="M"/>
		    <column name="RESOURCE_VALUE" value="Create Zone"/> <!—Replace with template name--> 
	</insert>

    <insert tableName="JCP_UI_METADATA">
		    <column name="RESOURCE_KEY" value="jsdn.resourcescript.help.zone"/> <!—Replace with the variable that is part of the script--> 
		    <column name="ORGANIZATION_ID" valueNumeric="1000"/>
		    <column name="LANGUAGE_CODE" value="en_US"/>
		    <column name="RESOURCE_TYPE" value="M"/>
		    <column name="RESOURCE_VALUE" value="Help message for zone"/> <!—Replace with the  resource value for the variable that is part of the script-->
	</insert>
    <insert tableName="JCP_UI_METADATA">
		    <column name="RESOURCE_KEY" value="jsdn.resourcescript.validation.zone"/> <!—Replace with the variable that is part of the script--> 

		    <column name="ORGANIZATION_ID" valueNumeric="1000"/>
		    <column name="LANGUAGE_CODE" value="en_US"/>
		    <column name="RESOURCE_TYPE" value="M"/>
		    <column name="RESOURCE_VALUE" value="Validation regular expression for zone"/> <!—Replace with the  resource value for the variable that is part of the script-->

	</insert>
    <insert tableName="JCP_UI_METADATA">
		    <column name="RESOURCE_KEY" value="jsdn.resourcescript.lable.zone"/> <!—Replace with the variable that is part of the script--> 

		    <column name="ORGANIZATION_ID" valueNumeric="1000"/>
		    <column name="LANGUAGE_CODE" value="en_US"/>
		    <column name="RESOURCE_TYPE" value="M"/>
		    <column name="RESOURCE_VALUE" value="Zone"/> <!—Replace with the  resource value for the variable that is part of the script-->

	</insert>
    <insert tableName="JCP_UI_METADATA">
		    <column name="RESOURCE_KEY" value="jsdn.resourcescript.validationmsg.zone"/> <!—Replace with the variable that is part of the script--> 

		    <column name="ORGANIZATION_ID" valueNumeric="1000"/>
		    <column name="LANGUAGE_CODE" value="en_US"/>
		    <column name="RESOURCE_TYPE" value="M"/>
		    <column name="RESOURCE_VALUE" value="Validation error message for zone"/> <!—Replace with the  resource value for the variable that is part of the script-->

	</insert>
</changeSet>

<!-- Inserting resource script message for existing template into DB ends -->

<!-- Change sets ends -->

Once you have the above seed data ready