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

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'