askrobots / dbbasic-tsv
Public
A database in TSV text files: data you can read, fast inserts, and an optional Rust speed-up.
advanced_features.py · 7.1 KB · 244 lines
| 1 | #!/usr/bin/env python3 |
| 2 | """ |
| 3 | Advanced features example for dbbasic-tsv |
| 4 | Shows indexing, transactions, caching, and performance features |
| 5 | """ |
| 6 | |
| 7 | import time |
| 8 | from pathlib import Path |
| 9 | from dbbasic import TSV, get_audit_log, get_query_log |
| 10 | |
| 11 | |
| 12 | def benchmark_performance(): |
| 13 | """Demonstrate performance capabilities""" |
| 14 | print("\n📊 Performance Benchmark") |
| 15 | print("-" * 40) |
| 16 | |
| 17 | db = TSV("benchmark", ["id", "data", "timestamp"]) |
| 18 | |
| 19 | # Batch insert performance |
| 20 | print("\nBatch insert (10,000 records):") |
| 21 | records = [ |
| 22 | {"id": str(i), "data": f"test_{i}", "timestamp": str(time.time())} |
| 23 | for i in range(10000) |
| 24 | ] |
| 25 | |
| 26 | start = time.perf_counter() |
| 27 | inserted = db.insert_many(records) |
| 28 | elapsed = time.perf_counter() - start |
| 29 | |
| 30 | print(f" Inserted: {inserted} records") |
| 31 | print(f" Time: {elapsed:.3f} seconds") |
| 32 | print(f" Rate: {inserted/elapsed:,.0f} records/sec") |
| 33 | |
| 34 | # Query performance with index |
| 35 | print("\nIndexed query performance:") |
| 36 | start = time.perf_counter() |
| 37 | result = db.query_one(id="5000") |
| 38 | elapsed = (time.perf_counter() - start) * 1000 |
| 39 | print(f" Found: {result['data']}") |
| 40 | print(f" Time: {elapsed:.2f}ms") |
| 41 | |
| 42 | # Full scan performance |
| 43 | print("\nFull scan performance:") |
| 44 | start = time.perf_counter() |
| 45 | results = db.query(data="test_9999") |
| 46 | elapsed = (time.perf_counter() - start) * 1000 |
| 47 | print(f" Found: {len(results)} record(s)") |
| 48 | print(f" Time: {elapsed:.2f}ms") |
| 49 | |
| 50 | db.drop() |
| 51 | |
| 52 | |
| 53 | def demonstrate_transactions(): |
| 54 | """Show atomic transactions""" |
| 55 | print("\n🔄 Transactions") |
| 56 | print("-" * 40) |
| 57 | |
| 58 | accounts = TSV("accounts", ["id", "name", "balance"]) |
| 59 | |
| 60 | # Setup accounts |
| 61 | accounts.insert({"id": "1", "name": "Alice", "balance": "1000"}) |
| 62 | accounts.insert({"id": "2", "name": "Bob", "balance": "1000"}) |
| 63 | |
| 64 | print("\nInitial balances:") |
| 65 | for acc in accounts.all(): |
| 66 | print(f" {acc['name']}: ${acc['balance']}") |
| 67 | |
| 68 | # Atomic transfer |
| 69 | print("\nTransferring $100 from Alice to Bob...") |
| 70 | with accounts.transaction() as tx: |
| 71 | # Debit Alice |
| 72 | alice = tx.query_one(id="1") |
| 73 | new_balance = str(int(alice["balance"]) - 100) |
| 74 | tx.update({"id": "1"}, {"balance": new_balance}) |
| 75 | |
| 76 | # Credit Bob |
| 77 | bob = tx.query_one(id="2") |
| 78 | new_balance = str(int(bob["balance"]) + 100) |
| 79 | tx.update({"id": "2"}, {"balance": new_balance}) |
| 80 | |
| 81 | print("\nFinal balances:") |
| 82 | for acc in accounts.all(): |
| 83 | print(f" {acc['name']}: ${acc['balance']}") |
| 84 | |
| 85 | accounts.drop() |
| 86 | |
| 87 | |
| 88 | def show_audit_logging(): |
| 89 | """Demonstrate audit logging""" |
| 90 | print("\n📝 Audit Logging") |
| 91 | print("-" * 40) |
| 92 | |
| 93 | # Enable audit logging |
| 94 | products = TSV("products", ["id", "name", "price"], audit=True) |
| 95 | |
| 96 | # Perform operations |
| 97 | products.insert({"id": "1", "name": "Widget", "price": "9.99"}) |
| 98 | products.update({"id": "1"}, {"price": "12.99"}) |
| 99 | products.query(name="Widget") |
| 100 | products.delete(id="1") |
| 101 | |
| 102 | # Get audit log |
| 103 | audit = get_audit_log() |
| 104 | print("\nRecent operations:") |
| 105 | for entry in audit.tail(4): |
| 106 | print(f" {entry['operation']:8} {entry['table']:10} Success: {entry['success']}") |
| 107 | |
| 108 | # Get statistics |
| 109 | stats = audit.get_stats() |
| 110 | print(f"\nTotal operations: {stats['total_operations']}") |
| 111 | print(f"Error rate: {stats['error_rate']:.1%}") |
| 112 | |
| 113 | products.drop() |
| 114 | |
| 115 | |
| 116 | def demonstrate_indexing(): |
| 117 | """Show custom indexing""" |
| 118 | print("\n🔍 Custom Indexing") |
| 119 | print("-" * 40) |
| 120 | |
| 121 | users = TSV("users", ["id", "email", "username", "created"]) |
| 122 | |
| 123 | # Insert test data |
| 124 | test_users = [ |
| 125 | {"id": "1", "email": "alice@example.com", "username": "alice", "created": "2024-01-01"}, |
| 126 | {"id": "2", "email": "bob@example.com", "username": "bob", "created": "2024-01-02"}, |
| 127 | {"id": "3", "email": "charlie@example.com", "username": "charlie", "created": "2024-01-03"}, |
| 128 | ] |
| 129 | users.insert_many(test_users) |
| 130 | |
| 131 | print("\nBefore creating email index:") |
| 132 | start = time.perf_counter() |
| 133 | result = users.query_one(email="bob@example.com") |
| 134 | elapsed = (time.perf_counter() - start) * 1000 |
| 135 | print(f" Query by email: {elapsed:.2f}ms (full scan)") |
| 136 | |
| 137 | # Create index on email |
| 138 | print("\nCreating index on email column...") |
| 139 | users.create_index("email") |
| 140 | |
| 141 | print("\nAfter creating email index:") |
| 142 | start = time.perf_counter() |
| 143 | result = users.query_one(email="bob@example.com") |
| 144 | elapsed = (time.perf_counter() - start) * 1000 |
| 145 | print(f" Query by email: {elapsed:.2f}ms (indexed)") |
| 146 | |
| 147 | users.drop() |
| 148 | |
| 149 | |
| 150 | def show_query_performance(): |
| 151 | """Track query performance""" |
| 152 | print("\n⚡ Query Performance Tracking") |
| 153 | print("-" * 40) |
| 154 | |
| 155 | orders = TSV("orders", ["id", "customer", "total", "status"]) |
| 156 | |
| 157 | # Insert test data |
| 158 | for i in range(100): |
| 159 | orders.insert({ |
| 160 | "id": str(i), |
| 161 | "customer": f"customer_{i % 10}", |
| 162 | "total": str(100 + i), |
| 163 | "status": "pending" if i % 2 == 0 else "completed" |
| 164 | }) |
| 165 | |
| 166 | # Perform various queries |
| 167 | print("\nRunning queries...") |
| 168 | orders.query_one(id="50") # Indexed query |
| 169 | orders.query(status="completed") # Full scan |
| 170 | orders.query(customer="customer_5") # Full scan |
| 171 | orders.query_one(id="75") # Cache hit |
| 172 | |
| 173 | # Get query statistics |
| 174 | qlog = get_query_log() |
| 175 | stats = qlog.get_stats() |
| 176 | |
| 177 | print(f"\nQuery Statistics:") |
| 178 | print(f" Total queries: {stats['total_queries']}") |
| 179 | print(f" Avg time: {stats['avg_time_ms']:.2f}ms") |
| 180 | print(f" Cache hit rate: {stats['cache_hit_rate']:.1%}") |
| 181 | print(f" Index hit rate: {stats['index_hit_rate']:.1%}") |
| 182 | print(f" Full scan rate: {stats['full_scan_rate']:.1%}") |
| 183 | |
| 184 | orders.drop() |
| 185 | |
| 186 | |
| 187 | def demonstrate_backup_restore(): |
| 188 | """Show backup and restore""" |
| 189 | print("\n💾 Backup and Restore") |
| 190 | print("-" * 40) |
| 191 | |
| 192 | # Create and populate table |
| 193 | config = TSV("config", ["key", "value", "description"]) |
| 194 | config.insert({"key": "app.name", "value": "MyApp", "description": "Application name"}) |
| 195 | config.insert({"key": "app.version", "value": "1.0.0", "description": "Version number"}) |
| 196 | config.insert({"key": "db.timeout", "value": "30", "description": "Database timeout"}) |
| 197 | |
| 198 | print(f"\nOriginal records: {config.count()}") |
| 199 | |
| 200 | # Backup |
| 201 | backup_path = Path("./backup_test") |
| 202 | print(f"\nBacking up to {backup_path}...") |
| 203 | config.backup(backup_path) |
| 204 | |
| 205 | # Modify data |
| 206 | config.delete(key="db.timeout") |
| 207 | config.update({"key": "app.version"}, {"value": "2.0.0"}) |
| 208 | print(f"\nAfter modifications: {config.count()} records") |
| 209 | |
| 210 | # Restore |
| 211 | print(f"\nRestoring from backup...") |
| 212 | config.restore(backup_path) |
| 213 | print(f"After restore: {config.count()} records") |
| 214 | |
| 215 | # Verify data |
| 216 | version = config.query_one(key="app.version") |
| 217 | print(f"App version: {version['value']}") |
| 218 | |
| 219 | # Cleanup |
| 220 | config.drop() |
| 221 | import shutil |
| 222 | shutil.rmtree(backup_path, ignore_errors=True) |
| 223 | |
| 224 | |
| 225 | def main(): |
| 226 | print("=" * 50) |
| 227 | print("dbbasic-tsv Advanced Features Demo") |
| 228 | print("=" * 50) |
| 229 | |
| 230 | # Run all demonstrations |
| 231 | benchmark_performance() |
| 232 | demonstrate_transactions() |
| 233 | show_audit_logging() |
| 234 | demonstrate_indexing() |
| 235 | show_query_performance() |
| 236 | demonstrate_backup_restore() |
| 237 | |
| 238 | print("\n" + "=" * 50) |
| 239 | print("✅ All demonstrations completed!") |
| 240 | print("=" * 50) |
| 241 | |
| 242 | |
| 243 | if __name__ == "__main__": |
| 244 | main() |