0
# Advanced Database Objects
1

2
Support for complex database objects including user-defined types, enums, domains, arrays, stored procedures, sequences, and other advanced database features.
3

4
## Capabilities
5

6
### User-Defined Types (UDTs)
7

8
Discovery and analysis of user-defined types including attributes and methods.
9

10
```java { .api }
11
/**
12
 * Gets all UDTs across all schemas
13
 * @returns List of all UDT definitions
14
 */
15
List<UDTDefinition> getUDTs();
16

17
/**
18
 * Gets UDTs within a specific schema
19
 * @param schema - Schema to search within
20
 * @returns List of UDT definitions in the schema
21
 */
22
List<UDTDefinition> getUDTs(SchemaDefinition schema);
23

24
/**
25
 * Gets a specific UDT by schema and name
26
 * @param schema - Schema containing the UDT
27
 * @param name - UDT name to lookup
28
 * @returns UDT definition or null if not found
29
 */
30
UDTDefinition getUDT(SchemaDefinition schema, String name);
31

32
/**
33
 * Gets a specific UDT with case sensitivity control
34
 * @param schema - Schema containing the UDT
35
 * @param name - UDT name to lookup
36
 * @param ignoreCase - Whether to ignore case in name matching
37
 * @returns UDT definition or null if not found
38
 */
39
UDTDefinition getUDT(SchemaDefinition schema, String name, boolean ignoreCase);
40

41
/**
42
 * Gets UDT attributes (properties/fields)
43
 * @returns List of attribute definitions for the UDT
44
 */
45
List<AttributeDefinition> getAttributes();
46

47
/**
48
 * Gets UDT methods/routines
49
 * @returns List of routine definitions for the UDT
50
 */
51
List<RoutineDefinition> getRoutines();
52
```
53

54
**Usage Examples:**
55

56
```java
57
import org.jooq.meta.UDTDefinition;
58
import org.jooq.meta.AttributeDefinition;
59
import org.jooq.meta.RoutineDefinition;
60

61
// Get all UDTs
62
List<UDTDefinition> udts = database.getUDTs(schema);
63
for (UDTDefinition udt : udts) {
64
    System.out.println("\nUDT: " + udt.getName());
65
    System.out.println("  Schema: " + udt.getSchema().getName());
66
    System.out.println("  Comment: " + udt.getComment());
67
    
68
    // Analyze UDT attributes
69
    List<AttributeDefinition> attributes = udt.getAttributes();
70
    System.out.println("  Attributes:");
71
    for (AttributeDefinition attr : attributes) {
72
        System.out.println("    - " + attr.getName() + 
73
                          " (" + attr.getType().getType() + ")");
74
        System.out.println("      Position: " + attr.getPosition());
75
        System.out.println("      Nullable: " + attr.getType().isNullable());
76
    }
77
    
78
    // Analyze UDT methods
79
    List<RoutineDefinition> methods = udt.getRoutines();
80
    if (!methods.isEmpty()) {
81
        System.out.println("  Methods:");
82
        for (RoutineDefinition method : methods) {
83
            System.out.println("    - " + method.getName() + "()");
84
        }
85
    }
86
}
87

88
// Get specific UDT
89
UDTDefinition addressUDT = database.getUDT(schema, "address_type");
90
if (addressUDT != null) {
91
    System.out.println("Found UDT: " + addressUDT.getQualifiedName());
92
}
93
```
94

95
### Enum Types
96

97
Discovery and analysis of enumeration types with literal values.
98

99
```java { .api }
100
/**
101
 * Gets enum types within a specific schema
102
 * @param schema - Schema to search within
103
 * @returns List of enum definitions in the schema
104
 */
105
List<EnumDefinition> getEnums(SchemaDefinition schema);
106

107
/**
108
 * Gets a specific enum by schema and name
109
 * @param schema - Schema containing the enum
110
 * @param name - Enum name to lookup
111
 * @returns Enum definition or null if not found
112
 */
113
EnumDefinition getEnum(SchemaDefinition schema, String name);
114

115
/**
116
 * Gets a specific enum with case sensitivity control
117
 * @param schema - Schema containing the enum
118
 * @param name - Enum name to lookup
119
 * @param ignoreCase - Whether to ignore case in name matching
120
 * @returns Enum definition or null if not found
121
 */
122
EnumDefinition getEnum(SchemaDefinition schema, String name, boolean ignoreCase);
123

124
/**
125
 * Gets enum literal values
126
 * @returns List of enum literal definitions
127
 */
128
List<EnumLiteralDefinition> getLiterals();
129
```
130

