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(?)"))
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
+ "\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;
});
}
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());
}