PAHMA Collection Objects data mapping

This page contains information about PAHMA's data migration of CollectionObjects and related info.
See parent page on PAHMA data migration ETL work.

Collection Objects extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	ObjectID	pahmaObjectID
ObjComponents	ComponentID	pahmaObjectID	must ensure no duplicates with Objects.ObjectID
Objects	ObjectNumber	objectNumber
ObjComponents	ComponentNumber	objectNumber
Objects	'no'	isComponent
ObjComponents	'yes'	isComponent
Objects	CatRais	pahmaCollection	NEW field (repeating; controlled list)
Objects	DepartmentID	pahmaTmsLegacyDepartment	FK to Departments; NEW field (controlled list)
Objects	HistAttributions	objectHistoryNote
Objects	LoanClass	pahmaTms2003DataSource	NEW field (controlled list)
Objects	Markings	distinguishingFeatures
Objects	Medium	material
Objects	ObjectStatusID	pahmaObjectStatus	6/12/2012: deprecated. Object Status becoming a repeating field.
Objects	ObjectTypeID	collection	FK to ObjectTypes
Objects	PaperFileRef	nagpraInventoryName	FK to MTB_NagpraInvNames; controlled list
Objects	Portfolio	portfolioSeries	FK to MTB_PortfolioSeries; NEW field (controlled list)
Objects	MTB_ResponsibleDept	responsibleDepartment	controlled list
Objects	State	pahmaNagpraCodeLegacy	FK to MTB_NagpraBurialCodes; controlled list
ObjContext	Integer3	inventoryCount	NEW field

sql query

view: cs_objects (refreshed 2012-04-10)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.ObjectNumber objectNumber,
 'no' isComponent,
 o.CatRais pahmaCollection,
 d.Department pahmaTmsLegacyDepartment,
 o.HistAttributions objectHistoryNote,
 (CASE WHEN lower(o.LoanClass) not in ('', 'catalog card', 'inventory', 'ledger') THEN o.LoanClass END) pahmaTms2003DataSource,
 o.Markings distinguishingFeatures,
 o.Medium material,
 s.ObjectStatus pahmaObjectStatus,
 t.ObjectType collection,
 i.CSpaceID nagpraInventoryName,
 o.MTB_Portfolio portfolioSeries,
 (CASE WHEN o.MTB_ResponsibleDept = 1 THEN 'Natasha Johnson'
       WHEN o.MTB_ResponsibleDept = 2 THEN 'Leslie Freund'
       WHEN o.MTB_ResponsibleDept = 3 THEN 'Victoria Bradshaw'
       WHEN o.MTB_ResponsibleDept = 4 THEN 'Alicja Egbert'
       WHEN o.MTB_ResponsibleDept = 5 THEN 'No collection manager (Registration)'
       WHEN o.MTB_ResponsibleDept = 8 THEN 'uncertain' END) responsibleDepartment,
 b.optionID pahmaNagpraCodeLegacy,
 c.integer3 inventoryCount
FROM
 Objects o
 LEFT OUTER JOIN Departments d ON o.departmentID = d.departmentID
 LEFT OUTER JOIN ObjectStatuses s ON o.objectStatusID = s.objectStatusID
 LEFT OUTER JOIN ObjectTypes t ON o.objectTypeID = t.objectTypeID
 LEFT OUTER JOIN MTB_NagpraInvNames i ON o.paperFileRef = i.LongTMSname
 LEFT OUTER JOIN MTB_NagpraBurialCodes b ON o.state = b.code
 LEFT OUTER JOIN ObjContext c ON o.objectID = c.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.componentNumber objectNumber,
 'yes' isComponent,
 NULL pahmaCollection,
 NULL pahmaTmsLegacyDepartment,
 NULL objectHistoryNote,
 NULL pahmaTms2003DataSource,
 NULL distinguishingFeatures,
 NULL material,
 NULL pahmaObjectStatus,
 NULL collection,
 NULL nagpraInventoryName,
 NULL portfolioSeries,
 NULL responsibleDepartment,
 NULL pahmaNagpraCodeLegacy,
 NULL inventoryCount
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

notes

object components are included as first-class objects if and only if there are multiple components for parent object
object components should have componentID (objectID) that does not conflict with any parent objectID: we can prepend "c" to the componentID to ensure this, per Michael (1/12/12)
many object components have a non-unique componentNumber (objectNumber), duplicated by other components and/or parent object; these values are OK to import as-is, per Michael (1/12/12)
"componentType" field does not need to be imported, per Michael (1/12/12)
"inscribed" field has only a few entries, which can be updated manually after data migration, per Michael (12/15/11)
"dimensions" field will be handled by a separate extract (John Lowe is working on this as of 1/9/12)
DONE: add 6 new custom fields: pahmaCollection, objectStatus, pahmaTms2003DataSource, pahmaTmsLegacyDepartment, inventoryCount, portfolioSeries
DONE: add controlled list options for 5 of these new fields (all except inventoryCount)
DONE: nagpraInventoryName field values need to be transformed to match option ID values in existing controlled list
DONE: add isComponent field for indicating components
June 12, 2012: Inventory Count now coming from two fields.
June 12, 2012: Object Status now a repeating field so not using pahmaObjectStatus in this view

