Object History and Association data mapping

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

Associated people (culture) extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjContext	Culture	assocPeople	should be mapped to Culture Authority
TextEntries	textEntry	assocPeopleNote	where textTypeID is 32, 49, 97, or 99
TextEntries	textType	assocPeopleType	where textTypeID is 49

sql query

view: cs_associatedpeoples (refreshed 2012-04-20)

SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.culture assocPeople,
 NULL assocPeopleType,
 NULL assocPeopleNote
FROM
 ObjContext c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.culture IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 NULL assocPeople,
 (case when t.textTypeID = 49 then 'nagpraCulturalAffiliation' end) assocPeopleType,
 t.TextEntry assocPeopleNote
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 (32, 49, 97, 99)
;

notes

TODO: map this data to new Culture Authority (to be populated from TMSThesaurus.Terms table)

Associated date extract

this data cannot be imported until CSPACE-4775 is done (v2.2)

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjDates	EventType	assocDateType
ObjDates	DateText	assocDate_dateDisplayDate
ObjDates	DateBegSearch	assocDate_dateEarliestSingleYear
ObjDates	DateEndSearch	assocDate_dateLatestYear
ObjDates	DayBegSearch	assocDate_dateEarliestSingleDay
ObjDates	DayEndSearch	assocDate_dateLatestDay
ObjDates	MonthBegSearch	assocDate_dateEarliestSingleMonth
ObjDates	MonthEndSearch	assocDate_dateLatestMonth
ObjDates	Remarks	assocDateNote
TextEntries	textEntry	assocDateNote	where textTypeID is 100

sql query

view: cs_associateddates (refreshed 2012-04-20)

SELECT
 CAST(d.objectID AS VARCHAR) objectID,
 (CASE WHEN d.eventType != '[not entered]' THEN lower(d.eventType) END) assocDateType,
 d.dateText assocDate_dateDisplayDate,
 NULL assocDate_dateNote,
 (CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) assocDate_dateEarliestSingleYear,
 (CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) assocDate_dateLatestYear,
 d.dayBegSearch assocDate_dateEarliestSingleDay,
 d.dayEndSearch assocDate_dateLatestDay,
 d.monthBegSearch assocDate_dateEarliestSingleMonth,
 d.monthEndSearch assocDate_dateLatestMonth,
 d.remarks assocDateNote
FROM
 ObjDates d
 JOIN Objects o ON d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) NOT IN ('accessioned', 'date collected', 'secondary collection date', 'tertiary collection date', 'date made')
 AND ((d.dateText IS NOT NULL AND d.dateText != '') OR d.dateBegSearch != 0)
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 NULL assocDateType,
 c.period assocDate_dateDisplayDate,
 NULL assocDate_dateNote,
 NULL assocDate_dateEarliestSingleYear,
 NULL assocDate_dateLatestYear,
 NULL assocDate_dateEarliestSingleDay,
 NULL assocDate_dateLatestDay,
 NULL assocDate_dateEarliestSingleMonth,
 NULL assocDate_dateLatestMonth,
 NULL assocDateNote
FROM
 ObjContext c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.period IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 NULL assocDateType,
 c.reign assocDate_dateDisplayDate,
 'produced during reign of' assocDate_dateNote,
 NULL assocDate_dateEarliestSingleYear,
 NULL assocDate_dateLatestYear,
 NULL assocDate_dateEarliestSingleDay,
 NULL assocDate_dateLatestDay,
 NULL assocDate_dateEarliestSingleMonth,
 NULL assocDate_dateLatestMonth,
 NULL assocDateNote
FROM
 ObjContext c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.reign IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 NULL assocDateType,
 NULL assocDate_dateDisplayDate,
 NULL assocDate_dateNote,
 NULL assocDate_dateEarliestSingleYear,
 NULL assocDate_dateLatestYear,
 NULL assocDate_dateEarliestSingleDay,
 NULL assocDate_dateLatestDay,
 NULL assocDate_dateEarliestSingleMonth,
 NULL assocDate_dateLatestMonth,
 t.TextEntry assocDateNote
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 = 100
;

notes

DONE: populate controlled list options for assocDateType field

Associated organization extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ConXrefs	constituentID	assocOrganization	reference to Organization
Roles	role	assocOrganizationType
ConXrefs	remarks	assocOrganizationNote

sql query

view: cs_associatedorganizations (refreshed 2012-03-12)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID assocOrganization,
 lower(r.role) assocOrganizationType,
 x.remarks assocOrganizationNote
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 ('donor', 'seller')
;

notes

Associated person extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ConXrefs	constituentID	assocPerson	reference to Person
Roles	role	assocPersonType
ConXrefs	remarks	assocPersonNote

sql query

view: cs_associatedpersons (refreshed 2012-03-12)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID assocPerson,
 lower(r.role) assocPersonType,
 x.remarks assocPersonNote
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 ('in memoriam', 'in-urned', 'informant', 'inspector', 'patron', 'photograph requestor', 'dealer', 'transferree', 'transferrer', 'donor', 'seller')
;

notes

Associated objects extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
TextEntries	textEntry	assocObject	where textTypeID is 104 or 106

sql query

view: cs_associatedobjects (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry assocObject
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 (104, 106)
;

notes

Associated place extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
TextEntries	textEntry	assocPlace	where textTypeID is 45

sql query

view: cs_associatedplaces (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry assocPlace,
 'used' assocPlaceType
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 = 45
;

notes

Previous owner extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ConXrefs	constituentID	previousOwner	reference to Person or Organization
ConXrefs	remarks	ownershipNote
TextEntries	textEntry	ownershipNote	where textTypeID is 93

sql query

view: cs_previousowners (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID previousOwner,
 x.remarks ownershipNote
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 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 lower(r.ROLE) IN ('original owner', 'previous owner', 'previous donor (to other museum)')
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 NULL previousOwner,
 t.TextEntry ownershipNote
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 = 93
;

notes

should Role field be imported? (it distinguishes "owners" from "previous donors")