...
Table of Contents | ||
---|---|---|
|
Note: still under construction
Accessions extractAccessions 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)
Code Block | ||||
---|---|---|---|---|
| ||||
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 shortIdentifier | reference to Object |
|
Objects | ObjectNumber ObjectID acquisitionReferenceNumber | reference to Accession |
|
sql query
view: cs_accessionobjectrelationships (refreshed 2012-03-22)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT obj.objectID objectID, acc.objectID o.objectID acquisitionID, o.ObjectNumber acquisitionReferenceNumber FROM Objects o WHERE oacquisitionID 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 oacc.IsTemplate = 0 AND oacc.objectNumber LIKE 'Acc%' ACC%' AND obj.objectID = oa.ID2 AND oa.ID1 = acc.objectID ) ; |
notes
- objAccessions table also stores info on Accessions that are not in the Objects tablefirst 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)
Code Block | ||||
---|---|---|---|---|
| ||||
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)
Code Block | ||||
---|---|---|---|---|
| ||||
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 | notes |
---|---|---|---|
ObjAccession | AcquisitionTerms | acquisitionNote |
|
ObjAccession | Source | acquisitionNote |
|
ConXrefs | Remarks | acquisitionNote |
|
sql query
view: cs_acquisitionnotes (refreshed 2012-03-22)
Code Block | ||||
---|---|---|---|---|
| ||||
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...