...
Table of Contents | ||
---|---|---|
|
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 |
|
TextEntries | TextEntry | annotationNote |
|
TextTypes TextEntries | TextType TextTypeID | annotationType | FK to TextEntries on TextTypeID TextTypes |
TextEntries | TextDate | annotationDate |
|
TextEntries | AuthorConID | annotationAuthor | reference to Person Authority |
...
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, o.edition annotationNote, 'TMS Editionedition 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) ; |
...