131
**Usage Examples:**
132

133
```java
134
import org.jooq.meta.EnumDefinition;
135
import org.jooq.meta.EnumLiteralDefinition;
136

137
// Get all enums in schema
138
List<EnumDefinition> enums = database.getEnums(schema);
139
for (EnumDefinition enumDef : enums) {
140
    System.out.println("\nEnum: " + enumDef.getName());
141
    System.out.println("  Values:");
142
    
143
    List<EnumLiteralDefinition> literals = enumDef.getLiterals();
144
    for (EnumLiteralDefinition literal : literals) {
145
        System.out.println("    - " + literal.getName() + 
146
                          " (position: " + literal.getPosition() + ")");
147
    }
148
}
149

150
// Get specific enum
151
EnumDefinition statusEnum = database.getEnum(schema, "order_status");
152
if (statusEnum != null) {
153
    List<EnumLiteralDefinition> values = statusEnum.getLiterals();
154
    System.out.println("Order status values: " + 
155
        values.stream().map(EnumLiteralDefinition::getName)
156
              .collect(Collectors.joining(", ")));
157
}
158
```
159

160
### Domain Types
161

162
Discovery and analysis of domain types with base types and constraints.
163

164
```java { .api }
165
/**
166
 * Gets all domains across all schemas
167
 * @returns List of all domain definitions
168
 */
169
List<DomainDefinition> getDomains();
170

171
/**
172
 * Gets domains within a specific schema
173
 * @param schema - Schema to search within
174
 * @returns List of domain definitions in the schema
175
 */
176
List<DomainDefinition> getDomains(SchemaDefinition schema);
177

178
/**
179
 * Gets a specific domain by schema and name
180
 * @param schema - Schema containing the domain
181
 * @param name - Domain name to lookup
182
 * @returns Domain definition or null if not found
183
 */
184
DomainDefinition getDomain(SchemaDefinition schema, String name);
185

186
/**
187
 * Gets the base type for the domain
188
 * @returns DataTypeDefinition for the underlying base type
189
 */
190
DataTypeDefinition getBaseType();
191

192
/**
193
 * Gets check constraints defined on the domain
194
 * @returns List of check constraint definitions for the domain
195
 */
196
List<CheckConstraintDefinition> getCheckConstraints();
197
```
198

199
**Usage Examples:**
200

201
```java
202
import org.jooq.meta.DomainDefinition;
203
import org.jooq.meta.DataTypeDefinition;
204
import org.jooq.meta.CheckConstraintDefinition;
205

206
// Get all domains
207
List<DomainDefinition> domains = database.getDomains(schema);
208
for (DomainDefinition domain : domains) {
209
    System.out.println("\nDomain: " + domain.getName());
210
    
211
    DataTypeDefinition baseType = domain.getBaseType();
212
    System.out.println("  Base Type: " + baseType.getType());
213
    System.out.println("  Length: " + baseType.getLength());
214
    System.out.println("  Nullable: " + baseType.isNullable());
215
    
216
    List<CheckConstraintDefinition> constraints = domain.getCheckConstraints();
217
    if (!constraints.isEmpty()) {
218
        System.out.println("  Constraints:");
219
        for (CheckConstraintDefinition constraint : constraints) {
220
            System.out.println("    - " + constraint.getName() + 
221
                              ": " + constraint.getCheckClause());
222
        }
223
    }
224
}
225

226
// Get specific domain
227
DomainDefinition emailDomain = database.getDomain(schema, "email_domain");
228
if (emailDomain != null) {
229
    System.out.println("Email domain base type: " + 
230
                      emailDomain.getBaseType().getType());
231
}
232
```
233

234
### Array Types
235

236
Discovery and analysis of array types with element type information.
237

