0
# Type System
1

2
Comprehensive type system for data conversion, validation, and SQL integration with automatic type inference and custom field definitions. Handles conversion between Python types and SQL types with support for filtering and ordering operations.
3

4
## Capabilities
5

6
### Base Field Class
7

8
Foundation for all field types providing parsing, formatting, and SQL integration capabilities.
9

10
```python { .api }
11
class Field:
12
    """Base field class for data type handling."""
13
    
14
    def parse(self, value):
15
        """
16
        Parse value from external format to Python type.
17
        
18
        Parameters:
19
        - value: Raw value from data source
20
        
21
        Returns:
22
        Parsed Python value
23
        """
24
    
25
    def format(self, value):
26
        """
27
        Format Python value for external representation.
28
        
29
        Parameters:
30
        - value: Python value to format
31
        
32
        Returns:
33
        Formatted value for output
34
        """
35
    
36
    def quote(self, value):
37
        """
38
        Quote value for SQL query generation.
39
        
40
        Parameters:
41
        - value: Value to quote for SQL
42
        
43
        Returns:
44
        str: SQL-quoted value
45
        """
46
    
47
    # Field properties
48
    type: str           # SQL type name
49
    db_api_type: str    # DB API 2.0 type constant
50
    filters: list       # Supported filter classes
51
```
52

53
### Basic Field Types
54

55
Standard field types for common data representations with automatic parsing and validation.
56

57
```python { .api }
58
class Boolean(Field):
59
    """Boolean field type."""
60
    type = "BOOLEAN"
61
    db_api_type = "NUMBER"
62

63
class StringBoolean(Field):
64
    """Boolean field stored as string (TRUE/FALSE)."""
65
    type = "BOOLEAN"
66
    db_api_type = "NUMBER"
67

68
class IntBoolean(Field):
69
    """Boolean field stored as integer (1/0)."""
70
    type = "BOOLEAN"
71
    db_api_type = "NUMBER"
72

73
class Integer(Field):
74
    """Integer field type."""
75
    type = "INTEGER"
76
    db_api_type = "NUMBER"
77

78
class StringInteger(Field):
79
    """Integer field stored as string (for large integers)."""
80
    type = "INTEGER"
81
    db_api_type = "NUMBER"
82

83
class Float(Field):
84
    """Floating point field type."""
85
    type = "REAL"
86
    db_api_type = "NUMBER"
87

88
class String(Field):
89
    """String field type."""
90
    type = "TEXT"
91
    db_api_type = "STRING"
92

93
class Decimal(Field):
94
    """Decimal field type for precise numeric values."""
95
    type = "DECIMAL"
96
    db_api_type = "NUMBER"
97

98
class StringDecimal(Field):
99
    """Decimal field stored as string."""
100
    type = "DECIMAL"
101
    db_api_type = "NUMBER"
102
```
103

104
### Date and Time Fields
105

106
Specialized field types for temporal data with timezone and format support.
107

108
```python { .api }
109
class DateTime(Field):
110
    """DateTime field using datetime.datetime objects."""
111
    type = "TIMESTAMP"
112
    db_api_type = "DATETIME"
113

114
class ISODateTime(Field):
115
    """DateTime field stored as ISO string."""
116
    type = "TIMESTAMP"
117
    db_api_type = "DATETIME"
118

119
class Date(Field):
120
    """Date field using datetime.date objects."""
121
    type = "DATE"
122
    db_api_type = "DATETIME"
123

124
class ISODate(Field):
125
    """Date field stored as ISO string."""
126
    type = "DATE"
127
    db_api_type = "DATETIME"
128

129
class Time(Field):
130
    """Time field using datetime.time objects."""
131
    type = "TIME"
132
    db_api_type = "DATETIME"
133

134
class ISOTime(Field):
135
    """Time field stored as ISO string."""
136
    type = "TIME"
137
    db_api_type = "DATETIME"
138

139
class Duration(Field):
140
    """Duration field using datetime.timedelta objects."""
141
    type = "DURATION"
142
    db_api_type = "DATETIME"
143

144
class StringDuration(Field):
145
    """Duration field stored as formatted string."""
146
    type = "DURATION"
147
    db_api_type = "DATETIME"
148
```
149

