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

0 downloads

dbbasic-tsv/examples/advanced_features.py
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
advanced_features.py · 7.1 KB · 244 lines
1#!/usr/bin/env python3
2"""
3Advanced features example for dbbasic-tsv
4Shows indexing, transactions, caching, and performance features
5"""
6
7import time
8from pathlib import Path
9from dbbasic import TSV, get_audit_log, get_query_log
10
11
12def 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
53def 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
88def 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
116def 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
150def 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
187def 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
225def 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
243if __name__ == "__main__":
244 main()