<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.col.db.mapper.DatasetMapper">

    <sql id="SELECT">
      key,
      type,
      title,
      gbif_key,
      gbif_publisher_key,
      description,
      organisation,
      contact_person,
      authors_and_editors,
      license,
      version,
      release_date,
      homepage,
      data_format,
      data_access,
      import_frequency,
      notes,
      created,
      modified,
      deleted
    </sql>

    <sql id="COLS">
      type,
      title,
      gbif_key,
      gbif_publisher_key,
      description,
      organisation,
      contact_person,
      authors_and_editors,
      license,
      version,
      release_date,
      homepage,
      data_format,
      data_access,
      import_frequency,
      notes
    </sql>

    <sql id="PROPS">
      #{type},
      #{title},
      #{gbifKey},
      #{gbifPublisherKey},
      #{description},
      #{organisation},
      #{contactPerson},
      #{authorsAndEditors, typeHandler=org.col.db.type.StringArrayTypeHandler},
      #{license},
      #{version},
      #{releaseDate},
      #{homepage},
      #{dataFormat},
      #{dataAccess},
      #{importFrequency},
      #{notes}
    </sql>

    <sql id="FROM">
        dataset d
    </sql>

    <!--  A mapping to Name, mostly auto mapped -->
    <resultMap id="datasetResultMap" type="Dataset" autoMapping="true">
        <id property="key" column="key" />
        <result property="authorsAndEditors" column="authors_and_editors" typeHandler="org.col.db.type.StringArrayTypeHandler" />
    </resultMap>

    <select id="count" parameterType="map" resultType="Integer">
        SELECT count(*)
        FROM <include refid="FROM" />
    </select>

    <select id="list" parameterType="map" resultMap="datasetResultMap">
        SELECT <include refid="SELECT" />
        FROM <include refid="FROM" />
        ORDER BY key
        <include refid="org.col.db.mapper.Common.limit"/>
    </select>

    <select id="countSearchResults" parameterType="map" resultType="Integer">
        SELECT COUNT(*)
        FROM
            (
              SELECT
                 setweight(to_tsvector(coalesce(unaccent(title),'')), 'D') ||
                 setweight(to_tsvector(coalesce(unaccent(description),'')), 'B') ||
                 setweight(to_tsvector(coalesce(unaccent(organisation),'')), 'B') ||
                 setweight(to_tsvector(coalesce(unaccent(contact_person),'')), 'A') ||
                 setweight(to_tsvector(coalesce(unaccent(array_to_string(authors_and_editors,'|')),'')), 'A') ||
                 setweight(to_tsvector(coalesce(''||gbif_key,'')), 'A') as document
              FROM dataset     
            ) AS ds
        WHERE ds.document @@ plainto_tsquery(#{q})
    </select>

    <select id="search" parameterType="map" resultMap="datasetResultMap">
        SELECT <include refid="SELECT" />
        FROM
            (
              SELECT <include refid="SELECT" />,
                 setweight(to_tsvector(coalesce(unaccent(title),'')), 'D') ||
                 setweight(to_tsvector(coalesce(unaccent(description),'')), 'B') ||
                 setweight(to_tsvector(coalesce(unaccent(organisation),'')), 'B') ||
                 setweight(to_tsvector(coalesce(unaccent(contact_person),'')), 'A') ||
                 setweight(to_tsvector(coalesce(unaccent(array_to_string(authors_and_editors,'|')),'')), 'A') ||
                 setweight(to_tsvector(coalesce(''||gbif_key,'')), 'A') as document
              FROM dataset     
            ) AS ds
        WHERE ds.document @@ plainto_tsquery(#{q})
        ORDER BY ts_rank(ds.document, plainto_tsquery(#{q})) DESC
        <include refid="org.col.db.mapper.Common.limit"/>
    </select>
    
    
    <select id="get" parameterType="map" resultMap="datasetResultMap">
      SELECT <include refid="SELECT" />
      FROM <include refid="FROM" />
      WHERE key = #{key}
    </select>

    <select id="getByGBIF" parameterType="map" resultMap="datasetResultMap">
        SELECT <include refid="SELECT" />
        FROM <include refid="FROM" />
        WHERE gbif_key = #{key}
    </select>

    <insert id="create" parameterType="Dataset" useGeneratedKeys="true" keyProperty="key">
      INSERT INTO dataset (<include refid="COLS" />, created)
             VALUES (<include refid="PROPS" />, now())
    </insert>

    <update id="update" parameterType="Dataset">
        UPDATE dataset
        SET (<include refid="COLS" />, modified) = (<include refid="PROPS" />, now())
        WHERE key = #{key}
    </update>

    <update id="delete" parameterType="map">
        UPDATE dataset SET deleted = now()
        WHERE key = #{key}
    </update>
</mapper><commit_msg>Remove existing data before imports<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.col.db.mapper.DatasetMapper">

    <sql id="SELECT">
      key,
      type,
      title,
      gbif_key,
      gbif_publisher_key,
      description,
      organisation,
      contact_person,
      authors_and_editors,
      license,
      version,
      release_date,
      homepage,
      data_format,
      data_access,
      import_frequency,
      notes,
      created,
      modified,
      deleted
    </sql>

    <sql id="COLS">
      type,
      title,
      gbif_key,
      gbif_publisher_key,
      description,
      organisation,
      contact_person,
      authors_and_editors,
      license,
      version,
      release_date,
      homepage,
      data_format,
      data_access,
      import_frequency,
      notes
    </sql>

    <sql id="PROPS">
      #{type},
      #{title},
      #{gbifKey},
      #{gbifPublisherKey},
      #{description},
      #{organisation},
      #{contactPerson},
      #{authorsAndEditors, typeHandler=org.col.db.type.StringArrayTypeHandler},
      #{license},
      #{version},
      #{releaseDate},
      #{homepage},
      #{dataFormat},
      #{dataAccess},
      #{importFrequency},
      #{notes}
    </sql>

    <sql id="FROM">
        dataset d
    </sql>

    <!--  A mapping to Name, mostly auto mapped -->
    <resultMap id="datasetResultMap" type="Dataset" autoMapping="true">
        <id property="key" column="key" />
        <result property="authorsAndEditors" column="authors_and_editors" typeHandler="org.col.db.type.StringArrayTypeHandler" />
    </resultMap>

    <select id="count" parameterType="map" resultType="Integer">
        SELECT count(*)
        FROM <include refid="FROM" />
    </select>

    <select id="list" parameterType="map" resultMap="datasetResultMap">
        SELECT <include refid="SELECT" />
        FROM <include refid="FROM" />
        ORDER BY key
        <include refid="org.col.db.mapper.Common.limit"/>
    </select>

    <select id="countSearchResults" parameterType="map" resultType="Integer">
        SELECT COUNT(*)
        FROM
            (
              SELECT
                 setweight(to_tsvector(coalesce(unaccent(title),'')), 'D') ||
                 setweight(to_tsvector(coalesce(unaccent(description),'')), 'B') ||
                 setweight(to_tsvector(coalesce(unaccent(organisation),'')), 'B') ||
                 setweight(to_tsvector(coalesce(unaccent(contact_person),'')), 'A') ||
                 setweight(to_tsvector(coalesce(unaccent(array_to_string(authors_and_editors,'|')),'')), 'A') ||
                 setweight(to_tsvector(coalesce(''||gbif_key,'')), 'A') as document
              FROM dataset     
            ) AS ds
        WHERE ds.document @@ plainto_tsquery(#{q})
    </select>

    <select id="search" parameterType="map" resultMap="datasetResultMap">
        SELECT <include refid="SELECT" />
        FROM
            (
              SELECT <include refid="SELECT" />,
                 setweight(to_tsvector(coalesce(unaccent(title),'')), 'D') ||
                 setweight(to_tsvector(coalesce(unaccent(description),'')), 'B') ||
                 setweight(to_tsvector(coalesce(unaccent(organisation),'')), 'B') ||
                 setweight(to_tsvector(coalesce(unaccent(contact_person),'')), 'A') ||
                 setweight(to_tsvector(coalesce(unaccent(array_to_string(authors_and_editors,'|')),'')), 'A') ||
                 setweight(to_tsvector(coalesce(''||gbif_key,'')), 'A') as document
              FROM dataset     
            ) AS ds
        WHERE ds.document @@ plainto_tsquery(#{q})
        ORDER BY ts_rank(ds.document, plainto_tsquery(#{q})) DESC
        <include refid="org.col.db.mapper.Common.limit"/>
    </select>
    
    
    <select id="get" parameterType="map" resultMap="datasetResultMap">
      SELECT <include refid="SELECT" />
      FROM <include refid="FROM" />
      WHERE key = #{key}
    </select>

    <select id="getByGBIF" parameterType="map" resultMap="datasetResultMap">
        SELECT <include refid="SELECT" />
        FROM <include refid="FROM" />
        WHERE gbif_key = #{key}
    </select>

    <insert id="create" parameterType="Dataset" useGeneratedKeys="true" keyProperty="key">
      INSERT INTO dataset (<include refid="COLS" />, created)
             VALUES (<include refid="PROPS" />, now())
    </insert>

    <update id="update" parameterType="Dataset">
        UPDATE dataset
        SET (<include refid="COLS" />, modified) = (<include refid="PROPS" />, now())
        WHERE key = #{key}
    </update>

    <update id="delete" parameterType="map">
        UPDATE dataset SET deleted = now()
        WHERE key = #{key}
    </update>

    <delete id="truncateDatasetData" parameterType="map">
        DELETE FROM distribution_references
        WHERE dataset_key = #{key};

        DELETE FROM distribution
        WHERE dataset_key = #{key};

        DELETE FROM vernacular_name_references
        WHERE dataset_key = #{key};

        DELETE FROM vernacular_name
        WHERE dataset_key = #{key};

        DELETE FROM synonyms
        WHERE dataset_key = #{key};

        DELETE FROM taxon_references
        WHERE dataset_key = #{key};

        DELETE FROM taxon
        WHERE dataset_key = #{key};

        DELETE FROM name_act
        WHERE dataset_key = #{key};

        DELETE FROM name
        WHERE dataset_key = #{key};

        DELETE FROM verbatim_record
        WHERE dataset_key = #{key};
    </delete>

</mapper>
{'fraction_non_alphanumeric': 0.14522111269614835, 'fraction_numerical': 0.0011412268188302425, 'mean_word_length': 2.417614559636009, 'pattern_counts': {'":': 0, '<': 105, '<?xml version=': 2, '>': 105, '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': '4044472', 'n_tokens_mistral': 3302, 'n_tokens_neox': 3214, 'n_words': 601}