Revize 7bf2464a

Added controller and some tests for external catalog

re #9624

backend/src/main/java/cz/zcu/kiv/backendapi/catalog/CatalogItem.java

@@ -119 +119 @@
 
         this.name = csvFields.get(1);
         Set<String> stringList = processListField(csvFields.get(2));
+        stringList.add(this.name);
         this.alternativeNames = stringList.stream().map(s -> new AlternativeName(s, this)).collect(Collectors.toSet());
 
         this.certainty = processIntField(csvFields.get(3));

backend/src/main/java/cz/zcu/kiv/backendapi/catalog/CatalogItemServiceImpl.java

@@ -224 +224 @@
     }
 
     /**
-     * Converts catalog DTO to catalog entity
+     * Converts catalog item DTO to catalog item entity
      *
-     * @param catalogItemDto catalog DTO
-     * @param catalogItem    catalog entity
+     * @param catalogItemDto catalog item DTO
+     * @param catalogItem    catalog item entity
      */
     private void convertDtoToEntity(CatalogItemDto catalogItemDto, CatalogItem catalogItem) {
         catalogItem.setName(catalogItemDto.getName());
@@ -247 +247 @@
         catalogItem.getCountries().addAll(catalogItemDto.getCountries()
                 .stream().map(s -> new Country(s, catalogItem)).collect(Collectors.toSet()));
 
+        catalogItemDto.getAlternativeNames().add(catalogItemDto.getName());
         catalogItem.getAlternativeNames().clear();
         catalogItem.getAlternativeNames().addAll(catalogItemDto.getAlternativeNames()
                 .stream().map(s -> new AlternativeName(s, catalogItem)).collect(Collectors.toSet()));
@@ -259 +260 @@
     }
 
     /**
-     * Converts catalog entity to catalog DTO
+     * Converts catalog item entity to catalog item DTO
      *
-     * @param catalogItem catalog entity
-     * @return catalog DTO
+     * @param catalogItem catalog item entity
+     * @return catalog item DTO
      */
     private CatalogItemDto convertEntityToDto(CatalogItem catalogItem) {
         CatalogItemDto catalogItemDto = new CatalogItemDto();

backend/src/main/java/cz/zcu/kiv/backendapi/external/ExternalCatalogController.java

@@ -1 +1 @@
 package cz.zcu.kiv.backendapi.external;
 
+import io.swagger.v3.oas.annotations.Operation;
 import lombok.RequiredArgsConstructor;
-import org.springframework.web.bind.annotation.PostMapping;
-import org.springframework.web.bind.annotation.RequestMapping;
-import org.springframework.web.bind.annotation.RestController;
+import org.springframework.http.HttpStatus;
+import org.springframework.http.ResponseEntity;
+import org.springframework.web.bind.annotation.*;
+
+import java.util.List;
 
 /**
  * Controller for external catalog
@@ -16 +19 @@
     /**
      * External catalog service
      */
-    private final IExternalCatalogService externalCatalogService;
+    private final IExternalCatalogItemService externalCatalogService;
 
     @PostMapping("")
+    @Operation(summary = "updates external catalog")
     public void updateCatalog() {
         externalCatalogService.updateCatalog();
     }
+
+    /**
+     * Returns external catalog items satisfying given filter
+     *
+     * @param name   name
+     * @param source source
+     * @return list of external catalog items satisfying given filter
+     */
+    @GetMapping("")
+    @Operation(summary = "returns external catalog items based on filter")
+    public ResponseEntity<List<ExternalCatalogItem>> getCatalog(@RequestParam String name, @RequestParam ExternalSource source) {
+        return new ResponseEntity<>(externalCatalogService.getCatalog(name, source), HttpStatus.OK);
+    }
 }

backend/src/main/java/cz/zcu/kiv/backendapi/external/ExternalCatalogItem.java

@@ -1 +1 @@
 package cz.zcu.kiv.backendapi.external;
 
-import lombok.EqualsAndHashCode;
-import lombok.Getter;
-import lombok.NoArgsConstructor;
-import lombok.Setter;
+import io.swagger.v3.oas.annotations.media.Schema;
+import lombok.*;
 
 import javax.persistence.*;
 import java.util.*;
@@ -18 +16 @@
 @Setter
 @EqualsAndHashCode(onlyExplicitlyIncluded = true)
 @NoArgsConstructor
+@AllArgsConstructor
 @Entity
 @Table(name = "external_catalog_items")