238
```java { .api }
239
/**
240
 * Gets array types within a specific schema
241
 * @param schema - Schema to search within
242
 * @returns List of array definitions in the schema
243
 */
244
List<ArrayDefinition> getArrays(SchemaDefinition schema);
245

246
/**
247
 * Gets a specific array by schema and name
248
 * @param schema - Schema containing the array
249
 * @param name - Array name to lookup
250
 * @returns Array definition or null if not found
251
 */
252
ArrayDefinition getArray(SchemaDefinition schema, String name);
253

254
/**
255
 * Gets the element type for the array
256
 * @returns DataTypeDefinition for the array element type
257
 */
258
DataTypeDefinition getElementType();
259
```
260

261
**Usage Examples:**
262

263
```java
264
import org.jooq.meta.ArrayDefinition;
265
import org.jooq.meta.DataTypeDefinition;
266

267
// Get all arrays in schema
268
List<ArrayDefinition> arrays = database.getArrays(schema);
269
for (ArrayDefinition array : arrays) {
270
    System.out.println("\nArray: " + array.getName());
271
    
272
    DataTypeDefinition elementType = array.getElementType();
273
    System.out.println("  Element Type: " + elementType.getType());
274
    System.out.println("  Element Java Type: " + elementType.getJavaType());
275
}
276

277
// Get specific array type
278
ArrayDefinition tagsArray = database.getArray(schema, "tag_array");
279
if (tagsArray != null) {
280
    System.out.println("Tags array element type: " + 
281
                      tagsArray.getElementType().getType());
282
}
283
```
284

285
### Stored Procedures and Functions
286

287
Discovery and analysis of stored routines including parameters and return types.
288

289
```java { .api }
290
/**
291
 * Gets routines within a specific schema
292
 * @param schema - Schema to search within
293
 * @returns List of routine definitions in the schema
294
 */
295
List<RoutineDefinition> getRoutines(SchemaDefinition schema);
296

297
/**
298
 * Gets a specific routine by schema and name
299
 * @param schema - Schema containing the routine
300
 * @param name - Routine name to lookup
301
 * @returns Routine definition or null if not found
302
 */
303
RoutineDefinition getRoutine(SchemaDefinition schema, String name);
304

305
/**
306
 * Gets input parameters for the routine
307
 * @returns List of input parameter definitions
308
 */
309
List<ParameterDefinition> getInParameters();
310

311
/**
312
 * Gets output parameters for the routine
313
 * @returns List of output parameter definitions
314
 */
315
List<ParameterDefinition> getOutParameters();
316

317
/**
318
 * Gets input/output parameters for the routine
319
 * @returns List of input/output parameter definitions
320
 */
321
List<ParameterDefinition> getInOutParameters();
322

323
/**
324
 * Gets all parameters for the routine
325
 * @returns List of all parameter definitions
326
 */
327
List<ParameterDefinition> getAllParameters();
328

329
/**
330
 * Gets the return value parameter
331
 * @returns Parameter definition for return value or null
332
 */
333
ParameterDefinition getReturnValue();
334

335
/**
336
 * Gets the return type
337
 * @returns DataTypeDefinition for return type or null
338
 */
339
DataTypeDefinition getReturnType();
340

341
/**
342
 * Checks if routine is a function (vs procedure)
343
 * @returns true if the routine is a function
344
 */
345
boolean isFunction();
346

347
/**
348
 * Checks if routine is an aggregate function
349
 * @returns true if the routine is an aggregate function
350
 */
351
boolean isAggregate();
352
```
353

354
**Usage Examples:**
355

