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