HLS-36 - Link version to historic JIRA issues =============================================== The issue HLS-36 will introduce version numbers into `HLS-Stream-Creator`, so the aim of this doc is to discover and record the process of linking the newly created version record to all the old JIRA issues. Get the schema of the issue table mysql> describe jiraissue; +----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | NULL | | | pkey | varchar(255) | YES | | NULL | | | issuenum | decimal(18,0) | YES | MUL | NULL | | | PROJECT | decimal(18,0) | YES | MUL | NULL | | | REPORTER | varchar(255) | YES | | NULL | | | ASSIGNEE | varchar(255) | YES | MUL | NULL | | | issuetype | varchar(255) | YES | | NULL | | | SUMMARY | varchar(255) | YES | | NULL | | | DESCRIPTION | longtext | YES | | NULL | | | ENVIRONMENT | longtext | YES | | NULL | | | PRIORITY | varchar(255) | YES | | NULL | | | RESOLUTION | varchar(255) | YES | | NULL | | | issuestatus | varchar(255) | YES | | NULL | | | CREATED | datetime | YES | | NULL | | | UPDATED | datetime | YES | MUL | NULL | | | DUEDATE | datetime | YES | | NULL | | | RESOLUTIONDATE | datetime | YES | | NULL | | | VOTES | decimal(18,0) | YES | | NULL | | | WATCHES | decimal(18,0) | YES | | NULL | | | TIMEORIGINALESTIMATE | decimal(18,0) | YES | | NULL | | | TIMEESTIMATE | decimal(18,0) | YES | | NULL | | | TIMESPENT | decimal(18,0) | YES | | NULL | | | WORKFLOW_ID | decimal(18,0) | YES | MUL | NULL | | | SECURITY | decimal(18,0) | YES | | NULL | | | FIXFOR | decimal(18,0) | YES | | NULL | | | COMPONENT | decimal(18,0) | YES | | NULL | | +----------------------+---------------+------+-----+---------+-------+ 26 rows in set (0.03 sec) Get the project ID mysql> select id,pname,pkey from project where pkey='HLS'; +-------+--------------------+------+ | id | pname | pkey | +-------+--------------------+------+ | 11407 | HLS Stream Creator | HLS | +-------+--------------------+------+ List out the issues in the project mysql> select ID,pkey,issuenum,summary from jiraissue where project=11407 order by issuenum ASC; +-------+------+----------+-----------------------------------------------------------------------------+ | ID | pkey | issuenum | summary | +-------+------+----------+-----------------------------------------------------------------------------+ | 13547 | NULL | 1 | PR#2 Changed let by expr | | 13548 | NULL | 2 | PR#3 added support for a third parameter | | 13549 | NULL | 3 | PR#4 Allowed inputfile to be a pth | | 13550 | NULL | 4 | PR#5 Test executable defined in var $FFMPEG | | 13551 | NULL | 5 | PR#6 FFMPEG will be called only once | | 13552 | NULL | 6 | Adaptive Streams | | 13553 | NULL | 7 | Implement use of getopt | | 13554 | NULL | 8 | Implement ability to tell FFMPEG the input is a linear stream | | 13558 | NULL | 9 | Allow filename prefix to be specified | | 13559 | NULL | 10 | When parrallelised transcodes finish, the bitrate for the last isn't output | | 13560 | NULL | 11 | Document Environment Variables | | 13561 | NULL | 12 | Auto-delete expired segments | | 13563 | NULL | 13 | Adaptive Bitrates from Named Pipe | | 13564 | NULL | 14 | General Testing Notes | | 13565 | NULL | 15 | Append #EXT-X-ENDLIST when a live stream concludes | | 13566 | NULL | 16 | Segments into a subdirectory | | 13595 | NULL | 17 | PR#7 Added AES-128 encryption to the generated files | | 14240 | NULL | 18 | FFMPEG_FLAGS not being honoured | | 14254 | NULL | 19 | Re-Asses Suitability of FFMPEGs HLS functionality | | 15602 | NULL | 20 | Reported issues with Encryption | | 15603 | NULL | 21 | Review Encrypt function | | 15604 | NULL | 22 | IV will be wrong for some bitrates in ABR streams | | 15605 | NULL | 23 | On recent Ubuntu install AAC codec is marked as experimental | | 15726 | NULL | 24 | Encode fails where audio stream present but null | | 15727 | NULL | 25 | Additional flags/features to aid Low Latency Streaming | | 15728 | NULL | 26 | Ability to override WRAP_POINT | | 15729 | NULL | 27 | Ability to define resolutions with bitrates | | 15730 | NULL | 28 | Ability to create Event type playlists | | 15816 | NULL | 29 | Return relevant exit statuses | | 15820 | NULL | 30 | Specification of key and playlist URL prefixes | | 16010 | NULL | 31 | Auto Calculation of bitrates | | 17114 | NULL | 32 | PR#29 Add FFMPEG input options as environment variable | | 17206 | NULL | 33 | Brew no longer supports --with-default-names | | 17800 | NULL | 34 | Audio Bitrate Scaling | | 18108 | NULL | 35 | Playback of encrypted streams fails on Macs (inc VLC) | | 18112 | NULL | 36 | Create a release | +-------+------+----------+-----------------------------------------------------------------------------+ Issues are linked out via the `nodeassociation` table, so get the schema of that mysql> describe nodeassociation; +--------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------+------+-----+---------+-------+ | SOURCE_NODE_ID | decimal(18,0) | NO | PRI | NULL | | | SOURCE_NODE_ENTITY | varchar(60) | NO | PRI | NULL | | | SINK_NODE_ID | decimal(18,0) | NO | PRI | NULL | | | SINK_NODE_ENTITY | varchar(60) | NO | PRI | NULL | | | ASSOCIATION_TYPE | varchar(60) | NO | PRI | NULL | | | SEQUENCE | decimal(9,0) | YES | | NULL | | +--------------------+---------------+------+-----+---------+-------+ Currently, HLS-36 is linked to the version record, so see what else (if anything) it links to via that table mysql> select * from nodeassociation where SOURCE_NODE_ID=18112; +----------------+--------------------+--------------+------------------+------------------+----------+ | SOURCE_NODE_ID | SOURCE_NODE_ENTITY | SINK_NODE_ID | SINK_NODE_ENTITY | ASSOCIATION_TYPE | SEQUENCE | +----------------+--------------------+--------------+------------------+------------------+----------+ | 18112 | Issue | 13900 | Version | IssueFixVersion | NULL | +----------------+--------------------+--------------+------------------+------------------+----------+ OK, so inserting the Fix Version _shouldn't_ be too hard mysql> insert into nodeassociation (SOURCE_NODE_ID,SOURCE_NODE_ENTITY,SINK_NODE_ID,SINK_NODE_ENTITY,ASSOCIATION_TYPE) -> select ID, "Issue" as `SOURCE_NODE_ID`, 13900 as `SINK_NODE_ID`, "Version" AS `SINK_NODE_ENTITY`, "IssueFixVersion" AS `ASSOCIATION_TYPE` from jiraissue where project=11407 and ID NOT IN (18112); Query OK, 35 rows affected (0.08 sec) Records: 35 Duplicates: 0 Warnings: 0 We also want to do the same to set "IssueVersion" (applies to the "Affects") field, but only for Bugs. So, we need to find the ID for the bugtype: mysql> describe issuetype; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ID | varchar(60) | NO | PRI | NULL | | | SEQUENCE | decimal(18,0) | YES | | NULL | | | pname | varchar(60) | YES | | NULL | | | pstyle | varchar(60) | YES | | NULL | | | DESCRIPTION | text | YES | | NULL | | | ICONURL | varchar(255) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ mysql> select pname, ID from issuetype; +----------------+----+ | pname | ID | +----------------+----+ | Bug | 1 | | Correspondence | 10 | | Conversation | 11 | | Pull Request | 12 | | Epic | 13 | | New Feature | 2 | | Task | 3 | | Improvement | 4 | | Sub-task | 5 | | Story | 6 | | Notification | 7 | | Informational | 8 | | Alert | 9 | +----------------+----+ Then pull out a list of issues that appear to be bugs mysql> select ID,issuenum,summary from jiraissue where project=11407 and issuetype=1; +-------+----------+-----------------------------------------------------------------------------+ | ID | issuenum | summary | +-------+----------+-----------------------------------------------------------------------------+ | 15605 | 23 | On recent Ubuntu install AAC codec is marked as experimental | | 13559 | 10 | When parrallelised transcodes finish, the bitrate for the last isn't output | | 14240 | 18 | FFMPEG_FLAGS not being honoured | | 15602 | 20 | Reported issues with Encryption | | 15726 | 24 | Encode fails where audio stream present but null | | 17206 | 33 | Brew no longer supports --with-default-names | | 18108 | 35 | Playback of encrypted streams fails on Macs (inc VLC) | +-------+----------+-----------------------------------------------------------------------------+ That looks right, so now for another select/insert mysql> insert into nodeassociation (SOURCE_NODE_ID,SOURCE_NODE_ENTITY,SINK_NODE_ID,SINK_NODE_ENTITY,ASSOCIATION_TYPE) -> select ID, "Issue" as `SOURCE_NODE_ID`, 13900 as `SINK_NODE_ID`, "Version" AS `SINK_NODE_ENTITY`, "IssueVersion" AS `ASSOCIATION_TYPE` from jiraissue where project=11407 and ID NOT IN (18112) and issuetype=1; Query OK, 7 rows affected (0.13 sec) The issues show the correct fix versions/affects versions when viewing them now, but something's still not quite right - the issues aren't listed when filtering by version type. On the other hand my jiralist pages _do_ show it - is there something additional JIRA is looking up or do we just need a re-index? Have triggered a re-index. Yep, that's done it, all show up as correctly linked. Ok, the JIRA side work is basically done here then