Scm Activity SQL Commands or CLI

Use below SQL Commands on your SCM Post hook scripts to post Change-set data

get issue id by key

$ curl --request GET --url "https://ilaesolution.atlassian.net/rest/api/3/issue/ILADEV-10?fields=id" --user "<email>:<token>" \
--header "Accept: application/json"
{"expand":"renderedFields,names,schema,operations,editmeta,changelog,versionedRepresentations",
 "id":"10036","self":"https://ilaesolution.atlassian.net/rest/api/3/issue/10036",
 "key":"ILADEV-10"}
$

See, Jira API Token Help

Add Or Post New SCM Activity

Insert change-set:

(Required)

$ psql -h scm-db-server -U postgres scmactivity
Password for user postgres:
psql (9.6.3)

scmactivity=# insert into scm_activity (issueid, issuekey, changeid, changetype, changeauthor, changedate, changebranch, \
changetag, changelink) values (10036, 'ILADEV-10', '6ed693768c1ecdc32298fba977238d20122df8e1', 'git_engsw', 'scmenthusiast@gmail.com',\
'2019-02-16 23:18:50', 'master', 'R1.0, R2.0', 'http://changelink?id=x');
INSERT 0 1
scmactivity=#
$

Query last inserted change set:

scmactivity=# select id from scm_activity where issueid = 10036 and changeid = '6ed693768c1ecdc32298fba977238d20122df8e1'\
and changetype = 'git_engsw';
 id
----
  1
(1 row)

scmactivity=#

Insert change-set message:

(Optional)

scmactivity=# insert into scm_message (message, scmactivityid) values ('added tag split and conn testing button', 1);
INSERT 0 1
scmactivity=#

Insert change-sets affected files:

(Optional)

scmactivity=# insert into scm_files (fileaction, filename, scmactivityid) \
values ('MODIFY', 'src/main/resources/application.yml', 1);
INSERT 0 1
scmactivity=#

Insert change-sets external jobs:

(Optional)

scmactivity=# insert into scm_job (jobname, joblink, jobstatus, scmactivityid) values ('Build #101', 'http://jenkins', 'success', 1)
scmactivity=#

Result:

Schema (scm_activity):

{
  "issuekey": {
    "type": "string"
  },
  "changeid": {
    "type": "string"
  },
  "changetype": { //Preferred format: ChangeType_Repo/Instance Name e.g. p4_engsw
    "type": "string"
  },
  "changeauthor": {
    "type": "string"
  },
  "changedate": { //UTC date time with format %Y-%m-%d %H:%M:%S
    "type": "string"
  },
  "changebranch": {
    "type": "string"
  },
  "changetag": {
    "type": "string"
  },
  "changestatus": {
    "type": "string"
  },
  "changelink": {
    "type": "string"
  }   
}

Schema (scm_message):

{
   "message": {
      "type": "string"
   },
   "scmactivityid": {
      "type": "integer"
   }
}

Schema (scm_files):

{
   "fileaction": {
      "type": "string"
   },
   "filename": {
      "type": "string"
   },
   "fileversion": {
      "type": "string"
   },
   "scmactivityid": {
      "type": "integer"
   }
}

Schema (scm_job):

{
   "jobname": {
      "type": "string"
   },
   "joblink": {
      "type": "string"
   },
   "jobstatus": {
      "type": "string"
   },
   "scmactivityid": {
      "type": "integer"
   }
}