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

in progress

field mapping

TMS table name	TMS field name	CSpace field name
Objects	Edition	annotationNote
Objects	CuratorialRemarks	annotationNote
Components	PrepComments	annotationNote
ObjUserFields	FieldValue	annotationNote

sql query

view: cs_annotations

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.edition annotationNote,
 'TMS Edition field' annotationType
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
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
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
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,
 t.userFieldType annotationType
FROM
 ObjUserFields u
 JOIN UserFieldTypes t ON u.fieldTypeID = t.userFieldTypeID
WHERE
 u.FieldTypeID != 17
;

notes

some annotationType field values set by query above
TODO: update controlled list options for annotationType field
TODO: include some data from TextEntries table in this query...?