+@Schema(name = "ExternalCatalogItemDto")
 public class ExternalCatalogItem {
     /**
      * Map where key is country code and value is name of country
@@ -116 +116 @@
     @Column(length = 20000)
     private Set<String> names = new HashSet<>();
 
+
     /**
      * Feature code
      */

backend/src/main/java/cz/zcu/kiv/backendapi/external/ExternalCatalogItemItemServiceImpl.java

@@ +1 @@
+package cz.zcu.kiv.backendapi.external;
+
+import com.zaxxer.hikari.HikariDataSource;
+import lombok.RequiredArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
+import org.apache.commons.csv.CSVFormat;
+import org.apache.commons.csv.CSVParser;
+import org.apache.commons.csv.CSVRecord;
+import org.apache.tomcat.util.http.fileupload.FileUtils;
+import org.springframework.stereotype.Service;
+import org.springframework.transaction.annotation.Transactional;
+
+import javax.persistence.Table;
+import java.io.*;
+import java.net.URL;
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.nio.file.Paths;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.concurrent.Callable;
+import java.util.concurrent.ExecutorService;
+import java.util.concurrent.Executors;
+import java.util.stream.Collectors;
+import java.util.zip.GZIPInputStream;
+import java.util.zip.ZipEntry;
+import java.util.zip.ZipInputStream;
+
+/**
+ * External catalog item service implementation
+ */
+@Service
+@Transactional
+@RequiredArgsConstructor
+@Slf4j
+public class ExternalCatalogItemItemServiceImpl implements IExternalCatalogItemService {
+    /**
+     * Buffer size
+     */
+    public static final int BUFFER_SIZE = 1024;
+    /**
+     * Directory where files for external directory will be stored
+     */
+    private static final String DIRECTORY_FOR_EXTERNAL_FILES = "sources";
+    /**
+     * URL for Pleiades file
+     */
+    private static final String PLEIADES_FILE_URL = "https://atlantides.org/downloads/pleiades/dumps/pleiades-names-latest.csv.gz";
+    /**
+     * Name of Pleiades file
+     */
+    private static final String PLEIADES_FILE_NAME = "pleiades-names-latest.csv";
+    /**
+     * URL for Pleiades file – needs to be formatted (more sources)
+     */
+    private static final String GEONAMES_FILE_URL = "https://download.geonames.org/export/dump/%s.zip";
+    /**
+     * Name of GeoNames file – needs to be formatted (more sources)
+     */
+    private static final String GEONAMES_FILE_NAME = "%s.txt";
+    /**
+     * URL for CIGS file
+     */
+    private static final String CIGS_FILE_URL = "https://zenodo.org/record/5642899/files/CIGS_v1_4_20211101.csv";
+    /**
+     * Name of CIGS file
+     */
+    private static final String CIGS_FILE_NAME = "CIGS_v1_4_20211101.csv";
+    /**
+     * Batch size for saving items
+     */
+    private static final int BATCH_SIZE = 5000;
+
+    /**
+     * Regex for one arbitrary character in string
+     */
+    private static final String WILDCARD_CHARACTER_REGEX = "\\?";
+
+    /**
+     * Regex for one arbitrary character in string used in SQL
+     */
+    private static final String WILDCARD_CHARACTER_REGEX_SQL = "_";
+
+    /**
+     * Regex for any number of arbitrary characters in string
+     */
+    private static final String WILDCARD_CHARACTERS_REGEX = "\\*";
+
+    /**
+     * Regex for any number of arbitrary characters in string used in SQL
+     */
+    private static final String WILDCARD_CHARACTERS_REGEX_SQL = "%";
+
+    /**
+     * External catalog repository
+     */
+    private final ExternalCatalogItemRepository externalCatalogItemRepository;
+
+    /**
+     * Hikari data source
+     */
+    private final HikariDataSource hikariDataSource;
+
+    /**
+     * Set to string convertor
+     */
+    private final SetToStringConverter setToStringConverter;
+
+
+    @Override
+    public void updateCatalog() {
+        log.info("Updating external catalog");
+        try {
+            Files.createDirectories(Paths.get(DIRECTORY_FOR_EXTERNAL_FILES)); // creates directory if not exists
+            FileUtils.cleanDirectory(new File(DIRECTORY_FOR_EXTERNAL_FILES)); // cleans the directory
+            externalCatalogItemRepository.deleteAll(); // clears database – updated list will be stored later
+            addPleiadesSource();
+            addGeonamesSources();
+            addCigsSources();
+        } catch (Exception e) {
+            e.printStackTrace();
+        }
+        log.info("External catalog updated");
+    }
+
+    @Override
+    public List<ExternalCatalogItem> getCatalog(String name, ExternalSource source) {
+        name = WILDCARD_CHARACTERS_REGEX_SQL +
+                name.replaceAll(WILDCARD_CHARACTER_REGEX, WILDCARD_CHARACTER_REGEX_SQL).replaceAll(WILDCARD_CHARACTERS_REGEX, WILDCARD_CHARACTERS_REGEX_SQL) +
+                WILDCARD_CHARACTERS_REGEX_SQL;
+        return externalCatalogItemRepository.getFilteredExternalCatalog(name, source.name());
+    }
+
+    /**
+     * Downloads, extracts and reads Pleiades sources and saves them to database
+     */
+    private void addPleiadesSource() {
+        List<ExternalCatalogItem> externalCatalogItems = new ArrayList<>();
+        byte[] buffer = new byte[BUFFER_SIZE];
+        File pleiadesFile = new File(new File(DIRECTORY_FOR_EXTERNAL_FILES), PLEIADES_FILE_NAME);
+        try (InputStream fileIn = new URL(PLEIADES_FILE_URL).openStream();
+             GZIPInputStream gZIPInputStream = new GZIPInputStream(fileIn);
+             FileOutputStream fileOutputStream = new FileOutputStream(pleiadesFile)) {
+
+            int bytes_read;
+
+            while ((bytes_read = gZIPInputStream.read(buffer)) > 0) {
+                fileOutputStream.write(buffer, 0, bytes_read);
+            }
+
+            log.info("The Pleiades file was decompressed successfully");
+
+        } catch (IOException ex) {
+            ex.printStackTrace();
+        }
+
+        try (InputStream csvData = new FileInputStream(pleiadesFile)) {
+            CSVParser parser = CSVParser.parse(csvData, StandardCharsets.UTF_8, CSVFormat.Builder.create(CSVFormat.DEFAULT)
+                    .setHeader()
+                    .setSkipHeaderRecord(true)
+                    .build());
+            for (CSVRecord csvRecord : parser) {
+                ExternalCatalogItem e = new ExternalCatalogItem(csvRecord.toList(), ExternalSource.PLEIADES);
+                externalCatalogItems.add(e);
+            }
+
+        } catch (IOException ex) {
+            ex.printStackTrace();
+        }
+        saveAllWithThreads(externalCatalogItems);
+    }
+
+    /**
+     * Downloads, extracts and reads GeoNames sources and saves them to database
+     */
+    private void addGeonamesSources() {
+        byte[] buffer = new byte[BUFFER_SIZE];
+        for (String countryCode : ExternalCatalogItem.COUNTRY_CODES.keySet()) {
+            List<ExternalCatalogItem> externalCatalogItems = new ArrayList<>();
+            // Downloads file from URL and extracts it
+            String url = String.format(GEONAMES_FILE_URL, countryCode);
+            try (ZipInputStream zis = new ZipInputStream(new URL(url).openStream())) {
+                ZipEntry zipEntry = zis.getNextEntry();
+                while (zipEntry != null) {
+                    FileOutputStream fileOutputStream = new FileOutputStream(new File(new File(DIRECTORY_FOR_EXTERNAL_FILES), zipEntry.getName()));
+                    int bytes_read;
+                    while ((bytes_read = zis.read(buffer)) > 0) {
+                        fileOutputStream.write(buffer, 0, bytes_read);
+                    }
+                    zipEntry = zis.getNextEntry();
+                    fileOutputStream.close();
+                }
+            } catch (IOException e) {
+                e.printStackTrace();
+            }
+
+            log.info("The GeoNames file with country code " + countryCode + " was decompressed successfully");
+
+            // Reads file and adds catalog items to list
+            File geoNamesFile = new File(new File(DIRECTORY_FOR_EXTERNAL_FILES), String.format(GEONAMES_FILE_NAME, countryCode));
+            try (BufferedReader reader = new BufferedReader(new FileReader(geoNamesFile))) {
+                String line;
+                while ((line = reader.readLine()) != null) {
+                    ExternalCatalogItem e = new ExternalCatalogItem(List.of(line.split("\t")), ExternalSource.GEONAMES);
+                    externalCatalogItems.add(e);
+                }
+            } catch (IOException e) {
+                e.printStackTrace();
+            }
+            saveAllWithThreads(externalCatalogItems);
+        }
+    }
+
+    /**
+     * Downloads and reads CIGS sources and saves them to database
+     */
+    private void addCigsSources() {
+        List<ExternalCatalogItem> externalCatalogItems = new ArrayList<>();
+        byte[] buffer = new byte[BUFFER_SIZE];
+        File cigsFile = new File(new File(DIRECTORY_FOR_EXTERNAL_FILES), CIGS_FILE_NAME);
+
+        try (InputStream inputStream = new URL(CIGS_FILE_URL).openStream();
+             FileOutputStream fileOutputStream = new FileOutputStream(cigsFile)) {
+            int bytes_read;
+
+            while ((bytes_read = inputStream.read(buffer)) > 0) {
+
+                fileOutputStream.write(buffer, 0, bytes_read);
+            }
+        } catch (IOException e) {
+            e.printStackTrace();
+        }
+
+        log.info("The CIGS file was downloaded successfully");
+
+        try (InputStream csvData = new FileInputStream(cigsFile)) {
+            CSVParser parser = CSVParser.parse(csvData, StandardCharsets.UTF_8, CSVFormat.Builder.create(CSVFormat.DEFAULT)
+                    .setHeader()
+                    .setSkipHeaderRecord(true)
+                    .build());
+            for (CSVRecord csvRecord : parser) {
+                ExternalCatalogItem e = new ExternalCatalogItem(csvRecord.toList(), ExternalSource.CIGS);
+                externalCatalogItems.add(e);
+            }
+        } catch (IOException e) {
+            e.printStackTrace();
+        }
+        saveAllWithThreads(externalCatalogItems);
+    }
+
+    /**
+     * Creates list of lists of external catalog items divided by batch size
+     *
+     * @param externalCatalogItems list of external catalog items
+     * @return divided list of lists of external catalog items
+     */
+    private List<List<ExternalCatalogItem>> createSublist(List<ExternalCatalogItem> externalCatalogItems) {
+        List<List<ExternalCatalogItem>> listOfSubList = new ArrayList<>();
+        for (int i = 0; i < externalCatalogItems.size(); i += BATCH_SIZE) {
+            if (i + BATCH_SIZE <= externalCatalogItems.size()) {
+                listOfSubList.add(externalCatalogItems.subList(i, i + BATCH_SIZE));
+            } else {
+                listOfSubList.add(externalCatalogItems.subList(i, externalCatalogItems.size()));
+            }
+        }
+        return listOfSubList;
+    }
+
+    /**
+     * Divides list of external catalog items to sublist, creates threads (for saving sublists in batch) and executes them
+     *
+     * @param externalCatalogItems list of external catalog items
+     */
+    private void saveAllWithThreads(List<ExternalCatalogItem> externalCatalogItems) {
+        ExecutorService executorService = Executors.newFixedThreadPool(hikariDataSource.getMaximumPoolSize());
+        List<List<ExternalCatalogItem>> subList = createSublist(externalCatalogItems);
+        List<Callable<Void>> callables = subList.stream().map(sublist ->
+                (Callable<Void>) () -> {
+                    saveAllInBatch(sublist);
+                    return null;
+                }).collect(Collectors.toList());
+        try {
+            executorService.invokeAll(callables);
+        } catch (InterruptedException e) {
+            e.printStackTrace();
+        }
+    }
+
+    /**
+     * Saves external catalog items in batch
+     *
+     * @param externalCatalogItems list of external catalog items
+     */
+    private void saveAllInBatch(List<ExternalCatalogItem> externalCatalogItems) {
+        String sqlExternalCatalogItem = String.format("INSERT INTO %s (id, external_source, latitude, longitude, location_precision, max_date, " +
+                "min_date, time_period_keys, pid, names, feature_code, country, accuracy, geoname_id, pleiades_id, osm_id) " +
+                "VALUES (?, ?, ?, ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?)", ExternalCatalogItem.class.getAnnotation(Table.class).name());
+        try (Connection connection = hikariDataSource.getConnection();
+             PreparedStatement statementExternalCatalogItem = connection.prepareStatement(sqlExternalCatalogItem)) {
+            int counter = 0;
+            for (ExternalCatalogItem item : externalCatalogItems) {
+                statementExternalCatalogItem.clearParameters();
+                statementExternalCatalogItem.setObject(1, item.getId());
+                statementExternalCatalogItem.setString(2, item.getExternalSource().name());
+                statementExternalCatalogItem.setObject(3, item.getLatitude());
+                statementExternalCatalogItem.setObject(4, item.getLongitude());
+                statementExternalCatalogItem.setString(5, item.getLocationPrecision());
+                statementExternalCatalogItem.setObject(6, item.getMaxDate());
+                statementExternalCatalogItem.setObject(7, item.getMinDate());
+                statementExternalCatalogItem.setString(8, item.getTimePeriodKeys());
+                statementExternalCatalogItem.setString(9, item.getPid());
+                statementExternalCatalogItem.setString(10, setToStringConverter.convertToDatabaseColumn(item.getNames()));
+                statementExternalCatalogItem.setString(11, item.getFeatureCode());
+                statementExternalCatalogItem.setString(12, item.getCountry());
+                statementExternalCatalogItem.setObject(13, item.getAccuracy());
+                statementExternalCatalogItem.setObject(14, item.getGeonameId());
+                statementExternalCatalogItem.setObject(15, item.getPleiadesId());
+                statementExternalCatalogItem.setObject(16, item.getOsmId());
+                statementExternalCatalogItem.addBatch();
+
+                counter++;
+                if (counter % BATCH_SIZE == 0 || counter == externalCatalogItems.size()) {
+                    statementExternalCatalogItem.executeBatch();
+                    statementExternalCatalogItem.clearBatch();
+                }
+            }
+        } catch (Exception e) {
+            e.printStackTrace();
+        }
+    }
+}

backend/src/main/java/cz/zcu/kiv/backendapi/external/ExternalCatalogItemRepository.java

@@ +1 @@
+package cz.zcu.kiv.backendapi.external;
+
+import org.springframework.data.jpa.repository.JpaRepository;
+import org.springframework.data.jpa.repository.Query;
+import org.springframework.stereotype.Repository;
+
+import java.util.List;
+import java.util.UUID;
+
+/**
+ * External catalog repository
+ */
+@Repository
+public interface ExternalCatalogItemRepository extends JpaRepository<ExternalCatalogItem, UUID> {
+
+    /**
+     * Returns list of external catalog items satisfying given filter
+     *
+     * @param name           name
+     * @param externalSource source
+     * @return list of external catalog items satisfying given filter
+     */
+    @Query(value = "SELECT * FROM external_catalog_items e WHERE UPPER(e.names) LIKE UPPER(?1) AND e.external_source = ?2", nativeQuery = true)
+    List<ExternalCatalogItem> getFilteredExternalCatalog(String name, String externalSource);
+}

backend/src/main/java/cz/zcu/kiv/backendapi/external/ExternalCatalogRepository.java

@@ -1 @@
-package cz.zcu.kiv.backendapi.external;
-
-import org.springframework.data.jpa.repository.JpaRepository;
-import org.springframework.stereotype.Repository;
-
-import java.util.UUID;
-
-/**
- * External catalog repository
- */
-@Repository
-public interface ExternalCatalogRepository extends JpaRepository<ExternalCatalogItem, UUID> {
-}

backend/src/main/java/cz/zcu/kiv/backendapi/external/ExternalCatalogServiceImpl.java

@@ -1 @@
-package cz.zcu.kiv.backendapi.external;
-
-import com.zaxxer.hikari.HikariDataSource;
-import lombok.RequiredArgsConstructor;
-import lombok.extern.slf4j.Slf4j;
-import org.apache.commons.csv.CSVFormat;
-import org.apache.commons.csv.CSVParser;
-import org.apache.commons.csv.CSVRecord;
-import org.apache.tomcat.util.http.fileupload.FileUtils;
-import org.springframework.stereotype.Service;
-import org.springframework.transaction.annotation.Transactional;
-
-import javax.persistence.Table;
-import java.io.*;
-import java.net.URL;
-import java.nio.charset.StandardCharsets;
-import java.nio.file.Files;
-import java.nio.file.Paths;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.util.ArrayList;
-import java.util.List;
-import java.util.concurrent.Callable;
-import java.util.concurrent.ExecutorService;
-import java.util.concurrent.Executors;
-import java.util.stream.Collectors;
-import java.util.zip.GZIPInputStream;
-import java.util.zip.ZipEntry;
-import java.util.zip.ZipInputStream;
-
-/**
- * External catalog service implementation
- */
-@Service
-@Transactional
-@RequiredArgsConstructor
-@Slf4j
-public class ExternalCatalogServiceImpl implements IExternalCatalogService {
-    /**
-     * Buffer size
-     */
-    public static final int BUFFER_SIZE = 1024;
-    /**
-     * Directory where files for external directory will be stored
-     */
-    private static final String DIRECTORY_FOR_EXTERNAL_FILES = "sources";
-    /**
-     * URL for Pleiades file
-     */
-    private static final String PLEIADES_FILE_URL = "https://atlantides.org/downloads/pleiades/dumps/pleiades-names-latest.csv.gz";
-    /**
-     * Name of Pleiades file
-     */
-    private static final String PLEIADES_FILE_NAME = "pleiades-names-latest.csv";
-    /**
-     * URL for Pleiades file – needs to be formatted (more sources)
-     */
-    private static final String GEONAMES_FILE_URL = "https://download.geonames.org/export/dump/%s.zip";
-    /**
-     * Name of GeoNames file – needs to be formatted (more sources)
-     */
-    private static final String GEONAMES_FILE_NAME = "%s.txt";
-    /**
-     * URL for CIGS file
-     */
-    private static final String CIGS_FILE_URL = "https://zenodo.org/record/5642899/files/CIGS_v1_4_20211101.csv";
-    /**
-     * Name of CIGS file
-     */
-    private static final String CIGS_FILE_NAME = "CIGS_v1_4_20211101.csv";
-    /**
-     * Batch size for saving items
-     */
-    private static final int BATCH_SIZE = 5000;
-
-    /**
-     * External catalog repository
-     */
-    private final ExternalCatalogRepository externalCatalogRepository;
-
-    /**
-     * Hikari data source
-     */
-    private final HikariDataSource hikariDataSource;
-
-
-    @Override
-    public void updateCatalog() {
-        log.info("Updating external catalog");
-        try {
-            Files.createDirectories(Paths.get(DIRECTORY_FOR_EXTERNAL_FILES)); // creates directory if not exists
-            FileUtils.cleanDirectory(new File(DIRECTORY_FOR_EXTERNAL_FILES)); // cleans the directory
-            externalCatalogRepository.deleteAll(); // clears database – updated list will be stored later
-            addPleiadesSource();
-            addGeonamesSources();
-            addCigsSources();
-        } catch (IOException e) {
-            e.printStackTrace();
-        }
-        log.info("External catalog updated");
-    }
-
-    /**
-     * Downloads, extracts and reads Pleiades sources and saves them to database
-     */
-    private void addPleiadesSource() {
-        List<ExternalCatalogItem> externalCatalogItems = new ArrayList<>();
-        byte[] buffer = new byte[BUFFER_SIZE];
-        File pleiadesFile = new File(new File(DIRECTORY_FOR_EXTERNAL_FILES), PLEIADES_FILE_NAME);
-        try (InputStream fileIn = new URL(PLEIADES_FILE_URL).openStream();
-             GZIPInputStream gZIPInputStream = new GZIPInputStream(fileIn);
-             FileOutputStream fileOutputStream = new FileOutputStream(pleiadesFile)) {
-
-            int bytes_read;
-
-            while ((bytes_read = gZIPInputStream.read(buffer)) > 0) {
-                fileOutputStream.write(buffer, 0, bytes_read);
-            }
-
-            log.info("The Pleiades file was decompressed successfully");
-
-        } catch (IOException ex) {
-            ex.printStackTrace();
-        }
-
-        try (InputStream csvData = new FileInputStream(pleiadesFile)) {
-            CSVParser parser = CSVParser.parse(csvData, StandardCharsets.UTF_8, CSVFormat.Builder.create(CSVFormat.DEFAULT)
-                    .setHeader()
-                    .setSkipHeaderRecord(true)
-                    .build());
-            for (CSVRecord csvRecord : parser) {
-                externalCatalogItems.add(new ExternalCatalogItem(csvRecord.toList(), ExternalSource.PLEIADES));
-            }
-
-        } catch (IOException ex) {
-            ex.printStackTrace();
-        }
-        saveAllWithThreads(externalCatalogItems);
-    }
-
-    /**
-     * Downloads, extracts and reads GeoNames sources and saves them to database
-     */
-    private void addGeonamesSources() {
-        byte[] buffer = new byte[BUFFER_SIZE];
-        for (String countryCode : ExternalCatalogItem.COUNTRY_CODES.keySet()) {
-            List<ExternalCatalogItem> externalCatalogItems = new ArrayList<>();
-            // Downloads file from URL and extracts it
-            String url = String.format(GEONAMES_FILE_URL, countryCode);
-            try (ZipInputStream zis = new ZipInputStream(new URL(url).openStream())) {
-                ZipEntry zipEntry = zis.getNextEntry();
-                while (zipEntry != null) {
-                    FileOutputStream fileOutputStream = new FileOutputStream(new File(new File(DIRECTORY_FOR_EXTERNAL_FILES), zipEntry.getName()));
-                    int bytes_read;
-                    while ((bytes_read = zis.read(buffer)) > 0) {
-                        fileOutputStream.write(buffer, 0, bytes_read);
-                    }
-                    zipEntry = zis.getNextEntry();
-                    fileOutputStream.close();
-                }
-            } catch (IOException e) {
-                e.printStackTrace();
-            }
-
-            log.info("The Geonames file with country code " + countryCode + " was decompressed successfully");
-
-            // Reads file and adds catalog items to list
-            File geoNamesFile = new File(new File(DIRECTORY_FOR_EXTERNAL_FILES), String.format(GEONAMES_FILE_NAME, countryCode));
-            try (BufferedReader reader = new BufferedReader(new FileReader(geoNamesFile))) {
-                String line;
-                while ((line = reader.readLine()) != null) {
-                    externalCatalogItems.add(new ExternalCatalogItem(List.of(line.split("\t")), ExternalSource.GEONAMES));
-                }
-            } catch (IOException e) {
-                e.printStackTrace();
-            }
-            saveAllWithThreads(externalCatalogItems);
-        }
-    }
-
-    /**
-     * Downloads and reads CIGS sources and saves them to database
-     */
-    private void addCigsSources() {
-        List<ExternalCatalogItem> externalCatalogItems = new ArrayList<>();
-        byte[] buffer = new byte[BUFFER_SIZE];
-        File cigsFile = new File(new File(DIRECTORY_FOR_EXTERNAL_FILES), CIGS_FILE_NAME);
-
-        try (InputStream inputStream = new URL(CIGS_FILE_URL).openStream();
-             FileOutputStream fileOutputStream = new FileOutputStream(cigsFile)) {
-            int bytes_read;
-
-            while ((bytes_read = inputStream.read(buffer)) > 0) {
-
-                fileOutputStream.write(buffer, 0, bytes_read);
-            }
-        } catch (IOException e) {
-            e.printStackTrace();
-        }
-
-        log.info("The CIGS file was downloaded successfully");
-
-        try (InputStream csvData = new FileInputStream(cigsFile)) {
-            CSVParser parser = CSVParser.parse(csvData, StandardCharsets.UTF_8, CSVFormat.Builder.create(CSVFormat.DEFAULT)
-                    .setHeader()
-                    .setSkipHeaderRecord(true)
-                    .build());
-            for (CSVRecord csvRecord : parser) {
-                externalCatalogItems.add(new ExternalCatalogItem(csvRecord.toList(), ExternalSource.CIGS));
-            }
-        } catch (IOException e) {
-            e.printStackTrace();
-        }
-        saveAllWithThreads(externalCatalogItems);
-    }
-
-    /**
-     * Creates list of lists of external catalog items divided by batch size
-     *
-     * @param externalCatalogItems list of external catalog items
-     * @return divided list of lists of external catalog items
-     */
-    private List<List<ExternalCatalogItem>> createSublist(List<ExternalCatalogItem> externalCatalogItems) {
-        List<List<ExternalCatalogItem>> listOfSubList = new ArrayList<>();
-        for (int i = 0; i < externalCatalogItems.size(); i += BATCH_SIZE) {
-            if (i + BATCH_SIZE <= externalCatalogItems.size()) {
-                listOfSubList.add(externalCatalogItems.subList(i, i + BATCH_SIZE));
-            } else {
-                listOfSubList.add(externalCatalogItems.subList(i, externalCatalogItems.size()));
-            }
-        }
-        return listOfSubList;
-    }
-
-    /**
-     * Divides list of external catalog items to sublist, creates threads (for saving sublists in batch) and executes them
-     *
-     * @param externalCatalogItems list of external catalog items
-     */
-    private void saveAllWithThreads(List<ExternalCatalogItem> externalCatalogItems) {
-        ExecutorService executorService = Executors.newFixedThreadPool(hikariDataSource.getMaximumPoolSize());
-        List<List<ExternalCatalogItem>> subList = createSublist(externalCatalogItems);
-        List<Callable<Void>> callables = subList.stream().map(sublist ->
-                (Callable<Void>) () -> {
-                    saveAllInBatch(sublist);
-                    return null;
-                }).collect(Collectors.toList());
-        try {
-            executorService.invokeAll(callables);
-        } catch (InterruptedException e) {
-            e.printStackTrace();
-        }
-    }
-
-    /**
-     * Saves external catalog items in batch
-     *
-     * @param externalCatalogItems list of external catalog items
-     */
-    private void saveAllInBatch(List<ExternalCatalogItem> externalCatalogItems) {
-        String sql = String.format("INSERT INTO %s (id, external_source, latitude, longitude, location_precision, max_date, " +
-                "min_date, time_period_keys, pid, names, feature_code, country, accuracy, geoname_id, pleiades_id, osm_id) " +
-                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", ExternalCatalogItem.class.getAnnotation(Table.class).name());
-        try (Connection connection = hikariDataSource.getConnection();
-             PreparedStatement statement = connection.prepareStatement(sql)) {
-            int counter = 0;
-            for (ExternalCatalogItem item : externalCatalogItems) {
-                statement.clearParameters();
-                statement.setObject(1, item.getId());
-                statement.setString(2, item.getExternalSource().name());
-                statement.setObject(3, item.getLatitude());
-                statement.setObject(4, item.getLongitude());
-                statement.setString(5, item.getLocationPrecision());
-                statement.setObject(6, item.getMaxDate());
-                statement.setObject(7, item.getMinDate());
-                statement.setString(8, item.getTimePeriodKeys());
-                statement.setString(9, item.getPid());
-                statement.setString(10, String.join(",", item.getNames()));
-                statement.setString(11, item.getFeatureCode());
-                statement.setString(12, item.getCountry());
-                statement.setObject(13, item.getAccuracy());
-                statement.setObject(14, item.getGeonameId());
-                statement.setObject(15, item.getPleiadesId());
-                statement.setObject(16, item.getOsmId());
-                statement.addBatch();
-                counter++;
-                if (counter % BATCH_SIZE == 0 || counter == externalCatalogItems.size()) {
-                    statement.executeBatch();
-                    statement.clearBatch();
-                }
-            }
-        } catch (Exception e) {
-            e.printStackTrace();
-        }
-    }
-}

backend/src/main/java/cz/zcu/kiv/backendapi/external/IExternalCatalogItemService.java

@@ +1 @@
+package cz.zcu.kiv.backendapi.external;
+
+import java.util.List;
+
+/**
+ * External catalog service interface
+ */
+public interface IExternalCatalogItemService {
+    /**
+     * Updates external catalog
+     */
+    void updateCatalog();
+
+    /**
+     * Returns external catalog items satisfying given filter
+     *
+     * @param name name
+     * @return list of external catalog items satisfying given filter
+     */
+    List<ExternalCatalogItem> getCatalog(String name, ExternalSource source);
+}

backend/src/main/java/cz/zcu/kiv/backendapi/external/IExternalCatalogService.java

@@ -1 @@
-package cz.zcu.kiv.backendapi.external;
-
-/**
- * External catalog service interface
- */
-public interface IExternalCatalogService {
-    /**
-     * Updates external catalog
-     */
-    void updateCatalog();
-}

backend/src/main/java/cz/zcu/kiv/backendapi/external/SetToStringConverter.java

@@ -1 +1 @@
 package cz.zcu.kiv.backendapi.external;
 
+import org.springframework.stereotype.Component;
+
 import javax.persistence.AttributeConverter;
 import java.util.Arrays;
 import java.util.HashSet;
@@ -9 +11 @@
 /**
  * Class that converts set of strings to string and string to set of strings
  */
+@Component
 public class SetToStringConverter implements AttributeConverter<Set<String>, String> {
     /**
      * Delimiter for splitting/joining string

backend/src/main/java/cz/zcu/kiv/backendapi/path/PathController.java

@@ -1 +1 @@
 package cz.zcu.kiv.backendapi.path;
 
 import cz.zcu.kiv.backendapi.catalog.ICatalogItemService;
+import io.swagger.v3.oas.annotations.Operation;
 import lombok.RequiredArgsConstructor;
 import org.springframework.web.bind.annotation.GetMapping;
 import org.springframework.web.bind.annotation.RequestBody;
@@ -27 +28 @@
      * @return path DTO with highlighted text and found catalog items
      */
     @GetMapping("")
+    @Operation(summary = "returns path with highlighted text and found catalog items based on given text")
     public PathDto getPath(@RequestBody PathDto pathDto) {
         return catalogItemService.getPath(pathDto.getText());
     }

backend/src/main/java/cz/zcu/kiv/backendapi/security/SecurityConfig.java

@@ -62 +62 @@
         PERMITTED_ENDPOINTS.put("/v3/api-docs/swagger-config", HttpMethod.GET);
         PERMITTED_ENDPOINTS.put("/catalog-items", HttpMethod.GET);
         PERMITTED_ENDPOINTS.put("/catalog-items/**", HttpMethod.GET);
-        PERMITTED_ENDPOINTS.put("/external-catalog-items", HttpMethod.POST); //TODO delete
+        PERMITTED_ENDPOINTS.put("/title-page", HttpMethod.GET);
     }
 
     /**
@@ -84 +84 @@
                 .authorizeRequests()
                 .antMatchers(HttpMethod.GET, PERMITTED_ENDPOINTS.keySet().stream().filter(k -> PERMITTED_ENDPOINTS.get(k).equals(HttpMethod.GET)).toArray(String[]::new)).permitAll()
                 .antMatchers(HttpMethod.POST, "/login").permitAll()
-                .antMatchers(HttpMethod.POST, "/external-catalog-items").permitAll() //TODO delete
+                .antMatchers("/external-catalog-items").hasRole(Role.ADMIN.name())
                 .antMatchers(HttpMethod.PATCH, "/users/*/permissions", "/users/*/password").hasRole(Role.ADMIN.name())
                 .antMatchers(HttpMethod.DELETE, "/users/**").hasRole(Role.ADMIN.name())
                 .antMatchers(HttpMethod.GET, "/users").hasRole(Role.ADMIN.name())

backend/src/test/java/cz/zcu/kiv/backendapi/catalog/CatalogItemServiceImplTest.java

@@ -88 +88 @@
         // given
         CatalogItemDto catalogItemDto = new CatalogItemDto();
         catalogItemDto.setName("name");
-        catalogItemDto.setBibliography(Set.of("bibl"));
-        catalogItemDto.setTypes(Set.of("type"));
-        catalogItemDto.setAlternativeNames(Set.of("altName"));
-        catalogItemDto.setCountries(Set.of("country"));
-        catalogItemDto.setWrittenForms(Set.of("written"));
+        catalogItemDto.setBibliography(Stream.of("bibl").collect(Collectors.toSet()));
+        catalogItemDto.setTypes(Stream.of("type").collect(Collectors.toSet()));
+        catalogItemDto.setAlternativeNames(Stream.of("altName").collect(Collectors.toSet()));
+        catalogItemDto.setCountries(Stream.of("country").collect(Collectors.toSet()));
+        catalogItemDto.setWrittenForms(Stream.of("written").collect(Collectors.toSet()));
         catalogItemDto.setCertainty(0);
         catalogItemDto.setLatitude(0.1);
         catalogItemDto.setLongitude(0.2);
@@ -102 +102 @@
 
         CatalogItem catalogItem = new CatalogItem();
         catalogItem.setName("name");
-        catalogItem.setBibliography(Set.of(new Bibliography("bibl", catalogItem)));
-        catalogItem.setTypes(Set.of(type));
-        catalogItem.setAlternativeNames(Set.of(new AlternativeName("altName", catalogItem)));
-        catalogItem.setCountries(Set.of(new Country("country", catalogItem)));
-        catalogItem.setWrittenForms(Set.of(new WrittenForm("written", catalogItem)));
+        catalogItem.setBibliography(Stream.of(new Bibliography("bibl", catalogItem)).collect(Collectors.toSet()));
+        catalogItem.setTypes(Stream.of(type).collect(Collectors.toSet()));
+        catalogItem.setAlternativeNames(Stream.of(new AlternativeName("altName", catalogItem), new AlternativeName("name", catalogItem)).collect(Collectors.toSet()));
+        catalogItem.setCountries(Stream.of(new Country("country", catalogItem)).collect(Collectors.toSet()));
+        catalogItem.setWrittenForms(Stream.of(new WrittenForm("written", catalogItem)).collect(Collectors.toSet()));
         catalogItem.setCertainty(0);
         catalogItem.setLatitude(0.1);
         catalogItem.setLongitude(0.2);
@@ -177 +177 @@
 
         CatalogItemDto catalogItemDto = new CatalogItemDto();
         catalogItemDto.setName("name");
-        catalogItemDto.setBibliography(Set.of("bibl"));
-        catalogItemDto.setTypes(Set.of("type"));
-        catalogItemDto.setAlternativeNames(Set.of("altName"));
-        catalogItemDto.setCountries(Set.of("country"));
-        catalogItemDto.setWrittenForms(Set.of("written"));
+        catalogItemDto.setBibliography(Stream.of("bibl").collect(Collectors.toSet()));
+        catalogItemDto.setTypes(Stream.of("type").collect(Collectors.toSet()));
+        catalogItemDto.setAlternativeNames(Stream.of("altName").collect(Collectors.toSet()));
+        catalogItemDto.setCountries(Stream.of("country").collect(Collectors.toSet()));
+        catalogItemDto.setWrittenForms(Stream.of("written").collect(Collectors.toSet()));
         catalogItemDto.setCertainty(0);
         catalogItemDto.setLatitude(0.1);
         catalogItemDto.setLongitude(0.2);
@@ -237 +237 @@
         assertThat(capturedCatalogItem.getLongitude()).isEqualTo(catalogItem.getLongitude());
         assertThat(capturedCatalogItem.getDescription()).isEqualTo(catalogItem.getDescription());
         assertThat(capturedCatalogItem.getTypes().stream().map(Type::getType).collect(Collectors.toSet())).isEqualTo(catalogItem.getTypes().stream().map(Type::getType).collect(Collectors.toSet()));
-        assertThat(capturedCatalogItem.getAlternativeNames().stream().map(AlternativeName::getName).collect(Collectors.toSet())).isEqualTo(catalogItem.getAlternativeNames().stream().map(AlternativeName::getName).collect(Collectors.toSet()));
+        Set<String> alternativeNamesEntity = catalogItem.getAlternativeNames().stream().map(AlternativeName::getName).collect(Collectors.toSet());
+        alternativeNamesEntity.add(catalogItem.getName());
+        assertThat(capturedCatalogItem.getAlternativeNames().stream().map(AlternativeName::getName).collect(Collectors.toSet())).isEqualTo(alternativeNamesEntity);
         assertThat(capturedCatalogItem.getCountries().stream().map(Country::getName).collect(Collectors.toSet())).isEqualTo(catalogItem.getCountries().stream().map(Country::getName).collect(Collectors.toSet()));
         assertThat(capturedCatalogItem.getBibliography().stream().map(Bibliography::getSource).collect(Collectors.toSet())).isEqualTo(catalogItem.getBibliography().stream().map(Bibliography::getSource).collect(Collectors.toSet()));
         assertThat(capturedCatalogItem.getWrittenForms().stream().map(WrittenForm::getForm).collect(Collectors.toSet())).isEqualTo(catalogItem.getWrittenForms().stream().map(WrittenForm::getForm).collect(Collectors.toSet()));

backend/src/test/java/cz/zcu/kiv/backendapi/external/ExternalCatalogItemItemServiceImplTest.java

@@ +1 @@
+package cz.zcu.kiv.backendapi.external;
+
+import com.zaxxer.hikari.HikariDataSource;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.ExtendWith;
+import org.mockito.Mock;
+import org.mockito.junit.jupiter.MockitoExtension;
+
+import java.util.List;
+import java.util.Set;
+import java.util.UUID;
+
+import static org.assertj.core.api.AssertionsForClassTypes.assertThat;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+import static org.mockito.BDDMockito.given;
+import static org.mockito.Mockito.verify;
+
+@ExtendWith(MockitoExtension.class)
+class ExternalCatalogItemItemServiceImplTest {
+
+    @Mock
+    private ExternalCatalogItemRepository externalCatalogItemRepository;
+
+    @Mock
+    private HikariDataSource hikariDataSource;
+
+    @Mock
+    private SetToStringConverter setToStringConverter;
+
+    private ExternalCatalogItemItemServiceImpl underTest;
+
+    @BeforeEach
+    void setUp() {
+        underTest = new ExternalCatalogItemItemServiceImpl(externalCatalogItemRepository, hikariDataSource, setToStringConverter);
+    }
+
+    @Test
+    void getCatalog() {
+        // given
+        ExternalCatalogItem catalogItem1 = new ExternalCatalogItem(UUID.randomUUID(), ExternalSource.PLEIADES, 10.0, 10.0, null, null, null, null, null, Set.of("first", "NAME"), null, null, null, null, null, null);
+        ExternalCatalogItem catalogItem2 = new ExternalCatalogItem(UUID.randomUUID(), ExternalSource.PLEIADES, 10.0, 10.0, null, null, null, null, null, Set.of("second", "name"), null, null, null, null, null, null);
+        ExternalCatalogItem catalogItem3 = new ExternalCatalogItem(UUID.randomUUID(), ExternalSource.PLEIADES, 10.0, 10.0, null, null, null, null, null, Set.of("fourth", "nane"), null, null, null, null, null, null);
+        String name = "na?*";
+        ExternalSource source = ExternalSource.PLEIADES;
+        given(externalCatalogItemRepository.getFilteredExternalCatalog("%na_%%", source.name())).willReturn(List.of(catalogItem1, catalogItem2, catalogItem3));
+
+        // when
+        List<ExternalCatalogItem> result = underTest.getCatalog(name, source);
+
+        // then
+        verify(externalCatalogItemRepository).getFilteredExternalCatalog("%na_%%", source.name());
+        assertThat(result.size()).isEqualTo(3);
+        assertTrue(result.contains(catalogItem1));
+        assertTrue(result.contains(catalogItem2));
+        assertTrue(result.contains(catalogItem3));
+    }
+}

backend/src/test/java/cz/zcu/kiv/backendapi/external/ExternalCatalogItemRepositoryTest.java

@@ +1 @@
+package cz.zcu.kiv.backendapi.external;
+
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
+
+import java.util.List;
+import java.util.Set;
+import java.util.UUID;
+
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+@DataJpaTest
+class ExternalCatalogItemRepositoryTest {
+
+    @Autowired
+    private ExternalCatalogItemRepository underTest;
+
+    private ExternalCatalogItem catalogItem1;
+    private ExternalCatalogItem catalogItem2;
+    private ExternalCatalogItem catalogItem3;
+    private ExternalCatalogItem catalogItem4;
+
+    private ExternalCatalogItem catalogItem5;
+    private ExternalCatalogItem catalogItem6;
+
+    private ExternalCatalogItem catalogItem7;
+
+    @BeforeEach
+    void setUp() {
+        catalogItem1 = new ExternalCatalogItem(UUID.randomUUID(), ExternalSource.PLEIADES, 10.0, 10.0, null, null, null, null, null, Set.of("first", "NAME"), null, null, null, null, null, null);
+        catalogItem2 = new ExternalCatalogItem(UUID.randomUUID(), ExternalSource.PLEIADES, 10.0, 10.0, null, null, null, null, null, Set.of("second", "name"), null, null, null, null, null, null);
+        catalogItem3 = new ExternalCatalogItem(UUID.randomUUID(), ExternalSource.CIGS, 10.0, 10.0, null, null, null, null, null, Set.of("third", "NAME"), null, null, null, null, null, null);
+        catalogItem4 = new ExternalCatalogItem(UUID.randomUUID(), ExternalSource.PLEIADES, 10.0, 10.0, null, null, null, null, null, Set.of("fourth", "nane"), null, null, null, null, null, null);
+        catalogItem5 = new ExternalCatalogItem(UUID.randomUUID(), ExternalSource.GEONAMES, 10.0, 10.0, null, null, null, null, null, Set.of("fifthName", "Achala"), null, null, null, null, null, null);
+        catalogItem6 = new ExternalCatalogItem(UUID.randomUUID(), ExternalSource.GEONAMES, 10.0, 10.0, null, null, null, null, null, Set.of("fifthMama", "Bafala"), null, null, null, null, null, null);
+        catalogItem7 = new ExternalCatalogItem(UUID.randomUUID(), ExternalSource.CIGS, 10.0, 10.0, null, null, null, null, null, Set.of("seventhName", "hello"), null, null, null, null, null, null);
+        underTest.saveAll(List.of(catalogItem1, catalogItem2, catalogItem3, catalogItem4, catalogItem5, catalogItem6, catalogItem7));
+    }
+
+    @AfterEach
+    void tearDown() {
+        underTest.deleteAll();
+    }
+
+    @Test
+    void getFilteredExternalCatalog() {
+        // given
+        // when
+        List<ExternalCatalogItem> result = underTest.getFilteredExternalCatalog("%name%", ExternalSource.PLEIADES.name());
+
+        // then
+        assertThat(result.size()).isEqualTo(2);
+        assertTrue(result.contains(catalogItem1));
+        assertTrue(result.contains(catalogItem2));
+    }
+
+    @Test
+    void getFilteredExternalCatalogWildcardsCharacter() {
+        // given
+        // when
+        List<ExternalCatalogItem> result = underTest.getFilteredExternalCatalog("%na_e%", ExternalSource.PLEIADES.name());
+
+        // then
+        assertThat(result.size()).isEqualTo(3);
+        assertTrue(result.contains(catalogItem1));
+        assertTrue(result.contains(catalogItem2));
+        assertTrue(result.contains(catalogItem4));
+    }
+
+    @Test
+    void getFilteredExternalCatalogWildcardsCharacters() {
+        // given
+        // when
+        List<ExternalCatalogItem> result = underTest.getFilteredExternalCatalog("%fifth%%", ExternalSource.GEONAMES.name());
+
+        // then
+        assertThat(result.size()).isEqualTo(2);
+        assertTrue(result.contains(catalogItem5));
+        assertTrue(result.contains(catalogItem6));
+    }
+
+    @Test
+    void getFilteredExternalCatalogAllSource() {
+        // given
+        // when
+        List<ExternalCatalogItem> result = underTest.getFilteredExternalCatalog("%%%", ExternalSource.CIGS.name());
+
+        // then
+        assertThat(result.size()).isEqualTo(2);
+        assertTrue(result.contains(catalogItem3));
+        assertTrue(result.contains(catalogItem7));
+    }
+}