Object-Object Relationships extract

field mapping

TMS table name	TMS field name	CSpace field name
ObjComponents	ObjectID	reference to one Object
ObjComponents	ComponentID	reference to other Object
Associations	ID1	reference to one Object
Associations	ID2	reference to other Object

sql query

view: cs_objectobjectrelationships (refreshed 2012-04-10)

SELECT
 CAST(c.objectID AS VARCHAR) objectID_1,
  'c' + CAST(c.componentID AS VARCHAR) objectID_2,
  'Object-Component' relationshipType
FROM
  ObjComponents c
WHERE
  c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
UNION ALL
SELECT
 CAST(oa.ID1 AS VARCHAR) objectID_1,
 CAST(oa.ID2 AS VARCHAR) objectID_2,
 r.relation1 + '-' + r.relation2 relationshipType
FROM
 Associations oa,
 Objects obj1,
 Objects obj2,
 Relationships r
WHERE
 oa.tableID = 108
 AND oa.ID1 = obj1.objectID
 AND oa.ID2 = obj2.objectID
 AND obj1.IsVirtual = 0 AND obj1.IsTemplate = 0
 AND obj2.IsVirtual = 0 AND obj2.IsTemplate = 0
 AND oa.relationshipID = r.relationshipID
;

notes

this should be imported as two-way relationship, so it shows up in UI for both objects
relationship type is included in this query, but we don't have a good place to put it in CSpace

Number of Objects extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	ObjectCount	numberOfObjects
ObjComponents	CompCount	numberOfObjects

sql query

view: cs_numberofobjects (refreshed 2012-04-10)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.objectCount numberOfObjects
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.objectID NOT IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN c.compCount > o.objectCount THEN c.compCount ELSE o.objectCount END) numberOfObjects
FROM
 Objects o
 JOIN  ObjComponents c ON o.objectID = c.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.compCount numberOfObjects
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

notes

for objects with single component, componentCount should be used in lieu of objectCount if and only if it is greater than objectCount, per Michael (1/5/12)

Alternate Numbers extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
AltNums	AltNum	pahmaAltNum
ObjComponents	ComponentNumber	pahmaAltNum
AltNums	Description	pahmaAltNumType	controlled list
AltNums	Remarks	pahmaAltNumNote

sql query

view: cs_alternatenumbers (refreshed 2012-04-10)

SELECT
  CAST(n.ID AS VARCHAR) objectID,
  n.altnum pahmaAltNum,
  n.description pahmaAltNumType,
  n.remarks pahmaAltNumNote
FROM
  AltNums n
WHERE
  n.tableID = 108
 AND n.altnum IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.componentNumber pahmaAltNum,
 NULL pahmaAltNumType,
 NULL pahmaAltNumNote
FROM
 ObjComponents c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.componentNumber IS NOT NULL
 AND c.componentNumber != o.objectNumber
;

notes

DONE: 3 new custom fields in repeating field group
DONE: add controlled list options for pahmaAltNumType field

Object Names extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	ObjectName	objectName
ObjComponents	ComponentName	objectName
ObjectNames	ObjectName	objectName
ObjectNames	LanguageID	objectNameLanguage	FK to Languages
ObjectNames	ObjectNameTypeID	objectNameNote	FK to ObjectNameTypes; concatenate with Remarks
ObjectNames	Remarks	objectNameNote	concatenate with ObjectNameType

sql query

view: cs_objectnames (refreshed 2012-03-22)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.objectName,
 NULL objectNameLanguage,
 NULL objectNameNote
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.objectName IS NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.componentName objectName,
 NULL objectNameLanguage,
 NULL objectNameNote
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.componentName IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.componentName objectName,
 NULL objectNameLanguage,
 'Component name' objectNameNote
FROM
 ObjComponents c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.componentName IS NOT NULL
 AND coalesce(o.objectName,'') != c.componentName
