{"action":"created","issue":{"url":"https://api.github.com/repos/PecanProject/bety/issues/181","repository_url":"https://api.github.com/repos/PecanProject/bety","labels_url":"https://api.github.com/repos/PecanProject/bety/issues/181/labels{/name}","comments_url":"https://api.github.com/repos/PecanProject/bety/issues/181/comments","events_url":"https://api.github.com/repos/PecanProject/bety/issues/181/events","html_url":"https://github.com/PecanProject/bety/issues/181","id":55326503,"number":181,"title":"citations: add uniqueness constraint","user":{"login":"gsrohde","id":1706896,"avatar_url":"https://avatars.githubusercontent.com/u/1706896?v=3","gravatar_id":"","url":"https://api.github.com/users/gsrohde","html_url":"https://github.com/gsrohde","followers_url":"https://api.github.com/users/gsrohde/followers","following_url":"https://api.github.com/users/gsrohde/following{/other_user}","gists_url":"https://api.github.com/users/gsrohde/gists{/gist_id}","starred_url":"https://api.github.com/users/gsrohde/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gsrohde/subscriptions","organizations_url":"https://api.github.com/users/gsrohde/orgs","repos_url":"https://api.github.com/users/gsrohde/repos","events_url":"https://api.github.com/users/gsrohde/events{/privacy}","received_events_url":"https://api.github.com/users/gsrohde/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/PecanProject/bety/labels/2%20-%20Working","name":"2 - Working","color":"CCCCCC"},{"url":"https://api.github.com/repos/PecanProject/bety/labels/constraints","name":"constraints","color":"e99695"},{"url":"https://api.github.com/repos/PecanProject/bety/labels/help%20wanted","name":"help wanted","color":"e99695"}],"state":"open","locked":false,"assignee":{"login":"gsrohde","id":1706896,"avatar_url":"https://avatars.githubusercontent.com/u/1706896?v=3","gravatar_id":"","url":"https://api.github.com/users/gsrohde","html_url":"https://github.com/gsrohde","followers_url":"https://api.github.com/users/gsrohde/followers","following_url":"https://api.github.com/users/gsrohde/following{/other_user}","gists_url":"https://api.github.com/users/gsrohde/gists{/gist_id}","starred_url":"https://api.github.com/users/gsrohde/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gsrohde/subscriptions","organizations_url":"https://api.github.com/users/gsrohde/orgs","repos_url":"https://api.github.com/users/gsrohde/repos","events_url":"https://api.github.com/users/gsrohde/events{/privacy}","received_events_url":"https://api.github.com/users/gsrohde/received_events","type":"User","site_admin":false},"assignees":[{"login":"gsrohde","id":1706896,"avatar_url":"https://avatars.githubusercontent.com/u/1706896?v=3","gravatar_id":"","url":"https://api.github.com/users/gsrohde","html_url":"https://github.com/gsrohde","followers_url":"https://api.github.com/users/gsrohde/followers","following_url":"https://api.github.com/users/gsrohde/following{/other_user}","gists_url":"https://api.github.com/users/gsrohde/gists{/gist_id}","starred_url":"https://api.github.com/users/gsrohde/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gsrohde/subscriptions","organizations_url":"https://api.github.com/users/gsrohde/orgs","repos_url":"https://api.github.com/users/gsrohde/repos","events_url":"https://api.github.com/users/gsrohde/events{/privacy}","received_events_url":"https://api.github.com/users/gsrohde/received_events","type":"User","site_admin":false},{"login":"serbinsh","id":3067369,"avatar_url":"https://avatars.githubusercontent.com/u/3067369?v=3","gravatar_id":"","url":"https://api.github.com/users/serbinsh","html_url":"https://github.com/serbinsh","followers_url":"https://api.github.com/users/serbinsh/followers","following_url":"https://api.github.com/users/serbinsh/following{/other_user}","gists_url":"https://api.github.com/users/serbinsh/gists{/gist_id}","starred_url":"https://api.github.com/users/serbinsh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/serbinsh/subscriptions","organizations_url":"https://api.github.com/users/serbinsh/orgs","repos_url":"https://api.github.com/users/serbinsh/repos","events_url":"https://api.github.com/users/serbinsh/events{/privacy}","received_events_url":"https://api.github.com/users/serbinsh/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/PecanProject/bety/milestones/8","html_url":"https://github.com/PecanProject/bety/milestones/Implement%20Database%20Constraints","labels_url":"https://api.github.com/repos/PecanProject/bety/milestones/8/labels","id":886188,"number":8,"title":"Implement Database Constraints","description":"Once we have ensure that our data is consistent with the constraints we wish to add we can go ahead and add them.","creator":{"login":"gsrohde","id":1706896,"avatar_url":"https://avatars.githubusercontent.com/u/1706896?v=3","gravatar_id":"","url":"https://api.github.com/users/gsrohde","html_url":"https://github.com/gsrohde","followers_url":"https://api.github.com/users/gsrohde/followers","following_url":"https://api.github.com/users/gsrohde/following{/other_user}","gists_url":"https://api.github.com/users/gsrohde/gists{/gist_id}","starred_url":"https://api.github.com/users/gsrohde/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gsrohde/subscriptions","organizations_url":"https://api.github.com/users/gsrohde/orgs","repos_url":"https://api.github.com/users/gsrohde/repos","events_url":"https://api.github.com/users/gsrohde/events{/privacy}","received_events_url":"https://api.github.com/users/gsrohde/received_events","type":"User","site_admin":false},"open_issues":5,"closed_issues":20,"state":"open","created_at":"2014-12-01T17:12:33Z","updated_at":"2015-12-07T00:04:18Z","due_on":null,"closed_at":null},"comments":9,"created_at":"2015-01-23T20:08:45Z","updated_at":"2016-09-28T19:37:40Z","closed_at":null,"body":"* [x] _Optional but recommended--see below._ Normalize white space in title and author columns\r\n* [x] Consolidate duplicate citation rows\r\n* [ ] Add citation uniqueness constraint\r\n\r\n~~Add not-NULL constraints - READY TO BE DONE~~ ~~Deferred to issue #230~~ DONE\r\n * [x] fix records with year = NULL\r\n\r\nTabled for now ~~_Optional--see below._ Add a trigger function to ensure title and author columns are whitespace-normalized upon insertion or update~~\r\n\r\n\r\nThe minimal constraint we wish to add is\r\n\r\n```sql\r\nALTER TABLE citations ADD CONSTRAINT unique_author_year_title UNIQUE (author, year, title);\r\n```\r\n\r\nTo see violators, run\r\n\r\n```sql\r\nSELECT ARRAY_AGG(id), author, year, title FROM citations GROUP BY author, year, title HAVING count(*) > 1;\r\n```\r\n\r\nThis currently produces 8 pairs of duplicates on ebi_production.\r\n\r\n\r\n**_THE WHITESPACE NORMALIZATION OF AUTHORS AND TITLES MENTIONED BELOW HAS BEEN DONE.  WE WILL NOT ATTEMPT TO NORMALIZE CAPITALIZATION._**\r\n\r\n\r\nIdeally, however, we would like to go beyond this minimal constraint and prevent the occurrence of two rows where the authors and/or titles don't exactly match but differ only minimally--for example, they differ only in whitespace or capitalization.  To see the problem, run this variant of the above query:\r\n\r\n```sql\r\nSELECT\r\n    ARRAY_AGG(id),\r\n    REGEXP_REPLACE(TRIM(LOWER(author)), ' +', ' '),\r\n    year,\r\n    REGEXP_REPLACE(TRIM(LOWER(title)), ' +', ' ')\r\nFROM\r\n    citations\r\nGROUP BY\r\n    REGEXP_REPLACE(TRIM(LOWER(author)), ' +', ' '),\r\n    year,\r\n    REGEXP_REPLACE(TRIM(LOWER(title)), ' +', ' ')\r\nHAVING\r\n    COUNT (*) > 1;\r\n```\r\n\r\nThis produces 11 pairs of duplicates--three more than when we require exact matching.\r\n\r\nWe can go even further in this direction by using the PostgreSQL trigram extension:\r\n\r\n```SQL\r\nCREATE EXTENSION pg_trgm; -- provides the 'similar' function\r\n\r\nSELECT\r\n    c1.id,\r\n    c1.author,\r\n    c1.year,\r\n    c1.title,\r\n    c2.id,\r\n    c2.author,\r\n    c2.year,\r\n    c2.title\r\nFROM\r\n    citations c1\r\nJOIN citations c2 ON (\r\n    c1.id < c2.id\r\n    AND SIMILARITY(c1.author, c2.author) > 0.9\r\n    AND c1.year = c2.year\r\n    AND SIMILARITY(c1.title, c2.title) > 0.5\r\n);\r\n```\r\n\r\nThis finds a few more potential duplicates (compare citations with ids 12 and 13 and with ids 675 and 676 on ebi_forecast).\r\n\r\n\r\n# Options for preventing near matches.\r\n\r\nHere are two possible sets of constraints to prevent near matches:\r\n\r\n```sql\r\nALTER TABLE citations ADD CONSTRAINT normalized_titles CHECK (title = REGEXP_REPLACE(TRIM(title), ' +', ' '));\r\nALTER TABLE citations ADD CONSTRAINT normalized_authors CHECK (author = REGEXP_REPLACE(TRIM(author), ' +', ' '));\r\nCREATE UNIQUE INDEX citations_natural_key ON citations (LOWER(author), year, LOWER(title));\r\n```\r\n\r\nAlternatively:\r\n\r\n```sql\r\nCREATE UNIQUE INDEX citations_natural_key ON citations (REGEXP_REPLACE(TRIM(LOWER(author)), ' +', ' '), year, REGEXP_REPLACE(TRIM(LOWER(title)), ' +', ' '));\r\n```\r\n\r\nThe first set has the advantage of ensuring the title and author fields are always whitespace-normalized.  This may be a useful thing for client programs to be able to rely upon for display, searching, matching, etc.\r\n\r\nNote that we have to switch to using unique indices where we want our uniqueness constraint to be anything more complicated than a simple column list.\r\n\r\nThe second set has the advantage of requiring minimal cleanup.  But this is a small advantage, since the normalization is easily done:\r\n\r\n```sql\r\nUPDATE citations SET title = REGEXP_REPLACE(TRIM(title), ' +', ' ');\r\nUPDATE citations SET author = REGEXP_REPLACE(TRIM(author), ' +', ' ');\r\n```\r\n\r\nIn either case, a trigger function could also be added to ensure that the author and title columns are automatically normalized when an insertion or update occurs.\r\n\r\nFinally, in order that (author, year, title) truly constitute a natural key, we should impose non-null constraints:\r\n\r\n```sql\r\nALTER TABLE citations ALTER COLUMN author SET NOT NULL;\r\nALTER TABLE citations ALTER COLUMN year SET NOT NULL;\r\nALTER TABLE citations ALTER COLUMN title SET NOT NULL;\r\n```\r\n\r\nCurrently, in ebi_production, only `year` is ever null.  If this is meant to mean \"PUBLICATION DATE UNKNOWN\" or \"UNPUBLISHED\", special integer values should be designated to represent these meanings.  Alternatively, year should be changed to some user-defined type that comprises both numbers, representing year of publication, and special non-numerical values used for other meanings.\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/PecanProject/bety/issues/comments/250276547","html_url":"https://github.com/PecanProject/bety/issues/181#issuecomment-250276547","issue_url":"https://api.github.com/repos/PecanProject/bety/issues/181","id":250276547,"user":{"login":"dlebauer","id":464871,"avatar_url":"https://avatars.githubusercontent.com/u/464871?v=3","gravatar_id":"","url":"https://api.github.com/users/dlebauer","html_url":"https://github.com/dlebauer","followers_url":"https://api.github.com/users/dlebauer/followers","following_url":"https://api.github.com/users/dlebauer/following{/other_user}","gists_url":"https://api.github.com/users/dlebauer/gists{/gist_id}","starred_url":"https://api.github.com/users/dlebauer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dlebauer/subscriptions","organizations_url":"https://api.github.com/users/dlebauer/orgs","repos_url":"https://api.github.com/users/dlebauer/repos","events_url":"https://api.github.com/users/dlebauer/events{/privacy}","received_events_url":"https://api.github.com/users/dlebauer/received_events","type":"User","site_admin":false},"created_at":"2016-09-28T19:37:40Z","updated_at":"2016-09-28T19:37:40Z","body":"@serbinsh did you update all of the tables with a `citation_id` field?"}}
{"actor":{"display_login":"dlebauer"}}