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-11)

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
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,
  null owner
FROM
 Objects o
WHERE
   o.IsTemplate = 0 and o.IsVirtual=1 and o.DepartmentID = 34
 ;

notes

"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)