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.

Accessions extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	ObjectNumber	acquisitionReferenceNumber
ObjAccession	AccessionMethodID	acquisitionMethod	FK to AccessionMethods table
ObjAccession	AcqJustification	acquisitionReason

sql query

view: cs_accessions (refreshed 2012-03-22)

SELECT
 acc.objectID acquisitionID,
 acc.objectNumber acquisitionReferenceNumber,
 (CASE WHEN acc2.AccessionMethodID != 0 THEN m.AccessionMethod ELSE 'unknown' END) acquisitionMethod,
-- acc2.AccessionValue,
 acc2.AcqJustification acquisitionReason
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

ObjAccession.AcquisitionLot duplicates data in AcquisitionNumber, and so does not need to be imported
ObjAccession.AccessionValue has data for only a few (<60) records, and we aren't sure where to put it; this data can be updated manually later, per Michael (1/19/12)

Accessions-Objects Relationships extact

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	ObjectID	reference to Object
Objects	ObjectID	reference to Accession

sql query

view: cs_accessionobjectrelationships (refreshed 2012-03-22)

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
UNION ALL
SELECT
 obj.objectID objectID,
 acc.objectID acquisitionID
FROM
 Objects obj,
 ObjAccession 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.objectID
 AND (CASE WHEN oa.acquisitionNumber NOT LIKE 'ACC.%' THEN 'ACC.' END) + REPLACE(oa.acquisitionNumber, ' ', '') = acc.objectNumber
 AND obj.objectID NOT IN
  (SELECT
    obj.objectID
  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

first half of the query includes objects linked to accessions via the Associations table
second half of the query includes objects that are not linked to any accession record via the Associations table, but do have an accession number in the ObjAccession table which references (after removing spaces and prepending 'ACC.') an existing accession record
objects that have an accession number in the ObjAccession table which does not reference an existing accession record, or which references an accession record that is different than one the object is linked to via the Associations table, are not imported

Acquisition Dates extact

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	ObjectID	reference to Accession
ObjAccession	AccessionISODate	acquisitionDate	repeating field

sql query

view: cs_acquisitiondates (refreshed 2012-03-20)

SELECT
 acc.objectID acquisitionID,
 oa.AccessionISODate acquisitionDate
FROM
 Objects obj,
 Associations oaa,
 Objects acc,
 ObjAccession oa
WHERE
 obj.IsVirtual = 0 AND obj.IsTemplate = 0
 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND obj.objectID = oaa.ID2
 AND oaa.ID1 = acc.objectID
 AND obj.objectID = oa.objectID
 AND len(oa.AccessionISODate) > 0
UNION
SELECT
 acc.objectID acquisitionID,
 oa.AccessionISODate acquisitionDate
FROM
 Objects obj,
 ObjAccession 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.objectID
 AND (CASE WHEN oa.acquisitionNumber NOT LIKE 'ACC.%' THEN 'ACC.' END) + REPLACE(oa.acquisitionNumber, ' ', '') = acc.objectNumber
 AND obj.objectID NOT IN
  (SELECT
    obj.objectID
  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
  )
 AND len(oa.AccessionISODate) > 0
;

notes

the join structure of this query mirrors the structure of the Accessions-Objects Relationships query above

Owner extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ConXrefs	ObjectID	reference to Accession
ConXrefs	ConstituentID	owner

sql query

view: cs_previousowners2 (refreshed 2012-03-22)

SELECT
 acc.objectID acquisitionID,
 x.constituentID owner
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects acc ON x.ID = acc.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND lower(r.role) IN ('donor', 'seller')
;

notes

Acquisition Note extract

field mapping

TMS table name	TMS field name	CSpace field name
ObjAccession	AcquisitionTerms	acquisitionNote
ObjAccession	Source	acquisitionNote
ConXrefs	Remarks	acquisitionNote

sql query

view: cs_acquisitionnotes (refreshed 2012-03-22)

SELECT
 acc.objectID acquisitionID,
 acc2.AcquisitionTerms acquisitionNote
FROM
 Objects acc,
 ObjAccession acc2
WHERE
 acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND acc.objectID = acc2.objectID
 AND acc2.AcquisitionTerms IS NOT NULL
UNION ALL
SELECT
 acc.objectID acquisitionID,
 acc2.Source acquisitionNote
FROM
 Objects acc,
 ObjAccession acc2
WHERE
 acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND acc.objectID = acc2.objectID
 AND acc2.Source IS NOT NULL
UNION ALL
SELECT
 acc.objectID acquisitionID,
 'TMS Donor: ' + x.remarks acquisitionNote
FROM
 ConXrefs x
 JOIN Roles r ON x.roleID = r.roleID
 JOIN Objects acc ON x.ID = acc.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND lower(r.role) IN ('donor', 'seller')
 AND x.remarks IS NOT NULL
;

notes

NOTE: multiple acquisitionNote values per Accession record should be concatenated into a single field, separated by line breaks
ObjAccession.Source also contains data for some records that are linked to objects rather than accessions, and that data is not included here; whether it needs to be imported at all is an open question...