356
```java
357
import org.jooq.meta.RoutineDefinition;
358
import org.jooq.meta.ParameterDefinition;
359
import org.jooq.meta.DataTypeDefinition;
360

361
// Get all routines
362
List<RoutineDefinition> routines = database.getRoutines(schema);
363
for (RoutineDefinition routine : routines) {
364
    System.out.println("\nRoutine: " + routine.getName());
365
    System.out.println("  Type: " + (routine.isFunction() ? "FUNCTION" : "PROCEDURE"));
366
    System.out.println("  Aggregate: " + routine.isAggregate());
367
    
368
    // Analyze parameters
369
    List<ParameterDefinition> inParams = routine.getInParameters();
370
    if (!inParams.isEmpty()) {
371
        System.out.println("  Input Parameters:");
372
        for (ParameterDefinition param : inParams) {
373
            System.out.println("    - " + param.getName() + 
374
                              " (" + param.getType().getType() + ")");
375
            if (param.getDefaultValue() != null) {
376
                System.out.println("      Default: " + param.getDefaultValue());
377
            }
378
        }
379
    }
380
    
381
    List<ParameterDefinition> outParams = routine.getOutParameters();
382
    if (!outParams.isEmpty()) {
383
        System.out.println("  Output Parameters:");
384
        for (ParameterDefinition param : outParams) {
385
            System.out.println("    - " + param.getName() + 
386
                              " (" + param.getType().getType() + ")");
387
        }
388
    }
389
    
390
    // Check return type for functions
391
    if (routine.isFunction()) {
392
        DataTypeDefinition returnType = routine.getReturnType();
393
        if (returnType != null) {
394
            System.out.println("  Return Type: " + returnType.getType());
395
        }
396
    }
397
}
398

399
// Get specific routine
400
RoutineDefinition calculateTax = database.getRoutine(schema, "calculate_tax");
401
if (calculateTax != null && calculateTax.isFunction()) {
402
    System.out.println("Tax calculation function return type: " + 
403
                      calculateTax.getReturnType().getType());
404
}
405
```
406

407
### Sequences
408

409
Discovery and analysis of database sequences for auto-generation.
410

411
```java { .api }
412
/**
413
 * Gets all sequences across all schemas
414
 * @returns List of all sequence definitions
415
 */
416
List<SequenceDefinition> getSequences();
417

418
/**
419
 * Gets sequences within a specific schema
420
 * @param schema - Schema to search within
421
 * @returns List of sequence definitions in the schema
422
 */
423
List<SequenceDefinition> getSequences(SchemaDefinition schema);
424

425
/**
426
 * Gets a specific sequence by schema and name
427
 * @param schema - Schema containing the sequence
428
 * @param name - Sequence name to lookup
429
 * @returns Sequence definition or null if not found
430
 */
431
SequenceDefinition getSequence(SchemaDefinition schema, String name);
432

433
/**
434
 * Gets a specific sequence with case sensitivity control
435
 * @param schema - Schema containing the sequence
436
 * @param name - Sequence name to lookup
437
 * @param ignoreCase - Whether to ignore case in name matching
438
 * @returns Sequence definition or null if not found
439
 */
440
SequenceDefinition getSequence(SchemaDefinition schema, String name, boolean ignoreCase);
441
```
442

443
**Usage Examples:**
444

445
```java
446
import org.jooq.meta.SequenceDefinition;
447

448
// Get all sequences
449
List<SequenceDefinition> sequences = database.getSequences(schema);
450
for (SequenceDefinition sequence : sequences) {
451
    System.out.println("Sequence: " + sequence.getName());
452
    System.out.println("  Schema: " + sequence.getSchema().getName());
453
    System.out.println("  Qualified Name: " + sequence.getQualifiedName());
454
    
455
    if (sequence.getComment() != null) {
456
        System.out.println("  Comment: " + sequence.getComment());
457
    }
458
}
459

460
// Get specific sequence
461
SequenceDefinition userIdSeq = database.getSequence(schema, "user_id_seq");
462
if (userIdSeq != null) {
463
    System.out.println("Found user ID sequence: " + userIdSeq.getQualifiedName());
464
}
465
```
466

467
### Packages (Oracle-style)
468

469
Discovery and analysis of database packages containing routines and types.
470

471
```java { .api }
472
/**
473
 * Gets packages within a specific schema
474
 * @param schema - Schema to search within
475
 * @returns List of package definitions in the schema
476
 */
477
List<PackageDefinition> getPackages(SchemaDefinition schema);
478

479
/**
480
 * Gets a specific package by schema and name
481
 * @param schema - Schema containing the package
482
 * @param inputName - Package name to lookup
483
 * @returns Package definition or null if not found
484
 */
485
PackageDefinition getPackage(SchemaDefinition schema, String inputName);
486

487
/**
488
 * Gets UDTs within a specific package
489
 * @param pkg - Package to search within
490
 * @returns List of UDT definitions in the package
491
 */
492
List<UDTDefinition> getUDTs(PackageDefinition pkg);
493
```
494

495
### XML Schema Collections
496

497
Discovery and analysis of XML schema collections and types.
498

