This page contains information about PAHMA's data migration of Object Collection Information.
See parent page on PAHMA data migration ETL work.
Field collection 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 |
DateText |
fieldCollectionDate_dateDisplayDate |
|
ObjDates |
DateBegSearch |
fieldCollectionDate_dateEarliestSingleYear |
|
ObjDates |
DateEndSearch |
fieldCollectionDate_dateLatestYear |
|
ObjDates |
DayBegSearch |
fieldCollectionDate_dateEarliestSingleDay |
|
ObjDates |
DayEndSearch |
fieldCollectionDate_dateLatestDay |
|
ObjDates |
MonthBegSearch |
fieldCollectionDate_dateEarliestSingleMonth |
|
ObjDates |
MonthEndSearch |
fieldCollectionDate_dateLatestMonth |
|
Objects |
Dated |
fieldCollectionDate_dateDisplayDate |
|
Objects |
DateBegin |
fieldCollectionDate_dateEarliestSingleYear |
|
Objects |
DateEnd |
fieldCollectionDate_dateLatestYear |
|
ConXrefs |
DisplayDate |
fieldCollectionDate_dateDisplayDate |
|
ConXrefs |
DateBegin |
fieldCollectionDate_dateEarliestSingleYear |
|
ConXrefs |
DateEnd |
fieldCollectionDate_dateLatestYear |
|
sql query
view: cs_fieldcollectiondates (refreshed 2012-03-12)
SELECT
CAST(o.objectID AS VARCHAR) objectID,
d.dateText fieldCollectionDate_dateDisplayDate,
(CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) fieldCollectionDate_dateEarliestSingleYear,
(CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) fieldCollectionDate_dateLatestYear,
d.dayBegSearch fieldCollectionDate_dateEarliestSingleDay,
d.dayEndSearch fieldCollectionDate_dateLatestDay,
d.monthBegSearch fieldCollectionDate_dateEarliestSingleMonth,
d.monthEndSearch fieldCollectionDate_dateLatestMonth
FROM
ObjDates d
JOIN Objects o on d.objectID = o.objectID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND lower(d.eventType) in ('date collected', 'secondary collection date', 'tertiary collection date')
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
o.dated fieldCollectionDate_dateDisplayDate,
(CASE WHEN o.dateBegin != '0' THEN o.dateBegin END) fieldCollectionDate_dateEarliestSingleYear,
(CASE WHEN o.dateEnd != '0' THEN o.dateEnd END) fieldCollectionDate_dateLatestYear,
null fieldCollectionDate_dateEarliestSingleDay,
null fieldCollectionDate_dateLatestDay,
null fieldCollectionDate_dateEarliestSingleMonth,
null fieldCollectionDate_dateLatestMonth
FROM
Objects o
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND o.dated IS NOT NULL OR o.dateBegin != '0' OR o.dateEnd != '0'
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
(CASE WHEN LEN(x.displayDate) > 0 THEN x.displayDate END) fieldCollectionDate_dateDisplayDate,
(CASE WHEN x.dateBegin != '0' THEN x.dateBegin END) fieldCollectionDate_dateEarliestSingleYear,
(CASE WHEN x.dateEnd != '0' THEN x.dateEnd END) fieldCollectionDate_dateLatestYear,
NULL fieldCollectionDate_dateEarliestSingleDay,
NULL fieldCollectionDate_dateLatestDay,
NULL fieldCollectionDate_dateEarliestSingleMonth,
NULL fieldCollectionDate_dateLatestMonth
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 ('collector', 'secondary collector', 'tertiary collector')
AND (x.dateBegin != 0 OR (x.displayDate IS NOT NULL AND len(x.displayDate) > 0))
;
notes
- differences in ObjDates.EventType ('date collected', 'secondary collection date', 'tertiary collection date') are not important and do not need to be migrated, per Michael (12/15/11)
- DONE: add new field pahmaFieldCollectionDate, to change fieldCollectionDate field into a repeating field
Field collection place extract
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
|---|---|---|---|
Objects |
MTB_PlaceTermID |
fieldCollectionPlace |
reference to Place Authority |
Objects |
Provenance |
fieldLocVerbatim |
|
SiteObjXrefs |
SiteID |
fieldCollectionPlace |
FK to Sites table to get MTB_PlaceTermID1; reference to Place Authority |
SiteObjXrefs |
Subsite1/Subsite2/Subsite3 |
fieldLocVerbatim |
|
ObjGeography |
MTB_PlaceTermID |
fieldCollectionPlace |
reference to Place Authority |
ThesXrefs |
TermID |
fieldCollectionPlace |
reference to Place Authority |
sql query
view: cs_fieldcollectionplaces (refreshed 2012-03-12)
SELECT
CAST(o.objectID AS VARCHAR) objectID,
o.MTB_PlaceTermID fieldCollectionPlace,
o.provenance fieldLocVerbatim
FROM
Objects o
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND (o.MTB_PlaceTermID IS NOT NULL OR o.provenance IS NOT NULL)
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
s.MTB_PlaceTermID1 fieldCollectionPlace,
COALESCE(x.subsite1, '') + (CASE WHEN x.subsite1 IS NOT NULL AND x.subsite2 IS NOT NULL THEN ', ' ELSE '' END) + COALESCE(x.subsite2, '') + (CASE WHEN x.subsite3 IS NOT NULL THEN ', ' + x.subsite3 ELSE '' END) fieldLocVerbatim
FROM
SiteObjXrefs x
JOIN Objects o ON x.objectID = o.objectID
JOIN Sites s ON x.siteID = s.siteID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND s.MTB_PlaceTermID1 IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
g.MTB_PlaceTermID fieldCollectionPlace,
NULL fieldLocVerbatim
FROM
ObjGeography g
JOIN Objects o ON g.objectID = o.objectID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND g.MTB_PlaceTermID IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
x.termID fieldCollectionPlace,
NULL fieldLocVerbatim
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 ('archaeological site', 'card header', 'provenience')
;
notes
- NOTE: fieldCollectionPlace values should be used to construct refName for PlaceAuthority; we may need a mapping table in order to do this?
Field collector extract
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
|---|---|---|---|
ConXrefs |
constituentID |
fieldCollector |
reference to Person or Organization |
sql query
view: cs_fieldcollectors (refreshed 2012-03-12)
SELECT
CAST(o.objectID AS VARCHAR) objectID,
x.constituentID fieldCollector
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 ('collector', 'secondary collector', 'tertiary collector')
;
notes
Field collection note extract
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
|---|---|---|---|
ObjDates |
Remarks |
fieldCollectionNote |
pertains to fieldCollectionDate |
SiteObjXrefs |
Remarks |
fieldCollectionNote |
pertains to fieldCollectionPlace |
ThesXrefs |
Remarks |
fieldCollectionNote |
pertains to fieldCollectionPlace |
ConXrefs |
Remarks |
fieldCollectionNote |
pertains to fieldCollector |
TextEntries |
textEntry |
fieldCollectionNote |
where textTypeID is 1, 8, 14, 58, 90, or 102 |
sql query
view: cs_fieldcollectionnotes (refreshed 2012-03-13) (NEEDS UPDATE)
SELECT
CAST(o.objectID AS VARCHAR) objectID,
'Field collection date: 'fieldCollectionNote_prefix,
d.remarks fieldCollectionNote
FROM
ObjDates d
JOIN Objects o on d.objectID = o.objectID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND lower(d.eventType) IN ('date collected', 'secondary collection date', 'tertiary collection date')
AND d.remarks IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
'Field collection place: 'fieldCollectionNote_prefix,
x.remarks fieldCollectionNote
FROM
SiteObjXrefs x
JOIN Objects o ON x.objectID = o.objectID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND x.remarks IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
'Field collection place: 'fieldCollectionNote_prefix,
x.remarks fieldCollectionNote
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 ('archaeological site', 'card header', 'provenience')
AND x.remarks IS NOT NULL AND x.remarks != ''
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
'Field collector: 'fieldCollectionNote_prefix,
x.remarks fieldCollectionNote
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 ('collector', 'secondary collector', 'tertiary collector')
AND x.remarks IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
(CASE WHEN t.textTypeID = 8 THEN 'NAGPRA site concordance note: ' WHEN t.textTypeID IN (14, 58, 102) THEN tt.textType + ': ' ELSE '' END) fieldCollectionNote_prefix,
t.textEntry fieldCollectionNote
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 (8, 14, 58, 90, 102)
;
notes
- NOTE: 'fieldCollectionNote_prefix' should be prepended to 'fieldCollectionNote' 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 fieldCollectionNote, separated by line breaks