0
# Type System
1

2
Comprehensive type system for converting between Python and Phoenix data types, including support for date/time, numeric, binary, and array types with full precision handling.
3

4
## Import Statement
5

6
```python
7
from phoenixdb import Date, Time, Timestamp, DateFromTicks, TimeFromTicks, TimestampFromTicks, Binary
8
from phoenixdb import STRING, BINARY, NUMBER, DATETIME, BOOLEAN, ROWID
9
from phoenixdb.types import TypeHelper, ColumnType
10
```
11

12
Common import pattern:
13

14
```python
15
import phoenixdb.types
16
```
17

18
## Capabilities
19

20
### Type Constructor Functions
21

22
DB API 2.0 compliant functions for creating typed values compatible with Phoenix database operations.
23

24
```python { .api }
25
def Date(year, month, day):
26
    """
27
    Constructs a date object.
28

29
    Parameters:
30
    - year (int): Year component
31
    - month (int): Month component (1-12)
32
    - day (int): Day component
33

34
    Returns:
35
    datetime.date: Date object
36
    """
37

38
def Time(hour, minute, second):
39
    """
40
    Constructs a time object.
41

42
    Parameters:
43
    - hour (int): Hour component (0-23)
44
    - minute (int): Minute component (0-59)
45
    - second (int): Second component (0-59)
46

47
    Returns:
48
    datetime.time: Time object
49
    """
50

51
def Timestamp(year, month, day, hour, minute, second):
52
    """
53
    Constructs a datetime/timestamp object.
54

55
    Parameters:
56
    - year (int): Year component
57
    - month (int): Month component (1-12)
58
    - day (int): Day component
59
    - hour (int): Hour component (0-23)
60
    - minute (int): Minute component (0-59)
61
    - second (int): Second component (0-59)
62

63
    Returns:
64
    datetime.datetime: Datetime object
65
    """
66

67
def DateFromTicks(ticks):
68
    """
69
    Constructs a date object from UNIX timestamp.
70

71
    Parameters:
72
    - ticks (float): UNIX timestamp
73

74
    Returns:
75
    datetime.date: Date object
76
    """
77

78
def TimeFromTicks(ticks):
79
    """
80
    Constructs a time object from UNIX timestamp.
81

82
    Parameters:
83
    - ticks (float): UNIX timestamp
84

85
    Returns:
86
    datetime.time: Time object
87
    """
88

89
def TimestampFromTicks(ticks):
90
    """
91
    Constructs a datetime object from UNIX timestamp.
92

93
    Parameters:
94
    - ticks (float): UNIX timestamp
95

96
    Returns:
97
    datetime.datetime: Datetime object
98
    """
99

100
def Binary(value):
101
    """
102
    Constructs a binary object for handling binary data.
103

104
    Parameters:
105
    - value: Binary data (bytes, bytearray, or convertible)
106

107
    Returns:
108
    bytes: Binary data object
109
    """
110
```
111

112
### Type Constants
113

114
Column type objects for type comparison and identification in cursor descriptions.
115

116
```python { .api }
117
STRING: ColumnType
118
"""Type object for string-based columns (VARCHAR, CHAR)."""
119

120
BINARY: ColumnType
121
"""Type object for binary columns (BINARY, VARBINARY)."""
122

123
NUMBER: ColumnType
124
"""
125
Type object for numeric columns including:
126
INTEGER, UNSIGNED_INT, BIGINT, UNSIGNED_LONG, TINYINT, UNSIGNED_TINYINT,
127
SMALLINT, UNSIGNED_SMALLINT, FLOAT, UNSIGNED_FLOAT, DOUBLE, UNSIGNED_DOUBLE, DECIMAL
128
"""
129

130
DATETIME: ColumnType
131
"""
132
Type object for date/time columns including:
133
TIME, DATE, TIMESTAMP, UNSIGNED_TIME, UNSIGNED_DATE, UNSIGNED_TIMESTAMP
134
"""
135

136
BOOLEAN: ColumnType
137
"""Type object for boolean columns (Phoenix-specific extension)."""
138

139
ROWID: ColumnType
140
"""Type object for row identifiers (DB API 2.0 compatibility only)."""
141
```
142

143
### Type Helper Class
144

145
Utility class for internal type mapping and conversion between JDBC types and Python types.
146

