askrobots / dbbasic Public

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

0 downloads

dbbasic/dbbasic_crud_engine.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
dbbasic_crud_engine.py · 87.6 KB · 1904 lines
1#!/usr/bin/env python3
2"""
3DBBasic CRUD Engine - Config-Driven CRUD Interface Generator
4
5Converts YAML configurations into live, real-time CRUD interfaces
6with WebSocket updates, DuckDB performance, and AI Service Builder integration.
7"""
8
9import os
10import asyncio
11import yaml
12import json
13import logging
14import csv
15import io
16from typing import Dict, List, Any, Optional
17from datetime import datetime
18from pathlib import Path
19from watchdog.observers import Observer
20from watchdog.events import FileSystemEventHandler
21
22import duckdb
23
24from dbbasic_crud_engine_presentation import get_crud_dashboard, get_template_marketplace
25from fastapi import FastAPI, HTTPException, WebSocket, WebSocketDisconnect, Depends, Query, UploadFile, File, Header, Request
26from fastapi.responses import RedirectResponse
27from fastapi.responses import HTMLResponse, FileResponse, StreamingResponse
28from fastapi.staticfiles import StaticFiles
29import uvicorn
30import requests
31import aiofiles
32
33# Import presentation layer for clean UI generation
34from presentation_layer import PresentationLayer
35
36# Configure logging
37logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
38logger = logging.getLogger(__name__)
39
40class CRUDConfigHandler(FileSystemEventHandler):
41 """Watches for CRUD config file changes and triggers reloads"""
42
43 def __init__(self, crud_engine):
44 self.crud_engine = crud_engine
45
46 def on_modified(self, event):
47 if event.is_directory:
48 return
49 if event.src_path.endswith('_crud.yaml'):
50 logger.info(f"🔄 CRUD config changed: {event.src_path}")
51 asyncio.create_task(self.crud_engine.reload_config(event.src_path))
52
53class CRUDResource:
54 """Represents a single CRUD resource with its configuration"""
55
56 def __init__(self, config_path: str, config: Dict[str, Any]):
57 self.config_path = config_path
58 self.config = config
59 self.resource_name = config['resource']
60 self.database_path = config.get('database', f"{self.resource_name}.db")
61 self.fields = config['fields']
62 self.interface = config.get('interface', {})
63 self.hooks = config.get('hooks', {})
64 self.permissions = config.get('permissions', {})
65 self.api = config.get('api', {})
66
67 # Initialize database connection
68 self.db = duckdb.connect(self.database_path)
69 self._create_table()
70
71 def _create_table(self):
72 """Create database table based on field configuration"""
73 fields_sql = []
74
75 for field_name, field_config in self.fields.items():
76 field_type = field_config.get('type', 'string')
77
78 # Map field types to SQL types
79 sql_type = {
80 'primary_key': 'INTEGER PRIMARY KEY',
81 'string': f"VARCHAR({field_config.get('max_length', 255)})",
82 'email': 'VARCHAR(255)',
83 'decimal': 'DECIMAL(10,2)',
84 'integer': 'INTEGER',
85 'timestamp': 'TIMESTAMP',
86 'boolean': 'BOOLEAN',
87 'text': 'TEXT',
88 'select': 'VARCHAR(50)'
89 }.get(field_type, 'VARCHAR(255)')
90
91 # Add constraints
92 constraints = []
93 if field_config.get('required', False) and field_type != 'primary_key':
94 constraints.append('NOT NULL')
95 if field_config.get('unique', False):
96 constraints.append('UNIQUE')
97 if 'default' in field_config:
98 default_val = field_config['default']
99 if isinstance(default_val, str):
100 constraints.append(f"DEFAULT '{default_val}'")
101 else:
102 constraints.append(f"DEFAULT {default_val}")
103
104 constraint_str = ' ' + ' '.join(constraints) if constraints else ''
105 fields_sql.append(f"{field_name} {sql_type}{constraint_str}")
106
107 create_sql = f"""
108 CREATE TABLE IF NOT EXISTS {self.resource_name} (
109 {', '.join(fields_sql)}
110 )
111 """
112
113 try:
114 self.db.execute(create_sql)
115 logger.info(f"✅ Table '{self.resource_name}' created/verified")
116 except Exception as e:
117 logger.error(f"❌ Error creating table '{self.resource_name}': {e}")
118
119 def generate_list_html(self) -> str:
120 """Generate HTML for list view"""
121 list_display = self.interface.get('list_display', list(self.fields.keys())[:5])
122 search_fields = self.interface.get('search_fields', [])
123 filters = self.interface.get('filters', [])
124
125 # Generate search form
126 search_form = ""
127 if search_fields:
128 search_inputs = [f'<input type="text" name="search_{field}" placeholder="Search {field}" class="search-input">'
129 for field in search_fields]
130 search_form = f"""
131 <div class="search-bar">
132 <form class="search-form">
133 {' '.join(search_inputs)}
134 <button type="submit" class="search-btn">Search</button>
135 </form>
136 </div>
137 """
138
139 # Generate filter form
140 filter_form = ""
141 if filters:
142 filter_inputs = []
143 for filter_field in filters:
144 field_config = self.fields.get(filter_field, {})
145 if field_config.get('type') == 'select':
146 options = field_config.get('options', [])
147 option_html = ''.join([f'<option value="{opt}">{opt}</option>' for opt in options])
148 filter_inputs.append(f"""
149 <select name="filter_{filter_field}" class="filter-select">
150 <option value="">All {filter_field}</option>
151 {option_html}
152 </select>
153 """)
154 else:
155 filter_inputs.append(f'<input type="text" name="filter_{filter_field}" placeholder="Filter {filter_field}" class="filter-input">')
156
157 if filter_inputs:
158 filter_form = f"""
159 <div class="filter-bar">
160 <form class="filter-form">
161 {' '.join(filter_inputs)}
162 <button type="submit" class="filter-btn">Filter</button>
163 </form>
164 </div>
165 """
166
167 # Generate table headers
168 headers = [f'<th class="sortable" data-field="{field}">{field.replace("_", " ").title()}</th>'
169 for field in list_display]
170 headers.append('<th>Actions</th>')
171
172 return f"""
173 <!DOCTYPE html>
174 <html>
175 <head>
176 <title>{self.resource_name.title()} - DBBasic CRUD</title>
177 <meta charset="utf-8">
178 <meta name="viewport" content="width=device-width, initial-scale=1">
179 <style>
180 body {{ font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; margin: 0; padding: 20px; background: #f5f5f5; }}
181 .container {{ max-width: 1200px; margin: 0 auto; background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }}
182 .header {{ display: flex; justify-content: space-between; align-items: center; margin-bottom: 20px; }}
183 .title {{ font-size: 24px; font-weight: bold; color: #333; }}
184 .add-btn {{ background: #007bff; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; }}
185 .add-btn:hover {{ background: #0056b3; }}
186 .search-bar, .filter-bar {{ margin-bottom: 20px; }}
187 .search-form, .filter-form {{ display: flex; gap: 10px; flex-wrap: wrap; }}
188 .search-input, .filter-input, .filter-select {{ padding: 8px; border: 1px solid #ddd; border-radius: 4px; }}
189 .search-btn, .filter-btn {{ background: #28a745; color: white; padding: 8px 16px; border: none; border-radius: 4px; cursor: pointer; }}
190 .search-btn:hover, .filter-btn:hover {{ background: #218838; }}
191 .table-container {{ overflow-x: auto; }}
192 table {{ width: 100%; border-collapse: collapse; }}
193 th, td {{ padding: 12px; text-align: left; border-bottom: 1px solid #ddd; }}
194 th {{ background: #f8f9fa; font-weight: 600; cursor: pointer; user-select: none; }}
195 th:hover {{ background: #e9ecef; }}
196 tr:hover {{ background: #f8f9fa; }}
197 .actions {{ display: flex; gap: 8px; }}
198 .edit-btn, .delete-btn {{ padding: 4px 8px; border: none; border-radius: 4px; cursor: pointer; font-size: 12px; }}
199 .edit-btn {{ background: #ffc107; color: #212529; }}
200 .delete-btn {{ background: #dc3545; color: white; }}
201 .edit-btn:hover {{ background: #e0a800; }}
202 .delete-btn:hover {{ background: #c82333; }}
203 .status {{ font-weight: 500; padding: 4px 8px; border-radius: 12px; font-size: 12px; }}
204 .status.active {{ background: #d4edda; color: #155724; }}
205 .status.inactive {{ background: #f8d7da; color: #721c24; }}
206 .status.pending {{ background: #fff3cd; color: #856404; }}
207 </style>
208 </head>
209 <body>
210 <div class="container">
211 <div class="header">
212 <div class="title">{self.resource_name.title()} Management</div>
213 <button class="add-btn" onclick="createNew()">+ Add {self.resource_name.title()}</button>
214 </div>
215
216 {search_form}
217 {filter_form}
218
219 <div class="table-container">
220 <table id="dataTable">
221 <thead>
222 <tr>{''.join(headers)}</tr>
223 </thead>
224 <tbody id="tableBody">
225 <!-- Data will be loaded via WebSocket -->
226 </tbody>
227 </table>
228 </div>
229 </div>
230
231 <script>
232 const resourceName = '{self.resource_name}';
233 const ws = new WebSocket('ws://localhost:8005/ws/{self.resource_name}');
234
235 ws.onmessage = function(event) {{
236 const data = JSON.parse(event.data);
237 if (data.type === 'data_update') {{
238 updateTable(data.records);
239 }}
240 }};
241
242 function updateTable(records) {{
243 const tbody = document.getElementById('tableBody');
244 tbody.innerHTML = records.map(record => `
245 <tr>
246 {' '.join([f"<td>${{record.{field} || ''}}</td>" for field in list_display])}
247 <td class="actions">
248 <button class="edit-btn" onclick="editRecord(${{record.id}})">Edit</button>
249 <button class="delete-btn" onclick="deleteRecord(${{record.id}})">Delete</button>
250 </td>
251 </tr>
252 `).join('');
253 }}
254
255 function createNew() {{
256 window.location.href = `/{self.resource_name}/create`;
257 }}
258
259 function editRecord(id) {{
260 window.location.href = `/{self.resource_name}/${{id}}/edit`;
261 }}
262
263 function deleteRecord(id) {{
264 if (confirm('Are you sure you want to delete this record?')) {{
265 fetch(`/api/{self.resource_name}/${{id}}`, {{ method: 'DELETE' }})
266 .then(() => location.reload());
267 }}
268 }}
269
270 // Request initial data
271 ws.onopen = function() {{
272 ws.send(JSON.stringify({{ type: 'request_data' }}));
273 }};
274
275 // Also fetch data via HTTP as fallback
276 fetch('/api/{self.resource_name}')
277 .then(response => response.json())
278 .then(data => {{
279 if (data.records && data.records.length > 0) {{
280 updateTable(data.records);
281 }}
282 }})
283 .catch(err => console.error('Error fetching initial data:', err));
284 </script>
285 </body>
286 </html>
287 """
288
289 def generate_form_html(self, record_id: Optional[str] = None) -> str:
290 """Generate HTML for create/edit form"""
291 is_edit = record_id is not None
292 title = f"Edit {self.resource_name.title()}" if is_edit else f"Create {self.resource_name.title()}"
293
294 # Generate form fields
295 form_fields = []
296 for field_name, field_config in self.fields.items():
297 if field_config.get('type') == 'primary_key' or field_config.get('auto_now', False) or field_config.get('auto_now_add', False):
298 continue # Skip auto fields
299
300 field_type = field_config.get('type', 'string')
301 required = ' required' if field_config.get('required', False) else ''
302
303 if field_type == 'select':
304 options = field_config.get('options', [])
305 option_html = ''.join([f'<option value="{opt}">{opt}</option>' for opt in options])
306 form_fields.append(f"""
307 <div class="form-group">
308 <label for="{field_name}">{field_name.replace('_', ' ').title()}</label>
309 <select id="{field_name}" name="{field_name}"{required}>
310 <option value="">Select {field_name}</option>
311 {option_html}
312 </select>
313 </div>
314 """)
315 elif field_type in ['text']:
316 form_fields.append(f"""
317 <div class="form-group">
318 <label for="{field_name}">{field_name.replace('_', ' ').title()}</label>
319 <textarea id="{field_name}" name="{field_name}"{required}></textarea>
320 </div>
321 """)
322 else:
323 input_type = {
324 'email': 'email',
325 'decimal': 'number',
326 'integer': 'number'
327 }.get(field_type, 'text')
328
329 step = ' step="0.01"' if field_type == 'decimal' else ''
330 min_val = f' min="{field_config["min"]}"' if 'min' in field_config else ''
331 max_val = f' max="{field_config["max"]}"' if 'max' in field_config else ''
332 max_length = f' maxlength="{field_config["max_length"]}"' if 'max_length' in field_config else ''
333
334 form_fields.append(f"""
335 <div class="form-group">
336 <label for="{field_name}">{field_name.replace('_', ' ').title()}</label>
337 <input type="{input_type}" id="{field_name}" name="{field_name}"{required}{step}{min_val}{max_val}{max_length}>
338 </div>
339 """)
340
341 return f"""
342 <!DOCTYPE html>
343 <html>
344 <head>
345 <title>{title} - DBBasic CRUD</title>
346 <meta charset="utf-8">
347 <meta name="viewport" content="width=device-width, initial-scale=1">
348 <style>
349 body {{ font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; margin: 0; padding: 20px; background: #f5f5f5; }}
350 .container {{ max-width: 600px; margin: 0 auto; background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }}
351 .header {{ display: flex; justify-content: space-between; align-items: center; margin-bottom: 20px; }}
352 .title {{ font-size: 24px; font-weight: bold; color: #333; }}
353 .back-btn {{ background: #6c757d; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; text-decoration: none; }}
354 .back-btn:hover {{ background: #545b62; }}
355 .form-group {{ margin-bottom: 20px; }}
356 label {{ display: block; margin-bottom: 5px; font-weight: 500; color: #333; }}
357 input, select, textarea {{ width: 100%; padding: 10px; border: 1px solid #ddd; border-radius: 4px; font-size: 14px; }}
358 textarea {{ height: 100px; resize: vertical; }}
359 .form-actions {{ display: flex; gap: 10px; justify-content: flex-end; margin-top: 30px; }}
360 .save-btn {{ background: #007bff; color: white; padding: 12px 24px; border: none; border-radius: 4px; cursor: pointer; }}
361 .save-btn:hover {{ background: #0056b3; }}
362 .cancel-btn {{ background: #6c757d; color: white; padding: 12px 24px; border: none; border-radius: 4px; cursor: pointer; text-decoration: none; }}
363 .cancel-btn:hover {{ background: #545b62; }}
364 </style>
365 </head>
366 <body>
367 <div class="container">
368 <div class="header">
369 <div class="title">{title}</div>
370 <a href="/{self.resource_name}" class="back-btn">← Back to List</a>
371 </div>
372
373 <form id="recordForm" onsubmit="saveRecord(event)">
374 {''.join(form_fields)}
375
376 <div class="form-actions">
377 <a href="/{self.resource_name}" class="cancel-btn">Cancel</a>
378 <button type="submit" class="save-btn">Save</button>
379 </div>
380 </form>
381 </div>
382
383 <script>
384 const isEdit = {str(is_edit).lower()};
385 const recordId = {f"'{record_id}'" if record_id else 'null'};
386 const resourceName = '{self.resource_name}';
387
388 if (isEdit && recordId) {{
389 // Load existing record data
390 fetch(`/api/{self.resource_name}/${{recordId}}`)
391 .then(response => response.json())
392 .then(data => {{
393 for (const [key, value] of Object.entries(data)) {{
394 const field = document.getElementById(key);
395 if (field) field.value = value || '';
396 }}
397 }});
398 }}
399
400 function saveRecord(event) {{
401 event.preventDefault();
402 const formData = new FormData(event.target);
403 const data = Object.fromEntries(formData.entries());
404
405 const method = isEdit ? 'PUT' : 'POST';
406 const url = isEdit ? `/api/{self.resource_name}/${{recordId}}` : `/api/{self.resource_name}`;
407
408 fetch(url, {{
409 method: method,
410 headers: {{ 'Content-Type': 'application/json' }},
411 body: JSON.stringify(data)
412 }})
413 .then(response => {{
414 if (response.ok) {{
415 window.location.href = '/{self.resource_name}';
416 }} else {{
417 alert('Error saving record');
418 }}
419 }});
420 }}
421 </script>
422 </body>
423 </html>
424 """
425
426class CRUDEngine:
427 """Main CRUD Engine that manages all resources and provides web interface"""
428
429 def __init__(self):
430 self.app = FastAPI(title="DBBasic CRUD Engine", version="1.0.0")
431 self.resources: Dict[str, CRUDResource] = {}
432 self.websocket_connections: Dict[str, List[WebSocket]] = {}
433 self.config_watcher = Observer()
434
435 # Setup file watcher
436 handler = CRUDConfigHandler(self)
437 self.config_watcher.schedule(handler, ".", recursive=False)
438
439 # Setup FastAPI routes
440 self._setup_routes()
441
442 # Load initial configurations
443 self.load_all_configs()
444
445 def _setup_routes(self):
446 """Setup FastAPI routes"""
447
448 @self.app.get("/")
449 async def dashboard():
450 """Serve CRUD Engine dashboard using presentation layer"""
451 ui_data = get_crud_dashboard()
452 html_content = PresentationLayer.render(ui_data, 'bootstrap')
453 return HTMLResponse(content=html_content)
454
455 @self.app.get("/models/new")
456 async def new_model_form():
457 """Show form for creating a new CRUD model"""
458 from dbbasic_unified_ui import get_master_layout
459
460 ui_data = get_master_layout(
461 title='Create New Model - Data Service',
462 service_name='data',
463 content=[
464 {
465 'type': 'breadcrumb',
466 'items': [
467 {'text': 'Data Service', 'url': '/'},
468 {'text': 'Create New Model', 'active': True}
469 ]
470 },
471 {
472 'type': 'card',
473 'title': '📝 Create New CRUD Model',
474 'body': {
475 'type': 'form',
476 'action': '/api/models/create',
477 'method': 'POST',
478 'fields': [
479 {
480 'type': 'text',
481 'name': 'resource',
482 'label': 'Resource Name',
483 'placeholder': 'e.g., products, orders, customers',
484 'required': True,
485 'help': 'Lowercase plural name for your model'
486 },
487 {
488 'type': 'text',
489 'name': 'title',
490 'label': 'Display Title',
491 'placeholder': 'e.g., Product Management',
492 'required': True
493 },
494 {
495 'type': 'textarea',
496 'name': 'description',
497 'label': 'Description',
498 'placeholder': 'Brief description of what this model does',
499 'rows': 3
500 },
501 {
502 'type': 'textarea',
503 'name': 'yaml_config',
504 'label': 'YAML Configuration',
505 'placeholder': '''fields:
506 id:
507 type: integer
508 primary: true
509 name:
510 type: string
511 required: true
512 created_at:
513 type: timestamp
514 default: now''',
515 'rows': 15,
516 'help': 'Define your fields and configuration in YAML format'
517 }
518 ],
519 'buttons': [
520 {
521 'type': 'submit',
522 'text': 'Create Model',
523 'variant': 'primary'
524 },
525 {
526 'type': 'button',
527 'text': 'Cancel',
528 'variant': 'secondary',
529 'onclick': 'history.back()'
530 }
531 ]
532 }
533 },
534 {
535 'type': 'card',
536 'title': '💡 Quick Tips',
537 'body': {
538 'type': 'list',
539 'items': [
540 'Resource names should be lowercase and plural',
541 'Each field needs a type (string, integer, boolean, etc.)',
542 'Use "required: true" for mandatory fields',
543 'Add "hooks" section for business logic',
544 'Check templates for examples'
545 ]
546 }
547 }
548 ]
549 )
550
551 html_content = PresentationLayer.render(ui_data, 'bootstrap')
552 return HTMLResponse(content=html_content)
553
554 @self.app.get("/templates")
555 async def templates_marketplace():
556 """Show template marketplace with deployment functionality"""
557 try:
558 # Get available templates
559 from pathlib import Path
560 templates_dir = Path("templates")
561 templates = []
562
563 if templates_dir.exists():
564 for category_dir in templates_dir.iterdir():
565 if category_dir.is_dir():
566 category = category_dir.name
567 for template_file in category_dir.glob("*.yaml"):
568 template_name = template_file.stem.replace('_crud', '')
569 template_id = f"{category}/{template_file.stem}"
570
571 # Read template for metadata
572 try:
573 with open(template_file, 'r') as f:
574 config = yaml.safe_load(f)
575
576 templates.append({
577 'id': template_id,
578 'name': template_name.replace('_', ' ').title(),
579 'category': category.title(),
580 'description': config.get('description', f"{category.title()} {template_name} management"),
581 'resource': config.get('resource', template_name),
582 'fields_count': len(config.get('fields', {})),
583 })
584 except Exception as e:
585 logger.warning(f"Error reading template {template_file}: {e}")
586
587 # Build UI structure as data
588 ui_structure = {
589 'type': 'page',
590 'title': 'DBBasic - Template Marketplace',
591 'components': [
592 {
593 'type': 'navbar',
594 'brand': 'DBBasic',
595 'links': [
596 {'text': 'Monitor', 'url': 'http://localhost:8004'},
597 {'text': 'Data', 'url': 'http://localhost:8005'},
598 {'text': 'AI Services', 'url': 'http://localhost:8003'},
599 {'text': 'Templates', 'url': '/templates'}
600 ]
601 },
602 {
603 'type': 'hero',
604 'title': '🛍️ Template Marketplace',
605 'subtitle': 'Deploy production-ready applications instantly'
606 },
607 {
608 'type': 'grid',
609 'columns': 3,
610 'items': [
611 {
612 'type': 'card',
613 'id': f"template-{t['id'].replace('/', '-')}",
614 'title': t['name'],
615 'category': t['category'],
616 'description': f"{t['description']} • {t['fields_count']} fields",
617 'body': f'''
618 <div class="d-grid gap-2">
619 <button class="btn btn-outline-primary" onclick="previewTemplate('{t['id']}')">
620 <i class="bi bi-eye me-2"></i>Preview Config
621 </button>
622 <button class="btn btn-success" onclick="deployTemplate('{t['id']}')">
623 <i class="bi bi-rocket-takeoff me-2"></i>Deploy
624 </button>
625 </div>
626 '''
627 } for t in templates
628 ]
629 },
630 # Modal for preview
631 {
632 'type': 'raw',
633 'content': '''
634 <div class="modal fade" id="previewModal" tabindex="-1">
635 <div class="modal-dialog modal-lg modal-dialog-scrollable">
636 <div class="modal-content">
637 <div class="modal-header">
638 <h5 class="modal-title">
639 <i class="bi bi-file-code me-2"></i>
640 <span id="previewTitle">Template Preview</span>
641 </h5>
642 <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
643 </div>
644 <div class="modal-body">
645 <div id="previewContent">
646 <div class="text-center py-4">
647 <div class="spinner-border text-primary" role="status">
648 <span class="visually-hidden">Loading...</span>
649 </div>
650 </div>
651 </div>
652 </div>
653 <div class="modal-footer">
654 <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
655 <button type="button" class="btn btn-success" id="deployFromPreview">
656 <i class="bi bi-rocket-takeoff me-2"></i>Deploy This Template
657 </button>
658 </div>
659 </div>
660 </div>
661 </div>
662 '''
663 },
664 # JavaScript for preview functionality
665 {
666 'type': 'script',
667 'content': '''
668 let currentTemplateId = null;
669
670 async function previewTemplate(templateId) {
671 currentTemplateId = templateId;
672 const modal = new bootstrap.Modal(document.getElementById('previewModal'));
673 modal.show();
674
675 // Update title
676 document.getElementById('previewTitle').textContent = `Template: ${templateId}`;
677
678 // Show loading
679 document.getElementById('previewContent').innerHTML = `
680 <div class="text-center py-4">
681 <div class="spinner-border text-primary" role="status">
682 <span class="visually-hidden">Loading...</span>
683 </div>
684 </div>
685 `;
686
687 try {
688 const response = await fetch(`/api/templates/${templateId}/preview`);
689 const data = await response.json();
690
691 // Display YAML with basic syntax highlighting
692 const highlightedYaml = data.content
693 .replace(/^(\w+):/gm, '<span class="text-primary fw-bold">$1</span>:')
694 .replace(/: (.*)/g, ': <span class="text-success">$1</span>')
695 .replace(/(#.*$)/gm, '<span class="text-muted fst-italic">$1</span>')
696 .replace(/(\n - )/g, '\n <span class="text-warning">-</span> ');
697
698 document.getElementById('previewContent').innerHTML = `
699 <div class="mb-3">
700 <div class="alert alert-info">
701 <strong>Resource:</strong> ${data.metadata.resource}<br>
702 <strong>Fields:</strong> ${data.metadata.fields_count}<br>
703 <strong>Hooks:</strong> ${data.metadata.hooks_count || 0}
704 </div>
705 </div>
706 <pre class="bg-dark text-light p-3 rounded" style="max-height: 500px; overflow-y: auto;">
707 <code>${highlightedYaml}</code>
708 </pre>
709 `;
710
711 // Set up deploy button
712 document.getElementById('deployFromPreview').onclick = () => {
713 modal.hide();
714 deployTemplate(templateId);
715 };
716
717 } catch (error) {
718 document.getElementById('previewContent').innerHTML = `
719 <div class="alert alert-danger">
720 <i class="bi bi-exclamation-triangle me-2"></i>
721 Error loading template: ${error.message}
722 </div>
723 `;
724 }
725 }
726
727 function deployTemplate(templateId) {
728 if (confirm(`Deploy template: ${templateId}?`)) {
729 // Implementation for deployment
730 fetch('/api/templates/deploy?template_id=' + encodeURIComponent(templateId), {
731 method: 'POST'
732 }).then(response => {
733 if (response.ok) {
734 alert('Template deployed successfully!');
735 window.location.reload();
736 } else {
737 alert('Deployment failed. Check console for details.');
738 }
739 });
740 }
741 }
742 '''
743 }
744 ]
745 }
746
747 # Use presentation layer - can switch to 'tailwind' or custom later
748 return HTMLResponse(PresentationLayer.render(ui_structure, 'bootstrap'))
749 except Exception as e:
750 logger.error(f"Error loading templates marketplace: {e}")
751 raise HTTPException(500, f"Error loading templates: {e}")
752
753 @self.app.get("/{resource_name}")
754 async def resource_list(resource_name: str):
755 """Show list view for a resource"""
756 if resource_name not in self.resources:
757 raise HTTPException(404, f"Resource '{resource_name}' not found")
758
759 # Use presentation layer with unified UI
760 from dbbasic_unified_ui import get_master_layout
761 resource = self.resources[resource_name]
762
763 # Get resource fields for table headers
764 list_display = resource.interface.get('list_display', list(resource.fields.keys())[:5])
765
766 ui_data = get_master_layout(
767 title=f'{resource_name.title()} - Data Service',
768 service_name='data',
769 content=[
770 {
771 'type': 'breadcrumb',
772 'items': [
773 {'text': 'Data Service', 'url': '/'},
774 {'text': resource_name.title(), 'active': True}
775 ]
776 },
777 {
778 'type': 'card',
779 'title': f'📋 {resource_name.title()} Records',
780 'body': {
781 'type': 'raw',
782 'content': f'''
783 <div class="d-flex justify-content-between mb-3">
784 <div>
785 <input type="text" class="form-control" placeholder="Search..." id="searchInput" style="width: 300px;">
786 </div>
787 <a href="/{resource_name}/create" class="btn btn-success">
788 <i class="bi bi-plus-circle me-2"></i>Add New
789 </a>
790 </div>
791 <div class="table-responsive">
792 <table class="table table-hover" id="dataTable">
793 <thead>
794 <tr>
795 {' '.join([f'<th>{field.replace("_", " ").title()}</th>' for field in list_display])}
796 <th>Actions</th>
797 </tr>
798 </thead>
799 <tbody id="tableBody">
800 <!-- Data will be loaded here -->
801 </tbody>
802 </table>
803 </div>
804 '''
805 }
806 }
807 ],
808 scripts=[
809 {
810 'type': 'script',
811 'content': f'''
812 // Load data for {resource_name}
813 async function loadData() {{
814 try {{
815 const response = await fetch('/api/{resource_name}');
816 const data = await response.json();
817 const tbody = document.getElementById('tableBody');
818
819 if (data.records && data.records.length > 0) {{
820 tbody.innerHTML = data.records.map(record => {{
821 const fields = {list_display};
822 const values = fields.map(field => `<td>${{record[field] || ''}}</td>`).join('');
823 return `
824 <tr>
825 ${{values}}
826 <td>
827 <div class="btn-group btn-group-sm">
828 <a href="/{resource_name}/${{record.id}}/edit" class="btn btn-warning">
829 <i class="bi bi-pencil"></i>
830 </a>
831 <button class="btn btn-danger" onclick="deleteRecord(${{record.id}})">
832 <i class="bi bi-trash"></i>
833 </button>
834 </div>
835 </td>
836 </tr>
837 `;
838 }}).join('');
839 }} else {{
840 tbody.innerHTML = '<tr><td colspan="{len(list_display) + 1}" class="text-center">No records found</td></tr>';
841 }}
842 }} catch (error) {{
843 console.error('Error loading data:', error);
844 }}
845 }}
846
847 async function deleteRecord(id) {{
848 if (confirm('Are you sure you want to delete this record?')) {{
849 try {{
850 await fetch(`/api/{resource_name}/${{id}}`, {{ method: 'DELETE' }});
851 loadData();
852 }} catch (error) {{
853 console.error('Error deleting record:', error);
854 }}
855 }}
856 }}
857
858 // Search functionality
859 document.getElementById('searchInput').addEventListener('input', function(e) {{
860 const filter = e.target.value.toLowerCase();
861 const rows = document.querySelectorAll('#tableBody tr');
862 rows.forEach(row => {{
863 const text = row.textContent.toLowerCase();
864 row.style.display = text.includes(filter) ? '' : 'none';
865 }});
866 }});
867
868 // Load data on page load
869 document.addEventListener('DOMContentLoaded', loadData);
870 '''
871 }
872 ]
873 )
874
875 html_content = PresentationLayer.render(ui_data, 'bootstrap')
876 return HTMLResponse(content=html_content)
877
878 @self.app.get("/{resource_name}/create")
879 async def resource_create_form(resource_name: str):
880 """Show create form for a resource"""
881 if resource_name not in self.resources:
882 raise HTTPException(404, f"Resource '{resource_name}' not found")
883 return HTMLResponse(self.resources[resource_name].generate_form_html())
884
885 @self.app.get("/{resource_name}/{record_id}/edit")
886 async def resource_edit_form(resource_name: str, record_id: str):
887 """Show edit form for a resource"""
888 if resource_name not in self.resources:
889 raise HTTPException(404, f"Resource '{resource_name}' not found")
890 return HTMLResponse(self.resources[resource_name].generate_form_html(record_id))
891
892 # API Endpoints - must come before generic routes
893 @self.app.get("/api/models")
894 async def api_get_models():
895 """Get list of active CRUD models/resources"""
896 models = []
897 for resource_name, resource in self.resources.items():
898 try:
899 # Get record count
900 count = len(resource.db.execute(f"SELECT COUNT(*) FROM {resource_name}").fetchall())
901
902 # Get fields from config
903 fields = list(resource.config.get('fields', {}).keys())
904
905 models.append({
906 'name': resource_name,
907 'title': resource.config.get('title', resource_name.title()),
908 'description': resource.config.get('description', f'{resource_name} management'),
909 'fields': fields,
910 'field_count': len(fields),
911 'record_count': count,
912 'database': resource.config.get('database', f'{resource_name}.db'),
913 'hooks': list(resource.config.get('hooks', {}).keys()),
914 'endpoint': f'/{resource_name}'
915 })
916 except Exception as e:
917 logger.warning(f"Error getting model info for {resource_name}: {e}")
918 models.append({
919 'name': resource_name,
920 'error': str(e)
921 })
922
923 return models
924
925 # Template Marketplace APIs - must come before generic routes
926 @self.app.get("/api/templates/{template_id:path}/preview")
927 async def api_preview_template(template_id: str):
928 """Get the YAML content of a template for preview"""
929 try:
930 from pathlib import Path
931
932 # Validate and sanitize template_id
933 if '..' in template_id or template_id.startswith('/'):
934 raise HTTPException(400, "Invalid template ID")
935
936 # Parse template_id (e.g., "blog/posts_crud")
937 if '/' not in template_id:
938 raise HTTPException(400, "Invalid template ID format. Expected: category/template")
939
940 # Ensure template_id ends with _crud if not already
941 if not template_id.endswith('_crud'):
942 parts = template_id.rsplit('/', 1)
943 if len(parts) == 2:
944 template_id = f"{parts[0]}/{parts[1]}_crud"
945
946 template_path = Path(f"templates/{template_id}.yaml")
947
948 if not template_path.exists():
949 raise HTTPException(404, f"Template not found: {template_id}")
950
951 # Read the YAML content
952 with open(template_path, 'r') as f:
953 yaml_content = f.read()
954
955 # Parse to get metadata
956 config = yaml.safe_load(yaml_content)
957
958 return {
959 'id': template_id,
960 'content': yaml_content,
961 'metadata': {
962 'resource': config.get('resource', ''),
963 'title': config.get('title', ''),
964 'description': config.get('description', ''),
965 'fields_count': len(config.get('fields', {})),
966 'hooks_count': len(config.get('hooks', {}))
967 }
968 }
969
970 except HTTPException:
971 raise
972 except Exception as e:
973 logger.error(f"Error previewing template {template_id}: {e}")
974 raise HTTPException(500, f"Error loading template: {e}")
975
976 @self.app.get("/api/templates")
977 async def api_get_templates():
978 """Get list of available templates from the templates directory"""
979 try:
980 from pathlib import Path
981 templates_dir = Path("templates")
982
983 if not templates_dir.exists():
984 return {"templates": [], "categories": []}
985
986 templates = []
987 categories = set()
988
989 # Scan template directories
990 for category_dir in templates_dir.iterdir():
991 if category_dir.is_dir() and not category_dir.name.startswith('.'):
992 category_name = category_dir.name
993 categories.add(category_name)
994
995 # Scan template files in category
996 for template_file in category_dir.glob("*_crud.yaml"):
997 try:
998 with open(template_file, 'r') as f:
999 template_config = yaml.safe_load(f)
1000
1001 resource_name = template_config.get('resource', template_file.stem.replace('_crud', ''))
1002
1003 template_info = {
1004 "id": f"{category_name}/{template_file.stem}",
1005 "name": resource_name.replace('_', ' ').title(),
1006 "description": f"Complete {resource_name} management system",
1007 "category": category_name,
1008 "file_path": str(template_file),
1009 "fields_count": len(template_config.get('fields', {})),
1010 "has_hooks": bool(template_config.get('hooks', {})),
1011 "has_permissions": bool(template_config.get('permissions', {})),
1012 "ui_theme": template_config.get('ui', {}).get('theme', 'bootstrap')
1013 }
1014 templates.append(template_info)
1015
1016 except Exception as e:
1017 logger.warning(f"Error reading template {template_file}: {e}")
1018
1019 return {
1020 "templates": templates,
1021 "categories": sorted(list(categories))
1022 }
1023
1024 except Exception as e:
1025 logger.error(f"Error getting templates: {e}")
1026 raise HTTPException(500, f"Error getting templates: {e}")
1027
1028 @self.app.post("/api/models/create")
1029 async def api_create_model(request: Request):
1030 """Create a new CRUD model from form data"""
1031 try:
1032 from pathlib import Path
1033
1034 # Parse form data
1035 form_data = await request.form()
1036 resource = form_data.get('resource', '').strip()
1037 title = form_data.get('title', '')
1038 description = form_data.get('description', '')
1039 yaml_config = form_data.get('yaml_config', '')
1040
1041 if not resource:
1042 raise HTTPException(400, "Resource name is required")
1043
1044 # Create the YAML configuration
1045 config_content = f"""# {title}
1046# {description}
1047
1048resource: {resource}
1049title: {title}
1050description: {description}
1051
1052{yaml_config}
1053"""
1054
1055 # Save to a file
1056 filename = f"{resource}_crud.yaml"
1057 file_path = Path(filename)
1058
1059 with open(file_path, 'w') as f:
1060 f.write(config_content)
1061
1062 # Load the new resource
1063 self.load_resource(filename)
1064
1065 # Redirect to the new resource
1066 return RedirectResponse(url=f"/{resource}", status_code=303)
1067
1068 except Exception as e:
1069 logger.error(f"Error creating model: {e}")
1070 raise HTTPException(500, f"Error creating model: {e}")
1071
1072 @self.app.post("/api/templates/deploy")
1073 async def api_deploy_template(
1074 template_id: str = Query(..., description="Template ID in format category/template"),
1075 deployment_config: dict = {}
1076 ):
1077 """Deploy a template by copying it to the current directory and loading it"""
1078 try:
1079 from pathlib import Path
1080 import shutil
1081 import time
1082
1083 # Parse template_id (e.g., "blog/posts_crud")
1084 if '/' not in template_id:
1085 raise HTTPException(400, "Invalid template_id format. Expected: category/template")
1086
1087 category, template_name = template_id.split('/', 1)
1088 template_file = Path(f"templates/{category}/{template_name}.yaml")
1089
1090 if not template_file.exists():
1091 raise HTTPException(404, f"Template not found: {template_id}")
1092
1093 # Generate unique resource name for deployment
1094 base_name = deployment_config.get('resource_name', template_name.replace('_crud', ''))
1095 timestamp = str(int(time.time()))[-6:] # Last 6 digits of timestamp
1096 deployed_name = f"{base_name}_{timestamp}"
1097 deployed_file = Path(f"{deployed_name}_crud.yaml")
1098
1099 # Copy template file to current directory
1100 shutil.copy2(template_file, deployed_file)
1101
1102 # Customize the deployed template
1103 with open(deployed_file, 'r') as f:
1104 config = yaml.safe_load(f)
1105
1106 # Update resource name and database
1107 config['resource'] = deployed_name
1108 if 'database' in config:
1109 original_db = config['database']
1110 # Create unique database name
1111 db_name = f"{deployed_name}.db"
1112 config['database'] = db_name
1113
1114 # Apply any custom configuration
1115 if deployment_config:
1116 # Allow customizing certain fields
1117 if 'title_override' in deployment_config:
1118 # Find title-like fields and update descriptions
1119 for field_name, field_config in config.get('fields', {}).items():
1120 if field_name in ['title', 'name'] and 'ui' in field_config:
1121 field_config['ui']['placeholder'] = deployment_config['title_override']
1122
1123 # Write customized config back
1124 with open(deployed_file, 'w') as f:
1125 yaml.dump(config, f, default_flow_style=False, sort_keys=False)
1126
1127 # Load the new template into the engine
1128 self.load_config(str(deployed_file))
1129
1130 logger.info(f"✅ Template deployed: {template_id} → {deployed_name}")
1131
1132 return {
1133 "success": True,
1134 "resource_name": deployed_name,
1135 "file_path": str(deployed_file),
1136 "url": f"http://localhost:8005/{deployed_name}",
1137 "api_url": f"http://localhost:8005/api/{deployed_name}",
1138 "message": f"Template {template_id} deployed successfully as {deployed_name}"
1139 }
1140
1141 except Exception as e:
1142 logger.error(f"❌ Error deploying template {template_id}: {e}")
1143 raise HTTPException(500, f"Error deploying template: {e}")
1144
1145 @self.app.get("/api/templates/preview")
1146 async def api_preview_template(template_id: str = Query(..., description="Template ID in format category/template")):
1147 """Get a preview of a template's configuration and UI"""
1148 try:
1149 from pathlib import Path
1150
1151 # Parse template_id
1152 if '/' not in template_id:
1153 raise HTTPException(400, "Invalid template_id format. Expected: category/template")
1154
1155 category, template_name = template_id.split('/', 1)
1156 template_file = Path(f"templates/{category}/{template_name}.yaml")
1157
1158 if not template_file.exists():
1159 raise HTTPException(404, f"Template not found: {template_id}")
1160
1161 # Read template configuration
1162 with open(template_file, 'r') as f:
1163 config = yaml.safe_load(f)
1164
1165 # Generate preview data
1166 resource_name = config.get('resource', template_name.replace('_crud', ''))
1167 fields = config.get('fields', {})
1168
1169 # Create sample preview data structure
1170 preview_data = {
1171 "resource_name": resource_name,
1172 "fields": list(fields.keys()),
1173 "field_types": {name: field.get('type', 'string') for name, field in fields.items()},
1174 "ui_components": {},
1175 "has_hooks": bool(config.get('hooks', {})),
1176 "hook_events": list(config.get('hooks', {}).keys()),
1177 "permissions": config.get('permissions', {}),
1178 "ui_theme": config.get('ui', {}).get('theme', 'bootstrap'),
1179 "sample_form_fields": []
1180 }
1181
1182 # Generate UI component preview
1183 for field_name, field_config in fields.items():
1184 ui_config = field_config.get('ui', {})
1185 component_type = ui_config.get('component', 'input')
1186
1187 preview_data['ui_components'][field_name] = {
1188 "type": component_type,
1189 "placeholder": ui_config.get('placeholder', ''),
1190 "help_text": ui_config.get('help_text', ''),
1191 "required": field_config.get('required', False)
1192 }
1193
1194 # Sample form field for preview
1195 preview_data['sample_form_fields'].append({
1196 "name": field_name,
1197 "label": field_name.replace('_', ' ').title(),
1198 "type": component_type,
1199 "value": f"Sample {field_name}"
1200 })
1201
1202 return preview_data
1203
1204 except Exception as e:
1205 logger.error(f"❌ Error previewing template {template_id}: {e}")
1206 raise HTTPException(500, f"Error previewing template: {e}")
1207
1208 # API routes for resources
1209 @self.app.get("/api/{resource_name}")
1210 async def api_list_records(resource_name: str, limit: int = Query(25), offset: int = Query(0), x_user: str = Header(None)):
1211 """Get records for a resource"""
1212 if resource_name not in self.resources:
1213 raise HTTPException(404, f"Resource '{resource_name}' not found")
1214
1215 resource = self.resources[resource_name]
1216
1217 # Check read permission
1218 if not self._check_permission(resource, 'read', x_user):
1219 raise HTTPException(403, "Permission denied")
1220
1221 try:
1222 result = resource.db.execute(
1223 f"SELECT * FROM {resource_name} LIMIT {limit} OFFSET {offset}"
1224 ).fetchall()
1225
1226 # Convert to list of dicts
1227 columns = [desc[0] for desc in resource.db.description]
1228 records = [dict(zip(columns, row)) for row in result]
1229
1230 return {"records": records, "total": len(result)}
1231 except Exception as e:
1232 raise HTTPException(500, f"Database error: {e}")
1233
1234 @self.app.get("/api/{resource_name}/{record_id}")
1235 async def api_get_record(resource_name: str, record_id: str, x_user: str = Header(None)):
1236 """Get single record"""
1237 if resource_name not in self.resources:
1238 raise HTTPException(404, f"Resource '{resource_name}' not found")
1239
1240 resource = self.resources[resource_name]
1241
1242 # Check read permission
1243 if not self._check_permission(resource, 'read', x_user):
1244 raise HTTPException(403, "Permission denied")
1245
1246 try:
1247 result = resource.db.execute(
1248 f"SELECT * FROM {resource_name} WHERE id = ?", [record_id]
1249 ).fetchone()
1250
1251 if not result:
1252 raise HTTPException(404, "Record not found")
1253
1254 columns = [desc[0] for desc in resource.db.description]
1255 return dict(zip(columns, result))
1256 except Exception as e:
1257 raise HTTPException(500, f"Database error: {e}")
1258
1259 @self.app.post("/api/{resource_name}")
1260 async def api_create_record(resource_name: str, data: dict, x_user: str = Header(None)):
1261 """Create new record"""
1262 if resource_name not in self.resources:
1263 raise HTTPException(404, f"Resource '{resource_name}' not found")
1264
1265 resource = self.resources[resource_name]
1266
1267 # Check create permission
1268 if not self._check_permission(resource, 'create', x_user):
1269 raise HTTPException(403, "Permission denied")
1270
1271 # Auto-populate magic fields
1272 data = self._populate_magic_fields(resource, data, 'create', x_user)
1273
1274 # Execute hooks
1275 if 'before_create' in resource.hooks:
1276 await self._execute_hook(resource.hooks['before_create'], data)
1277
1278 try:
1279 # For DuckDB, we need to generate the ID ourselves
1280 # Get next ID by finding max existing ID
1281 max_id_result = resource.db.execute(f"SELECT COALESCE(MAX(id), 0) FROM {resource_name}").fetchone()
1282 next_id = (max_id_result[0] if max_id_result else 0) + 1
1283
1284 # Add the generated ID to the data
1285 data['id'] = next_id
1286
1287 # Build insert query - include all fields including generated ID
1288 fields = [k for k in data.keys() if k in resource.fields]
1289 placeholders = ','.join(['?' for _ in fields])
1290 values = [data[f] for f in fields]
1291
1292 # Execute the insert
1293 resource.db.execute(
1294 f"INSERT INTO {resource_name} ({','.join(fields)}) VALUES ({placeholders})",
1295 values
1296 )
1297
1298 # Get the created record
1299 created_record = resource.db.execute(
1300 f"SELECT * FROM {resource_name} WHERE id = ?", [next_id]
1301 ).fetchone()
1302
1303 columns = [desc[0] for desc in resource.db.description]
1304 record_dict = dict(zip(columns, created_record))
1305
1306 # Execute after hook
1307 if 'after_create' in resource.hooks:
1308 await self._execute_hook(resource.hooks['after_create'], record_dict)
1309
1310 # Notify WebSocket clients
1311 await self._broadcast_update(resource_name)
1312
1313 return record_dict
1314 except Exception as e:
1315 raise HTTPException(500, f"Database error: {e}")
1316
1317 @self.app.put("/api/{resource_name}/{record_id}")
1318 async def api_update_record(resource_name: str, record_id: int, data: dict, x_user: str = Header(None)):
1319 """Update existing record"""
1320 if resource_name not in self.resources:
1321 raise HTTPException(404, f"Resource '{resource_name}' not found")
1322
1323 resource = self.resources[resource_name]
1324
1325 # Check if record exists
1326 existing = resource.db.execute(f"SELECT * FROM {resource_name} WHERE id = ?", [record_id]).fetchone()
1327 if not existing:
1328 raise HTTPException(404, f"Record {record_id} not found")
1329
1330 # Convert to dict for permission check
1331 columns = [desc[0] for desc in resource.db.description]
1332 existing_dict = dict(zip(columns, existing))
1333
1334 # Check update permission
1335 if not self._check_permission(resource, 'update', x_user, existing_dict):
1336 raise HTTPException(403, "Permission denied")
1337
1338 # Auto-populate magic fields for update
1339 data = self._populate_magic_fields(resource, data, 'update', x_user)
1340
1341 # Execute hooks
1342 if 'before_update' in resource.hooks:
1343 await self._execute_hook(resource.hooks['before_update'], data)
1344
1345 try:
1346 # Build update query
1347 fields = [k for k in data.keys() if k in resource.fields and k != 'id']
1348 set_clause = ','.join([f"{f} = ?" for f in fields])
1349 values = [data[f] for f in fields] + [record_id]
1350
1351 # Execute the update
1352 resource.db.execute(
1353 f"UPDATE {resource_name} SET {set_clause} WHERE id = ?",
1354 values
1355 )
1356
1357 # Execute after_update hook
1358 if 'after_update' in resource.hooks:
1359 await self._execute_hook(resource.hooks['after_update'], data)
1360
1361 # Broadcast real-time update
1362 await self._broadcast_update(resource_name)
1363
1364 # Return updated record
1365 updated = resource.db.execute(f"SELECT * FROM {resource_name} WHERE id = ?", [record_id]).fetchone()
1366 columns = [desc[0] for desc in resource.db.description]
1367 return dict(zip(columns, updated))
1368
1369 except Exception as e:
1370 logger.error(f"❌ Error updating record: {e}")
1371 raise HTTPException(500, f"Error updating record: {e}")
1372
1373 @self.app.delete("/api/{resource_name}/{record_id}")
1374 async def api_delete_record(resource_name: str, record_id: int, x_user: str = Header(None)):
1375 """Delete record"""
1376 if resource_name not in self.resources:
1377 raise HTTPException(404, f"Resource '{resource_name}' not found")
1378
1379 resource = self.resources[resource_name]
1380
1381 # Check if record exists
1382 existing = resource.db.execute(f"SELECT * FROM {resource_name} WHERE id = ?", [record_id]).fetchone()
1383 if not existing:
1384 raise HTTPException(404, f"Record {record_id} not found")
1385
1386 # Convert to dict for permission check
1387 columns = [desc[0] for desc in resource.db.description]
1388 existing_dict = dict(zip(columns, existing))
1389
1390 # Check delete permission
1391 if not self._check_permission(resource, 'delete', x_user, existing_dict):
1392 raise HTTPException(403, "Permission denied")
1393
1394 # Execute hooks
1395 if 'before_delete' in resource.hooks:
1396 await self._execute_hook(resource.hooks['before_delete'], existing_dict)
1397
1398 try:
1399 # Execute the delete
1400 resource.db.execute(f"DELETE FROM {resource_name} WHERE id = ?", [record_id])
1401
1402 # Execute after_delete hook
1403 if 'after_delete' in resource.hooks:
1404 existing_data = dict(zip([desc[0] for desc in resource.db.description], existing))
1405 await self._execute_hook(resource.hooks['after_delete'], existing_data)
1406
1407 # Broadcast real-time update
1408 await self._broadcast_update(resource_name)
1409
1410 return {"message": "Record deleted successfully", "id": record_id}
1411
1412 except Exception as e:
1413 logger.error(f"❌ Error deleting record: {e}")
1414 raise HTTPException(500, f"Error deleting record: {e}")
1415
1416 @self.app.get("/api/{resource_name}/export/csv")
1417 async def api_export_csv(resource_name: str):
1418 """Export all records as CSV"""
1419 if resource_name not in self.resources:
1420 raise HTTPException(404, f"Resource '{resource_name}' not found")
1421
1422 resource = self.resources[resource_name]
1423
1424 try:
1425 # Get all records
1426 result = resource.db.execute(f"SELECT * FROM {resource_name}").fetchall()
1427 columns = [desc[0] for desc in resource.db.description]
1428
1429 # Create CSV in memory
1430 output = io.StringIO()
1431 writer = csv.writer(output)
1432
1433 # Write header
1434 writer.writerow(columns)
1435
1436 # Write data rows
1437 for row in result:
1438 writer.writerow(row)
1439
1440 # Prepare response
1441 csv_content = output.getvalue()
1442 output.close()
1443
1444 return StreamingResponse(
1445 io.StringIO(csv_content),
1446 media_type="text/csv",
1447 headers={"Content-Disposition": f"attachment; filename={resource_name}_export.csv"}
1448 )
1449
1450 except Exception as e:
1451 logger.error(f"❌ Error exporting CSV: {e}")
1452 raise HTTPException(500, f"Error exporting CSV: {e}")
1453
1454 @self.app.get("/api/{resource_name}/export/json")
1455 async def api_export_json(resource_name: str):
1456 """Export all records as JSON (for backup purposes)"""
1457 if resource_name not in self.resources:
1458 raise HTTPException(404, f"Resource '{resource_name}' not found")
1459
1460 resource = self.resources[resource_name]
1461
1462 try:
1463 # Get all records
1464 result = resource.db.execute(f"SELECT * FROM {resource_name}").fetchall()
1465 columns = [desc[0] for desc in resource.db.description]
1466 records = [dict(zip(columns, row)) for row in result]
1467
1468 # Create backup structure with metadata
1469 backup_data = {
1470 "resource": resource_name,
1471 "exported_at": datetime.now().isoformat(),
1472 "config": resource.config,
1473 "total_records": len(records),
1474 "records": records
1475 }
1476
1477 json_content = json.dumps(backup_data, indent=2, default=str)
1478
1479 return StreamingResponse(
1480 io.StringIO(json_content),
1481 media_type="application/json",
1482 headers={"Content-Disposition": f"attachment; filename={resource_name}_backup.json"}
1483 )
1484
1485 except Exception as e:
1486 logger.error(f"❌ Error exporting JSON: {e}")
1487 raise HTTPException(500, f"Error exporting JSON: {e}")
1488
1489 @self.app.post("/api/{resource_name}/import/csv")
1490 async def api_import_csv(
1491 resource_name: str,
1492 file: UploadFile = File(...),
1493 run_hooks: bool = Query(True, description="Execute hooks during import"),
1494 skip_validation: bool = Query(False, description="Skip validation hooks (only when run_hooks=True)")
1495 ):
1496 """Import records from CSV file with optional hook execution"""
1497 if resource_name not in self.resources:
1498 raise HTTPException(404, f"Resource '{resource_name}' not found")
1499
1500 resource = self.resources[resource_name]
1501
1502 try:
1503 # Read CSV content
1504 content = await file.read()
1505 csv_content = content.decode('utf-8')
1506
1507 # Parse CSV
1508 csv_reader = csv.DictReader(io.StringIO(csv_content))
1509
1510 imported_count = 0
1511 skipped_count = 0
1512 errors = []
1513
1514 for row_num, row in enumerate(csv_reader, start=2): # Start from 2 (after header)
1515 try:
1516 # Auto-populate magic fields for import
1517 data = self._populate_magic_fields(resource, dict(row), 'create')
1518
1519 # Remove empty string values and convert to None
1520 for key, value in data.items():
1521 if value == '':
1522 data[key] = None
1523
1524 # Handle ID field - generate if not present or empty
1525 if 'id' not in data or data.get('id') in [None, '']:
1526 # Generate ID like in the regular create endpoint
1527 max_id_result = resource.db.execute(f"SELECT COALESCE(MAX(id), 0) FROM {resource_name}").fetchone()
1528 next_id = (max_id_result[0] if max_id_result else 0) + imported_count + 1
1529 data['id'] = next_id
1530
1531 # Execute before_create hook if enabled
1532 if run_hooks and 'before_create' in resource.hooks:
1533 try:
1534 if not skip_validation or not resource.hooks['before_create'].endswith('_validation'):
1535 await self._execute_hook(resource.hooks['before_create'], data)
1536 except Exception as hook_error:
1537 errors.append(f"Row {row_num}: Hook validation failed - {str(hook_error)}")
1538 skipped_count += 1
1539 continue
1540
1541 # Build parameterized query
1542 fields = list(data.keys())
1543 placeholders = ', '.join(['?' for _ in fields])
1544 field_names = ', '.join(fields)
1545 values = [data[field] for field in fields]
1546
1547 # Insert record
1548 result = resource.db.execute(
1549 f"INSERT INTO {resource_name} ({field_names}) VALUES ({placeholders}) RETURNING *",
1550 values
1551 ).fetchone()
1552
1553 # Execute after_create hook if enabled
1554 if run_hooks and 'after_create' in resource.hooks and result:
1555 try:
1556 columns = [desc[0] for desc in resource.db.description]
1557 record_dict = dict(zip(columns, result))
1558 await self._execute_hook(resource.hooks['after_create'], record_dict)
1559 except Exception as hook_error:
1560 # Log but don't fail - record is already created
1561 logger.warning(f"After-create hook failed for row {row_num}: {hook_error}")
1562
1563 imported_count += 1
1564
1565 except Exception as e:
1566 errors.append(f"Row {row_num}: {str(e)}")
1567 continue
1568
1569 # Broadcast real-time update if any records were imported
1570 if imported_count > 0:
1571 await self._broadcast_update(resource_name)
1572
1573 result = {
1574 "message": f"Import completed",
1575 "imported_records": imported_count,
1576 "skipped_records": skipped_count,
1577 "total_rows": row_num - 1 if 'row_num' in locals() else 0,
1578 "hooks_executed": run_hooks,
1579 "errors": errors[:10] # Limit error messages
1580 }
1581
1582 if errors:
1583 result["error_count"] = len(errors)
1584 if len(errors) > 10:
1585 result["note"] = f"Showing first 10 of {len(errors)} errors"
1586
1587 return result
1588
1589 except Exception as e:
1590 logger.error(f"❌ Error importing CSV: {e}")
1591 raise HTTPException(500, f"Error importing CSV: {e}")
1592
1593 @self.app.post("/api/{resource_name}/import/json")
1594 async def api_import_json(
1595 resource_name: str,
1596 file: UploadFile = File(...),
1597 run_hooks: bool = Query(True, description="Execute hooks during import"),
1598 skip_validation: bool = Query(False, description="Skip validation hooks")
1599 ):
1600 """Import records from JSON backup file with optional hook execution"""
1601 if resource_name not in self.resources:
1602 raise HTTPException(404, f"Resource '{resource_name}' not found")
1603
1604 resource = self.resources[resource_name]
1605
1606 try:
1607 # Read JSON content
1608 content = await file.read()
1609 json_content = content.decode('utf-8')
1610 backup_data = json.loads(json_content)
1611
1612 # Validate backup structure
1613 if 'records' not in backup_data:
1614 raise HTTPException(400, "Invalid backup file: missing 'records' field")
1615
1616 records = backup_data['records']
1617 imported_count = 0
1618 skipped_count = 0
1619 errors = []
1620
1621 for record_num, record in enumerate(records, start=1):
1622 try:
1623 # Auto-populate magic fields for import
1624 data = self._populate_magic_fields(resource, dict(record), 'create')
1625
1626 # Handle ID field - generate if not present or empty
1627 if 'id' not in data or data.get('id') in [None, '']:
1628 # Generate ID like in the regular create endpoint
1629 max_id_result = resource.db.execute(f"SELECT COALESCE(MAX(id), 0) FROM {resource_name}").fetchone()
1630 next_id = (max_id_result[0] if max_id_result else 0) + imported_count + 1
1631 data['id'] = next_id
1632
1633 # Execute before_create hook if enabled
1634 if run_hooks and 'before_create' in resource.hooks:
1635 try:
1636 if not skip_validation or not resource.hooks['before_create'].endswith('_validation'):
1637 await self._execute_hook(resource.hooks['before_create'], data)
1638 except Exception as hook_error:
1639 errors.append(f"Record {record_num}: Hook validation failed - {str(hook_error)}")
1640 skipped_count += 1
1641 continue
1642
1643 # Build parameterized query
1644 fields = list(data.keys())
1645 placeholders = ', '.join(['?' for _ in fields])
1646 field_names = ', '.join(fields)
1647 values = [data[field] for field in fields]
1648
1649 # Insert record
1650 result = resource.db.execute(
1651 f"INSERT INTO {resource_name} ({field_names}) VALUES ({placeholders}) RETURNING *",
1652 values
1653 ).fetchone()
1654
1655 # Execute after_create hook if enabled
1656 if run_hooks and 'after_create' in resource.hooks and result:
1657 try:
1658 columns = [desc[0] for desc in resource.db.description]
1659 record_dict = dict(zip(columns, result))
1660 await self._execute_hook(resource.hooks['after_create'], record_dict)
1661 except Exception as hook_error:
1662 # Log but don't fail - record is already created
1663 logger.warning(f"After-create hook failed for record {record_num}: {hook_error}")
1664
1665 imported_count += 1
1666
1667 except Exception as e:
1668 errors.append(f"Record {record_num}: {str(e)}")
1669 skipped_count += 1
1670 continue
1671
1672 # Broadcast real-time update if any records were imported
1673 if imported_count > 0:
1674 await self._broadcast_update(resource_name)
1675
1676 result = {
1677 "message": f"Import completed",
1678 "imported_records": imported_count,
1679 "skipped_records": skipped_count,
1680 "total_records": len(records),
1681 "hooks_executed": run_hooks,
1682 "backup_metadata": {
1683 "original_resource": backup_data.get('resource'),
1684 "exported_at": backup_data.get('exported_at'),
1685 "original_total": backup_data.get('total_records')
1686 },
1687 "errors": errors[:10] # Limit error messages
1688 }
1689
1690 if errors:
1691 result["error_count"] = len(errors)
1692 if len(errors) > 10:
1693 result["note"] = f"Showing first 10 of {len(errors)} errors"
1694
1695 return result
1696
1697 except Exception as e:
1698 logger.error(f"❌ Error importing JSON: {e}")
1699 raise HTTPException(500, f"Error importing JSON: {e}")
1700
1701 @self.app.websocket("/ws/{resource_name}")
1702 async def websocket_endpoint(websocket: WebSocket, resource_name: str):
1703 """WebSocket endpoint for real-time updates"""
1704 await websocket.accept()
1705
1706 if resource_name not in self.websocket_connections:
1707 self.websocket_connections[resource_name] = []
1708 self.websocket_connections[resource_name].append(websocket)
1709
1710 try:
1711 while True:
1712 data = await websocket.receive_json()
1713 if data.get('type') == 'request_data':
1714 # Send current data
1715 if resource_name in self.resources:
1716 resource = self.resources[resource_name]
1717 try:
1718 result = resource.db.execute(f"SELECT * FROM {resource_name}").fetchall()
1719 columns = [desc[0] for desc in resource.db.description]
1720 records = [dict(zip(columns, row)) for row in result]
1721
1722 await websocket.send_json({
1723 "type": "data_update",
1724 "records": records
1725 })
1726 except Exception as e:
1727 logger.error(f"Error fetching data for WebSocket: {e}")
1728 except WebSocketDisconnect:
1729 if resource_name in self.websocket_connections:
1730 self.websocket_connections[resource_name].remove(websocket)
1731
1732
1733 def _populate_magic_fields(self, resource: 'CRUDResource', data: dict, operation: str, user_id: str = None) -> dict:
1734 """Auto-populate magic fields like timestamps and user tracking"""
1735 now = datetime.now().isoformat()
1736 # For now, use a placeholder user ID since we don't have authentication yet
1737 current_user = user_id or "system"
1738
1739 # Check each field in the resource configuration
1740 # Fields are stored as a dict with field names as keys
1741 for field_name, field_config in resource.fields.items():
1742 # Handle auto_now_add fields (only on create)
1743 if operation == 'create' and field_config.get('auto_now_add'):
1744 if field_config.get('type') == 'timestamp' and field_name not in data:
1745 data[field_name] = now
1746
1747 # Handle auto_now fields (on create and update)
1748 if field_config.get('auto_now'):
1749 if field_config.get('type') == 'timestamp':
1750 data[field_name] = now
1751
1752 # Handle ownership tracking fields
1753 if operation == 'create' and field_config.get('auto_user_add'):
1754 if field_name not in data:
1755 data[field_name] = current_user
1756
1757 if field_config.get('auto_user'):
1758 data[field_name] = current_user
1759
1760 return data
1761
1762 def _check_permission(self, resource: 'CRUDResource', operation: str, user_id: str = None, record: dict = None) -> bool:
1763 """Check if user has permission for the operation based on config"""
1764 # No permissions config means allow all
1765 if 'permissions' not in resource.config:
1766 return True
1767
1768 permissions = resource.permissions
1769 permission_rule = permissions.get(operation, 'public')
1770
1771 # Public permission - anyone can access
1772 if permission_rule == 'public':
1773 return True
1774
1775 # Authenticated permission - must have a user ID
1776 if permission_rule == 'authenticated':
1777 return user_id is not None
1778
1779 # Owner or admin permission
1780 if permission_rule == 'owner_or_admin':
1781 if not record:
1782 return False
1783 # Check if user is owner (look for created_by field)
1784 owner_field = None
1785 for field_name, field_config in resource.fields.items():
1786 if field_config.get('auto_user_add'):
1787 owner_field = field_name
1788 break
1789 if owner_field and record.get(owner_field) == user_id:
1790 return True
1791 # Check if user is admin (simplified - in real app would check user roles)
1792 if user_id and user_id.startswith('admin'):
1793 return True
1794 return False
1795
1796 # Admin only permission
1797 if permission_rule == 'admin_only':
1798 return user_id and user_id.startswith('admin')
1799
1800 # Default deny if unknown permission rule
1801 return False
1802
1803 async def _execute_hook(self, hook_name: str, data: dict):
1804 """Execute a hook by calling the corresponding service"""
1805 try:
1806 # Check if service exists
1807 response = requests.get(f"http://localhost:8003/api/services/{hook_name}")
1808 if response.status_code == 404:
1809 logger.warning(f"🤖 Hook service '{hook_name}' not found, requesting AI generation...")
1810 # TODO: Integrate with AI Service Builder to generate missing hook
1811 return
1812
1813 # Call the hook service
1814 hook_response = requests.post(
1815 f"http://localhost:8003/api/{hook_name}",
1816 json=data,
1817 timeout=30
1818 )
1819
1820 if hook_response.status_code != 200:
1821 logger.error(f"❌ Hook '{hook_name}' failed: {hook_response.text}")
1822 else:
1823 logger.info(f"✅ Hook '{hook_name}' executed successfully")
1824
1825 except Exception as e:
1826 logger.error(f"❌ Error executing hook '{hook_name}': {e}")
1827
1828 async def _broadcast_update(self, resource_name: str):
1829 """Broadcast updates to all WebSocket clients for a resource"""
1830 if resource_name not in self.websocket_connections:
1831 return
1832
1833 if resource_name in self.resources:
1834 resource = self.resources[resource_name]
1835 try:
1836 result = resource.db.execute(f"SELECT * FROM {resource_name}").fetchall()
1837 columns = [desc[0] for desc in resource.db.description]
1838 records = [dict(zip(columns, row)) for row in result]
1839
1840 # Send to all connected clients
1841 for websocket in self.websocket_connections[resource_name][:]:
1842 try:
1843 await websocket.send_json({
1844 "type": "data_update",
1845 "records": records
1846 })
1847 except:
1848 # Remove disconnected clients
1849 self.websocket_connections[resource_name].remove(websocket)
1850 except Exception as e:
1851 logger.error(f"Error broadcasting update: {e}")
1852
1853 def load_all_configs(self):
1854 """Load all CRUD configuration files"""
1855 config_files = list(Path(".").glob("*_crud.yaml"))
1856 for config_file in config_files:
1857 self.load_config(str(config_file))
1858
1859 def load_config(self, config_path: str):
1860 """Load a CRUD configuration file"""
1861 try:
1862 with open(config_path, 'r') as f:
1863 config = yaml.safe_load(f)
1864
1865 resource_name = config['resource']
1866 self.resources[resource_name] = CRUDResource(config_path, config)
1867 logger.info(f"✅ Loaded CRUD config: {resource_name}")
1868
1869 except Exception as e:
1870 logger.error(f"❌ Error loading config {config_path}: {e}")
1871
1872 async def reload_config(self, config_path: str):
1873 """Reload a changed configuration file"""
1874 self.load_config(config_path)
1875
1876 # Broadcast updates to all clients
1877 for resource_name in self.resources.keys():
1878 await self._broadcast_update(resource_name)
1879
1880 def start_watching(self):
1881 """Start watching for config file changes"""
1882 self.config_watcher.start()
1883 logger.info("🔍 Started watching for CRUD config changes...")
1884
1885 def stop_watching(self):
1886 """Stop watching for config file changes"""
1887 self.config_watcher.stop()
1888 self.config_watcher.join()
1889
1890def main():
1891 """Run the CRUD Engine"""
1892 engine = CRUDEngine()
1893 engine.start_watching()
1894
1895 try:
1896 logger.info("🚀 Starting DBBasic CRUD Engine on http://localhost:8005")
1897 uvicorn.run(engine.app, host="0.0.0.0", port=8005, log_level="info")
1898 except KeyboardInterrupt:
1899 logger.info("🛑 Shutting down CRUD Engine...")
1900 finally:
1901 engine.stop_watching()
1902
1903if __name__ == "__main__":
1904 main()