0
# SODA (Simple Oracle Document Access)
1

2
Document-oriented database operations for JSON documents with collection management, document operations, and query capabilities providing a NoSQL-style interface to Oracle Database.
3

4
## Capabilities
5

6
### SODA Database Access
7

8
Access SODA functionality through database connections for document operations.
9

10
```python { .api }
11
class Connection:
12
    def getSodaDatabase(self) -> SodaDatabase:
13
        """
14
        Get SODA database object for document operations.
15
        
16
        Returns:
17
        SodaDatabase object for collection and document management
18
        """
19
```
20

21
```python { .api }
22
class SodaDatabase:
23
    def createCollection(self, name: str, metadata=None) -> SodaCollection:
24
        """
25
        Create new document collection.
26
        
27
        Parameters:
28
        - name (str): Collection name
29
        - metadata (dict): Collection metadata specification
30
        
31
        Returns:
32
        SodaCollection object
33
        """
34
        
35
    def openCollection(self, name: str) -> SodaCollection:
36
        """
37
        Open existing document collection.
38
        
39
        Parameters:
40
        - name (str): Collection name
41
        
42
        Returns:
43
        SodaCollection object or None if not found
44
        """
45
        
46
    def getCollectionNames(self, startName=None, limit=None) -> list:
47
        """
48
        Get list of collection names.
49
        
50
        Parameters:
51
        - startName (str): Starting name for filtering
52
        - limit (int): Maximum number of names to return
53
        
54
        Returns:
55
        List of collection names
56
        """
57
        
58
    def createDocument(self, content, key=None, mediaType=None) -> SodaDoc:
59
        """
60
        Create document from content.
61
        
62
        Parameters:
63
        - content: Document content (dict, str, or bytes)
64
        - key (str): Document key (auto-generated if None)
65
        - mediaType (str): Media type (default: application/json)
66
        
67
        Returns:
68
        SodaDoc object
69
        """
70
```
71

72
Usage examples:
73

74
```python
75
# Get SODA database
76
soda_db = connection.getSodaDatabase()
77

78
# Create collection with default settings
79
employees_coll = soda_db.createCollection("employees")
80

81
# Create collection with custom metadata
82
metadata = {
83
    "keyColumn": {"name": "ID"},
84
    "contentColumn": {"name": "JSON_DOCUMENT", "jsonFormat": "OSON"},
85
    "versionColumn": {"name": "VERSION"},
86
    "lastModifiedColumn": {"name": "LAST_MODIFIED"},
87
    "creationTimeColumn": {"name": "CREATED_ON"}
88
}
89
products_coll = soda_db.createCollection("products", metadata)
90

91
# Open existing collection
92
orders_coll = soda_db.openCollection("orders")
93
if orders_coll is None:
94
    print("Orders collection not found")
95

96
# List all collections
97
collection_names = soda_db.getCollectionNames()
98
print(f"Collections: {collection_names}")
99

100
# List collections starting with 'emp'
101
emp_collections = soda_db.getCollectionNames(startName="emp", limit=10)
102
```
103

104
### Document Creation and Management
105

106
Create and manage individual documents within collections.
107

108
```python { .api }
109
class SodaDoc:
110
    @property
111
    def key(self) -> str:
112
        """Document key (unique identifier)"""
113
        
114
    @property
115
    def content(self) -> dict:
116
        """Document content as dictionary"""
117
        
118
    @property
119
    def mediaType(self) -> str:
120
        """Document media type"""
121
        
122
    @property
123
    def version(self) -> str:
124
        """Document version"""
125
        
126
    @property
127
    def createdOn(self) -> str:
128
        """Document creation timestamp"""
129
        
130
    @property
131
    def lastModified(self) -> str:
132
        """Document last modification timestamp"""
133
        
134
    def getContent(self) -> dict:
135
        """Get document content as dictionary"""
136
        
137
    def getContentAsBytes(self) -> bytes:
138
        """Get document content as bytes"""
139
        
140
    def getContentAsString(self) -> str:
141
        """Get document content as JSON string"""
142
```
143

144
Usage examples:
145

146
```python
147
# Create documents from different content types
148
employee_dict = {
149
    "id": 1001,
150
    "name": "John Doe", 
151
    "department": "Engineering",
152
    "salary": 75000,
153
    "skills": ["Python", "Oracle", "SQL"]
154
}
155

156
# Create document from dictionary
157
emp_doc = soda_db.createDocument(employee_dict)
158
print(f"Created document with key: {emp_doc.key}")
159

160
# Create document with specific key
161
emp_doc_2 = soda_db.createDocument(
162
    {"id": 1002, "name": "Jane Smith", "department": "Sales"},
163
    key="emp_1002"
164
)
165

166
# Create document from JSON string
167
json_str = '{"id": 1003, "name": "Bob Johnson", "active": true}'
168
emp_doc_3 = soda_db.createDocument(json_str)
169

170
# Access document properties
171
print(f"Document key: {emp_doc.key}")
172
print(f"Content: {emp_doc.content}")
173
print(f"Media type: {emp_doc.mediaType}")
174
print(f"Version: {emp_doc.version}")
175
```
176