147
```python { .api }
148
class TypeHelper:
149
    """Static utility methods for type conversion and mapping."""
150

151
    @staticmethod
152
    def from_param(param):
153
        """
154
        Retrieves type information from AvaticaParameter object.
155

156
        Parameters:
157
        - param: Protobuf AvaticaParameter object
158

159
        Returns:
160
        tuple: (field_name, rep, mutate_to, cast_from, is_array)
161
               - field_name: Attribute in TypedValue
162
               - rep: common_pb2.Rep enum
163
               - mutate_to: Function to cast to Phoenix values
164
               - cast_from: Function to cast from Phoenix values
165
               - is_array: Boolean indicating array parameter
166

167
        Raises:
168
        NotImplementedError: For unsupported JDBC type codes
169
        """
170

171
    @staticmethod
172
    def from_column(column):
173
        """
174
        Retrieves type information from column metadata.
175

176
        Parameters:
177
        - column: Protobuf TypedValue object
178

179
        Returns:
180
        tuple: (field_name, rep, mutate_to, cast_from)
181
               - field_name: Attribute in TypedValue
182
               - rep: common_pb2.Rep enum
183
               - mutate_to: Function to cast to Phoenix values
184
               - cast_from: Function to cast from Phoenix values
185

186
        Raises:
187
        NotImplementedError: For unsupported JDBC type codes
188
        """
189
```
190

191
### Column Type Class
192

193
Type comparison objects for cursor description analysis.
194

195
```python { .api }
196
class ColumnType:
197
    """Type object for column type comparison."""
198

199
    def __init__(self, eq_types):
200
        """
201
        Initialize with list of equivalent type names.
202

203
        Parameters:
204
        - eq_types (list): List of type names this object represents
205
        """
206

207
    def __eq__(self, other):
208
        """
209
        Compare with type name.
210

211
        Parameters:
212
        - other (str): Type name to compare
213

214
        Returns:
215
        bool: True if other matches this type
216
        """
217
```
218

219
## Usage Examples
220

221
### Creating Typed Values
222

223
```python
224
import phoenixdb
225
from datetime import datetime, date, time
226

227
# Date values
228
birth_date = phoenixdb.Date(1990, 5, 15)
229
today = phoenixdb.DateFromTicks(time.time())
230

231
# Time values
232
lunch_time = phoenixdb.Time(12, 30, 0)
233
current_time = phoenixdb.TimeFromTicks(time.time())
234

235
# Timestamp values
236
event_time = phoenixdb.Timestamp(2023, 12, 25, 14, 30, 0)
237
now = phoenixdb.TimestampFromTicks(time.time())
238

239
# Binary data
240
binary_data = phoenixdb.Binary(b'\x00\x01\x02\x03')
241

242
# Use in queries
243
cursor.execute("INSERT INTO events (id, event_date, event_time, data) VALUES (?, ?, ?, ?)",
244
               (1, birth_date, lunch_time, binary_data))
245
```
246

247
### Type Checking in Results
248

249
```python
250
cursor.execute("SELECT * FROM mixed_table")
251

252
# Check column types
253
for col in cursor.description:
254
    if col.type_code == phoenixdb.STRING:
255
        print(f"{col.name} is a string column")
256
    elif col.type_code == phoenixdb.NUMBER:
257
        print(f"{col.name} is a numeric column")
258
    elif col.type_code == phoenixdb.DATETIME:
259
        print(f"{col.name} is a date/time column")
260
    elif col.type_code == phoenixdb.BOOLEAN:
261
        print(f"{col.name} is a boolean column")
262
```
263

264
### Working with Python Native Types
265

266
```python
267
from datetime import datetime, date
268
from decimal import Decimal
269

270
# phoenixdb automatically handles Python native types
271
cursor = conn.cursor()
272

273
# Python datetime objects work directly
274
current_datetime = datetime.now()
275
cursor.execute("INSERT INTO logs (timestamp, message) VALUES (?, ?)",
276
               (current_datetime, "Log message"))
277

278
# Python date objects
279
event_date = date.today()
280
cursor.execute("INSERT INTO events (event_date, description) VALUES (?, ?)",
281
               (event_date, "Event description"))
282

283
# Decimal for precise numeric values
284
price = Decimal('199.95')
285
cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)",
286
               ("Product Name", price))
287
```
288

289
## Phoenix-Specific Type Handling
290

291
### Array Types
292

293
Phoenix supports array columns, which phoenixdb handles automatically.
294

295
```python
296
# Insert array values
297
cursor.execute("CREATE TABLE test_arrays (id INTEGER, numbers INTEGER ARRAY)")
298

299
# Arrays as Python lists
300
numbers = [1, 2, 3, 4, 5]
301
cursor.execute("INSERT INTO test_arrays VALUES (?, ?)", (1, numbers))
302

303
# Query array columns
304
cursor.execute("SELECT * FROM test_arrays")
305
row = cursor.fetchone()
306
array_values = row[1]  # Returns Python list
307
print(f"Array values: {array_values}")
308
```
309

310
### Precision Handling
311

312
Phoenix timestamps have nanosecond precision, but the remote protocol truncates to milliseconds.
313

