Versions Compared

Old Version 4

changes.mady.by.user John W Keller

Saved on

compared with

New Version 5

changes.mady.by.user John W Keller

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table of Contents
maxlevel3

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
sql
sql
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) 
;

...