Object Description data mapping

This page contains information about PAHMA's data migration of Object Description Information.
See parent page on PAHMA data migration ETL work.

Person depicted extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ConXrefs	constituentID	contentPerson	reference to Person Authority

sql query

view: cs_personsdepicted (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID contentPerson
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
 JOIN constituents c ON x.constituentID = c.constituentID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.constituentTypeID = 1
 AND lower(r.ROLE) IN ('subject')
;

notes

Place depicted extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ThesXrefs	termID	contentPlace	reference to Place Authority

sql query

view: cs_placesdepicted (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.termID contentPlace
FROM
 ThesXrefs x
 JOIN ThesXrefTypes t ON x.thesXrefTypeID = t.thesXrefTypeID
 JOIN Objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND x.thesXrefTableID = 346
 AND lower(t.thesXrefType) IN ('subject')
;

notes

Cultural group depicted extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
TextEntries	textEntry	contentPeople	where textTypeID is 110 or 111

sql query

view: cs_culturalgroupsdepicted (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry contentPeople
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 (110, 111)
;

notes

Content note extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
TextEntries	textEntry	contentNote	where textTypeID is 85

sql query

view: cs_contentnotes (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry contentNote
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 = 85
;

notes

Inscription content extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	Signed	inscriptionContent
TextEntries	textEntry	inscriptionContent	where textTypeID is 19, 20, 22, 78, or 87

sql query

view: cs_inscriptionscontent (refreshed 2012-04-20)

SELECT     	CAST(o.objectID AS VARCHAR) objectID, 
		NULL inscriptionContent_prefix, 
		o.Signed inscriptionContent,
		NULL inscriptionType,
		NULL inscriptionTransliteration,
		NULL inscriptionTranslation,
		NULL inscriptionInterpretation,
		NULL inscriptionLanguage,
		NULL inscriptionScript,
		NULL inscriptionPosition,
		NULL inscriptionMethod,
		NULL inscriptionDate
FROM         Objects o
WHERE     o.IsVirtual = 0 AND o.IsTemplate = 0 AND o.Signed IS NOT NULL
UNION ALL
SELECT     	CAST(o.objectID AS VARCHAR) objectID, 
		(CASE WHEN t.textTypeID IN (19, 20, 22, 123) THEN substring(tt.TextType, 7, 1) + lower(substring(tt.TextType, 8, len(tt.TextType))) + ': ' ELSE '' END) inscriptionContent_prefix, 
		(CASE WHEN len(t.MTB_Inscription) > 2 THEN t.MTB_Inscription ELSE t.TextEntry END) inscriptionContent,
		(CASE WHEN t.textTypeID IN (19, 20, 22, 123) THEN 'coin inscription' ELSE '' END) inscriptionType,
		t.MTB_Transliteration inscriptionTransliteration,
		t.MTB_Translation inscriptionTranslation,
		t.MTB_Interpretation inscriptionInterpretation,
		t.MTB_Language inscriptionLanguage,
		t.MTB_Script inscriptionScript,
		t.MTB_InscriptionPosition inscriptionPosition,
		t.MTB_InscriptionMethod inscriptionMethod,
		t.MTB_Dated inscriptionDate
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 (19, 20, 22, 78, 87, 123)

notes

NOTE: 'inscriptionContent_prefix' should be prepended to 'inscriptionContent' prior to import (this can't be done here because some fields are type TEXT
which cannot be concatenated in SQL)
NOTE: multiple notes per object should be concatenated into single (non-repeating) field inscriptionContent, separated by line breaks
June 12, 2012: View updated to add additional fields