150
### Binary and Special Fields
151

152
Field types for binary data and special use cases.
153

154
```python { .api }
155
class Blob(Field):
156
    """Binary field using bytes objects."""
157
    type = "BLOB"
158
    db_api_type = "BINARY"
159

160
class StringBlob(Field):
161
    """Binary field stored as hexadecimal string."""
162
    type = "BLOB"
163
    db_api_type = "BINARY"
164

165
class RowID(Integer):
166
    """Special field for row identifiers."""
167
    type = "INTEGER"
168
    db_api_type = "NUMBER"
169

170
class Unknown(Field):
171
    """Field for unknown or dynamic types."""
172
    type = "TEXT"
173
    db_api_type = "STRING"
174
```
175

176
### Column Ordering
177

178
Enumeration for specifying column ordering capabilities and sort directions.
179

180
```python { .api }
181
class Order:
182
    """Enumeration for column ordering."""
183
    
184
    NONE = "none"           # No ordering
185
    ASCENDING = "asc"       # Ascending order
186
    DESCENDING = "desc"     # Descending order
187
```
188

189
### Type Analysis
190

191
Utilities for automatic type inference and data analysis from streams of data.
192

193
```python { .api }
194
def analyze(data):
195
    """
196
    Analyze data stream to infer types, ordering, and row count.
197
    
198
    Parameters:
199
    - data (Iterator[dict]): Stream of row dictionaries
200
    
201
    Returns:
202
    tuple: (num_rows, order_info, type_info)
203
        - num_rows (int): Total number of rows
204
        - order_info (dict): Column ordering information  
205
        - type_info (dict): Column type mappings
206
    """
207

208
def update_order(current_order, previous, current, num_rows):
209
    """
210
    Update ordering information for a column.
211
    
212
    Parameters:
213
    - current_order (Order): Current ordering state
214
    - previous: Previous value in sequence
215
    - current: Current value in sequence  
216
    - num_rows (int): Number of rows processed
217
    
218
    Returns:
219
    Order: Updated ordering state 
220
    """
221
```
222

223
### Field Registry
224

225
System for registering and accessing field types dynamically.
226

227
```python { .api }
228
# Available field types
229
FIELD_TYPES = {
230
    'boolean': Boolean,
231
    'integer': Integer, 
232
    'float': Float,
233
    'string': String,
234
    'datetime': DateTime,
235
    'date': Date,
236
    'time': Time,
237
    'duration': Duration
238
}
239
```
240

241
## Usage Examples
242

243
### Custom Field Definition
244

245
```python
246
from shillelagh.fields import Field, String
247

248
class EmailField(Field):
249
    """Custom field for email addresses."""
250
    
251
    def parse(self, value):
252
        """Parse and validate email format."""
253
        if value and '@' not in str(value):
254
            raise ValueError(f"Invalid email format: {value}")
255
        return str(value) if value else None
256
    
257
    def quote(self, value):
258
        """Quote email for SQL."""
259
        return f"'{value}'" if value else 'NULL'
260

261
# Use in adapter
262
columns = {
263
    'user_email': EmailField(),
264
    'user_name': String(),
265
    'created_at': DateTime()
266
}
267
```
268

269
### Automatic Type Inference
270

