{"action":"created","issue":{"url":"https://api.github.com/repos/WGBH/AAPB2/issues/229","labels_url":"https://api.github.com/repos/WGBH/AAPB2/issues/229/labels{/name}","comments_url":"https://api.github.com/repos/WGBH/AAPB2/issues/229/comments","events_url":"https://api.github.com/repos/WGBH/AAPB2/issues/229/events","html_url":"https://github.com/WGBH/AAPB2/issues/229","id":62441222,"number":229,"title":"Document Ci -> AMS import process?","user":{"login":"mccalluc","id":730388,"avatar_url":"https://avatars.githubusercontent.com/u/730388?v=3","gravatar_id":"","url":"https://api.github.com/users/mccalluc","html_url":"https://github.com/mccalluc","followers_url":"https://api.github.com/users/mccalluc/followers","following_url":"https://api.github.com/users/mccalluc/following{/other_user}","gists_url":"https://api.github.com/users/mccalluc/gists{/gist_id}","starred_url":"https://api.github.com/users/mccalluc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mccalluc/subscriptions","organizations_url":"https://api.github.com/users/mccalluc/orgs","repos_url":"https://api.github.com/users/mccalluc/repos","events_url":"https://api.github.com/users/mccalluc/events{/privacy}","received_events_url":"https://api.github.com/users/mccalluc/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-03-17T16:06:23Z","updated_at":"2015-06-09T18:36:13Z","closed_at":"2015-06-09T18:36:13Z","body":"@foo4thought : Could we document precisely how Ci IDs get inserted into the AMS? If there's a script or something, it'd be great to check it in here, or if it's all by hand, I'd like to capture that in the readme. Thanks!"},"comment":{"url":"https://api.github.com/repos/WGBH/AAPB2/issues/comments/110457855","html_url":"https://github.com/WGBH/AAPB2/issues/229#issuecomment-110457855","issue_url":"https://api.github.com/repos/WGBH/AAPB2/issues/229","id":110457855,"user":{"login":"foo4thought","id":516505,"avatar_url":"https://avatars.githubusercontent.com/u/516505?v=3","gravatar_id":"","url":"https://api.github.com/users/foo4thought","html_url":"https://github.com/foo4thought","followers_url":"https://api.github.com/users/foo4thought/followers","following_url":"https://api.github.com/users/foo4thought/following{/other_user}","gists_url":"https://api.github.com/users/foo4thought/gists{/gist_id}","starred_url":"https://api.github.com/users/foo4thought/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/foo4thought/subscriptions","organizations_url":"https://api.github.com/users/foo4thought/orgs","repos_url":"https://api.github.com/users/foo4thought/repos","events_url":"https://api.github.com/users/foo4thought/events{/privacy}","received_events_url":"https://api.github.com/users/foo4thought/received_events","type":"User","site_admin":false},"created_at":"2015-06-09T18:36:13Z","updated_at":"2015-06-09T18:36:13Z","body":"Disregard my earlier supposition that an additional column was needed, etc.  Here is the process I used:\r\ngiven a Muraszko-generated directory of unix-format text files containing tab-delimited data like this from file \"AA1671L5_Ci_log_Complete.csv\":\r\n`2015-02-19 10:31:39 -0500\tcpb-aacip-17-00000qrv.h264.mp4\tsomeLongString\t{\"id\"=>\"someReallyLongString\", \"name\"=>\"cpb-aacip-17-00000qrv.h264.mp4\", \"size\"=>185277328, \"createdBy\"=>{\"id\"=>\"someEvenLongerString\", \"name\"=>\"M Muraszko\", \"email\"=>\"m_muraszko@foo.bar\"}, \"createdOn\"=>\"2015-02-19T15:30:49.41Z\", \"thumbnailUrl\"=>\"https://foo.cloudfront.net/cifiles/someDirectoryString/early-thumbnail.jpg?Policy=absurdlyLongString\", \"proxyUrl\"=>\"\", \"format\"=>\"mp4\", \"status\"=>\"Processing\", \"folder\"=>{\"id\"=>\"someIDString\"}, \"modifiedOn\"=>\"2015-02-19T15:31:38.94Z\", \"archiveStatus\"=>\"Not archived\", \"restoreStatus\"=>\"Not restored\", \"isDeleted\"=>false, \"isTrashed\"=>false, \"uploadTransferType\"=>\"MultipartHttp\", \"thumbnails\"=>[{\"type\"=>\"early\", \"location\"=>\"https://foo.cloudfront.net/cifiles/someDirectory/early-thumbnail.jpg?Policy=reallyLongString\", \"size\"=>8723, \"width\"=>250, \"height\"=>188}], \"proxies\"=>[], \"acquisitionSource\"=>{\"name\"=>\"American Archive\"}}\r\n`\r\nI wrote and ran a BASH script to generate (and then ran) corresponding SQL like this:\r\n```# here are two variables for OUTFILE report at end of the session\r\nSET @xguids = '';\r\nSET @indexids = '';\r\n# the following structure is repeated for each row of input data\r\nSET @aaguid = 'cpb-aacip/17-00000qrv';\r\nSET @assetid = (select assets_id from identifiers where identifier=@aaguid limit 1);\r\nSET @xguids = (SELECT IF(@assetid,@xguids,CONCAT(@xguids,',',@aaguid)));\r\nSET @indexids = (SELECT IF(@assetid,CONCAT(@indexids,',',@assetid),@indexids));\r\nINSERT INTO identifiers (assets_id,identifier,identifier_source) VALUES (@assetid,'sonydatastring','Sony Ci');\r\n# \r\n# \r\n# the following requires that the mysql tmp directory exists, has correct permissions and is declared in /etc/my.cnf\r\nSELECT @xguids INTO OUTFILE '/var/lib/mysql/tmp/sonyci_failures_20150429_170455.txt';\r\nSELECT @indexids INTO OUTFILE '/var/lib/mysql/tmp/sonyci_assetids_20150429_170455.txt';\r\n```\r\nThe use of OUTFILE provides data for QA (failures) and for reindexing affected asset (assetids).\r\n\r\nThe script that created that SQL script follows:\r\n\r\n```\r\n#!/bin/bash\r\n\r\n# read tsv (named csv) files to generate SQL to insert ASSET-LEVEL IDENTIFIER data on AMS.AMERICANARCHIVE.ORG\r\n# Copyright 2015, WGBH MLA, by Kevin Carter\r\n\r\n\r\n\r\n# mysql> describe identifiers;\r\n# +-------------------+--------------+------+-----+---------+----------------+\r\n# | Field             | Type         | Null | Key | Default | Extra          |\r\n# +-------------------+--------------+------+-----+---------+----------------+\r\n# | id                | int(11)      | NO   | PRI | NULL    | auto_increment |\r\n# | assets_id         | int(11)      | NO   | PRI | NULL    |                |\r\n# | identifier        | varchar(255) | NO   | MUL | NULL    |                |\r\n# | identifier_source | varchar(255) | NO   |     | NULL    |                |\r\n# | identifier_ref    | varchar(255) | YES  |     | NULL    |                |\r\n# +-------------------+--------------+------+-----+---------+----------------+\r\n\r\nusage() {\r\necho `basename $0`' /path/to/some/specific-formatted-file.tsv | tee /path/to/ams-specific.sql';\r\necho;\r\necho 'read the script for assumptions it makes about the format of the tab-separated values and the SQL output';\r\n}\r\n\r\nif [ \"$#\" -ne 1 ];\r\nthen usage;\r\nexit 1;\r\nfi;\r\n\r\n\r\nOLDIFS=$IFS;\r\nIFS=$(echo -en '\\n\\b');\r\ntab=`printf %s a | tr a '\\t'`;\r\n\r\n\r\necho \"SET @xguids = '';\";\r\necho \"SET @indexids = '';\";\r\n\r\n# NOTE THE USE OF `grep -v '_' to avoid processing currently-missing items affected by Zend/Google library bugwork\r\n\r\n# for dataString in `grep '^20' $1  | cut -f2,3 | cut -f3- -d - | grep -v '_' | sed -e \"s#\\..*$tab#$tab#g\" -e \"s#_.*$tab#$tab#g\"`;\r\nfor dataString in `grep '^20' $1  | cut -f2,3 | cut -f3- -d - | grep '_' | sed -e \"s#\\..*$tab#$tab#g\" -e \"s#_.*$tab#$tab#g\"`;\r\ndo \r\n\t\r\n\taaguid='cpb-aacip/'$(echo \"$dataString\" | cut -f1);\r\n\tsonyid=$(echo \"$dataString\" | cut -f2);\r\n# \techo 'dataString is   '\"$dataString\";\r\n# \techo 'sonyid is   '$sonyid;\r\n\techo \"SET @aaguid = '$aaguid';\";\r\n\techo \"SET @assetid = (select assets_id from identifiers where identifier=@aaguid limit 1);\";\r\n\techo \"SET @xguids = (SELECT IF(@assetid,@xguids,CONCAT(@xguids,',',@aaguid)));\";\r\n\techo \"SET @indexids = (SELECT IF(@assetid,CONCAT(@indexids,',',@assetid),@indexids));\";\r\n#\techo \"DELETE FROM identifiers WHERE assets_id=@assetid AND identifier_source='Sony Ci';\";\r\n\techo \"INSERT INTO identifiers (assets_id,identifier,identifier_source) VALUES (@assetid,'$sonyid','Sony Ci');\";\r\n\techo;echo '#';echo;\r\n\r\ndone\r\n\r\nnowString=`date +%Y%m%d_%H%M%S`;\r\necho '# the following requires that the mysql tmp directory exists, has correct permissions and is declared in /etc/my.cnf'\r\necho \"SELECT @xguids INTO OUTFILE '/var/lib/mysql/tmp/sonyci_failures_$nowString.txt';\";\r\necho \"SELECT @indexids INTO OUTFILE '/var/lib/mysql/tmp/sonyci_assetids_$nowString.txt';\";\r\n\r\nIFS=$OLDIFS;\r\n\r\n\r\n```\r\n\r\n"}}