UNION ALL
SELECT
 CAST(n.objectID AS VARCHAR) objectID,
 n.objectName,
 (CASE WHEN n.languageID != '0' THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(languages):item:name(' + rtrim(l.MTB_ISOcode) + ')''' + (CASE WHEN charindex('/', l.language) > 0 THEN substring(l.language, 1, charindex('/', l.language) - 1) ELSE l.language END) + '''' END) objectNameLanguage,
 (CASE WHEN n.objectNameTypeID != '0' THEN t.objectNameType ELSE '' END) + (CASE WHEN n.objectNameTypeID != '0' AND n.remarks is not null THEN '; ' ELSE '' END) + coalesce(n.remarks, '') objectNameNote
FROM
 objectNames n
 LEFT OUTER JOIN objectNameTypes t ON n.objectNameTypeID = t.objectNameTypeID
 LEFT OUTER JOIN languages l ON n.languageID = l.languageID
WHERE
 n.objectName IS NOT NULL
;

notes

DONE: change objectNameLanguage from static term list to vocabulary-controlled list

Brief Descriptions extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	description	briefDescription
ObjComponents	physDesc	briefDescription
TextEntries	textEntry	briefDescription	where textTypeID is 6, 7, or 23

sql query

view: cs_briefdescriptions (refreshed 2012-04-10)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.description briefDescription
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.description IS NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.physDesc briefDescription
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.physDesc IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.physDesc briefDescription
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.physDesc IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry briefDescription
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 (6, 7, 23)
;

Object Titles extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	Title	title
ObjTitles	Title	title
ObjTitles	LanguageID	titleLanguage	FK to Languages
ObjTitles	TitleTypeID	titleType	FK to TitleTypes

sql query

view: cs_objecttitles (refreshed 2012-03-22)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 Title title,
 NULL titleLanguage,
 NULL titleType
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.title IS NOT NULL
UNION ALL
SELECT
 CAST(n.objectID AS VARCHAR) objectID,
 n.title,
 (CASE WHEN n.languageID != '0' THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(languages):item:name(' + rtrim(l.MTB_ISOcode) + ')''' + (CASE WHEN charindex('/', l.language) > 0 THEN substring(l.language, 1, charindex('/', l.language) - 1) ELSE l.language END) + '''' END) titleLanguage,
 (CASE WHEN n.titleTypeID != '0' THEN t.titleType END) titleType
FROM
 objTitles n
 LEFT OUTER JOIN titleTypes t ON n.titleTypeID = t.titleTypeID
 LEFT OUTER JOIN languages l ON n.languageID = l.languageID
;

notes

"remarks" field has only a few entries, which can be updated manually after data migration, per Michael (12/15/11)
DONE: change titleLanguage from static term list to vocabulary-controlled list

Ethnographic File Codes extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjUserFields	FieldValue	pahmaEthnographicFileCode

sql query

view: cs_ethnographicfilecodes

SELECT
 CAST(u.objectID AS VARCHAR) objectID,
 c.CSoptionID pahmaEthnographicFileCode
FROM
 ObjUserFields u
 JOIN MTB_EthnographicUseCodes c ON u.FieldValue = c.FieldValue
WHERE
 u.FieldTypeID = 17
;

notes

pahmaEthnographicFileCode is currently a static term list, but it will be changed to a Concept Authority reference once that authority is available

Comments extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	Notes	comment
TextEntries	textEntry	comment	where textTypeID is 27

sql query

view: cs_objectcomments (refreshed 2012-04-10)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.Notes comment
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.Notes IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry comment
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 = 27
;

notes

Materials note extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
TextEntries	textEntry	materialComponentNote	where textTypeID is 37 or 92

sql query

view: cs_materialsnotes (refreshed 2012-04-10)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN t.textTypeID = 37 THEN tt.TextType + ': ' ELSE '' END) materialComponentNote_prefix,
 t.TextEntry materialComponentNote
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 (37, 92)
;

notes

NOTE: 'materialComponentNote_prefix' should be prepended to 'materialComponentNote' prior to import (this can't be done here because some fields are type TEXT which cannot be concatenated in SQL)

NAGPRA cultural determination extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
TextEntries	textEntry	nagpraCulturalDetermination	where textTypeID is 121

sql query

view: cs_nagpraculturaldeterminations (refreshed 2012-04-10)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 substring(tt.TextType, 1, 7) + lower(substring(tt.TextType, 8, len(tt.TextType))) + ': ' nagpraCulturalDetermination_prefix,
 t.TextEntry nagpraCulturalDetermination
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 = 121
 ;

notes

NOTE: 'nagpraCulturalDetermination_prefix' should be prepended to 'nagpraCulturalDetermination' prior to import (this can't be done here because some fields are type TEXT which cannot be concatenated in SQL)

Repatriation note extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
TextEntries	textEntry	repatriationNote	where textTypeID is 81

sql query

view: cs_repatriationnotes (refreshed 2012-04-10)

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

notes

Taxonomic determination history notes extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
TextEntries	textEntry	notes	where textTypeID is 96

sql query

view: cs_taxonomicdeterminationnotes (refreshed 2012-04-10)

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

notes

Object Status

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	objectStatusID	Object Status (PAHMA schema)	FK to ObjectStatuses
StatusFlags	FlagID	Object Status (PAHMA schema)	FK to FlagLabels

sql query

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 s.ObjectStatus pahmaObjectStatus
FROM
 Objects o
 LEFT OUTER JOIN ObjectStatuses s ON o.objectStatusID = s.objectStatusID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.objectStatusID IS NOT NULL
UNION ALL
SELECT
 CAST(sf.objectID AS VARCHAR) objectID,
 fl.FlagLabel pahmaObjectStatus
FROM
 StatusFlags sf
 LEFT OUTER JOIN FlagLabels fl ON sf.FlagID = fl.FlagID
WHERE
   fl.FlagID IS NOT NULL
ORDER BY objectID