Newer
Older
/*
* This file is part of vospace-file-service
* Copyright (C) 2021 Istituto Nazionale di Astrofisica
* SPDX-License-Identifier: GPL-3.0-or-later
*/
import it.inaf.ia2.transfer.persistence.model.FileInfo;
import java.nio.file.Path;
import java.sql.ResultSet;
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"
Sonia Zorba
committed
+ "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"
+ "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"
FileInfo fileInfo = jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, virtualPath);
ps.setInt(2, uploadLocationId);
ps.setString(3, virtualPath);
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;
});
}
Nicola Fulvio Calabria
committed
public void updateFileAttributes(int nodeId,
String contentType,
String contentEncoding,
Long contentLength,
String contentMd5) {
String sql = "UPDATE node SET content_type = ?, content_encoding = ?, content_length = ?, content_md5 = ?, location_id = ? "
Nicola Fulvio Calabria
committed
+ "WHERE node_id = ?";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, contentType);
ps.setString(2, contentEncoding);
ps.setLong(3, contentLength);
ps.setString(4, contentMd5);
ps.setInt(5, uploadLocationId);
ps.setInt(6, nodeId);
Nicola Fulvio Calabria
committed
return ps;
});
}
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
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"
+ "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"
+ "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"
+ "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"
+ "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"));
Sonia Zorba
committed
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"));
Sonia Zorba
committed
int locationId = rs.getInt("location_id");
if (!rs.wasNull()) {
fi.setLocationId(locationId);
}
fillOsPath(fi, rs);
return fi;
}
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());
}