...
Table of Contents | ||
---|---|---|
|
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)
Code Block | ||||
---|---|---|---|---|
| ||||
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)
Code Block | ||||
---|---|---|---|---|
| ||||
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)
Code Block | ||||
---|---|---|---|---|
| ||||
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-05-14)
Code Block | ||||
---|---|---|---|---|
| ||||
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