This page contains information about PAHMA's data migration of Media and related info.
See parent page on PAHMA data migration ETL work.
Media extract
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
---|---|---|---|
MediaRenditions |
renditionNumber |
identificationNumber |
|
MediaFiles |
fileName |
filename |
|
MediaPaths |
path |
path |
|
MediaFiles |
fileDate |
dateCreated |
|
MediaFormats |
format |
format |
|
MediaTypes |
mediaType |
type |
|
MediaMaster |
approvedForWeb |
approvedForWeb |
NEW custom field |
MediaMaster |
copyright |
rightsHolder |
reference to Person/Org Authority |
MediaMaster |
copyright |
copyrightStatement |
|
sql query
view: cs_media (refreshed 2012-05-03)
SELECT r.renditionID mediaID, r.renditionNumber identificationNumber, f.fileName filename, SUBSTRING(p.Path, 4, LEN(p.Path) - 3) 'path', f.fileDate dateCreated, t.format format, lower(y.mediaType) type, MAX(CASE WHEN m.approvedForWeb = 1 THEN 'yes' ELSE 'no' END) approvedForWeb, MAX(CASE WHEN m.copyright LIKE 'Benjamin M. Auerbach%' THEN 9223 WHEN m.copyright LIKE 'Brian Burd%' THEN 9225 WHEN m.copyright LIKE 'Darren Modzelewski%' THEN 8506 WHEN m.copyright LIKE 'Smithsonian Institution Archives%' THEN 9224 WHEN m.copyright LIKE 'Phoebe A. Hearst Museum of Anthropology%' THEN 8107 WHEN m.copyright LIKE '______/ Phoebe A. Hearst Museum of Anthropology%' THEN 8107 END) rightsHolder, MAX(CASE WHEN m.copyright LIKE '%/ %' THEN 'rights holder: ' + m.copyright END) copyrightStatement FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID INNER JOIN dbo.MediaPaths p ON f.PathID = p.PathID LEFT OUTER JOIN MediaFormats t ON f.formatID = t.formatID LEFT OUTER JOIN MediaTypes y ON r.mediaTypeID = y.mediaTypeID LEFT OUTER JOIN MediaMaster m ON r.renditionID = m.primaryRendID GROUP BY r.renditionID, r.renditionNumber, f.fileName, p.path, f.fileDate, t.format, y.mediaType ;
notes
- DONE: create new boolean field 'approvedForWeb'
Media Description extract
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
---|---|---|---|
MediaMaster |
publicCaption |
description |
|
MediaMaster |
mediaView |
description |
|
MediaMaster |
description |
description |
|
MediaMaster |
restrictions |
description |
|
MediaRenditions |
remarks |
description |
|
MediaXrefs |
remarks |
description |
|
sql query
view: cs_mediadescriptions (refreshed 2012-03-22)
SELECT r.renditionID mediaID, m.publicCaption description, 1 sortorder FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID JOIN MediaMaster m ON r.renditionID = m.primaryRendID WHERE m.publicCaption IS NOT NULL UNION ALL SELECT DISTINCT r.renditionID mediaID, m.mediaView description, 2 sortorder FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID JOIN MediaMaster m ON r.renditionID = m.primaryRendID WHERE m.mediaView IS NOT NULL UNION ALL SELECT r.renditionID mediaID, m.description description, 3 sortorder FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID JOIN MediaMaster m ON r.renditionID = m.primaryRendID WHERE m.description IS NOT NULL UNION ALL SELECT r.renditionID mediaID, m.restrictions description, 4 sortorder FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID JOIN MediaMaster m ON r.renditionID = m.primaryRendID WHERE m.restrictions IS NOT NULL UNION ALL SELECT r.renditionID mediaID, r.remarks description, 5 sortorder FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID WHERE r.remarks IS NOT NULL UNION ALL SELECT DISTINCT r.renditionID mediaID, x.remarks description, 6 sortorder FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID JOIN MediaMaster m ON r.renditionID = m.primaryRendID JOIN MediaXrefs x ON m.mediaMasterID = x.mediaMasterID WHERE x.remarks IS NOT NULL ;
notes
- NOTE: multiple descriptions per media record should be concatenated into single (non-repeating) field, separated by line breaks, in the order indicated by the 'sortorder' field
Media Dimensions extract
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
---|---|---|---|
MediaFiles |
fileSize |
dimensions.value |
with dimension = 'filesize', unit = 'bytes' |
MediaFiles |
memorySize |
dimensions.value |
with dimension = 'memorysize', unit = 'bytes' |
MediaFiles |
pixelH |
dimensions.value |
with dimension = 'height', unit = 'pixels' |
MediaFiles |
pixelW |
dimensions.value |
with dimension = 'width', unit = 'pixels' |
sql query
view: cs_mediadimensions (refreshed 2012-03-22)
SELECT r.renditionID mediaID, 'digital file' media_dimension_measuredPart, 'filesize' media_dimension_dimension, 'bytes' media_dimension_measurementUnit, f.fileSize media_dimension_value FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID WHERE f.fileSize != 0 UNION ALL SELECT r.renditionID mediaID, 'digital file' media_dimension_measuredPart, 'memorysize' media_dimension_dimension, 'bytes' media_dimension_measurementUnit, f.memorySize media_dimension_value FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID WHERE f.memorySize != 0 UNION ALL SELECT r.renditionID mediaID, 'digital file' media_dimension_measuredPart, 'height' media_dimension_dimension, 'pixels' media_dimension_measurementUnit, f.pixelH media_dimension_value FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID WHERE f.pixelH != 0 UNION ALL SELECT r.renditionID mediaID, 'digital file' media_dimension_measuredPart, 'width' media_dimension_dimension, 'pixels' media_dimension_measurementUnit, f.pixelW media_dimension_value FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID WHERE f.pixelW != 0 ;
notes
Media-Objects Relationships extract
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
---|---|---|---|
MediaRenditions |
renditionID |
reference to Media |
|
Objects |
objectID |
reference to Object |
|
sql query
view: cs_mediaobjectrelationships (refreshed 2012-03-22)
SELECT CAST(o.objectID AS VARCHAR) objectID, r.renditionID mediaID FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID JOIN MediaMaster m ON r.renditionID = m.primaryRendID JOIN MediaXrefs x ON m.mediaMasterID = x.mediaMasterID JOIN Objects o ON x.ID = o.objectID WHERE o.IsVirtual = 0 AND o.IsTemplate = 0 AND x.tableID = 108 ;
notes
Media "Primary Display" extract
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
---|---|---|---|
MediaXrefs |
primaryDisplay |
primaryDisplay |
NEW custom field |
sql query
view: cs_mediaprimarydisplay (refreshed 2012-03-22)
SELECT r.renditionID mediaID, MAX(CASE WHEN x.primaryDisplay = 1 THEN 'yes' ELSE 'no' END) primaryDisplay FROM MediaRenditions r JOIN MediaFiles f ON r.primaryFileID = f.fileID JOIN MediaMaster m ON r.renditionID = m.primaryRendID JOIN MediaXrefs x ON m.mediaMasterID = x.mediaMasterID JOIN Objects o ON x.ID = o.objectID WHERE o.IsVirtual = 0 AND o.IsTemplate = 0 AND x.tableID = 108 GROUP BY r.renditionID ;
notes
- NOTE: 'primaryDisplay' is really an attribute of Media-Object relationship, but there is no comparable place to put it in CSpace, so we are importing it into Media record instead; this will result it some data loss since some Media records have different 'primaryDisplay' values for different related objects
- DONE: create new boolean field 'primaryDisplay'