Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

...

Table of Contents
maxlevel3

Note: still under construction

Accessions extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

Objects

ObjectID ObjectNumber

shortIdentifier acquisitionReferenceNumber

 

ObjAccession

?? AccessionMethodID

shortIdentifier

 

Objects

ObjectNumber

acquisitionReferenceNumber

 

ObjAccession

acquisitionNumber

acquisitionReferenceNumber acquisitionMethod

FK to AccessionMethods table

ObjAccession

AcqJustification

acquisitionReason

 

sql query

view:  cs_accessions (refreshed 2012-03-22)

Code Block
sql
sql
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%'
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(
 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)

Code Block
sql
sql

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 'accACC.%' THEN 'accACC.' END) + REPLACE(oa.acquisitionNumber, ' ', '')) = acc.objectNumber
 AND obj.objectID NOT IN
  (SELECT
    accobj.objectNumberobjectID
  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  )
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...

...

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

ObjAccession Objects

AccessionMethodID ObjectID

acquisitionMethod

FK to AccessionMethods table

ObjAccession

AcqJustification

acquisitionReason reference to Accession

 

ObjAccession

AcquisitionTerms AccessionISODate

acquisitionNote

 

ObjAccession

Source

acquisitionNote

 

acquisitionDate

repeating field

sql query

view:  cs_acquisitiondates (refreshed 2012-03-20)

Code Block
sql
sql
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 = (CASE1 WHENAND acc2acc.AccessionMethodIDIsTemplate != 0 THENAND macc.AccessionMethodobjectNumber ELSELIKE '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%'
 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 accobj.objectID = acc2.objectIDoa.ID2
    AND acc2oa.AccessionMethodIDID1 = m.AccessionMethodID acc.objectID
  )
 AND len(oa.AccessionISODate) > 0
;

notes

  • the join structure of 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?)...

...

  • 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
sql
sql

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

shortIdentifier for Object

TMS table name

TMS field name

CSpace field name

notes

Objects

ObjectID

ObjAccession

AcquisitionTerms

acquisitionNote

 

ObjAccession

Source

acquisitionNote

 

Objects ConXrefs

ObjectID Remarks

shortIdentifier for Accession acquisitionNote

 

sql query

view:  cs_acquisitionnotes (refreshed 2012-03-22)

Code Block
sql
sql
SELECT
 acc.objectID acquisitionID,
 acc2.AcquisitionTerms acquisitionNote
FROM
 Objects acc,
 ObjAccession acc2

 objWHERE
 acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND acc.objectID objectID,= acc2.objectID
 AND acc2.AcquisitionTerms IS NOT NULL
UNION ALL
SELECT
 acc.objectID acquisitionID,
 acc2.Source acquisitionNote
FROM
 Objects objacc,
 ObjAccession acc2

 Associations oa,
 Objects acc
WHERE
 obj.IsVirtual = 0 AND obj.IsTemplate = 0 
 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 obj.objectID = oa.ID2 lower(r.role) IN ('donor', 'seller')
 AND oax.ID1remarks =IS acc.objectIDNOT NULL
;

notes

  • TODO: include relationships for accessions created from ObjAccession.acquisitionNumberNOTE: 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...