|
<?xml version="1.0" encoding="UTF-8"?>
|
|
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
|
|
xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:cup="http://contentservices.cambridge.org"
|
|
xmlns:math="http://www.w3.org/2005/xpath-functions/math"
|
|
xmlns:java="http://saxon.sf.net/java-type" xmlns:sql="http://saxon.sf.net/sql"
|
|
xmlns:err="http://www.w3.org/2005/xqt-errors" xmlns:xd="http://www.oxygenxml.com/ns/doc/xsl"
|
|
exclude-result-prefixes="xs math xd cup" version="3.0">
|
|
|
|
|
|
|
|
<xsl:param name="db-username" select="'***'" as="xs:string"/>
|
|
<xsl:param name="db-password" select="'***'" as="xs:string"/>
|
|
<xsl:param name="db-host" select="'***'" as="xs:string"/>
|
|
<xsl:param name="db" select="'***'" as="xs:string"/>
|
|
<xsl:param name="db-port" select="'1521'"/>
|
|
|
|
<xsl:variable name="db-connection-string"
|
|
select="
|
|
'jdbc:oracle:thin:' || $db-username || '/' || $db-password || '@//' ||
|
|
$db-host || ':' || $db-port || '/' || $db"/>
|
|
|
|
<xsl:variable as="java:java.sql.Connection" name="connection">
|
|
<xsl:try>
|
|
<sql:connect auto-commit="no" database="{$db-connection-string}"
|
|
driver="oracle.jdbc.driver.OracleDriver" password="{$db-username}" user="{$db-password}"
|
|
xsl:extension-element-prefixes="sql"/>
|
|
<xsl:catch errors="err:SXSQ0003">
|
|
<sql:connect auto-commit="no" database="{$db-connection-string}"
|
|
driver="oracle.jdbc.driver.OracleDriver" password="{$db-username}" user="{$db-password}"
|
|
xsl:extension-element-prefixes="sql"/>
|
|
</xsl:catch>
|
|
</xsl:try>
|
|
</xsl:variable>
|
|
|
|
|
|
|
|
<xsl:function name="cup:book-meta" as="element(BOOK)">
|
|
|
|
<xsl:param name="identifier" as="xs:string"/>
|
|
|
|
<BOOK>
|
|
<xsl:sequence select="cup:titles-query($connection)"/>
|
|
<xsl:sequence select="cup:isbn-subject-query($identifier)"/>
|
|
</BOOK>
|
|
|
|
</xsl:function>
|
|
|
|
<xd:doc>
|
|
<xd:desc>
|
|
<xd:p>Extract the title records from the Oracle database.</xd:p>
|
|
</xd:desc>
|
|
<xd:note>
|
|
<xd:p>The original version of this code went to great lengths to escape ampersond, quote,
|
|
apostraphe, etc. The sql:query statement handles output escaping so this has been
|
|
removed.</xd:p>
|
|
</xd:note>
|
|
</xd:doc>
|
|
<xsl:function name="cup:titles-query" as="element(*)*">
|
|
|
|
<xsl:param name="title-code" as="xs:string"/>
|
|
|
|
<xsl:variable name="select"
|
|
select="
|
|
'ct.title as title,
|
|
ct.subtitle as subtitle,
|
|
ct.volume_number as vol,
|
|
ct.part_number as pt_num,
|
|
ct.part_title as pt_ttl,
|
|
ct.edition_number as ed_num,
|
|
ct.volume_title as vl_ttl'"/>
|
|
|
|
<xsl:variable name="results" as="element(*)*">
|
|
<sql:query column="{$select}" connection="$connection" row-tag="TITLES"
|
|
table="oraliv.core_title ct" where="ct.title_code = '{$title-code}'"/>
|
|
</xsl:variable>
|
|
|
|
<xsl:sequence select="$results/*"/>
|
|
|
|
</xsl:function>
|
|
|
|
<xd:doc>
|
|
<xd:desc>
|
|
<xd:p>Extract ISBN and related data from the Oracle database. </xd:p>
|
|
</xd:desc>
|
|
</xd:doc>
|
|
<xsl:function name="cup:isbn-subject-query" as="element(*)*">
|
|
|
|
<xsl:param name="identifier" as="xs:string"/>
|
|
|
|
<xsl:variable name="select"
|
|
select="
|
|
'
|
|
decode(pit.format_code,''OC'',pit.series_code) as s_code,
|
|
decode(pit.format_code,''OC'',pit.series_legend) as series,
|
|
pit.bic_edition_statement as ed_text,
|
|
substr(pit.subject_2_code,1,2) as subj1,
|
|
pit.subject_2_code as subj2,
|
|
pit.subject_3_code as subj3,
|
|
(SELECT sb.SUBJECT_LEGEND FROM oraliv.sr_subject_vista sb WHERE sb.SUBJECT_CODE = SUBSTR (pit.subject_2_code, 0, 2)) as subject1_legend,
|
|
pit.subject_2_legend as subject2_legend,
|
|
pit.subject_3_legend as subject3_legend,
|
|
pit.category_code as prod_group,
|
|
pit.pages as pages'"/>
|
|
|
|
|
|
<xsl:variable name="results" as="element(*)*">
|
|
<sql:query column="{$select}" connection="$connection" row-tag="PROD"
|
|
table="oraliv.product_isbn_title pit" where="pit.ean_number = '{$identifier}'"/>
|
|
</xsl:variable>
|
|
|
|
<xsl:message><xsl:copy-of select="serialize($results)"/></xsl:message>
|
|
|
|
<xsl:variable name="data" select="$results/*" as="element(*)*"/>
|
|
|
|
<xsl:sequence select="$data"/>
|
|
|
|
</xsl:function>
|
|
|
|
|
|
|
|
</xsl:stylesheet>
|
.