-
Notifications
You must be signed in to change notification settings - Fork 0
/
prot2rs
executable file
·281 lines (260 loc) · 15.3 KB
/
prot2rs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
#!/usr/bin/env bash
SCRIPTDIR="$(dirname "$0")"
CONF_FILE="${SCRIPTDIR}/pbpro.conf"
# pbcore 2 resourcespace mapping
# fields below prefixed with 'PBCORE_' will be mapped from PBCore to ResourceSpace
# it is recommended to set up a display template for these fields in resourcespace like this:
# <div><h3>[title]</h3><p>[value]</p></div><div class="clearerleft"> </div>
PBCORE_ASSETTYPE_FIELD=85
PBCORE_TITLE_SERIES_FIELD=86
PBCORE_ASSETDATE_FIELD=12
PBCORE_IDENTIFIER_FIELD=98
PBCORE_TITLE_FIELD=92
PBCORE_SUBTITLE_FIELD=94
PBCORE_DESCRIPTION_FIELD=88
PBCORE_INSTANTIATION_FIELD=97
PBCORE_COVERAGE_FIELD=103
PBCORE_CREATOR_FIELD=104
PBCORE_CONTRIBUTOR_FIELD=105
PBCORE_PUBLISHER_FIELD=106
LAST_UPDATED_FIELD=99
_usage(){
cat <<EOF
$(basename "${0}")
Query ProTrack with pbprotracktor to gather PBCore data, transform it, and push
it to ResourceSpace.
Usage: $(basename "${0}") [ -d /path/to/deliver/to/ ] {fileorpackage}
-F DATE or Negative Integer (update ResourceSpace's records updated after
the DATE provided (in YYYY-MM-DD form) or provide a negative number to
update records updated within the past number of specified days)
-f DATE or Negative Integer (update ProTrack's records updated after the
DATE provided (in YYYY-MM-DD form) or provide a negative number to update
records updated within the past number of specified days)
-t sets up a featured collection based on today's date
-h display this help
EOF
exit
}
urlencode() {
# urlencode <string>
old_lc_collate=$LC_COLLATE
LC_COLLATE=C
TEXT=$(echo "$1" | sed 's|"|\\\\\\"|g')
local length="${#1}"
for (( i = 0; i < length; i++ )); do
local c="${TEXT:i:1}"
case $c in
[a-zA-Z0-9.~_-]) printf "$c" ;;
*) printf '%%%02X' "'$c" ;;
esac
done
LC_COLLATE=$old_lc_collate
}
_maketemp(){
mktemp -q "/tmp/$(basename "${0}").XXXXXX"
if [ "${?}" -ne 0 ]; then
echo "${0}: Can't create temp file, exiting..."
_writeerrorlog "_maketemp" "was unable to create the temp file, so the script had to exit."
exit 1
fi
}
_remove_empty_xml_nodes(){
xml ed -d '//*[not(./*) and (not(./text()) or normalize-space(./text())="")]'
}
# resourcespace API functions
_search(){
query="user=${user}&function=do_search¶m1=${1}¶m2=¶m3=resourceid¶m4=0"
sign=$(printf '%s' "${private_key}${query}" | sha256deep)
curl -# "${api_url}/?$query&sign=${sign}" | jq -r '.[] | select (.field8=="'"${1}"'") | .ref' 2>/dev/null
}
_search_public_collections(){
query="user=${user}&function=search_public_collections¶m1=¶m2=ref¶m3=ASC¶m4=false¶m5=false"
sign=$(printf '%s' "${private_key}${query}" | sha256deep)
curl -# "${api_url}/?$query&sign=${sign}" | jq -r '.[] | select (.name=="'"${1}"'") | .ref'
}
_add_resource_to_collection(){
query="user=${user}&function=add_resource_to_collection¶m1=${1}¶m2=${2}";
sign=$(printf '%s' "${private_key}${query}" | sha256deep)
curl -# "${api_url}/?$query&sign=${sign}"
}
_updatefield(){
uploadtext=$(echo "${3}" | sed "s|\"|'|g")
query="user=${user}&function=update_field¶m1=${1}¶m2=${2}¶m3=$(urlencode "${uploadtext}")"
sign=$(printf '%s' "${private_key}${query}" | sha256deep)
curl -# "${api_url}/?$query&sign=${sign}"
}
_query_protrack_push_to_resourcespace(){
MEDIAID="${1}"
echo -n "Working on ${MEDIAID}"
# search rs for mediaid
REF="$(_search "${MEDIAID}" 2>/dev/null | head -n 1)"
if [[ -z "${REF}" ]] ; then
echo ", not found in resourcespace, skipping."
else
echo ", found in resourcespace (ref=$REF)"
TEMPXML=$(_maketemp)
# get PBCore XML for that mediaid
"${SCRIPTDIR}/pbprotracktor" "${MEDIAID}" | _remove_empty_xml_nodes 2> /dev/null > "${TEMPXML}"
if [[ -f "${SCRIPTDIR}/fmpbcore" && -f "${TEMPXML}" && ! -s "${TEMPXML}" ]] ; then
echo "No data retrived from protrack. Now trying to grab pbcore for ${MEDIAID} using fmpbcore."
"${SCRIPTDIR}/fmpbcore" "${MEDIAID}" | _remove_empty_xml_nodes 2> /dev/null > "${TEMPXML}"
fi
if [[ ! -s "${TEMPXML}" ]] ; then
echo "Error, no pbcore data found for ${MEDIAID}."
return
fi
xml val "${TEMPXML}" >/dev/null
if [ "${?}" != "0" ] ; then
echo "pbprotracktor did not return a result for ${MEDIAID}, skipping."
return
fi
# upload asset type
UPLOAD_ASSETTYPE=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreAssetType -v . -n -b "${TEMPXML}")
_updatefield "${REF}" "${PBCORE_ASSETTYPE_FIELD}" "${UPLOAD_ASSETTYPE}"
# upload asset date
UPLOAD_ASSETDATE=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreAssetDate -v . -o " (" -v @dateType -o ")" -n -b "${TEMPXML}")
_updatefield "${REF}" "${PBCORE_ASSETDATE_FIELD}" "${UPLOAD_ASSETDATE}"
# upload identifiers
UPLOAD_IDENTIFIERS=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreIdentifier -v . -o " (" -v @source -o ")" -n -b "${TEMPXML}")
_updatefield "${REF}" "${PBCORE_IDENTIFIER_FIELD}" "${UPLOAD_IDENTIFIERS}"
# upload title
UPLOAD_TITLES=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m "//p:pbcoreDescriptionDocument/p:pbcoreTitle[@titleType!='Series']|p:pbcoreTitle[@titleType!='Subtitle']" -v "." -o " (" -v "@titleType" -o ")" -n -b "${TEMPXML}" | sed "s| ()||g")
_updatefield "${REF}" "${PBCORE_TITLE_FIELD}" "${UPLOAD_TITLES}"
# upload title series
UPLOAD_TITLE_SERIES=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreDescriptionDocument -v "p:pbcoreTitle[@titleType='Series'][1]" "${TEMPXML}")
_updatefield "${REF}" "${PBCORE_TITLE_SERIES_FIELD}" "${UPLOAD_TITLE_SERIES}"
# upload subtitle
UPLOAD_SUBTITLE=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreDescriptionDocument -v "p:pbcoreTitle[@titleType='Subtitle'][1]" "${TEMPXML}")
_updatefield "${REF}" "${PBCORE_SUBTITLE_FIELD}" "${UPLOAD_SUBTITLE}"
# upload description info
UPLOAD_DESCRIPTIONS=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreDescriptionDocument/p:pbcoreDescription -v . -o " (" -v @descriptionType -o ")" -n -n -b "${TEMPXML}" | sed "s| ()||g")
_updatefield "${REF}" "${PBCORE_DESCRIPTION_FIELD}" "${UPLOAD_DESCRIPTIONS}"
# upload coverage info
UPLOAD_COVERAGE=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreDescriptionDocument/p:pbcoreCoverage -v . -o " (" -v @coverageType -o ")" -n -b "${TEMPXML}" | sed "s| ()||g")
_updatefield "${REF}" "${PBCORE_COVERAGE_FIELD}" "${UPLOAD_COVERAGE}"
# upload creator info
UPLOAD_CREATORS=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreDescriptionDocument/p:pbcoreCreator -v p:creator -o " (" -v @creatorRole -o ")" -n -b "${TEMPXML}" | sed "s| \[\]||g;s| ()||g")
_updatefield "${REF}" "${PBCORE_CREATOR_FIELD}" "${UPLOAD_CREATORS}"
# upload contributor info
UPLOAD_CONTRIBUTORS=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreDescriptionDocument/p:pbcoreContributor -v p:contributor -o " [" -v p:contributor/@affiliation -o "] (" -v @contributorRole -o ")" -n -b "${TEMPXML}" | sed "s|\[\]||g;s|()||g")
_updatefield "${REF}" "${PBCORE_CONTRIBUTOR_FIELD}" "${UPLOAD_CONTRIBUTORS}"
# upload publisher info
UPLOAD_PUBLISHERS=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreDescriptionDocument/p:pbcorePublisher -v p:publisher -o " (" -v @publisherRole -o ")" "${TEMPXML}" | sed "s| \[\]||g;s| ()||g")
_updatefield "${REF}" "${PBCORE_PUBLISHER_FIELD}" "${UPLOAD_PUBLISHERS}"
# upload interstitial info
UPLOAD_INSTANTIATION=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreInstantiation -m p:instantiationIdentifier -v . -o " (" -v @source -o ")" -n -b -o "timestart: " -v p:instantiationTimeStart -n -o "duration: " -v p:instantiationDuration -n -n "${TEMPXML}" | sed "s| ()||g")
_updatefield "${REF}" "${PBCORE_INSTANTIATION_FIELD}" "${UPLOAD_INSTANTIATION}"
# provide last updated info
UPLOAD_LASTUPDATE=$(xml sel -N p="http://www.pbcore.org/PBCore/PBCoreNamespace.html" -T -t -m //p:pbcoreCollection -v @collectionDate "${TEMPXML}")
_updatefield "${REF}" "${LAST_UPDATED_FIELD}" "${UPLOAD_LASTUPDATE}"
if [ -f "${TEMPXML}" ] ;then
rm "${TEMPXML}"
fi
fi
}
_catalog_airings(){
CATALOGDATE="${1}"
CATALOGMONTH="$(echo "${CATALOGDATE}" | cut -d- -f1-2)"
CATALOGYEAR="$(echo "${CATALOGDATE}" | cut -d- -f1)"
CATALOG_NAME="${CATALOGDATE} Shows"
# check is a collection already exists for a broadcast date
DATE_COLLECTION_ID=$(_search_public_collections "${CATALOG_NAME}")
if [[ -z "${DATE_COLLECTION_ID}" ]] ; then
# create a collection in rs if needed
mysql -h "${RESOURCESPACE_DB_HOST}" -u "${RESOURCESPACE_DB_USER}" -p"${RESOURCESPACE_DB_PW}" -D "${RESOURCESPACE_DB_NAME}" -e "INSERT INTO collection (name,public,theme,theme2,theme3,theme4,created,user,allow_changes) VALUES('$(printf "%q" "${CATALOG_NAME}")',1,'Programming','$(printf "%q" "${CATALOGYEAR}")','$(printf "%q" "${CATALOGMONTH}")','$(printf "%q" "${CATALOGDATE}")',now(),3,1)" ;
DATE_COLLECTION_ID=$(_search_public_collections "${CATALOG_NAME}")
fi
# query protrack for records and add them all to a collection according to broadcast date
## programs
psql -Ath "$PROTRACK_DB_URL" -U "$PROTRACK_DB_USER" -P pager=off -d protrack -c "SELECT CASE WHEN li_material_id is not null THEN trim(li_material_id) ELSE trim(fi_video_src) END FROM prog_log LEFT JOIN linkinfo ON li_vsn_id = prog_log.log_vsn_id LEFT JOIN filler ON fi_serial = prog_log.log_fi_id WHERE log_start_inst BETWEEN '${CATALOGDATE}' AND '$(date -j -f "%Y-%m-%d" -v+1d "${CATALOGDATE}" "+%Y-%m-%d")' AND log_type ='PG'" | sort -u | while read id ; do
MEDIAREF=$(_search "${id}")
if [[ ! -z "${MEDIAREF}" ]] ; then
echo "Adding ${id} with ref id of ${MEDIAREF} to ${CATALOGDATE} with ref id of ${DATE_COLLECTION_ID}"
_add_resource_to_collection "${MEDIAREF}" "${DATE_COLLECTION_ID}"
else
echo NOT uploading "$id"
fi
done
## promos
CATALOG_PROMO_NAME="${CATALOGDATE} Promos"
DATE_PROMO_COLLECTION_ID=$(_search_public_collections "${CATALOG_PROMO_NAME}")
if [[ -z "${DATE_PROMO_COLLECTION_ID}" ]] ; then
# create a collection in rs if needed
mysql -h "${RESOURCESPACE_DB_HOST}" -u "${RESOURCESPACE_DB_USER}" -p"${RESOURCESPACE_DB_PW}" -D "${RESOURCESPACE_DB_NAME}" -e "INSERT INTO collection (name,public,theme,theme2,theme3,theme4,created,user,allow_changes) VALUES('$(printf "%q" "${CATALOG_PROMO_NAME}")',1,'Programming','$(printf "%q" "${CATALOGYEAR}")','$(printf "%q" "${CATALOGMONTH}")','$(printf "%q" "${CATALOGDATE}")',now(),3,1)" ;
DATE_PROMO_COLLECTION_ID=$(_search_public_collections "${CATALOG_PROMO_NAME}")
fi
psql -Ath "$PROTRACK_DB_URL" -U "$PROTRACK_DB_USER" -P pager=off -d protrack -c "SELECT CASE WHEN li_material_id is not null THEN trim(li_material_id) ELSE trim(fi_video_src) END FROM prog_log LEFT JOIN linkinfo ON li_vsn_id = prog_log.log_vsn_id LEFT JOIN filler ON fi_serial = prog_log.log_fi_id WHERE log_start_inst BETWEEN '${CATALOGDATE}' AND '$(date -j -f "%Y-%m-%d" -v+1d "${CATALOGDATE}" "+%Y-%m-%d")' AND log_type ='PR'" | sort -u | while read id ; do
MEDIAREF=$(_search "${id}")
if [[ ! -z "${MEDIAREF}" ]] ; then
echo "Adding ${id} with ref id of ${MEDIAREF} to ${CATALOGDATE} with ref id of ${DATE_PROMO_COLLECTION_ID}"
_add_resource_to_collection "${MEDIAREF}" "${DATE_PROMO_COLLECTION_ID}"
else
echo NOT uploading "$MEDIAREF"
fi
done
}
if [ ! -f "${CONF_FILE}" ] ; then
echo "A configuration file is needed, please edit ${CONF_FILE}."
echo "Please edit ${CONF_FILE}"
echo "PROTRACK_DB_URL=" > "${CONF_FILE}"
echo "PROTRACK_DB_USER=" >> "${CONF_FILE}"
echo "RESOURCESPACE_DB_HOST=" >> "${CONF_FILE}"
echo "RESOURCESPACE_DB_NAME=" >> "${CONF_FILE}"
echo "RESOURCESPACE_DB_USER=" >> "${CONF_FILE}"
echo "RESOURCESPACE_DB_PW=" >> "${CONF_FILE}"
echo "# for resourcespace api"
echo "private_key=" >> "${CONF_FILE}"
echo "user=" >> "${CONF_FILE}"
echo "api_url=" >> "${CONF_FILE}"
exit 1
fi
. "${CONF_FILE}" || { echo "Missing ${CONF_FILE}. Exiting." ; exit 1 ; }
OPTIND=1
while getopts ":f:F:t:h" OPT ; do
case "${OPT}" in
f) FROM_DATE_PT="${OPTARG}" ;;
F) FROM_DATE_RS="${OPTARG}" ;;
t) MAKEDATE="${OPTARG}" ;;
h) _usage ;;
:) echo "Option -${OPTARG} requires an argument" ; _writeerrorlog "makeyoutube" "The option selected required an argument and none was provided. The script had to exit." ; exit 1 ;;
*) echo "bad option -${OPTARG}" ; _usage ;;
esac
done
shift $(( ${OPTIND} - 1 ))
# if from date is relative (like a negative number) then figure out what that date actually is
if [[ "${FROM_DATE_PT:0:1}" = "-" ]] ; then
FROM_DATE_PT=$(date -j -v"${FROM_DATE_PT}"d "+%Y-%m-%d %H:%M:%S")
fi
if [[ "${FROM_DATE_RS:0:1}" = "-" ]] ; then
FROM_DATE_RS=$(date -j -v"${FROM_DATE_RS}"d "+%Y-%m-%d %H:%M:%S")
fi
if [[ ! -z "${MAKEDATE}" ]]; then
_catalog_airings "$MAKEDATE"
#_catalog_airings "$(date -j -v+1d "+%Y-%m-%d")"
fi
if [[ -n "${FROM_DATE_PT}" ]] ; then
# query protrack for filler and update data to rs
psql -Ath "$PROTRACK_DB_URL" -U "$PROTRACK_DB_USER" -P pager=off -d protrack -c "SELECT fi_video_src FROM filler AS f LEFT JOIN notes AS n ON n.no_parent = f.fi_serial WHERE f.up_date BETWEEN '${FROM_DATE_PT}' AND '3000-01-01' OR n.up_date BETWEEN '${FROM_DATE_PT}' AND '3000-01-01'" | while read MEDIAID ; do
_query_protrack_push_to_resourcespace "${MEDIAID}"
done
# query protrack for programs and episodes and update data to rs
psql -Ath "$PROTRACK_DB_URL" -U "$PROTRACK_DB_USER" -P pager=off -d protrack -c "SELECT li_material_id FROM linkinfo LEFT JOIN quad_tab ON linkinfo.li_vsn_id = quad_tab.vsn_serial WHERE linkinfo.up_date BETWEEN '${FROM_DATE_PT}' AND '3000-01-01'" | while read MEDIAID ; do
_query_protrack_push_to_resourcespace "${MEDIAID}"
done
elif [[ -n "${FROM_DATE_RS}" ]] ; then
# query rs for resources and query protrack for that data
echo "searching resourcespace records back to ${FROM_DATE_RS}"
mysql -BNr -h "${RESOURCESPACE_DB_HOST}" -u "${RESOURCESPACE_DB_USER}" -p"${RESOURCESPACE_DB_PW}" -D "${RESOURCESPACE_DB_NAME}" -e "SELECT field8 FROM resource WHERE archive!='3' and creation_date BETWEEN '${FROM_DATE_RS}' AND '3000-01-01'" | while read MEDIAID ; do
if [[ "${MEDIAID}" != "NULL" ]] && [[ "${MEDIAID}" != "" ]] ; then
_query_protrack_push_to_resourcespace "${MEDIAID}"
fi
done
else
# loop over list of ids provided and upload data from protrack to rs
while [ "${*}" != "" ] ; do
MEDIAID="${1}"
shift
_query_protrack_push_to_resourcespace "${MEDIAID}"
done
echo
fi