...
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 |
|
...
Code Block | ||||
---|---|---|---|---|
| ||||
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
;
|
...