314
```python
315
from datetime import datetime
316

317
# Microsecond precision is preserved up to millisecond level
318
precise_time = datetime(2023, 12, 25, 14, 30, 45, 123456)  # 123.456 ms
319
cursor.execute("INSERT INTO events (timestamp) VALUES (?)", (precise_time,))
320

321
# Retrieved value will have millisecond precision
322
cursor.execute("SELECT timestamp FROM events")
323
retrieved_time = cursor.fetchone()[0]
324
# Retrieved: 2023-12-25 14:30:45.123000 (microseconds truncated to ms)
325
```
326

327
### Unsigned Types
328

329
Phoenix supports unsigned numeric types that map to appropriate Python types.
330

331
```python
332
# Phoenix unsigned types automatically handled
333
cursor.execute("CREATE TABLE test_unsigned (id UNSIGNED_INT, big_num UNSIGNED_LONG)")
334

335
# Python int values work directly
336
cursor.execute("INSERT INTO test_unsigned VALUES (?, ?)", (4294967295, 18446744073709551615))
337

338
cursor.execute("SELECT * FROM test_unsigned")
339
row = cursor.fetchone()
340
print(f"Unsigned int: {row[0]}, Unsigned long: {row[1]}")
341
```
342

343
### Binary Data
344

345
```python
346
# Insert binary data
347
image_data = open('image.png', 'rb').read()
348
binary_obj = phoenixdb.Binary(image_data)
349

350
cursor.execute("CREATE TABLE images (id INTEGER, data VARBINARY)")
351
cursor.execute("INSERT INTO images VALUES (?, ?)", (1, binary_obj))
352

353
# Retrieve binary data
354
cursor.execute("SELECT data FROM images WHERE id = ?", (1,))
355
retrieved_data = cursor.fetchone()[0]  # Returns bytes object
356
```
357

358
## Internal Type Conversion
359

360
Phoenix uses Java/JDBC type system internally. phoenixdb handles conversion automatically:
361

362
### Java to Python Mapping
363

364
- Java SQL Date → Python datetime.date
365
- Java SQL Time → Python datetime.time
366
- Java SQL Timestamp → Python datetime.datetime
367
- Java BigDecimal → Python Decimal (for precise numeric values)
368
- Java byte[] → Python bytes
369
- Java primitive types → Python int/float/bool
370

371
### Conversion Functions
372

373
Internal utility functions handle the conversion:
374

375
```python
376
# These functions are used internally but available if needed
377
from phoenixdb.types import (
378
    date_from_java_sql_date, date_to_java_sql_date,
379
    time_from_java_sql_time, time_to_java_sql_time,
380
    datetime_from_java_sql_timestamp, datetime_to_java_sql_timestamp
381
)
382

383
# Manual conversion (rarely needed)
384
java_date_ms = date_to_java_sql_date(date.today())
385
python_date = date_from_java_sql_date(java_date_ms)
386
```
387

388
### Java Conversion Function Definitions
389

390
Complete API definitions for the Java conversion functions.
391

392
```python { .api }
393
def date_from_java_sql_date(n):
394
    """
395
    Converts Java SQL Date (milliseconds since epoch) to Python date.
396

397
    Parameters:
398
    - n (int): Milliseconds since epoch (Java Date format)
399

400
    Returns:
401
    datetime.date: Python date object
402
    """
403

404
def date_to_java_sql_date(d):
405
    """
406
    Converts Python date to Java SQL Date format.
407

408
    Parameters:
409
    - d (datetime.date): Python date object
410

411
    Returns:
412
    int: Milliseconds since epoch (Java Date format)
413
    """
414

415
def time_from_java_sql_time(n):
416
    """
417
    Converts Java SQL Time (milliseconds since midnight) to Python time.
418

419
    Parameters:
420
    - n (int): Milliseconds since midnight (Java Time format)
421

422
    Returns:
423
    datetime.time: Python time object
424
    """
425

426
def time_to_java_sql_time(t):
427
    """
428
    Converts Python time to Java SQL Time format.
429

430
    Parameters:
431
    - t (datetime.time): Python time object
432

433
    Returns:
434
    int: Milliseconds since midnight (Java Time format)
435
    """
436

437
def datetime_from_java_sql_timestamp(n):
438
    """
439
    Converts Java SQL Timestamp (milliseconds since epoch) to Python datetime.
440

441
    Parameters:
442
    - n (int): Milliseconds since epoch (Java Timestamp format)
443

444
    Returns:
445
    datetime.datetime: Python datetime object
446
    """
447

448
def datetime_to_java_sql_timestamp(d):
449
    """
450
    Converts Python datetime to Java SQL Timestamp format.
451

452
    Parameters:
453
    - d (datetime.datetime): Python datetime object
454

455
    Returns:
456
    int: Milliseconds since epoch (Java Timestamp format)
457
    """
458
```