Versions Compared

Old Version 93

changes.mady.by.user John W Keller

Saved on

compared with

New Version 94

changes.mady.by.user John W Keller

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
sql
sql
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.Notes comment
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.Notes IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 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

TMS table name

TMS field name

CSpace field name

notes

TextEntries

textEntry

materialComponentNote

where textTypeID is 37 or 92

sql query

view: (NEEDS UPDATE)

Code Block
sql
sql

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN t.textTypeID = 37 THEN tt.TextType + ': ' ELSE '' END) materialComponentNote_prefix,
 t.TextEntry materialComponentNote
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 (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)

Person depicted extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ConXrefs

constituentID

contentPerson

reference to Person Authority

sql query

view: (NEEDS UPDATE)

Code Block
sql
sql
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID contentPerson
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
 JOIN constituents c ON x.constituentID = c.constituentID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.constituentTypeID = 1
 AND lower(r.ROLE) IN ('subject')
;

notes

Place depicted extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

ThesXrefs

termID

contentPlace

reference to Place Authority

sql query

view: (NEEDS UPDATE)

Code Block
sql
sql
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.termID contentPlace
FROM
 ThesXrefs x
 JOIN ThesXrefTypes t ON x.thesXrefTypeID = t.thesXrefTypeID
 JOIN Objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND x.thesXrefTableID = 346
 AND lower(t.thesXrefType) IN ('subject')
;

notes

Cultural group depicted extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

TextEntries

textEntry

contentPeople

where textTypeID is 110 or 111

sql query

view: (NEEDS UPDATE)

Code Block
sql
sql
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry contentPeople
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 (110, 111)
;

notes

Content note extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

TextEntries

textEntry

contentNote

where textTypeID is 85

sql query

view: (NEEDS UPDATE)

Code Block
sql
sql
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry contentNote
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 = 85
;

notes

Inscription content extract

field mapping

TMS table name

TMS field name

CSpace field name

notes

Objects

Signed

inscriptionContent

 

TextEntries

textEntry

inscriptionContent

where textTypeID is 19, 20, 22, 78, or 87

sql query

view: (NEEDS UPDATE)

Code Block
sql
sql
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 NULL inscriptionContent_prefix,
 o.Signed inscriptionContent
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.Signed IS NOT NULL
UNION ALL 
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN t.textTypeID IN (19, 20, 22) THEN substring(tt.TextType, 7, 1) + lower(substring(tt.TextType, 8, len(tt.TextType))) + ': ' ELSE '' END) inscriptionContent_prefix,
 t.TextEntry inscriptionContent
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 (19, 20, 22, 78, 87) 
;

notes

  • NOTE: 'inscriptionContent_prefix' should be prepended to 'inscriptionContent' prior to import (this can't be done here because some fields are type TEXT
    which cannot be concatenated in SQL)
  • NOTE: multiple notes per object should be concatenated into single (non-repeating) field inscriptionContent, separated by line breaks