<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.registry.persistence.mapper.OrganizationMapper">

  <!-- Auto-mapping and eager loading of sub resources  -->
  <resultMap id="ORGANIZATION_MAP" type="Organization" autoMapping="true">
    <id property="key" column="key"/>
    <result property="email" column="email" typeHandler="StringArrayTypeHandler"/>
    <result property="phone" column="phone" typeHandler="StringArrayTypeHandler"/>
    <result property="address" column="address" typeHandler="StringArrayTypeHandler"/>
    <result property="homepage" column="homepage" typeHandler="UriArrayTypeHandler"/>
    <association property="numPublishedDatasets" column="key" select="countPublishedDatasets" />
    <collection property="contacts" column="key" select="listContacts"/>
    <collection property="endpoints" column="key" select="listEndpoints"/>
    <collection property="machineTags" column="key" select="listMachineTags"/>
    <collection property="tags" column="key" select="listTags"/>
    <collection property="identifiers" column="key" select="listIdentifiers"/>
    <collection property="comments" column="key" select="listComments"/>
  </resultMap>

  <sql id="WRITABLE_ORGANIZATION_FIELDS">
    key,endorsing_node_key,endorsement_approved,password,title,abbreviation,description,language,email,
    phone,homepage,logo_url,address,city,province,country,postal_code,latitude,longitude,created,created_by,
    modified,modified_by
  </sql>

  <sql id="ORGANIZATION_FIELDS">
    o.key,o.endorsing_node_key,o.endorsement_approved,o.password,o.title,o.abbreviation,o.description,o.language,o.email,
    o.phone,o.homepage,o.logo_url,o.address,o.city,o.province,o.country,o.postal_code,o.latitude,o.longitude,
    o.created,o.created_by,o.modified,o.modified_by,o.deleted
  </sql>

  <sql id="LEGACY_ORGANIZATION_BRIEF_FIELDS">
    o.key,o.title AS name
  </sql>

  <!--
    Simple fulltext search on the search field or contact search field.
    plainto_tsquery() is used to handle ALL stuff from the URL so the limitations are constrained by this method.
    Uses the default configuration english for the English language.
    E.g. no prefix search is supported.
   -->
  <sql id="SIMPLE_SEARCH">
    AND (
      o.fulltext_search @@ plainto_tsquery('english',#{query})
      OR EXISTS(
        SELECT contact_key
        FROM organization_contact JOIN contact c ON c.key = contact_key
        WHERE organization_key=o.key AND c.fulltext_search @@ plainto_tsquery('english',#{query})
      )
    )
  </sql>

  <!--
    key, password, created and deleted are never changed
   -->
  <sql id="ORGANIZATION_UPDATE">
    endorsing_node_key = #{endorsingNodeKey,jdbcType=OTHER},
    endorsement_approved = #{endorsementApproved,jdbcType=BOOLEAN},
    title = #{title,jdbcType=VARCHAR},
    abbreviation = #{abbreviation,jdbcType=CHAR},
    description = #{description,jdbcType=VARCHAR},
    language = #{language,jdbcType=CHAR},
    email = #{email,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    phone = #{phone,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    homepage = #{homepage,jdbcType=ARRAY, typeHandler=UriArrayTypeHandler},
    logo_url = #{logoUrl,jdbcType=VARCHAR},
    address = #{address,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    city = #{city,jdbcType=VARCHAR},
    province = #{province,jdbcType=VARCHAR},
    country = upper(#{country,jdbcType=CHAR}),
    postal_code = #{postalCode,jdbcType=VARCHAR},
    latitude = #{latitude,jdbcType=DOUBLE},
    longitude = #{longitude,jdbcType=DOUBLE},
    modified = now(),
    modified_by = #{modifiedBy},
    deleted = null <!-- if we're updating it can't be deleted -->
  </sql>

  <sql id="WRITABLE_ORGANIZATION_FIELD_TYPES">
    #{key,jdbcType=OTHER},
    #{endorsingNodeKey,jdbcType=OTHER},
    #{endorsementApproved,jdbcType=BOOLEAN},
    #{password,jdbcType=VARCHAR},
    #{title,jdbcType=VARCHAR},
    #{abbreviation,jdbcType=CHAR},
    #{description,jdbcType=VARCHAR},
    #{language,jdbcType=CHAR},
    #{email,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    #{phone,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    #{homepage,jdbcType=ARRAY, typeHandler=UriArrayTypeHandler},
    #{logoUrl,jdbcType=VARCHAR},
    #{address,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    #{city,jdbcType=VARCHAR},
    #{province,jdbcType=VARCHAR},
    #{country,jdbcType=CHAR},
    #{postalCode,jdbcType=VARCHAR},
    #{latitude,jdbcType=DOUBLE},
    #{longitude,jdbcType=DOUBLE},
    now(), <!-- created -->
    #{createdBy},
    now(), <!-- modified -->
    #{createdBy}
  </sql>

  <select id="countPublishedDatasets" resultType="int">
    SELECT COUNT(*) AS num_datasets FROM dataset WHERE deleted IS NULL AND publishing_organization_key = #{key}
  </select>

  <!--  Note: you can get entities which are deleted -->
  <select id="get" resultMap="ORGANIZATION_MAP">
    SELECT <include refid="ORGANIZATION_FIELDS"/>
    FROM organization o
    WHERE o.key = #{key,jdbcType=OTHER}
  </select>

  <insert id="create" parameterType="Organization">
    INSERT INTO organization(<include refid="WRITABLE_ORGANIZATION_FIELDS"/>)
    VALUES(<include refid="WRITABLE_ORGANIZATION_FIELD_TYPES"/>)
  </insert>

  <update id="update" parameterType="Organization">
    UPDATE organization
    SET <include refid="ORGANIZATION_UPDATE"/>
    WHERE key = #{key,jdbcType=OTHER}
  </update>

  <!-- For safety, should it be already deleted nothing is done -->
  <update id="delete">
    UPDATE organization
    SET deleted = now()
    WHERE key = #{key,jdbcType=OTHER} AND deleted IS NULL
  </update>

  <!-- Append safe ordering, omitting deleted entities -->
  <select id="list" resultType="Organization"  resultMap="ORGANIZATION_MAP" parameterType="Pageable">
    SELECT <include refid="ORGANIZATION_FIELDS"/>
    FROM organization o
    WHERE o.deleted IS NULL
    ORDER BY o.created DESC, o.key
    <if test="page != null" >
      LIMIT #{page.limit} OFFSET #{page.offset}
    </if>
  </select>

  <!-- Append ordering by title, omitting deleted entities. No paging, instead all organizations included in response -->
  <select id="listLegacyOrganizationsBrief" resultType="org.gbif.registry.ws.model.LegacyOrganizationBriefResponse">
    SELECT <include refid="LEGACY_ORGANIZATION_BRIEF_FIELDS"/>
    FROM organization o
    WHERE o.deleted IS NULL
    ORDER BY lower(o.title)
  </select>

  <!-- Basic paging search functionality -->
  <select id="search" resultMap="ORGANIZATION_MAP">
    SELECT <include refid="ORGANIZATION_FIELDS"/>
    FROM organization o
    WHERE deleted IS NULL
    <if test="query != null" >
      <include refid="SIMPLE_SEARCH"/>
    </if>
    ORDER BY o.created DESC, key
    <if test="page != null" >
      LIMIT #{page.limit} OFFSET #{page.offset}
    </if>
  </select>

  <select id="count" resultType="Integer">
    SELECT COUNT(*)
    FROM organization o
    WHERE deleted IS NULL
    <if test="query != null" >
      <include refid="SIMPLE_SEARCH"/>
    </if>
  </select><commit_msg>sorting organization search results by rank http://dev.gbif.org/issues/browse/POR-2535<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.registry.persistence.mapper.OrganizationMapper">

  <!-- Auto-mapping and eager loading of sub resources  -->
  <resultMap id="ORGANIZATION_MAP" type="Organization" autoMapping="true">
    <id property="key" column="key"/>
    <result property="email" column="email" typeHandler="StringArrayTypeHandler"/>
    <result property="phone" column="phone" typeHandler="StringArrayTypeHandler"/>
    <result property="address" column="address" typeHandler="StringArrayTypeHandler"/>
    <result property="homepage" column="homepage" typeHandler="UriArrayTypeHandler"/>
    <association property="numPublishedDatasets" column="key" select="countPublishedDatasets" />
    <collection property="contacts" column="key" select="listContacts"/>
    <collection property="endpoints" column="key" select="listEndpoints"/>
    <collection property="machineTags" column="key" select="listMachineTags"/>
    <collection property="tags" column="key" select="listTags"/>
    <collection property="identifiers" column="key" select="listIdentifiers"/>
    <collection property="comments" column="key" select="listComments"/>
  </resultMap>

  <sql id="WRITABLE_ORGANIZATION_FIELDS">
    key,endorsing_node_key,endorsement_approved,password,title,abbreviation,description,language,email,
    phone,homepage,logo_url,address,city,province,country,postal_code,latitude,longitude,created,created_by,
    modified,modified_by
  </sql>

  <sql id="ORGANIZATION_FIELDS">
    o.key,o.endorsing_node_key,o.endorsement_approved,o.password,o.title,o.abbreviation,o.description,o.language,o.email,
    o.phone,o.homepage,o.logo_url,o.address,o.city,o.province,o.country,o.postal_code,o.latitude,o.longitude,
    o.created,o.created_by,o.modified,o.modified_by,o.deleted
  </sql>

  <sql id="LEGACY_ORGANIZATION_BRIEF_FIELDS">
    o.key,o.title AS name
  </sql>

  <!--
    Simple fulltext search on the search field or contact search field.
    plainto_tsquery() is used to handle ALL stuff from the URL so the limitations are constrained by this method.
    Uses the default configuration english for the English language.
    E.g. no prefix search is supported.
   -->
  <sql id="SIMPLE_SEARCH">
    AND (
      query  @@ o.fulltext_search
      OR EXISTS(
        SELECT contact_key
        FROM organization_contact JOIN contact c ON c.key = contact_key
        WHERE organization_key=o.key AND query @@ c.fulltext_search
      )
    )
  </sql>

  <!--
    key, password, created and deleted are never changed
   -->
  <sql id="ORGANIZATION_UPDATE">
    endorsing_node_key = #{endorsingNodeKey,jdbcType=OTHER},
    endorsement_approved = #{endorsementApproved,jdbcType=BOOLEAN},
    title = #{title,jdbcType=VARCHAR},
    abbreviation = #{abbreviation,jdbcType=CHAR},
    description = #{description,jdbcType=VARCHAR},
    language = #{language,jdbcType=CHAR},
    email = #{email,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    phone = #{phone,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    homepage = #{homepage,jdbcType=ARRAY, typeHandler=UriArrayTypeHandler},
    logo_url = #{logoUrl,jdbcType=VARCHAR},
    address = #{address,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    city = #{city,jdbcType=VARCHAR},
    province = #{province,jdbcType=VARCHAR},
    country = upper(#{country,jdbcType=CHAR}),
    postal_code = #{postalCode,jdbcType=VARCHAR},
    latitude = #{latitude,jdbcType=DOUBLE},
    longitude = #{longitude,jdbcType=DOUBLE},
    modified = now(),
    modified_by = #{modifiedBy},
    deleted = null <!-- if we're updating it can't be deleted -->
  </sql>

  <sql id="WRITABLE_ORGANIZATION_FIELD_TYPES">
    #{key,jdbcType=OTHER},
    #{endorsingNodeKey,jdbcType=OTHER},
    #{endorsementApproved,jdbcType=BOOLEAN},
    #{password,jdbcType=VARCHAR},
    #{title,jdbcType=VARCHAR},
    #{abbreviation,jdbcType=CHAR},
    #{description,jdbcType=VARCHAR},
    #{language,jdbcType=CHAR},
    #{email,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    #{phone,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    #{homepage,jdbcType=ARRAY, typeHandler=UriArrayTypeHandler},
    #{logoUrl,jdbcType=VARCHAR},
    #{address,jdbcType=ARRAY, typeHandler=StringArrayTypeHandler},
    #{city,jdbcType=VARCHAR},
    #{province,jdbcType=VARCHAR},
    #{country,jdbcType=CHAR},
    #{postalCode,jdbcType=VARCHAR},
    #{latitude,jdbcType=DOUBLE},
    #{longitude,jdbcType=DOUBLE},
    now(), <!-- created -->
    #{createdBy},
    now(), <!-- modified -->
    #{createdBy}
  </sql>

  <select id="countPublishedDatasets" resultType="int">
    SELECT COUNT(*) AS num_datasets FROM dataset WHERE deleted IS NULL AND publishing_organization_key = #{key}
  </select>

  <!--  Note: you can get entities which are deleted -->
  <select id="get" resultMap="ORGANIZATION_MAP">
    SELECT <include refid="ORGANIZATION_FIELDS"/>
    FROM organization o
    WHERE o.key = #{key,jdbcType=OTHER}
  </select>

  <insert id="create" parameterType="Organization">
    INSERT INTO organization(<include refid="WRITABLE_ORGANIZATION_FIELDS"/>)
    VALUES(<include refid="WRITABLE_ORGANIZATION_FIELD_TYPES"/>)
  </insert>

  <update id="update" parameterType="Organization">
    UPDATE organization
    SET <include refid="ORGANIZATION_UPDATE"/>
    WHERE key = #{key,jdbcType=OTHER}
  </update>

  <!-- For safety, should it be already deleted nothing is done -->
  <update id="delete">
    UPDATE organization
    SET deleted = now()
    WHERE key = #{key,jdbcType=OTHER} AND deleted IS NULL
  </update>

  <!-- Append safe ordering, omitting deleted entities -->
  <select id="list" resultType="Organization"  resultMap="ORGANIZATION_MAP" parameterType="Pageable">
    SELECT <include refid="ORGANIZATION_FIELDS"/>
    FROM organization o
    WHERE o.deleted IS NULL
    ORDER BY o.created DESC, o.key
    <if test="page != null" >
      LIMIT #{page.limit} OFFSET #{page.offset}
    </if>
  </select>

  <!-- Append ordering by title, omitting deleted entities. No paging, instead all organizations included in response -->
  <select id="listLegacyOrganizationsBrief" resultType="org.gbif.registry.ws.model.LegacyOrganizationBriefResponse">
    SELECT <include refid="LEGACY_ORGANIZATION_BRIEF_FIELDS"/>
    FROM organization o
    WHERE o.deleted IS NULL
    ORDER BY lower(o.title)
  </select>

  <!-- Basic paging search functionality -->
  <select id="search" resultMap="ORGANIZATION_MAP">
    SELECT <include refid="ORGANIZATION_FIELDS"/>
    FROM organization o <if test="query != null" >, plainto_tsquery('english',#{query}) AS query</if>
    WHERE deleted IS NULL
    <if test="query != null" >
      <include refid="SIMPLE_SEARCH"/>
    </if>
    ORDER BY <if test="query != null" >ts_rank_cd(o.fulltext_search, query) DESC</if>, o.created DESC, key
    <if test="page != null" >
      LIMIT #{page.limit} OFFSET #{page.offset}
    </if>
  </select>

  <select id="count" resultType="Integer">
    SELECT COUNT(*)
    FROM organization o <if test="query != null" >, plainto_tsquery('english',#{query}) AS query</if>
    WHERE deleted IS NULL
    <if test="query != null" >
      <include refid="SIMPLE_SEARCH"/>
    </if>
  </select>
{'fraction_non_alphanumeric': 0.13858807402330364, 'fraction_numerical': 0.0010966415352981493, 'mean_word_length': 4.644487427466151, 'pattern_counts': {'":': 0, '<': 159, '<?xml version=': 2, '>': 159, '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': '6965693', 'n_tokens_mistral': 4758, 'n_tokens_neox': 4579, 'n_words': 939}