PAHMA Media data mapping

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	relative to TMSimages directory
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
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'