Page Comparison

...

Object-Object Relationships extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjComponents	ObjectID	reference to Object
ObjComponents	ComponentID	reference to Object

sql query

view: cs_objectobjectrelationships

...

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

additional Object-Object Relationships extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Associations	ID1	reference to Object
Associations	ID2	reference to Object

sql query

view:

Code Block

	sql
	sql


SELECT
 oa.ID1 objectID_1,
 oa.ID2 objectID_2
FROM
 Associations oa,
 Objects obj1,
 Objects obj2,
 Relationships r
WHERE
 oa.tableID = 108
 AND oa.ID1 = obj1.objectID 
 AND oa.ID2 = obj2.objectID
 AND obj1.IsVirtual = 0 AND obj1.IsTemplate = 0
 AND obj2.IsVirtual = 0 AND obj2.IsTemplate = 0
 AND oa.relationshipID = r.relationshipID 
;

notes

type of relationship (as indicated in Relationships.Relation1 and Relationships.Relation2) is not included in this query, as we don't have a good place to put it in CSpace

Number of Objects extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	ObjectCount	numberOfObjects
ObjComponents	CompCount	numberOfObjects

sql query

view: cs_numberofobjects

Code Block

	sql
	sql

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.objectCount numberOfObjects
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.objectID NOT IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN c.compCount > o.objectCount THEN c.compCount ELSE o.objectCount 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.compCount numberOfObjects
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

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

TMS table name	TMS field name	CSpace field name	notes
AltNums	AltNum	pahmaAltNum
ObjComponents	ComponentNumber	pahmaAltNum
AltNums	Description	pahmaAltNumType	controlled list
AltNums	Remarks	pahmaAltNumNote

sql query

view: cs_alternatenumbers

Code Block

	sql
	sql

SELECT
  CAST(n.ID AS VARCHAR) objectID,
  n.altnum pahmaAltNum,
  n.description pahmaAltNumType,
  n.remarks pahmaAltNumNote
FROM
  AltNums n
WHERE
  n.tableID = 108
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.componentNumber pahmaAltNum,
 NULL pahmaAltNumType,
 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

TMS table name	TMS field name	CSpace field name	notes
Objects	ObjectName	objectName
ObjComponents	ComponentName	objectName
ObjectNames	ObjectName	objectName
ObjectNames	LanguageID	objectNameLanguage	FK to Languages
ObjectNames	ObjectNameTypeID	objectNameNote	FK to ObjectNameTypes; concatenate with Remarks
ObjectNames	Remarks	objectNameNote	concatenate with ObjectNameType

sql query

view: cs_objectnames

Code Block

	sql
	sql

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.objectName,
 NULL objectNameLanguage,
 NULL objectNameNote
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.componentName 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.componentName IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.componentName objectName,
 NULL objectNameLanguage,
 'Component name' objectNameNote
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.componentName IS NOT NULL
 AND coalesce(o.objectName,'') != c.componentName
