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
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
- TODO: update controlled list options for annotationType field, using this SQL query:
SELECT 'TMS edition field' annotationType UNION SELECT 'TMS curatorial remarks' annotationType UNION SELECT 'Component prep comments' annotationType UNION SELECT DISTINCT substring(t.userFieldType, 1, charindex(' ', t.userFieldType) - 1) + lower(substring(t.userFieldType, charindex(' ', t.userFieldType), len(t.userFieldType))) annotationType FROM ObjUserFields u JOIN UserFieldTypes t ON u.fieldTypeID = t.userFieldTypeID WHERE u.FieldTypeID != 17 UNION SELECT DISTINCT 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 FROM TextTypes tt WHERE tt.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) ORDER BY annotationType ;