Object Reference data mapping

This page contains information about PAHMA's data migration of Object Reference information.
See parent page on PAHMA data migration ETL work.

Bibliographic References extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
RefObjXrefs	ReferenceID	reference	reference to Citation Authority
RefObjXrefs	Illustrated/PageNumber/Remarks	referenceNote
Objects	Bibliography	referenceNote
Objects	PubReferences	referenceNote
TextEntries	textEntry	referenceNote	where textTypeID is 101

sql query

view: cs_bibliographicreferences (refreshed 2012-04-20)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.referenceID reference,
 (CASE WHEN x.illustrated = 1 THEN 'Illustrated' ELSE '' END) + (CASE WHEN x.pageNumber IS NOT NULL THEN (CASE WHEN x.illustrated = 1 THEN '; ' ELSE '' END) + 'Page number ' + x.pageNumber ELSE '' END) + (CASE WHEN x.remarks IS NOT NULL THEN (CASE WHEN x.illustrated = 1 OR x.pageNumber IS NOT NULL THEN '; ' ELSE '' END) + x.remarks ELSE '' END) referenceNote
FROM
 RefObjXrefs x
 JOIN Objects o ON x.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 NULL reference,
 o.bibliography referenceNote
FROM
 Objects o
WHERE
 o.bibliography is not null
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 NULL reference,
 o.PubReferences referenceNote
FROM
 Objects o
WHERE
 o.PubReferences is not null
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 NULL reference,
 t.TextEntry referenceNote
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 = 101
;

notes

NOTE: this data should probably not be imported until the Citation Authority is available and data has been loaded into it?
TODO: use reference to construct refName for Citation Authority