271
```python
272
from shillelagh.lib import analyze
273

274
# Sample data stream
275
data = [
276
    {'id': 1, 'name': 'Alice', 'score': 95.5, 'active': True},
277
    {'id': 2, 'name': 'Bob', 'score': 87.2, 'active': False},
278
    {'id': 3, 'name': 'Charlie', 'score': 92.0, 'active': True}
279
]
280

281
# Analyze data for types and ordering
282
num_rows, order_info, type_info = analyze(iter(data))
283

284
print(f"Rows: {num_rows}")
285
print(f"Types: {type_info}")
286
print(f"Order: {order_info}")
287

288
# Output:
289
# Rows: 3
290
# Types: {'id': Integer, 'name': String, 'score': Float, 'active': Boolean}
291
# Order: {'id': Order.ASCENDING, 'name': Order.NONE, 'score': Order.NONE, 'active': Order.NONE}
292
```
293

294
### Field Configuration in Adapters
295

296
```python
297
from shillelagh.adapters.base import Adapter
298
from shillelagh.fields import Integer, String, DateTime, Boolean
299

300
class CustomAdapter(Adapter):
301
    """Custom adapter with explicit field definitions."""
302
    
303
    def get_columns(self):
304
        """Define column types explicitly."""
305
        return {
306
            'id': Integer(),
307
            'title': String(), 
308
            'created_at': DateTime(timezone='UTC'),
309
            'is_published': Boolean(),
310
            'view_count': Integer()
311
        }
312
    
313
    def get_data(self, bounds=None, order=None, **kwargs):
314
        """Return typed data matching field definitions."""
315
        for row in self._fetch_raw_data():
316
            yield {
317
                'id': int(row['id']),
318
                'title': str(row['title']),
319
                'created_at': self._parse_datetime(row['created_at']),
320
                'is_published': bool(row['is_published']),
321
                'view_count': int(row['view_count'])
322
            }
323
```
324

325
### Working with Temporal Data
326

327
```python
328
from shillelagh.fields import DateTime, Date, Time
329
from shillelagh.backends.apsw.db import connect
330
from datetime import datetime, timezone
331

332
# Configure datetime fields with timezone
333
datetime_field = DateTime(timezone='America/New_York')
334
date_field = Date()
335
time_field = Time()
336

337
# Parse various datetime formats
338
dt1 = datetime_field.parse('2023-12-25 15:30:00')
339
dt2 = datetime_field.parse('2023-12-25T15:30:00Z')
340
date1 = date_field.parse('2023-12-25')
341
time1 = time_field.parse('15:30:00')
342

343
# Use in SQL queries with proper quoting
344
connection = connect(":memory:")
345
cursor = connection.cursor()
346

347
cursor.execute(f"""
348
    SELECT * FROM events 
349
    WHERE event_date >= {date_field.quote('2023-01-01')}
350
    AND event_time BETWEEN {time_field.quote('09:00:00')} 
351
                       AND {time_field.quote('17:00:00')}
352
""")
353
```
354

355
### Data Validation and Conversion
356

357
```python
358
from shillelagh.fields import Integer, Float, Boolean, String
359

360
# Create field instances
361
int_field = Integer()
362
float_field = Float()
363
bool_field = Boolean()
364
string_field = String()
365

366
# Parse various input formats
367
test_data = [
368
    ('123', int_field),      # String to int
369
    ('45.67', float_field),  # String to float  
370
    ('true', bool_field),    # String to bool
371
    (123, string_field),     # Int to string
372
]
373

374
for value, field in test_data:
375
    parsed = field.parse(value)
376
    formatted = field.format(parsed)
377
    quoted = field.quote(parsed)
378
    
379
    print(f"Input: {value} -> Parsed: {parsed} -> Formatted: {formatted} -> Quoted: {quoted}")
380

381
# Output:
382
# Input: 123 -> Parsed: 123 -> Formatted: 123 -> Quoted: 123
383
# Input: 45.67 -> Parsed: 45.67 -> Formatted: 45.67 -> Quoted: 45.67
384
# Input: true -> Parsed: True -> Formatted: True -> Quoted: 1
385
# Input: 123 -> Parsed: '123' -> Formatted: '123' -> Quoted: '123'
386
```