Object Annotations data mapping

This page contains information about PAHMA's data migration of Object Annotations.
See parent page on PAHMA data migration ETL work.

Annotations extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

Objects

Edition

annotationNote

 

Objects

CuratorialRemarks

annotationNote

 

Components

PrepComments

annotationNote

 

ObjUserFields

FieldValue

annotationNote

 

TextEntries

TextEntry

annotationNote

 

TextEntries

TextTypeID

annotationType

FK to TextTypes

TextEntries

TextDate

annotationDate

 

TextEntries

AuthorConID

annotationAuthor

reference to Person Authority

sql query

view: cs_annotations (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.edition annotationNote,
 'TMS edition field' annotationType,
 NULL annotationDate,
 NULL annotationAuthor
FROM
 Objects o
WHERE
 o.edition IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.curatorialRemarks annotationNote,
 'TMS curatorial remarks' annotationType,
 NULL annotationDate,
 NULL annotationAuthor
FROM
 Objects o
WHERE
 o.curatorialRemarks IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.prepComments annotationNote,
 'Component prep comments' annotationType,
 NULL annotationDate,
 NULL annotationAuthor
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.prepComments IS NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.prepComments annotationNote,
 'Component prep comments' annotationType,
 NULL annotationDate,
 NULL annotationAuthor
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.prepComments IS NOT NULL
UNION ALL
SELECT
 CAST(u.objectID AS VARCHAR) objectID,
 u.fieldValue annotationNote,
 substring(t.userFieldType, 1, charindex(' ', t.userFieldType) - 1) + lower(substring(t.userFieldType, charindex(' ', t.userFieldType), len(t.userFieldType))) annotationType,
 NULL annotationDate,
 NULL annotationAuthor
FROM
 ObjUserFields u
 JOIN UserFieldTypes t ON u.fieldTypeID = t.userFieldTypeID
WHERE
 u.FieldTypeID != 17
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry annotationNote,
 substring(REPLACE(tt.TextType, 'Coin: ', ''), 1, charindex(' ', REPLACE(tt.TextType + ' ', 'Coin: ', '')) - 1) + lower(substring(REPLACE(tt.TextType, 'Coin: ', ''), charindex(' ', REPLACE(tt.TextType + ' ', 'Coin: ' , '')), len(tt.TextType))) annotationType,
 t.TextDate annotationDate,
 t.AuthorConID annotationAuthor
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID IN (2, 11, 13, 16, 17, 18, 21, 24, 25, 26, 28, 29, 30, 36, 44, 51, 55, 64, 66, 67, 68, 71, 72, 74, 80, 83, 88, 94, 95, 98, 105, 107, 109, 118, 120, 122)
;

notes

  • some annotationType field values set by query above
  • DONE: add all controlled list options for annotationType field