{"action":"closed","issue":{"url":"https://api.github.com/repos/IQSS/dataverse/issues/3360","repository_url":"https://api.github.com/repos/IQSS/dataverse","labels_url":"https://api.github.com/repos/IQSS/dataverse/issues/3360/labels{/name}","comments_url":"https://api.github.com/repos/IQSS/dataverse/issues/3360/comments","events_url":"https://api.github.com/repos/IQSS/dataverse/issues/3360/events","html_url":"https://github.com/IQSS/dataverse/issues/3360","id":177197917,"number":3360,"title":"Fix content types of unknown files with extensions '.dcm', '.NSDstat', '.xz', '.docx', '.doc', 'pdf'","user":{"login":"raprasad","id":1036060,"avatar_url":"https://avatars.githubusercontent.com/u/1036060?v=3","gravatar_id":"","url":"https://api.github.com/users/raprasad","html_url":"https://github.com/raprasad","followers_url":"https://api.github.com/users/raprasad/followers","following_url":"https://api.github.com/users/raprasad/following{/other_user}","gists_url":"https://api.github.com/users/raprasad/gists{/gist_id}","starred_url":"https://api.github.com/users/raprasad/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/raprasad/subscriptions","organizations_url":"https://api.github.com/users/raprasad/orgs","repos_url":"https://api.github.com/users/raprasad/repos","events_url":"https://api.github.com/users/raprasad/events{/privacy}","received_events_url":"https://api.github.com/users/raprasad/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/IQSS/dataverse/labels/Component:%20Metadata","name":"Component: Metadata","color":"c7def8"},{"url":"https://api.github.com/repos/IQSS/dataverse/labels/Priority%202:%20Moderate","name":"Priority 2: Moderate","color":"e99695"},{"url":"https://api.github.com/repos/IQSS/dataverse/labels/Status:%20Dev","name":"Status: Dev","color":"66cc66"},{"url":"https://api.github.com/repos/IQSS/dataverse/labels/Type:%20Bug","name":"Type: Bug","color":"fef2c0"}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/IQSS/dataverse/milestones/57","html_url":"https://github.com/IQSS/dataverse/milestones/4.5.1%20-%20IP%20Groups%20fixes","labels_url":"https://api.github.com/repos/IQSS/dataverse/milestones/57/labels","id":1960683,"number":57,"title":"4.5.1 - IP Groups fixes","description":"","creator":{"login":"djbrooke","id":7773392,"avatar_url":"https://avatars.githubusercontent.com/u/7773392?v=3","gravatar_id":"","url":"https://api.github.com/users/djbrooke","html_url":"https://github.com/djbrooke","followers_url":"https://api.github.com/users/djbrooke/followers","following_url":"https://api.github.com/users/djbrooke/following{/other_user}","gists_url":"https://api.github.com/users/djbrooke/gists{/gist_id}","starred_url":"https://api.github.com/users/djbrooke/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/djbrooke/subscriptions","organizations_url":"https://api.github.com/users/djbrooke/orgs","repos_url":"https://api.github.com/users/djbrooke/repos","events_url":"https://api.github.com/users/djbrooke/events{/privacy}","received_events_url":"https://api.github.com/users/djbrooke/received_events","type":"User","site_admin":false},"open_issues":8,"closed_issues":18,"state":"open","created_at":"2016-08-24T14:44:22Z","updated_at":"2016-09-20T15:07:42Z","due_on":null,"closed_at":null},"comments":0,"created_at":"2016-09-15T14:48:16Z","updated_at":"2016-09-20T15:07:42Z","closed_at":"2016-09-20T15:07:42Z","body":"### Problem\r\n31,415 files  mistakenly have a ```contenttype``` of ```application/octet-stream```--or unknown. \r\n\r\n### Overall fix:\r\n\r\nThis will fix 28% of the files marked as unkown (```application/octet-stream```)\r\n\r\n - [x] Change the contenttype via sql queries (instructions below)\r\n - [ ] Run a re-indexing to fix file cards\r\n - Note: Re-ingesting, if applicable, will be done file type by file type\r\n\r\nThe file extensions are as follows (9/15/2016):\r\n  - **.dcm**: 5,156 files\r\n  - **.NSDstat**: 7,429 files\r\n  - **.xz**: 12,423 files\r\n  - **.docx**: 3,646 files\r\n  - **.doc**: 1,534 files\r\n  - **.pdf**: 1,227 files\r\n \r\n--- \r\n\r\n# Fix Checklist\r\n\r\nScroll down for queries to fix each item\r\n\r\n- [ ] 1. Fix ```DICOM (Digital Imaging and Communications in Medicine)``` files\r\n    - Change extension ```.dcm``` to contenttype ```image/dicom-rle```\r\n- [ ] 2. Fix ```Nesstar files (Norwegian Center for Data Research)``` files\r\n    - Change extension ```.NSDstat``` to contenttype ```application/x-nsdstat```\r\n- [ ] 3. Fix ```.xz format for compressed streams``` files\r\n    - Change extension ```.xz``` to contenttype ```application/x-xz```\r\n- [ ] 4. Fix ```MS Word (.docx)``` files\r\n    - Change extension ```.docx``` to contenttype ```application/vnd.openxmlformats-officedocument.wordprocessingml.document```\r\n- [ ] 5. Fix ```MS Word (.doc)``` files\r\n    - Change extension ```.doc``` to contenttype ```application/msword```\r\n- [ ] 6. Fix ```PDF files(.pdf)``` files\r\n    - Change extension ```.pdf``` to contenttype ```application/pdf```\r\n\r\n--- \r\n\r\n# (1) Fix ```DICOM (Digital Imaging and Communications in Medicine)``` files\r\n  - Change extension ```.dcm``` to contenttype ```image/dicom-rle```:\r\n  - reference: http://www.iana.org/assignments/media-types/image/dicom-rle\r\n\r\n## A - Run subquery count\r\n\r\n```sql\r\nSELECT count(distinct(df.id))\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.dcm'\r\nAND df.contenttype = 'application/octet-stream';\r\n```\r\n\r\n## B - Run update query\r\n\r\n```sql\r\nBEGIN;\r\nupdate datafile set contenttype='image/dicom-rle' where id in (SELECT distinct(df.id)\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.dcm'\r\nAND df.contenttype = 'application/octet-stream');\r\n\r\n/**\r\nCheck if it works!  e.g. File count matches!\r\n*/\r\nCOMMIT;\r\n```\r\n\r\n## Sanity\r\n\r\n  - Counts in **A** and **B** should match.\r\n\r\n        \r\n\r\n# (2) Fix ```Nesstar files (Norwegian Center for Data Research)``` files\r\n  - Change extension ```.NSDstat``` to contenttype ```application/x-nsdstat```:\r\n  - reference: http://www.nesstar.com/support/faq.html#pub.cubes_old_nsdstat\r\n\r\n## A - Run subquery count\r\n\r\n```sql\r\nSELECT count(distinct(df.id))\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.NSDstat'\r\nAND df.contenttype = 'application/octet-stream';\r\n```\r\n\r\n## B - Run update query\r\n\r\n```sql\r\nBEGIN;\r\nupdate datafile set contenttype='application/x-nsdstat' where id in (SELECT distinct(df.id)\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.NSDstat'\r\nAND df.contenttype = 'application/octet-stream');\r\n\r\n/**\r\nCheck if it works!  e.g. File count matches!\r\n*/\r\nCOMMIT;\r\n```\r\n\r\n## Sanity\r\n\r\n  - Counts in **A** and **B** should match.\r\n\r\n        \r\n\r\n# (3) Fix ```.xz format for compressed streams``` files\r\n  - Change extension ```.xz``` to contenttype ```application/x-xz```:\r\n  - reference: http://tukaani.org/xz/xz-file-format.txt\r\n\r\n## A - Run subquery count\r\n\r\n```sql\r\nSELECT count(distinct(df.id))\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.xz'\r\nAND df.contenttype = 'application/octet-stream';\r\n```\r\n\r\n## B - Run update query\r\n\r\n```sql\r\nBEGIN;\r\nupdate datafile set contenttype='application/x-xz' where id in (SELECT distinct(df.id)\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.xz'\r\nAND df.contenttype = 'application/octet-stream');\r\n\r\n/**\r\nCheck if it works!  e.g. File count matches!\r\n*/\r\nCOMMIT;\r\n```\r\n\r\n## Sanity\r\n\r\n  - Counts in **A** and **B** should match.\r\n\r\n        \r\n\r\n# (4) Fix ```MS Word (.docx)``` files\r\n  - Change extension ```.docx``` to contenttype ```application/vnd.openxmlformats-officedocument.wordprocessingml.document```:\r\n  - reference: http://stackoverflow.com/questions/4212861/what-is-a-correct-mime-type-for-docx-pptx-etc\r\n\r\n## A - Run subquery count\r\n\r\n```sql\r\nSELECT count(distinct(df.id))\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.docx'\r\nAND df.contenttype = 'application/octet-stream';\r\n```\r\n\r\n## B - Run update query\r\n\r\n```sql\r\nBEGIN;\r\nupdate datafile set contenttype='application/vnd.openxmlformats-officedocument.wordprocessingml.document' where id in (SELECT distinct(df.id)\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.docx'\r\nAND df.contenttype = 'application/octet-stream');\r\n\r\n/**\r\nCheck if it works!  e.g. File count matches!\r\n*/\r\nCOMMIT;\r\n```\r\n\r\n## Sanity\r\n\r\n  - Counts in **A** and **B** should match.\r\n\r\n        \r\n\r\n# (5) Fix ```MS Word (.doc)``` files\r\n  - Change extension ```.doc``` to contenttype ```application/msword```:\r\n  - reference: http://stackoverflow.com/questions/4212861/what-is-a-correct-mime-type-for-docx-pptx-etc\r\n\r\n## A - Run subquery count\r\n\r\n```sql\r\nSELECT count(distinct(df.id))\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.doc'\r\nAND df.contenttype = 'application/octet-stream';\r\n```\r\n\r\n## B - Run update query\r\n\r\n```sql\r\nBEGIN;\r\nupdate datafile set contenttype='application/msword' where id in (SELECT distinct(df.id)\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.doc'\r\nAND df.contenttype = 'application/octet-stream');\r\n\r\n/**\r\nCheck if it works!  e.g. File count matches!\r\n*/\r\nCOMMIT;\r\n```\r\n\r\n## Sanity\r\n\r\n  - Counts in **A** and **B** should match.\r\n\r\n        \r\n\r\n\r\n# (6) Fix ```PDF files(.pdf)``` files\r\n  - Change extension ```.pdf``` to contenttype ```application/pdf```:\r\n  - reference: http://stackoverflow.com/questions/312230/proper-mime-media-type-for-pdf-files\r\n\r\n## A - Run subquery count\r\n\r\n```sql\r\nSELECT count(distinct(df.id))\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.pdf'\r\nAND df.contenttype = 'application/octet-stream';\r\n```\r\n\r\n## B - Run update query\r\n\r\n```sql\r\nBEGIN;\r\nupdate datafile set contenttype='application/pdf' where id in (SELECT distinct(df.id)\r\nFROM datafile df,  filemetadata fm\r\nWHERE fm.datafile_id = df.id\r\nAND fm.label LIKE '%.pdf'\r\nAND df.contenttype = 'application/octet-stream');\r\n\r\n/**\r\nCheck if it works!  e.g. File count matches!\r\n*/\r\nCOMMIT;\r\n```\r\n\r\n## Sanity\r\n\r\n  - Counts in **A** and **B** should match."}}
{"actor":{"display_login":"djbrooke"}}