177
### Collection Operations
178

179
Manage documents within collections with insert, update, and delete operations.
180

181
```python { .api }
182
class SodaCollection:
183
    @property
184
    def name(self) -> str:
185
        """Collection name"""
186
        
187
    @property
188
    def metadata(self) -> dict:
189
        """Collection metadata"""
190
        
191
    def insertOne(self, doc) -> SodaDoc:
192
        """
193
        Insert single document into collection.
194
        
195
        Parameters:
196
        - doc: Document content (dict, SodaDoc, or JSON string)
197
        
198
        Returns:
199
        SodaDoc representing inserted document with generated metadata
200
        """
201
        
202
    def insertMany(self, docs: list) -> list:
203
        """
204
        Insert multiple documents into collection.
205
        
206
        Parameters:
207
        - docs (list): List of documents to insert
208
        
209
        Returns:
210
        List of SodaDoc objects representing inserted documents
211
        """
212
        
213
    def insertOneAndGet(self, doc) -> SodaDoc:
214
        """
215
        Insert document and return complete document with metadata.
216
        
217
        Parameters:
218
        - doc: Document content to insert
219
        
220
        Returns:
221
        Complete SodaDoc with all metadata
222
        """
223
        
224
    def save(self, doc) -> None:
225
        """
226
        Save document (insert or replace).
227
        
228
        Parameters:
229
        - doc: Document to save
230
        """
231
        
232
    def saveAndGet(self, doc) -> SodaDoc:
233
        """
234
        Save document and return complete document with metadata.
235
        
236
        Parameters:
237
        - doc: Document to save
238
        
239
        Returns:
240
        Complete SodaDoc with all metadata
241
        """
242
        
243
    def truncate(self) -> None:
244
        """Remove all documents from collection"""
245
        
246
    def drop(self) -> bool:
247
        """
248
        Drop collection and all documents.
249
        
250
        Returns:
251
        True if collection was dropped, False if not found
252
        """
253
```
254

255
Usage examples:
256

257
```python
258
# Insert single document
259
result = employees_coll.insertOne({
260
    "id": 2001,
261
    "name": "Alice Brown",
262
    "department": "Marketing",
263
    "hire_date": "2023-01-15"
264
})
265
print(f"Inserted document with key: {result.key}")
266

267
# Insert multiple documents
268
new_employees = [
269
    {"id": 2002, "name": "Charlie Davis", "department": "IT"},
270
    {"id": 2003, "name": "Diana Wilson", "department": "HR"},
271
    {"id": 2004, "name": "Eve Miller", "department": "Finance"}
272
]
273
results = employees_coll.insertMany(new_employees)
274
print(f"Inserted {len(results)} documents")
275

276
# Insert and get complete document
277
doc_with_metadata = employees_coll.insertOneAndGet({
278
    "id": 2005,
279
    "name": "Frank Garcia",
280
    "department": "Operations"
281
})
282
print(f"Document created on: {doc_with_metadata.createdOn}")
283

284
# Save or update document
285
employee_update = {
286
    "id": 2001,
287
    "name": "Alice Brown",
288
    "department": "Marketing", 
289
    "salary": 65000,  # Added salary
290
    "promotion_date": "2023-06-01"  # Added promotion
291
}
292
employees_coll.save(employee_update)
293
```
294

295
### Document Querying and Search
296

297
Find and retrieve documents using various query methods.
298

299
```python { .api }
300
class SodaCollection:
301
    def find(self) -> SodaOperation:
302
        """
303
        Create operation for finding documents.
304
        
305
        Returns:
306
        SodaOperation object for building queries
307
        """
308
```
309

310
```python { .api }
311
class SodaOperation:
312
    def key(self, key: str) -> SodaOperation:
313
        """Filter by document key"""
314
        
315
    def keys(self, keys: list) -> SodaOperation:
316
        """Filter by multiple document keys"""
317
        
318
    def filter(self, filterSpec: dict) -> SodaOperation:
319
        """Apply JSON filter specification"""
320
        
321
    def version(self, version: str) -> SodaOperation:
322
        """Filter by document version"""
323
        
324
    def limit(self, limit: int) -> SodaOperation:
325
        """Limit number of results"""
326
        
327
    def skip(self, skip: int) -> SodaOperation:
328
        """Skip number of documents"""
329
        
330
    def count(self) -> int:
331
        """Count matching documents"""
332
        
333
    def getCursor(self) -> SodaDocCursor:
334
        """Get cursor for iterating results"""
335
        
336
    def getDocuments(self) -> list:
337
        """Get all matching documents as list"""
338
        
339
    def getOne(self) -> SodaDoc:
340
        """Get first matching document"""
341
        
342
    def replaceOne(self, doc) -> SodaDoc:
343
        """Replace first matching document"""
344
        
345
    def replaceOneAndGet(self, doc) -> SodaDoc:
346
        """Replace first matching document and return result"""
347
        
348
    def remove(self) -> int:
349
        """
350
        Remove matching documents.
351
        
352
        Returns:
353
        Number of documents removed
354
        """
355
```
356

