Page Comparison

e

Collection Objects extract

...

sql query

view: cs_objects (refreshed 2012-04-10)

SELECT
 CAST(o.objectID objectIDAS VARCHAR) objectID,
 o.ObjectNumber objectNumber,
 NULL'no' isComponent,
 o.CatRais pahmaCollection,
 d.Department pahmaTmsLegacyDepartment,
 o.HistAttributions objectHistoryNote,
 (CASE WHEN lower(o.LoanClass) not in ('', 'catalog card', 'inventory', 'ledger') THEN o.LoanClass END) pahmaTms2003DataSource,
 o.Markings distinguishingFeatures,
 o.Medium material,
 MTB_PlaceTermID fieldCollectionPlace,
 Notes comments,
 s.ObjectStatus pahmaObjectStatus,
 t.ObjectType collection,
 PaperFileRefi.CSpaceID nagpraInventoryName,
 o.MTB_Portfolio portfolioSeries,
 Provenance(CASE fieldLocVerbatim,WHEN  o.MTB_ResponsibleDept responsibleDepartment,= 1 THEN Signed inscriptionContent,'Natasha Johnson'
 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_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)
;

notes

• 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)
• TODO: 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

Object-Object Relationships extract

sql query

view:

...

SELECT
 c.objectID parentObjectID,
  'c' + CAST(c.componentID AS VARCHAR) childObjectID
FROM
  ObjComponents c
WHERE
  c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

notes

• this should be imported as two-way relationship, so it shows up in UI for both parent and child object

Number of Objects extract

field mapping

sql query

view:

SELECT
 o.objectID,
 o.objectCount numberOfObjects
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.objectID NOTWHEN o.MTB_ResponsibleDept = 2 THEN 'Leslie Freund'
       WHEN o.MTB_ResponsibleDept = 3 THEN 'Victoria Bradshaw'
       WHEN o.MTB_ResponsibleDept = 4 THEN 'Alicja Egbert'
       WHEN o.MTB_ResponsibleDept = 5 THEN 'No collection manager (Registration)'
       WHEN o.MTB_ResponsibleDept = 8 THEN 'uncertain' END) responsibleDepartment,
 b.optionID pahmaNagpraCodeLegacy,
 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 pahmaObjectStatus,
 NULL collection,
 NULL nagpraInventoryName,
 NULL portfolioSeries,
 NULL responsibleDepartment,
 NULL pahmaNagpraCodeLegacy,
 NULL inventoryCount
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

notes

• object components are included as first-class objects if and only if there are multiple components for parent object
• object components should 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)
• "dimensions" field will be handled by a separate extract (John Lowe is working on this as of 1/9/12)
• 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)
• DONE: nagpraInventoryName field values need to be transformed to match option ID values in existing controlled list
• DONE: add isComponent field for indicating components
• June 12, 2012: Inventory Count now coming from two fields.
• June 12, 2012: Object Status now a repeating field so not using pahmaObjectStatus in this view

Object-Object Relationships extract

field mapping

sql query

view: cs_objectobjectrelationships  (refreshed 2012-04-10)

SELECT
 CAST(c.objectID AS VARCHAR) objectID_1,
  'c' + CAST(c.componentID AS VARCHAR) objectID_2,
  'Object-Component' relationshipType
FROM
  ObjComponents c
WHERE
  c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) => 1)
UNION ALL
SELECT
 o.objectIDCAST(oa.ID1 AS VARCHAR) objectID_1,
 CAST(CASE WHEN c.compCount > o.objectCount THEN c.compCount ELSE o.objectCount END) numberOfObjectsoa.ID2 AS VARCHAR) objectID_2,
 r.relation1 + '-' + r.relation2 relationshipType
FROM
 ObjectsAssociations ooa,
 JOINObjects obj1,