499
```java { .api }
500
/**
501
 * Gets all XML schema collections
502
 * @returns List of all XML schema collection definitions
503
 */
504
List<XMLSchemaCollectionDefinition> getXMLSchemaCollections();
505

506
/**
507
 * Gets XML schema collections within a specific schema
508
 * @param schema - Schema to search within
509
 * @returns List of XML schema collection definitions in the schema
510
 */
511
List<XMLSchemaCollectionDefinition> getXMLSchemaCollections(SchemaDefinition schema);
512

513
/**
514
 * Gets a specific XML schema collection by schema and name
515
 * @param schema - Schema containing the collection
516
 * @param name - Collection name to lookup
517
 * @returns XML schema collection definition or null if not found
518
 */
519
XMLSchemaCollectionDefinition getXMLSchemaCollection(SchemaDefinition schema, String name);
520
```
521

522
## Types
523

524
```java { .api }
525
interface UDTDefinition extends PackageDefinition {
526
    List<AttributeDefinition> getAttributes();
527
    List<RoutineDefinition> getRoutines();
528
}
529

530
interface AttributeDefinition extends TypedElementDefinition<UDTDefinition>, PositionedDefinition {
531
    UDTDefinition getContainer();
532
    DataTypeDefinition getType();
533
    int getPosition();
534
}
535

536
interface EnumDefinition extends Definition {
537
    List<EnumLiteralDefinition> getLiterals();
538
}
539

540
interface EnumLiteralDefinition extends Definition, PositionedDefinition {
541
    String getName();
542
    int getPosition();
543
}
544

545
interface DomainDefinition extends Definition {
546
    DataTypeDefinition getBaseType();
547
    List<CheckConstraintDefinition> getCheckConstraints();
548
}
549

550
interface ArrayDefinition extends Definition {
551
    DataTypeDefinition getElementType();
552
}
553

554
interface RoutineDefinition extends Definition {
555
    List<ParameterDefinition> getInParameters();
556
    List<ParameterDefinition> getOutParameters();
557
    List<ParameterDefinition> getInOutParameters();
558
    List<ParameterDefinition> getAllParameters();
559
    ParameterDefinition getReturnValue();
560
    DataTypeDefinition getReturnType();
561
    boolean isFunction();
562
    boolean isAggregate();
563
}
564

565
interface ParameterDefinition extends TypedElementDefinition<RoutineDefinition>, PositionedDefinition {
566
    RoutineDefinition getContainer();
567
    boolean isIn();
568
    boolean isOut();
569
    boolean isInOut();
570
    boolean isReturn();
571
    String getDefaultValue();
572
    int getPosition();
573
}
574

575
interface SequenceDefinition extends Definition {
576
    String getName();
577
    SchemaDefinition getSchema();
578
}
579

580
interface PackageDefinition extends Definition {
581
    String getName();
582
    SchemaDefinition getSchema();
583
}
584
```
585

586
**Usage Examples:**
587

588
```java
589
import org.jooq.meta.*;
590

591
// Comprehensive analysis of advanced objects
592
SchemaDefinition schema = database.getSchema("public");
593

594
// Analyze all advanced object types
595
System.out.println("=== Advanced Database Objects Analysis ===");
596

597
// UDTs
598
List<UDTDefinition> udts = database.getUDTs(schema);
599
System.out.println("\nUser-Defined Types: " + udts.size());
600

601
// Enums  
602
List<EnumDefinition> enums = database.getEnums(schema);
603
System.out.println("Enum Types: " + enums.size());
604

605
// Domains
606
List<DomainDefinition> domains = database.getDomains(schema);
607
System.out.println("Domain Types: " + domains.size());
608

609
// Arrays
610
List<ArrayDefinition> arrays = database.getArrays(schema);
611
System.out.println("Array Types: " + arrays.size());
612

613
// Routines
614
List<RoutineDefinition> routines = database.getRoutines(schema);
615
long functions = routines.stream().filter(RoutineDefinition::isFunction).count();
616
long procedures = routines.size() - functions;
617
System.out.println("Functions: " + functions);
618
System.out.println("Procedures: " + procedures);
619

620
// Sequences
621
List<SequenceDefinition> sequences = database.getSequences(schema);
622
System.out.println("Sequences: " + sequences.size());
623

624
// Packages
625
List<PackageDefinition> packages = database.getPackages(schema);
626
System.out.println("Packages: " + packages.size());
627
```