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