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 |
|
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
- NOTE: the acquisitionNote_1 and acquisitionNote_2 fields in this query should be concatenated into a single field acquisitionNote, separated by line breaks; this cannot be done in this query because fields of type TEXT do not allow concatenation
- 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)
- 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...
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 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:
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
Acquisition Owners extact
field mapping
TMS table name |
TMS field name |
CSpace field name |
notes |
---|---|---|---|
ConXrefs |
ObjectID |
reference to Accession |
|
ConXrefs |
ConstituentID |
owner |
|
sql query
view:
SELECT o.objectID acquisitionID, x.constituentID owner 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 = 1 AND o.IsTemplate = 0 AND o.objectNumber LIKE 'ACC%' AND r.roleID IN (2,62) --r.ROLE IN ('donor', 'seller') ;