Newer
Older
/*
* This file is part of vospace-rest
* Copyright (C) 2021 Istituto Nazionale di Astrofisica
* SPDX-License-Identifier: GPL-3.0-or-later
*/
import it.inaf.oats.vospace.datamodel.NodeProperties;
import it.inaf.oats.vospace.datamodel.NodeUtils;
import it.inaf.oats.vospace.exception.InternalFaultException;
import it.inaf.oats.vospace.exception.NodeNotFoundException;
Sonia Zorba
committed
import java.sql.Array;
import net.ivoa.xml.vospace.v2.Node;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
Sara Bertocco
committed
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.function.Function;
import java.util.stream.Collectors;
import net.ivoa.xml.vospace.v2.ContainerNode;
import net.ivoa.xml.vospace.v2.DataNode;
import net.ivoa.xml.vospace.v2.Property;
import net.ivoa.xml.vospace.v2.View;
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 NodeDAO {
private static final Logger LOG = LoggerFactory.getLogger(DeleteNodeController.class);
@Value("${vospace-authority}")
private String authority;
@Autowired
public NodeDAO(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
Sara Bertocco
committed
String nodeURI = myNode.getUri();
String path = nodeURI.replaceAll("vos://[^/]+", "");
Sara Bertocco
committed
throw new IllegalStateException("Unable to find parent node during node creation");
throw new IllegalStateException("Multiple ltree parent paths found for " + parentPath);
}
Sara Bertocco
committed
sb.append("INSERT INTO node");
sb.append(" (name, busy_state, creator_id, group_read, group_write,");
sb.append(" is_public, parent_path, parent_relative_path, type, accept_views, provide_views)");
sb.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
Sara Bertocco
committed
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sb.toString());
ps.setString(++i, NodeUtils.getNodeName(myNode));
ps.setBoolean(++i, NodeUtils.getIsBusy(myNode));
ps.setString(++i, NodeProperties.getStandardNodePropertyByName(myNode, "creator"));
ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getStandardNodePropertyByName(myNode, "groupread")));
ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getStandardNodePropertyByName(myNode, "groupwrite")));
ps.setBoolean(++i, Boolean.valueOf(NodeProperties.getStandardNodePropertyByName(myNode, "publicread")));
ps.setObject(++i, paths.get(0).path, Types.OTHER);
ps.setObject(++i, paths.get(0).relativePath, Types.OTHER);
ps.setObject(++i, NodeUtils.getDbNodeType(myNode), Types.OTHER);
ps.setObject(++i, fromViewsToArray(ps, myNode, d -> d.getAccepts()), Types.OTHER);
ps.setObject(++i, fromViewsToArray(ps, myNode, d -> d.getProvides()), Types.OTHER);
Sara Bertocco
committed
return ps;
});
public Optional<Node> listNode(String path) {
String sql = "SELECT os.vos_path, n.node_id, type, async_trans, sticky, busy_state, creator_id, group_read, group_write,\n"
+ "is_public, content_length, created_on, last_modified, accept_views, provide_views\n"
+ "FROM node n\n"
+ "JOIN node_vos_path os ON n.node_id = os.node_id\n"
+ "WHERE n.path ~ (" + getFirstLevelChildrenSelector(path) + ")::lquery\n"
+ "OR os.vos_path = ? ORDER BY vos_path";
List<Node> parentAndChildren = jdbcTemplate.query(conn -> {
ps.setString(1, path);
ps.setString(2, path);
if (parentAndChildren.isEmpty()) {
return Optional.empty();
}
// Query returns parent as first node
Node node = parentAndChildren.get(0);
// Fill children
if (node instanceof ContainerNode && parentAndChildren.size() > 1) {
ContainerNode parent = (ContainerNode) node;
for (int i = 1; i < parentAndChildren.size(); i++) {
parent.getNodes().add(parentAndChildren.get(i));
}
}
public Node setNode(Node newNode) {
return setNode(newNode, false);
/**
* If recursive flag is true the update is applied to children too.
*/
public Node setNode(Node newNode, boolean recursive) {
Sonia Zorba
committed
sb.append(" SET group_read = ?, group_write = ?, is_public = ? ");
sb.append(" FROM node_vos_path p WHERE p.vos_path = ? AND p.node_id = node.node_id ");
} else {
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sb.toString());
int i = 0;
ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getNodePropertyByURI(newNode, NodeProperties.GROUP_READ_URI)));
ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getNodePropertyByURI(newNode, NodeProperties.GROUP_WRITE_URI)));
ps.setBoolean(++i, Boolean.valueOf(NodeProperties.getNodePropertyByURI(newNode, NodeProperties.PUBLIC_READ_URI)));
ps.setString(++i, vosPath);
return ps;
});
}
Sonia Zorba
committed
return newNode;
private String getFirstLevelChildrenSelector(String path) {
String select = "(SELECT path FROM node WHERE node_id = (SELECT node_id FROM node_vos_path WHERE vos_path = ?))::varchar || '";
if (!"/".equals(path)) {
select += ".";
}
select += "*{1}'";
return select;
Nicola Fulvio Calabria
committed
private String getAllLevelsChildrenSelector(String path) {
String select = "(SELECT path FROM node WHERE node_id = (SELECT node_id FROM node_vos_path WHERE vos_path = ?))::varchar || '";
if (!"/".equals(path)) {
select += ".";
}
select += "*{1,}'";
return select;
}
private Node getNodeFromResultSet(ResultSet rs) throws SQLException {
Sonia Zorba
committed
if (node instanceof DataNode) {
DataNode dataNode = (DataNode) node;
dataNode.setBusy(rs.getBoolean("busy_state"));
dataNode.setAccepts(getViews(rs.getArray("accept_views")));
dataNode.setProvides(getViews(rs.getArray("provide_views")));
Sonia Zorba
committed
}
node.setUri(getUri(rs.getString("vos_path")));
List<Property> properties = new ArrayList<>();
Sonia Zorba
committed
// Content length is required for CADC client compatibility
String contentLength = rs.getString("content_length");
addProperty(NodeProperties.LENGTH_URI, contentLength == null ? "0" : contentLength, properties);
Sonia Zorba
committed
Sonia Zorba
committed
String creationTime = rs.getString("created_on").replace(" ", "T");
Sonia Zorba
committed
addProperty(NodeProperties.INITIAL_CREATION_TIME_URI, creationTime, properties);
addProperty(NodeProperties.DATE_URI, creationTime, properties); // required by CADC
Sonia Zorba
committed
addProperty(NodeProperties.CREATOR_URI, rs.getString("creator_id"),
addProperty(NodeProperties.MODIFICATION_TIME_URI, rs.getString("last_modified"),
properties);
Sonia Zorba
committed
addProperty(NodeProperties.GROUP_READ_URI, getGroupsString(rs, "group_read"),
properties);
Sonia Zorba
committed
addProperty(NodeProperties.GROUP_WRITE_URI, getGroupsString(rs, "group_write"),
properties);
Sonia Zorba
committed
addProperty(NodeProperties.PUBLIC_READ_URI, String.valueOf(rs.getBoolean("is_public")),
properties);
Sonia Zorba
committed
addProperty("urn:async_trans", String.valueOf(rs.getBoolean("async_trans")),
properties);
addProperty("urn:sticky", String.valueOf(rs.getBoolean("sticky")), properties);
Sonia Zorba
committed
node.setProperties(properties);
public Optional<Long> getNodeId(String nodeVosPath) {
String sql = "SELECT node_id FROM node_vos_path WHERE vos_path = ? FOR UPDATE";
List<Long> nodeIdList = jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, nodeVosPath);
return ps;
}, (row, index) -> {
return row.getLong("node_id");
});
switch (nodeIdList.size()) {
case 0:
return Optional.empty();
case 1:
return Optional.of(nodeIdList.get(0));
default:
throw new InternalFaultException("More than 1 node id at path: " + nodeVosPath);
}
public Optional<Node> getNodeById(Long nodeId, boolean enforceTapeStoredCheck) {
String sql = "SELECT os.vos_path, loc.location_type, n.node_id, type, async_trans, sticky, busy_state, creator_id, group_read, group_write,\n"
+ "is_public, content_length, created_on, last_modified, accept_views, provide_views\n"
+ "FROM node n\n"
+ "JOIN node_vos_path os ON n.node_id = os.node_id\n"
+ "JOIN location loc ON n.location_id = loc.location_id\n"
+ "WHERE n.node_id = ?\n"
+ "FOR UPDATE";
List<Node> result = jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, nodeId);
return ps;
}, (row, index) -> {
if (enforceTapeStoredCheck && row.getString("location_type").equals("async")) {
throw new InternalFaultException(
"Node id: " + nodeId + " has async location type. "
+ "Failure due to enforced check.");
}
return getNodeFromResultSet(row);
});
switch (result.size()) {
case 0:
return Optional.empty();
case 1:
return Optional.of(result.get(0));
default:
throw new InternalFaultException("Multiple nodes with id: " + nodeId);
}
}
// First node is the root node
public List<Node> listNodesInBranch(Long rootNodeId, boolean enforceTapeStoredCheck) {
String sql = "SELECT os.vos_path, loc.location_type, n.node_id, type, async_trans, sticky, busy_state, creator_id, group_read, group_write,\n"
+ "is_public, content_length, created_on, last_modified, accept_views, provide_views\n"
+ "FROM node n\n"
+ "JOIN node_vos_path os ON n.node_id = os.node_id\n"
+ "JOIN location loc ON n.location_id = loc.location_id\n"
+ "WHERE n.path ~ ('*.' || ? || '.*')::lquery\n"
+ "ORDER BY n.path FOR UPDATE";
List<Node> result = jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, Long.toString(rootNodeId));
return ps;
}, (row, index) -> {
if (enforceTapeStoredCheck && row.getString("location_type").equals("async")) {
throw new InternalFaultException(
"At least one node in branch with root id: " + rootNodeId
+ " has async location type. "
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
+ "Failure due to enforced check.");
}
return getNodeFromResultSet(row);
});
return result;
}
public void setBranchBusy(Long rootNodeId, boolean busyState) {
String sql = "UPDATE node SET busy_state = ?\n"
+ "WHERE path ~ ('*.' || ? || '.*')::lquery";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setBoolean(1, busyState);
ps.setLong(2, rootNodeId);
return ps;
});
}
public void renameNode(Long nodeId, String name) {
String sql = "UPDATE node SET name = ?\n"
+ "WHERE path ~ ('*.' || ?)::lquery";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, name);
ps.setLong(2, nodeId);
return ps;
});
}
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
/*
// unused?
public Optional<String> getNodeLtreePathById(Long nodeId) {
String sql = "SELECT path FROM node WHERE node_id = ? FOR UPDATE";
List<String> pathList = jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, nodeId);
return ps;
}, (row, index) -> {
return row.getString("path");
});
switch (pathList.size()) {
case 0:
return Optional.empty();
case 1:
return Optional.of(pathList.get(0));
default:
throw new InternalFaultException("More than one id = " + nodeId);
}
}
//remove
public String getParentPath(Long id) {
String sql = "SELECT parent_path FROM node WHERE node_id = ?";
List<String> nodeIdList = jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, id);
return ps;
}, (row, index) -> {
return row.getString("parent_path");
});
if(nodeIdList.size() > 0)
{
return nodeIdList.get(0);
} else {
return null;
}
}
*/
public void moveNodeBranch(Long sourceRootId, Long destinationParentId)
{
String sql = "UPDATE node\n"+
"SET parent_path = ((SELECT path FROM node WHERE node_id = ?) ||\n"+
"(CASE WHEN node_id = ? THEN '' ELSE subpath(parent_path, index(parent_path,(?::varchar)::ltree)) END))\n" +
"WHERE path ~ ('*.' || ? || '.*')::lquery";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, destinationParentId);
ps.setLong(2, sourceRootId);
ps.setLong(3, sourceRootId);
ps.setLong(4, sourceRootId);
return ps;
});
}
Nicola Fulvio Calabria
committed
public void deleteNode(String path) {
int nodesWithPath = countNodesWithPath(path);
Nicola Fulvio Calabria
committed
if (nodesWithPath == 0) {
throw new IllegalStateException("Node at path "
Nicola Fulvio Calabria
committed
+ path + " not found");
}
Nicola Fulvio Calabria
committed
if (nodesWithPath > 1) {
throw new IllegalStateException("Multiple nodes at path " + path);
}
Nicola Fulvio Calabria
committed
String insertSql = "INSERT INTO deleted_node "
+ "(node_id, parent_path, parent_relative_path, "
+ "name, os_name, tstamp_wrapper_dir, type, location_id, format, "
+ "async_trans, busy_state, creator_id, group_read, "
Nicola Fulvio Calabria
committed
+ "group_write, is_public, delta, content_type, content_encoding, "
+ "content_length, content_md5, created_on, last_modified, "
+ "accept_views, provide_views, protocols, sticky)\n";
Nicola Fulvio Calabria
committed
String deleteSql = "DELETE \n"
+ "FROM node n\n"
+ "USING node_vos_path os\n"
+ "WHERE n.node_id = os.node_id AND\n"
+ "(n.path ~ (" + getAllLevelsChildrenSelector(path) + ")::lquery\n"
+ "OR os.vos_path = ?) RETURNING\n"
+ "n.node_id, parent_path, parent_relative_path, "
+ "name, os_name, tstamp_wrapper_dir, type, location_id, format, "
+ "async_trans, busy_state, creator_id, group_read, "
Nicola Fulvio Calabria
committed
+ "group_write, is_public, delta, content_type, content_encoding, "
+ "content_length, content_md5, created_on, last_modified, "
+ "accept_views, provide_views, protocols, sticky\n";
String withSql = "WITH del AS (" + deleteSql + ")";
String sql = withSql + insertSql + "SELECT * FROM del\n";
jdbcTemplate.update(sql, path, path);
Nicola Fulvio Calabria
committed
}
// utility method for deleteNode
Nicola Fulvio Calabria
committed
public int countNodesWithPath(String path) {
String sql = "SELECT COUNT(*) from "
+ "node_vos_path p "
+ "where p.vos_path = ?";
Nicola Fulvio Calabria
committed
Object[] args = {path};
int[] types = {Types.VARCHAR};
Nicola Fulvio Calabria
committed
return jdbcTemplate.queryForObject(sql, args, types, Integer.class);
}
public String getNodeOsName(String vosPath) {
String sql = "SELECT \n"
+ "(CASE WHEN os_name IS NOT NULL THEN os_name ELSE name END) AS os_name\n"
+ "FROM node n\n"
+ "JOIN node_vos_path p ON n.node_id = p.node_id\n"
+ "WHERE p.vos_path = ?";
Object[] args = {vosPath};
int[] types = {Types.VARCHAR};
return jdbcTemplate.queryForObject(sql, args, types, String.class);
}
public void setNodeLocation(String vosPath, int locationId, String nodeOsName) {
String sql = "UPDATE node SET location_id = ?, os_name = ? WHERE node_id = "
+ "(SELECT node_id FROM node_vos_path WHERE vos_path = ?)";
int updated = jdbcTemplate.update(sql, ps -> {
ps.setInt(1, locationId);
ps.setString(2, nodeOsName);
ps.setString(3, vosPath);
});
if (updated != 1) {
throw new InternalFaultException("Unable to set node location for path " + vosPath);
}
}
Sonia Zorba
committed
private String getGroupsString(ResultSet rs, String column) throws SQLException {
Array array = rs.getArray(column);
if (array == null) {
return null;
}
return String.join(" ", (String[]) array.getArray());
}
// If value is null does nothing
private void addProperty(String uri, String value, List<Property> list) {
if (value != null) {
Property prop = new Property();
prop.setUri(uri);
prop.setValue(value);
list.add(prop);
}
}
private String getUri(String path) {
return "vos://" + authority + path;
}
Sara Bertocco
committed
private NodePaths getPathsFromResultSet(ResultSet rs) throws SQLException {
NodePaths paths = new NodePaths(rs.getString("path"), rs.getString("relative_path"));
Sara Bertocco
committed
}
private Array fromPropertyToArray(PreparedStatement ps, String myProperty) throws SQLException {
if (myProperty == null || myProperty.isBlank()) {
Sara Bertocco
committed
return null;
} else {
return ps.getConnection().createArrayOf("varchar", myProperty.split(" "));
Sara Bertocco
committed
}
private Array fromViewsToArray(PreparedStatement ps, Node node, Function<DataNode, List<View>> viewsExtractor) throws SQLException {
if (node instanceof DataNode) {
DataNode dataNode = (DataNode) node;
List<View> views = viewsExtractor.apply(dataNode);
if (views != null && !views.isEmpty()) {
Object[] array = views.stream().map(v -> v.getUri()).toArray();
return ps.getConnection().createArrayOf("varchar", array);
}
}
return null;
}
if (array == null) {
return null;
}
try {
return Arrays.stream((String[]) array.getArray())
.map(uri -> {
View view = new View();
view.setUri(uri);
return view;
})
.collect(Collectors.toList());
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
/*
Map contains:
Key column
column name value
column name value
and value is a String containing comma separated groups having permissions
*/
private Map<String, List<String>> getPermissionsFromDB(String vosPath) {
String sql = "SELECT group_read, group_write "
+ "FROM node n JOIN node_vos_path p ON n.node_id = p.node_id "
+ "WHERE p.vos_path = ?";
return jdbcTemplate.query(sql, new Object[]{vosPath}, rs -> {
if (!rs.next()) {
throw new InternalFaultException("No records found for " + vosPath);
}
return Map.of(
"group_read", getArrayValue(rs, "group_read"),
"group_write", getArrayValue(rs, "group_write")
);
});
}
private List<String> getArrayValue(ResultSet rs, String key) throws SQLException {
Array array = rs.getArray(key);
if (array == null) {
return new ArrayList<>();
}
return Arrays.asList((String[]) array.getArray());
private void updatePermissionsRecursively(Node newNode, String vosPath) {
Map<String, List<String>> permissions = getPermissionsFromDB(vosPath);
List<String> existingGroupReadList = permissions.get("group_read");
List<String> existingGroupWriteList = permissions.get("group_write");
List<String> newGroupReadList = NodeProperties.getNodePropertyAsListByURI(newNode, NodeProperties.GROUP_READ_URI);
List<String> newGroupWriteList = NodeProperties.getNodePropertyAsListByURI(newNode, NodeProperties.GROUP_WRITE_URI);
Set<String> existingGroupRead = new HashSet<>(existingGroupReadList);
Set<String> existingGroupWrite = new HashSet<>(existingGroupWriteList);
Set<String> newGroupRead = new HashSet<>(newGroupReadList);
Set<String> newGroupWrite = new HashSet<>(newGroupWriteList);
Set<String> groupReadToAdd = differenceBetweenSets(newGroupRead, existingGroupRead);
Set<String> groupReadToRemove = differenceBetweenSets(existingGroupRead, newGroupRead);
Set<String> groupWriteToAdd = differenceBetweenSets(newGroupWrite, existingGroupWrite);
Set<String> groupWriteToRemove = differenceBetweenSets(existingGroupWrite, newGroupWrite);
String sql = "UPDATE node SET "
+ "group_read = update_array(group_read, ?, ?), "
+ "group_write = update_array(group_write, ?, ?), "
+ "is_public = ? "
+ "WHERE path <@ (SELECT path FROM node n "
+ "JOIN node_vos_path p ON n.node_id = p.node_id "
+ "AND p.vos_path = ?)";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
int i = 0;
ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupReadToAdd.toArray()));
ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupReadToRemove.toArray()));
ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupWriteToAdd.toArray()));
ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupWriteToRemove.toArray()));
ps.setBoolean(++i, Boolean.valueOf(NodeProperties.getNodePropertyByURI(newNode, NodeProperties.PUBLIC_READ_URI)));
ps.setString(++i, vosPath);
return ps;
});
}
// Returns the difference a minus b
private Set<String> differenceBetweenSets(Set<String> a, Set<String> b) {
Set<String> diff = new HashSet<>(a);
diff.removeAll(b);
private List<NodePaths> getNodePathsFromDB(String nodeURI) {
Sonia Zorba
committed
String path = nodeURI.replaceAll("vos://[^/]+", "");
String parentPath = NodeUtils.getParentPath(path);
String sql = "SELECT path, relative_path from "
+ "node n join node_vos_path p on n.node_id = p.node_id "
+ "where p.vos_path = ?";
List<NodePaths> paths = jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, parentPath);
return ps;
}, (row, index) -> {
return getPathsFromResultSet(row);
});
Sara Bertocco
committed
private class NodePaths {
private final String path;
private final String relativePath;
public NodePaths(String myPath, String myRelativePath) {
Sara Bertocco
committed
this.path = myPath;
Sara Bertocco
committed
public String toString() {
Sara Bertocco
committed
}
public String getRelativePath() {
return this.relativePath;