askrobots / dbbasic Public

Model-config web apps: describe the app in configuration instead of writing code. Built on Polars and DuckDB.

0 downloads

dbbasic/core/server.py
Download zip
Clone
Clone with git
git clone https://gitstable.com/git/askrobots/dbbasic.git
HTTPS
https://gitstable.com/git/askrobots/dbbasic.git
SSH
ssh://git@gitstable.com:2222/askrobots/dbbasic.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 DBBasic crate engine logo and update README 0d628db 1 year, 2 months ago History
server.py · 20.0 KB · 718 lines
1#!/usr/bin/env python3
2"""
3DBBasic Server - FastAPI wrapper around dbbasic_fast.py
4
5Provides REST API for spreadsheet operations using Polars + DuckDB.
6Each user gets their own isolated data directory.
7
8Requirements:
9 pip install fastapi uvicorn polars duckdb pyarrow python-multipart aiofiles
10
11Run:
12 uvicorn dbbasic_server:app --reload --port 8000
13"""
14
15from fastapi import FastAPI, HTTPException, File, UploadFile, Form
16from fastapi.middleware.cors import CORSMiddleware
17from fastapi.responses import FileResponse, StreamingResponse
18from fastapi.staticfiles import StaticFiles
19from pydantic import BaseModel
20from typing import Optional, List, Dict, Any, Union
21import polars as pl
22import duckdb
23import numpy as np
24import time
25import os
26import json
27import uuid
28from pathlib import Path
29import io
30import aiofiles
31from datetime import datetime
32
33# Initialize FastAPI
34app = FastAPI(
35 title="DBBasic API",
36 description="Spreadsheet operations at 285 million rows/second",
37 version="1.0.0"
38)
39
40# Enable CORS for browser access
41app.add_middleware(
42 CORSMiddleware,
43 allow_origins=["*"], # In production, specify your domain
44 allow_credentials=True,
45 allow_methods=["*"],
46 allow_headers=["*"],
47)
48
49# Mount static files
50app.mount("/static", StaticFiles(directory="static"), name="static")
51
52# Serve the main HTML file
53@app.get("/")
54async def serve_main():
55 """Serve the main DBBasic HTML interface"""
56 return FileResponse("dbbasic_v1.html")
57
58@app.get("/dbbasic_v1.html")
59async def serve_dbbasic_v1():
60 """Serve the DBBasic V1 HTML file"""
61 return FileResponse("dbbasic_v1.html")
62
63# User data directory
64DATA_DIR = Path("user_data")
65DATA_DIR.mkdir(exist_ok=True)
66
67# Session storage (in production, use Redis)
68sessions = {}
69
70# Request/Response Models
71class GenerateRequest(BaseModel):
72 rows: int
73 columns: Optional[List[str]] = None
74 session_id: Optional[str] = None
75
76class CalculateRequest(BaseModel):
77 operation: str # SUM, AVG, COUNT, GROUP BY, etc.
78 column: Optional[str] = None
79 group_by: Optional[str] = None
80 session_id: str
81
82class SQLRequest(BaseModel):
83 query: str
84 session_id: str
85
86class CommandRequest(BaseModel):
87 command: str
88 session_id: str
89
90class DataResponse(BaseModel):
91 data: List[List[Any]]
92 columns: List[Dict[str, Any]]
93 rows: int
94 execution_time: float
95 rows_per_second: Optional[float] = None
96
97class CalculationResponse(BaseModel):
98 result: Any
99 execution_time: float
100 rows_processed: int
101 rows_per_second: float
102
103class SessionInfo(BaseModel):
104 session_id: str
105 created_at: str
106 rows: int
107 columns: int
108 memory_mb: float
109
110# Helper Functions
111def get_user_dir(session_id: str) -> Path:
112 """Get or create user-specific data directory"""
113 user_dir = DATA_DIR / session_id
114 user_dir.mkdir(exist_ok=True)
115 return user_dir
116
117def get_session_data(session_id: str) -> pl.DataFrame:
118 """Load session data from disk or memory"""
119 if session_id in sessions:
120 return sessions[session_id]
121
122 user_dir = get_user_dir(session_id)
123 parquet_file = user_dir / "data.parquet"
124
125 if parquet_file.exists():
126 df = pl.read_parquet(parquet_file)
127 sessions[session_id] = df
128 return df
129
130 return pl.DataFrame()
131
132def save_session_data(session_id: str, df: pl.DataFrame):
133 """Save session data to disk and memory"""
134 sessions[session_id] = df
135 user_dir = get_user_dir(session_id)
136 parquet_file = user_dir / "data.parquet"
137 df.write_parquet(parquet_file)
138
139# API Endpoints
140@app.get("/")
141async def root():
142 return {
143 "name": "DBBasic API",
144 "version": "1.0.0",
145 "speed": "285 million rows/second",
146 "engine": "Polars + DuckDB"
147 }
148
149@app.post("/update/cell")
150async def update_cell(
151 session_id: str = Form(...),
152 row: int = Form(...),
153 column: str = Form(...),
154 value: str = Form(...)
155):
156 """Update a single cell value"""
157 df = get_session_data(session_id)
158 if df.is_empty():
159 raise HTTPException(status_code=404, detail="No data in session")
160
161 try:
162 # Update the value in the DataFrame
163 df = df.with_row_count()
164
165 # Convert value to appropriate type based on column
166 col_type = df[column].dtype
167 if col_type in [pl.Float64, pl.Int64]:
168 value = float(value) if '.' in str(value) else int(value)
169
170 # Update the specific cell
171 df = df.with_columns(
172 pl.when(pl.col("row_nr") == row)
173 .then(pl.lit(value))
174 .otherwise(pl.col(column))
175 .alias(column)
176 )
177
178 # Remove the row_nr column
179 df = df.drop("row_nr")
180
181 # Save updated data
182 save_session_data(session_id, df)
183
184 return {
185 "success": True,
186 "row": row,
187 "column": column,
188 "value": value
189 }
190 except Exception as e:
191 raise HTTPException(status_code=400, detail=str(e))
192
193@app.post("/update/batch")
194async def update_batch(
195 session_id: str = Form(...),
196 updates: str = Form(...) # JSON string of updates
197):
198 """Update multiple cells at once"""
199 import json
200
201 df = get_session_data(session_id)
202 if df.is_empty():
203 raise HTTPException(status_code=404, detail="No data in session")
204
205 try:
206 updates_list = json.loads(updates)
207
208 for update in updates_list:
209 row = update['row']
210 column = update['column']
211 value = update['value']
212
213 # Update each cell
214 df = df.with_row_count()
215
216 # Convert value to appropriate type
217 col_type = df[column].dtype
218 if col_type in [pl.Float64, pl.Int64]:
219 value = float(value) if '.' in str(value) else int(value)
220
221 df = df.with_columns(
222 pl.when(pl.col("row_nr") == row)
223 .then(pl.lit(value))
224 .otherwise(pl.col(column))
225 .alias(column)
226 )
227
228 df = df.drop("row_nr")
229
230 # Save updated data
231 save_session_data(session_id, df)
232
233 return {
234 "success": True,
235 "updates": len(updates_list)
236 }
237 except Exception as e:
238 raise HTTPException(status_code=400, detail=str(e))
239
240@app.post("/add/row")
241async def add_row(
242 session_id: str = Form(...),
243 data: Optional[str] = Form(None) # JSON string of row data
244):
245 """Add a new row to the data"""
246 import json
247
248 df = get_session_data(session_id)
249
250 try:
251 if data:
252 row_data = json.loads(data)
253 else:
254 # Create empty row with default values
255 row_data = {col: None for col in df.columns}
256
257 # Create new row as DataFrame
258 new_row = pl.DataFrame([row_data])
259
260 # Append to existing data
261 df = pl.concat([df, new_row])
262
263 # Save updated data
264 save_session_data(session_id, df)
265
266 return {
267 "success": True,
268 "rows": len(df)
269 }
270 except Exception as e:
271 raise HTTPException(status_code=400, detail=str(e))
272
273@app.delete("/delete/row")
274async def delete_row(
275 session_id: str = Form(...),
276 row: int = Form(...)
277):
278 """Delete a row from the data"""
279 df = get_session_data(session_id)
280 if df.is_empty():
281 raise HTTPException(status_code=404, detail="No data in session")
282
283 try:
284 # Add row numbers and filter out the specified row
285 df = df.with_row_count()
286 df = df.filter(pl.col("row_nr") != row)
287 df = df.drop("row_nr")
288
289 # Save updated data
290 save_session_data(session_id, df)
291
292 return {
293 "success": True,
294 "rows": len(df)
295 }
296 except Exception as e:
297 raise HTTPException(status_code=400, detail=str(e))
298
299@app.post("/session/create")
300async def create_session():
301 """Create a new user session"""
302 session_id = str(uuid.uuid4())
303 sessions[session_id] = pl.DataFrame()
304
305 return SessionInfo(
306 session_id=session_id,
307 created_at=datetime.now().isoformat(),
308 rows=0,
309 columns=0,
310 memory_mb=0
311 )
312
313@app.get("/session/{session_id}/info")
314async def get_session_info(session_id: str):
315 """Get session information"""
316 df = get_session_data(session_id)
317
318 return SessionInfo(
319 session_id=session_id,
320 created_at=datetime.now().isoformat(),
321 rows=len(df),
322 columns=len(df.columns),
323 memory_mb=df.estimated_size('mb') if len(df) > 0 else 0
324 )
325
326@app.get("/session/{session_id}/data")
327async def get_session_data_endpoint(session_id: str):
328 """Get the actual data from a session"""
329 df = get_session_data(session_id)
330
331 if df.is_empty():
332 return {
333 "data": [],
334 "columns": [],
335 "rows": 0
336 }
337
338 # Convert to list format for spreadsheet
339 data_list = df.to_numpy().tolist()
340 columns = [
341 {"title": col, "width": 120, "type": "numeric" if df[col].dtype in [pl.Float64, pl.Int64] else "text"}
342 for col in df.columns
343 ]
344
345 return {
346 "data": data_list,
347 "columns": columns,
348 "rows": len(df)
349 }
350
351@app.post("/generate", response_model=DataResponse)
352async def generate_data(request: GenerateRequest):
353 """Generate test data with specified rows"""
354 start_time = time.time()
355
356 # Get or create session
357 session_id = request.session_id or str(uuid.uuid4())
358 n = request.rows
359
360 # Generate data using Polars (FAST!)
361 data = {
362 'ID': range(1, n + 1),
363 'Value': np.random.randn(n) * 100,
364 'Category': np.random.choice(['A', 'B', 'C', 'D'], n),
365 'Price': np.random.uniform(10, 1000, n),
366 'Quantity': np.random.randint(1, 100, n),
367 }
368
369 df = pl.DataFrame(data)
370
371 # Add calculated column
372 df = df.with_columns(
373 (pl.col('Price') * pl.col('Quantity')).alias('Total')
374 )
375
376 # Save to session
377 save_session_data(session_id, df)
378
379 # Convert to response format
380 elapsed = time.time() - start_time
381
382 # Convert to list format for JSON response
383 data_list = df.to_numpy().tolist()
384 columns = [
385 {"title": col, "width": 120, "type": "numeric" if df[col].dtype in [pl.Float64, pl.Int64] else "text"}
386 for col in df.columns
387 ]
388
389 return DataResponse(
390 data=data_list[:100], # Return first 100 rows for display
391 columns=columns,
392 rows=len(df),
393 execution_time=elapsed,
394 rows_per_second=n / elapsed
395 )
396
397@app.post("/calculate", response_model=CalculationResponse)
398async def calculate(request: CalculateRequest):
399 """Perform calculations on data"""
400 start_time = time.time()
401
402 df = get_session_data(request.session_id)
403 if df.is_empty():
404 raise HTTPException(status_code=404, detail="No data in session")
405
406 result = None
407 operation = request.operation.upper()
408
409 if operation == "SUM":
410 if request.column and request.column in df.columns:
411 result = df[request.column].sum()
412 else:
413 result = df.select(pl.sum(pl.all())).to_numpy()[0].tolist()
414
415 elif operation == "AVG" or operation == "MEAN":
416 if request.column and request.column in df.columns:
417 result = df[request.column].mean()
418 else:
419 result = df.select(pl.mean(pl.all())).to_numpy()[0].tolist()
420
421 elif operation == "COUNT":
422 result = len(df)
423
424 elif operation == "MAX":
425 if request.column and request.column in df.columns:
426 result = df[request.column].max()
427
428 elif operation == "MIN":
429 if request.column and request.column in df.columns:
430 result = df[request.column].min()
431
432 elif operation == "GROUP" and request.group_by:
433 if request.column:
434 grouped = df.group_by(request.group_by).agg(pl.col(request.column).sum())
435 result = grouped.to_dict(as_series=False)
436 else:
437 result = {"error": "Column required for GROUP BY"}
438
439 elapsed = time.time() - start_time
440 rows = len(df)
441
442 return CalculationResponse(
443 result=result,
444 execution_time=elapsed,
445 rows_processed=rows,
446 rows_per_second=rows / elapsed if elapsed > 0 else 0
447 )
448
449@app.post("/sql")
450async def execute_sql(request: SQLRequest):
451 """Execute SQL query using DuckDB"""
452 start_time = time.time()
453
454 df = get_session_data(request.session_id)
455 if df.is_empty():
456 raise HTTPException(status_code=404, detail="No data in session")
457
458 # Create DuckDB connection
459 conn = duckdb.connect(':memory:')
460
461 # Register DataFrame as table
462 conn.register('sheet', df)
463
464 try:
465 # Execute query
466 result = conn.execute(request.query).pl()
467
468 elapsed = time.time() - start_time
469
470 # Convert result to response format
471 data_list = result.to_numpy().tolist() if not result.is_empty() else []
472 columns = [
473 {"title": col, "width": 120}
474 for col in result.columns
475 ]
476
477 return DataResponse(
478 data=data_list[:1000], # Limit response size
479 columns=columns,
480 rows=len(result),
481 execution_time=elapsed,
482 rows_per_second=len(df) / elapsed if elapsed > 0 else 0
483 )
484
485 except Exception as e:
486 raise HTTPException(status_code=400, detail=f"SQL Error: {str(e)}")
487 finally:
488 conn.close()
489
490@app.post("/command")
491async def execute_command(request: CommandRequest):
492 """Execute DBBasic command"""
493 cmd = request.command.upper().strip()
494 session_id = request.session_id
495
496 if cmd.startswith("GENERATE"):
497 # Parse: GENERATE 1000 rows
498 import re
499 match = re.match(r'GENERATE\s+(\d+)\s+ROWS?', cmd)
500 if match:
501 rows = int(match.group(1))
502 gen_request = GenerateRequest(rows=rows, session_id=session_id)
503 return await generate_data(gen_request)
504
505 elif cmd.startswith("CALC"):
506 # Parse: CALC SUM(column)
507 import re
508 match = re.match(r'CALC\s+(\w+)\(([\w\s]+)\)', cmd)
509 if match:
510 operation = match.group(1)
511 column = match.group(2).strip()
512 calc_request = CalculateRequest(
513 operation=operation,
514 column=column,
515 session_id=session_id
516 )
517 return await calculate(calc_request)
518
519 elif cmd.startswith("SQL"):
520 # Parse: SQL SELECT * FROM sheet
521 query = cmd[3:].strip()
522 sql_request = SQLRequest(query=query, session_id=session_id)
523 return await execute_sql(sql_request)
524
525 else:
526 raise HTTPException(status_code=400, detail=f"Unknown command: {cmd}")
527
528@app.post("/upload/csv")
529async def upload_csv(
530 file: UploadFile = File(...),
531 session_id: str = Form(...)
532):
533 """Upload CSV file"""
534 start_time = time.time()
535
536 # Read CSV into Polars
537 contents = await file.read()
538 df = pl.read_csv(io.BytesIO(contents))
539
540 # Save to session
541 save_session_data(session_id, df)
542
543 elapsed = time.time() - start_time
544
545 return {
546 "filename": file.filename,
547 "rows": len(df),
548 "columns": len(df.columns),
549 "execution_time": elapsed,
550 "rows_per_second": len(df) / elapsed
551 }
552
553@app.get("/download/csv")
554async def download_csv(session_id: str):
555 """Download data as CSV"""
556 df = get_session_data(session_id)
557 if df.is_empty():
558 raise HTTPException(status_code=404, detail="No data in session")
559
560 # Convert to CSV
561 csv_buffer = io.StringIO()
562 df.write_csv(csv_buffer)
563 csv_buffer.seek(0)
564
565 return StreamingResponse(
566 io.BytesIO(csv_buffer.getvalue().encode()),
567 media_type="text/csv",
568 headers={"Content-Disposition": f"attachment; filename=dbbasic_{session_id}.csv"}
569 )
570
571@app.get("/download/parquet")
572async def download_parquet(session_id: str):
573 """Download data as Parquet (much faster and smaller)"""
574 df = get_session_data(session_id)
575 if df.is_empty():
576 raise HTTPException(status_code=404, detail="No data in session")
577
578 user_dir = get_user_dir(session_id)
579 parquet_file = user_dir / "data.parquet"
580
581 if parquet_file.exists():
582 return FileResponse(
583 parquet_file,
584 media_type="application/octet-stream",
585 filename=f"dbbasic_{session_id}.parquet"
586 )
587 else:
588 raise HTTPException(status_code=404, detail="Parquet file not found")
589
590@app.post("/benchmark")
591async def run_benchmark(session_id: str = Form(...)):
592 """Run performance benchmark"""
593 results = []
594
595 # Generate test data
596 df = pl.DataFrame({
597 'ID': range(1, 1000001),
598 'Value': np.random.randn(1000000) * 100,
599 'Category': np.random.choice(['A', 'B', 'C', 'D'], 1000000),
600 'Price': np.random.uniform(10, 1000, 1000000),
601 'Quantity': np.random.randint(1, 100, 1000000),
602 })
603
604 df = df.with_columns(
605 (pl.col('Price') * pl.col('Quantity')).alias('Total')
606 )
607
608 save_session_data(session_id, df)
609 rows = len(df)
610
611 # Benchmark 1: SUM
612 start = time.time()
613 total = df['Total'].sum()
614 sum_time = time.time() - start
615 results.append({
616 "operation": "SUM",
617 "time": sum_time,
618 "rows_per_second": rows / sum_time,
619 "result": float(total)
620 })
621
622 # Benchmark 2: GROUP BY
623 start = time.time()
624 grouped = df.group_by('Category').agg(pl.col('Total').sum())
625 group_time = time.time() - start
626 results.append({
627 "operation": "GROUP BY",
628 "time": group_time,
629 "rows_per_second": rows / group_time
630 })
631
632 # Benchmark 3: FILTER
633 start = time.time()
634 filtered = df.filter(pl.col('Price') > 500)
635 filter_time = time.time() - start
636 results.append({
637 "operation": "FILTER",
638 "time": filter_time,
639 "rows_per_second": rows / filter_time,
640 "matches": len(filtered)
641 })
642
643 # Benchmark 4: SORT
644 start = time.time()
645 sorted_df = df.sort('Total', descending=True)
646 sort_time = time.time() - start
647 results.append({
648 "operation": "SORT",
649 "time": sort_time,
650 "rows_per_second": rows / sort_time
651 })
652
653 # Benchmark 5: Complex SQL
654 conn = duckdb.connect(':memory:')
655 conn.register('sheet', df)
656 start = time.time()
657 result = conn.execute("""
658 SELECT Category,
659 COUNT(*) as count,
660 AVG(Price) as avg_price,
661 SUM(Total) as total_revenue
662 FROM sheet
663 GROUP BY Category
664 ORDER BY total_revenue DESC
665 """).pl()
666 sql_time = time.time() - start
667 conn.close()
668
669 results.append({
670 "operation": "Complex SQL",
671 "time": sql_time,
672 "rows_per_second": rows / sql_time
673 })
674
675 # Calculate average
676 total_ops = sum(r['rows_per_second'] for r in results)
677 avg_speed = total_ops / len(results)
678
679 return {
680 "results": results,
681 "average_speed": avg_speed,
682 "rows_tested": rows,
683 "summary": f"Average: {avg_speed:,.0f} operations/second"
684 }
685
686@app.delete("/session/{session_id}")
687async def delete_session(session_id: str):
688 """Delete session and its data"""
689 # Remove from memory
690 if session_id in sessions:
691 del sessions[session_id]
692
693 # Remove from disk
694 user_dir = get_user_dir(session_id)
695 if user_dir.exists():
696 import shutil
697 shutil.rmtree(user_dir)
698
699 return {"message": "Session deleted", "session_id": session_id}
700
701if __name__ == "__main__":
702 import uvicorn
703 print("""
704╔══════════════════════════════════════════════════╗
705║ DBBasic Server - FastAPI + Polars + DuckDB ║
706║ ║
707║ Endpoints: ║
708║ POST /session/create - Create new session ║
709║ POST /generate - Generate test data ║
710║ POST /calculate - Run calculations ║
711║ POST /sql - Execute SQL queries ║
712║ POST /benchmark - Run performance test ║
713║ ║
714║ Starting server on http://localhost:8000 ║
715╚══════════════════════════════════════════════════╝
716 """)
717
718 uvicorn.run(app, host="0.0.0.0", port=8000, reload=True)