357
Usage examples:
358

359
```python
360
# Find document by key
361
doc = employees_coll.find().key("emp_1002").getOne()
362
if doc:
363
    print(f"Found employee: {doc.content['name']}")
364

365
# Find documents by multiple keys
366
keys = ["emp_1001", "emp_1002", "emp_1003"]
367
docs = employees_coll.find().keys(keys).getDocuments()
368
print(f"Found {len(docs)} employees")
369

370
# Query with JSON filter - find by department
371
dept_filter = {"department": "Engineering"}
372
eng_employees = employees_coll.find().filter(dept_filter).getDocuments()
373
for emp in eng_employees:
374
    print(f"Engineer: {emp.content['name']}")
375

376
# Complex filter - salary range query
377
salary_filter = {"salary": {"$gte": 50000, "$lte": 80000}}
378
mid_range = employees_coll.find().filter(salary_filter).getDocuments()
379

380
# Query with limit and skip (pagination)
381
page_size = 10
382
page_num = 2
383
page_docs = (employees_coll.find()
384
             .skip((page_num - 1) * page_size)
385
             .limit(page_size)
386
             .getDocuments())
387

388
# Count documents matching criteria
389
total_engineers = employees_coll.find().filter({"department": "Engineering"}).count()
390
print(f"Total engineers: {total_engineers}")
391

392
# Update documents using query
393
employees_coll.find().filter({"department": "IT"}).replaceOne({
394
    "department": "Information Technology",
395
    "updated": True
396
})
397

398
# Remove documents
399
removed_count = employees_coll.find().filter({"active": False}).remove()
400
print(f"Removed {removed_count} inactive employees")
401
```
402

403
### Document Cursors
404

405
Iterate through large result sets efficiently using cursors.
406

407
```python { .api }
408
class SodaDocCursor:
409
    def getNext(self) -> SodaDoc:
410
        """
411
        Get next document from cursor.
412
        
413
        Returns:
414
        Next SodaDoc or None if no more documents
415
        """
416
        
417
    def close(self) -> None:
418
        """Close cursor and free resources"""
419
```
420

421
Usage examples:
422

423
```python
424
# Iterate through all documents using cursor
425
cursor = employees_coll.find().getCursor()
426
try:
427
    while True:
428
        doc = cursor.getNext()
429
        if doc is None:
430
            break
431
        employee = doc.content
432
        print(f"Employee {employee['id']}: {employee['name']}")
433
finally:
434
    cursor.close()
435

436
# Context manager for automatic cursor cleanup
437
with employees_coll.find().getCursor() as cursor:
438
    doc = cursor.getNext()
439
    while doc:
440
        # Process document
441
        print(f"Processing: {doc.key}")
442
        doc = cursor.getNext()
443
```
444

445
### Collection Indexing
446

447
Create and manage indexes for better query performance.
448

449
```python { .api }
450
class SodaCollection:
451
    def createIndex(self, spec: dict) -> None:
452
        """
453
        Create index on collection.
454
        
455
        Parameters:
456
        - spec (dict): Index specification
457
        """
458
        
459
    def dropIndex(self, name: str, force=False) -> bool:
460
        """
461
        Drop index from collection.
462
        
463
        Parameters:
464
        - name (str): Index name
465
        - force (bool): Force drop even if index is being used
466
        
467
        Returns:
468
        True if index was dropped
469
        """
470
        
471
    def getDataGuide(self) -> SodaDoc:
472
        """
473
        Get collection data guide (schema summary).
474
        
475
        Returns:
476
        SodaDoc containing data guide information
477
        """
478
```
479

480
Usage examples:
481

482
```python
483
# Create simple index on department field
484
dept_index = {
485
    "name": "dept_idx",
486
    "fields": [{"path": "department", "datatype": "varchar2", "maxLength": 100}]
487
}
488
employees_coll.createIndex(dept_index)
489

490
# Create composite index
491
composite_index = {
492
    "name": "dept_salary_idx", 
493
    "fields": [
494
        {"path": "department", "datatype": "varchar2", "maxLength": 100},
495
        {"path": "salary", "datatype": "number"}
496
    ]
497
}
498
employees_coll.createIndex(composite_index)
499

500
# Create functional index
501
functional_index = {
502
    "name": "upper_name_idx",
503
    "fields": [{"path": "upper(name)", "datatype": "varchar2", "maxLength": 200}]
504
}
505
employees_coll.createIndex(functional_index)
506

507
# Drop index
508
success = employees_coll.dropIndex("dept_idx")
509
print(f"Index dropped: {success}")
510

511
# Get data guide to understand document structure
512
data_guide = employees_coll.getDataGuide()
513
if data_guide:
514
    guide_content = data_guide.content
515
    print("Collection schema summary:")
516
    print(json.dumps(guide_content, indent=2))
517
```
518