ObjComponents cObjects ONobj2,
o.objectID =Relationships c.objectIDr
WHERE
 ooa.IsVirtualtableID = 0108
 AND ooa.IsTemplateID1 = 0obj1.objectID
 AND coa.objectIDID2 IN (SELECT t= obj2.objectID
 FROMAND ObjComponentsobj1.IsVirtual t= GROUP0 BYAND t.objectID HAVING count(*)obj1.IsTemplate = 1)0
UNION ALLAND SELECTobj2.IsVirtual = 'c'0 +AND CAST(cobj2.componentIDIsTemplate AS= VARCHAR)0
objectID, AND coa.compCountrelationshipID numberOfObjects
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
; = r.relationshipID
;

notes

• for objects with single component, componentCount should be used in lieu of objectCount if and only if it is greater than objectCount, per Michael (1/5/12)

...

• this should be imported as two-way relationship, so it shows up in UI for both objects
• relationship type is included in this query, but we don't have a good place to put it in CSpace

Number of Objects extract

field mapping

sql query

...

sql query

view: cs_numberofobjects (refreshed 2012-04-10)

SELECT
  n.ID objectID,
  n.altnum pahmaAltNumCAST(o.objectID AS VARCHAR) objectID,
  no.descriptionobjectCount pahmaAltNumType,
  n.remarks pahmaAltNumNote
numberOfObjects
FROM
 Objects AltNumso
n
WHERE
  no.tableIDIsVirtual = 1080 UNION ALL
SELECT
 c.objectID,
 c.componentNumber pahmaAltNum,
 NULL pahmaAltNumType,
 NULL pahmaAltNumNote
FROM
 ObjComponents c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 c.objectID AND o.IsTemplate = 0
 AND o.objectID NOT IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
UNION ANDALL
c.componentNumberSELECT
IS NOT NULL
 AND CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN c.compCount > o.objectCount THEN c.componentNumbercompCount !=ELSE o.objectNumber
;

notes

• DONE: 3 new custom fields in repeating field group
• DONE: add controlled list options for pahmaAltNumType field

Object Names extract

field mapping

sql query

view: cs_objectnames

SELECT
 o.objectID,
 o.objectName,
 NULL objectNameLanguage,
 NULL objectNameNote
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0objectCount END) numberOfObjects
FROM
 Objects o
 JOIN  ObjComponents c ON o.objectID = c.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.componentName objectName,
 NULL objectNameLanguage,
 NULL objectNameNote
compCount numberOfObjects
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.componentName IS NOT NULL
UNION ALL
SELECT 
 c.objectID,
 c.componentName objectName,
 NULL objectNameLanguage,
 'Component name' objectNameNote
FROM
 ObjComponents c 
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.componentName;

notes

• for objects with single component, componentCount should be used in lieu of objectCount if and only if it is greater than objectCount, per Michael (1/5/12)

Alternate Numbers extract

field mapping

sql query

view: cs_alternatenumbers (refreshed 2012-04-10)

SELECT
  CAST(n.ID AS VARCHAR) objectID,
  n.altnum pahmaAltNum,
  n.description pahmaAltNumType,
  n.remarks pahmaAltNumNote
FROM
  AltNums n
WHERE
  n.tableID = 108
 AND n.altnum IS NOT NULL
UNION AND coalesce(o.objectName,'') != c.componentName
UNION ALL
SELECT
 n.ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 nc.objectNamecomponentNumber pahmaAltNum,
 NULL (CASEpahmaAltNumType,
WHEN n.languageID != '0' THEN l.language END) objectNameLanguage,
 (CASE WHEN n.objectNameTypeID != '0' THEN t.objectNameType ELSE '' END) + (CASE WHEN n.objectNameTypeID != '0' AND n.remarks is not null THEN '; ' ELSE '' END) + coalesce(n.remarks, '') objectNameNote
FROM
 objectNames n
 LEFT OUTER JOIN objectNameTypes t ON n.objectNameTypeID = t.objectNameTypeID
 LEFT OUTER JOIN languages l ON n.languageID = l.languageID
WHERE
 n.objectName IS NOT NULL
;

notes

• TODO: change objectNameLanguage from static term list to vocabulary-controlled list
• TODO: translate controlled list options for objectNameLanguage field (current options do not match up)

