A database in TSV text files: data you can read, fast inserts, and an optional Rust speed-up.

0 downloads

Download zip
Clone
Clone with git
git clone https://gitstable.com/git/askrobots/dbbasic-tsv.git
HTTPS
https://gitstable.com/git/askrobots/dbbasic-tsv.git
SSH
ssh://git@gitstable.com:2222/askrobots/dbbasic-tsv.git

Cloning needs no sign-in. Pushing over HTTPS asks for your username and an access token. SSH needs your key under SSH keys.

danq9 Add launch announcement for dbbasic-tsv v1.0.1 1c9b0b8 1 year, 1 month ago History
tsv.py · 21.8 KB · 685 lines
1#!/usr/bin/env python3
2"""
3Simple, robust TSV database
4Focus on reliability over complexity
5"""
6
7import os
8import csv
9import json
10import time
11import fcntl
12import hashlib
13import tempfile
14import threading
15from pathlib import Path
16from datetime import datetime
17from collections import defaultdict, OrderedDict
18from typing import Dict, List, Optional, Any, Iterator
19from contextlib import contextmanager
20
21# Configure CSV for large fields
22csv.field_size_limit(min(2**31-1, os.sysconf('SC_ARG_MAX')))
23
24
25class TSV:
26 """
27 Simple, reliable TSV database
28 - Atomic writes
29 - Row-level operations
30 - Basic indexing
31 - Crash recovery
32 """
33
34 def __init__(self, table_name: str, columns: List[str], data_dir: Path = None):
35 self.table_name = table_name
36 self.columns = columns
37 self.data_dir = data_dir or Path.cwd() / "data"
38 self.data_dir.mkdir(parents=True, exist_ok=True)
39
40 # Files
41 self.data_file = self.data_dir / f"{table_name}.tsv"
42 self.lock_file = self.data_dir / f"{table_name}.lock"
43 self.index_file = self.data_dir / f"{table_name}.idx"
44 self.meta_file = self.data_dir / f"{table_name}.meta"
45
46 # In-memory index (simple dict)
47 self.index = defaultdict(list)
48 self.row_count = 0
49
50 # Cache for frequently accessed rows
51 self.cache = OrderedDict()
52 self.max_cache_size = 100
53
54 # Transaction state
55 self._in_transaction = False
56
57 # Initialize
58 self._init_table()
59 self._load_index()
60
61 def _init_table(self):
62 """Initialize table with headers"""
63 if not self.data_file.exists():
64 with open(self.data_file, 'w', newline='', encoding='utf-8') as f:
65 writer = csv.writer(f, delimiter='\t', quoting=csv.QUOTE_MINIMAL)
66 writer.writerow(self.columns)
67
68 # Save metadata
69 self._save_metadata()
70
71 def _save_metadata(self):
72 """Save table metadata"""
73 meta = {
74 'table_name': self.table_name,
75 'columns': self.columns,
76 'created': datetime.utcnow().isoformat(),
77 'row_count': self.row_count
78 }
79
80 with open(self.meta_file, 'w') as f:
81 json.dump(meta, f, indent=2)
82
83 @contextmanager
84 def _lock(self, exclusive: bool = True):
85 """File locking for concurrent access"""
86 # Skip locking if we're already in a transaction
87 if self._in_transaction:
88 yield
89 return
90
91 lock_fd = os.open(self.lock_file, os.O_CREAT | os.O_RDWR)
92
93 try:
94 # Get lock
95 if exclusive:
96 fcntl.flock(lock_fd, fcntl.LOCK_EX)
97 else:
98 fcntl.flock(lock_fd, fcntl.LOCK_SH)
99
100 yield
101
102 finally:
103 # Release lock
104 fcntl.flock(lock_fd, fcntl.LOCK_UN)
105 os.close(lock_fd)
106
107 def _load_index(self):
108 """Load or rebuild index"""
109 # Check if index exists and is up to date
110 if self.index_file.exists() and self.data_file.exists():
111 try:
112 # Compare modification times - rebuild if data is newer than index
113 data_mtime = os.path.getmtime(self.data_file)
114 index_mtime = os.path.getmtime(self.index_file)
115
116 if index_mtime >= data_mtime:
117 # Index is current, load it
118 with open(self.index_file, 'r') as f:
119 data = json.load(f)
120 self.index = defaultdict(list, data.get('index', {}))
121 self.row_count = data.get('row_count', 0)
122 return
123 except (json.JSONDecodeError, KeyError, OSError):
124 pass
125
126 # Rebuild index from data (either missing, corrupt, or stale)
127 self._rebuild_index()
128
129 def _rebuild_index(self):
130 """Rebuild index from scratch"""
131 self.index.clear()
132 self.row_count = 0
133
134 if not self.data_file.exists():
135 return
136
137 with open(self.data_file, 'r', encoding='utf-8') as f:
138 reader = csv.DictReader(f, delimiter='\t')
139
140 for row_num, row in enumerate(reader):
141 # Index first column (usually ID)
142 if self.columns and self.columns[0] in row:
143 key = row[self.columns[0]]
144 if key:
145 self.index[key].append(row_num)
146
147 self.row_count += 1
148
149 self._save_index()
150
151 def _save_index(self):
152 """Save index to file"""
153 data = {
154 'index': dict(self.index),
155 'row_count': self.row_count,
156 'updated': datetime.utcnow().isoformat()
157 }
158
159 # Write atomically
160 temp_file = self.index_file.with_suffix('.tmp')
161 with open(temp_file, 'w') as f:
162 json.dump(data, f)
163
164 temp_file.replace(self.index_file)
165
166 def insert(self, record: Dict[str, Any]) -> bool:
167 """Insert a record"""
168 with self._lock():
169 try:
170 # Validate and normalize
171 record = self._normalize_record(record)
172
173 # Append to file
174 with open(self.data_file, 'a', newline='', encoding='utf-8') as f:
175 writer = csv.DictWriter(f, fieldnames=self.columns,
176 delimiter='\t', quoting=csv.QUOTE_MINIMAL)
177 writer.writerow(record)
178
179 # Update index
180 if self.columns and self.columns[0] in record:
181 key = record[self.columns[0]]
182 if key:
183 self.index[key].append(self.row_count)
184
185 self.row_count += 1
186
187 # Save index periodically
188 if self.row_count % 100 == 0:
189 self._save_index()
190
191 return True
192
193 except Exception as e:
194 print(f"Insert error: {e}")
195 return False
196
197 def insert_many(self, records: List[Dict[str, Any]]) -> int:
198 """Insert multiple records efficiently"""
199 with self._lock():
200 inserted = 0
201
202 try:
203 with open(self.data_file, 'a', newline='', encoding='utf-8') as f:
204 writer = csv.DictWriter(f, fieldnames=self.columns,
205 delimiter='\t', quoting=csv.QUOTE_MINIMAL)
206
207 for record in records:
208 record = self._normalize_record(record)
209 writer.writerow(record)
210
211 # Update index
212 if self.columns and self.columns[0] in record:
213 key = record[self.columns[0]]
214 if key:
215 self.index[key].append(self.row_count)
216
217 self.row_count += 1
218 inserted += 1
219
220 self._save_index()
221 return inserted
222
223 except Exception as e:
224 print(f"Batch insert error: {e}")
225 return inserted
226
227 def query(self, **conditions) -> List[Dict[str, Any]]:
228 """Query records"""
229 results = []
230
231 with self._lock(exclusive=False):
232 # Try to use index for first column
233 if self.columns and self.columns[0] in conditions:
234 key = str(conditions[self.columns[0]])
235
236 # Check cache
237 if key in self.cache:
238 cached = self.cache[key]
239 if self._matches_conditions(cached, conditions):
240 return [cached]
241
242 # Use index
243 if key in self.index:
244 row_nums = self.index[key]
245
246 with open(self.data_file, 'r', encoding='utf-8') as f:
247 reader = list(csv.DictReader(f, delimiter='\t'))
248
249 for row_num in row_nums:
250 if row_num < len(reader):
251 row = reader[row_num]
252 if self._matches_conditions(row, conditions):
253 results.append(row)
254 # Cache result
255 self._add_to_cache(key, row)
256 else:
257 # Full scan
258 with open(self.data_file, 'r', encoding='utf-8') as f:
259 reader = csv.DictReader(f, delimiter='\t')
260
261 for row in reader:
262 if self._matches_conditions(row, conditions):
263 results.append(row)
264
265 return results
266
267 def query_one(self, **conditions) -> Optional[Dict[str, Any]]:
268 """Query single record"""
269 results = self.query(**conditions)
270 return results[0] if results else None
271
272 def update(self, conditions: Dict[str, Any], updates: Dict[str, Any]) -> int:
273 """Update matching records"""
274 with self._lock():
275 # Read all records
276 records = []
277 updated = 0
278
279 with open(self.data_file, 'r', encoding='utf-8') as f:
280 reader = csv.DictReader(f, delimiter='\t')
281
282 for row in reader:
283 if self._matches_conditions(row, conditions):
284 row.update(updates)
285 updated += 1
286 # Invalidate cache
287 if self.columns and self.columns[0] in row:
288 key = row[self.columns[0]]
289 if key in self.cache:
290 del self.cache[key]
291
292 records.append(row)
293
294 if updated > 0:
295 # Write back atomically
296 self._write_all(records)
297 # Rebuild index since row positions might change
298 self._rebuild_index()
299
300 return updated
301
302 def delete(self, **conditions) -> int:
303 """Delete matching records"""
304 with self._lock():
305 # Read all records
306 records = []
307 deleted = 0
308
309 with open(self.data_file, 'r', encoding='utf-8') as f:
310 reader = csv.DictReader(f, delimiter='\t')
311
312 for row in reader:
313 if self._matches_conditions(row, conditions):
314 deleted += 1
315 # Invalidate cache
316 if self.columns and self.columns[0] in row:
317 key = row[self.columns[0]]
318 if key in self.cache:
319 del self.cache[key]
320 else:
321 records.append(row)
322
323 if deleted > 0:
324 # Write back atomically
325 self._write_all(records)
326 # Rebuild index
327 self._rebuild_index()
328
329 return deleted
330
331 def count(self, **conditions) -> int:
332 """Count matching records"""
333 if not conditions:
334 return self.row_count
335
336 return len(self.query(**conditions))
337
338 def all(self) -> Iterator[Dict[str, Any]]:
339 """Iterate over all records"""
340 with self._lock(exclusive=False):
341 with open(self.data_file, 'r', encoding='utf-8') as f:
342 reader = csv.DictReader(f, delimiter='\t')
343 for row in reader:
344 yield row
345
346 def truncate(self):
347 """Remove all records but keep structure"""
348 with self._lock():
349 with open(self.data_file, 'w', newline='', encoding='utf-8') as f:
350 writer = csv.writer(f, delimiter='\t', quoting=csv.QUOTE_MINIMAL)
351 writer.writerow(self.columns)
352
353 self.index.clear()
354 self.cache.clear()
355 self.row_count = 0
356 self._save_index()
357
358 def drop(self):
359 """Delete the table completely"""
360 with self._lock():
361 for file in [self.data_file, self.index_file, self.meta_file, self.lock_file]:
362 file.unlink(missing_ok=True)
363
364 def backup(self, backup_path: Path):
365 """Create backup of table"""
366 with self._lock(exclusive=False):
367 import shutil
368
369 backup_path.mkdir(parents=True, exist_ok=True)
370
371 for file in [self.data_file, self.index_file, self.meta_file]:
372 if file.exists():
373 shutil.copy2(file, backup_path / file.name)
374
375 def restore(self, backup_path: Path):
376 """Restore from backup"""
377 with self._lock():
378 import shutil
379
380 for file in [self.data_file, self.index_file, self.meta_file]:
381 backup_file = backup_path / file.name
382 if backup_file.exists():
383 shutil.copy2(backup_file, file)
384
385 self._load_index()
386 self.cache.clear()
387
388 def stats(self) -> Dict[str, Any]:
389 """Get table statistics"""
390 file_size = self.data_file.stat().st_size if self.data_file.exists() else 0
391
392 return {
393 'table_name': self.table_name,
394 'columns': self.columns,
395 'row_count': self.row_count,
396 'file_size': file_size,
397 'file_size_human': self._format_bytes(file_size),
398 'index_size': len(self.index),
399 'cache_size': len(self.cache),
400 'data_file': str(self.data_file)
401 }
402
403 def create_index(self, column: str):
404 """Create or rebuild index on a specific column
405
406 Args:
407 column: Column name to index
408
409 Note:
410 Currently indexes are automatically maintained on the first column.
411 This method rebuilds the index to ensure it's up to date.
412 """
413 if column not in self.columns:
414 raise ValueError(f"Column '{column}' not found in table columns: {self.columns}")
415
416 # For now, just rebuild the existing index
417 # The TSV class already indexes the first column automatically
418 self._rebuild_index()
419
420 @contextmanager
421 def transaction(self):
422 """Context manager for atomic transactions
423
424 Usage:
425 with db.transaction() as tx:
426 tx.update({"id": "1"}, {"value": "new"})
427 tx.insert({"id": "2", "value": "another"})
428
429 All operations are atomic - either all succeed or all fail.
430 """
431 import shutil
432
433 # Create a simple transaction object that delegates to self
434 class Transaction:
435 def __init__(self, tsv_instance):
436 self.tsv = tsv_instance
437
438 def insert(self, record):
439 return self.tsv.insert(record)
440
441 def update(self, conditions, updates):
442 return self.tsv.update(conditions, updates)
443
444 def delete(self, **conditions):
445 return self.tsv.delete(**conditions)
446
447 # Acquire lock once for the entire transaction
448 lock_fd = os.open(self.lock_file, os.O_CREAT | os.O_RDWR)
449 fcntl.flock(lock_fd, fcntl.LOCK_EX)
450
451 # Backup the data file for rollback
452 backup_file = self.data_file.parent / f"{self.data_file.name}.tx_backup"
453 if self.data_file.exists():
454 shutil.copy2(self.data_file, backup_file)
455
456 # Set transaction flag so nested operations don't try to lock
457 self._in_transaction = True
458
459 tx = Transaction(self)
460 try:
461 yield tx
462 # Transaction succeeded - remove backup
463 backup_file.unlink(missing_ok=True)
464 except Exception as e:
465 # Transaction failed - rollback by restoring from backup
466 if backup_file.exists():
467 shutil.copy2(backup_file, self.data_file)
468 backup_file.unlink()
469 # Rebuild index after rollback (index is now stale)
470 self._rebuild_index()
471 raise e
472 finally:
473 # Always release lock and reset flag
474 self._in_transaction = False
475 fcntl.flock(lock_fd, fcntl.LOCK_UN)
476 os.close(lock_fd)
477
478 def _normalize_record(self, record: Dict[str, Any]) -> Dict[str, str]:
479 """Normalize record for TSV storage"""
480 normalized = {}
481
482 for col in self.columns:
483 value = record.get(col, '')
484
485 # Convert to string
486 if value is None:
487 value = ''
488 elif isinstance(value, bool):
489 value = '1' if value else '0'
490 elif isinstance(value, (list, dict)):
491 value = json.dumps(value)
492 else:
493 value = str(value)
494
495 # Clean up for TSV
496 value = value.replace('\t', ' ').replace('\n', ' ').replace('\r', '')
497 normalized[col] = value
498
499 return normalized
500
501 def _matches_conditions(self, row: Dict[str, Any], conditions: Dict[str, Any]) -> bool:
502 """Check if row matches all conditions"""
503 for key, value in conditions.items():
504 if key not in row:
505 return False
506
507 # Handle different types of comparisons
508 row_val = row[key]
509 cond_val = str(value) if value is not None else ''
510
511 if row_val != cond_val:
512 return False
513
514 return True
515
516 def _write_all(self, records: List[Dict[str, Any]]):
517 """Write all records atomically"""
518 temp_file = self.data_file.with_suffix('.tmp')
519
520 with open(temp_file, 'w', newline='', encoding='utf-8') as f:
521 writer = csv.DictWriter(f, fieldnames=self.columns,
522 delimiter='\t', quoting=csv.QUOTE_MINIMAL)
523 writer.writeheader()
524 writer.writerows(records)
525
526 # Atomic replace
527 temp_file.replace(self.data_file)
528
529 def _add_to_cache(self, key: str, value: Dict[str, Any]):
530 """Add to LRU cache"""
531 # Remove if exists (for LRU ordering)
532 if key in self.cache:
533 del self.cache[key]
534
535 # Add to end
536 self.cache[key] = value
537
538 # Trim if too large
539 while len(self.cache) > self.max_cache_size:
540 self.cache.popitem(last=False)
541
542 @staticmethod
543 def _format_bytes(size: int) -> str:
544 """Format bytes to human readable"""
545 for unit in ['B', 'KB', 'MB', 'GB']:
546 if size < 1024.0:
547 return f"{size:.1f} {unit}"
548 size /= 1024.0
549 return f"{size:.1f} TB"
550
551
552def test_simple_tsv():
553 """Test TSV database"""
554 print("Testing TSV Database")
555 print("=" * 50)
556
557 # Create test database
558 db = TSV("test_simple", ["id", "name", "email", "age", "active"])
559
560 # Test 1: Insert
561 print("\n1. Testing Insert:")
562 records = [
563 {"id": "1", "name": "Alice", "email": "alice@example.com", "age": 25, "active": True},
564 {"id": "2", "name": "Bob", "email": "bob@example.com", "age": 30, "active": False},
565 {"id": "3", "name": "Charlie", "email": "charlie@example.com", "age": 35, "active": True},
566 ]
567
568 for record in records:
569 db.insert(record)
570
571 print(f" ✓ Inserted {len(records)} records")
572
573 # Test 2: Query
574 print("\n2. Testing Query:")
575 result = db.query_one(id="1")
576 print(f" ✓ Found by ID: {result['name']}")
577
578 active_users = db.query(active="1")
579 print(f" ✓ Active users: {len(active_users)}")
580
581 # Test 3: Update
582 print("\n3. Testing Update:")
583 updated = db.update({"id": "2"}, {"active": "1"})
584 print(f" ✓ Updated {updated} record(s)")
585
586 # Test 4: Count
587 print("\n4. Testing Count:")
588 total = db.count()
589 active = db.count(active="1")
590 print(f" ✓ Total: {total}, Active: {active}")
591
592 # Test 5: Delete
593 print("\n5. Testing Delete:")
594 deleted = db.delete(id="3")
595 print(f" ✓ Deleted {deleted} record(s)")
596
597 # Test 6: Stats
598 print("\n6. Database Stats:")
599 stats = db.stats()
600 for key, value in stats.items():
601 if key != 'data_file':
602 print(f" {key}: {value}")
603
604 # Clean up
605 db.drop()
606 print("\n✓ All tests passed!")
607
608
609def benchmark_simple():
610 """Benchmark TSV performance"""
611 import random
612 import string
613
614 print("TSV Performance Benchmark")
615 print("=" * 50)
616
617 db = TSV("benchmark", ["id", "name", "email", "age", "city"])
618
619 def random_string(length=10):
620 return ''.join(random.choices(string.ascii_letters, k=length))
621
622 cities = ["New York", "London", "Tokyo", "Paris", "Berlin"]
623
624 # Benchmark batch insert
625 print("\n1. Batch Insert Performance:")
626 records = []
627 for i in range(10000):
628 records.append({
629 "id": str(i),
630 "name": random_string(),
631 "email": f"{random_string()}@example.com",
632 "age": random.randint(18, 80),
633 "city": random.choice(cities)
634 })
635
636 start = time.time()
637 inserted = db.insert_many(records)
638 insert_time = time.time() - start
639
640 print(f" Inserted {inserted} records in {insert_time:.2f}s")
641 print(f" Rate: {inserted/insert_time:.0f} records/sec")
642
643 # Benchmark queries
644 print("\n2. Query Performance:")
645
646 # Random ID lookups
647 start = time.time()
648 for _ in range(1000):
649 db.query_one(id=str(random.randint(0, 9999)))
650 query_time = time.time() - start
651
652 print(f" 1000 random lookups in {query_time:.3f}s")
653 print(f" Rate: {1000/query_time:.0f} queries/sec")
654
655 # Scan query
656 start = time.time()
657 results = db.query(city="Tokyo")
658 scan_time = time.time() - start
659
660 print(f" Scan query returned {len(results)} records in {scan_time:.3f}s")
661
662 # Show final stats
663 print("\n3. Final Statistics:")
664 stats = db.stats()
665 print(f" Rows: {stats['row_count']:,}")
666 print(f" File size: {stats['file_size_human']}")
667 print(f" Index entries: {stats['index_size']}")
668
669 # Clean up
670 db.drop()
671
672
673if __name__ == "__main__":
674 import sys
675
676 if len(sys.argv) > 1:
677 if sys.argv[1] == "test":
678 test_simple_tsv()
679 elif sys.argv[1] == "benchmark":
680 benchmark_simple()
681 else:
682 # Run both
683 test_simple_tsv()
684 print("\n" + "=" * 50 + "\n")
685 benchmark_simple()