FileDAO.java

/*
 * This file is part of vospace-file-service
 * Copyright (C) 2021 Istituto Nazionale di Astrofisica
 * SPDX-License-Identifier: GPL-3.0-or-later
 */
package it.inaf.ia2.transfer.persistence;

import it.inaf.ia2.transfer.persistence.model.FileInfo;
import java.nio.file.Path;
import java.sql.Array;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Optional;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class FileDAO {

    private static final Logger LOG = LoggerFactory.getLogger(FileDAO.class);

    @Value("${upload_location_id}")
    private int uploadLocationId;

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public FileDAO(DataSource fileCatalogDatasource) {
        this.jdbcTemplate = new JdbcTemplate(fileCatalogDatasource);
    }

    public Optional<FileInfo> getFileInfo(String virtualPath) {

        String sql = "SELECT n.node_id, is_public, group_read, group_write, creator_id, async_trans,\n"
                + "content_type, content_encoding, content_length, content_md5, name, n.location_id,\n"
                + "accept_views, provide_views, l.location_type, n.path <> n.relative_path AS virtual_parent,\n"
                + "(SELECT user_name FROM users WHERE user_id = creator_id) AS username, n.job_id,\n"
                + "base_path, get_os_path(n.node_id) AS os_path, ? AS vos_path, false AS is_directory,\n"
                + "n.type = 'link' AS is_link, n.target,\n"
                + "fs_path \n"
                + "FROM node n\n"
                + "JOIN location l ON (n.location_id IS NOT NULL AND n.location_id = l.location_id) OR (n.location_id IS NULL AND l.location_id = ?)\n"
                + "LEFT JOIN storage s ON s.storage_id = l.storage_dest_id\n"
                + "WHERE n.node_id = id_from_vos_path(?)";

        FileInfo fileInfo = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, virtualPath);
            ps.setInt(2, uploadLocationId);
            ps.setString(3, virtualPath);
            return ps;
        }, rs -> {
            if (rs.next()) {
                return getFileInfo(rs);
            }
            return null;
        });

        return Optional.ofNullable(fileInfo);
    }

    private List<String> toList(Array array) throws SQLException {
        if (array == null) {
            return new ArrayList<>();
        }
        return Arrays.asList((String[]) array.getArray());
    }

    public int setBusy(String vosPath, String jobId) {
        String sql = "UPDATE node SET job_id = ? WHERE node_id = id_from_vos_path(?)";

        int nodes = jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            if (jobId == null) {
                ps.setNull(1, Types.VARCHAR);
            } else {
                ps.setString(1, jobId);
            }
            ps.setString(2, vosPath);
            return ps;
        });

        if (nodes > 1) {
            // This should never happen thanks to database constraints
            throw new IllegalStateException("More than one node at path: " + vosPath);
        }

        return nodes;
    }

    public void setOsName(int nodeId, String newName) {

        String sql = "UPDATE node SET os_name = ? WHERE node_id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, newName);
            ps.setInt(2, nodeId);
            return ps;
        });
    }

    public void updateFileAttributes(int nodeId, 
            String fsPath,
            String contentType,
            String contentEncoding,
            Long contentLength,
            String contentMd5) {

        String sql = "UPDATE node SET fs_path = ?, content_type = ?, content_encoding = ?, content_length = ?, content_md5 = ?, location_id = ? "
                + "WHERE node_id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            ps.setString(++i, fsPath);
            ps.setString(++i, contentType);
            ps.setString(++i, contentEncoding);
            ps.setLong(++i, contentLength);
            ps.setString(++i, contentMd5);
            ps.setInt(++i, uploadLocationId);
            ps.setInt(++i, nodeId);
            return ps;
        });

    }

    public Long getRemainingQuota(String parentVosPath) {

        String sql = "WITH quota_info AS (\n"
                + "    SELECT p.quota, get_vos_path(p.node_id) AS parent_vos_path\n"
                + "    FROM node p\n"
                + "    JOIN node n ON p.path @> n.path\n"
                + "    WHERE n.node_id = id_from_vos_path(?) AND p.quota IS NOT NULL\n"
                + "    ORDER BY nlevel(p.path) DESC LIMIT 1\n"
                + ")\n"
                + "SELECT AVG(qi.quota)::bigint - SUM(c.content_length) AS remaining_quota\n"
                + "FROM node c\n"
                + "JOIN node n ON n.path @> c.path\n"
                + "JOIN quota_info qi ON n.node_id = id_from_vos_path(qi.parent_vos_path)\n"
                + "WHERE c.type <> 'container'";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, parentVosPath);
            return ps;
        }, rs -> {
            if (rs.next()) {
                long remainingQuota = rs.getLong(1);
                if (!rs.wasNull()) {
                    return remainingQuota;
                }
            }
            LOG.warn("Quota not defined for path {}", parentVosPath);
            return null;
        });
    }

    // TODO: set maximum list size or use stream to avoid memory issues
    public List<FileInfo> getArchiveFileInfos(List<String> vosPaths) {

        if (vosPaths.isEmpty()) {
            throw new IllegalArgumentException("Received empty list of paths");
        }

        String sql = "SELECT n.node_id, n.is_public, n.group_read, n.group_write, n.creator_id, n.async_trans, n.fs_path,\n"
                + "n.content_type, n.content_encoding, n.content_length, n.content_md5,\n"
                + "n.accept_views, n.provide_views, l.location_type, n.path <> n.relative_path AS virtual_parent,\n"
                + "(SELECT user_name FROM users WHERE user_id = n.creator_id) AS username,\n"
                + "base_path, get_os_path(n.node_id) AS os_path, get_vos_path(n.node_id) AS vos_path,\n"
                + "n.type = 'container' AS is_directory, n.name, n.location_id, n.job_id,\n"
                + "n.type = 'link' AS is_link, n.target, l.location_type\n"
                + "FROM node n\n"
                + "JOIN node p ON p.path @> n.path\n"
                + "LEFT JOIN location l ON l.location_id = n.location_id\n"
                + "LEFT JOIN storage s ON s.storage_id = l.storage_dest_id\n"
                + "WHERE " + String.join(" OR ", Collections.nCopies(vosPaths.size(), "p.node_id  = id_from_vos_path(?)"))
                + "\nORDER BY vos_path ASC";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            for (String vosPath : vosPaths) {
                ps.setString(++i, vosPath);
            }
            return ps;
        }, rs -> {
            List<FileInfo> fileInfos = new ArrayList<>();
            while (rs.next()) {
                fileInfos.add(getFileInfo(rs));
            }
            return fileInfos;
        });
    }

    // TODO: same problem as get archive file infos
    public List<FileInfo> getBranchFileInfos(String rootVosPath, String jobId) {

        String sql = "SELECT n.node_id, n.is_public, n.group_read, n.group_write, n.creator_id, n.async_trans, n.fs_path,\n"
                + "n.content_type, n.content_encoding, n.content_length, n.content_md5,\n"
                + "n.accept_views, n.provide_views, l.location_type, n.path <> n.relative_path AS virtual_parent,\n"
                + "(SELECT user_name FROM users WHERE user_id = n.creator_id) AS username,\n"
                + "base_path, get_os_path(n.node_id) AS os_path, get_vos_path(n.node_id) AS vos_path,\n"
                + "n.type = 'container' AS is_directory, n.name, n.location_id, n.job_id,\n"
                + "n.type = 'link' AS is_link, n.target, l.location_type\n"
                + "FROM node n\n"
                + "JOIN node p ON p.path @> n.path\n"
                + "LEFT JOIN location l ON l.location_id = n.location_id\n"
                + "LEFT JOIN storage s ON s.storage_id = l.storage_dest_id\n"
                + "WHERE (p.node_id = id_from_vos_path(?) AND n.job_id = ?)\n"
                + "ORDER BY vos_path ASC\n";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, rootVosPath);
            ps.setString(2, jobId);
            return ps;
        }, rs -> {
            List<FileInfo> fileInfos = new ArrayList<>();
            while (rs.next()) {
                fileInfos.add(getFileInfo(rs));
            }
            return fileInfos;
        });
    }

    public void setBranchLocationId(String rootVosPath, String jobId, int locationId) {

        String cte = "SELECT n.node_id AS id\n"
                + "FROM node n\n"
                + "JOIN node p ON p.path @> n.path\n"
                + "WHERE (p.node_id = id_from_vos_path(?) AND n.job_id = ?)\n";

        String update = "UPDATE node\n"
                + " SET location_id = ?\n"
                + "FROM cte\n"
                + "WHERE node_id = cte.id\n";

        String sql = "WITH cte AS (\n"
                + cte
                + ")\n"
                + update;

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, rootVosPath);
            ps.setString(2, jobId);
            ps.setInt(3, locationId);
            return ps;
        });
    }

    // !! duplicate code from NodeDAO
    public void releaseBusyNodesByJobId(String jobId) {
        String sql = "UPDATE node SET job_id = NULL WHERE job_id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, jobId);
            return ps;
        });
    }

    private FileInfo getFileInfo(ResultSet rs) throws SQLException {
        FileInfo fi = new FileInfo();
        fi.setNodeId(rs.getInt("node_id"));
        fi.setPublic(rs.getBoolean("is_public"));
        fi.setGroupRead(toList(rs.getArray("group_read")));
        fi.setGroupWrite(toList(rs.getArray("group_write")));
        fi.setOwnerId(rs.getString("creator_id"));
        fi.setAsyncTrans(rs.getBoolean("async_trans"));
        fi.setAcceptViews(toList(rs.getArray("accept_views")));
        fi.setProvideViews(toList(rs.getArray("provide_views")));
        fi.setVirtualParent(rs.getBoolean("virtual_parent"));
        fi.setVirtualPath(rs.getString("vos_path"));
        fi.setVirtualName(rs.getString("name"));
        fi.setContentEncoding(rs.getString("content_encoding"));
        long contentLength = rs.getLong("content_length");
        if (!rs.wasNull()) {
            fi.setContentLength(contentLength);
        }        
        fi.setContentMd5(rs.getString("content_md5"));
        fi.setContentType(rs.getString("content_type"));
        fi.setDirectory(rs.getBoolean("is_directory"));
        if(rs.getBoolean("is_link")){
            fi.setLink(true);
            fi.setTarget(rs.getString("target"));
        } else {
            fi.setLink(false);
        }
        fi.setJobId(rs.getString("job_id"));
        int locationId = rs.getInt("location_id");
        if (!rs.wasNull()) {
            fi.setLocationId(locationId);
            fi.setLocationType(rs.getString("location_type"));
        }

        this.fillActualBasePath(fi, rs);
        this.fillFsPath(fi, rs);

        return fi;
    }

    private void fillActualBasePath(FileInfo fi, ResultSet rs) throws SQLException {
        String basePath = rs.getString("base_path");
        if (basePath == null) {
            return;
        }

        Path completeBasePath = Path.of(basePath);

        boolean asyncLocation = "async".equals(rs.getString("location_type"));

        if (asyncLocation) {
            String username = rs.getString("username");
            completeBasePath = completeBasePath.resolve(username).resolve("retrieve");
        }

        fi.setActualBasePath(completeBasePath.toString());
    }

    private void fillFsPath(FileInfo fi, ResultSet rs) throws SQLException {

        String fsPath = rs.getString("fs_path");

        if (fsPath == null) {
            return;
        }

        if (fsPath.startsWith("/")) {
            fsPath = fsPath.substring(1);
        }

        Path completeFsPath = Path.of(fsPath);

        fi.setFsPath(completeFsPath.toString());
    }

    private void fillOsPath(FileInfo fi, ResultSet rs) throws SQLException {

        String basePath = rs.getString("base_path");
        if (basePath == null) {
            return;
        }

        String osPath = rs.getString("os_path");
        if (osPath.startsWith("/")) {
            osPath = osPath.substring(1);
        }

        Path completeOsPath = Path.of(basePath);

        boolean asyncLocation = "async".equals(rs.getString("location_type"));

        if (asyncLocation) {
            String username = rs.getString("username");
            completeOsPath = completeOsPath.resolve(username).resolve("retrieve");
        } else if (fi.hasVirtualParent()) {
            completeOsPath = completeOsPath.resolve(fi.getOwnerId());
        }

        completeOsPath = completeOsPath.resolve(osPath);
        fi.setOsPath(completeOsPath.toString());
    }
}