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 |
MTB_PlaceTermID |
fieldCollectionPlace |
reference to Place Authority |
Objects |
Notes |
comments |
|
Objects |
ObjectStatusID |
pahmaObjectStatus |
FK to ObjectStatuses; NEW field (controlled list) |
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 |
Provenance |
fieldLocVerbatim |
|
Objects |
MTB_ResponsibleDept |
responsibleDepartment |
controlled list |
Objects |
Signed |
inscriptionContent |
FK to MTB_NagpraBurialCodes |
Objects |
State |
pahmaNagpraCodeLegacy |
controlled list |
ObjContext |
Integer3 |
inventoryCount |
NEW field |
sql query
view: cs_objects
SELECT CAST(o.objectID AS VARCHAR) objectID, o.ObjectNumber objectNumber, 'no' isComponent, CatRais pahmaCollection, d.Department pahmaTmsLegacyDepartment, HistAttributions objectHistoryNote, (CASE WHEN lower(LoanClass) not in ('', 'catalog card', 'inventory', 'ledger') THEN LoanClass END) pahmaTms2003DataSource, Markings distinguishingFeatures, Medium material, MTB_PlaceTermID fieldCollectionPlace, Notes comments, s.ObjectStatus pahmaObjectStatus, t.ObjectType collection, i.CSpaceID nagpraInventoryName, MTB_Portfolio portfolioSeries, Provenance fieldLocVerbatim, (CASE WHEN MTB_ResponsibleDept = 1 THEN 'Natasha Johnson' WHEN MTB_ResponsibleDept = 2 THEN 'Leslie Freund' WHEN MTB_ResponsibleDept = 3 THEN 'Victoria Bradshaw' WHEN MTB_ResponsibleDept = 4 THEN 'Alicja Egbert' WHEN MTB_ResponsibleDept = 5 THEN 'No collection manager (Registration)' WHEN MTB_ResponsibleDept = 8 THEN 'uncertain' END) responsibleDepartment, Signed inscriptionContent, 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 fieldCollectionPlace, NULL comments, NULL pahmaObjectStatus, NULL collection, NULL nagpraInventoryName, NULL portfolioSeries, NULL fieldLocVerbatim, NULL responsibleDepartment, NULL inscriptionContent, 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
Object-Object Relationships extract
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
---|---|---|---|
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
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
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
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
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 l.MTB_ISOcode 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 |
|
sql query
view: cs_briefdescriptions
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 ;
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
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 l.MTB_ISOcode 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
Person depicted extract
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
---|---|---|---|
ConXrefs |
constituentID |
contentPerson |
reference to Person |
sql query
view:
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') ;