package jnpf.limsService.provider;
|
|
import com.fasterxml.jackson.annotation.JsonIgnore;
|
import com.fasterxml.jackson.core.JsonProcessingException;
|
import com.fasterxml.jackson.databind.JsonNode;
|
import com.fasterxml.jackson.databind.ObjectMapper;
|
import jnpf.exception.DataException;
|
import lombok.Value;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
|
import javax.sql.DataSource;
|
import java.util.List;
|
import java.util.LinkedHashMap;
|
import java.util.Map;
|
import java.util.HashSet;
|
import java.util.Set;
|
import java.util.Objects;
|
import java.util.function.Supplier;
|
import java.util.regex.Pattern;
|
import java.util.stream.Collectors;
|
|
/** Only registered business objects can contribute source candidates. */
|
public class LimsElnSourceMetadata {
|
private static final Pattern RESTRICTED_COLUMN = Pattern.compile(
|
"(^|_)(password|pwd|secret|token|credential|signature|sign|salt|private_key|qianming|flow|tenant|delete_mark)(_|$)",
|
Pattern.CASE_INSENSITIVE);
|
private final JdbcTemplate configuration;
|
private final JdbcTemplate metadata;
|
private final Supplier<LimsElnSourceCatalog.Actor> actors;
|
private final ObjectMapper json = new ObjectMapper();
|
|
public LimsElnSourceMetadata(DataSource configurationDataSource, DataSource readOnlyDataSource,
|
Supplier<LimsElnSourceCatalog.Actor> actors) {
|
this.configuration = new JdbcTemplate(configurationDataSource);
|
this.metadata = new JdbcTemplate(readOnlyDataSource);
|
configuration.setQueryTimeout(5);
|
metadata.setQueryTimeout(5);
|
this.actors = actors;
|
}
|
|
public List<BusinessObject> objects() {
|
requireManager();
|
return configuration.query("SELECT object_id, display_name, root_anchor FROM lims_eln_provider_object "
|
+ "WHERE f_tenant_id = '0' AND biz_enabled = true ORDER BY object_id",
|
(row, index) -> new BusinessObject(row.getString(1), row.getString(2), row.getBoolean(3)));
|
}
|
|
public List<Candidate> fields(String objectId) {
|
requireManager();
|
List<String[]> objects = configuration.query("SELECT schema_name, table_name, tenant_column, delete_column FROM lims_eln_provider_object "
|
+ "WHERE f_tenant_id = '0' AND object_id = ? AND biz_enabled = true",
|
(row, index) -> new String[]{row.getString(1), row.getString(2), row.getString(3), row.getString(4)}, objectId);
|
if (objects.size() != 1) throw new DataException("Source object is not registered");
|
String[] object = objects.get(0);
|
FormHints hints = formHints(object[1]);
|
return metadata.query("SELECT c.oid, a.attnum, a.atttypid, a.attname, t.typname, "
|
+ "pg_catalog.col_description(c.oid, a.attnum) AS label "
|
+ "FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
|
+ "JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid "
|
+ "JOIN pg_catalog.pg_type t ON t.oid = a.atttypid "
|
+ "WHERE n.nspname = ? AND c.relname = ? AND c.relkind IN ('r', 'p') "
|
+ "AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum",
|
(row, index) -> object[2].equals(row.getString("attname"))
|
|| Objects.equals(object[3], row.getString("attname"))
|
|| RESTRICTED_COLUMN.matcher(row.getString("attname")).find()
|
|| hints.restricted.contains(row.getString("attname")) ? null
|
: new Candidate("c" + row.getLong("oid") + "_" + row.getInt("attnum")
|
+ "_" + row.getLong("atttypid"), hints.labels.getOrDefault(row.getString("attname"), row.getString("label") == null
|
? row.getString("attname") : row.getString("label")), dataType(row.getString("typname")),
|
row.getString("typname"), row.getString("attname"), hints.nameOptions.get(row.getString("attname")),
|
hints.nameLookups.get(row.getString("attname")), hints.conflictingNames.contains(row.getString("attname"))),
|
object[0], object[1]).stream().filter(Objects::nonNull)
|
.filter(field -> field.getDataType() != null).collect(Collectors.toList());
|
}
|
|
public Map<String, String> options(String objectId, String fieldId) {
|
Candidate field = fields(objectId).stream().filter(candidate -> candidate.getId().equals(fieldId)).findFirst()
|
.orElseThrow(() -> new DataException("Source candidate is no longer available"));
|
if (field.getNameOptions() != null) return new LinkedHashMap<>(field.getNameOptions());
|
NameLookup lookup = field.getNameLookup();
|
if (lookup == null || !"base_dictionary_data".equals(lookup.getTable())) return java.util.Collections.emptyMap();
|
String table = LimsElnSourceExecution.identifier(lookup.getSchema()) + "."
|
+ LimsElnSourceExecution.identifier(lookup.getTable());
|
List<String[]> rows = metadata.query("SELECT " + LimsElnSourceExecution.identifier(lookup.getKeyColumn())
|
+ ", " + LimsElnSourceExecution.identifier(lookup.getLabelColumn()) + " FROM " + table
|
+ " WHERE f_tenant_id = '0' AND (f_delete_mark IS NULL OR f_delete_mark = 0) AND f_enabled_mark = 1"
|
+ " AND " + LimsElnSourceExecution.identifier(lookup.getQualifierColumn()) + " = ?"
|
+ " ORDER BY " + LimsElnSourceExecution.identifier(lookup.getKeyColumn()) + " LIMIT 1001",
|
(row, index) -> new String[]{row.getString(1), row.getString(2)}, lookup.getQualifierValue());
|
if (rows.size() > 1000) throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_LIMIT", "Too many source enum options");
|
Map<String, String> options = new LinkedHashMap<>();
|
for (String[] row : rows) {
|
if (row[0] == null || row[0].trim().isEmpty() || row[1] == null || row[1].trim().isEmpty()) continue;
|
if (options.putIfAbsent(row[0], row[1]) != null) {
|
throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_AMBIGUOUS", "Source enum codes are not unique");
|
}
|
}
|
return options;
|
}
|
|
public List<BusinessObject> enumObjects() {
|
requireManager();
|
return configuration.query("SELECT o.object_id, o.display_name, o.root_anchor FROM lims_eln_provider_object o "
|
+ "JOIN lims_eln_provider_enum_object e ON e.f_tenant_id = o.f_tenant_id AND e.object_id = o.object_id "
|
+ "WHERE o.f_tenant_id = '0' AND o.biz_enabled = true ORDER BY o.object_id",
|
(row, index) -> new BusinessObject(row.getString(1), row.getString(2), row.getBoolean(3)));
|
}
|
|
public Map<String, String> enumOptions(EnumSelection selection) {
|
requireManager();
|
if (selection == null) throw new DataException("Source enum selection is required");
|
ObjectMapping object = objectMapping(selection.getObjectId());
|
List<String[]> policies = configuration.query("SELECT enabled_column, enabled_value FROM lims_eln_provider_enum_object "
|
+ "WHERE f_tenant_id = '0' AND object_id = ?",
|
(row, index) -> new String[]{row.getString(1), row.getString(2)}, selection.getObjectId());
|
if (policies.size() != 1) throw new DataException("Source object is not registered for enum options");
|
List<Candidate> candidates = fields(selection.getObjectId());
|
Candidate key = enumField(candidates, selection.getValueFieldId());
|
Candidate label = enumField(candidates, selection.getLabelFieldId());
|
String table = LimsElnSourceExecution.identifier(object.getSchema()) + "."
|
+ LimsElnSourceExecution.identifier(object.getTable());
|
StringBuilder sql = new StringBuilder("SELECT ").append(LimsElnSourceExecution.identifier(key.getColumn()))
|
.append(", ").append(LimsElnSourceExecution.identifier(label.getColumn())).append(" FROM ").append(table)
|
.append(" WHERE ").append(LimsElnSourceExecution.identifier(object.getTenantColumn())).append(" = '0'");
|
if (object.getDeleteColumn() != null) {
|
String deleted = LimsElnSourceExecution.identifier(object.getDeleteColumn());
|
sql.append(" AND (").append(deleted).append(" IS NULL OR ").append(deleted).append(" = 0)");
|
}
|
String[] policy = policies.get(0);
|
if (policy[0] != null) {
|
if (candidates.stream().noneMatch(field -> field.getColumn().equals(policy[0]))) {
|
throw new DataException("Source enum enabled field is unavailable");
|
}
|
sql.append(" AND ").append(LimsElnSourceExecution.identifier(policy[0])).append("::text = ?");
|
}
|
sql.append(" ORDER BY ").append(LimsElnSourceExecution.identifier(key.getColumn())).append(" LIMIT 1001");
|
return metadata.execute((org.springframework.jdbc.core.ConnectionCallback<Map<String, String>>) connection -> {
|
connection.setReadOnly(true);
|
connection.setAutoCommit(false);
|
try {
|
LimsElnSourceExecution.enforceReadOnlyTransaction(connection);
|
try (java.sql.PreparedStatement query = connection.prepareStatement(sql.toString())) {
|
query.setQueryTimeout(5);
|
if (policy[0] != null) query.setString(1, policy[1]);
|
Map<String, String> options = new LinkedHashMap<>();
|
try (java.sql.ResultSet rows = query.executeQuery()) {
|
int count = 0;
|
while (rows.next()) {
|
if (++count > 1000) throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_LIMIT", "Too many source enum options");
|
String value = rows.getString(1);
|
String name = rows.getString(2);
|
if (value == null || value.trim().isEmpty() || name == null || name.trim().isEmpty()) continue;
|
if (options.putIfAbsent(value, name) != null) {
|
throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_AMBIGUOUS", "Source enum codes are not unique");
|
}
|
}
|
}
|
return options;
|
}
|
} finally { connection.rollback(); }
|
});
|
}
|
|
private static Candidate enumField(List<Candidate> candidates, String id) {
|
return candidates.stream().filter(field -> field.getId().equals(id) && "TEXT".equals(field.getDataType()))
|
.findFirst().orElseThrow(() -> new DataException("Source enum code and name must be available text fields"));
|
}
|
|
NameLookup enumNameLookup(EnumSelection selection) {
|
requireManager();
|
if (selection == null || enumObjects().stream().noneMatch(object -> object.getId().equals(selection.getObjectId()))) {
|
throw new DataException("Source object is not registered for enum options");
|
}
|
ObjectMapping object = objectMapping(selection.getObjectId());
|
List<Candidate> candidates = fields(selection.getObjectId());
|
return new NameLookup(object.getSchema(), object.getTable(),
|
enumField(candidates, selection.getValueFieldId()).getColumn(),
|
enumField(candidates, selection.getLabelFieldId()).getColumn(), null, null,
|
object.getTenantColumn(), object.getDeleteColumn());
|
}
|
|
Map<String, Map<String, String>> freezeColumnIdentities(LimsElnReadonlyResolver.FrozenDefinition definition) {
|
requireManager();
|
return metadata.execute((org.springframework.jdbc.core.ConnectionCallback<Map<String, Map<String, String>>>)
|
connection -> LimsElnSourceExecution.columnIdentities(connection, definition, definition.getFields().keySet()));
|
}
|
|
private FormHints formHints(String table) {
|
FormHints hints = new FormHints();
|
List<String[]> forms = configuration.query("SELECT f_tables_data, f_form_data, f_db_link_id FROM base_visual_release "
|
+ "WHERE f_tenant_id = '0' AND f_state = 1 AND (f_delete_mark IS NULL OR f_delete_mark = 0) "
|
+ "AND strpos(f_tables_data, ?) > 0",
|
(row, index) -> new String[]{row.getString(1), row.getString(2), row.getString(3)}, table);
|
for (String[] form : forms) {
|
try {
|
if (form[0] == null || form[0].trim().isEmpty()) continue;
|
JsonNode tables = json.readTree(form[0]);
|
if (tables == null || !tables.isArray()) throw new DataException("Published source form metadata is invalid");
|
String main = null;
|
boolean matching = false;
|
for (JsonNode entry : tables) {
|
String name = entry.path("table").asText();
|
if ("1".equals(entry.path("typeId").asText())) main = name;
|
if (table.equals(name)) matching = true;
|
}
|
if (matching) {
|
JsonNode definition = json.readTree(form[1]);
|
if (definition == null || !definition.isObject() || !definition.path("fields").isArray()) {
|
throw new DataException("Published source form metadata is invalid");
|
}
|
// Unknown connections contribute restrictions, never names or executable lookup rules.
|
boolean trustedNames = form[2] == null || form[2].isEmpty() || "0".equals(form[2]);
|
collectHints(definition.path("fields"), table, main, hints, 0, trustedNames);
|
}
|
} catch (JsonProcessingException | IllegalArgumentException e) {
|
throw new DataException("Published source form metadata is invalid");
|
}
|
}
|
hints.nameOptions.keySet().stream().filter(hints.nameLookups::containsKey).forEach(hints.conflictingNames::add);
|
hints.dynamicNames.stream().filter(column -> hints.nameOptions.containsKey(column)
|
|| hints.nameLookups.containsKey(column)).forEach(hints.conflictingNames::add);
|
for (String column : hints.conflictingNames) {
|
hints.nameOptions.remove(column);
|
hints.nameLookups.remove(column);
|
}
|
return hints;
|
}
|
|
private void collectHints(JsonNode fields, String table, String inheritedTable,
|
FormHints hints, int depth, boolean trustedNames) {
|
if (depth > 20) throw new DataException("Source form nesting exceeds limits");
|
if (!fields.isArray()) throw new DataException("Published source form metadata is invalid");
|
for (JsonNode field : fields) {
|
JsonNode config = field.path("__config__");
|
if (!field.isObject() || !config.isObject()) {
|
throw new DataException("Published source form metadata is invalid");
|
}
|
String fieldTable = config.path("tableName").asText("");
|
if (fieldTable.isEmpty()) fieldTable = inheritedTable;
|
String column = field.path("__vModel__").asText();
|
String label = config.path("label").asText();
|
if (table.equals(fieldTable) && !column.isEmpty()) {
|
String control = config.path("jnpfKey").asText();
|
if (field.path("multiple").asBoolean() || field.path("showPassword").asBoolean()
|
|| "password".equals(field.path("type").asText())
|
|| "signature".equals(control) || "sign".equals(control) || "checkbox".equals(control)
|
|| "uploadImg".equals(control) || "uploadFile".equals(control)
|
|| config.path("isVirtualField").asBoolean()) {
|
hints.restricted.add(column);
|
}
|
if (trustedNames && !label.trim().isEmpty()) {
|
hints.labels.merge(column, label, (previous, next) -> previous.compareTo(next) <= 0 ? previous : next);
|
}
|
if (trustedNames && ("select".equals(control) || "radio".equals(control))
|
&& "dynamic".equals(config.path("dataType").asText())) {
|
hints.dynamicNames.add(column);
|
}
|
if (trustedNames && ("select".equals(control) || "radio".equals(control))
|
&& "static".equals(config.path("dataType").asText())) {
|
Map<String, String> names = staticNames(field);
|
Map<String, String> previous = hints.nameOptions.putIfAbsent(column, names);
|
if (previous != null) {
|
names.forEach((code, name) -> {
|
String existing = previous.putIfAbsent(code, name);
|
if (existing != null && !existing.equals(name)) hints.conflictingNames.add(column);
|
});
|
if (previous.size() > 1000) {
|
throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_LIMIT", "Too many source enum options");
|
}
|
}
|
}
|
if (trustedNames && ("select".equals(control) || "radio".equals(control))
|
&& "dictionary".equals(config.path("dataType").asText())) {
|
String key = field.path("props").path("value").asText();
|
String category = config.path("dictionaryType").asText("");
|
if (!("id".equals(key) || "enCode".equals(key)) || category.trim().isEmpty()
|
|| !"fullName".equals(field.path("props").path("label").asText())) {
|
throw new DataException("Invalid source dictionary metadata");
|
}
|
NameLookup lookup = platformNameLookup("base_dictionary_data", "id".equals(key) ? "f_id" : "f_en_code",
|
"f_full_name", "f_dictionary_type_id", category);
|
NameLookup previous = hints.nameLookups.putIfAbsent(column, lookup);
|
if (previous != null && !previous.equals(lookup)) {
|
hints.conflictingNames.add(column);
|
}
|
}
|
if (trustedNames && "userSelect".equals(control) && !hints.restricted.contains(column)) {
|
NameLookup lookup = platformNameLookup("base_user", "f_id", "f_real_name", null, null);
|
NameLookup previous = hints.nameLookups.putIfAbsent(column, lookup);
|
if (previous != null && !previous.equals(lookup)) {
|
hints.conflictingNames.add(column);
|
}
|
}
|
}
|
JsonNode children = config.path("children");
|
if (!children.isMissingNode() && !children.isNull()) {
|
collectHints(children, table, fieldTable, hints, depth + 1, trustedNames);
|
}
|
}
|
}
|
|
private static class FormHints {
|
private final Map<String, String> labels = new LinkedHashMap<>();
|
private final Set<String> restricted = new HashSet<>();
|
private final Map<String, Map<String, String>> nameOptions = new LinkedHashMap<>();
|
private final Map<String, NameLookup> nameLookups = new LinkedHashMap<>();
|
private final Set<String> conflictingNames = new HashSet<>();
|
private final Set<String> dynamicNames = new HashSet<>();
|
}
|
|
private NameLookup platformNameLookup(String table, String key, String label, String qualifier, String value) {
|
List<String> schemas = configuration.query("SELECT n.nspname FROM pg_catalog.pg_class c "
|
+ "JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
|
+ "WHERE c.oid = pg_catalog.to_regclass(?) AND c.relkind IN ('r', 'p')",
|
(row, index) -> row.getString(1), table);
|
if (schemas.size() != 1) throw new DataException("Source display-name object is unavailable");
|
return new NameLookup(schemas.get(0), table, key, label, qualifier, value, "f_tenant_id", "f_delete_mark");
|
}
|
|
private static Map<String, String> staticNames(JsonNode field) {
|
JsonNode options = field.path("options");
|
String key = field.path("props").path("value").asText("");
|
String label = field.path("props").path("label").asText("");
|
if (!options.isArray() || options.size() > 1000 || key.isEmpty() || label.isEmpty()) {
|
throw new DataException("Invalid source display-name metadata");
|
}
|
Map<String, String> names = new LinkedHashMap<>();
|
for (JsonNode option : options) {
|
if (!option.path(key).isTextual() || !option.path(label).isTextual()
|
|| option.path(key).asText().trim().isEmpty() || option.path(label).asText().trim().isEmpty()
|
|| names.putIfAbsent(option.path(key).asText(), option.path(label).asText()) != null) {
|
throw new DataException("Invalid or ambiguous source display-name metadata");
|
}
|
}
|
return names;
|
}
|
|
ObjectMapping objectMapping(String objectId) {
|
requireManager();
|
List<ObjectMapping> result = configuration.query("SELECT schema_name, table_name, id_column, tenant_column, root_anchor, delete_column "
|
+ "FROM lims_eln_provider_object WHERE f_tenant_id = '0' AND object_id = ? AND biz_enabled = true",
|
(row, index) -> new ObjectMapping(row.getString(1), row.getString(2), row.getString(3),
|
row.getString(4), row.getBoolean(5), row.getString(6)), objectId);
|
if (result.size() != 1) throw new DataException("Source object is not registered");
|
return result.get(0);
|
}
|
|
private void requireManager() {
|
LimsElnSourceCatalog.Actor actor = actors.get();
|
if (actor == null || !"0".equals(actor.getTenantId()) || actor.getUserId() == null
|
|| actor.getUserId().trim().isEmpty() || !actor.isFieldManager()) {
|
throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_FORBIDDEN", "Field management permission required");
|
}
|
}
|
|
private static String dataType(String postgresType) {
|
switch (postgresType) {
|
case "text": case "varchar": case "bpchar": return "TEXT";
|
case "numeric": case "int2": case "int4": case "int8": return "DECIMAL";
|
case "date": return "DATE";
|
case "timestamp": case "timestamptz": return "DATETIME";
|
case "bool": return "BOOLEAN";
|
default: return null;
|
}
|
}
|
|
@Value
|
@com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
|
public static class BusinessObject {
|
String id;
|
String displayName;
|
boolean rootAnchor;
|
}
|
|
@Value
|
@com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
|
public static class Candidate {
|
String id;
|
String displayName;
|
String dataType;
|
@JsonIgnore
|
String postgresType;
|
@JsonIgnore
|
String column;
|
Map<String, String> nameOptions;
|
@JsonIgnore
|
NameLookup nameLookup;
|
boolean nameRuleConflict;
|
|
public boolean isDisplayNameAvailable() {
|
return "TEXT".equals(dataType) && (nameLookup != null || nameOptions != null && !nameOptions.isEmpty());
|
}
|
}
|
|
@Value
|
static class NameLookup {
|
String schema;
|
String table;
|
String keyColumn;
|
String labelColumn;
|
String qualifierColumn;
|
String qualifierValue;
|
String tenantColumn;
|
String deleteColumn;
|
}
|
|
@lombok.Data
|
@com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
|
public static class EnumSelection {
|
private String objectId;
|
private String valueFieldId;
|
private String labelFieldId;
|
}
|
|
@Value
|
static class ObjectMapping {
|
String schema;
|
String table;
|
String idColumn;
|
String tenantColumn;
|
boolean rootAnchor;
|
String deleteColumn;
|
}
|
}
|
