PAHMA Groups data mapping

This page contains information about PAHMA's data migration of Groups and related info.
See parent page on PAHMA data migration ETL work.

Groups extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ObjectPackages

name

title

 

ObjectPackages

notes

scopeNote

 

ObjectPackages

owner

owner

FK to Users; reference to Person Authority

Objects

objectNumber

title

for Burial Lot

sql query

view: cs_groups  (refreshed 2012-05-24)

SELECT
 CAST(p.objectPackageID AS VARCHAR) groupID,
 p.name title,
 p.notes scopeNote,
 (CASE WHEN u.MTB_ConstituentID = -1 THEN NULL ELSE u.MTB_ConstituentID END) owner,
 p.entereddate dateCreated
FROM
 ObjectPackages p
 LEFT OUTER JOIN Users u ON p.owner = u.login
UNION ALL
SELECT
  'bur' + CAST(o.objectID AS VARCHAR) groupID,
  'Burial lot ' + o.objectNumber title,
  null scopeNote,
  7252 owner,
  o.entereddate dateCreated
FROM
 Objects o
WHERE
   o.IsTemplate = 0 and o.IsVirtual=1 and o.DepartmentID = 34
 ;

notes

  • 7252 is the ConstituentID of "PAHMA-NAGPRA staff"
  • "global" field does not need to be imported, per Michael (1/12/12)
  • Capture burial lots as groups (Object table, isTemplate=0, isVirtual=1 & DepartmentID=34) per Michael's email (5/10/12)

Object-Group Relationship extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ObjPkgList

ObjectID

reference to Object

 

ObjPkgList

ObjectPackageID

reference to Group

 

Objects

objectID

reference to Object

via "Associations" table for Burial Lot

Objects

objectID

reference to Group

Burial Lot

sql query

view: cs_objectgrouprelationships  (refreshed 2012-05-11)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 CAST(o.objectPackageID AS VARCHAR) groupID
FROM
 objPkgList o
UNION ALL
SELECT
 CAST(obj.objectID AS VARCHAR) objectID,
 'bur' + CAST(bl.objectID AS VARCHAR) groupID
FROM
 Objects obj,
 Associations oa,
 Objects bl
WHERE
 obj.IsVirtual = 0 AND obj.IsTemplate = 0
 AND bl.IsVirtual = 1 AND bl.IsTemplate = 0 AND bl.DepartmentID = 34
 AND obj.objectID = oa.ID2
 AND oa.ID1 = bl.objectID ;

notes

  • TODO: handle ObjPkgList.Notes field; we need a place to put this info, either in Groups or Objects schema
  • Capture object-burial lots group relationship via associations, per Michael's email (5/10/12)