...

NULL pahmaAltNumNote
FROM
 ObjComponents c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.componentNumber IS NOT NULL
 AND c.componentNumber != o.objectNumber
;

notes

• DONE: 3 new custom fields in repeating field group
• DONE: add controlled list options for pahmaAltNumType field

Object Names extract

field mapping

...

...

...

...

...

sql query

view: cs_objectnames (refreshed 2012-03-22)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.descriptionobjectName,
briefDescription FROMNULL objectNameLanguage,
 NULL objectNameNote
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.objectName IS NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.physDesc briefDescriptioncomponentName objectName,
 NULL objectNameLanguage,
 NULL objectNameNote
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.physDesccomponentName IS NOT NULL
UNION ALL
SELECT
  CAST(c.objectID AS VARCHAR) objectID,
 c.physDesc briefDescriptioncomponentName objectName,
 NULL objectNameLanguage,
 'Component name' objectNameNote
FROM
 ObjComponents c
WHERE JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.physDesccomponentName IS NOT NULL
;

Object Titles extract

field mapping

sql query

view: cs_objecttitles

SELECT
 
 AND coalesce(o.objectName,'') != c.componentName
UNION ALL
SELECT
 CAST(n.objectID AS VARCHAR) objectID,
  titlen.objectName,
  (CASE WHEN n.languageID != '0' THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(languages):item:name(' + rtrim(l.language ENDMTB_ISOcode) titleLanguage,
 + ')''' + (CASE WHEN n.titleTypeID != '0'charindex('/', l.language) > 0 THEN t.titleType ELSE '' 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
;

notes

...

substring(l.language, 1, charindex('/', l.language) - 1) ELSE l.language END) + '''' END) objectNameLanguage,
 (CASE WHEN n.objectNameTypeID != '0' THEN t.objectNameType ELSE '' END) + (CASE WHEN n.objectNameTypeID != '0' AND n.remarks is not null THEN '; ' ELSE '' END) + coalesce(n.remarks, '') objectNameNote
FROM
 objectNames n
 LEFT OUTER JOIN objectNameTypes t ON n.objectNameTypeID = t.objectNameTypeID
 LEFT OUTER JOIN languages l ON n.languageID = l.languageID
WHERE
 n.objectName IS NOT NULL
;

notes

• DONE: change objectNameLanguage from static term list to vocabulary-controlled list
• TODO: translate controlled list options for titleLanguage field (current options do not match up)
• TODO: update options for titleType field

Associated Dates extract

...

Brief Descriptions extract

field mapping

sql query

view: cs_associateddatesbriefdescriptions (refreshed 2012-04-10)

SELECT
  CAST(o.objectID, AS  lower(eventTypeVARCHAR) assocDateTypeobjectID,
 o.description briefDescription
dateTextFROM
assocDate_dateDisplayDate, Objects o
(CASEWHERE
WHEN dateBegSearcho.IsVirtual != '0' THEN dateBegSearch END) assocDate_dateEarliestSingleYear,
  (CASE WHEN dateEndSearch != '0' THEN dateEndSearch END) assocDate_dateLatestYear,
  dayBegSearch assocDate_dateEarliestSingleDay,
  dayEndSearch assocDate_dateLatestDay,
  monthBegSearch assocDate_dateEarliestSingleMonth,
  monthEndSearch assocDate_dateLatestMonth,
  remarks assocDateNote
FROM
  ObjDates
WHERE
  lower(eventType) not in ('', 'accessioned', 'date collected', 'secondary collection date', 'tertiary collection date', 'date made')
  AND (dateText is not null OR dateBegSearch != 0)
;

notes

• DONE: populate controlled list options for assocDateType field
• TODO: wait until assocDate field is changed into a structured date (in v2.1; see CSPACE-4775)

Field Collection Dates extract

this data cannot be imported until v2.1

field mapping

sql query

view: cs_fieldcollectiondates

 AND o.IsTemplate = 0
 AND o.description IS NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.physDesc briefDescription
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.physDesc IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.physDesc briefDescription
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.physDesc IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry briefDescription
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID IN (6, 7, 23)
;

Object Titles extract

field mapping

sql query

view: cs_objecttitles (refreshed 2012-03-22)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 Title dateText fieldCollectionDate_dateDisplayDate,
  (CASE WHEN dateBegSearch != '0' THEN dateBegSearch END) fieldCollectionDate_dateEarliestSingleYear,
  (CASE WHEN dateEndSearch != '0' THEN dateEndSearch END) fieldCollectionDate_dateLatestYear,
  dayBegSearch fieldCollectionDate_dateEarliestSingleDay,
  dayEndSearch fieldCollectionDate_dateLatestDay,
  monthBegSearch fieldCollectionDate_dateEarliestSingleMonth,
  monthEndSearch fieldCollectionDate_dateLatestMonth,
  remarks fieldCollectionNote
FROM
  ObjDates
WHERE
  lower(eventType) in ('date collected', 'secondary collection date', 'tertiary collection date')
UNION
SELECT
  objectID,
  dated fieldCollectionDate_dateDisplayDate,
  (CASE WHEN DateBegin != '0' THEN DateBegin END) fieldCollectionDate_dateEarliestSingleYear,
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 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(languages):item:name(' + rtrim(l.MTB_ISOcode) + ')''' + (CASE WHEN charindex('/', l.language) > 0 THEN substring(l.language, 1, charindex('/', l.language) - 1) ELSE l.language END) + '''' END) titleLanguage,
 (CASE WHEN DateEndn.titleTypeID != '0' THEN DateEndt.titleType END) fieldCollectionDate_dateLatestYear, titleType
FROM
 objTitles nulln
fieldCollectionDate_dateEarliestSingleDay, LEFT OUTER null fieldCollectionDate_dateLatestDay,
  null fieldCollectionDate_dateEarliestSingleMonth,
  null fieldCollectionDate_dateLatestMonth,
  null fieldCollectionNote
FROM
  Objects
WHERE
  dated is not null OR dateBegin != '0' OR dateEnd != '0'
;

notes

...

JOIN titleTypes t ON n.titleTypeID = t.titleTypeID
 LEFT OUTER JOIN languages l ON n.languageID = l.languageID
;

notes

• "remarks" field has only a few entries, which can be updated manually after data migration, per Michael (12/15/11)
• TODO: wait until fieldCollectionDate field is changed into a structured date (in v2.1; see CSPACE-4775)
• TODO: change fieldCollectionDate field into a repeating field

...

• DONE: change titleLanguage from static term list to vocabulary-controlled list

Ethnographic File Codes extract

field mapping

sql query

view: cs_productiondates

...

SELECT
  objectID,
  dateText objectProductionDate_dateDisplayDate,
  (CASE WHEN dateBegSearch != '0' THEN dateBegSearch END) objectProductionDate_dateEarliestSingleYear,
  (CASE WHEN dateEndSearch != '0' THEN dateEndSearch END) objectProductionDate_dateLatestYear,
  dayBegSearch objectProductionDate_dateEarliestSingleDay,
  dayEndSearch objectProductionDate_dateLatestDay,
  monthBegSearch objectProductionDate_dateEarliestSingleMonth,
  monthEndSearch objectProductionDate_dateLatestMonth,
  remarks objectProductionNote
FROM
  ObjDates
WHERE
  lower(eventType) = 'date made'
;

notes

Bibliographic References extract

field mapping

sql query

view: cs_bibliographicreferences

...

sql query

view: cs_ethnographicfilecodes

SELECT
 CAST(u.objectID AS VARCHAR) objectID,
 c.CSoptionID pahmaEthnographicFileCode
FROM
 ObjUserFields u
 JOIN MTB_EthnographicUseCodes c ON u.FieldValue = c.FieldValue
WHERE
 u.FieldTypeID = 17
;

notes

• pahmaEthnographicFileCode is currently a static term list, but it will be changed to a Concept Authority reference once that authority is available

Comments extract

field mapping

sql query

view:  cs_objectcomments (refreshed 2012-04-10)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.Notes bibliographycomment
referenceNote
FROM
 Objects Objectso
WHERE
 o.IsVirtual bibliography= is0 notAND nullo.IsTemplate UNION= ALL0
SELECT AND o.Notes objectID,IS NOT NULL
PubReferencesUNION referenceNoteALL
FROMSELECT
 CAST(o.objectID ObjectsAS WHEREVARCHAR) objectID,
 PubReferences is not null
;

Annotations extract

under construction

field mapping

sql query

view: cs_annotations

...

SELECT
 objectid,
 edition annotationNote,
 'TMS Edition field' annotationType
FROM
 Objects
WHERE
 edition is not null
UNION ALL
SELECT
 objectid,
 curatorialRemarks annotationNote,
 'TMS Curatorial remarks' annotationType
FROM
 Objects
WHERE
 curatorialRemarks is not null
;

notes

• some annotationType field values set by query above
• TODO: include data from TextEntries table in this query

Groups extract

to be imported into the Groups Procedure schema

field mapping

...

 t.TextEntry comment
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID = 27
;

notes

Materials note extract

field mapping

sql query

view: cs_groups

...

materialsnotes (refreshed 2012-04-10)

SELECT
 objectPackageID groupID,
 name title,
 notes scopeNoteCAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN ut.MTB_ConstituentIDtextTypeID = -137 THEN NULL tt.TextType + ': ' ELSE u.MTB_ConstituentID'' END) ownermaterialComponentNote_prefix,
FROM t.TextEntry ObjectPackagesmaterialComponentNote
pFROM
 LEFTTextEntries OUTER JOIN Users ut
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON pt.ownerTextTypeID = u.login
;

notes

• "global" field does not need to be imported, per Michael (1/12/12)

Object-Group Relationship extract

field mapping

...

TMS table name

...

TMS field name

...

CSpace field name

...

notes

...

ObjPkgList

...

ObjectID

...

objectID

...

...

ObjPkgList

...

ObjectPackageID

...

groupID

...

tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID IN (37, 92)
;

notes

• NOTE: 'materialComponentNote_prefix' should be prepended to 'materialComponentNote' prior to import (this can't be done here because some fields are type TEXT which cannot be concatenated in SQL)

NAGPRA cultural determination extract

field mapping

sql query

view: cs_objectgrouprelationships

...

 cs_nagpraculturaldeterminations (refreshed 2012-04-10)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 substring(tt.TextType, 1, 7) + lower(substring(tt.TextType, 8, len(tt.TextType))) + ': ' nagpraCulturalDetermination_prefix,
 t.TextEntry nagpraCulturalDetermination
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID = 121
 ;

notes

• NOTE: 'nagpraCulturalDetermination_prefix' should be prepended to 'nagpraCulturalDetermination' prior to import (this can't be done here because some fields are type TEXT which cannot be concatenated in SQL)

Repatriation note extract

field mapping

sql query

view:  cs_repatriationnotes (refreshed 2012-04-10)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry repatriationNote
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID = 81
;

notes

Taxonomic determination history notes extract

field mapping

sql query

view:  cs_taxonomicdeterminationnotes (refreshed 2012-04-10)

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry notes
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID = 96
;

notes

Object Status

field mapping

sql query

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 s.ObjectStatus pahmaObjectStatus
FROM
 Objects o
 LEFT OUTER JOIN ObjectStatuses s ON o.objectStatusID = s.objectStatusID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.objectStatusID IS NOT NULL
UNION ALL
SELECT
 CAST(sf.objectID AS VARCHAR) objectID,
 fl.FlagLabel pahmaObjectStatus
FROM
 StatusFlags sf
 LEFT OUTER JOIN FlagLabels fl ON sf.FlagID = fl.FlagID
WHERE
   fl.FlagID IS NOT NULL
ORDER BY objectID

notes