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 |
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...