UNION ALL
SELECT
 CAST(n.objectID AS VARCHAR) objectID,
 n.objectName,
 (CASE WHEN n.languageID != '0' THEN l.MTB_ISOcode 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

Brief Descriptions extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	description	briefDescription
ObjComponents	physDesc	briefDescription

sql query

view: cs_briefdescriptions

Code Block

	sql
	sql

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.description briefDescription
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
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
;

Object Titles extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjTitles	Title	title
ObjTitles	LanguageID	titleLanguage	FK to Languages
ObjTitles	TitleTypeID	titleType	FK to TitleTypes

sql query

view: cs_objecttitles

Code Block

	sql
	sql

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
;

notes

"remarks" field has only a few entries, which can be updated manually after data migration, per Michael (12/15/11)
DONE: change titleLanguage from static term list to vocabulary-controlled list

Ethnographic File Codes extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
ObjUserFields	FieldValue	pahmaEthnographicFileCode

sql query

view: cs_ethnographicfilecodes

Code Block

	sql
	sql

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

...

this data cannot be imported until v2.1

field mapping

TMS table name	TMS field name	CSpace field name
ObjDates	EventType	assocDateType
ObjDates	DateText	assocDate_dateDisplayDate
ObjDates	DateBegSearch	assocDate_dateEarliestSingleYear
ObjDates	DateEndSearch	assocDate_dateLatestYear
ObjDates	DayBegSearch	assocDate_dateEarliestSingleDay
ObjDates	DayEndSearch	assocDate_dateLatestDay
ObjDates	MonthBegSearch	assocDate_dateEarliestSingleMonth
ObjDates	MonthEndSearch	assocDate_dateLatestMonth
ObjDates	Remarks	assocDateNote

sql query

view: cs_associateddates

Code Block

	sql
	sql

SELECT
 CAST(d.objectID AS VARCHAR) objectID,
 lower(d.eventType) assocDateType,
 d.dateText assocDate_dateDisplayDate,
 (CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) assocDate_dateEarliestSingleYear,
 (CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) assocDate_dateLatestYear,
 d.dayBegSearch assocDate_dateEarliestSingleDay,
 d.dayEndSearch assocDate_dateLatestDay,
 d.monthBegSearch assocDate_dateEarliestSingleMonth,
 d.monthEndSearch assocDate_dateLatestMonth,
 d.remarks assocDateNote
FROM
 ObjDates d
WHERE
 lower(d.eventType) not in ('', 'accessioned', 'date collected', 'secondary collection date', 'tertiary collection date', 'date made')
 AND (d.dateText is not null OR d.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)

...

this data cannot be imported until v2.1

field mapping

TMS table name	TMS field name	CSpace field name
ObjDates	DateText	fieldCollectionDate_dateDisplayDate
ObjDates	DateBegSearch	fieldCollectionDate_dateEarliestSingleYear
ObjDates	DateEndSearch	fieldCollectionDate_dateLatestYear
ObjDates	DayBegSearch	fieldCollectionDate_dateEarliestSingleDay
ObjDates	DayEndSearch	fieldCollectionDate_dateLatestDay
ObjDates	MonthBegSearch	fieldCollectionDate_dateEarliestSingleMonth
ObjDates	MonthEndSearch	fieldCollectionDate_dateLatestMonth
ObjDates	Remarks	fieldCollectionNote
Objects	Dated	fieldCollectionDate_dateDisplayDate
Objects	DateBegin	fieldCollectionDate_dateEarliestSingleYear
Objects	DateEnd	fieldCollectionDate_dateLatestYear

sql query

view: cs_fieldcollectiondates

Code Block

	sql
	sql

SELECT
 CAST(d.objectID AS VARCHAR) objectID,
 d.dateText fieldCollectionDate_dateDisplayDate,
 (CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) fieldCollectionDate_dateLatestYear,
 d.dayBegSearch fieldCollectionDate_dateEarliestSingleDay,
 d.dayEndSearch fieldCollectionDate_dateLatestDay,
 d.monthBegSearch fieldCollectionDate_dateEarliestSingleMonth,
 d.monthEndSearch fieldCollectionDate_dateLatestMonth,
 d.remarks fieldCollectionNote
FROM
 ObjDates d
WHERE
 lower(d.eventType) in ('date collected', 'secondary collection date', 'tertiary collection date')
UNION
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.dated fieldCollectionDate_dateDisplayDate,
 (CASE WHEN o.dateBegin != '0' THEN o.dateBegin END) fieldCollectionDate_dateEarliestSingleYear,
 (CASE WHEN o.dateEnd != '0' THEN o.dateEnd END) fieldCollectionDate_dateLatestYear,
 null fieldCollectionDate_dateEarliestSingleDay,
 null fieldCollectionDate_dateLatestDay,
 null fieldCollectionDate_dateEarliestSingleMonth,
 null fieldCollectionDate_dateLatestMonth,
 null fieldCollectionNote
FROM
 Objects o
WHERE
 o.dated IS NOT NULL OR o.dateBegin != '0' OR o.dateEnd != '0'
;

notes

differences in eventType ('date collected', 'secondary collection date', 'tertiary collection date') are not important and do not need to be migrated, per Michael (12/15/11)
DONE: add new field pahmaFieldCollectionDate, to change fieldCollectionDate field into a repeating field
TODO: wait until fieldCollectionDate field is changed into a structured date (in v2.1; see CSPACE-4775)

Production Date extract

field mapping

TMS table name	TMS field name	CSpace field name
ObjDates	DateText	objectProductionDate_dateDisplayDate
ObjDates	DateBegSearch	objectProductionDate_dateEarliestSingleYear
ObjDates	DateEndSearch	objectProductionDate_dateLatestYear
ObjDates	DayBegSearch	objectProductionDate_dateEarliestSingleDay
ObjDates	DayEndSearch	objectProductionDate_dateLatestDay
ObjDates	MonthBegSearch	objectProductionDate_dateEarliestSingleMonth
ObjDates	MonthEndSearch	objectProductionDate_dateLatestMonth
ObjDates	Remarks	objectProductionNote

sql query

view: cs_productiondates

Code Block

	sql
	sql

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

notes

Bibliographic References extract

field mapping

TMS table name	TMS field name	CSpace field name	notes
Objects	Bibliography	referenceNote
Objects	PubReferences	referenceNote

sql query

view: cs_bibliographicreferences

Code Block

	sql
	sql

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.bibliography referenceNote
FROM
 Objects o
WHERE
 o.bibliography is not null
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.PubReferences referenceNote
FROM
 Objects o
WHERE
 o.PubReferences is not null
;

Annotations extract

field mapping

TMS table name	TMS field name	CSpace field name
Objects	Edition	annotationNote
Objects	CuratorialRemarks	annotationNote
Components	PrepComments	annotationNote
ObjUserFields	FieldValue	annotationNote

sql query

view: cs_annotations

Code Block

	sql
	sql

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.edition annotationNote,
 'TMS Edition field' annotationType
FROM
 Objects o
WHERE
 o.edition IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.curatorialRemarks annotationNote,
 'TMS Curatorial remarks' annotationType
FROM
 Objects o
WHERE
 o.curatorialRemarks IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.prepComments annotationNote,
 'Component Prep comments' annotationType
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.prepComments IS NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.prepComments annotationNote,
 'Component Prep comments' annotationType
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.prepComments IS NOT NULL
UNION ALL
SELECT
 CAST(u.objectID AS VARCHAR) objectID,
 u.fieldValue annotationNote,
 t.userFieldType annotationType
FROM
 ObjUserFields u
 JOIN UserFieldTypes t ON u.fieldTypeID = t.userFieldTypeID
WHERE
 u.FieldTypeID != 17
;

notes

some annotationType field values set by query above
TODO: update controlled list options for annotationType field
TODO: include some data from TextEntries table in this query...?

Page Comparison

Versions Compared

Old Version 73

New Version 74

Key

Object-Object Relationships extract

field mapping

sql query

additional Object-Object Relationships extract

field mapping

sql query

notes

Number of Objects extract

field mapping

sql query

notes

Alternate Numbers extract

field mapping

sql query

notes

Object Names extract

field mapping

sql query

notes

Brief Descriptions extract

field mapping

sql query

Object Titles extract

field mapping

sql query

notes

Ethnographic File Codes extract

field mapping

sql query

notes

field mapping

sql query

notes

field mapping

sql query

notes

Production Date extract

field mapping

sql query

notes

Bibliographic References extract

field mapping

sql query

Annotations extract

field mapping

sql query

notes