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