{"action":"created","issue":{"url":"https://api.github.com/repos/USGCRP/gcis/issues/453","repository_url":"https://api.github.com/repos/USGCRP/gcis","labels_url":"https://api.github.com/repos/USGCRP/gcis/issues/453/labels{/name}","comments_url":"https://api.github.com/repos/USGCRP/gcis/issues/453/comments","events_url":"https://api.github.com/repos/USGCRP/gcis/issues/453/events","html_url":"https://github.com/USGCRP/gcis/issues/453","id":182363126,"number":453,"title":"Incorrect NCA3 Self-Reference","user":{"login":"lomky","id":6129479,"avatar_url":"https://avatars.githubusercontent.com/u/6129479?v=3","gravatar_id":"","url":"https://api.github.com/users/lomky","html_url":"https://github.com/lomky","followers_url":"https://api.github.com/users/lomky/followers","following_url":"https://api.github.com/users/lomky/following{/other_user}","gists_url":"https://api.github.com/users/lomky/gists{/gist_id}","starred_url":"https://api.github.com/users/lomky/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lomky/subscriptions","organizations_url":"https://api.github.com/users/lomky/orgs","repos_url":"https://api.github.com/users/lomky/repos","events_url":"https://api.github.com/users/lomky/events{/privacy}","received_events_url":"https://api.github.com/users/lomky/received_events","type":"User","site_admin":false},"labels":[{"id":459393977,"url":"https://api.github.com/repos/USGCRP/gcis/labels/context%20QA","name":"context QA","color":"e99695","default":false},{"id":389198797,"url":"https://api.github.com/repos/USGCRP/gcis/labels/priority%20medium","name":"priority medium","color":"fef2c0","default":false},{"id":362138263,"url":"https://api.github.com/repos/USGCRP/gcis/labels/type%20content","name":"type content","color":"0052cc","default":false}],"state":"open","locked":false,"assignee":{"login":"lomky","id":6129479,"avatar_url":"https://avatars.githubusercontent.com/u/6129479?v=3","gravatar_id":"","url":"https://api.github.com/users/lomky","html_url":"https://github.com/lomky","followers_url":"https://api.github.com/users/lomky/followers","following_url":"https://api.github.com/users/lomky/following{/other_user}","gists_url":"https://api.github.com/users/lomky/gists{/gist_id}","starred_url":"https://api.github.com/users/lomky/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lomky/subscriptions","organizations_url":"https://api.github.com/users/lomky/orgs","repos_url":"https://api.github.com/users/lomky/repos","events_url":"https://api.github.com/users/lomky/events{/privacy}","received_events_url":"https://api.github.com/users/lomky/received_events","type":"User","site_admin":false},"assignees":[{"login":"lomky","id":6129479,"avatar_url":"https://avatars.githubusercontent.com/u/6129479?v=3","gravatar_id":"","url":"https://api.github.com/users/lomky","html_url":"https://github.com/lomky","followers_url":"https://api.github.com/users/lomky/followers","following_url":"https://api.github.com/users/lomky/following{/other_user}","gists_url":"https://api.github.com/users/lomky/gists{/gist_id}","starred_url":"https://api.github.com/users/lomky/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lomky/subscriptions","organizations_url":"https://api.github.com/users/lomky/orgs","repos_url":"https://api.github.com/users/lomky/repos","events_url":"https://api.github.com/users/lomky/events{/privacy}","received_events_url":"https://api.github.com/users/lomky/received_events","type":"User","site_admin":false},{"login":"amruelama","id":18075324,"avatar_url":"https://avatars.githubusercontent.com/u/18075324?v=3","gravatar_id":"","url":"https://api.github.com/users/amruelama","html_url":"https://github.com/amruelama","followers_url":"https://api.github.com/users/amruelama/followers","following_url":"https://api.github.com/users/amruelama/following{/other_user}","gists_url":"https://api.github.com/users/amruelama/gists{/gist_id}","starred_url":"https://api.github.com/users/amruelama/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/amruelama/subscriptions","organizations_url":"https://api.github.com/users/amruelama/orgs","repos_url":"https://api.github.com/users/amruelama/repos","events_url":"https://api.github.com/users/amruelama/events{/privacy}","received_events_url":"https://api.github.com/users/amruelama/received_events","type":"User","site_admin":false}],"milestone":null,"comments":6,"created_at":"2016-10-11T20:16:35Z","updated_at":"2016-11-08T14:54:40Z","closed_at":null,"body":"The NCA3 appears to reference itself:\n\n[/reference/dd5b893d-4462-4bb3-9205-67b532919566](https://data-stage.globalchange.gov/reference/dd5b893d-4462-4bb3-9205-67b532919566)\n\n```\ngcis=# select * from publication_reference_map join publication on publication_reference_map.publication_id = publication.id join reference on publication_reference_map.reference_identifier = reference.identifier where reference_identifier = 'dd5b893d-4462-4bb3-9205-67b532919566' and publication_id = 10239;\n-[ RECORD 1 ]---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\npublication_reference_map.publication_id              | 10239\npublication_reference_map.reference_identifier        | dd5b893d-4462-4bb3-9205-67b532919566\npublication.id                                        | 10239\npublication.publication_type_identifier               | report\npublication.fk                                        | \"identifier\"=>\"nca3\"\nreference.identifier                                  | dd5b893d-4462-4bb3-9205-67b532919566\nreference.attrs                                       | \"DOI\"=>\"10.7930/J0Z31WJ2\", \"URL\"=>\"http://nca2014.globalchange.gov\", \"Year\"=>\"2014\", \"Title\"=>\"Climate Change Impacts in the United States: The Third National Climate Assessment\", \"_uuid\"=>\"dd5b893d-4462-4bb3-9205-67b532919566\", \"Editor\"=>\"Melillo, Jerry M.; Richmond, Terese (T.C.); Yohe, Gary W.\", \"reftype\"=>\"Edited Book\", \"_chapter\"=>\"[\\\"Ch. 0: About this Report FINAL\\\"]\", \"Publisher\"=>\"U.S. Global Change Research Program\", \"_record_number\"=>\"4692\", \".reference_type\"=>\"9\", \"Number of Pages\"=>\"841\", \"Place Published\"=>\"Washington, DC\"\nreference.child_publication_id                        | 10239\n```\n\nNotice the `publication_id` in the `publication_reference_map` is the same as the `child_publication_id` in the `reference`.\n\nI think the correct move is to simple delete the entry in the `publication_reference_map`.\n\nI think it would also be worthwhile to add a Database constraint to forbid the creation of an entry is the `publication_id` is the same as the reference's `child_publication_id`.\n"},"comment":{"url":"https://api.github.com/repos/USGCRP/gcis/issues/comments/259157550","html_url":"https://github.com/USGCRP/gcis/issues/453#issuecomment-259157550","issue_url":"https://api.github.com/repos/USGCRP/gcis/issues/453","id":259157550,"user":{"login":"amruelama","id":18075324,"avatar_url":"https://avatars.githubusercontent.com/u/18075324?v=3","gravatar_id":"","url":"https://api.github.com/users/amruelama","html_url":"https://github.com/amruelama","followers_url":"https://api.github.com/users/amruelama/followers","following_url":"https://api.github.com/users/amruelama/following{/other_user}","gists_url":"https://api.github.com/users/amruelama/gists{/gist_id}","starred_url":"https://api.github.com/users/amruelama/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/amruelama/subscriptions","organizations_url":"https://api.github.com/users/amruelama/orgs","repos_url":"https://api.github.com/users/amruelama/repos","events_url":"https://api.github.com/users/amruelama/events{/privacy}","received_events_url":"https://api.github.com/users/amruelama/received_events","type":"User","site_admin":false},"created_at":"2016-11-08T14:54:40Z","updated_at":"2016-11-08T14:54:40Z","body":"I see. Can we delete their relationship on stage or is it easier for you to do it through script?\r\n@lomky.\r\n I will add a 'convention' label to this ticket to make sure we don't repeat this in future. "}}
{"actor":{"display_login":"amruelama"}}