519
## Advanced SODA Operations
520

521
### Bulk Document Operations
522

523
Efficiently handle large numbers of documents:
524

525
```python
526
def bulk_insert_employees(collection, employee_data):
527
    """Insert large number of employees efficiently"""
528
    batch_size = 1000
529
    
530
    for i in range(0, len(employee_data), batch_size):
531
        batch = employee_data[i:i + batch_size]
532
        results = collection.insertMany(batch)
533
        print(f"Inserted batch: {len(results)} documents")
534

535
def bulk_update_salaries(collection, salary_increases):
536
    """Update multiple employee salaries"""
537
    for emp_id, new_salary in salary_increases.items():
538
        collection.find().filter({"id": emp_id}).replaceOne({
539
            "salary": new_salary,
540
            "last_updated": "2023-12-01"
541
        })
542

543
# Usage
544
large_employee_list = [{"id": i, "name": f"Employee {i}"} for i in range(1, 10001)]
545
bulk_insert_employees(employees_coll, large_employee_list)
546

547
salary_updates = {1001: 80000, 1002: 75000, 1003: 85000}
548
bulk_update_salaries(employees_coll, salary_updates)
549
```
550

551
### JSON Query Operators
552

553
Use MongoDB-style query operators in filters:
554

555
```python
556
# Comparison operators
557
employees_coll.find().filter({"salary": {"$gt": 70000}})  # Greater than
558
employees_coll.find().filter({"salary": {"$gte": 70000}}) # Greater than or equal
559
employees_coll.find().filter({"salary": {"$lt": 50000}})  # Less than
560
employees_coll.find().filter({"salary": {"$lte": 50000}}) # Less than or equal
561
employees_coll.find().filter({"salary": {"$ne": 60000}})  # Not equal
562

563
# Array operators
564
employees_coll.find().filter({"skills": {"$in": ["Python", "Java"]}})    # In array
565
employees_coll.find().filter({"skills": {"$nin": ["COBOL", "Fortran"]}}) # Not in array
566
employees_coll.find().filter({"skills": {"$all": ["Python", "SQL"]}})    # All elements
567

568
# Logical operators
569
employees_coll.find().filter({
570
    "$and": [
571
        {"department": "Engineering"},
572
        {"salary": {"$gt": 70000}}
573
    ]
574
})
575

576
employees_coll.find().filter({
577
    "$or": [
578
        {"department": "Sales"},
579
        {"department": "Marketing"}
580
    ]
581
})
582

583
# Existence and type checking
584
employees_coll.find().filter({"email": {"$exists": True}})   # Field exists
585
employees_coll.find().filter({"salary": {"$type": "number"}}) # Field type
586

587
# Regular expressions
588
employees_coll.find().filter({"name": {"$regex": "^John.*"}}) # Name starts with "John"
589
```
590

591
### Error Handling
592

593
Handle SODA-specific errors and exceptions:
594

595
```python
596
try:
597
    # SODA operations
598
    collection = soda_db.createCollection("test_collection")
599
    doc = collection.insertOne({"test": "data"})
600
    
601
except cx_Oracle.DatabaseError as e:
602
    error_obj, = e.args
603
    if error_obj.code == 40842:  # Collection already exists
604
        print("Collection already exists")
605
        collection = soda_db.openCollection("test_collection")
606
    elif error_obj.code == 40623:  # Invalid document
607
        print("Invalid document format")
608
    else:
609
        print(f"SODA error: {error_obj.message}")
610
        
611
except Exception as e:
612
    print(f"Unexpected error: {e}")
613
    
614
finally:
615
    # Cleanup resources
616
    if 'cursor' in locals():
617
        cursor.close()
618
```
619

620
## SODA Best Practices
621

622
1. **Use appropriate indexes**: Create indexes on frequently queried fields
623
2. **Batch operations**: Use insertMany() for bulk document insertion
624
3. **Close cursors**: Always close cursors to free resources
625
4. **Handle large results**: Use cursors for large result sets
626
5. **Optimize filters**: Use specific filters to reduce query scope
627
6. **Monitor collection size**: Consider partitioning for very large collections
628
7. **Use data guides**: Leverage getDataGuide() to understand document structure
629
8. **Version management**: Use document versions for optimistic locking