Page Comparison

...

TMS table name	TMS field name	CSpace field name
ObjComponents	ObjectID	reference to one Object
ObjComponents	ComponentID	reference to other Object
Associations	ID1	reference to one Object
Associations	ID2	reference to other Object

sql query

view: cs_objectobjectrelationships

Code Block

	sql
	sql

SELECT
 CAST(c.objectID AS VARCHAR) parentObjectIDobjectID_1,
  'c' + CAST(c.componentID AS VARCHAR) childObjectID 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 ;

notes

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


SELECT
 oa.ID1 objectID_1,
 oa.ID2 objectID_2ALL;
SELECT
 CAST(oa.ID1 AS VARCHAR) objectID_1,
 CAST(oa.ID2 AS VARCHAR) objectID_2,
 r.relation1 + '-' + r.relation2 relationshipType
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 this should be imported as two-way relationship, so it shows up in UI for both objects
relationship type is included in this query, as but 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
Objects	Title	title
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(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
;

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

Versions Compared

Old Version 77

New Version 78

Key

sql query

notes

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