1
2
3
4
5
6 package org.tailormap.api.geotools.featuresources;
7
8 import jakarta.validation.constraints.NotNull;
9 import java.io.IOException;
10 import java.lang.invoke.MethodHandles;
11 import java.nio.ByteBuffer;
12 import java.sql.Connection;
13 import java.sql.DatabaseMetaData;
14 import java.sql.PreparedStatement;
15 import java.sql.ResultSet;
16 import java.sql.SQLException;
17 import java.sql.Statement;
18 import java.sql.Timestamp;
19 import java.text.MessageFormat;
20 import java.time.OffsetDateTime;
21 import java.time.ZoneId;
22 import java.util.ArrayList;
23 import java.util.HashMap;
24 import java.util.List;
25 import java.util.Locale;
26 import java.util.Map;
27 import java.util.UUID;
28 import java.util.regex.Pattern;
29 import java.util.stream.Collectors;
30 import org.apache.commons.dbcp.DelegatingConnection;
31 import org.geotools.api.feature.type.AttributeDescriptor;
32 import org.geotools.jdbc.JDBCDataStore;
33 import org.jspecify.annotations.NonNull;
34 import org.slf4j.Logger;
35 import org.slf4j.LoggerFactory;
36 import org.springframework.security.core.context.SecurityContextHolder;
37 import org.tailormap.api.persistence.TMFeatureType;
38 import org.tailormap.api.persistence.json.JDBCConnectionProperties;
39 import org.tailormap.api.viewer.model.AttachmentMetadata;
40
41
42 public final class AttachmentsHelper {
43 private static final Logger logger =
44 LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());
45
46 private static final Pattern NUMERIC_WITH_IDENTITY = Pattern.compile(
47 "(?i)\\b(?:int|integer|bigint|smallint|numeric|decimal|number)(?:\\s*\\(\\s*\\d+(?:\\s*,\\s*\\d+)?\\s*\\))?\\s+identity\\b");
48
49 private static final List<String> allowedPKTypesSupportingSize = List.of(
50
51
52
53 "CHARACTER",
54 "CHARACTER VARYING",
55 "CHAR",
56 "VARCHAR",
57
58 "NUMERIC",
59 "DECIMAL",
60
61
62 "NVARCHAR",
63 "NCHAR",
64
65 "VARCHAR2",
66 "NVARCHAR2",
67 "NUMBER",
68 "RAW");
69
70 private AttachmentsHelper() {
71
72 }
73
74 private static String getPostGISCreateAttachmentsTableStatement(
75 String tableName, String pkColumnName, String fkColumnType, String typeModifier, String schemaPrefix) {
76 if (!schemaPrefix.isEmpty()) {
77 schemaPrefix += ".";
78 }
79 return MessageFormat.format("""
80 CREATE TABLE IF NOT EXISTS {4}{0}_attachments (
81 {0}_pk {2}{3} NOT NULL REFERENCES {4}{0}({1}) ON DELETE CASCADE,
82 attachment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
83 file_name VARCHAR(255),
84 attribute_name VARCHAR(255) NOT NULL,
85 description TEXT,
86 attachment BYTEA NOT NULL,
87 attachment_size INTEGER NOT NULL,
88 mime_type VARCHAR(100),
89 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
90 created_by VARCHAR(255) NOT NULL)
91 """, tableName, pkColumnName, fkColumnType, typeModifier, schemaPrefix);
92 }
93
94 private static String getSQLServerCreateAttachmentsTableStatement(
95 String tableName, String pkColumnName, String fkColumnType, String typeModifier, String schemaPrefix) {
96 if (!schemaPrefix.isEmpty()) {
97 schemaPrefix += ".";
98 }
99 return MessageFormat.format("""
100 IF OBJECT_ID(N''{4}{0}_attachments'', ''U'') IS NULL
101 BEGIN
102 CREATE TABLE {4}{0}_attachments (
103 {0}_pk {2}{3} NOT NULL REFERENCES {4}{0}({1}) ON DELETE CASCADE,
104 attachment_id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
105 file_name NVARCHAR(255),
106 attribute_name VARCHAR(255) NOT NULL,
107 description NVARCHAR(MAX),
108 attachment VARBINARY(MAX) NOT NULL,
109 mime_type NVARCHAR(100),
110 attachment_size INT NOT NULL,
111 created_at DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
112 created_by NVARCHAR(255) NOT NULL)
113 END
114 """, tableName, pkColumnName, fkColumnType, typeModifier, schemaPrefix);
115 }
116
117 private static String getOracleCreateAttachmentsTableStatement(
118 String tableName, String pkColumnName, String fkColumnType, String typeModifier, String schemaPrefix) {
119 if (!schemaPrefix.isEmpty()) {
120 schemaPrefix += ".";
121 }
122
123 return MessageFormat.format("""
124 CREATE TABLE IF NOT EXISTS {4}{0}_ATTACHMENTS (
125 {0}_PK {2}{3} NOT NULL REFERENCES {4}{0}({1}) ON DELETE CASCADE,
126 ATTACHMENT_ID RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
127 FILE_NAME VARCHAR2(255),
128 ATTACHMENT BLOB NOT NULL,
129 ATTRIBUTE_NAME VARCHAR2(255) NOT NULL,
130 DESCRIPTION CLOB,
131 MIME_TYPE VARCHAR2(100),
132 ATTACHMENT_SIZE INT NOT NULL,
133 CREATED_AT TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
134 CREATED_BY VARCHAR2(255) NOT NULL)
135 """, tableName, pkColumnName, fkColumnType, typeModifier, schemaPrefix);
136 }
137
138
139
140
141
142
143
144
145
146
147 public static void createAttachmentTableForFeatureType(TMFeatureType featureType)
148 throws IOException, SQLException, IllegalArgumentException {
149 if (featureType == null
150 || featureType.getSettings() == null
151 || featureType.getSettings().getAttachmentAttributes() == null
152 || featureType.getSettings().getAttachmentAttributes().isEmpty()) {
153 throw new IllegalArgumentException("FeatureType "
154 + (featureType != null ? featureType.getName() : "null")
155 + " is invalid or has no attachment attributes defined in its settings");
156 }
157
158 featureType.getSettings().getAttachmentAttributes().stream()
159 .filter(attachmentAttributeType -> (attachmentAttributeType.getAttributeName() == null
160 || attachmentAttributeType.getAttributeName().isEmpty()))
161 .findAny()
162 .ifPresent(attachmentAttributeType -> {
163 throw new IllegalArgumentException("FeatureType "
164 + featureType.getName()
165 + " has an attachment attribute with invalid (null or empty) attribute name");
166 });
167
168 logger.debug(
169 "Creating attachment table for FeatureType: {} and attachment names {}",
170 featureType.getName(),
171 featureType.getSettings().getAttachmentAttributes());
172
173 JDBCDataStore ds = null;
174 try {
175 ds = (JDBCDataStore) new JDBCFeatureSourceHelper().createDataStore(featureType.getFeatureSource());
176
177 try (Connection conn = ds.getDataSource().getConnection();
178 Statement stmt = conn.createStatement()) {
179 String sql = getCreateAttachmentsForFeatureTypeStatements(featureType, ds);
180 logger.debug("About to create attachments table using statement:\n{}", sql);
181 stmt.execute(sql);
182 logger.info("Attachment table created for FeatureType: {}", featureType.getName());
183
184 sql = getCreateAttachmentsIndexForFeatureTypeStatements(featureType, ds);
185 logger.debug("About to create attachments table FK index using statement:\n{}", sql);
186 stmt.execute(sql);
187 logger.info("Attachment table FK index created for FeatureType: {}", featureType.getName());
188 }
189 } finally {
190 if (ds != null) {
191 ds.dispose();
192 }
193 }
194 }
195
196 public static void dropAttachmentTableForFeatureType(TMFeatureType featureType) throws IOException, SQLException {
197 JDBCDataStore ds = null;
198 try {
199 ds = (JDBCDataStore) new JDBCFeatureSourceHelper().createDataStore(featureType.getFeatureSource());
200 String schemaPrefix = ds.getDatabaseSchema();
201 if (!schemaPrefix.isEmpty()) {
202 schemaPrefix += ".";
203 }
204 String dropSql = MessageFormat.format("DROP TABLE {1}{0}_attachments", featureType.getName(), schemaPrefix);
205 logger.debug("About to drop attachments table using statement:\n{}", dropSql);
206 try (Connection conn = ds.getDataSource().getConnection();
207 Statement stmt = conn.createStatement()) {
208 stmt.execute(dropSql);
209 logger.info("Attachment table dropped for FeatureType: {}", featureType.getName());
210 }
211 } finally {
212 if (ds != null) {
213 ds.dispose();
214 }
215 }
216 }
217
218
219
220
221
222
223
224
225
226 private static String getCreateAttachmentsForFeatureTypeStatements(TMFeatureType featureType, JDBCDataStore ds)
227 throws IOException, IllegalArgumentException, SQLException {
228
229 String fkColumnType = null;
230 int fkColumnSize = 0;
231 AttributeDescriptor pkDescriptor =
232 ds.getSchema(featureType.getName()).getDescriptor(featureType.getPrimaryKeyAttribute());
233
234
235 try (Connection pooledConn = ds.getDataSource().getConnection()) {
236
237 @SuppressWarnings("PMD.CloseResource")
238 Connection conn = ((DelegatingConnection) pooledConn).getInnermostDelegate();
239 DatabaseMetaData metaData = conn.getMetaData();
240 try (ResultSet rs = metaData.getColumns(
241 conn.getCatalog(),
242 ds.getDatabaseSchema(),
243 featureType.getName(),
244 featureType.getPrimaryKeyAttribute())) {
245 if (rs.next()) {
246 fkColumnType = rs.getString("TYPE_NAME");
247 fkColumnSize = rs.getInt("COLUMN_SIZE");
248 }
249 }
250
251
252
253 if (fkColumnType == null) {
254 try (ResultSet rs = metaData.getColumns(
255 conn.getCatalog(),
256 ds.getDatabaseSchema(),
257 featureType.getName().toUpperCase(Locale.ROOT),
258 featureType.getPrimaryKeyAttribute().toUpperCase(Locale.ROOT))) {
259 if (rs.next()) {
260 fkColumnType = rs.getString("TYPE_NAME");
261 fkColumnSize = rs.getInt("COLUMN_SIZE");
262 }
263 }
264 }
265
266
267 if (fkColumnType == null) {
268 fkColumnType = (String) pkDescriptor.getUserData().get("org.geotools.jdbc.nativeTypeName");
269 }
270 }
271
272 String typeModifier = "";
273 if (fkColumnSize > 0) {
274 typeModifier = getValidModifier(fkColumnType, fkColumnSize);
275 }
276 logger.debug(
277 "Creating attachment table for feature type with primary key {} (native type: {}, meta type: {}, size:"
278 + " {} (modifier: {}))",
279 pkDescriptor.getLocalName(),
280 fkColumnType,
281 pkDescriptor.getUserData().get("org.geotools.jdbc.nativeTypeName"),
282 fkColumnSize,
283 typeModifier);
284
285 JDBCConnectionProperties connProperties = featureType.getFeatureSource().getJdbcConnection();
286 fkColumnType = getValidColumnType(fkColumnType, connProperties.getDbtype());
287 return switch (connProperties.getDbtype()) {
288 case POSTGIS ->
289 getPostGISCreateAttachmentsTableStatement(
290 featureType.getName(),
291 featureType.getPrimaryKeyAttribute(),
292 fkColumnType,
293 typeModifier,
294 ds.getDatabaseSchema());
295 case ORACLE ->
296 getOracleCreateAttachmentsTableStatement(
297 featureType.getName(),
298 featureType.getPrimaryKeyAttribute(),
299 fkColumnType,
300 typeModifier,
301 ds.getDatabaseSchema());
302 case SQLSERVER ->
303 getSQLServerCreateAttachmentsTableStatement(
304 featureType.getName(),
305 featureType.getPrimaryKeyAttribute(),
306 fkColumnType,
307 typeModifier,
308 ds.getDatabaseSchema());
309 default ->
310 throw new IllegalArgumentException(
311 "Unsupported database type for attachments: " + connProperties.getDbtype());
312 };
313 }
314
315 private static String getValidColumnType(String columnType, JDBCConnectionProperties.DbtypeEnum dbtype) {
316 if (dbtype.equals(JDBCConnectionProperties.DbtypeEnum.SQLSERVER)
317 && NUMERIC_WITH_IDENTITY.matcher(columnType).find()) {
318
319 columnType = columnType.replaceAll("(?i)\\s+identity\\b", "");
320 }
321
322 return columnType;
323 }
324
325 private static String getValidModifier(String columnType, int fkColumnSize) {
326 if (fkColumnSize > 0 && allowedPKTypesSupportingSize.contains(columnType.toUpperCase(Locale.ROOT))) {
327 if (columnType.equalsIgnoreCase("NUMERIC")
328 || columnType.equalsIgnoreCase("DECIMAL")
329 || columnType.equalsIgnoreCase("NUMBER")) {
330
331
332 return "(" + fkColumnSize + ",0)";
333 }
334 return "(" + fkColumnSize + ")";
335 } else {
336 return "";
337 }
338 }
339
340
341
342
343
344
345
346
347 private static String getCreateAttachmentsIndexForFeatureTypeStatements(TMFeatureType featureType, JDBCDataStore ds)
348 throws IllegalArgumentException {
349
350 String schemaPrefix = ds.getDatabaseSchema();
351 if (!schemaPrefix.isEmpty()) {
352 schemaPrefix += ".";
353 }
354
355 JDBCConnectionProperties connProperties = featureType.getFeatureSource().getJdbcConnection();
356 return switch (connProperties.getDbtype()) {
357 case POSTGIS ->
358 MessageFormat.format(
359 "CREATE INDEX IF NOT EXISTS {0}_attachments_fk ON {1}{0}_attachments({0}_pk)",
360 featureType.getName(), schemaPrefix);
361 case SQLSERVER -> MessageFormat.format("""
362 IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = ''{0}_attachments_fk'' AND object_id = OBJECT_ID(N''{1}{0}_attachments''))
363 BEGIN
364 CREATE INDEX {0}_attachments_fk ON {1}{0}_attachments({0}_pk)
365 END
366 """, featureType.getName(), schemaPrefix);
367 case ORACLE ->
368 MessageFormat.format(
369 "CREATE INDEX IF NOT EXISTS {1}{0}_attachments_fk ON {1}{0}_attachments({0}_pk)",
370 featureType.getName(), schemaPrefix)
371 .toUpperCase(Locale.ROOT);
372 default ->
373 throw new IllegalArgumentException(
374 "Unsupported database type for attachments: " + connProperties.getDbtype());
375 };
376 }
377
378
379 private static byte[] asBytes(UUID uuid) {
380 ByteBuffer bb = ByteBuffer.wrap(new byte[16]);
381 bb.putLong(uuid.getMostSignificantBits());
382 bb.putLong(uuid.getLeastSignificantBits());
383 return bb.array();
384 }
385
386 public static AttachmentMetadata insertAttachment(
387 TMFeatureType featureType, AttachmentMetadata attachment, Object primaryKey, byte[] fileData)
388 throws IOException, SQLException {
389
390
391 attachment.setAttachmentId(UUID.randomUUID());
392 attachment.setAttachmentSize((long) fileData.length);
393 attachment.createdAt(OffsetDateTime.now(ZoneId.of("UTC")));
394 attachment.setCreatedBy(
395 SecurityContextHolder.getContext().getAuthentication().getName());
396
397 logger.debug(
398 "Adding attachment {} for feature {}:{}, type {}: {} (bytes: {})",
399 attachment.getAttachmentId(),
400 featureType.getName(),
401 primaryKey,
402 attachment.getMimeType(),
403 attachment,
404 fileData.length);
405
406 JDBCDataStore ds = null;
407 try {
408 ds = (JDBCDataStore) new JDBCFeatureSourceHelper().createDataStore(featureType.getFeatureSource());
409
410 String insertSql = MessageFormat.format(
411 """
412 INSERT INTO {1}{0}_attachments (
413 {0}_pk, attachment_id, file_name, attribute_name, description, attachment, attachment_size,
414 mime_type, created_at, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
415 """, featureType.getName(), ds.getDatabaseSchema().isEmpty() ? "" : ds.getDatabaseSchema() + ".");
416
417 logger.debug("Insert attachment SQL: {}", insertSql);
418 try (Connection conn = ds.getDataSource().getConnection();
419 PreparedStatement stmt = conn.prepareStatement(insertSql)) {
420
421 stmt.setObject(1, primaryKey);
422 if (featureType
423 .getFeatureSource()
424 .getJdbcConnection()
425 .getDbtype()
426 .equals(JDBCConnectionProperties.DbtypeEnum.ORACLE)) {
427
428 stmt.setBytes(2, asBytes(attachment.getAttachmentId()));
429 } else {
430 stmt.setObject(2, attachment.getAttachmentId());
431 }
432 stmt.setString(3, attachment.getFileName());
433 stmt.setString(4, attachment.getAttributeName());
434 stmt.setString(5, attachment.getDescription());
435 stmt.setBytes(6, fileData);
436 stmt.setLong(7, fileData.length);
437 stmt.setString(8, attachment.getMimeType());
438 stmt.setTimestamp(9, Timestamp.from(attachment.getCreatedAt().toInstant()));
439 stmt.setString(10, attachment.getCreatedBy());
440
441 stmt.executeUpdate();
442
443 return attachment;
444 }
445 } finally {
446 if (ds != null) {
447 ds.dispose();
448 }
449 }
450 }
451
452 public static void deleteAttachment(UUID attachmentId, TMFeatureType featureType) throws IOException, SQLException {
453 JDBCDataStore ds = null;
454 try {
455 ds = (JDBCDataStore) new JDBCFeatureSourceHelper().createDataStore(featureType.getFeatureSource());
456
457 String deleteSql = MessageFormat.format(
458 """
459 DELETE FROM {1}{0}_attachments WHERE attachment_id = ?
460 """, featureType.getName(), ds.getDatabaseSchema().isEmpty() ? "" : ds.getDatabaseSchema() + ".");
461
462 try (Connection conn = ds.getDataSource().getConnection();
463 PreparedStatement stmt = conn.prepareStatement(deleteSql)) {
464 if (featureType
465 .getFeatureSource()
466 .getJdbcConnection()
467 .getDbtype()
468 .equals(JDBCConnectionProperties.DbtypeEnum.ORACLE)) {
469 stmt.setBytes(1, asBytes(attachmentId));
470 } else {
471 stmt.setObject(1, attachmentId);
472 }
473
474 stmt.executeUpdate();
475 }
476 } finally {
477 if (ds != null) {
478 ds.dispose();
479 }
480 }
481 }
482
483 public static List<AttachmentMetadata> listAttachmentsForFeature(TMFeatureType featureType, Object primaryKey)
484 throws IOException, SQLException {
485
486 List<AttachmentMetadata> attachments = new ArrayList<>();
487 JDBCDataStore ds = null;
488 try {
489 ds = (JDBCDataStore) new JDBCFeatureSourceHelper().createDataStore(featureType.getFeatureSource());
490 String querySql = MessageFormat.format(
491 """
492 SELECT
493 {0}_pk,
494 attachment_id,
495 file_name,
496 attribute_name,
497 description,
498 attachment_size,
499 mime_type,
500 created_at,
501 created_by
502 FROM {1}{0}_attachments WHERE {0}_pk = ?
503 """, featureType.getName(), ds.getDatabaseSchema().isEmpty() ? "" : ds.getDatabaseSchema() + ".");
504 try (Connection conn = ds.getDataSource().getConnection();
505 PreparedStatement stmt = conn.prepareStatement(querySql)) {
506
507 stmt.setObject(1, primaryKey);
508
509 try (ResultSet rs = stmt.executeQuery()) {
510 while (rs.next()) {
511 AttachmentMetadata a = getAttachmentMetadata(rs);
512 attachments.add(a);
513 }
514 }
515 }
516 } finally {
517 if (ds != null) {
518 ds.dispose();
519 }
520 }
521 return attachments;
522 }
523
524 public static AttachmentWithBinary getAttachment(TMFeatureType featureType, UUID attachmentId)
525 throws IOException, SQLException {
526
527 JDBCDataStore ds = null;
528 try {
529 byte[] attachment;
530 ds = (JDBCDataStore) new JDBCFeatureSourceHelper().createDataStore(featureType.getFeatureSource());
531 String querySql = MessageFormat.format(
532 "SELECT attachment, attachment_size, mime_type, file_name FROM {1}{0}_attachments WHERE attachment_id = ?",
533 featureType.getName(), ds.getDatabaseSchema().isEmpty() ? "" : ds.getDatabaseSchema() + ".");
534 try (Connection conn = ds.getDataSource().getConnection();
535 PreparedStatement stmt = conn.prepareStatement(querySql)) {
536
537 if (featureType
538 .getFeatureSource()
539 .getJdbcConnection()
540 .getDbtype()
541 .equals(JDBCConnectionProperties.DbtypeEnum.ORACLE)) {
542 stmt.setBytes(1, asBytes(attachmentId));
543 } else {
544 stmt.setObject(1, attachmentId);
545 }
546
547 try (ResultSet rs = stmt.executeQuery()) {
548 if (rs.next()) {
549 attachment = rs.getBytes("attachment");
550 AttachmentMetadata a = new AttachmentMetadata();
551 long size = rs.getLong("attachment_size");
552 if (!rs.wasNull()) {
553 a.setAttachmentSize(size);
554 }
555 a.setMimeType(rs.getString("mime_type"));
556 a.setFileName(rs.getString("file_name"));
557 return new AttachmentWithBinary(
558 a, ByteBuffer.wrap(attachment).asReadOnlyBuffer());
559 } else {
560 return null;
561 }
562 }
563 }
564 } finally {
565 if (ds != null) {
566 ds.dispose();
567 }
568 }
569 }
570
571
572
573
574
575
576
577
578
579
580 public static @NonNull Map<String, List<AttachmentMetadata>> listAttachmentsForFeaturesByFeatureId(
581 TMFeatureType featureType, List<Object> featurePKs) throws IOException {
582 List<AttachmentMetadataListItem> attachments = new ArrayList<>();
583 if (featurePKs == null || featurePKs.isEmpty()) {
584 return new HashMap<>();
585 }
586
587 JDBCDataStore ds = null;
588 try {
589 ds = (JDBCDataStore) new JDBCFeatureSourceHelper().createDataStore(featureType.getFeatureSource());
590 String querySql = MessageFormat.format(
591 """
592 SELECT
593 {0}_pk,
594 attachment_id,
595 file_name,
596 attribute_name,
597 description,
598 attachment_size,
599 mime_type,
600 created_at,
601 created_by
602 FROM {2}{0}_attachments WHERE {0}_pk IN ( {1} )
603 """,
604 featureType.getName(),
605 String.join(", ", featurePKs.stream().map(id -> "?").toArray(String[]::new)),
606 ds.getDatabaseSchema().isEmpty() ? "" : ds.getDatabaseSchema() + ".");
607
608 try (Connection conn = ds.getDataSource().getConnection();
609 PreparedStatement stmt = conn.prepareStatement(querySql)) {
610
611 Object firstPK = featurePKs.getFirst();
612 boolean isUUID = firstPK instanceof UUID;
613 boolean isByteBuffer = firstPK instanceof ByteBuffer;
614
615 switch (featureType.getFeatureSource().getJdbcConnection().getDbtype()) {
616 case ORACLE -> {
617 for (int i = 0; i < featurePKs.size(); i++) {
618 if (isUUID) {
619
620
621 stmt.setBytes(i + 1, asBytes((UUID) featurePKs.get(i)));
622 } else if (isByteBuffer) {
623
624 stmt.setBytes(i + 1, ((ByteBuffer) featurePKs.get(i)).array());
625 } else {
626 stmt.setObject(i + 1, featurePKs.get(i));
627 }
628 }
629 }
630 case SQLSERVER -> {
631 for (int i = 0; i < featurePKs.size(); i++) {
632 if (isUUID) {
633
634 stmt.setString(
635 i + 1, featurePKs.get(i).toString().toUpperCase(Locale.ROOT));
636 } else {
637 stmt.setObject(i + 1, featurePKs.get(i));
638 }
639 }
640 }
641 case POSTGIS -> {
642 for (int i = 0; i < featurePKs.size(); i++) {
643 stmt.setObject(i + 1, featurePKs.get(i));
644 }
645 }
646 default ->
647 throw new UnsupportedOperationException("Unsupported database type: "
648 + featureType
649 .getFeatureSource()
650 .getJdbcConnection()
651 .getDbtype());
652 }
653
654 try (ResultSet rs = stmt.executeQuery()) {
655 while (rs.next()) {
656 Object keyObject = rs.getObject(1);
657 if (isUUID
658 && featureType
659 .getFeatureSource()
660 .getJdbcConnection()
661 .getDbtype()
662 .equals(JDBCConnectionProperties.DbtypeEnum.ORACLE)) {
663
664 byte[] rawBytes = rs.getBytes(1);
665 ByteBuffer bb = ByteBuffer.wrap(rawBytes);
666 keyObject = new UUID(bb.getLong(), bb.getLong());
667 } else if (isUUID
668 && featureType
669 .getFeatureSource()
670 .getJdbcConnection()
671 .getDbtype()
672 .equals(JDBCConnectionProperties.DbtypeEnum.SQLSERVER)) {
673
674 keyObject = UUID.fromString(rs.getString(1));
675 } else if (isByteBuffer) {
676 assert keyObject instanceof byte[];
677 keyObject = ByteBuffer.wrap((byte[]) keyObject);
678 }
679 attachments.add(new AttachmentMetadataListItem(
680 AttachmentsHelper.fidFromPK(featureType, keyObject), getAttachmentMetadata(rs)));
681 }
682 }
683 } catch (SQLException ex) {
684 logger.error("Failed to get attachments for {}", featureType.getName(), ex);
685 }
686 } finally {
687 if (ds != null) {
688 ds.dispose();
689 }
690 }
691 logger.debug(
692 "Found {} attachments for {} features (features: {}, attachments: {})",
693 attachments.size(),
694 featurePKs.size(),
695 featurePKs,
696 attachments.toArray());
697
698 return attachments.stream()
699 .collect(Collectors.groupingBy(
700 AttachmentMetadataListItem::fid,
701 Collectors.mapping(AttachmentMetadataListItem::value, Collectors.toList())));
702 }
703
704
705
706
707
708
709
710
711
712 public static String fidFromPK(@NotNull TMFeatureType featureType, @NotNull Object featurePK) {
713 if (featurePK == null) {
714 throw new IllegalArgumentException("featurePK cannot be null");
715 }
716 if (featureType == null) {
717 throw new IllegalArgumentException("featureType cannot be null");
718 }
719 if (featurePK instanceof byte[] pkBytes) {
720 ByteBuffer bb = ByteBuffer.wrap(pkBytes);
721 UUID pkUUID = new UUID(bb.getLong(), bb.getLong());
722 return "%s.%s".formatted(featureType.getName(), pkUUID);
723 } else {
724 return "%s.%s".formatted(featureType.getName(), featurePK);
725 }
726 }
727
728 private static AttachmentMetadata getAttachmentMetadata(ResultSet rs) throws SQLException {
729 AttachmentMetadata a = new AttachmentMetadata();
730
731 Object idObj = rs.getObject("attachment_id");
732 if (idObj instanceof UUID u) {
733 a.setAttachmentId(u);
734 } else if (idObj instanceof byte[] b) {
735 ByteBuffer bb = ByteBuffer.wrap(b);
736 a.setAttachmentId(new UUID(bb.getLong(), bb.getLong()));
737 } else {
738 String s = rs.getString("attachment_id");
739 if (s != null && !s.isEmpty()) {
740 a.setAttachmentId(UUID.fromString(s));
741 }
742 }
743 a.setFileName(rs.getString("file_name"));
744 a.setAttributeName(rs.getString("attribute_name"));
745 a.setDescription(rs.getString("description"));
746 long size = rs.getLong("attachment_size");
747 if (!rs.wasNull()) {
748 a.setAttachmentSize(size);
749 }
750 a.setMimeType(rs.getString("mime_type"));
751 Timestamp ts = rs.getTimestamp("created_at");
752 if (ts != null) {
753 a.setCreatedAt(OffsetDateTime.ofInstant(ts.toInstant(), ZoneId.of("UTC")));
754 }
755 a.setCreatedBy(rs.getString("created_by"));
756 return a;
757 }
758
759 public record AttachmentWithBinary(
760 @NotNull AttachmentMetadata attachmentMetadata,
761 @NotNull ByteBuffer attachment) {}
762
763 private record AttachmentMetadataListItem(
764 @NotNull String fid, @NotNull AttachmentMetadata value) {}
765 }