code: /**
   * This file is part of Eclipse Steady.
   *
   * Licensed under the Apache License, Version 2.0 (the "License");
   * you may not use this file except in compliance with the License.
   * You may obtain a copy of the License at
   *
   *     http://www.apache.org/licenses/LICENSE-2.0
   *
   * Unless required by applicable law or agreed to in writing, software
   * distributed under the License is distributed on an "AS IS" BASIS,
   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   * See the License for the specific language governing permissions and
   * limitations under the License.
   *
   * SPDX-License-Identifier: Apache-2.0
   * SPDX-FileCopyrightText: Copyright (c) 2018-2020 SAP SE or an SAP affiliate company and Eclipse Steady contributors
   */
  package org.eclipse.steady.backend.repo;
  
  import java.util.List;
  
  import org.eclipse.steady.backend.model.AffectedConstructChange;
  import org.eclipse.steady.backend.model.Application;
  import org.eclipse.steady.backend.model.Bug;
  import org.eclipse.steady.backend.model.ConstructId;
  import org.eclipse.steady.backend.model.Library;
  import org.eclipse.steady.backend.model.LibraryId;
  import org.eclipse.steady.backend.util.ResultSetFilter;
  import org.eclipse.steady.shared.enums.ConstructType;
  import org.eclipse.steady.shared.enums.Scope;
  import org.springframework.data.jpa.repository.Query;
  import org.springframework.data.repository.CrudRepository;
  import org.springframework.data.repository.query.Param;
  import org.springframework.stereotype.Repository;
  
  /**
   * See here for JPQL: http://docs.oracle.com/javaee/6/tutorial/doc/bnbtg.html
   */
  @Repository
  // @RepositoryRestResource(collectionResourceRel = "bugss", path = "bugss")
  public interface LibraryRepository extends CrudRepository<Library, Long>, LibraryRepositoryCustom {
  
    /** Constant <code>FILTER</code> */
    public static final ResultSetFilter<Library> FILTER = new ResultSetFilter<Library>();
  
    /**
     * <p>findByDigest.</p>
     *
     * @param digest a {@link java.lang.String} object.
     * @return a {@link java.util.List} object.
     */
    @Query("SELECT l FROM Library l LEFT OUTER JOIN FETCH l.libraryId WHERE l.digest=:digest")
    List<Library> findByDigest(@Param("digest") String digest);
  
    /**
     * <p>countConstructOfLibrary.</p>
     *
     * @param id a {@link java.lang.Long} object.
     * @return a {@link java.lang.Integer} object.
     */
    @Query(
        value = "SELECT COUNT(*) FROM " + " lib_constructs  " + "  WHERE library_id =:id ",
        nativeQuery = true)
    Integer countConstructOfLibrary(@Param("id") Long id);
  
    /**
     * <p>countUsages.</p>
     *
     * @param digest a {@link java.lang.String} object.
     * @return a {@link java.lang.Integer} object.
     */
    @Query(
        value =
            "SELECT COUNT(distinct app) FROM "
                + " app_dependency  "
                + "  WHERE lib =:digest and transitive=false ",
        nativeQuery = true)
    Integer countUsages(@Param("digest") String digest);
  
    //	@Query(value="SELECT COUNT(distinct d.app) FROM "
    //			+ " Dependency d  "
    //			+ " WHERE lib =:digest and d.transitive=false AND d.scope NOT IN :exclScopes")
    //	Integer countUsages(@Param("digest") String digest,@Param("exclScopes") Dependency.Scope[]
    // exclScopes);
  
    /**
     * <p>findMostUsed.</p>
     *
     * @param num a {@link java.lang.Integer} object.
     * @return a {@link java.util.List} object.
     */
    @Query(
        value =
            "select t.lib from (SELECT COUNT(distinct app) as c,lib  FROM "
                + "    app_dependency   "
                + "   WHERE  transitive=false group by lib order by c desc) as t limit :num",
        nativeQuery = true)
    List<String> findMostUsed(@Param("num") Integer num);
  
    /**
     * <p>findMostUsed.</p>
     *
     * @param exclScopes an array of {@link org.eclipse.steady.shared.enums.Scope} objects.
     * @return a {@link java.util.List} object.
     */
    @Query(
        value =
            " SELECT d.lib.digest,COUNT(distinct d.app)  FROM    Dependency d     WHERE "
                + " transitive=false    AND d.scope NOT IN :exclScopes group by d.lib.digest order"
                + " by COUNT(distinct d.app) desc")
    List<Object[]> findMostUsed(@Param("exclScopes") Scope[] exclScopes);
  
    /**
     * Returns all {@link Bug}s relevant for the {@link Library} with the given digest.
     * A {@link Bug} is considered relevant if one or more of the {@link ConstructId}s
     * changed as part of the bug fix is contained in the {@link Library}.
     *
     * @param digest a {@link java.lang.String} object.
     * @return a {@link java.util.List} object.
     */
    @Query(
        "SELECT"
            + " DISTINCT b FROM"
            + "   Bug b"
            + "   JOIN b.constructChanges cc,"
            + "   Library l"
            + "   JOIN l.constructs lc"
            + " WHERE"
            + "       cc.constructId = lc"
            + "   AND l.digest = :digest")
    List<Bug> findBugs(@Param("digest") String digest);
  
    /**
     * Same as {@link LibraryRepository#findBugs(String)}, but offering an additional filter for selected bug ID(s).
     *
     * @param digest a {@link java.lang.String} object.
     * @param bugIds an array of {@link java.lang.String} objects.
     * @return a {@link java.util.List} object.
     */
    @Query(
        "SELECT"
            + " DISTINCT b FROM"
            + "   Bug b"
            + "   JOIN b.constructChanges cc,"
            + "   Library l"
            + "   JOIN l.constructs lc"
            + " WHERE"
            + "       cc.constructId = lc"
            + "   AND l.digest = :digest"
            + "   AND b.bugId IN :bugIds") // filter clause on bugId
    List<Bug> findBugs(@Param("digest") String digest, @Param("bugIds") String[] bugIds);
  
    /**
     * Returns all {@link ConstructId}s for the {@link Library} with the given digest and the bug with
     * the given ID that exist both in the library and the bug's change list.
     *
     * @param digest a {@link java.lang.String} object.
     * @param bugId a {@link java.lang.String} object.
     * @return a {@link java.util.List} object.
     */
    @Query(
        "SELECT"
            + " DISTINCT lc FROM"
            + "   Bug b"
            + "   JOIN b.constructChanges cc,"
            + "   Library l"
            + "   JOIN l.constructs lc"
            + " WHERE"
            + "       cc.constructId = lc"
            + "   AND l.digest  = :digest"
            + "   AND b.bugId = :bugId")
    List<ConstructId> findBuggyConstructIds(
        @Param("digest") String digest, @Param("bugId") String bugId);
  
    /**
     * Returns all {@link ConstructId}s contained in the {@link Library} with the given digest.
     *
     * @param digest a {@link java.lang.String} object.
     * @return a {@link java.util.List} object.
     */
    @Query(
        "SELECT"
            + " DISTINCT lc FROM"
            + "   Library l"
            + "   JOIN l.constructs lc"
            + " WHERE"
            + "   l.digest = :digest")
    List<ConstructId> findConstructIds(@Param("digest") String digest);
  
    /**
     * Returns all {@link ConstructId}s contained in the {@link Library} with the given digest and of the given type.
     *
     * @param digest a {@link java.lang.String} object.
     * @param type a {@link org.eclipse.steady.shared.enums.ConstructType} object.
     * @return a {@link java.util.List} object.
     */
    @Query(
        "SELECT"
            + " DISTINCT lc FROM"
            + "   Library l"
            + "   JOIN l.constructs lc"
            + " WHERE"
            + "   l.digest = :digest AND lc.type=:type")
    List<ConstructId> findConstructIdsOfType(
        @Param("digest") String digest, @Param("type") ConstructType type);
  
    /**
     * <p>findAffCCs.</p>
     *
     * @param digest a {@link java.lang.String} object.
     * @param bugId a {@link java.lang.String} object.
     * @return a {@link java.util.List} object.
     */
    @Query(
        "SELECT"
            + " DISTINCT affcc FROM"
            + "   AffectedConstructChange affcc"
            + "   JOIN affcc.affectedLib afflib,"
            + "	  Bug b"
            + "   JOIN b.constructChanges cc"
            + " WHERE"
            + "       affcc.cc = cc"
            + "   AND afflib.lib.digest = :digest"
            + "   AND b.bugId = :bugId")
    List<AffectedConstructChange> findAffCCs(
        @Param("digest") String digest, @Param("bugId") String bugId);
  
    /**
     * <p>findJPQLVulnerableLibrariesByBug.</p>
     *
     * @param bugId a {@link java.lang.String} object.
     * @return a {@link java.util.List} object.
     */
    @Query(
        "SELECT"
            + "   DISTINCT l FROM"
            + "	  Library l "
            + "   LEFT OUTER JOIN FETCH"
            + "   l.libraryId lid "
            + "   JOIN "
            + "   l.constructs lc,"
            + "	  Bug b"
            + "   JOIN "
            + "   b.constructChanges cc "
            + "	  WHERE b.bugId = :bugId"
            + "   AND lc = cc.constructId"
            + "   AND (NOT lc.type='PACK' " // Java + Python exception
            + "   OR NOT EXISTS (SELECT 1 FROM ConstructChange cc1 JOIN cc1.constructId c1 WHERE"
            + " cc1.bug=cc.bug AND NOT c1.type='PACK' AND NOT c1.qname LIKE '%test%' AND NOT"
            + " c1.qname LIKE '%Test%' and NOT cc1.constructChangeType='ADD') )    AND NOT"
            + " (lc.type='MODU' AND (lc.qname='setup' OR lc.qname='tests' OR"
            + " lc.qname='test.__init__'))" // Python-specific exception: setup.py is virtually
    // everywhere, considering it would bring far too many
    // FPs. Similarly tests.py originates such a generic
    // module that would bring up too many FPs
    )
    List<Library> findJPQLVulnerableLibrariesByBug(@Param("bugId") String bugId);
  
    //	@Query(value= "select distinct s.digest from " +
    //			"(select y.digest,y.library_id_id,y.m,i.n from " +
    // "(select v.digest as digest,v.library_id_id,count(u.id) as m from "+
    // "(select distinct a.digest,a.library_id_id,d.id  from lib as a join lib_constructs as b on
    // a.id=b.lib_id join construct_id as d on b.constructs_id=d.id where d.type='PACK' ) as v "+
    // "join "+
    // "(select c.id from lib as l join lib_constructs as lc on l.id=lc.lib_id join construct_id as c
    // on lc.constructs_id=c.id where l.digest=:digest and c.type='PACK') as u on v.id=u.id group by
    // v.digest,v.library_id_id) as y , "+
    // " (select count(*) as n from lib as l join lib_constructs as lc on l.id=lc.lib_id join
    // construct_id as c on lc.constructs_id=c.id "+
    // "where l.digest=:digest and c.type='PACK' ) as i "+
    // "where (y.m > (i.n *80/100) and  y.m < (i.n *120/100))) as s join library_id as lid on
    // s.library_id_id=lid.id ",nativeQuery=true)
    /**
     * <p>findDigestSamePack.</p>
     *
     * @param digest a {@link java.lang.String} object.
     * @return a {@link java.util.List} object.
     */
    @Query(
        value =
            "select distinct s.digest  from  (select candidate.digest, candidate.id,"
                + " candidate.mvn_group,candidate.artifact,candidate.version,count(*) as"
                + " candidate_pack_count from  (select distinct"
                + " a.digest,a.id,a.mvn_group,a.artifact,a.version   from  (select distinct"
                + " l1.digest,l1.id,lid1.mvn_group,lid1.artifact,lid1.version,c1.id  as cid  from lib"
                + " as l1   join library_id lid1 on l1.library_id_id=lid1.id  join lib_constructs as"
                + " lc1 on l1.id=lc1.library_id   join construct_id as c1 on lc1.constructs_id=c1.id"
                + " where c1.type='PACK' ) as a  join  (select c.id   from lib as l  join"
                + " lib_constructs as lc on l.id=lc.library_id  join construct_id as c on"
                + " lc.constructs_id=c.id   where l.digest=:digest and c.type='PACK') as unknown  on"
                + " a.cid=unknown.id) as candidate  join lib_constructs as lc3 on"
                + " candidate.id=lc3.library_id  join construct_id as c3 on lc3.constructs_id=c3.id  "
                + " where c3.type='PACK' group by"
                + " candidate.digest,candidate.id,candidate.mvn_group,candidate.artifact,candidate.version)"
                + " as s,  (select count(*) as unknown_pack_count  from lib as l2   join"
                + " lib_constructs as lc2 on l2.id=lc2.library_id  join construct_id as c2 on"
                + " lc2.constructs_id=c2.id   where l2.digest=:digest and c2.type='PACK') as"
                + " unknown_count  where (s.candidate_pack_count >="
                + " unknown_count.unknown_pack_count*90/100) AND (s.candidate_pack_count <="
                + " unknown_count.unknown_pack_count*100/100) ",
        nativeQuery = true)
    List<String> findDigestSamePack(@Param("digest") String digest);
  
    /**
     * <p>findByLibraryId.</p>
     *
     * @param bundledLibId a {@link org.eclipse.steady.backend.model.LibraryId} object.
     * @return a {@link java.util.List} object.
     */
    @Query("SELECT l FROM Library l WHERE l.libraryId =:bundledLibId")
    List<Library> findByLibraryId(@Param("bundledLibId") LibraryId bundledLibId);
  
    /**
     * <p>Finds libraries that are rebundled in an application dependency. It returns a list of pairs dependency, bundled library.
     *  Note that bundled libraries must have a digest and a GAV different from the one of the dependency rebundling it.</p>
     *
     * @param app a {@link org.eclipse.steady.backend.model.Application} object.
     * @return a {@link java.util.List} object.
     */
    @Query(
        value =
            "select distinct d.id as dep_id, l2.id as bundled_lib_id   from app_dependency d   "
                + " inner join lib l1 on d.lib=l1.digest inner join library_id lid1 on"
                + " l1.library_id_id =lid1.id    inner join lib_bundled_library_ids bl on"
                + " l1.id=bl.library_id inner join library_id lid2 on"
                + " bl.bundled_library_ids_id=lid2.id    inner join lib l2 on"
                + " bl.bundled_library_ids_id=l2.library_id_id    where d.app=:app and not l1.id ="
                + " l2.id and l2.wellknown_digest=true and not lid1.id=lid2.id",
        nativeQuery = true)
    List<Object[]> findBundledLibByApp(@Param("app") Application app);
  }
  