<commit_before><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.gbif.checklistbank.service.mybatis.mapper.DistributionMapper">

  <sql id="selectCommon">
    d.id, d.location_id, d.locality, d.country,
    d.occurrence_status, d.threat_status,d.establishment_means, d.appendix_cites,
    d.start_day_of_year, d.end_day_of_year,
    d.remarks,d.temporal,d.life_stage
  </sql>
  <sql id="select">
    d.usage_fk AS usageKey, null AS sourceTaxonKey,c.citation as source,<include refid="selectCommon"/>
  </sql>
  <sql id="selectNub">
    rel.nub_fk as usageKey, d.usage_fk as sourceTaxonKey,ds.title as source,<include refid="selectCommon"/>
  </sql>

  <sql id="from">distribution d
    LEFT JOIN citation c ON d.source_fk=c.id
  </sql>
  <sql id="fromNub">distribution d
    JOIN nub_rel rel ON rel.usage_fk=d.usage_fk
    <include refid="org.gbif.checklistbank.service.mybatis.mapper.Common.excludeRels"/>
    LEFT JOIN dataset ds ON rel.dataset_key=ds.key
  </sql>

  <sql id="page">LIMIT #{page.limit} OFFSET #{page.offset}</sql>

  <!--  A mapping to build a Distribution -->
  <resultMap id="distributionResultMap" type="Distribution" autoMapping="true">
    <result property="taxonKey" column="usageKey"/>
    <result property="status" column="occurrence_status"/>
    <result property="source" column="citation"/>
  </resultMap>

  <resultMap id="distributionRangeResultMap" type="UsageRelated" autoMapping="true">
    <id property="usageKey" column="usageKey"/>
    <id property="value" column="id"/>
    <association property="value" javaType="Distribution" resultMap="distributionResultMap" />
  </resultMap>

  <resultMap id="iucnRedListCategoryResultMap" type="IucnRedListCategory" autoMapping="true">
    <result property="category" column="threat_status"/>
    <result property="iucnRedListSpeciesKey" column="species_key"/>
    <result property="iucnRedListName" column="scientific_name"/>
  </resultMap>

  <!-- get all distributions linked to one usage -->
  <select id="listByChecklistUsage" parameterType="map" resultMap="distributionResultMap">
    SELECT
    <include refid="select"/>
    FROM
    <include refid="from"/>
    WHERE d.usage_fk=#{key}
    ORDER BY d.country NULLS FIRST, d.id
    <include refid="page"/>
  </select>

  <!-- get all distributions by nub usage -->
  <select id="listByNubUsage" parameterType="map" resultMap="distributionResultMap">
    SELECT
    <include refid="selectNub"/>
    FROM
    <include refid="fromNub"/>
    WHERE rel.nub_fk=#{key}
    ORDER BY d.country NULLS FIRST, d.id
    <include refid="page"/>
  </select>

  <!-- get all distributions linked to a usage range -->
  <select id="listByChecklistUsageRange" parameterType="map" resultMap="distributionRangeResultMap">
    SELECT
    <include refid="select"/>
    FROM
    <include refid="from"/>
    WHERE d.usage_fk between #{start} and #{end}
  </select>

  <!-- get all distributions linked to a nub usage range -->
  <select id="listByNubUsageRange" parameterType="map" resultMap="distributionRangeResultMap">
    SELECT
    <include refid="selectNub"/>
    FROM
    <include refid="fromNub"/>
    WHERE rel.nub_fk between #{start} and #{end}
  </select>

  <select id="getIucnRedListCategory" parameterType="map" resultType="iucnRedListCategoryResultMap">
    SELECT d.threat_status, iucn.species_fk AS species_key, n.scientific_name
    FROM nub_rel rel
      JOIN name_usage iucn ON iucn.id=rel.usage_fk
      JOIN name n ON iucn.name_fk=n.id
      JOIN distribution d ON d.usage_fk= CASE WHEN iucn.is_synonym THEN iucn.parent_fk ELSE iucn.id END
    WHERE rel.nub_fk=#{key} AND rel.dataset_key='<KEY>'
    LIMIT 1
  </select>

  <!--  select all distributions of a dataset
  Using a optimal fetchsize to enable low memory footprint while keeping good performance using a resulthandler
  -->
  <select id="processDataset" parameterType="map" resultMap="distributionResultMap" fetchSize="10000" resultSetType="FORWARD_ONLY">
    SELECT
    <choose><commit_msg>resultMap instead of resultType<commit_after><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.gbif.checklistbank.service.mybatis.mapper.DistributionMapper">

  <sql id="selectCommon">
    d.id, d.location_id, d.locality, d.country,
    d.occurrence_status, d.threat_status,d.establishment_means, d.appendix_cites,
    d.start_day_of_year, d.end_day_of_year,
    d.remarks,d.temporal,d.life_stage
  </sql>
  <sql id="select">
    d.usage_fk AS usageKey, null AS sourceTaxonKey,c.citation as source,<include refid="selectCommon"/>
  </sql>
  <sql id="selectNub">
    rel.nub_fk as usageKey, d.usage_fk as sourceTaxonKey,ds.title as source,<include refid="selectCommon"/>
  </sql>

  <sql id="from">distribution d
    LEFT JOIN citation c ON d.source_fk=c.id
  </sql>
  <sql id="fromNub">distribution d
    JOIN nub_rel rel ON rel.usage_fk=d.usage_fk
    <include refid="org.gbif.checklistbank.service.mybatis.mapper.Common.excludeRels"/>
    LEFT JOIN dataset ds ON rel.dataset_key=ds.key
  </sql>

  <sql id="page">LIMIT #{page.limit} OFFSET #{page.offset}</sql>

  <!--  A mapping to build a Distribution -->
  <resultMap id="distributionResultMap" type="Distribution" autoMapping="true">
    <result property="taxonKey" column="usageKey"/>
    <result property="status" column="occurrence_status"/>
    <result property="source" column="citation"/>
  </resultMap>

  <resultMap id="distributionRangeResultMap" type="UsageRelated" autoMapping="true">
    <id property="usageKey" column="usageKey"/>
    <id property="value" column="id"/>
    <association property="value" javaType="Distribution" resultMap="distributionResultMap" />
  </resultMap>

  <resultMap id="iucnRedListCategoryResultMap" type="IucnRedListCategory" autoMapping="true">
    <result property="category" column="threat_status"/>
    <result property="iucnRedListSpeciesKey" column="species_key"/>
    <result property="iucnRedListName" column="scientific_name"/>
  </resultMap>

  <!-- get all distributions linked to one usage -->
  <select id="listByChecklistUsage" parameterType="map" resultMap="distributionResultMap">
    SELECT
    <include refid="select"/>
    FROM
    <include refid="from"/>
    WHERE d.usage_fk=#{key}
    ORDER BY d.country NULLS FIRST, d.id
    <include refid="page"/>
  </select>

  <!-- get all distributions by nub usage -->
  <select id="listByNubUsage" parameterType="map" resultMap="distributionResultMap">
    SELECT
    <include refid="selectNub"/>
    FROM
    <include refid="fromNub"/>
    WHERE rel.nub_fk=#{key}
    ORDER BY d.country NULLS FIRST, d.id
    <include refid="page"/>
  </select>

  <!-- get all distributions linked to a usage range -->
  <select id="listByChecklistUsageRange" parameterType="map" resultMap="distributionRangeResultMap">
    SELECT
    <include refid="select"/>
    FROM
    <include refid="from"/>
    WHERE d.usage_fk between #{start} and #{end}
  </select>

  <!-- get all distributions linked to a nub usage range -->
  <select id="listByNubUsageRange" parameterType="map" resultMap="distributionRangeResultMap">
    SELECT
    <include refid="selectNub"/>
    FROM
    <include refid="fromNub"/>
    WHERE rel.nub_fk between #{start} and #{end}
  </select>

  <select id="getIucnRedListCategory" parameterType="map" resultMap="iucnRedListCategoryResultMap">
    SELECT d.threat_status, iucn.species_fk AS species_key, n.scientific_name
    FROM nub_rel rel
      JOIN name_usage iucn ON iucn.id=rel.usage_fk
      JOIN name n ON iucn.name_fk=n.id
      JOIN distribution d ON d.usage_fk= CASE WHEN iucn.is_synonym THEN iucn.parent_fk ELSE iucn.id END
    WHERE rel.nub_fk=#{key} AND rel.dataset_key='<KEY>'
    LIMIT 1
  </select>

  <!--  select all distributions of a dataset
  Using a optimal fetchsize to enable low memory footprint while keeping good performance using a resulthandler
  -->
  <select id="processDataset" parameterType="map" resultMap="distributionResultMap" fetchSize="10000" resultSetType="FORWARD_ONLY">
    SELECT
    <choose>
{'fraction_non_alphanumeric': 0.13394919168591224, 'fraction_numerical': 0.002917223775373769, 'mean_word_length': 4.391874180865006, 'pattern_counts': {'":': 0, '<': 127, '<?xml version=': 2, '>': 127, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7255940', 'n_tokens_mistral': 2815, 'n_tokens_neox': 2658, 'n_words': 620}