An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.
init_postblob.sql · 15.6 KB · 532 lines
| 1 | -- PostBlob Database: PostgreSQL does everything! |
| 2 | -- Create database: createdb postblob |
| 3 | -- Initialize: psql postblob < init_postblob.sql |
| 4 | |
| 5 | -- Enable PL/Python extension |
| 6 | CREATE EXTENSION IF NOT EXISTS plpython3u; |
| 7 | CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; |
| 8 | |
| 9 | -- ============================================= |
| 10 | -- CORE TABLES |
| 11 | -- ============================================= |
| 12 | |
| 13 | -- File/blob storage |
| 14 | CREATE TABLE IF NOT EXISTS blobs ( |
| 15 | id uuid DEFAULT uuid_generate_v4() PRIMARY KEY, |
| 16 | filename text NOT NULL, |
| 17 | mimetype text NOT NULL, |
| 18 | data bytea NOT NULL, |
| 19 | size bigint NOT NULL, |
| 20 | metadata jsonb DEFAULT '{}'::jsonb, |
| 21 | created_at timestamptz DEFAULT now(), |
| 22 | updated_at timestamptz DEFAULT now() |
| 23 | ); |
| 24 | |
| 25 | -- Job queue |
| 26 | CREATE TABLE IF NOT EXISTS jobs ( |
| 27 | id serial PRIMARY KEY, |
| 28 | type text NOT NULL, |
| 29 | payload jsonb DEFAULT '{}'::jsonb, |
| 30 | status text DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')), |
| 31 | result jsonb, |
| 32 | attempts integer DEFAULT 0, |
| 33 | created_at timestamptz DEFAULT now(), |
| 34 | started_at timestamptz, |
| 35 | completed_at timestamptz |
| 36 | ); |
| 37 | |
| 38 | -- Exception storage |
| 39 | CREATE TABLE IF NOT EXISTS exceptions ( |
| 40 | id serial PRIMARY KEY, |
| 41 | error_type text NOT NULL, |
| 42 | message text NOT NULL, |
| 43 | traceback text, |
| 44 | context jsonb DEFAULT '{}'::jsonb, |
| 45 | function_name text, |
| 46 | occurred_at timestamptz DEFAULT now(), |
| 47 | resolved boolean DEFAULT false |
| 48 | ); |
| 49 | |
| 50 | -- Sessions |
| 51 | CREATE TABLE IF NOT EXISTS sessions ( |
| 52 | id uuid DEFAULT uuid_generate_v4() PRIMARY KEY, |
| 53 | data jsonb DEFAULT '{}'::jsonb, |
| 54 | created_at timestamptz DEFAULT now(), |
| 55 | expires_at timestamptz DEFAULT (now() + interval '24 hours'), |
| 56 | last_access timestamptz DEFAULT now() |
| 57 | ); |
| 58 | |
| 59 | -- Key-value cache |
| 60 | CREATE TABLE IF NOT EXISTS cache ( |
| 61 | key text PRIMARY KEY, |
| 62 | value jsonb NOT NULL, |
| 63 | expires_at timestamptz, |
| 64 | created_at timestamptz DEFAULT now() |
| 65 | ); |
| 66 | |
| 67 | -- Circuit breakers |
| 68 | CREATE TABLE IF NOT EXISTS circuit_breakers ( |
| 69 | function_name text PRIMARY KEY, |
| 70 | state text DEFAULT 'closed' CHECK (state IN ('closed', 'open', 'half_open')), |
| 71 | failure_count integer DEFAULT 0, |
| 72 | last_failure timestamptz, |
| 73 | last_success timestamptz |
| 74 | ); |
| 75 | |
| 76 | -- ============================================= |
| 77 | -- MAIN API HANDLER |
| 78 | -- ============================================= |
| 79 | |
| 80 | CREATE OR REPLACE FUNCTION handle_request( |
| 81 | method text, |
| 82 | path text, |
| 83 | headers jsonb DEFAULT '{}'::jsonb, |
| 84 | body jsonb DEFAULT '{}'::jsonb, |
| 85 | session_id uuid DEFAULT NULL |
| 86 | ) RETURNS jsonb AS $$ |
| 87 | import json |
| 88 | import re |
| 89 | import base64 |
| 90 | import hashlib |
| 91 | import datetime |
| 92 | import mimetypes |
| 93 | |
| 94 | try: |
| 95 | # Session management |
| 96 | session = {} |
| 97 | if session_id: |
| 98 | result = plpy.execute( |
| 99 | "SELECT data FROM sessions WHERE id = %s AND expires_at > now()", |
| 100 | [str(session_id)] |
| 101 | ) |
| 102 | if result: |
| 103 | session = result[0]['data'] |
| 104 | # Update last access |
| 105 | plpy.execute( |
| 106 | "UPDATE sessions SET last_access = now() WHERE id = %s", |
| 107 | [str(session_id)] |
| 108 | ) |
| 109 | |
| 110 | # Route handling |
| 111 | response = {'status': 404, 'body': {'error': 'Not found'}} |
| 112 | |
| 113 | # GET routes |
| 114 | if method == 'GET': |
| 115 | if path == '/': |
| 116 | response = { |
| 117 | 'status': 200, |
| 118 | 'body': { |
| 119 | 'message': 'Welcome to PostBlob!', |
| 120 | 'endpoints': ['/blobs', '/jobs', '/stats'] |
| 121 | } |
| 122 | } |
| 123 | |
| 124 | elif path == '/stats': |
| 125 | stats = plpy.execute(""" |
| 126 | SELECT |
| 127 | (SELECT COUNT(*) FROM blobs) as blob_count, |
| 128 | (SELECT SUM(size) FROM blobs) as total_size, |
| 129 | (SELECT COUNT(*) FROM jobs WHERE status = 'completed') as jobs_completed, |
| 130 | (SELECT COUNT(*) FROM exceptions) as total_exceptions |
| 131 | """)[0] |
| 132 | response = {'status': 200, 'body': dict(stats)} |
| 133 | |
| 134 | elif path.startswith('/blob/'): |
| 135 | blob_id = path.split('/')[-1] |
| 136 | result = plpy.execute( |
| 137 | "SELECT * FROM blobs WHERE id = %s", |
| 138 | [blob_id] |
| 139 | ) |
| 140 | if result: |
| 141 | blob = result[0] |
| 142 | response = { |
| 143 | 'status': 200, |
| 144 | 'headers': { |
| 145 | 'Content-Type': blob['mimetype'], |
| 146 | 'Content-Disposition': f'inline; filename="{blob["filename"]}"' |
| 147 | }, |
| 148 | 'body': base64.b64encode(bytes(blob['data'])).decode('utf-8'), |
| 149 | 'binary': True |
| 150 | } |
| 151 | else: |
| 152 | response = {'status': 404, 'body': {'error': 'Blob not found'}} |
| 153 | |
| 154 | elif path == '/jobs': |
| 155 | jobs = plpy.execute( |
| 156 | "SELECT id, type, status, created_at FROM jobs ORDER BY created_at DESC LIMIT 100" |
| 157 | ) |
| 158 | response = {'status': 200, 'body': [dict(j) for j in jobs]} |
| 159 | |
| 160 | # POST routes |
| 161 | elif method == 'POST': |
| 162 | if path == '/blob': |
| 163 | # Upload blob |
| 164 | filename = body.get('filename', 'unnamed') |
| 165 | data = base64.b64decode(body.get('data', '')) |
| 166 | mimetype = body.get('mimetype', mimetypes.guess_type(filename)[0] or 'application/octet-stream') |
| 167 | metadata = body.get('metadata', {}) |
| 168 | |
| 169 | result = plpy.execute(""" |
| 170 | INSERT INTO blobs (filename, mimetype, data, size, metadata) |
| 171 | VALUES (%s, %s, %s, %s, %s) |
| 172 | RETURNING id, filename, size, created_at |
| 173 | """, [filename, mimetype, data, len(data), json.dumps(metadata)]) |
| 174 | |
| 175 | response = { |
| 176 | 'status': 201, |
| 177 | 'body': { |
| 178 | 'id': str(result[0]['id']), |
| 179 | 'filename': result[0]['filename'], |
| 180 | 'size': result[0]['size'], |
| 181 | 'url': f"/blob/{result[0]['id']}" |
| 182 | } |
| 183 | } |
| 184 | |
| 185 | elif path == '/job': |
| 186 | # Queue job |
| 187 | job_type = body.get('type', 'default') |
| 188 | payload = body.get('payload', {}) |
| 189 | |
| 190 | result = plpy.execute(""" |
| 191 | INSERT INTO jobs (type, payload) |
| 192 | VALUES (%s, %s) |
| 193 | RETURNING id, type, status, created_at |
| 194 | """, [job_type, json.dumps(payload)]) |
| 195 | |
| 196 | # Notify job worker |
| 197 | plpy.execute("NOTIFY new_job, %s", [str(result[0]['id'])]) |
| 198 | |
| 199 | response = { |
| 200 | 'status': 201, |
| 201 | 'body': { |
| 202 | 'job_id': result[0]['id'], |
| 203 | 'type': result[0]['type'], |
| 204 | 'status': result[0]['status'] |
| 205 | } |
| 206 | } |
| 207 | |
| 208 | elif path == '/execute': |
| 209 | # Execute arbitrary Python code (for development) |
| 210 | code = body.get('code', '') |
| 211 | context = body.get('context', {}) |
| 212 | |
| 213 | result = plpy.execute( |
| 214 | "SELECT safe_execute(%s, %s) as result", |
| 215 | [code, json.dumps(context)] |
| 216 | ) |
| 217 | response = {'status': 200, 'body': result[0]['result']} |
| 218 | |
| 219 | # DELETE routes |
| 220 | elif method == 'DELETE': |
| 221 | if path.startswith('/blob/'): |
| 222 | blob_id = path.split('/')[-1] |
| 223 | result = plpy.execute( |
| 224 | "DELETE FROM blobs WHERE id = %s RETURNING id", |
| 225 | [blob_id] |
| 226 | ) |
| 227 | if result: |
| 228 | response = {'status': 200, 'body': {'deleted': str(result[0]['id'])}} |
| 229 | else: |
| 230 | response = {'status': 404, 'body': {'error': 'Blob not found'}} |
| 231 | |
| 232 | return json.dumps(response) |
| 233 | |
| 234 | except Exception as e: |
| 235 | # Log exception |
| 236 | import traceback |
| 237 | plpy.execute(""" |
| 238 | INSERT INTO exceptions (error_type, message, traceback, context, function_name) |
| 239 | VALUES (%s, %s, %s, %s, 'handle_request') |
| 240 | """, [ |
| 241 | type(e).__name__, |
| 242 | str(e), |
| 243 | traceback.format_exc(), |
| 244 | json.dumps({'method': method, 'path': path}) |
| 245 | ]) |
| 246 | |
| 247 | return json.dumps({ |
| 248 | 'status': 500, |
| 249 | 'body': {'error': str(e), 'type': type(e).__name__} |
| 250 | }) |
| 251 | $$ LANGUAGE plpython3u; |
| 252 | |
| 253 | -- ============================================= |
| 254 | -- JOB PROCESSOR |
| 255 | -- ============================================= |
| 256 | |
| 257 | CREATE OR REPLACE FUNCTION process_job(job_id integer) RETURNS void AS $$ |
| 258 | import json |
| 259 | import time |
| 260 | |
| 261 | try: |
| 262 | # Get job details |
| 263 | job = plpy.execute( |
| 264 | "SELECT * FROM jobs WHERE id = %s FOR UPDATE", |
| 265 | [job_id] |
| 266 | )[0] |
| 267 | |
| 268 | if job['status'] != 'pending': |
| 269 | return |
| 270 | |
| 271 | # Mark as processing |
| 272 | plpy.execute( |
| 273 | "UPDATE jobs SET status = 'processing', started_at = now() WHERE id = %s", |
| 274 | [job_id] |
| 275 | ) |
| 276 | |
| 277 | result = {} |
| 278 | |
| 279 | # Process different job types |
| 280 | if job['type'] == 'resize_image': |
| 281 | # Simulated image processing |
| 282 | blob_id = job['payload'].get('blob_id') |
| 283 | width = job['payload'].get('width', 800) |
| 284 | height = job['payload'].get('height', 600) |
| 285 | |
| 286 | # In real implementation, would use PIL or similar |
| 287 | result = { |
| 288 | 'status': 'success', |
| 289 | 'message': f'Resized to {width}x{height}', |
| 290 | 'blob_id': blob_id |
| 291 | } |
| 292 | |
| 293 | elif job['type'] == 'analyze_text': |
| 294 | text = job['payload'].get('text', '') |
| 295 | result = { |
| 296 | 'word_count': len(text.split()), |
| 297 | 'char_count': len(text), |
| 298 | 'lines': len(text.split('\n')) |
| 299 | } |
| 300 | |
| 301 | elif job['type'] == 'cleanup': |
| 302 | # Clean old sessions and cache |
| 303 | deleted_sessions = plpy.execute( |
| 304 | "DELETE FROM sessions WHERE expires_at < now() RETURNING id" |
| 305 | ) |
| 306 | deleted_cache = plpy.execute( |
| 307 | "DELETE FROM cache WHERE expires_at < now() RETURNING key" |
| 308 | ) |
| 309 | result = { |
| 310 | 'sessions_cleaned': len(deleted_sessions), |
| 311 | 'cache_cleaned': len(deleted_cache) |
| 312 | } |
| 313 | |
| 314 | elif job['type'] == 'compute': |
| 315 | # Generic computation |
| 316 | time.sleep(2) # Simulate work |
| 317 | value = job['payload'].get('value', 0) |
| 318 | operation = job['payload'].get('operation', 'double') |
| 319 | |
| 320 | if operation == 'double': |
| 321 | result = {'result': value * 2} |
| 322 | elif operation == 'square': |
| 323 | result = {'result': value ** 2} |
| 324 | else: |
| 325 | result = {'result': value} |
| 326 | |
| 327 | else: |
| 328 | # Unknown job type |
| 329 | result = {'error': f"Unknown job type: {job['type']}"} |
| 330 | |
| 331 | # Mark as completed |
| 332 | plpy.execute(""" |
| 333 | UPDATE jobs |
| 334 | SET status = 'completed', |
| 335 | result = %s, |
| 336 | completed_at = now() |
| 337 | WHERE id = %s |
| 338 | """, [json.dumps(result), job_id]) |
| 339 | |
| 340 | except Exception as e: |
| 341 | import traceback |
| 342 | # Mark as failed and log exception |
| 343 | plpy.execute(""" |
| 344 | UPDATE jobs |
| 345 | SET status = 'failed', |
| 346 | result = %s, |
| 347 | attempts = attempts + 1 |
| 348 | WHERE id = %s |
| 349 | """, [json.dumps({'error': str(e), 'traceback': traceback.format_exc()}), job_id]) |
| 350 | |
| 351 | # Log to exceptions table |
| 352 | plpy.execute(""" |
| 353 | INSERT INTO exceptions (error_type, message, traceback, context, function_name) |
| 354 | VALUES (%s, %s, %s, %s, 'process_job') |
| 355 | """, [ |
| 356 | type(e).__name__, |
| 357 | str(e), |
| 358 | traceback.format_exc(), |
| 359 | json.dumps({'job_id': job_id, 'job_type': job.get('type')}) |
| 360 | ]) |
| 361 | $$ LANGUAGE plpython3u; |
| 362 | |
| 363 | -- ============================================= |
| 364 | -- SAFE EXECUTION WRAPPER |
| 365 | -- ============================================= |
| 366 | |
| 367 | CREATE OR REPLACE FUNCTION safe_execute(code text, context jsonb DEFAULT '{}'::jsonb) |
| 368 | RETURNS jsonb AS $$ |
| 369 | import json |
| 370 | import traceback |
| 371 | import sys |
| 372 | |
| 373 | namespace = { |
| 374 | 'context': context, |
| 375 | 'plpy': plpy, |
| 376 | 'json': json, |
| 377 | 'result': None |
| 378 | } |
| 379 | |
| 380 | try: |
| 381 | exec(code, namespace) |
| 382 | return json.dumps({ |
| 383 | 'success': True, |
| 384 | 'result': namespace.get('result'), |
| 385 | 'output': namespace.get('output') |
| 386 | }) |
| 387 | |
| 388 | except Exception as e: |
| 389 | exc_type, exc_value, exc_tb = sys.exc_info() |
| 390 | |
| 391 | # Log exception |
| 392 | plpy.execute(""" |
| 393 | INSERT INTO exceptions (error_type, message, traceback, context, function_name) |
| 394 | VALUES (%s, %s, %s, %s, 'safe_execute') |
| 395 | """, [ |
| 396 | exc_type.__name__, |
| 397 | str(exc_value), |
| 398 | traceback.format_exc(), |
| 399 | json.dumps({'code_preview': code[:500], 'context': context}) |
| 400 | ]) |
| 401 | |
| 402 | return json.dumps({ |
| 403 | 'success': False, |
| 404 | 'error': str(e), |
| 405 | 'type': exc_type.__name__, |
| 406 | 'traceback': traceback.format_exc() |
| 407 | }) |
| 408 | $$ LANGUAGE plpython3u; |
| 409 | |
| 410 | -- ============================================= |
| 411 | -- CACHE FUNCTIONS |
| 412 | -- ============================================= |
| 413 | |
| 414 | CREATE OR REPLACE FUNCTION cache_get(cache_key text) |
| 415 | RETURNS jsonb AS $$ |
| 416 | BEGIN |
| 417 | DELETE FROM cache WHERE expires_at < now(); |
| 418 | RETURN (SELECT value FROM cache WHERE key = cache_key); |
| 419 | END; |
| 420 | $$ LANGUAGE plpgsql; |
| 421 | |
| 422 | CREATE OR REPLACE FUNCTION cache_set( |
| 423 | cache_key text, |
| 424 | cache_value jsonb, |
| 425 | ttl_seconds integer DEFAULT 3600 |
| 426 | ) RETURNS void AS $$ |
| 427 | BEGIN |
| 428 | INSERT INTO cache (key, value, expires_at) |
| 429 | VALUES (cache_key, cache_value, now() + (ttl_seconds || ' seconds')::interval) |
| 430 | ON CONFLICT (key) DO UPDATE |
| 431 | SET value = EXCLUDED.value, |
| 432 | expires_at = EXCLUDED.expires_at, |
| 433 | created_at = now(); |
| 434 | END; |
| 435 | $$ LANGUAGE plpgsql; |
| 436 | |
| 437 | -- ============================================= |
| 438 | -- BACKGROUND WORKER |
| 439 | -- ============================================= |
| 440 | |
| 441 | CREATE OR REPLACE FUNCTION job_worker_loop() RETURNS void AS $$ |
| 442 | import time |
| 443 | import json |
| 444 | |
| 445 | max_runs = 1000 # Prevent infinite loops in development |
| 446 | runs = 0 |
| 447 | |
| 448 | while runs < max_runs: |
| 449 | runs += 1 |
| 450 | |
| 451 | try: |
| 452 | # Get next pending job |
| 453 | jobs = plpy.execute(""" |
| 454 | SELECT id FROM jobs |
| 455 | WHERE status = 'pending' |
| 456 | ORDER BY created_at |
| 457 | LIMIT 1 |
| 458 | FOR UPDATE SKIP LOCKED |
| 459 | """) |
| 460 | |
| 461 | if jobs: |
| 462 | job_id = jobs[0]['id'] |
| 463 | plpy.execute("SELECT process_job(%s)", [job_id]) |
| 464 | plpy.notice(f"Processed job {job_id}") |
| 465 | else: |
| 466 | # No jobs, wait a bit |
| 467 | time.sleep(1) |
| 468 | |
| 469 | except Exception as e: |
| 470 | plpy.warning(f"Worker error: {e}") |
| 471 | time.sleep(5) |
| 472 | |
| 473 | plpy.notice(f"Worker stopped after {runs} runs") |
| 474 | $$ LANGUAGE plpython3u; |
| 475 | |
| 476 | -- ============================================= |
| 477 | -- INDEXES |
| 478 | -- ============================================= |
| 479 | |
| 480 | CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status, created_at); |
| 481 | CREATE INDEX IF NOT EXISTS idx_blobs_created ON blobs(created_at DESC); |
| 482 | CREATE INDEX IF NOT EXISTS idx_exceptions_occurred ON exceptions(occurred_at DESC); |
| 483 | CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at); |
| 484 | CREATE INDEX IF NOT EXISTS idx_cache_expires ON cache(expires_at); |
| 485 | |
| 486 | -- ============================================= |
| 487 | -- NOTIFICATIONS |
| 488 | -- ============================================= |
| 489 | |
| 490 | -- Job notification trigger |
| 491 | CREATE OR REPLACE FUNCTION notify_new_job() RETURNS trigger AS $$ |
| 492 | BEGIN |
| 493 | PERFORM pg_notify('new_job', NEW.id::text); |
| 494 | RETURN NEW; |
| 495 | END; |
| 496 | $$ LANGUAGE plpgsql; |
| 497 | |
| 498 | DROP TRIGGER IF EXISTS job_created ON jobs; |
| 499 | CREATE TRIGGER job_created |
| 500 | AFTER INSERT ON jobs |
| 501 | FOR EACH ROW |
| 502 | EXECUTE FUNCTION notify_new_job(); |
| 503 | |
| 504 | -- ============================================= |
| 505 | -- INITIAL DATA |
| 506 | -- ============================================= |
| 507 | |
| 508 | -- Create a welcome blob |
| 509 | INSERT INTO blobs (filename, mimetype, data, size, metadata) |
| 510 | VALUES ( |
| 511 | 'welcome.txt', |
| 512 | 'text/plain', |
| 513 | 'Welcome to PostBlob - PostgreSQL does everything!'::bytea, |
| 514 | length('Welcome to PostBlob - PostgreSQL does everything!'), |
| 515 | '{"type": "system", "description": "Welcome message"}'::jsonb |
| 516 | ) ON CONFLICT DO NOTHING; |
| 517 | |
| 518 | -- Create sample job |
| 519 | INSERT INTO jobs (type, payload, status) |
| 520 | VALUES ( |
| 521 | 'cleanup', |
| 522 | '{"description": "Initial cleanup job"}'::jsonb, |
| 523 | 'pending' |
| 524 | ) ON CONFLICT DO NOTHING; |
| 525 | |
| 526 | -- Success message |
| 527 | DO $$ |
| 528 | BEGIN |
| 529 | RAISE NOTICE 'PostBlob database initialized successfully!'; |
| 530 | RAISE NOTICE 'Tables created: blobs, jobs, exceptions, sessions, cache, circuit_breakers'; |
| 531 | RAISE NOTICE 'Main functions: handle_request(), process_job(), safe_execute()'; |
| 532 | END $$; |