On 11/2/18 11:13 AM, Andy Higginson wrote: > Thank you Fred and David. You have both been most helpful. > > One other quick question while I'm thinking about music libraries. Is > there a way to use a spreadsheet of music library data (talktime, segue > points, hooks etc) with a Rivendell library? For instance the data > provided by Charlie Davy (http://www.charliedavy.co.uk/music-library/) > and others. It would be useful to take out some of the grunt work from > setting up a library. >
You would likely need to export the CSV data and use an app to coerce it into the acceptable XML format used in Rivendell. Not hard, but would require attention to the details. Speaking of which, Fred do you have a formal schema (DTD) for the XML used in the export data? I recently had a partially broken Rivendell installation (my "lab" system) and wanted to preserve the library, so I made my own "exporter" and created the XML with a script (attached for reference). Thanks! ~David
#!/bin/zsh
zmodload zsh/datetime
. /usr/local/bin/zsh-functions
# mysql -e "describe CART"
# NUMBER int(10) unsigned NO PRI NULL
# TYPE int(10) unsigned NO NULL
# GROUP_NAME char(10) NO MUL NULL
# TITLE char(255) YES MUL NULL
# ARTIST char(255) YES MUL NULL
# ALBUM char(255) YES MUL NULL
# YEAR date YES NULL
# CONDUCTOR char(64) YES MUL NULL
# LABEL char(64) YES MUL NULL
# CLIENT char(64) YES MUL NULL
# AGENCY char(64) YES MUL NULL
# PUBLISHER char(64) YES MUL NULL
# COMPOSER char(64) YES MUL NULL
# USER_DEFINED char(255) YES MUL NULL
# SONG_ID char(32) YES MUL NULL
# BPM int(10) unsigned YES 0
# USAGE_CODE int(11) YES 0
# FORCED_LENGTH int(10) unsigned YES NULL
# AVERAGE_LENGTH int(10) unsigned YES NULL
# LENGTH_DEVIATION int(10) unsigned YES 0
# AVERAGE_SEGUE_LENGTH int(10) unsigned YES NULL
# AVERAGE_HOOK_LENGTH int(10) unsigned YES 0
# CUT_QUANTITY int(10) unsigned YES NULL
# LAST_CUT_PLAYED int(10) unsigned YES NULL
# PLAY_ORDER int(10) unsigned YES NULL
# VALIDITY int(10) unsigned YES 2
# START_DATETIME datetime YES NULL
# END_DATETIME datetime YES NULL
# ENFORCE_LENGTH enum('N','Y') YES N
# USE_WEIGHTING enum('N','Y') YES Y
# PRESERVE_PITCH enum('N','Y') YES N
# ASYNCRONOUS enum('N','Y') YES N
# OWNER char(64) YES MUL NULL
# MACROS text YES NULL
# SCHED_CODES varchar(255) YES NULL
# NOTES text YES NULL
# METADATA_DATETIME datetime YES MUL NULL
# USE_EVENT_LENGTH enum('N','Y') YES N
# PENDING_STATION char(64) YES MUL NULL
# PENDING_PID int(11) YES NULL
# PENDING_DATETIME datetime YES MUL NULL
# mysql -e "describe CUTS"
# CUT_NAME char(12) NO PRI NULL
# CART_NUMBER int(10) unsigned NO MUL NULL
# EVERGREEN enum('N','Y') YES N
# DESCRIPTION char(64) YES MUL NULL
# OUTCUE char(64) YES MUL NULL
# ISRC char(12) YES MUL NULL
# ISCI char(32) YES MUL NULL
# LENGTH int(10) unsigned YES NULL
# SHA1_HASH char(40) YES MUL NULL
# ORIGIN_DATETIME datetime YES MUL NULL
# START_DATETIME datetime YES MUL NULL
# END_DATETIME datetime YES MUL NULL
# SUN enum('N','Y') YES Y
# MON enum('N','Y') YES Y
# TUE enum('N','Y') YES Y
# WED enum('N','Y') YES Y
# THU enum('N','Y') YES Y
# FRI enum('N','Y') YES Y
# SAT enum('N','Y') YES Y
# START_DAYPART time YES NULL
# END_DAYPART time YES NULL
# ORIGIN_NAME char(64) YES NULL
# ORIGIN_LOGIN_NAME char(255) YES NULL
# SOURCE_HOSTNAME char(255) YES NULL
# WEIGHT int(10) unsigned YES 1
# PLAY_ORDER int(11) YES NULL
# LAST_PLAY_DATETIME datetime YES NULL
# UPLOAD_DATETIME datetime YES NULL
# PLAY_COUNTER int(10) unsigned YES 0
# LOCAL_COUNTER int(10) unsigned YES 0
# VALIDITY int(10) unsigned YES 2
# CODING_FORMAT int(10) unsigned YES NULL
# SAMPLE_RATE int(10) unsigned YES NULL
# BIT_RATE int(10) unsigned YES NULL
# CHANNELS int(10) unsigned YES NULL
# PLAY_GAIN int(11) YES 0
# START_POINT int(11) YES -1
# END_POINT int(11) YES -1
# FADEUP_POINT int(11) YES -1
# FADEDOWN_POINT int(11) YES -1
# SEGUE_START_POINT int(11) YES -1
# SEGUE_END_POINT int(11) YES -1
# SEGUE_GAIN int(11) YES -3000
# HOOK_START_POINT int(11) YES -1
# HOOK_END_POINT int(11) YES -1
# TALK_START_POINT int(11) YES -1
# TALK_END_POINT int(11) YES -1
rightNow=$(strftime "%FT%T-05:00" ${EPOCHSECONDS})
typeset -i cartLoopCount=0
typeset -Z 6 cartn
typeset -Z 3 cutn
oIFS="${IFS}"
IFS='|'
#
# NOTES:
# - The shell seems to not like empty fields, or sequences of multiple
# field separator characters, so we use '^|' in the SELECT statement
# and strip off the '^' (with ${foo/^/) when we expand the shell parameters.
#
# - BUG ALERT: make sure no data include the '^' or '|' characters.
#
# - There seems to be a limit to the number of columns (arguments)
# CONCAT_WS() can accept, so we use some "shortcuts to cheat the
# conversion to XML.
#
# - Exclude the XTLD groups SynMusic and SynNews.
#
doSQL "SELECT
CONCAT_WS('^|',number,type,group_name,title,artist,album,YEAR(year),conductor,label,client,agency,publisher,composer,user_defined,usage_code,forced_length,average_length,length_deviation,average_segue_length,average_hook_length,cut_quantity,metadata_datetime)
FROM CART WHERE type=1 AND group_name NOT REGEXP 'Syn(Music|News)' ORDER BY
number" |
while read number type group_name title artist album year conductor label
client agency publisher composer user_defined usage_code forced_length
average_length length_deviation average_segue_length average_hook_length
cut_quantity metadata_datetime ; do
cartn=${number/^/}
echo "CART: ${cartn} (${number}) - ${title/^/}" >&2
if [[ -f /var/snd/${cartn}_001.wav ]] ; then
typeset -i cutLoopCount=0
# The audio file *MUST* exist in order for this whole
# thing to work.
for cut in /var/snd/${cartn}_???.wav ; do
cutn=${${cut#*_}%.wav}
doSQL "SELECT
CONCAT_WS('^|',cut_name,description,length,weight,coding_format,sample_rate,bit_rate,channels,play_gain,start_point,end_point,fadeup_point,fadedown_point,segue_start_point,segue_end_point,segue_gain,hook_start_point,hook_end_point,talk_start_point,talk_end_point)
FROM CUTS WHERE cut_name = '${cartn}_${cutn}'" |
while read cut_name description length weight coding_format
sample_rate bit_rate channels play_gain start_point end_point fadeup_point
fadedown_point segue_start_point segue_end_point segue_gain hook_start_point
hook_end_point talk_start_point talk_end_point ; do
outputTempFile=$(mktemp)
trap "rm -f ${outputTempFile} ; exit ;" 0 1 2 3
echo "CART: ${cartn} - ${title/^/} CUT: ${cutn}: $((
${length/^/} / 1000 ))" >&2
# We set (or reset) several values to
# defaults; e.g., Last Cut Played, daypart
# info, Cut Last Played datetime, and play
# counter.
cat <<EOF > ${outputTempFile}
<cart>
<number>${number/^/}</number>
<type>${type/^/}</type>
<groupName>${group_name/^/}</groupName>
<title>${title/^/}</title>
<artist>${artist/^/}</artist>
<album>${album/^/}</album>
<<year>${year/^/}</year>
<conductor>${conductor/^/}</conductor>
<label>${label/^/}</label>
<client>${client/^/}</client>
<agency>${agency/^/}</agency>
<publisher>${publisher/^/}</publisher>
<composer>${composer/^/}</composer>
<userDefined>${user_defined/^/}</userDefined>
<usageCode>${${usage_code//NULL/}/^/}</usageCode>
<forcedLength>${${forced_length//NULL/}/^/}</forcedLength>
<averageLength>${${average_length//NULL/}/^/}</averageLength>
<lengthDeviation>${${length_deviation//NULL/}/^/}</lengthDeviation>
<averageSegueLength>${average_segue_length/^/}</averageSegueLength>
<averageHookLength>${average_hook_length/^/}</averageHookLength>
<cutQuantity>${cut_quantity/^/}</cutQuantity>
<lastCutPlayed>0</lastCutPlayed>
<enforceLength>false</enforceLength>
<asynchronous>false</asynchronous>
<owner></owner>
<metadataDatetime>${metadata_datetime/^/}</metadataDatetime>
<songId></songId>
<cutList>
<cut>
<cutName>${cut_name/^/}</cutName>
<cartNumber>${number/^/}</cartNumber>
<evergreen>false</evergreen>
<description>${description/^/}</description>
<outcue></outcue>
<isrc></isrc>
<isci></isci>
<length>${length/^/}</length>
<originDatetime>${rightNow}</originDatetime>
<startDatetime></startDatetime>
<endDatetime></endDatetime>
<sun>true</sun>
<mon>true</mon>
<tue>true</tue>
<wed>true</wed>
<thu>true</thu>
<fri>true</fri>
<sat>true</sat>
<startDaypart></startDaypart>
<endDaypart></endDaypart>
<originName>ram</originName>
<originLoginName>user</originLoginName>
<sourceHostname>ram</sourceHostname>
<weight>${weight/^/}</weight>
<lastPlayDatetime></lastPlayDatetime>
<playCounter>0</playCounter>
<codingFormat>${coding_format/^/}</codingFormat>
<sampleRate>${sample_rate/^/}</sampleRate>
<bitRate>${bit_rate/^/}</bitRate>
<channels>${channels/^/}</channels>
<playGain>${play_gain/^/}</playGain>
<startPoint>${start_point/^/}</startPoint>
<endPoint>${end_point/^/}</endPoint>
<fadeupPoint>${fadeup_point/^/}</fadeupPoint>
<fadedownPoint>${fadedown_point/^/}</fadedownPoint>
<segueStartPoint>${segue_start_point/^/}</segueStartPoint>
<segueEndPoint>${segue_end_point/^/}</segueEndPoint>
<segueGain>${segue_gain/^/}</segueGain>
<hookStartPoint>${hook_start_point/^/}</hookStartPoint>
<hookEndPoint>${hook_end_point/^/}</hookEndPoint>
<talkStartPoint>${talk_start_point/^/}</talkStartPoint>
<talkEndPoint>${talk_end_point/^/}</talkEndPoint>
</cut>
</cutList>
</cart>
EOF
((cutLoopCount++))
done
echo "\t\t---->> sox /var/snd/${cartn}_${cutn}.wav
/mnt/varsnd/${cartn}-${cutn}.flac" >&2
# Use FLAC to save a bit of space.
sox /var/snd/${cartn}_${cutn}.wav
/mnt/varsnd/${cartn}-${cutn}.flac
echo "\t\t---->> cutLoopCount: ${cutLoopCount}" >&2
mv ${outputTempFile} /mnt/varsnd/${cartn}-${cutn}.xml
done
fi
((cartLoopCount++))
echo "\t---->>cartLoopCount: ${cartLoopCount}" >&2
done
exit
signature.asc
Description: OpenPGP digital signature
_______________________________________________ Rivendell-dev mailing list [email protected] http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev
