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 |
notes |
---|---|---|---|
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...?