e
| Info |
|---|
This page contains information about PAHMA's data migration of CollectionObjects and related info. |
...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
Objects | ObjectID | shortIdentifier |
|
Components | ComponentID | shortIdentifier | must ensure no duplicates with Objects.ObjectID |
Objects | ObjectNumber | objectNumber |
|
Components | ComponentNumber | objectNumber |
|
Objects | CatRais | pahmaCollection | NEW field (repeating; controlled list) |
Objects | DepartmentID | pahmaTmsLegacyDepartment | FK to Departments; NEW field (controlled list) |
Objects | HistAttributions | objectHistoryNote |
|
Objects | LoanClass | pahmaTms2003DataSource | NEW field (controlled list) |
Objects | Markings | distinguishingFeatures |
|
Objects | Medium | material |
|
Objects | MTB_PlaceTermID | fieldCollectionPlace | reference to Place Authority |
Objects | Notes | comments |
|
Objects | ObjectStatusID | pahmaObjectStatus | FK to ObjectStatuses; NEW field (controlled list) |
Objects | ObjectTypeID | collection | FK to ObjectTypes |
Objects | PaperFileRef | nagpraInventoryName | FK to MTB_NagpraInvNames; controlled list |
Objects | Portfolio | portfolioSeries | FK to MTB_PortfolioSeries; NEW field (controlled list) |
Objects | Provenance | fieldLocVerbatim |
|
Objects | MTB_ResponsibleDept | responsibleDepartment | controlled list |
Objects | Signed | inscriptionContent | FK to MTB_NagpraBurialCodes |
Objects | State | pahmaNagpraCodeLegacy | controlled list |
Objects | Title | title |
|
ObjContext | Integer3 | inventoryCount | NEW field |
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
o.objectID objectID,
o.ObjectNumber objectNumber,
NULL isComponent,
CatRais pahmaCollection,
d.Department pahmaTmsLegacyDepartment,
HistAttributions objectHistoryNote,
(CASE WHEN lower(LoanClass) not in ('', 'catalog card', 'inventory', 'ledger') THEN LoanClass END) pahmaTms2003DataSource,
Markings distinguishingFeatures,
Medium material,
MTB_PlaceTermID fieldCollectionPlace,
Notes comments,
s.ObjectStatus pahmaObjectStatus,
t.ObjectType collection,
PaperFileRefi.CSpaceID nagpraInventoryName,
MTB_Portfolio portfolioSeries,
Provenance fieldLocVerbatim,
MTB_ResponsibleDept responsibleDepartment,
Signed inscriptionContent,
b.optionID pahmaNagpraCodeLegacy,
Title title,
c.integer3 inventoryCount
FROM
Objects o
LEFT OUTER JOIN Departments d ON o.departmentID = d.departmentID
LEFT OUTER JOIN ObjectStatuses s ON o.objectStatusID = s.objectStatusID
LEFT OUTER JOIN ObjectTypes t ON o.objectTypeID = t.objectTypeID
LEFT OUTER JOIN MTB_NagpraInvNames i ON o.paperFileRef = i.LongTMSname
LEFT OUTER JOIN MTB_NagpraBurialCodes b ON o.state = b.code
LEFT OUTER JOIN ObjContext c ON o.objectID = c.objectID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
UNION ALL
SELECT
'c' + CAST(c.componentID AS VARCHAR) objectID,
c.componentNumber objectNumber,
'Component' isComponent,
NULL pahmaCollection,
NULL pahmaTmsLegacyDepartment,
NULL objectHistoryNote,
NULL pahmaTms2003DataSource,
NULL distinguishingFeatures,
NULL material,
NULL fieldCollectionPlace,
NULL comments,
NULL pahmaObjectStatus,
NULL collection,
NULL nagpraInventoryName,
NULL portfolioSeries,
NULL fieldLocVerbatim,
NULL responsibleDepartment,
NULL inscriptionContent,
NULL pahmaNagpraCodeLegacy,
NULL title,
NULL inventoryCount
FROM
ObjComponents c
WHERE
c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;
|
...
- object components are included as first-class objects if and only if there are multiple components for parent object
- object components must have componentID (objectID) that does not conflict with any parent objectID: we can prepend "c" to the componentID to ensure this, per Michael (1/12/12)
- many object components have a non-unique componentNumber (objectNumber), duplicated by other components and/or parent object; these values are OK to import as-is, per Michael (1/12/12)
- "componentType" field does not need to be imported, per Michael (1/12/12)
- "inscribed" field has only a few entries, which can be updated manually after data migration, per Michael (12/15/11)
- DONE: add 6 new custom fields: pahmaCollection, objectStatus, pahmaTms2003DataSource, pahmaTmsLegacyDepartment, inventoryCount, portfolioSeries
- DONE: add controlled list options for 5 of these new fields (all except inventoryCount)
- TODODONE: nagpraInventoryName field values need to be transformed to match option ID values in existing controlled list (Michael to provide mapping)
- TODO: "dimensions" field still needs to be dealt with (John Lowe is working on this as of 1/9/12)
- TODO: add isComponent field for indicating components; field should be hidden in main part of UI but included in display name at the top
- TODO: update options for responsibleDepartment field
...