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 |
notes |
---|---|---|---|
Objects |
ObjectNumber |
acquisitionReferenceNumber |
|
ObjAccession |
AccessionMethodID |
acquisitionMethod |
FK to AccessionMethods table |
ObjAccession |
AcqJustification |
acquisitionReason |
|
ObjAccession |
AcquisitionTerms |
acquisitionNote |
|
ObjAccession |
Source |
acquisitionNote |
|
sql query
view:
SELECT acc.objectID acquisitionID, acc.objectNumber acquisitionReferenceNumber, (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
- 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)
- 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 |
reference to Object |
|
Objects |
ObjectID |
reference to 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...