(25.03.01) ERD Update & 기능 개발(User, Sample, Template, Group CRUD)

ERD 재정리

• review_groups로 SQL 문법과 겹치지 않게 변경
• review_groups 와 users 테이블이 N:M 관계를 형성을 해야했기 때문에 중간 테이블인 user_group테이블을 생성
• 추후 라벨링 기능이 결정이 된다면 추가로 수정이 필요

User, Sample, Template, Group 테이블에 맞도록 각각의 CRUD 개발

• domain/global 분리해서 개발

CSV File Upload 기능 

• dataset 도메인에 CSV file upload 시 자동으로 sample 과 template에 해당하는  컬럼을 따로 분리해서 samples, review_groups 로 분리

 // Controller
 
 @PostMapping(value = "/upload", consumes = MediaType.MULTIPART_FORM_DATA_VALUE, produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<MessageResponseDto> uploadCsvFile(
            @Parameter(description = "CSV file to be uploaded") @RequestPart("file") MultipartFile file,
            @Parameter(description = "Metadata associated with the dataset") @RequestPart("metadata") DatasetMetadataDto metadata) throws Exception {
        datasetService.uploadCsvFile(file, metadata);
        return new ResponseEntity<>(new MessageResponseDto("Dataset uploaded successfully"), HttpStatus.CREATED);
    }

 

// Service
@Transactional
    public void uploadCsvFile(MultipartFile file, DatasetMetadataDto metadata) throws Exception {
        String datasetName = metadata.getDatasetName();
        String datasetDescription = metadata.getDatasetDescription();
		
        // Open CSV를 의존성 주입해서 CSV reader를 활용해 스트리밍으로 한 줄씩 읽어가면서(readNext() ) 진행 
        try (CSVReader csvReader = new CSVReader(new InputStreamReader(file.getInputStream()))) {
            String[] columns = csvReader.readNext();

            if (columns == null) throw new FileProcessingException("CSV file is empty");

            Map<String, Integer> columnIndexMap = new HashMap<>();

            for (int i = 0; i < columns.length; i++) {
                for (DatasetColumn column : DatasetColumn.values()) {
                    if (columns[i].equalsIgnoreCase(column.toString())) {
                        columnIndexMap.put(column.toString(), i);
                    }
                }
            }
			
            // 필요한 컬럼이 모두 있는지 검증이 필요
            if (columnIndexMap.size() != 4)
                throw new FileProcessingException("CSV file must contain 4 columns : sql_query, natural_question, no_template, sql_template");

            Integer noTemplateIndex = columnIndexMap.get(DatasetColumn.NO_SQL_TEMPLATE.toString());
            Integer sqlTemplateIndex = columnIndexMap.get(DatasetColumn.SQL_TEMPLATE.toString());

            String[] nextRecord;
            List<Sample> sampleList = new ArrayList<>();
            List<Template> templateList = new ArrayList<>();
            List<Long> templateNoList = new ArrayList<>();
			
            // 확인 된 내용을 Json 타입으로 바로 저장
            while ((nextRecord = csvReader.readNext()) != null) {
                JsonObject sampleData = new JsonObject();
                for (int i = 0; i < columns.length; i++) {
                    sampleData.addProperty(columns[i], nextRecord[i]);
                }

                Sample sample = Sample.builder()
                        .id(UUID.randomUUID().toString())
                        .datasetName(datasetName)
                        .datasetDescription(datasetDescription)
                        .versionId(1L)
                        .status(SampleStatus.CREATED)
                        .sampleData(sampleData.toString())
                        .build();

                sampleList.add(sample);
				
                // Template 테이블에도 해당 정보를 번호 기준 UNIQUE하게 저장
                Long templateNo = Long.parseLong(nextRecord[noTemplateIndex]);
                Template foundTemplate = templateRepository.findByTemplateNo(templateNo);

                if (foundTemplate == null && !templateNoList.contains(templateNo)) {
                    Template template = Template.builder()
                            .id(UUID.randomUUID().toString())
                            .templateNo(templateNo)
                            .content(nextRecord[sqlTemplateIndex])
                            .build();

                    templateList.add(template);
                    templateNoList.add(templateNo);
                }
            }

            sampleRepository.saveAll(sampleList);
            templateRepository.saveAll(templateList);

        } catch (Exception e) {
            throw new FileUploadException(e.getMessage(), e);
        }
    }

 

SQL_QUERY("sql_query"),
NATURAL_QUESTION("natural_question"),
NO_SQL_TEMPLATE("no_sql_template"),
SQL_TEMPLATE("sql_template");

• CSV 파일에는 기본 ENUM으로 지정된 위의 이름의 컬럼이 있을 경우 template 테이블의 template_no 와 content에 저장할 수 있도록 했음
• 위의 SQL QUERY 와 NATURAL QUESTION 이 Reviewer 가 확인하고, 수정, 또는 라벨링 할 수 있는 항목이기 때문에 반드시 존재해야 하는 컬럼
  • 따로 테이블에 저장하는 것이 아니라 있는지 검증 후, JSON 형태로 sample_data컬럼에 저장할 수 있도록 했음

Repository에서의 Native SQL 활용

public interface SampleRepository extends JpaRepository<Sample, String> {

    @Query(value = "SELECT s.* FROM samples s " +
            "WHERE s.status IN ('UPDATED','DELETED','CREATED') " +
            "AND s.version_id = (SELECT MAX(s2.version_id) FROM samples s2 WHERE JSON_UNQUOTE(JSON_EXTRACT(s2.sample_data, '$.id')) = JSON_UNQUOTE(JSON_EXTRACT(s.sample_data, '$.id'))) " +
            "ORDER BY JSON_UNQUOTE(JSON_EXTRACT(s.sample_data, '$.id')) ASC", nativeQuery = true)
    List<Sample> findLatestUpdatedSample();

    @Query(value = "SELECT s.* FROM samples s " +
            "WHERE JSON_UNQUOTE(JSON_EXTRACT(s.sample_data, '$.id')) = ?1 " +
            "AND s.status IN ('UPDATED', 'DELETED', 'CREATED') " +
            "ORDER BY s.version_id DESC " +
            "LIMIT 1", nativeQuery = true)
    Optional<Sample> findLatestBySampleId(String sampleId);

    @Query(value = "SELECT s.* FROM samples s " +
            "WHERE s.status LIKE 'REQUESTED_%' " +
            "ORDER BY JSON_UNQUOTE(JSON_EXTRACT(s.sample_data, '$.id')) ASC, s.version_id ASC", nativeQuery = true)
    List<Sample> findRequestedSample();

    @Query(value = "SELECT s.* FROM samples s " +
            "WHERE JSON_UNQUOTE(JSON_EXTRACT(s.sample_data, '$.id')) = ?1 " +
            "AND s.status LIKE 'REQUESTED_%' " +
            "And s.version_id = ?2", nativeQuery = true)
    List<Sample> findRequestedBySampleIdAndVersionId(String sampleId, Long versionId);

    @Query(value = "SELECT u.* " +
            "FROM users u " +
            "JOIN user_group ug ON u.id = ug.user_id " +
            "JOIN review_groups g ON ug.group_id = g.id " +
            "WHERE g.id = (SELECT s.group_id FROM samples s WHERE s.id = ?1 LIMIT 1)",
            nativeQuery = true)
    List<User> findUsersAssignedToSample(String sampleId);

    @Query(value = "SELECT * FROM samples " +
            "WHERE version_id = ?1 " +
            "AND id != ?2 " +
            "AND status != 'CREATED' " +
            "ORDER BY updated_at",
            nativeQuery = true)
    List<Sample> getOtherSamplesOfSameVersion(Long versionId, String sampleId);

    @Query(value = """
    SELECT sub.*
    FROM (
        SELECT s.*, 
               ROW_NUMBER() OVER (
                   PARTITION BY JSON_UNQUOTE(JSON_EXTRACT(s.sample_data, '$.id')) 
                   ORDER BY s.updated_at DESC, s.version_id DESC
               ) AS rn
        FROM samples s
        JOIN user_group ug ON ug.group_id = s.group_id
        WHERE ug.user_id = ?1
          AND (
              s.updated_by = ?1
              OR (COALESCE(s.updated_by, '') != ?1 AND s.status = 'CREATED')
          )
    ) sub
    WHERE sub.rn = 1
    ORDER BY JSON_UNQUOTE(JSON_EXTRACT(sub.sample_data, '$.id')) ASC
    """, nativeQuery = true)
    List<Sample> findAllByUserId(String userId);
}

• Sample을 필터링하거나 특정 조건으로 조회하기 위해서는 sample_data값에서의 특정 필드값을 확인하는 작업이 필요하기 때문에, 서비스단의 비지니스로직에서 활용하는 것보다 MySQL DB에서 해당 Native Query를 Spring Data JPA의 JPQL 보다는 좀더 정교하게 native query를 활용해서 ROW_NUMBER() 등 윈도우 함수를 활용하고자 했음
  • text2sql 앱이기 때문에 협업 개발자의 이해를 좀더 쉽게 돕기 위해 SQL언어를 활용했음