PAHMA Accession data mapping

This page contains information about PAHMA's data migration of Accessions and related info.
See parent page on PAHMA data migration ETL work.

Note: still under construction

Accessions extract

field mapping

TMS table name	TMS field name	CSpace field name
Objects	ObjectID	shortIdentifier
ObjAccession	??	shortIdentifier
Objects	ObjectNumber	acquisitionReferenceNumber
ObjAccession	acquisitionNumber	acquisitionReferenceNumber

sql query

view:

SELECT 
 acc.objectID acquisitionID,
 acc.objectNumber acquisitionReferenceNumber
FROM
 Objects acc
WHERE
 acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
UNION
SELECT DISTINCT
 NULL acquisitionID,
 upper((CASE WHEN oa.acquisitionNumber NOT LIKE 'acc.%' THEN 'acc.' END) + REPLACE(oa.acquisitionNumber, ' ', '')) acquisitionReferenceNumber
FROM
 ObjAccession oa
WHERE 
 oa.acquisitionNumber IS NOT NULL AND len(oa.acquisitionNumber) > 0 
 AND upper((CASE WHEN oa.acquisitionNumber NOT LIKE 'acc.%' THEN 'acc.' END) + REPLACE(oa.acquisitionNumber, ' ', '')) NOT IN
  (SELECT acc.objectNumber
  FROM Objects acc
  WHERE acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
  )
ORDER BY acquisitionReferenceNumber 
;

notes

objAccessions table stores info on some Accession numbers that are not in the Objects table
TODO: decide on shortIdentifier for accessions created from ObjAccession.acquisitionNumber...

Accessions detail extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjAccession	AccessionMethodID	acquisitionMethod	FK to AccessionMethods table
ObjAccession	AcqJustification	acquisitionReason
ObjAccession	AcquisitionTerms	acquisitionNote
ObjAccession	Source	acquisitionNote

sql query

view:

SELECT 
 acc.objectID acquisitionID,
 (CASE WHEN acc2.AccessionMethodID != 0 THEN m.AccessionMethod ELSE 'unknown' END) acquisitionMethod,
-- acc2.AccessionValue,
 acc2.AcqJustification acquisitionReason,
 acc2.AcquisitionTerms acquisitionNote_1,
 acc2.Source acquisitionNote_2
FROM
 Objects acc,
 ObjAccession acc2, 
 AccessionMethods m
WHERE
 acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND acc.objectID = acc2.objectID
 AND acc2.AccessionMethodID = m.AccessionMethodID
;

notes

this query is separate from the primary Accessions one above because it includes fields of type TEXT that cannot be included in a union distinct query
AccessionValue field can be imported manually later, because only a few records have values and we aren't sure where to put it, per Michael (1/19/12)
the acquisitionNote_1 and acquisitionNote_2 fields in this query should be concatenated into a single field acquisitionNote, but that cannot be done in this query because fields of type TEXT do not allow concatenation
TODO: include AccessionISODate field (here or in a separate query?)...

Accessions-Objects Relationships extact

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	ObjectID	shortIdentifier for Object
Objects	ObjectID	shortIdentifier for Accession

sql query

view:

SELECT 
 obj.objectID objectID,
 acc.objectID acquisitionID
FROM
 Objects obj, 
 Associations oa,
 Objects acc
WHERE
 obj.IsVirtual = 0 AND obj.IsTemplate = 0 
 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND obj.objectID = oa.ID2 
 AND oa.ID1 = acc.objectID 
;

notes

TODO: include relationships for accessions created from ObjAccession.acquisitionNumber...