...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
Objects | ObjectID | pahmaObjectID |
|
ObjComponents | ComponentID | pahmaObjectID | must ensure no duplicates with Objects.ObjectID |
Objects | ObjectNumber | objectNumber |
|
ObjComponents | ComponentNumber | objectNumber |
|
Objects | 'no' | isComponent |
|
ObjComponents | 'yes' | isComponent |
|
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
CAST(o.objectID AS VARCHAR) objectID,
o.ObjectNumber objectNumber,
'no' 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,
i.CSpaceID nagpraInventoryName,
MTB_Portfolio portfolioSeries,
Provenance fieldLocVerbatim,
(CASE WHEN MTB_ResponsibleDept = 1 THEN 'Natasha Johnson'
WHEN MTB_ResponsibleDept = 2 THEN 'Leslie Freund'
WHEN MTB_ResponsibleDept = 3 THEN 'Victoria Bradshaw'
WHEN MTB_ResponsibleDept = 4 THEN 'Alicja Egbert'
WHEN MTB_ResponsibleDept = 5 THEN 'No collection manager (Registration)'
WHEN MTB_ResponsibleDept = 8 THEN 'uncertain' END) 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,
'yes' 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)
;
|
...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
Objects | Title | title |
|
ObjTitles | Title | title |
|
ObjTitles | LanguageID | titleLanguage | FK to Languages |
ObjTitles | TitleTypeID | titleType | FK to TitleTypes |
...
view: cs_objecttitles
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
CAST(o.objectID AS VARCHAR) objectID,
Title title,
NULL titleLanguage,
NULL titleType
FROM
Objects o
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND o.title IS NOT NULL
UNION ALL
SELECT
CAST(n.objectID AS VARCHAR) objectID,
n.title,
(CASE WHEN n.languageID != '0' THEN l.MTB_ISOcode END) titleLanguage,
(CASE WHEN n.titleTypeID != '0' THEN t.titleType END) titleType
FROM
objTitles n
LEFT OUTER JOIN titleTypes t ON n.titleTypeID = t.titleTypeID
LEFT OUTER JOIN languages l ON n.languageID = l.languageID
;
|
...