0
# Database Metadata
1
2
Access to Phoenix database metadata including catalogs, schemas, tables, columns, primary keys, and indexes through JDBC-compatible interface for database introspection and schema discovery.
3
4
## Capabilities
5
6
### Meta Object Creation
7
8
Database metadata interface is accessed through the connection's meta() method.
9
10
```python { .api }
11
class Connection:
12
def meta(self):
13
"""
14
Creates a metadata interface for database introspection.
15
16
Returns:
17
Meta: Database metadata object
18
19
Raises:
20
ProgrammingError: If connection is closed
21
"""
22
```
23
24
### Meta Class
25
26
Provides methods for querying database schema information in JDBC-compatible format.
27
28
```python { .api }
29
class Meta:
30
"""Database metadata interface for schema introspection."""
31
32
def __init__(self, connection): ...
33
34
def get_catalogs(self):
35
"""
36
Returns available catalogs in the database.
37
38
Returns:
39
list: List of dictionaries with catalog information
40
Each dict contains catalog metadata fields
41
42
Raises:
43
ProgrammingError: If connection is closed
44
"""
45
46
def get_schemas(self, catalog=None, schemaPattern=None):
47
"""
48
Returns available schemas matching the criteria.
49
50
Parameters:
51
- catalog (str, optional): Catalog name filter
52
- schemaPattern (str, optional): Schema name pattern (SQL LIKE pattern)
53
54
Returns:
55
list: List of dictionaries with schema information
56
Each dict contains: TABLE_CATALOG, TABLE_SCHEM, etc.
57
58
Raises:
59
ProgrammingError: If connection is closed
60
"""
61
62
def get_tables(self, catalog=None, schemaPattern=None, tableNamePattern=None, typeList=None):
63
"""
64
Returns tables matching the specified criteria.
65
66
Parameters:
67
- catalog (str, optional): Catalog name filter
68
- schemaPattern (str, optional): Schema name pattern
69
- tableNamePattern (str, optional): Table name pattern
70
- typeList (list, optional): List of table types to include
71
72
Returns:
73
list: List of dictionaries with table information
74
Each dict contains: TABLE_CATALOG, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, etc.
75
76
Raises:
77
ProgrammingError: If connection is closed
78
"""
79
80
def get_columns(self, catalog=None, schemaPattern=None, tableNamePattern=None, columnNamePattern=None):
81
"""
82
Returns columns matching the specified criteria.
83
84
Parameters:
85
- catalog (str, optional): Catalog name filter
86
- schemaPattern (str, optional): Schema name pattern
87
- tableNamePattern (str, optional): Table name pattern
88
- columnNamePattern (str, optional): Column name pattern
89
90
Returns:
91
list: List of dictionaries with column information
92
Each dict contains: TABLE_CATALOG, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME,
93
DATA_TYPE, TYPE_NAME, COLUMN_SIZE, DECIMAL_DIGITS, NUM_PREC_RADIX,
94
NULLABLE, REMARKS, COLUMN_DEF, etc.
95
96
Raises:
97
ProgrammingError: If connection is closed
98
"""
99
100
def get_table_types(self):
101
"""
102
Returns available table types in the database.
103
104
Returns:
105
list: List of dictionaries with table type information
106
Each dict contains: TABLE_TYPE
107
108
Raises:
109
ProgrammingError: If connection is closed
110
"""
111
112
def get_type_info(self):
113
"""
114
Returns information about supported data types.
115
116
Returns:
117
list: List of dictionaries with data type information
118
Each dict contains: TYPE_NAME, DATA_TYPE, PRECISION, LITERAL_PREFIX,
119
LITERAL_SUFFIX, CREATE_PARAMS, NULLABLE, CASE_SENSITIVE, SEARCHABLE, etc.
120
121
Raises:
122
ProgrammingError: If connection is closed
123
"""
124
125
def get_primary_keys(self, catalog=None, schema=None, table=None):
126
"""
127
Returns primary key information for specified table.
128
129
Parameters:
130
- catalog (str, optional): Catalog name
131
- schema (str, optional): Schema name
132
- table (str, optional): Table name
133
134
Returns:
135
list: List of dictionaries with primary key information
136
Each dict contains: TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME,
137
KEY_SEQ, PK_NAME, plus Phoenix extensions: ASC_OR_DESC, DATA_TYPE,
138
TYPE_NAME, COLUMN_SIZE, TYPE_ID, VIEW_CONSTANT
139
140
Raises:
141
ProgrammingError: If connection is closed
142
"""
143
144
def get_index_info(self, catalog=None, schema=None, table=None, unique=False, approximate=False):
145
"""
146
Returns index information for specified table.
147
148
Parameters:
149
- catalog (str, optional): Catalog name
150
- schema (str, optional): Schema name
151
- table (str, optional): Table name
152
- unique (bool): Include only unique indexes if True
153
- approximate (bool): Allow approximate information
154
155
Returns:
156
list: List of dictionaries with index information
157
Each dict contains: TABLE_CAT, TABLE_SCHEM, TABLE_NAME, NON_UNIQUE,
158
INDEX_QUALIFIER, INDEX_NAME, TYPE, ORDINAL_POSITION, COLUMN_NAME,
159
ASC_OR_DESC, CARDINALITY, PAGES, FILTER_CONDITION, plus Phoenix extensions:
160
DATA_TYPE, TYPE_NAME, TYPE_ID, COLUMN_FAMILY, COLUMN_SIZE, ARRAY_SIZE
161
162
Raises:
163
ProgrammingError: If connection is closed
164
"""
165
```
166
167
## Usage Examples
168
169
### Basic Metadata Access
170
171
```python
172
import phoenixdb
173
174
conn = phoenixdb.connect('http://localhost:8765/')
175
meta = conn.meta()
176
177
# Get all schemas
178
schemas = meta.get_schemas()
179
for schema in schemas:
180
print(f"Schema: {schema['TABLE_SCHEM']}")
181
182
# Get all tables
183
tables = meta.get_tables()
184
for table in tables:
185
print(f"Table: {table['TABLE_SCHEM']}.{table['TABLE_NAME']} ({table['TABLE_TYPE']})")
186
187
# Get columns for a specific table
188
columns = meta.get_columns(schemaPattern='MY_SCHEMA', tableNamePattern='USERS')
189
for column in columns:
190
print(f"Column: {column['COLUMN_NAME']} ({column['TYPE_NAME']})")
191
```
192
193
### Schema Discovery
194
195
```python
196
def discover_schema(connection):
197
"""Discover and print complete database schema."""
198
meta = connection.meta()
199
200
print("=== Database Schema Discovery ===")
201
202
# Get all schemas
203
schemas = meta.get_schemas()
204
print(f"\nFound {len(schemas)} schemas:")
205
206
for schema in schemas:
207
schema_name = schema['TABLE_SCHEM'] or '<DEFAULT>'
208
print(f"\nSchema: {schema_name}")
209
210
# Get tables in this schema
211
tables = meta.get_tables(schemaPattern=schema['TABLE_SCHEM'])
212
print(f" Tables ({len(tables)}):")
213
214
for table in tables:
215
table_name = table['TABLE_NAME']
216
table_type = table['TABLE_TYPE']
217
print(f" {table_name} ({table_type})")
218
219
# Get columns for this table
220
columns = meta.get_columns(
221
schemaPattern=schema['TABLE_SCHEM'],
222
tableNamePattern=table_name
223
)
224
225
print(f" Columns ({len(columns)}):")
226
for column in columns:
227
col_name = column['COLUMN_NAME']
228
col_type = column['TYPE_NAME']
229
col_size = column['COLUMN_SIZE']
230
nullable = "NULL" if column['NULLABLE'] else "NOT NULL"
231
print(f" {col_name} {col_type}({col_size}) {nullable}")
232
233
# Usage
234
discover_schema(conn)
235
```
236
237
### Table Analysis
238
239
```python
240
def analyze_table(connection, schema_name, table_name):
241
"""Analyze specific table structure and constraints."""
242
meta = connection.meta()
243
244
print(f"=== Analyzing Table: {schema_name}.{table_name} ===")
245
246
# Get table information
247
tables = meta.get_tables(
248
schemaPattern=schema_name,
249
tableNamePattern=table_name
250
)
251
252
if not tables:
253
print("Table not found")
254
return
255
256
table_info = tables[0]
257
print(f"Table Type: {table_info['TABLE_TYPE']}")
258
if table_info.get('REMARKS'):
259
print(f"Remarks: {table_info['REMARKS']}")
260
261
# Get column details
262
columns = meta.get_columns(
263
schemaPattern=schema_name,
264
tableNamePattern=table_name
265
)
266
267
print(f"\nColumns ({len(columns)}):")
268
for column in columns:
269
col_name = column['COLUMN_NAME']
270
col_type = column['TYPE_NAME']
271
col_size = column.get('COLUMN_SIZE', 'N/A')
272
decimal_digits = column.get('DECIMAL_DIGITS')
273
nullable = column['NULLABLE']
274
default_val = column.get('COLUMN_DEF')
275
276
nullable_str = "NULL" if nullable == 1 else "NOT NULL" if nullable == 0 else "UNKNOWN"
277
278
print(f" {col_name}:")
279
print(f" Type: {col_type}")
280
print(f" Size: {col_size}")
281
if decimal_digits is not None:
282
print(f" Decimal Digits: {decimal_digits}")
283
print(f" Nullable: {nullable_str}")
284
if default_val:
285
print(f" Default: {default_val}")
286
287
# Get primary key information
288
primary_keys = meta.get_primary_keys(
289
schema=schema_name,
290
table=table_name
291
)
292
293
if primary_keys:
294
print(f"\nPrimary Key:")
295
pk_columns = sorted(primary_keys, key=lambda x: x['KEY_SEQ'])
296
for pk in pk_columns:
297
print(f" {pk['COLUMN_NAME']} (sequence: {pk['KEY_SEQ']})")
298
if primary_keys[0].get('PK_NAME'):
299
print(f" Constraint Name: {primary_keys[0]['PK_NAME']}")
300
301
# Get index information
302
indexes = meta.get_index_info(
303
schema=schema_name,
304
table=table_name
305
)
306
307
if indexes:
308
print(f"\nIndexes:")
309
index_groups = {}
310
for idx in indexes:
311
idx_name = idx['INDEX_NAME']
312
if idx_name not in index_groups:
313
index_groups[idx_name] = []
314
index_groups[idx_name].append(idx)
315
316
for idx_name, idx_columns in index_groups.items():
317
if idx_name: # Skip table statistics
318
unique = "UNIQUE" if not idx_columns[0]['NON_UNIQUE'] else ""
319
print(f" {idx_name} {unique}")
320
sorted_columns = sorted(idx_columns, key=lambda x: x['ORDINAL_POSITION'])
321
for idx_col in sorted_columns:
322
col_name = idx_col['COLUMN_NAME']
323
asc_desc = idx_col.get('ASC_OR_DESC', 'A')
324
order = "ASC" if asc_desc == 'A' else "DESC"
325
print(f" {col_name} {order}")
326
327
# Usage
328
analyze_table(conn, 'MY_SCHEMA', 'USERS')
329
```
330
331
### Data Type Discovery
332
333
```python
334
def show_supported_types(connection):
335
"""Display all supported data types."""
336
meta = connection.meta()
337
338
type_info = meta.get_type_info()
339
340
print("=== Supported Data Types ===")
341
for type_data in type_info:
342
type_name = type_data['TYPE_NAME']
343
data_type = type_data['DATA_TYPE']
344
precision = type_data.get('PRECISION', 'N/A')
345
nullable = type_data['NULLABLE']
346
347
nullable_str = "NULLABLE" if nullable == 1 else "NO NULLS" if nullable == 0 else "UNKNOWN"
348
349
print(f"{type_name}:")
350
print(f" JDBC Type Code: {data_type}")
351
print(f" Precision: {precision}")
352
print(f" Nullable: {nullable_str}")
353
354
if type_data.get('LITERAL_PREFIX'):
355
print(f" Literal Prefix: {type_data['LITERAL_PREFIX']}")
356
if type_data.get('LITERAL_SUFFIX'):
357
print(f" Literal Suffix: {type_data['LITERAL_SUFFIX']}")
358
359
print()
360
361
# Usage
362
show_supported_types(conn)
363
```
364
365
### Schema Comparison
366
367
```python
368
def compare_schemas(connection, schema1, schema2):
369
"""Compare two schemas for differences."""
370
meta = connection.meta()
371
372
print(f"=== Comparing Schemas: {schema1} vs {schema2} ===")
373
374
# Get tables from both schemas
375
tables1 = set()
376
for table in meta.get_tables(schemaPattern=schema1):
377
tables1.add(table['TABLE_NAME'])
378
379
tables2 = set()
380
for table in meta.get_tables(schemaPattern=schema2):
381
tables2.add(table['TABLE_NAME'])
382
383
# Find differences
384
only_in_schema1 = tables1 - tables2
385
only_in_schema2 = tables2 - tables1
386
common_tables = tables1 & tables2
387
388
if only_in_schema1:
389
print(f"\nTables only in {schema1}:")
390
for table in sorted(only_in_schema1):
391
print(f" {table}")
392
393
if only_in_schema2:
394
print(f"\nTables only in {schema2}:")
395
for table in sorted(only_in_schema2):
396
print(f" {table}")
397
398
print(f"\nCommon tables ({len(common_tables)}):")
399
for table in sorted(common_tables):
400
print(f" {table}")
401
402
# Compare column structures
403
cols1 = meta.get_columns(schemaPattern=schema1, tableNamePattern=table)
404
cols2 = meta.get_columns(schemaPattern=schema2, tableNamePattern=table)
405
406
cols1_names = {col['COLUMN_NAME']: col for col in cols1}
407
cols2_names = {col['COLUMN_NAME']: col for col in cols2}
408
409
col_diff = set(cols1_names.keys()) - set(cols2_names.keys())
410
if col_diff:
411
print(f" Columns only in {schema1}: {', '.join(sorted(col_diff))}")
412
413
col_diff = set(cols2_names.keys()) - set(cols1_names.keys())
414
if col_diff:
415
print(f" Columns only in {schema2}: {', '.join(sorted(col_diff))}")
416
417
# Usage
418
compare_schemas(conn, 'PROD_SCHEMA', 'TEST_SCHEMA')
419
```
420
421
### Phoenix-Specific Metadata
422
423
Phoenix extends standard JDBC metadata with additional fields:
424
425
```python
426
def show_phoenix_extensions(connection, schema_name, table_name):
427
"""Show Phoenix-specific metadata extensions."""
428
meta = connection.meta()
429
430
# Primary key extensions
431
primary_keys = meta.get_primary_keys(schema=schema_name, table=table_name)
432
if primary_keys:
433
print("Phoenix Primary Key Extensions:")
434
for pk in primary_keys:
435
print(f" Column: {pk['COLUMN_NAME']}")
436
print(f" ASC_OR_DESC: {pk.get('ASC_OR_DESC')}")
437
print(f" DATA_TYPE: {pk.get('DATA_TYPE')}")
438
print(f" TYPE_ID: {pk.get('TYPE_ID')}")
439
print(f" VIEW_CONSTANT: {pk.get('VIEW_CONSTANT')}")
440
441
# Index extensions
442
indexes = meta.get_index_info(schema=schema_name, table=table_name)
443
if indexes:
444
print("\nPhoenix Index Extensions:")
445
for idx in indexes:
446
if idx['INDEX_NAME']:
447
print(f" Index: {idx['INDEX_NAME']}")
448
print(f" Column Family: {idx.get('COLUMN_FAMILY')}")
449
print(f" Array Size: {idx.get('ARRAY_SIZE')}")
450
451
# Usage
452
show_phoenix_extensions(conn, 'MY_SCHEMA', 'MY_TABLE')
453
```