PAHMA Citation Authority data mapping

this data cannot be imported until the Citation Authority schema is finalized

Citation Authority extract

in progress

field mapping

sql query

view:  cs_citations (refreshed 2012-04-10)

SELECT
 r.referenceID,
 NULL sourceType,
 r.title sourceTitle,
 (CASE WHEN r.series IS NOT NULL THEN r.series ELSE r.journal END) sourceBroaderTitle,
 r.subTitle sourceSubtitle,
 l.MTB_ISOcode sourceTitleLanguage,
 r.displayDate sourcePublicationYear,
 r.edition sourceEdition,
 r.volume sourceVolume,
 r.numOfPages sourceDetail,
 r.copyright sourcePublisher,
 r.placePublished sourcePublicationPlace,
 r.notes remarks
FROM
 ReferenceMaster r
 LEFT OUTER JOIN Languages l ON r.languageID = l.languageID
;

notes

• TODO: make sure field names and mappings are correct after Citation Authority is developed
• TODO: map sourcePublisher to Person or Org Authority?
• TODO: map sourcePublicationPlace to Place Authority?
• TODO: check that all data values for sourceTitleLanguage are in vocabulary-controlled term list?
• TODO: clean up non-single-year data values in sourcePublicationYear?
• NOTE: we do not need data from RefFormats or RefDates tables (RefFormats is empty, and RefDates has only 3 rows which are all redundant with data in ReferenceMaster.displayDate field)

Agent and Publisher info extract

in progress

field mapping

sql query

view:  cs_agentspublishers (refreshed 2012-04-10)

SELECT
 rm.referenceID,
 x.constituentID sourceName,
 lower(r.role) sourceNameRole
FROM
 ConXrefs x
 JOIN ROLES r ON x.roleID = r.roleID
 JOIN ReferenceMaster rm ON x.ID = rm.referenceID
WHERE
 x.tableID = 143
 AND x.active = 1
;

notes