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 void setBusy(int nodeId, String jobId) {
String sql = "UPDATE node SET job_id = ? WHERE node_id = ?";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
if (jobId == null) {
ps.setNull(1, Types.VARCHAR);
} else {
ps.setString(1, jobId);
}
ps.setInt(2, nodeId);
return ps;
});
}
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;
});
}
128
129
130
131
132
133
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
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;
});
}
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
// TODO: same problem as get archive file infos
public List<FileInfo> getBranchFileInfos(String rootVosPath, String jobId) {
// TODO: validate rootVosPath as a vos_path
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 = ?)"
+ "\nORDER BY vos_path ASC";
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) {
// TODO: validate rootVosPath as a vos_path
String sql = "UPDATE node n SET\n"
+ "location_id = ?\n"
+ "JOIN node p ON n.path <@ p.path\n"
+ "WHERE (path ~ ('*.' || id_from_vos_path(?))::lquery AND n.job_id = ?)";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, locationId);
ps.setString(2, rootVosPath);
ps.setString(3, 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"));
fi.setJobId(rs.getString("job_id"));
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());
}