...
Code Block | ||||
---|---|---|---|---|
| ||||
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 | ||||
---|---|---|---|---|
| ||||
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 | ||||
---|---|---|---|---|
| ||||
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 | ||||
---|---|---|---|---|
| ||||
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 | ||||
---|---|---|---|---|
| ||||
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 | ||||
---|---|---|---|---|
| ||||
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 | ||||
---|---|---|---|---|
| ||||
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