An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.
postblob_backup.sql · 105.9 KB · 2355 lines
| 1 | -- |
| 2 | -- PostgreSQL database dump |
| 3 | -- |
| 4 | |
| 5 | \restrict ZHowxS4FzILvodLbuq1kLjCcpmcpSQ5reyQdJ8KCxiHbPVSQVdN99Uni14IBD26 |
| 6 | |
| 7 | -- Dumped from database version 14.19 (Homebrew) |
| 8 | -- Dumped by pg_dump version 14.19 (Homebrew) |
| 9 | |
| 10 | SET statement_timeout = 0; |
| 11 | SET lock_timeout = 0; |
| 12 | SET idle_in_transaction_session_timeout = 0; |
| 13 | SET client_encoding = 'UTF8'; |
| 14 | SET standard_conforming_strings = on; |
| 15 | SELECT pg_catalog.set_config('search_path', '', false); |
| 16 | SET check_function_bodies = false; |
| 17 | SET xmloption = content; |
| 18 | SET client_min_messages = warning; |
| 19 | SET row_security = off; |
| 20 | |
| 21 | -- |
| 22 | -- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: - |
| 23 | -- |
| 24 | |
| 25 | CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public; |
| 26 | |
| 27 | |
| 28 | -- |
| 29 | -- Name: EXTENSION pgcrypto; Type: COMMENT; Schema: -; Owner: |
| 30 | -- |
| 31 | |
| 32 | COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions'; |
| 33 | |
| 34 | |
| 35 | -- |
| 36 | -- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: - |
| 37 | -- |
| 38 | |
| 39 | CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public; |
| 40 | |
| 41 | |
| 42 | -- |
| 43 | -- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: |
| 44 | -- |
| 45 | |
| 46 | COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)'; |
| 47 | |
| 48 | |
| 49 | -- |
| 50 | -- Name: ask(text); Type: FUNCTION; Schema: public; Owner: danq |
| 51 | -- |
| 52 | |
| 53 | CREATE FUNCTION public.ask(question text) RETURNS jsonb |
| 54 | LANGUAGE plpgsql |
| 55 | AS $_$ |
| 56 | DECLARE |
| 57 | sql_query text; |
| 58 | result jsonb; |
| 59 | word_count integer; |
| 60 | BEGIN |
| 61 | -- Normalize input |
| 62 | question := lower(trim(question)); |
| 63 | |
| 64 | -- Simple pattern matching for common queries |
| 65 | sql_query := CASE |
| 66 | -- "show blobs" or "list blobs" |
| 67 | WHEN question ~ '^(show|list)\s+blobs?$' THEN |
| 68 | 'SELECT * FROM blobs ORDER BY created_at DESC LIMIT 10' |
| 69 | |
| 70 | -- "count blobs" |
| 71 | WHEN question ~ '^count\s+blobs?$' THEN |
| 72 | 'SELECT COUNT(*) as count FROM blobs' |
| 73 | |
| 74 | -- "show jobs" or "list jobs" |
| 75 | WHEN question ~ '^(show|list)\s+jobs?$' THEN |
| 76 | 'SELECT * FROM jobs ORDER BY created_at DESC LIMIT 10' |
| 77 | |
| 78 | -- "pending jobs" |
| 79 | WHEN question ~ '^pending\s+jobs?$' THEN |
| 80 | 'SELECT * FROM jobs WHERE status = ''pending''' |
| 81 | |
| 82 | -- "failed jobs" |
| 83 | WHEN question ~ '^failed\s+jobs?$' THEN |
| 84 | 'SELECT * FROM jobs WHERE status = ''failed''' |
| 85 | |
| 86 | -- "completed jobs" |
| 87 | WHEN question ~ '^completed\s+jobs?$' THEN |
| 88 | 'SELECT * FROM jobs WHERE status = ''completed''' |
| 89 | |
| 90 | -- "show stats" or "statistics" |
| 91 | WHEN question ~ '^(show\s+)?stats|statistics$' THEN |
| 92 | 'SELECT |
| 93 | (SELECT COUNT(*) FROM blobs) as blob_count, |
| 94 | (SELECT COUNT(*) FROM jobs) as job_count, |
| 95 | (SELECT COUNT(*) FROM sessions WHERE expires_at > now()) as active_sessions' |
| 96 | |
| 97 | -- "biggest blob" or "largest file" |
| 98 | WHEN question ~ '^(biggest|largest)\s+(blob|file)$' THEN |
| 99 | 'SELECT * FROM blobs ORDER BY size DESC LIMIT 1' |
| 100 | |
| 101 | -- "newest blob" or "latest file" |
| 102 | WHEN question ~ '^(newest|latest)\s+(blob|file)$' THEN |
| 103 | 'SELECT * FROM blobs ORDER BY created_at DESC LIMIT 1' |
| 104 | |
| 105 | -- "show 5 blobs" (with number) |
| 106 | WHEN question ~ '^show\s+(\d+)\s+blobs?$' THEN |
| 107 | format('SELECT * FROM blobs ORDER BY created_at DESC LIMIT %s', |
| 108 | (regexp_match(question, 'show\s+(\d+)', 'i'))[1]) |
| 109 | |
| 110 | ELSE |
| 111 | NULL |
| 112 | END; |
| 113 | |
| 114 | IF sql_query IS NULL THEN |
| 115 | RETURN jsonb_build_object( |
| 116 | 'error', 'Could not understand query', |
| 117 | 'original', question, |
| 118 | 'hint', 'Try: "show blobs", "count jobs", "failed jobs", "show stats"' |
| 119 | ); |
| 120 | END IF; |
| 121 | |
| 122 | -- Execute the query |
| 123 | BEGIN |
| 124 | EXECUTE 'SELECT jsonb_agg(row_to_json(t)) FROM (' || sql_query || ') t' INTO result; |
| 125 | |
| 126 | IF result IS NULL THEN |
| 127 | result := '[]'::jsonb; |
| 128 | END IF; |
| 129 | |
| 130 | RETURN jsonb_build_object( |
| 131 | 'success', true, |
| 132 | 'sql', sql_query, |
| 133 | 'data', result |
| 134 | ); |
| 135 | EXCEPTION WHEN OTHERS THEN |
| 136 | RETURN jsonb_build_object( |
| 137 | 'error', SQLERRM, |
| 138 | 'sql', sql_query, |
| 139 | 'original', question |
| 140 | ); |
| 141 | END; |
| 142 | END; |
| 143 | $_$; |
| 144 | |
| 145 | |
| 146 | ALTER FUNCTION public.ask(question text) OWNER TO danq; |
| 147 | |
| 148 | -- |
| 149 | -- Name: base64_decode(text); Type: FUNCTION; Schema: public; Owner: danq |
| 150 | -- |
| 151 | |
| 152 | CREATE FUNCTION public.base64_decode(input text) RETURNS text |
| 153 | LANGUAGE plpgsql IMMUTABLE |
| 154 | AS $$ |
| 155 | BEGIN |
| 156 | RETURN convert_from(decode(input, 'base64'), 'UTF8'); |
| 157 | END; |
| 158 | $$; |
| 159 | |
| 160 | |
| 161 | ALTER FUNCTION public.base64_decode(input text) OWNER TO danq; |
| 162 | |
| 163 | -- |
| 164 | -- Name: base64_encode(text); Type: FUNCTION; Schema: public; Owner: danq |
| 165 | -- |
| 166 | |
| 167 | CREATE FUNCTION public.base64_encode(input text) RETURNS text |
| 168 | LANGUAGE plpgsql IMMUTABLE |
| 169 | AS $$ |
| 170 | BEGIN |
| 171 | RETURN encode(input::bytea, 'base64'); |
| 172 | END; |
| 173 | $$; |
| 174 | |
| 175 | |
| 176 | ALTER FUNCTION public.base64_encode(input text) OWNER TO danq; |
| 177 | |
| 178 | -- |
| 179 | -- Name: build_query_string(jsonb); Type: FUNCTION; Schema: public; Owner: danq |
| 180 | -- |
| 181 | |
| 182 | CREATE FUNCTION public.build_query_string(params jsonb) RETURNS text |
| 183 | LANGUAGE plpgsql IMMUTABLE |
| 184 | AS $$ |
| 185 | DECLARE |
| 186 | result text := ''; |
| 187 | key text; |
| 188 | value text; |
| 189 | first boolean := true; |
| 190 | BEGIN |
| 191 | IF params IS NULL THEN |
| 192 | RETURN ''; |
| 193 | END IF; |
| 194 | |
| 195 | FOR key, value IN SELECT * FROM jsonb_each_text(params) LOOP |
| 196 | IF NOT first THEN |
| 197 | result := result || '&'; |
| 198 | END IF; |
| 199 | result := result || url_encode(key) || '=' || url_encode(value); |
| 200 | first := false; |
| 201 | END LOOP; |
| 202 | |
| 203 | RETURN result; |
| 204 | END; |
| 205 | $$; |
| 206 | |
| 207 | |
| 208 | ALTER FUNCTION public.build_query_string(params jsonb) OWNER TO danq; |
| 209 | |
| 210 | -- |
| 211 | -- Name: cache_get(text); Type: FUNCTION; Schema: public; Owner: danq |
| 212 | -- |
| 213 | |
| 214 | CREATE FUNCTION public.cache_get(p_key text) RETURNS jsonb |
| 215 | LANGUAGE plpgsql |
| 216 | AS $$ |
| 217 | BEGIN |
| 218 | -- Clean expired entries |
| 219 | DELETE FROM cache WHERE expires_at < now(); |
| 220 | |
| 221 | RETURN (SELECT value FROM cache WHERE key = p_key); |
| 222 | END; |
| 223 | $$; |
| 224 | |
| 225 | |
| 226 | ALTER FUNCTION public.cache_get(p_key text) OWNER TO danq; |
| 227 | |
| 228 | -- |
| 229 | -- Name: cache_set(text, jsonb, integer); Type: FUNCTION; Schema: public; Owner: danq |
| 230 | -- |
| 231 | |
| 232 | CREATE FUNCTION public.cache_set(p_key text, p_value jsonb, p_ttl_seconds integer DEFAULT 3600) RETURNS void |
| 233 | LANGUAGE plpgsql |
| 234 | AS $$ |
| 235 | BEGIN |
| 236 | INSERT INTO cache (key, value, expires_at) |
| 237 | VALUES (p_key, p_value, now() + (p_ttl_seconds || ' seconds')::interval) |
| 238 | ON CONFLICT (key) DO UPDATE |
| 239 | SET value = EXCLUDED.value, |
| 240 | expires_at = EXCLUDED.expires_at, |
| 241 | created_at = now(); |
| 242 | END; |
| 243 | $$; |
| 244 | |
| 245 | |
| 246 | ALTER FUNCTION public.cache_set(p_key text, p_value jsonb, p_ttl_seconds integer) OWNER TO danq; |
| 247 | |
| 248 | -- |
| 249 | -- Name: code_crud(text, text, text, jsonb); Type: FUNCTION; Schema: public; Owner: danq |
| 250 | -- |
| 251 | |
| 252 | CREATE FUNCTION public.code_crud(p_method text, p_path text, p_content text DEFAULT NULL::text, p_metadata jsonb DEFAULT NULL::jsonb) RETURNS jsonb |
| 253 | LANGUAGE plpgsql |
| 254 | AS $$ |
| 255 | DECLARE |
| 256 | v_result jsonb; |
| 257 | v_file_id integer; |
| 258 | v_old_content text; |
| 259 | v_version integer; |
| 260 | BEGIN |
| 261 | -- Debug log |
| 262 | RAISE NOTICE 'code_crud called with method=%, path=%', p_method, p_path; |
| 263 | |
| 264 | CASE p_method |
| 265 | WHEN 'GET' THEN |
| 266 | -- Get code file |
| 267 | SELECT jsonb_build_object( |
| 268 | 'id', id, |
| 269 | 'path', path, |
| 270 | 'content', content, |
| 271 | 'language', language, |
| 272 | 'version', version, |
| 273 | 'metadata', metadata, |
| 274 | 'updated_at', updated_at |
| 275 | ) INTO v_result |
| 276 | FROM code_files |
| 277 | WHERE path = p_path; |
| 278 | |
| 279 | IF v_result IS NULL THEN |
| 280 | RAISE NOTICE 'File not found: %', p_path; |
| 281 | v_result := jsonb_build_object('error', 'File not found', 'requested_path', p_path); |
| 282 | END IF; |
| 283 | |
| 284 | WHEN 'LIST' THEN |
| 285 | -- List all code files |
| 286 | SELECT jsonb_agg( |
| 287 | jsonb_build_object( |
| 288 | 'path', path, |
| 289 | 'language', language, |
| 290 | 'version', version, |
| 291 | 'size', length(content), |
| 292 | 'updated_at', updated_at |
| 293 | ) ORDER BY path |
| 294 | ) INTO v_result |
| 295 | FROM code_files |
| 296 | WHERE is_active = true; |
| 297 | |
| 298 | WHEN 'PUT', 'POST' THEN |
| 299 | -- Update or create code file |
| 300 | SELECT id, content, version |
| 301 | INTO v_file_id, v_old_content, v_version |
| 302 | FROM code_files |
| 303 | WHERE path = p_path; |
| 304 | |
| 305 | IF v_file_id IS NOT NULL THEN |
| 306 | -- Save to history |
| 307 | INSERT INTO code_history (file_id, content, version) |
| 308 | VALUES (v_file_id, v_old_content, v_version); |
| 309 | |
| 310 | -- Update file |
| 311 | UPDATE code_files |
| 312 | SET content = p_content, |
| 313 | version = version + 1, |
| 314 | metadata = COALESCE(p_metadata, metadata), |
| 315 | updated_at = now() |
| 316 | WHERE id = v_file_id |
| 317 | RETURNING jsonb_build_object( |
| 318 | 'id', id, |
| 319 | 'path', path, |
| 320 | 'version', version, |
| 321 | 'updated', true |
| 322 | ) INTO v_result; |
| 323 | ELSE |
| 324 | -- Create new file |
| 325 | INSERT INTO code_files (path, content, metadata) |
| 326 | VALUES (p_path, p_content, COALESCE(p_metadata, '{}'::jsonb)) |
| 327 | RETURNING jsonb_build_object( |
| 328 | 'id', id, |
| 329 | 'path', path, |
| 330 | 'version', version, |
| 331 | 'created', true |
| 332 | ) INTO v_result; |
| 333 | END IF; |
| 334 | |
| 335 | -- Auto hot-reload if it's a SQL function |
| 336 | IF p_path LIKE 'functions/%.sql' THEN |
| 337 | PERFORM hot_reload(p_path); |
| 338 | v_result := jsonb_set(v_result, '{hot_reloaded}', 'true'::jsonb); |
| 339 | END IF; |
| 340 | |
| 341 | WHEN 'DELETE' THEN |
| 342 | -- Soft delete |
| 343 | UPDATE code_files |
| 344 | SET is_active = false, |
| 345 | updated_at = now() |
| 346 | WHERE path = p_path |
| 347 | RETURNING jsonb_build_object( |
| 348 | 'path', path, |
| 349 | 'deleted', true |
| 350 | ) INTO v_result; |
| 351 | |
| 352 | IF v_result IS NULL THEN |
| 353 | v_result := jsonb_build_object('error', 'File not found'); |
| 354 | END IF; |
| 355 | |
| 356 | WHEN 'HISTORY' THEN |
| 357 | -- Get version history |
| 358 | SELECT jsonb_agg( |
| 359 | jsonb_build_object( |
| 360 | 'version', h.version, |
| 361 | 'changed_at', h.changed_at, |
| 362 | 'changed_by', h.changed_by, |
| 363 | 'size', length(h.content) |
| 364 | ) ORDER BY h.version DESC |
| 365 | ) INTO v_result |
| 366 | FROM code_history h |
| 367 | JOIN code_files f ON h.file_id = f.id |
| 368 | WHERE f.path = p_path; |
| 369 | |
| 370 | ELSE |
| 371 | v_result := jsonb_build_object('error', 'Invalid method'); |
| 372 | END CASE; |
| 373 | |
| 374 | RETURN COALESCE(v_result, jsonb_build_object('error', 'Unknown error')); |
| 375 | END; |
| 376 | $$; |
| 377 | |
| 378 | |
| 379 | ALTER FUNCTION public.code_crud(p_method text, p_path text, p_content text, p_metadata jsonb) OWNER TO danq; |
| 380 | |
| 381 | -- |
| 382 | -- Name: code_routes(text, text, jsonb); Type: FUNCTION; Schema: public; Owner: danq |
| 383 | -- |
| 384 | |
| 385 | CREATE FUNCTION public.code_routes(p_method text, p_path text, p_body jsonb DEFAULT '{}'::jsonb) RETURNS jsonb |
| 386 | LANGUAGE plpgsql |
| 387 | AS $$ |
| 388 | DECLARE |
| 389 | v_result jsonb; |
| 390 | v_file_path text; |
| 391 | v_content text; |
| 392 | v_crud_result jsonb; |
| 393 | BEGIN |
| 394 | -- Route: GET /code (Editor UI) |
| 395 | IF p_method = 'GET' AND p_path = '/code' THEN |
| 396 | SELECT content INTO v_content |
| 397 | FROM code_files |
| 398 | WHERE path = 'ui/code_editor.html'; |
| 399 | |
| 400 | RETURN jsonb_build_object( |
| 401 | 'status', 200, |
| 402 | 'headers', jsonb_build_object('Content-Type', 'text/html'), |
| 403 | 'body', v_content, |
| 404 | 'html', true |
| 405 | ); |
| 406 | |
| 407 | -- Route: GET /code/list |
| 408 | ELSIF p_method = 'GET' AND p_path = '/code/list' THEN |
| 409 | v_crud_result := code_crud('LIST', NULL); |
| 410 | RETURN jsonb_build_object( |
| 411 | 'status', 200, |
| 412 | 'body', v_crud_result |
| 413 | ); |
| 414 | |
| 415 | -- Route: GET /code/file?path=... |
| 416 | ELSIF p_method = 'GET' AND p_path LIKE '/code/file%' THEN |
| 417 | -- Extract path parameter |
| 418 | IF position('?path=' in p_path) > 0 THEN |
| 419 | v_file_path := substring(p_path from position('?path=' in p_path) + 6); |
| 420 | ELSE |
| 421 | v_file_path := NULL; |
| 422 | END IF; |
| 423 | |
| 424 | -- URL decode |
| 425 | IF v_file_path IS NOT NULL THEN |
| 426 | v_file_path := replace(v_file_path, '%2F', '/'); |
| 427 | v_file_path := replace(v_file_path, '%20', ' '); |
| 428 | v_file_path := replace(v_file_path, '+', ' '); |
| 429 | |
| 430 | -- Remove any trailing parameters |
| 431 | IF position('&' in v_file_path) > 0 THEN |
| 432 | v_file_path := substring(v_file_path from 1 for position('&' in v_file_path) - 1); |
| 433 | END IF; |
| 434 | END IF; |
| 435 | |
| 436 | -- Get the file |
| 437 | v_crud_result := code_crud('GET', v_file_path); |
| 438 | |
| 439 | RETURN jsonb_build_object( |
| 440 | 'status', 200, |
| 441 | 'body', v_crud_result |
| 442 | ); |
| 443 | |
| 444 | -- Route: POST /code/save |
| 445 | ELSIF p_method = 'POST' AND p_path = '/code/save' THEN |
| 446 | v_crud_result := code_crud('PUT', p_body->>'path', p_body->>'content'); |
| 447 | RETURN jsonb_build_object( |
| 448 | 'status', 200, |
| 449 | 'body', v_crud_result |
| 450 | ); |
| 451 | |
| 452 | -- Route: POST /code/execute |
| 453 | ELSIF p_method = 'POST' AND p_path = '/code/execute' THEN |
| 454 | v_crud_result := execute_code( |
| 455 | COALESCE(p_body->>'language', 'sql'), |
| 456 | p_body->>'code', |
| 457 | p_body->>'save_as' |
| 458 | ); |
| 459 | RETURN jsonb_build_object( |
| 460 | 'status', 200, |
| 461 | 'body', v_crud_result |
| 462 | ); |
| 463 | |
| 464 | -- Route: POST /code/reload |
| 465 | ELSIF p_method = 'POST' AND p_path = '/code/reload' THEN |
| 466 | v_crud_result := hot_reload(p_body->>'path'); |
| 467 | RETURN jsonb_build_object( |
| 468 | 'status', 200, |
| 469 | 'body', v_crud_result |
| 470 | ); |
| 471 | |
| 472 | ELSE |
| 473 | -- Return NULL to indicate route not handled |
| 474 | RETURN NULL; |
| 475 | END IF; |
| 476 | END; |
| 477 | $$; |
| 478 | |
| 479 | |
| 480 | ALTER FUNCTION public.code_routes(p_method text, p_path text, p_body jsonb) OWNER TO danq; |
| 481 | |
| 482 | -- |
| 483 | -- Name: execute_code(text, text, text); Type: FUNCTION; Schema: public; Owner: danq |
| 484 | -- |
| 485 | |
| 486 | CREATE FUNCTION public.execute_code(p_language text, p_code text, p_save_as text DEFAULT NULL::text) RETURNS jsonb |
| 487 | LANGUAGE plpgsql |
| 488 | AS $_$ |
| 489 | DECLARE |
| 490 | v_result jsonb; |
| 491 | v_do_block text; |
| 492 | BEGIN |
| 493 | -- Execute based on language |
| 494 | CASE p_language |
| 495 | WHEN 'sql' THEN |
| 496 | BEGIN |
| 497 | EXECUTE p_code; |
| 498 | v_result := jsonb_build_object( |
| 499 | 'success', true, |
| 500 | 'message', 'SQL executed successfully' |
| 501 | ); |
| 502 | |
| 503 | -- Save if requested |
| 504 | IF p_save_as IS NOT NULL THEN |
| 505 | PERFORM code_crud('PUT', p_save_as, p_code, |
| 506 | jsonb_build_object('language', 'sql', 'auto_saved', true)); |
| 507 | v_result := jsonb_set(v_result, '{saved_as}', to_jsonb(p_save_as)); |
| 508 | END IF; |
| 509 | |
| 510 | EXCEPTION WHEN OTHERS THEN |
| 511 | v_result := jsonb_build_object( |
| 512 | 'success', false, |
| 513 | 'error', SQLERRM, |
| 514 | 'detail', SQLSTATE |
| 515 | ); |
| 516 | END; |
| 517 | |
| 518 | WHEN 'plpgsql' THEN |
| 519 | BEGIN |
| 520 | -- For PL/pgSQL, wrap in DO block if needed |
| 521 | IF NOT (p_code ILIKE 'CREATE%FUNCTION%' OR p_code ILIKE 'DO%') THEN |
| 522 | v_do_block := 'DO $BLOCK$ BEGIN ' || p_code || ' END $BLOCK$;'; |
| 523 | EXECUTE v_do_block; |
| 524 | ELSE |
| 525 | EXECUTE p_code; |
| 526 | END IF; |
| 527 | |
| 528 | v_result := jsonb_build_object( |
| 529 | 'success', true, |
| 530 | 'message', 'PL/pgSQL executed successfully' |
| 531 | ); |
| 532 | |
| 533 | EXCEPTION WHEN OTHERS THEN |
| 534 | v_result := jsonb_build_object( |
| 535 | 'success', false, |
| 536 | 'error', SQLERRM |
| 537 | ); |
| 538 | END; |
| 539 | |
| 540 | ELSE |
| 541 | v_result := jsonb_build_object( |
| 542 | 'error', 'Unsupported language: ' || p_language |
| 543 | ); |
| 544 | END CASE; |
| 545 | |
| 546 | RETURN v_result; |
| 547 | END; |
| 548 | $_$; |
| 549 | |
| 550 | |
| 551 | ALTER FUNCTION public.execute_code(p_language text, p_code text, p_save_as text) OWNER TO danq; |
| 552 | |
| 553 | -- |
| 554 | -- Name: format_bytes(bigint); Type: FUNCTION; Schema: public; Owner: danq |
| 555 | -- |
| 556 | |
| 557 | CREATE FUNCTION public.format_bytes(bytes bigint) RETURNS text |
| 558 | LANGUAGE plpgsql IMMUTABLE |
| 559 | AS $$ |
| 560 | DECLARE |
| 561 | units text[] := ARRAY['B', 'KB', 'MB', 'GB', 'TB']; |
| 562 | unit_index int := 1; |
| 563 | size numeric := bytes; |
| 564 | BEGIN |
| 565 | WHILE size >= 1024 AND unit_index < array_length(units, 1) LOOP |
| 566 | size := size / 1024; |
| 567 | unit_index := unit_index + 1; |
| 568 | END LOOP; |
| 569 | |
| 570 | IF unit_index = 1 THEN |
| 571 | RETURN bytes || ' B'; |
| 572 | ELSE |
| 573 | RETURN round(size, 2) || ' ' || units[unit_index]; |
| 574 | END IF; |
| 575 | END; |
| 576 | $$; |
| 577 | |
| 578 | |
| 579 | ALTER FUNCTION public.format_bytes(bytes bigint) OWNER TO danq; |
| 580 | |
| 581 | -- |
| 582 | -- Name: get_file_extension(text); Type: FUNCTION; Schema: public; Owner: danq |
| 583 | -- |
| 584 | |
| 585 | CREATE FUNCTION public.get_file_extension(filename text) RETURNS text |
| 586 | LANGUAGE plpgsql IMMUTABLE |
| 587 | AS $$ |
| 588 | DECLARE |
| 589 | pos int; |
| 590 | BEGIN |
| 591 | pos := position('.' IN reverse(filename)); |
| 592 | IF pos > 0 THEN |
| 593 | RETURN lower(right(filename, pos - 1)); |
| 594 | END IF; |
| 595 | RETURN ''; |
| 596 | END; |
| 597 | $$; |
| 598 | |
| 599 | |
| 600 | ALTER FUNCTION public.get_file_extension(filename text) OWNER TO danq; |
| 601 | |
| 602 | -- |
| 603 | -- Name: get_version(); Type: FUNCTION; Schema: public; Owner: danq |
| 604 | -- |
| 605 | |
| 606 | CREATE FUNCTION public.get_version() RETURNS text |
| 607 | LANGUAGE plpgsql |
| 608 | AS $$ |
| 609 | BEGIN |
| 610 | RETURN 'Version 2.0 - Hot Reloaded!'; |
| 611 | END; |
| 612 | $$; |
| 613 | |
| 614 | |
| 615 | ALTER FUNCTION public.get_version() OWNER TO danq; |
| 616 | |
| 617 | -- |
| 618 | -- Name: handle_request(text, text, jsonb, jsonb, uuid); Type: FUNCTION; Schema: public; Owner: danq |
| 619 | -- |
| 620 | |
| 621 | CREATE FUNCTION public.handle_request(p_method text, p_path text, p_headers jsonb DEFAULT '{}'::jsonb, p_body jsonb DEFAULT '{}'::jsonb, p_session_id uuid DEFAULT NULL::uuid) RETURNS jsonb |
| 622 | LANGUAGE plpgsql |
| 623 | AS $$ |
| 624 | DECLARE |
| 625 | v_response jsonb; |
| 626 | v_route record; |
| 627 | v_blob_id uuid; |
| 628 | v_job_id integer; |
| 629 | v_stats jsonb; |
| 630 | v_html text; |
| 631 | v_blobs jsonb; |
| 632 | v_jobs jsonb; |
| 633 | BEGIN |
| 634 | -- Default response |
| 635 | v_response := jsonb_build_object( |
| 636 | 'status', 404, |
| 637 | 'body', jsonb_build_object('error', 'Not found') |
| 638 | ); |
| 639 | |
| 640 | -- Route: GET / (HTML Dashboard) |
| 641 | IF p_method = 'GET' AND p_path = '/' THEN |
| 642 | -- Get stats |
| 643 | SELECT jsonb_build_object( |
| 644 | 'blob_count', (SELECT COUNT(*) FROM blobs), |
| 645 | 'total_size', COALESCE((SELECT SUM(size) FROM blobs), 0), |
| 646 | 'jobs_pending', (SELECT COUNT(*) FROM jobs WHERE status = 'pending'), |
| 647 | 'jobs_completed', (SELECT COUNT(*) FROM jobs WHERE status = 'completed'), |
| 648 | 'jobs_failed', (SELECT COUNT(*) FROM jobs WHERE status = 'failed'), |
| 649 | 'total_exceptions', (SELECT COUNT(*) FROM exceptions), |
| 650 | 'active_sessions', (SELECT COUNT(*) FROM sessions WHERE expires_at > now()) |
| 651 | ) INTO v_stats; |
| 652 | |
| 653 | -- Get recent blobs |
| 654 | SELECT COALESCE(jsonb_agg( |
| 655 | jsonb_build_object( |
| 656 | 'id', id, |
| 657 | 'filename', filename, |
| 658 | 'size', size, |
| 659 | 'created_at', created_at |
| 660 | ) ORDER BY created_at DESC |
| 661 | ), '[]'::jsonb) |
| 662 | INTO v_blobs |
| 663 | FROM (SELECT * FROM blobs ORDER BY created_at DESC LIMIT 5) b; |
| 664 | |
| 665 | -- Get recent jobs |
| 666 | SELECT COALESCE(jsonb_agg( |
| 667 | jsonb_build_object( |
| 668 | 'id', id, |
| 669 | 'type', type, |
| 670 | 'status', status, |
| 671 | 'created_at', created_at |
| 672 | ) ORDER BY created_at DESC |
| 673 | ), '[]'::jsonb) |
| 674 | INTO v_jobs |
| 675 | FROM (SELECT * FROM jobs ORDER BY created_at DESC LIMIT 5) j; |
| 676 | |
| 677 | -- Build HTML |
| 678 | v_html := format('<!DOCTYPE html> |
| 679 | <html> |
| 680 | <head> |
| 681 | <title>PostBlob - PostgreSQL Does Everything!</title> |
| 682 | <meta charset="utf-8"> |
| 683 | <meta name="viewport" content="width=device-width, initial-scale=1"> |
| 684 | <style> |
| 685 | * { margin: 0; padding: 0; box-sizing: border-box; } |
| 686 | body { |
| 687 | font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif; |
| 688 | line-height: 1.6; |
| 689 | background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%); |
| 690 | min-height: 100vh; |
| 691 | padding: 20px; |
| 692 | } |
| 693 | .container { |
| 694 | max-width: 1200px; |
| 695 | margin: 0 auto; |
| 696 | } |
| 697 | header { |
| 698 | background: white; |
| 699 | border-radius: 12px; |
| 700 | padding: 30px; |
| 701 | margin-bottom: 30px; |
| 702 | box-shadow: 0 10px 40px rgba(0,0,0,0.1); |
| 703 | } |
| 704 | h1 { |
| 705 | color: #333; |
| 706 | margin-bottom: 10px; |
| 707 | font-size: 2.5em; |
| 708 | } |
| 709 | .subtitle { |
| 710 | color: #666; |
| 711 | font-size: 1.2em; |
| 712 | } |
| 713 | .grid { |
| 714 | display: grid; |
| 715 | grid-template-columns: repeat(auto-fit, minmax(300px, 1fr)); |
| 716 | gap: 20px; |
| 717 | margin-bottom: 30px; |
| 718 | } |
| 719 | .card { |
| 720 | background: white; |
| 721 | border-radius: 12px; |
| 722 | padding: 25px; |
| 723 | box-shadow: 0 10px 40px rgba(0,0,0,0.1); |
| 724 | } |
| 725 | .card h2 { |
| 726 | color: #333; |
| 727 | margin-bottom: 20px; |
| 728 | font-size: 1.5em; |
| 729 | border-bottom: 2px solid #667eea; |
| 730 | padding-bottom: 10px; |
| 731 | } |
| 732 | .stat-grid { |
| 733 | display: grid; |
| 734 | grid-template-columns: repeat(auto-fit, minmax(120px, 1fr)); |
| 735 | gap: 15px; |
| 736 | margin-bottom: 20px; |
| 737 | } |
| 738 | .stat { |
| 739 | text-align: center; |
| 740 | padding: 15px; |
| 741 | background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%); |
| 742 | border-radius: 8px; |
| 743 | color: white; |
| 744 | } |
| 745 | .stat-value { |
| 746 | font-size: 2em; |
| 747 | font-weight: bold; |
| 748 | } |
| 749 | .stat-label { |
| 750 | font-size: 0.9em; |
| 751 | opacity: 0.9; |
| 752 | } |
| 753 | .list-item { |
| 754 | padding: 12px; |
| 755 | margin: 8px 0; |
| 756 | background: #f8f9fa; |
| 757 | border-radius: 6px; |
| 758 | border-left: 4px solid #667eea; |
| 759 | } |
| 760 | .list-item strong { |
| 761 | color: #333; |
| 762 | } |
| 763 | .status { |
| 764 | display: inline-block; |
| 765 | padding: 2px 8px; |
| 766 | border-radius: 4px; |
| 767 | font-size: 0.85em; |
| 768 | font-weight: bold; |
| 769 | } |
| 770 | .status.pending { background: #ffc107; color: #333; } |
| 771 | .status.completed { background: #28a745; color: white; } |
| 772 | .status.failed { background: #dc3545; color: white; } |
| 773 | .status.processing { background: #17a2b8; color: white; } |
| 774 | .endpoint { |
| 775 | padding: 15px; |
| 776 | margin: 10px 0; |
| 777 | background: #f8f9fa; |
| 778 | border-radius: 6px; |
| 779 | border-left: 4px solid #764ba2; |
| 780 | } |
| 781 | .method { |
| 782 | display: inline-block; |
| 783 | padding: 4px 8px; |
| 784 | border-radius: 4px; |
| 785 | font-weight: bold; |
| 786 | font-size: 0.85em; |
| 787 | margin-right: 10px; |
| 788 | } |
| 789 | .method.GET { background: #28a745; color: white; } |
| 790 | .method.POST { background: #007bff; color: white; } |
| 791 | .method.DELETE { background: #dc3545; color: white; } |
| 792 | .path { |
| 793 | font-family: "Monaco", "Menlo", monospace; |
| 794 | color: #333; |
| 795 | font-weight: bold; |
| 796 | } |
| 797 | .description { |
| 798 | color: #666; |
| 799 | margin-top: 5px; |
| 800 | } |
| 801 | .query-box { |
| 802 | margin-top: 20px; |
| 803 | } |
| 804 | input[type="text"] { |
| 805 | width: 100%%; |
| 806 | padding: 12px; |
| 807 | border: 2px solid #ddd; |
| 808 | border-radius: 6px; |
| 809 | font-size: 1em; |
| 810 | margin-bottom: 10px; |
| 811 | } |
| 812 | input[type="text"]:focus { |
| 813 | outline: none; |
| 814 | border-color: #667eea; |
| 815 | } |
| 816 | button { |
| 817 | background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%); |
| 818 | color: white; |
| 819 | border: none; |
| 820 | padding: 12px 24px; |
| 821 | border-radius: 6px; |
| 822 | font-size: 1em; |
| 823 | cursor: pointer; |
| 824 | transition: transform 0.2s; |
| 825 | } |
| 826 | button:hover { |
| 827 | transform: translateY(-2px); |
| 828 | } |
| 829 | #queryResult { |
| 830 | margin-top: 15px; |
| 831 | padding: 15px; |
| 832 | background: #f8f9fa; |
| 833 | border-radius: 6px; |
| 834 | font-family: monospace; |
| 835 | white-space: pre-wrap; |
| 836 | display: none; |
| 837 | } |
| 838 | .footer { |
| 839 | text-align: center; |
| 840 | color: white; |
| 841 | margin-top: 40px; |
| 842 | } |
| 843 | .footer a { |
| 844 | color: white; |
| 845 | text-decoration: none; |
| 846 | font-weight: bold; |
| 847 | } |
| 848 | </style> |
| 849 | </head> |
| 850 | <body> |
| 851 | <div class="container"> |
| 852 | <header> |
| 853 | <h1>🎯 PostBlob</h1> |
| 854 | <p class="subtitle">PostgreSQL Does Everything - Web UI</p> |
| 855 | </header> |
| 856 | |
| 857 | <div class="grid"> |
| 858 | <div class="card"> |
| 859 | <h2>📊 Statistics</h2> |
| 860 | <div class="stat-grid"> |
| 861 | <div class="stat"> |
| 862 | <div class="stat-value">%s</div> |
| 863 | <div class="stat-label">Blobs</div> |
| 864 | </div> |
| 865 | <div class="stat"> |
| 866 | <div class="stat-value">%s</div> |
| 867 | <div class="stat-label">Pending Jobs</div> |
| 868 | </div> |
| 869 | <div class="stat"> |
| 870 | <div class="stat-value">%s</div> |
| 871 | <div class="stat-label">Completed</div> |
| 872 | </div> |
| 873 | <div class="stat"> |
| 874 | <div class="stat-value">%s</div> |
| 875 | <div class="stat-label">Sessions</div> |
| 876 | </div> |
| 877 | </div> |
| 878 | <div class="list-item"> |
| 879 | <strong>Total Storage:</strong> %s bytes |
| 880 | </div> |
| 881 | <div class="list-item"> |
| 882 | <strong>Exceptions:</strong> %s |
| 883 | </div> |
| 884 | </div> |
| 885 | |
| 886 | <div class="card"> |
| 887 | <h2>💬 Natural Language Query</h2> |
| 888 | <div class="query-box"> |
| 889 | <input type="text" id="queryInput" placeholder="Try: show blobs, failed jobs, show stats" /> |
| 890 | <button onclick="runQuery()">Ask Database</button> |
| 891 | <div id="queryResult"></div> |
| 892 | </div> |
| 893 | <script> |
| 894 | async function runQuery() { |
| 895 | const input = document.getElementById("queryInput").value; |
| 896 | const result = document.getElementById("queryResult"); |
| 897 | |
| 898 | try { |
| 899 | const response = await fetch("/ask?q=" + encodeURIComponent(input)); |
| 900 | const data = await response.json(); |
| 901 | result.style.display = "block"; |
| 902 | result.textContent = JSON.stringify(data, null, 2); |
| 903 | } catch (e) { |
| 904 | result.style.display = "block"; |
| 905 | result.textContent = "Error: " + e.message; |
| 906 | } |
| 907 | } |
| 908 | |
| 909 | document.getElementById("queryInput").addEventListener("keypress", (e) => { |
| 910 | if (e.key === "Enter") runQuery(); |
| 911 | }); |
| 912 | </script> |
| 913 | </div> |
| 914 | </div> |
| 915 | |
| 916 | <div class="grid"> |
| 917 | <div class="card"> |
| 918 | <h2>📁 Recent Blobs</h2> |
| 919 | %s |
| 920 | <div style="margin-top: 20px;"> |
| 921 | <a href="/blobs" style="color: #667eea;">View All Blobs →</a> |
| 922 | </div> |
| 923 | </div> |
| 924 | |
| 925 | <div class="card"> |
| 926 | <h2>⚡ Recent Jobs</h2> |
| 927 | %s |
| 928 | <div style="margin-top: 20px;"> |
| 929 | <a href="/jobs" style="color: #667eea;">View All Jobs →</a> |
| 930 | </div> |
| 931 | </div> |
| 932 | </div> |
| 933 | |
| 934 | <div class="card"> |
| 935 | <h2>🔥 Hot Deploy Code Editor</h2> |
| 936 | <div class="endpoint"> |
| 937 | <span class="method GET">GET</span> |
| 938 | <span class="path">/code</span> |
| 939 | <div class="description">Live code editor - Edit functions while the server is running!</div> |
| 940 | </div> |
| 941 | <div style="margin-top: 20px;"> |
| 942 | <a href="/code" style="display: inline-block; background: linear-gradient(135deg, #ff6b6b 0%%, #feca57 100%%); color: white; padding: 12px 24px; border-radius: 6px; text-decoration: none; font-weight: bold; box-shadow: 0 4px 15px rgba(0,0,0,0.2); transition: transform 0.2s;">🔥 Open Code Editor</a> |
| 943 | </div> |
| 944 | </div> |
| 945 | |
| 946 | <div class="card"> |
| 947 | <h2>🚀 API Endpoints</h2> |
| 948 | |
| 949 | <div class="endpoint"> |
| 950 | <span class="method GET">GET</span> |
| 951 | <span class="path">/stats</span> |
| 952 | <div class="description">Get database statistics</div> |
| 953 | </div> |
| 954 | |
| 955 | <div class="endpoint"> |
| 956 | <span class="method GET">GET</span> |
| 957 | <span class="path">/blobs</span> |
| 958 | <div class="description">List all blobs</div> |
| 959 | </div> |
| 960 | |
| 961 | <div class="endpoint"> |
| 962 | <span class="method GET">GET</span> |
| 963 | <span class="path">/blob/{id}</span> |
| 964 | <div class="description">Get specific blob by ID</div> |
| 965 | </div> |
| 966 | |
| 967 | <div class="endpoint"> |
| 968 | <span class="method POST">POST</span> |
| 969 | <span class="path">/blob</span> |
| 970 | <div class="description">Upload new blob (JSON: filename, data, mimetype)</div> |
| 971 | </div> |
| 972 | |
| 973 | <div class="endpoint"> |
| 974 | <span class="method DELETE">DELETE</span> |
| 975 | <span class="path">/blob/{id}</span> |
| 976 | <div class="description">Delete a blob</div> |
| 977 | </div> |
| 978 | |
| 979 | <div class="endpoint"> |
| 980 | <span class="method GET">GET</span> |
| 981 | <span class="path">/jobs</span> |
| 982 | <div class="description">List all jobs</div> |
| 983 | </div> |
| 984 | |
| 985 | <div class="endpoint"> |
| 986 | <span class="method POST">POST</span> |
| 987 | <span class="path">/job</span> |
| 988 | <div class="description">Create new job (JSON: type, payload)</div> |
| 989 | </div> |
| 990 | |
| 991 | <div class="endpoint"> |
| 992 | <span class="method GET">GET</span> |
| 993 | <span class="path">/ask?q={query}</span> |
| 994 | <div class="description">Natural language database query</div> |
| 995 | </div> |
| 996 | </div> |
| 997 | |
| 998 | <div class="footer"> |
| 999 | <p>Powered by PostgreSQL • <a href="https://github.com/yourname/postblob">GitHub</a></p> |
| 1000 | </div> |
| 1001 | </div> |
| 1002 | </body> |
| 1003 | </html>', |
| 1004 | v_stats->>'blob_count', |
| 1005 | v_stats->>'jobs_pending', |
| 1006 | v_stats->>'jobs_completed', |
| 1007 | v_stats->>'active_sessions', |
| 1008 | v_stats->>'total_size', |
| 1009 | v_stats->>'total_exceptions', |
| 1010 | CASE |
| 1011 | WHEN jsonb_array_length(v_blobs) = 0 THEN '<p style="color: #999;">No blobs yet</p>' |
| 1012 | ELSE ( |
| 1013 | SELECT string_agg( |
| 1014 | format('<div class="list-item"><strong>%s</strong><br>Size: %s bytes<br>%s</div>', |
| 1015 | b->>'filename', |
| 1016 | b->>'size', |
| 1017 | to_char((b->>'created_at')::timestamptz, 'YYYY-MM-DD HH24:MI') |
| 1018 | ), '' |
| 1019 | ) |
| 1020 | FROM jsonb_array_elements(v_blobs) b |
| 1021 | ) |
| 1022 | END, |
| 1023 | CASE |
| 1024 | WHEN jsonb_array_length(v_jobs) = 0 THEN '<p style="color: #999;">No jobs yet</p>' |
| 1025 | ELSE ( |
| 1026 | SELECT string_agg( |
| 1027 | format('<div class="list-item"><strong>%s</strong><br><span class="status %s">%s</span><br>%s</div>', |
| 1028 | j->>'type', |
| 1029 | j->>'status', |
| 1030 | j->>'status', |
| 1031 | to_char((j->>'created_at')::timestamptz, 'YYYY-MM-DD HH24:MI') |
| 1032 | ), '' |
| 1033 | ) |
| 1034 | FROM jsonb_array_elements(v_jobs) j |
| 1035 | ) |
| 1036 | END |
| 1037 | ); |
| 1038 | |
| 1039 | v_response := jsonb_build_object( |
| 1040 | 'status', 200, |
| 1041 | 'headers', jsonb_build_object('Content-Type', 'text/html'), |
| 1042 | 'body', v_html, |
| 1043 | 'html', true |
| 1044 | ); |
| 1045 | |
| 1046 | -- Route: GET /ask (Natural Language Query) |
| 1047 | ELSIF p_method = 'GET' AND p_path LIKE '/ask%' THEN |
| 1048 | DECLARE |
| 1049 | v_query text; |
| 1050 | BEGIN |
| 1051 | -- Extract query parameter (simple parsing) |
| 1052 | v_query := substring(p_path from 'q=([^&]+)'); |
| 1053 | v_query := replace(v_query, '%20', ' '); |
| 1054 | |
| 1055 | IF v_query IS NOT NULL THEN |
| 1056 | v_response := jsonb_build_object( |
| 1057 | 'status', 200, |
| 1058 | 'body', ask(v_query) |
| 1059 | ); |
| 1060 | ELSE |
| 1061 | v_response := jsonb_build_object( |
| 1062 | 'status', 400, |
| 1063 | 'body', jsonb_build_object('error', 'Missing query parameter') |
| 1064 | ); |
| 1065 | END IF; |
| 1066 | END; |
| 1067 | |
| 1068 | -- Route: GET /stats |
| 1069 | ELSIF p_method = 'GET' AND p_path = '/stats' THEN |
| 1070 | SELECT jsonb_build_object( |
| 1071 | 'blob_count', (SELECT COUNT(*) FROM blobs), |
| 1072 | 'total_size', COALESCE((SELECT SUM(size) FROM blobs), 0), |
| 1073 | 'jobs_completed', (SELECT COUNT(*) FROM jobs WHERE status = 'completed'), |
| 1074 | 'total_exceptions', (SELECT COUNT(*) FROM exceptions), |
| 1075 | 'active_sessions', (SELECT COUNT(*) FROM sessions WHERE expires_at > now()) |
| 1076 | ) INTO v_stats; |
| 1077 | |
| 1078 | v_response := jsonb_build_object( |
| 1079 | 'status', 200, |
| 1080 | 'body', v_stats |
| 1081 | ); |
| 1082 | |
| 1083 | -- Route: GET /blobs |
| 1084 | ELSIF p_method = 'GET' AND p_path = '/blobs' THEN |
| 1085 | v_response := jsonb_build_object( |
| 1086 | 'status', 200, |
| 1087 | 'body', ( |
| 1088 | SELECT COALESCE(jsonb_agg( |
| 1089 | jsonb_build_object( |
| 1090 | 'id', id, |
| 1091 | 'filename', filename, |
| 1092 | 'size', size, |
| 1093 | 'mimetype', mimetype, |
| 1094 | 'created_at', created_at |
| 1095 | ) ORDER BY created_at DESC |
| 1096 | ), '[]'::jsonb) |
| 1097 | FROM blobs |
| 1098 | LIMIT 100 |
| 1099 | ) |
| 1100 | ); |
| 1101 | |
| 1102 | -- Route: GET /blob/{id} |
| 1103 | ELSIF p_method = 'GET' AND p_path LIKE '/blob/%' THEN |
| 1104 | v_blob_id := substring(p_path from '/blob/(.+)')::uuid; |
| 1105 | |
| 1106 | SELECT jsonb_build_object( |
| 1107 | 'id', id, |
| 1108 | 'filename', filename, |
| 1109 | 'size', size, |
| 1110 | 'mimetype', mimetype, |
| 1111 | 'metadata', metadata, |
| 1112 | 'created_at', created_at |
| 1113 | ) INTO v_response |
| 1114 | FROM blobs |
| 1115 | WHERE id = v_blob_id; |
| 1116 | |
| 1117 | IF v_response IS NOT NULL THEN |
| 1118 | v_response := jsonb_build_object( |
| 1119 | 'status', 200, |
| 1120 | 'body', v_response |
| 1121 | ); |
| 1122 | ELSE |
| 1123 | v_response := jsonb_build_object( |
| 1124 | 'status', 404, |
| 1125 | 'body', jsonb_build_object('error', 'Blob not found') |
| 1126 | ); |
| 1127 | END IF; |
| 1128 | |
| 1129 | -- Route: POST /blob |
| 1130 | ELSIF p_method = 'POST' AND p_path = '/blob' THEN |
| 1131 | INSERT INTO blobs (filename, mimetype, data, size, metadata) |
| 1132 | VALUES ( |
| 1133 | COALESCE(p_body->>'filename', 'unnamed'), |
| 1134 | COALESCE(p_body->>'mimetype', 'application/octet-stream'), |
| 1135 | decode(p_body->>'data', 'base64'), |
| 1136 | length(decode(p_body->>'data', 'base64')), |
| 1137 | COALESCE(p_body->'metadata', '{}'::jsonb) |
| 1138 | ) |
| 1139 | RETURNING id INTO v_blob_id; |
| 1140 | |
| 1141 | v_response := jsonb_build_object( |
| 1142 | 'status', 201, |
| 1143 | 'body', jsonb_build_object( |
| 1144 | 'id', v_blob_id, |
| 1145 | 'url', '/blob/' || v_blob_id |
| 1146 | ) |
| 1147 | ); |
| 1148 | |
| 1149 | -- Route: DELETE /blob/{id} |
| 1150 | ELSIF p_method = 'DELETE' AND p_path LIKE '/blob/%' THEN |
| 1151 | v_blob_id := substring(p_path from '/blob/(.+)')::uuid; |
| 1152 | |
| 1153 | DELETE FROM blobs WHERE id = v_blob_id; |
| 1154 | |
| 1155 | IF FOUND THEN |
| 1156 | v_response := jsonb_build_object( |
| 1157 | 'status', 200, |
| 1158 | 'body', jsonb_build_object('deleted', v_blob_id) |
| 1159 | ); |
| 1160 | ELSE |
| 1161 | v_response := jsonb_build_object( |
| 1162 | 'status', 404, |
| 1163 | 'body', jsonb_build_object('error', 'Blob not found') |
| 1164 | ); |
| 1165 | END IF; |
| 1166 | |
| 1167 | -- Route: GET /jobs |
| 1168 | ELSIF p_method = 'GET' AND p_path = '/jobs' THEN |
| 1169 | v_response := jsonb_build_object( |
| 1170 | 'status', 200, |
| 1171 | 'body', ( |
| 1172 | SELECT COALESCE(jsonb_agg( |
| 1173 | jsonb_build_object( |
| 1174 | 'id', id, |
| 1175 | 'type', type, |
| 1176 | 'status', status, |
| 1177 | 'created_at', created_at, |
| 1178 | 'completed_at', completed_at |
| 1179 | ) ORDER BY created_at DESC |
| 1180 | ), '[]'::jsonb) |
| 1181 | FROM jobs |
| 1182 | LIMIT 100 |
| 1183 | ) |
| 1184 | ); |
| 1185 | |
| 1186 | -- Route: POST /job |
| 1187 | ELSIF p_method = 'POST' AND p_path = '/job' THEN |
| 1188 | INSERT INTO jobs (type, payload) |
| 1189 | VALUES ( |
| 1190 | COALESCE(p_body->>'type', 'default'), |
| 1191 | COALESCE(p_body->'payload', '{}'::jsonb) |
| 1192 | ) |
| 1193 | RETURNING id INTO v_job_id; |
| 1194 | |
| 1195 | -- Notify job worker |
| 1196 | PERFORM pg_notify('new_job', v_job_id::text); |
| 1197 | |
| 1198 | v_response := jsonb_build_object( |
| 1199 | 'status', 201, |
| 1200 | 'body', jsonb_build_object( |
| 1201 | 'job_id', v_job_id, |
| 1202 | 'status', 'pending' |
| 1203 | ) |
| 1204 | ); |
| 1205 | |
| 1206 | -- Route: GET /job/{id} |
| 1207 | ELSIF p_method = 'GET' AND p_path LIKE '/job/%' THEN |
| 1208 | v_job_id := substring(p_path from '/job/(\d+)')::integer; |
| 1209 | |
| 1210 | SELECT jsonb_build_object( |
| 1211 | 'id', id, |
| 1212 | 'type', type, |
| 1213 | 'status', status, |
| 1214 | 'payload', payload, |
| 1215 | 'result', result, |
| 1216 | 'created_at', created_at, |
| 1217 | 'completed_at', completed_at |
| 1218 | ) INTO v_response |
| 1219 | FROM jobs |
| 1220 | WHERE id = v_job_id; |
| 1221 | |
| 1222 | IF v_response IS NOT NULL THEN |
| 1223 | v_response := jsonb_build_object( |
| 1224 | 'status', 200, |
| 1225 | 'body', v_response |
| 1226 | ); |
| 1227 | ELSE |
| 1228 | v_response := jsonb_build_object( |
| 1229 | 'status', 404, |
| 1230 | 'body', jsonb_build_object('error', 'Job not found') |
| 1231 | ); |
| 1232 | END IF; |
| 1233 | END IF; |
| 1234 | |
| 1235 | RETURN v_response; |
| 1236 | END; |
| 1237 | $$; |
| 1238 | |
| 1239 | |
| 1240 | ALTER FUNCTION public.handle_request(p_method text, p_path text, p_headers jsonb, p_body jsonb, p_session_id uuid) OWNER TO danq; |
| 1241 | |
| 1242 | -- |
| 1243 | -- Name: hot_reload(text); Type: FUNCTION; Schema: public; Owner: danq |
| 1244 | -- |
| 1245 | |
| 1246 | CREATE FUNCTION public.hot_reload(file_path text DEFAULT NULL::text) RETURNS jsonb |
| 1247 | LANGUAGE plpgsql |
| 1248 | AS $$ |
| 1249 | DECLARE |
| 1250 | v_code text; |
| 1251 | v_result jsonb; |
| 1252 | v_file record; |
| 1253 | BEGIN |
| 1254 | v_result := jsonb_build_object('reloaded', jsonb_array()); |
| 1255 | |
| 1256 | -- Reload specific file or all active files |
| 1257 | FOR v_file IN |
| 1258 | SELECT * FROM code_files |
| 1259 | WHERE is_active = true |
| 1260 | AND (file_path IS NULL OR path = file_path) |
| 1261 | AND language = 'sql' |
| 1262 | ORDER BY path |
| 1263 | LOOP |
| 1264 | BEGIN |
| 1265 | -- Execute the SQL code |
| 1266 | EXECUTE v_file.content; |
| 1267 | |
| 1268 | -- Update executed timestamp |
| 1269 | UPDATE code_files |
| 1270 | SET executed_at = now() |
| 1271 | WHERE id = v_file.id; |
| 1272 | |
| 1273 | -- Add to result |
| 1274 | v_result := jsonb_set( |
| 1275 | v_result, |
| 1276 | '{reloaded, 999999}', |
| 1277 | jsonb_build_object( |
| 1278 | 'path', v_file.path, |
| 1279 | 'version', v_file.version, |
| 1280 | 'success', true |
| 1281 | ) |
| 1282 | ); |
| 1283 | |
| 1284 | EXCEPTION WHEN OTHERS THEN |
| 1285 | -- Log error but continue |
| 1286 | v_result := jsonb_set( |
| 1287 | v_result, |
| 1288 | '{errors, 999999}', |
| 1289 | jsonb_build_object( |
| 1290 | 'path', v_file.path, |
| 1291 | 'error', SQLERRM |
| 1292 | ) |
| 1293 | ); |
| 1294 | END; |
| 1295 | END LOOP; |
| 1296 | |
| 1297 | RETURN v_result; |
| 1298 | END; |
| 1299 | $$; |
| 1300 | |
| 1301 | |
| 1302 | ALTER FUNCTION public.hot_reload(file_path text) OWNER TO danq; |
| 1303 | |
| 1304 | -- |
| 1305 | -- Name: html_decode(text); Type: FUNCTION; Schema: public; Owner: danq |
| 1306 | -- |
| 1307 | |
| 1308 | CREATE FUNCTION public.html_decode(input text) RETURNS text |
| 1309 | LANGUAGE plpgsql IMMUTABLE |
| 1310 | AS $$ |
| 1311 | BEGIN |
| 1312 | IF input IS NULL THEN |
| 1313 | RETURN NULL; |
| 1314 | END IF; |
| 1315 | |
| 1316 | -- Replace HTML entities back to characters |
| 1317 | RETURN replace( |
| 1318 | replace( |
| 1319 | replace( |
| 1320 | replace( |
| 1321 | replace(input, '<', '<'), |
| 1322 | '>', '>'), |
| 1323 | '"', '"'), |
| 1324 | ''', ''''), |
| 1325 | '&', '&'); |
| 1326 | END; |
| 1327 | $$; |
| 1328 | |
| 1329 | |
| 1330 | ALTER FUNCTION public.html_decode(input text) OWNER TO danq; |
| 1331 | |
| 1332 | -- |
| 1333 | -- Name: html_encode(text); Type: FUNCTION; Schema: public; Owner: danq |
| 1334 | -- |
| 1335 | |
| 1336 | CREATE FUNCTION public.html_encode(input text) RETURNS text |
| 1337 | LANGUAGE plpgsql IMMUTABLE |
| 1338 | AS $$ |
| 1339 | BEGIN |
| 1340 | IF input IS NULL THEN |
| 1341 | RETURN NULL; |
| 1342 | END IF; |
| 1343 | |
| 1344 | -- Replace HTML special characters |
| 1345 | RETURN replace( |
| 1346 | replace( |
| 1347 | replace( |
| 1348 | replace( |
| 1349 | replace(input, '&', '&'), |
| 1350 | '<', '<'), |
| 1351 | '>', '>'), |
| 1352 | '"', '"'), |
| 1353 | '''', '''); |
| 1354 | END; |
| 1355 | $$; |
| 1356 | |
| 1357 | |
| 1358 | ALTER FUNCTION public.html_encode(input text) OWNER TO danq; |
| 1359 | |
| 1360 | -- |
| 1361 | -- Name: increment_version(); Type: FUNCTION; Schema: public; Owner: danq |
| 1362 | -- |
| 1363 | |
| 1364 | CREATE FUNCTION public.increment_version() RETURNS jsonb |
| 1365 | LANGUAGE plpgsql |
| 1366 | AS $_$ |
| 1367 | DECLARE |
| 1368 | v_current_version text; |
| 1369 | v_new_version text; |
| 1370 | v_new_code text; |
| 1371 | v_version_num numeric; |
| 1372 | BEGIN |
| 1373 | -- Get current version |
| 1374 | v_current_version := get_version(); |
| 1375 | |
| 1376 | -- Parse and increment |
| 1377 | v_version_num := substring(v_current_version from '\d+\.\d+')::numeric + 0.1; |
| 1378 | v_new_version := 'Version ' || v_version_num::text; |
| 1379 | |
| 1380 | -- Create new function code |
| 1381 | v_new_code := 'CREATE OR REPLACE FUNCTION get_version() |
| 1382 | RETURNS text AS $FUNC$ |
| 1383 | BEGIN |
| 1384 | RETURN ' || quote_literal(v_new_version) || '; |
| 1385 | END; |
| 1386 | $FUNC$ LANGUAGE plpgsql;'; |
| 1387 | |
| 1388 | -- Update in database |
| 1389 | UPDATE code_files |
| 1390 | SET content = v_new_code, |
| 1391 | version = version + 1, |
| 1392 | updated_at = now() |
| 1393 | WHERE path = 'functions/self_modify.sql'; |
| 1394 | |
| 1395 | -- Hot reload |
| 1396 | EXECUTE v_new_code; |
| 1397 | |
| 1398 | RETURN jsonb_build_object( |
| 1399 | 'old_version', v_current_version, |
| 1400 | 'new_version', v_new_version, |
| 1401 | 'reloaded', true |
| 1402 | ); |
| 1403 | END; |
| 1404 | $_$; |
| 1405 | |
| 1406 | |
| 1407 | ALTER FUNCTION public.increment_version() OWNER TO danq; |
| 1408 | |
| 1409 | -- |
| 1410 | -- Name: is_valid_email(text); Type: FUNCTION; Schema: public; Owner: danq |
| 1411 | -- |
| 1412 | |
| 1413 | CREATE FUNCTION public.is_valid_email(email text) RETURNS boolean |
| 1414 | LANGUAGE plpgsql IMMUTABLE |
| 1415 | AS $_$ |
| 1416 | BEGIN |
| 1417 | RETURN email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'; |
| 1418 | END; |
| 1419 | $_$; |
| 1420 | |
| 1421 | |
| 1422 | ALTER FUNCTION public.is_valid_email(email text) OWNER TO danq; |
| 1423 | |
| 1424 | -- |
| 1425 | -- Name: json_escape(text); Type: FUNCTION; Schema: public; Owner: danq |
| 1426 | -- |
| 1427 | |
| 1428 | CREATE FUNCTION public.json_escape(input text) RETURNS text |
| 1429 | LANGUAGE plpgsql IMMUTABLE |
| 1430 | AS $$ |
| 1431 | BEGIN |
| 1432 | IF input IS NULL THEN |
| 1433 | RETURN NULL; |
| 1434 | END IF; |
| 1435 | |
| 1436 | -- Use PostgreSQL's built-in JSON escaping |
| 1437 | RETURN to_json(input)::text; |
| 1438 | END; |
| 1439 | $$; |
| 1440 | |
| 1441 | |
| 1442 | ALTER FUNCTION public.json_escape(input text) OWNER TO danq; |
| 1443 | |
| 1444 | -- |
| 1445 | -- Name: notify_new_job(); Type: FUNCTION; Schema: public; Owner: danq |
| 1446 | -- |
| 1447 | |
| 1448 | CREATE FUNCTION public.notify_new_job() RETURNS trigger |
| 1449 | LANGUAGE plpgsql |
| 1450 | AS $$ |
| 1451 | BEGIN |
| 1452 | PERFORM pg_notify('new_job', NEW.id::text); |
| 1453 | RETURN NEW; |
| 1454 | END; |
| 1455 | $$; |
| 1456 | |
| 1457 | |
| 1458 | ALTER FUNCTION public.notify_new_job() OWNER TO danq; |
| 1459 | |
| 1460 | -- |
| 1461 | -- Name: parse_query_string(text); Type: FUNCTION; Schema: public; Owner: danq |
| 1462 | -- |
| 1463 | |
| 1464 | CREATE FUNCTION public.parse_query_string(query_string text) RETURNS jsonb |
| 1465 | LANGUAGE plpgsql IMMUTABLE |
| 1466 | AS $$ |
| 1467 | DECLARE |
| 1468 | params jsonb := '{}'::jsonb; |
| 1469 | pair text; |
| 1470 | key text; |
| 1471 | value text; |
| 1472 | pos int; |
| 1473 | BEGIN |
| 1474 | IF query_string IS NULL OR query_string = '' THEN |
| 1475 | RETURN params; |
| 1476 | END IF; |
| 1477 | |
| 1478 | -- Remove leading ? if present |
| 1479 | IF left(query_string, 1) = '?' THEN |
| 1480 | query_string := substring(query_string, 2); |
| 1481 | END IF; |
| 1482 | |
| 1483 | -- Split by & and process each pair |
| 1484 | FOREACH pair IN ARRAY string_to_array(query_string, '&') LOOP |
| 1485 | pos := position('=' IN pair); |
| 1486 | IF pos > 0 THEN |
| 1487 | key := url_decode(substring(pair, 1, pos - 1)); |
| 1488 | value := url_decode(substring(pair, pos + 1)); |
| 1489 | ELSE |
| 1490 | key := url_decode(pair); |
| 1491 | value := ''; |
| 1492 | END IF; |
| 1493 | |
| 1494 | -- Add to params |
| 1495 | params := params || jsonb_build_object(key, value); |
| 1496 | END LOOP; |
| 1497 | |
| 1498 | RETURN params; |
| 1499 | END; |
| 1500 | $$; |
| 1501 | |
| 1502 | |
| 1503 | ALTER FUNCTION public.parse_query_string(query_string text) OWNER TO danq; |
| 1504 | |
| 1505 | -- |
| 1506 | -- Name: process_job(integer); Type: FUNCTION; Schema: public; Owner: danq |
| 1507 | -- |
| 1508 | |
| 1509 | CREATE FUNCTION public.process_job(p_job_id integer) RETURNS void |
| 1510 | LANGUAGE plpgsql |
| 1511 | AS $$ |
| 1512 | DECLARE |
| 1513 | v_job record; |
| 1514 | v_result jsonb; |
| 1515 | BEGIN |
| 1516 | -- Get job details |
| 1517 | SELECT * INTO v_job FROM jobs WHERE id = p_job_id FOR UPDATE; |
| 1518 | |
| 1519 | IF NOT FOUND OR v_job.status != 'pending' THEN |
| 1520 | RETURN; |
| 1521 | END IF; |
| 1522 | |
| 1523 | -- Mark as processing |
| 1524 | UPDATE jobs |
| 1525 | SET status = 'processing', started_at = now() |
| 1526 | WHERE id = p_job_id; |
| 1527 | |
| 1528 | -- Process different job types |
| 1529 | BEGIN |
| 1530 | CASE v_job.type |
| 1531 | WHEN 'cleanup' THEN |
| 1532 | -- Clean old sessions and cache |
| 1533 | DELETE FROM sessions WHERE expires_at < now(); |
| 1534 | DELETE FROM cache WHERE expires_at < now(); |
| 1535 | v_result := jsonb_build_object( |
| 1536 | 'status', 'success', |
| 1537 | 'message', 'Cleanup completed' |
| 1538 | ); |
| 1539 | |
| 1540 | WHEN 'compute' THEN |
| 1541 | -- Simple computation |
| 1542 | v_result := jsonb_build_object( |
| 1543 | 'result', (COALESCE((v_job.payload->>'value')::integer, 0) * 2) |
| 1544 | ); |
| 1545 | |
| 1546 | WHEN 'analyze_blobs' THEN |
| 1547 | -- Analyze blob storage |
| 1548 | v_result := jsonb_build_object( |
| 1549 | 'total_blobs', (SELECT COUNT(*) FROM blobs), |
| 1550 | 'total_size', (SELECT COALESCE(SUM(size), 0) FROM blobs), |
| 1551 | 'avg_size', (SELECT COALESCE(AVG(size), 0) FROM blobs) |
| 1552 | ); |
| 1553 | |
| 1554 | ELSE |
| 1555 | -- Unknown job type |
| 1556 | v_result := jsonb_build_object( |
| 1557 | 'error', format('Unknown job type: %s', v_job.type) |
| 1558 | ); |
| 1559 | END CASE; |
| 1560 | |
| 1561 | -- Mark as completed |
| 1562 | UPDATE jobs |
| 1563 | SET status = 'completed', |
| 1564 | result = v_result, |
| 1565 | completed_at = now() |
| 1566 | WHERE id = p_job_id; |
| 1567 | |
| 1568 | EXCEPTION WHEN OTHERS THEN |
| 1569 | -- Mark as failed |
| 1570 | UPDATE jobs |
| 1571 | SET status = 'failed', |
| 1572 | result = jsonb_build_object( |
| 1573 | 'error', SQLERRM, |
| 1574 | 'detail', SQLSTATE |
| 1575 | ), |
| 1576 | attempts = attempts + 1 |
| 1577 | WHERE id = p_job_id; |
| 1578 | |
| 1579 | -- Log exception |
| 1580 | INSERT INTO exceptions (error_type, message, context, function_name) |
| 1581 | VALUES ( |
| 1582 | SQLSTATE, |
| 1583 | SQLERRM, |
| 1584 | jsonb_build_object('job_id', p_job_id, 'job_type', v_job.type), |
| 1585 | 'process_job' |
| 1586 | ); |
| 1587 | END; |
| 1588 | END; |
| 1589 | $$; |
| 1590 | |
| 1591 | |
| 1592 | ALTER FUNCTION public.process_job(p_job_id integer) OWNER TO danq; |
| 1593 | |
| 1594 | -- |
| 1595 | -- Name: random_string(integer); Type: FUNCTION; Schema: public; Owner: danq |
| 1596 | -- |
| 1597 | |
| 1598 | CREATE FUNCTION public.random_string(len integer) RETURNS text |
| 1599 | LANGUAGE plpgsql |
| 1600 | AS $$ |
| 1601 | DECLARE |
| 1602 | chars text := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; |
| 1603 | result text := ''; |
| 1604 | i int; |
| 1605 | BEGIN |
| 1606 | FOR i IN 1..len LOOP |
| 1607 | result := result || substring(chars, floor(random() * length(chars) + 1)::int, 1); |
| 1608 | END LOOP; |
| 1609 | |
| 1610 | RETURN result; |
| 1611 | END; |
| 1612 | $$; |
| 1613 | |
| 1614 | |
| 1615 | ALTER FUNCTION public.random_string(len integer) OWNER TO danq; |
| 1616 | |
| 1617 | -- |
| 1618 | -- Name: slugify(text); Type: FUNCTION; Schema: public; Owner: danq |
| 1619 | -- |
| 1620 | |
| 1621 | CREATE FUNCTION public.slugify(input text) RETURNS text |
| 1622 | LANGUAGE plpgsql IMMUTABLE |
| 1623 | AS $$ |
| 1624 | BEGIN |
| 1625 | IF input IS NULL THEN |
| 1626 | RETURN NULL; |
| 1627 | END IF; |
| 1628 | |
| 1629 | RETURN lower( |
| 1630 | regexp_replace( |
| 1631 | regexp_replace( |
| 1632 | regexp_replace( |
| 1633 | input, |
| 1634 | '[^a-zA-Z0-9\s-]', '', 'g' -- Remove special chars |
| 1635 | ), |
| 1636 | '\s+', '-', 'g' -- Replace spaces with hyphens |
| 1637 | ), |
| 1638 | '-+', '-', 'g' -- Replace multiple hyphens with single |
| 1639 | ) |
| 1640 | ); |
| 1641 | END; |
| 1642 | $$; |
| 1643 | |
| 1644 | |
| 1645 | ALTER FUNCTION public.slugify(input text) OWNER TO danq; |
| 1646 | |
| 1647 | -- |
| 1648 | -- Name: strip_tags(text); Type: FUNCTION; Schema: public; Owner: danq |
| 1649 | -- |
| 1650 | |
| 1651 | CREATE FUNCTION public.strip_tags(input text) RETURNS text |
| 1652 | LANGUAGE plpgsql IMMUTABLE |
| 1653 | AS $$ |
| 1654 | BEGIN |
| 1655 | IF input IS NULL THEN |
| 1656 | RETURN NULL; |
| 1657 | END IF; |
| 1658 | |
| 1659 | -- Remove all HTML tags |
| 1660 | RETURN regexp_replace(input, '<[^>]+>', '', 'g'); |
| 1661 | END; |
| 1662 | $$; |
| 1663 | |
| 1664 | |
| 1665 | ALTER FUNCTION public.strip_tags(input text) OWNER TO danq; |
| 1666 | |
| 1667 | -- |
| 1668 | -- Name: truncate_text(text, integer, text); Type: FUNCTION; Schema: public; Owner: danq |
| 1669 | -- |
| 1670 | |
| 1671 | CREATE FUNCTION public.truncate_text(input text, max_length integer, ellipsis text DEFAULT '...'::text) RETURNS text |
| 1672 | LANGUAGE plpgsql IMMUTABLE |
| 1673 | AS $$ |
| 1674 | BEGIN |
| 1675 | IF input IS NULL OR length(input) <= max_length THEN |
| 1676 | RETURN input; |
| 1677 | END IF; |
| 1678 | |
| 1679 | RETURN left(input, max_length - length(ellipsis)) || ellipsis; |
| 1680 | END; |
| 1681 | $$; |
| 1682 | |
| 1683 | |
| 1684 | ALTER FUNCTION public.truncate_text(input text, max_length integer, ellipsis text) OWNER TO danq; |
| 1685 | |
| 1686 | -- |
| 1687 | -- Name: url_decode(text); Type: FUNCTION; Schema: public; Owner: danq |
| 1688 | -- |
| 1689 | |
| 1690 | CREATE FUNCTION public.url_decode(input text) RETURNS text |
| 1691 | LANGUAGE plpgsql IMMUTABLE |
| 1692 | AS $_$ |
| 1693 | DECLARE |
| 1694 | result text := ''; |
| 1695 | i int := 1; |
| 1696 | len int; |
| 1697 | hex_str text; |
| 1698 | BEGIN |
| 1699 | IF input IS NULL THEN |
| 1700 | RETURN NULL; |
| 1701 | END IF; |
| 1702 | |
| 1703 | len := length(input); |
| 1704 | |
| 1705 | WHILE i <= len LOOP |
| 1706 | IF substring(input, i, 1) = '%' AND i + 2 <= len THEN |
| 1707 | -- Decode %HH |
| 1708 | hex_str := substring(input, i + 1, 2); |
| 1709 | IF hex_str ~ '^[0-9A-Fa-f]{2}$' THEN |
| 1710 | result := result || chr(('x' || hex_str)::bit(8)::int); |
| 1711 | i := i + 3; |
| 1712 | ELSE |
| 1713 | -- Invalid hex sequence, keep the % |
| 1714 | result := result || '%'; |
| 1715 | i := i + 1; |
| 1716 | END IF; |
| 1717 | ELSIF substring(input, i, 1) = '+' THEN |
| 1718 | -- + becomes space |
| 1719 | result := result || ' '; |
| 1720 | i := i + 1; |
| 1721 | ELSE |
| 1722 | -- Regular character |
| 1723 | result := result || substring(input, i, 1); |
| 1724 | i := i + 1; |
| 1725 | END IF; |
| 1726 | END LOOP; |
| 1727 | |
| 1728 | RETURN result; |
| 1729 | END; |
| 1730 | $_$; |
| 1731 | |
| 1732 | |
| 1733 | ALTER FUNCTION public.url_decode(input text) OWNER TO danq; |
| 1734 | |
| 1735 | -- |
| 1736 | -- Name: url_encode(text); Type: FUNCTION; Schema: public; Owner: danq |
| 1737 | -- |
| 1738 | |
| 1739 | CREATE FUNCTION public.url_encode(input text) RETURNS text |
| 1740 | LANGUAGE plpgsql IMMUTABLE |
| 1741 | AS $$ |
| 1742 | DECLARE |
| 1743 | result text := ''; |
| 1744 | char_code int; |
| 1745 | i int; |
| 1746 | BEGIN |
| 1747 | IF input IS NULL THEN |
| 1748 | RETURN NULL; |
| 1749 | END IF; |
| 1750 | |
| 1751 | -- Encode each character |
| 1752 | FOR i IN 1..length(input) LOOP |
| 1753 | char_code := ascii(substring(input, i, 1)); |
| 1754 | |
| 1755 | -- Check if character needs encoding |
| 1756 | IF (char_code >= 48 AND char_code <= 57) OR -- 0-9 |
| 1757 | (char_code >= 65 AND char_code <= 90) OR -- A-Z |
| 1758 | (char_code >= 97 AND char_code <= 122) OR -- a-z |
| 1759 | char_code IN (45, 46, 95, 126) THEN -- - . _ ~ |
| 1760 | -- Safe character, don't encode |
| 1761 | result := result || substring(input, i, 1); |
| 1762 | ELSIF char_code = 32 THEN |
| 1763 | -- Space becomes + |
| 1764 | result := result || '+'; |
| 1765 | ELSE |
| 1766 | -- Encode as %HH |
| 1767 | result := result || '%' || upper(to_hex(char_code)); |
| 1768 | END IF; |
| 1769 | END LOOP; |
| 1770 | |
| 1771 | RETURN result; |
| 1772 | END; |
| 1773 | $$; |
| 1774 | |
| 1775 | |
| 1776 | ALTER FUNCTION public.url_encode(input text) OWNER TO danq; |
| 1777 | |
| 1778 | SET default_tablespace = ''; |
| 1779 | |
| 1780 | SET default_table_access_method = heap; |
| 1781 | |
| 1782 | -- |
| 1783 | -- Name: blobs; Type: TABLE; Schema: public; Owner: danq |
| 1784 | -- |
| 1785 | |
| 1786 | CREATE TABLE public.blobs ( |
| 1787 | id uuid DEFAULT public.uuid_generate_v4() NOT NULL, |
| 1788 | filename text NOT NULL, |
| 1789 | mimetype text NOT NULL, |
| 1790 | data bytea NOT NULL, |
| 1791 | size bigint NOT NULL, |
| 1792 | metadata jsonb DEFAULT '{}'::jsonb, |
| 1793 | created_at timestamp with time zone DEFAULT now(), |
| 1794 | updated_at timestamp with time zone DEFAULT now() |
| 1795 | ); |
| 1796 | |
| 1797 | |
| 1798 | ALTER TABLE public.blobs OWNER TO danq; |
| 1799 | |
| 1800 | -- |
| 1801 | -- Name: cache; Type: TABLE; Schema: public; Owner: danq |
| 1802 | -- |
| 1803 | |
| 1804 | CREATE TABLE public.cache ( |
| 1805 | key text NOT NULL, |
| 1806 | value jsonb NOT NULL, |
| 1807 | expires_at timestamp with time zone, |
| 1808 | created_at timestamp with time zone DEFAULT now() |
| 1809 | ); |
| 1810 | |
| 1811 | |
| 1812 | ALTER TABLE public.cache OWNER TO danq; |
| 1813 | |
| 1814 | -- |
| 1815 | -- Name: circuit_breakers; Type: TABLE; Schema: public; Owner: danq |
| 1816 | -- |
| 1817 | |
| 1818 | CREATE TABLE public.circuit_breakers ( |
| 1819 | function_name text NOT NULL, |
| 1820 | state text DEFAULT 'closed'::text, |
| 1821 | failure_count integer DEFAULT 0, |
| 1822 | last_failure timestamp with time zone, |
| 1823 | last_success timestamp with time zone, |
| 1824 | CONSTRAINT circuit_breakers_state_check CHECK ((state = ANY (ARRAY['closed'::text, 'open'::text, 'half_open'::text]))) |
| 1825 | ); |
| 1826 | |
| 1827 | |
| 1828 | ALTER TABLE public.circuit_breakers OWNER TO danq; |
| 1829 | |
| 1830 | -- |
| 1831 | -- Name: code_files; Type: TABLE; Schema: public; Owner: danq |
| 1832 | -- |
| 1833 | |
| 1834 | CREATE TABLE public.code_files ( |
| 1835 | id integer NOT NULL, |
| 1836 | path text NOT NULL, |
| 1837 | content text NOT NULL, |
| 1838 | language text DEFAULT 'sql'::text, |
| 1839 | version integer DEFAULT 1, |
| 1840 | is_active boolean DEFAULT true, |
| 1841 | created_at timestamp with time zone DEFAULT now(), |
| 1842 | updated_at timestamp with time zone DEFAULT now(), |
| 1843 | executed_at timestamp with time zone, |
| 1844 | metadata jsonb DEFAULT '{}'::jsonb |
| 1845 | ); |
| 1846 | |
| 1847 | |
| 1848 | ALTER TABLE public.code_files OWNER TO danq; |
| 1849 | |
| 1850 | -- |
| 1851 | -- Name: code_files_id_seq; Type: SEQUENCE; Schema: public; Owner: danq |
| 1852 | -- |
| 1853 | |
| 1854 | CREATE SEQUENCE public.code_files_id_seq |
| 1855 | AS integer |
| 1856 | START WITH 1 |
| 1857 | INCREMENT BY 1 |
| 1858 | NO MINVALUE |
| 1859 | NO MAXVALUE |
| 1860 | CACHE 1; |
| 1861 | |
| 1862 | |
| 1863 | ALTER TABLE public.code_files_id_seq OWNER TO danq; |
| 1864 | |
| 1865 | -- |
| 1866 | -- Name: code_files_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: danq |
| 1867 | -- |
| 1868 | |
| 1869 | ALTER SEQUENCE public.code_files_id_seq OWNED BY public.code_files.id; |
| 1870 | |
| 1871 | |
| 1872 | -- |
| 1873 | -- Name: code_history; Type: TABLE; Schema: public; Owner: danq |
| 1874 | -- |
| 1875 | |
| 1876 | CREATE TABLE public.code_history ( |
| 1877 | id integer NOT NULL, |
| 1878 | file_id integer, |
| 1879 | content text NOT NULL, |
| 1880 | version integer NOT NULL, |
| 1881 | changed_at timestamp with time zone DEFAULT now(), |
| 1882 | changed_by text DEFAULT CURRENT_USER |
| 1883 | ); |
| 1884 | |
| 1885 | |
| 1886 | ALTER TABLE public.code_history OWNER TO danq; |
| 1887 | |
| 1888 | -- |
| 1889 | -- Name: code_history_id_seq; Type: SEQUENCE; Schema: public; Owner: danq |
| 1890 | -- |
| 1891 | |
| 1892 | CREATE SEQUENCE public.code_history_id_seq |
| 1893 | AS integer |
| 1894 | START WITH 1 |
| 1895 | INCREMENT BY 1 |
| 1896 | NO MINVALUE |
| 1897 | NO MAXVALUE |
| 1898 | CACHE 1; |
| 1899 | |
| 1900 | |
| 1901 | ALTER TABLE public.code_history_id_seq OWNER TO danq; |
| 1902 | |
| 1903 | -- |
| 1904 | -- Name: code_history_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: danq |
| 1905 | -- |
| 1906 | |
| 1907 | ALTER SEQUENCE public.code_history_id_seq OWNED BY public.code_history.id; |
| 1908 | |
| 1909 | |
| 1910 | -- |
| 1911 | -- Name: exceptions; Type: TABLE; Schema: public; Owner: danq |
| 1912 | -- |
| 1913 | |
| 1914 | CREATE TABLE public.exceptions ( |
| 1915 | id integer NOT NULL, |
| 1916 | error_type text NOT NULL, |
| 1917 | message text NOT NULL, |
| 1918 | traceback text, |
| 1919 | context jsonb DEFAULT '{}'::jsonb, |
| 1920 | function_name text, |
| 1921 | occurred_at timestamp with time zone DEFAULT now(), |
| 1922 | resolved boolean DEFAULT false |
| 1923 | ); |
| 1924 | |
| 1925 | |
| 1926 | ALTER TABLE public.exceptions OWNER TO danq; |
| 1927 | |
| 1928 | -- |
| 1929 | -- Name: exceptions_id_seq; Type: SEQUENCE; Schema: public; Owner: danq |
| 1930 | -- |
| 1931 | |
| 1932 | CREATE SEQUENCE public.exceptions_id_seq |
| 1933 | AS integer |
| 1934 | START WITH 1 |
| 1935 | INCREMENT BY 1 |
| 1936 | NO MINVALUE |
| 1937 | NO MAXVALUE |
| 1938 | CACHE 1; |
| 1939 | |
| 1940 | |
| 1941 | ALTER TABLE public.exceptions_id_seq OWNER TO danq; |
| 1942 | |
| 1943 | -- |
| 1944 | -- Name: exceptions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: danq |
| 1945 | -- |
| 1946 | |
| 1947 | ALTER SEQUENCE public.exceptions_id_seq OWNED BY public.exceptions.id; |
| 1948 | |
| 1949 | |
| 1950 | -- |
| 1951 | -- Name: jobs; Type: TABLE; Schema: public; Owner: danq |
| 1952 | -- |
| 1953 | |
| 1954 | CREATE TABLE public.jobs ( |
| 1955 | id integer NOT NULL, |
| 1956 | type text NOT NULL, |
| 1957 | payload jsonb DEFAULT '{}'::jsonb, |
| 1958 | status text DEFAULT 'pending'::text, |
| 1959 | result jsonb, |
| 1960 | attempts integer DEFAULT 0, |
| 1961 | created_at timestamp with time zone DEFAULT now(), |
| 1962 | started_at timestamp with time zone, |
| 1963 | completed_at timestamp with time zone, |
| 1964 | CONSTRAINT jobs_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'processing'::text, 'completed'::text, 'failed'::text]))) |
| 1965 | ); |
| 1966 | |
| 1967 | |
| 1968 | ALTER TABLE public.jobs OWNER TO danq; |
| 1969 | |
| 1970 | -- |
| 1971 | -- Name: jobs_id_seq; Type: SEQUENCE; Schema: public; Owner: danq |
| 1972 | -- |
| 1973 | |
| 1974 | CREATE SEQUENCE public.jobs_id_seq |
| 1975 | AS integer |
| 1976 | START WITH 1 |
| 1977 | INCREMENT BY 1 |
| 1978 | NO MINVALUE |
| 1979 | NO MAXVALUE |
| 1980 | CACHE 1; |
| 1981 | |
| 1982 | |
| 1983 | ALTER TABLE public.jobs_id_seq OWNER TO danq; |
| 1984 | |
| 1985 | -- |
| 1986 | -- Name: jobs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: danq |
| 1987 | -- |
| 1988 | |
| 1989 | ALTER SEQUENCE public.jobs_id_seq OWNED BY public.jobs.id; |
| 1990 | |
| 1991 | |
| 1992 | -- |
| 1993 | -- Name: routes; Type: TABLE; Schema: public; Owner: danq |
| 1994 | -- |
| 1995 | |
| 1996 | CREATE TABLE public.routes ( |
| 1997 | id integer NOT NULL, |
| 1998 | method text NOT NULL, |
| 1999 | path_pattern text NOT NULL, |
| 2000 | handler text NOT NULL, |
| 2001 | description text |
| 2002 | ); |
| 2003 | |
| 2004 | |
| 2005 | ALTER TABLE public.routes OWNER TO danq; |
| 2006 | |
| 2007 | -- |
| 2008 | -- Name: routes_id_seq; Type: SEQUENCE; Schema: public; Owner: danq |
| 2009 | -- |
| 2010 | |
| 2011 | CREATE SEQUENCE public.routes_id_seq |
| 2012 | AS integer |
| 2013 | START WITH 1 |
| 2014 | INCREMENT BY 1 |
| 2015 | NO MINVALUE |
| 2016 | NO MAXVALUE |
| 2017 | CACHE 1; |
| 2018 | |
| 2019 | |
| 2020 | ALTER TABLE public.routes_id_seq OWNER TO danq; |
| 2021 | |
| 2022 | -- |
| 2023 | -- Name: routes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: danq |
| 2024 | -- |
| 2025 | |
| 2026 | ALTER SEQUENCE public.routes_id_seq OWNED BY public.routes.id; |
| 2027 | |
| 2028 | |
| 2029 | -- |
| 2030 | -- Name: sessions; Type: TABLE; Schema: public; Owner: danq |
| 2031 | -- |
| 2032 | |
| 2033 | CREATE TABLE public.sessions ( |
| 2034 | id uuid DEFAULT public.uuid_generate_v4() NOT NULL, |
| 2035 | data jsonb DEFAULT '{}'::jsonb, |
| 2036 | created_at timestamp with time zone DEFAULT now(), |
| 2037 | expires_at timestamp with time zone DEFAULT (now() + '24:00:00'::interval), |
| 2038 | last_access timestamp with time zone DEFAULT now() |
| 2039 | ); |
| 2040 | |
| 2041 | |
| 2042 | ALTER TABLE public.sessions OWNER TO danq; |
| 2043 | |
| 2044 | -- |
| 2045 | -- Name: code_files id; Type: DEFAULT; Schema: public; Owner: danq |
| 2046 | -- |
| 2047 | |
| 2048 | ALTER TABLE ONLY public.code_files ALTER COLUMN id SET DEFAULT nextval('public.code_files_id_seq'::regclass); |
| 2049 | |
| 2050 | |
| 2051 | -- |
| 2052 | -- Name: code_history id; Type: DEFAULT; Schema: public; Owner: danq |
| 2053 | -- |
| 2054 | |
| 2055 | ALTER TABLE ONLY public.code_history ALTER COLUMN id SET DEFAULT nextval('public.code_history_id_seq'::regclass); |
| 2056 | |
| 2057 | |
| 2058 | -- |
| 2059 | -- Name: exceptions id; Type: DEFAULT; Schema: public; Owner: danq |
| 2060 | -- |
| 2061 | |
| 2062 | ALTER TABLE ONLY public.exceptions ALTER COLUMN id SET DEFAULT nextval('public.exceptions_id_seq'::regclass); |
| 2063 | |
| 2064 | |
| 2065 | -- |
| 2066 | -- Name: jobs id; Type: DEFAULT; Schema: public; Owner: danq |
| 2067 | -- |
| 2068 | |
| 2069 | ALTER TABLE ONLY public.jobs ALTER COLUMN id SET DEFAULT nextval('public.jobs_id_seq'::regclass); |
| 2070 | |
| 2071 | |
| 2072 | -- |
| 2073 | -- Name: routes id; Type: DEFAULT; Schema: public; Owner: danq |
| 2074 | -- |
| 2075 | |
| 2076 | ALTER TABLE ONLY public.routes ALTER COLUMN id SET DEFAULT nextval('public.routes_id_seq'::regclass); |
| 2077 | |
| 2078 | |
| 2079 | -- |
| 2080 | -- Data for Name: blobs; Type: TABLE DATA; Schema: public; Owner: danq |
| 2081 | -- |
| 2082 | |
| 2083 | COPY public.blobs (id, filename, mimetype, data, size, metadata, created_at, updated_at) FROM stdin; |
| 2084 | 7d92e779-4534-451c-9a6b-f4b2208fb60c welcome.txt text/plain \\x57656c636f6d6520746f20506f7374426c6f62202d20506f737467726553514c20706f776572732065766572797468696e6721 51 {"type": "system", "description": "Welcome message"} 2025-09-22 19:48:50.240894-05 2025-09-22 19:48:50.240894-05 |
| 2085 | 98d08b53-fe15-42db-95c5-02ccb877bf6e test.txt text/plain \\x48656c6c6f20576f726c6421 12 {} 2025-09-22 19:50:09.25042-05 2025-09-22 19:50:09.25042-05 |
| 2086 | \. |
| 2087 | |
| 2088 | |
| 2089 | -- |
| 2090 | -- Data for Name: cache; Type: TABLE DATA; Schema: public; Owner: danq |
| 2091 | -- |
| 2092 | |
| 2093 | COPY public.cache (key, value, expires_at, created_at) FROM stdin; |
| 2094 | \. |
| 2095 | |
| 2096 | |
| 2097 | -- |
| 2098 | -- Data for Name: circuit_breakers; Type: TABLE DATA; Schema: public; Owner: danq |
| 2099 | -- |
| 2100 | |
| 2101 | COPY public.circuit_breakers (function_name, state, failure_count, last_failure, last_success) FROM stdin; |
| 2102 | \. |
| 2103 | |
| 2104 | |
| 2105 | -- |
| 2106 | -- Data for Name: code_files; Type: TABLE DATA; Schema: public; Owner: danq |
| 2107 | -- |
| 2108 | |
| 2109 | COPY public.code_files (id, path, content, language, version, is_active, created_at, updated_at, executed_at, metadata) FROM stdin; |
| 2110 | 2 functions/ask.sql CREATE OR REPLACE FUNCTION public.ask(question text)\n RETURNS jsonb\n LANGUAGE plpgsql\nAS $function$\nDECLARE\n sql_query text;\n result jsonb;\n word_count integer;\nBEGIN\n -- Normalize input\n question := lower(trim(question));\n\n -- Simple pattern matching for common queries\n sql_query := CASE\n -- "show blobs" or "list blobs"\n WHEN question ~ '^(show|list)\\s+blobs?$' THEN\n 'SELECT * FROM blobs ORDER BY created_at DESC LIMIT 10'\n\n -- "count blobs"\n WHEN question ~ '^count\\s+blobs?$' THEN\n 'SELECT COUNT(*) as count FROM blobs'\n\n -- "show jobs" or "list jobs"\n WHEN question ~ '^(show|list)\\s+jobs?$' THEN\n 'SELECT * FROM jobs ORDER BY created_at DESC LIMIT 10'\n\n -- "pending jobs"\n WHEN question ~ '^pending\\s+jobs?$' THEN\n 'SELECT * FROM jobs WHERE status = ''pending'''\n\n -- "failed jobs"\n WHEN question ~ '^failed\\s+jobs?$' THEN\n 'SELECT * FROM jobs WHERE status = ''failed'''\n\n -- "completed jobs"\n WHEN question ~ '^completed\\s+jobs?$' THEN\n 'SELECT * FROM jobs WHERE status = ''completed'''\n\n -- "show stats" or "statistics"\n WHEN question ~ '^(show\\s+)?stats|statistics$' THEN\n 'SELECT\n (SELECT COUNT(*) FROM blobs) as blob_count,\n (SELECT COUNT(*) FROM jobs) as job_count,\n (SELECT COUNT(*) FROM sessions WHERE expires_at > now()) as active_sessions'\n\n -- "biggest blob" or "largest file"\n WHEN question ~ '^(biggest|largest)\\s+(blob|file)$' THEN\n 'SELECT * FROM blobs ORDER BY size DESC LIMIT 1'\n\n -- "newest blob" or "latest file"\n WHEN question ~ '^(newest|latest)\\s+(blob|file)$' THEN\n 'SELECT * FROM blobs ORDER BY created_at DESC LIMIT 1'\n\n -- "show 5 blobs" (with number)\n WHEN question ~ '^show\\s+(\\d+)\\s+blobs?$' THEN\n format('SELECT * FROM blobs ORDER BY created_at DESC LIMIT %s',\n (regexp_match(question, 'show\\s+(\\d+)', 'i'))[1])\n\n ELSE\n NULL\n END;\n\n IF sql_query IS NULL THEN\n RETURN jsonb_build_object(\n 'error', 'Could not understand query',\n 'original', question,\n 'hint', 'Try: "show blobs", "count jobs", "failed jobs", "show stats"'\n );\n END IF;\n\n -- Execute the query\n BEGIN\n EXECUTE 'SELECT jsonb_agg(row_to_json(t)) FROM (' || sql_query || ') t' INTO result;\n\n IF result IS NULL THEN\n result := '[]'::jsonb;\n END IF;\n\n RETURN jsonb_build_object(\n 'success', true,\n 'sql', sql_query,\n 'data', result\n );\n EXCEPTION WHEN OTHERS THEN\n RETURN jsonb_build_object(\n 'error', SQLERRM,\n 'sql', sql_query,\n 'original', question\n );\n END;\nEND;\n$function$\n sql 1 t 2025-09-22 20:00:58.479645-05 2025-09-22 20:00:58.479645-05 \N {"type": "function", "hot_reload": true} |
| 2111 | 3 functions/process_job.sql CREATE OR REPLACE FUNCTION public.process_job(p_job_id integer)\n RETURNS void\n LANGUAGE plpgsql\nAS $function$\nDECLARE\n v_job record;\n v_result jsonb;\nBEGIN\n -- Get job details\n SELECT * INTO v_job FROM jobs WHERE id = p_job_id FOR UPDATE;\n\n IF NOT FOUND OR v_job.status != 'pending' THEN\n RETURN;\n END IF;\n\n -- Mark as processing\n UPDATE jobs\n SET status = 'processing', started_at = now()\n WHERE id = p_job_id;\n\n -- Process different job types\n BEGIN\n CASE v_job.type\n WHEN 'cleanup' THEN\n -- Clean old sessions and cache\n DELETE FROM sessions WHERE expires_at < now();\n DELETE FROM cache WHERE expires_at < now();\n v_result := jsonb_build_object(\n 'status', 'success',\n 'message', 'Cleanup completed'\n );\n\n WHEN 'compute' THEN\n -- Simple computation\n v_result := jsonb_build_object(\n 'result', (COALESCE((v_job.payload->>'value')::integer, 0) * 2)\n );\n\n WHEN 'analyze_blobs' THEN\n -- Analyze blob storage\n v_result := jsonb_build_object(\n 'total_blobs', (SELECT COUNT(*) FROM blobs),\n 'total_size', (SELECT COALESCE(SUM(size), 0) FROM blobs),\n 'avg_size', (SELECT COALESCE(AVG(size), 0) FROM blobs)\n );\n\n ELSE\n -- Unknown job type\n v_result := jsonb_build_object(\n 'error', format('Unknown job type: %s', v_job.type)\n );\n END CASE;\n\n -- Mark as completed\n UPDATE jobs\n SET status = 'completed',\n result = v_result,\n completed_at = now()\n WHERE id = p_job_id;\n\n EXCEPTION WHEN OTHERS THEN\n -- Mark as failed\n UPDATE jobs\n SET status = 'failed',\n result = jsonb_build_object(\n 'error', SQLERRM,\n 'detail', SQLSTATE\n ),\n attempts = attempts + 1\n WHERE id = p_job_id;\n\n -- Log exception\n INSERT INTO exceptions (error_type, message, context, function_name)\n VALUES (\n SQLSTATE,\n SQLERRM,\n jsonb_build_object('job_id', p_job_id, 'job_type', v_job.type),\n 'process_job'\n );\n END;\nEND;\n$function$\n sql 1 t 2025-09-22 20:00:58.479645-05 2025-09-22 20:00:58.479645-05 \N {"type": "function", "hot_reload": true} |
| 2112 | 4 functions/cache_get.sql CREATE OR REPLACE FUNCTION public.cache_get(p_key text)\n RETURNS jsonb\n LANGUAGE plpgsql\nAS $function$\nBEGIN\n -- Clean expired entries\n DELETE FROM cache WHERE expires_at < now();\n\n RETURN (SELECT value FROM cache WHERE key = p_key);\nEND;\n$function$\n sql 1 t 2025-09-22 20:00:58.479645-05 2025-09-22 20:00:58.479645-05 \N {"type": "function", "hot_reload": true} |
| 2113 | 5 functions/cache_set.sql CREATE OR REPLACE FUNCTION public.cache_set(p_key text, p_value jsonb, p_ttl_seconds integer DEFAULT 3600)\n RETURNS void\n LANGUAGE plpgsql\nAS $function$\nBEGIN\n INSERT INTO cache (key, value, expires_at)\n VALUES (p_key, p_value, now() + (p_ttl_seconds || ' seconds')::interval)\n ON CONFLICT (key) DO UPDATE\n SET value = EXCLUDED.value,\n expires_at = EXCLUDED.expires_at,\n created_at = now();\nEND;\n$function$\n sql 1 t 2025-09-22 20:00:58.479645-05 2025-09-22 20:00:58.479645-05 \N {"type": "function", "hot_reload": true} |
| 2114 | 1 functions/handle_request.sql CREATE OR REPLACE FUNCTION public.handle_request(p_method text, p_path text, p_headers jsonb DEFAULT '{}'::jsonb, p_body jsonb DEFAULT '{}'::jsonb, p_session_id uuid DEFAULT NULL::uuid)\n RETURNS jsonb\n LANGUAGE plpgsql\nAS $function$\nDECLARE\n v_response jsonb;\n v_route record;\n v_blob_id uuid;\n v_job_id integer;\n v_stats jsonb;\n v_html text;\n v_blobs jsonb;\n v_jobs jsonb;\nBEGIN\n -- Default response\n v_response := jsonb_build_object(\n 'status', 404,\n 'body', jsonb_build_object('error', 'Not found')\n );\n\n -- Route: GET / (HTML Dashboard)\n IF p_method = 'GET' AND p_path = '/' THEN\n -- Get stats\n SELECT jsonb_build_object(\n 'blob_count', (SELECT COUNT(*) FROM blobs),\n 'total_size', COALESCE((SELECT SUM(size) FROM blobs), 0),\n 'jobs_pending', (SELECT COUNT(*) FROM jobs WHERE status = 'pending'),\n 'jobs_completed', (SELECT COUNT(*) FROM jobs WHERE status = 'completed'),\n 'jobs_failed', (SELECT COUNT(*) FROM jobs WHERE status = 'failed'),\n 'total_exceptions', (SELECT COUNT(*) FROM exceptions),\n 'active_sessions', (SELECT COUNT(*) FROM sessions WHERE expires_at > now())\n ) INTO v_stats;\n\n -- Get recent blobs\n SELECT COALESCE(jsonb_agg(\n jsonb_build_object(\n 'id', id,\n 'filename', filename,\n 'size', size,\n 'created_at', created_at\n ) ORDER BY created_at DESC\n ), '[]'::jsonb)\n INTO v_blobs\n FROM (SELECT * FROM blobs ORDER BY created_at DESC LIMIT 5) b;\n\n -- Get recent jobs\n SELECT COALESCE(jsonb_agg(\n jsonb_build_object(\n 'id', id,\n 'type', type,\n 'status', status,\n 'created_at', created_at\n ) ORDER BY created_at DESC\n ), '[]'::jsonb)\n INTO v_jobs\n FROM (SELECT * FROM jobs ORDER BY created_at DESC LIMIT 5) j;\n\n -- Build HTML\n v_html := format('<!DOCTYPE html>\n<html>\n<head>\n <title>PostBlob - PostgreSQL Does Everything!</title>\n <meta charset="utf-8">\n <meta name="viewport" content="width=device-width, initial-scale=1">\n <style>\n * { margin: 0; padding: 0; box-sizing: border-box; }\n body {\n font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;\n line-height: 1.6;\n background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);\n min-height: 100vh;\n padding: 20px;\n }\n .container {\n max-width: 1200px;\n margin: 0 auto;\n }\n header {\n background: white;\n border-radius: 12px;\n padding: 30px;\n margin-bottom: 30px;\n box-shadow: 0 10px 40px rgba(0,0,0,0.1);\n }\n h1 {\n color: #333;\n margin-bottom: 10px;\n font-size: 2.5em;\n }\n .subtitle {\n color: #666;\n font-size: 1.2em;\n }\n .grid {\n display: grid;\n grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));\n gap: 20px;\n margin-bottom: 30px;\n }\n .card {\n background: white;\n border-radius: 12px;\n padding: 25px;\n box-shadow: 0 10px 40px rgba(0,0,0,0.1);\n }\n .card h2 {\n color: #333;\n margin-bottom: 20px;\n font-size: 1.5em;\n border-bottom: 2px solid #667eea;\n padding-bottom: 10px;\n }\n .stat-grid {\n display: grid;\n grid-template-columns: repeat(auto-fit, minmax(120px, 1fr));\n gap: 15px;\n margin-bottom: 20px;\n }\n .stat {\n text-align: center;\n padding: 15px;\n background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);\n border-radius: 8px;\n color: white;\n }\n .stat-value {\n font-size: 2em;\n font-weight: bold;\n }\n .stat-label {\n font-size: 0.9em;\n opacity: 0.9;\n }\n .list-item {\n padding: 12px;\n margin: 8px 0;\n background: #f8f9fa;\n border-radius: 6px;\n border-left: 4px solid #667eea;\n }\n .list-item strong {\n color: #333;\n }\n .status {\n display: inline-block;\n padding: 2px 8px;\n border-radius: 4px;\n font-size: 0.85em;\n font-weight: bold;\n }\n .status.pending { background: #ffc107; color: #333; }\n .status.completed { background: #28a745; color: white; }\n .status.failed { background: #dc3545; color: white; }\n .status.processing { background: #17a2b8; color: white; }\n .endpoint {\n padding: 15px;\n margin: 10px 0;\n background: #f8f9fa;\n border-radius: 6px;\n border-left: 4px solid #764ba2;\n }\n .method {\n display: inline-block;\n padding: 4px 8px;\n border-radius: 4px;\n font-weight: bold;\n font-size: 0.85em;\n margin-right: 10px;\n }\n .method.GET { background: #28a745; color: white; }\n .method.POST { background: #007bff; color: white; }\n .method.DELETE { background: #dc3545; color: white; }\n .path {\n font-family: "Monaco", "Menlo", monospace;\n color: #333;\n font-weight: bold;\n }\n .description {\n color: #666;\n margin-top: 5px;\n }\n .query-box {\n margin-top: 20px;\n }\n input[type="text"] {\n width: 100%%;\n padding: 12px;\n border: 2px solid #ddd;\n border-radius: 6px;\n font-size: 1em;\n margin-bottom: 10px;\n }\n input[type="text"]:focus {\n outline: none;\n border-color: #667eea;\n }\n button {\n background: linear-gradient(135deg, #667eea 0%%, #764ba2 100%%);\n color: white;\n border: none;\n padding: 12px 24px;\n border-radius: 6px;\n font-size: 1em;\n cursor: pointer;\n transition: transform 0.2s;\n }\n button:hover {\n transform: translateY(-2px);\n }\n #queryResult {\n margin-top: 15px;\n padding: 15px;\n background: #f8f9fa;\n border-radius: 6px;\n font-family: monospace;\n white-space: pre-wrap;\n display: none;\n }\n .footer {\n text-align: center;\n color: white;\n margin-top: 40px;\n }\n .footer a {\n color: white;\n text-decoration: none;\n font-weight: bold;\n }\n </style>\n</head>\n<body>\n <div class="container">\n <header>\n <h1>🎯 PostBlob</h1>\n <p class="subtitle">PostgreSQL Does Everything - Web UI</p>\n </header>\n\n <div class="grid">\n <div class="card">\n <h2>📊 Statistics</h2>\n <div class="stat-grid">\n <div class="stat">\n <div class="stat-value">%s</div>\n <div class="stat-label">Blobs</div>\n </div>\n <div class="stat">\n <div class="stat-value">%s</div>\n <div class="stat-label">Pending Jobs</div>\n </div>\n <div class="stat">\n <div class="stat-value">%s</div>\n <div class="stat-label">Completed</div>\n </div>\n <div class="stat">\n <div class="stat-value">%s</div>\n <div class="stat-label">Sessions</div>\n </div>\n </div>\n <div class="list-item">\n <strong>Total Storage:</strong> %s bytes\n </div>\n <div class="list-item">\n <strong>Exceptions:</strong> %s\n </div>\n </div>\n\n <div class="card">\n <h2>💬 Natural Language Query</h2>\n <div class="query-box">\n <input type="text" id="queryInput" placeholder="Try: show blobs, failed jobs, show stats" />\n <button onclick="runQuery()">Ask Database</button>\n <div id="queryResult"></div>\n </div>\n <script>\n async function runQuery() {\n const input = document.getElementById("queryInput").value;\n const result = document.getElementById("queryResult");\n\n try {\n const response = await fetch("/ask?q=" + encodeURIComponent(input));\n const data = await response.json();\n result.style.display = "block";\n result.textContent = JSON.stringify(data, null, 2);\n } catch (e) {\n result.style.display = "block";\n result.textContent = "Error: " + e.message;\n }\n }\n\n document.getElementById("queryInput").addEventListener("keypress", (e) => {\n if (e.key === "Enter") runQuery();\n });\n </script>\n </div>\n </div>\n\n <div class="grid">\n <div class="card">\n <h2>📁 Recent Blobs</h2>\n %s\n <div style="margin-top: 20px;">\n <a href="/blobs" style="color: #667eea;">View All Blobs →</a>\n </div>\n </div>\n\n <div class="card">\n <h2>⚡ Recent Jobs</h2>\n %s\n <div style="margin-top: 20px;">\n <a href="/jobs" style="color: #667eea;">View All Jobs →</a>\n </div>\n </div>\n </div>\n\n <div class="card">\n <h2>🔥 Hot Deploy Code Editor</h2>\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/code</span>\n <div class="description">Live code editor - Edit functions while the server is running!</div>\n </div>\n <div style="margin-top: 20px;">\n <a href="/code" style="display: inline-block; background: linear-gradient(135deg, #ff6b6b 0%%, #feca57 100%%); color: white; padding: 12px 24px; border-radius: 6px; text-decoration: none; font-weight: bold; box-shadow: 0 4px 15px rgba(0,0,0,0.2); transition: transform 0.2s;">🔥 Open Code Editor</a>\n </div>\n </div>\n\n <div class="card">\n <h2>🚀 API Endpoints</h2>\n\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/stats</span>\n <div class="description">Get database statistics</div>\n </div>\n\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/blobs</span>\n <div class="description">List all blobs</div>\n </div>\n\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/blob/{id}</span>\n <div class="description">Get specific blob by ID</div>\n </div>\n\n <div class="endpoint">\n <span class="method POST">POST</span>\n <span class="path">/blob</span>\n <div class="description">Upload new blob (JSON: filename, data, mimetype)</div>\n </div>\n\n <div class="endpoint">\n <span class="method DELETE">DELETE</span>\n <span class="path">/blob/{id}</span>\n <div class="description">Delete a blob</div>\n </div>\n\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/jobs</span>\n <div class="description">List all jobs</div>\n </div>\n\n <div class="endpoint">\n <span class="method POST">POST</span>\n <span class="path">/job</span>\n <div class="description">Create new job (JSON: type, payload)</div>\n </div>\n\n <div class="endpoint">\n <span class="method GET">GET</span>\n <span class="path">/ask?q={query}</span>\n <div class="description">Natural language database query</div>\n </div>\n </div>\n\n <div class="footer">\n <p>Powered by PostgreSQL • <a href="https://github.com/yourname/postblob">GitHub</a></p>\n </div>\n </div>\n</body>\n</html>',\n v_stats->>'blob_count',\n v_stats->>'jobs_pending',\n v_stats->>'jobs_completed',\n v_stats->>'active_sessions',\n v_stats->>'total_size',\n v_stats->>'total_exceptions',\n CASE\n WHEN jsonb_array_length(v_blobs) = 0 THEN '<p style="color: #999;">No blobs yet</p>'\n ELSE (\n SELECT string_agg(\n format('<div class="list-item"><strong>%s</strong><br>Size: %s bytes<br>%s</div>',\n b->>'filename',\n b->>'size',\n to_char((b->>'created_at')::timestamptz, 'YYYY-MM-DD HH24:MI')\n ), ''\n )\n FROM jsonb_array_elements(v_blobs) b\n )\n END,\n CASE\n WHEN jsonb_array_length(v_jobs) = 0 THEN '<p style="color: #999;">No jobs yet</p>'\n ELSE (\n SELECT string_agg(\n format('<div class="list-item"><strong>%s</strong><br><span class="status %s">%s</span><br>%s</div>',\n j->>'type',\n j->>'status',\n j->>'status',\n to_char((j->>'created_at')::timestamptz, 'YYYY-MM-DD HH24:MI')\n ), ''\n )\n FROM jsonb_array_elements(v_jobs) j\n )\n END\n );\n\n v_response := jsonb_build_object(\n 'status', 200,\n 'headers', jsonb_build_object('Content-Type', 'text/html'),\n 'body', v_html,\n 'html', true\n );\n\n -- Route: GET /ask (Natural Language Query)\n ELSIF p_method = 'GET' AND p_path LIKE '/ask%' THEN\n DECLARE\n v_query text;\n BEGIN\n -- Extract query parameter (simple parsing)\n v_query := substring(p_path from 'q=([^&]+)');\n v_query := replace(v_query, '%20', ' ');\n\n IF v_query IS NOT NULL THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', ask(v_query)\n );\n ELSE\n v_response := jsonb_build_object(\n 'status', 400,\n 'body', jsonb_build_object('error', 'Missing query parameter')\n );\n END IF;\n END;\n\n -- Route: GET /stats\n ELSIF p_method = 'GET' AND p_path = '/stats' THEN\n SELECT jsonb_build_object(\n 'blob_count', (SELECT COUNT(*) FROM blobs),\n 'total_size', COALESCE((SELECT SUM(size) FROM blobs), 0),\n 'jobs_completed', (SELECT COUNT(*) FROM jobs WHERE status = 'completed'),\n 'total_exceptions', (SELECT COUNT(*) FROM exceptions),\n 'active_sessions', (SELECT COUNT(*) FROM sessions WHERE expires_at > now())\n ) INTO v_stats;\n\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', v_stats\n );\n\n -- Route: GET /blobs\n ELSIF p_method = 'GET' AND p_path = '/blobs' THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', (\n SELECT COALESCE(jsonb_agg(\n jsonb_build_object(\n 'id', id,\n 'filename', filename,\n 'size', size,\n 'mimetype', mimetype,\n 'created_at', created_at\n ) ORDER BY created_at DESC\n ), '[]'::jsonb)\n FROM blobs\n LIMIT 100\n )\n );\n\n -- Route: GET /blob/{id}\n ELSIF p_method = 'GET' AND p_path LIKE '/blob/%' THEN\n v_blob_id := substring(p_path from '/blob/(.+)')::uuid;\n\n SELECT jsonb_build_object(\n 'id', id,\n 'filename', filename,\n 'size', size,\n 'mimetype', mimetype,\n 'metadata', metadata,\n 'created_at', created_at\n ) INTO v_response\n FROM blobs\n WHERE id = v_blob_id;\n\n IF v_response IS NOT NULL THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', v_response\n );\n ELSE\n v_response := jsonb_build_object(\n 'status', 404,\n 'body', jsonb_build_object('error', 'Blob not found')\n );\n END IF;\n\n -- Route: POST /blob\n ELSIF p_method = 'POST' AND p_path = '/blob' THEN\n INSERT INTO blobs (filename, mimetype, data, size, metadata)\n VALUES (\n COALESCE(p_body->>'filename', 'unnamed'),\n COALESCE(p_body->>'mimetype', 'application/octet-stream'),\n decode(p_body->>'data', 'base64'),\n length(decode(p_body->>'data', 'base64')),\n COALESCE(p_body->'metadata', '{}'::jsonb)\n )\n RETURNING id INTO v_blob_id;\n\n v_response := jsonb_build_object(\n 'status', 201,\n 'body', jsonb_build_object(\n 'id', v_blob_id,\n 'url', '/blob/' || v_blob_id\n )\n );\n\n -- Route: DELETE /blob/{id}\n ELSIF p_method = 'DELETE' AND p_path LIKE '/blob/%' THEN\n v_blob_id := substring(p_path from '/blob/(.+)')::uuid;\n\n DELETE FROM blobs WHERE id = v_blob_id;\n\n IF FOUND THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', jsonb_build_object('deleted', v_blob_id)\n );\n ELSE\n v_response := jsonb_build_object(\n 'status', 404,\n 'body', jsonb_build_object('error', 'Blob not found')\n );\n END IF;\n\n -- Route: GET /jobs\n ELSIF p_method = 'GET' AND p_path = '/jobs' THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', (\n SELECT COALESCE(jsonb_agg(\n jsonb_build_object(\n 'id', id,\n 'type', type,\n 'status', status,\n 'created_at', created_at,\n 'completed_at', completed_at\n ) ORDER BY created_at DESC\n ), '[]'::jsonb)\n FROM jobs\n LIMIT 100\n )\n );\n\n -- Route: POST /job\n ELSIF p_method = 'POST' AND p_path = '/job' THEN\n INSERT INTO jobs (type, payload)\n VALUES (\n COALESCE(p_body->>'type', 'default'),\n COALESCE(p_body->'payload', '{}'::jsonb)\n )\n RETURNING id INTO v_job_id;\n\n -- Notify job worker\n PERFORM pg_notify('new_job', v_job_id::text);\n\n v_response := jsonb_build_object(\n 'status', 201,\n 'body', jsonb_build_object(\n 'job_id', v_job_id,\n 'status', 'pending'\n )\n );\n\n -- Route: GET /job/{id}\n ELSIF p_method = 'GET' AND p_path LIKE '/job/%' THEN\n v_job_id := substring(p_path from '/job/(\\d+)')::integer;\n\n SELECT jsonb_build_object(\n 'id', id,\n 'type', type,\n 'status', status,\n 'payload', payload,\n 'result', result,\n 'created_at', created_at,\n 'completed_at', completed_at\n ) INTO v_response\n FROM jobs\n WHERE id = v_job_id;\n\n IF v_response IS NOT NULL THEN\n v_response := jsonb_build_object(\n 'status', 200,\n 'body', v_response\n );\n ELSE\n v_response := jsonb_build_object(\n 'status', 404,\n 'body', jsonb_build_object('error', 'Job not found')\n );\n END IF;\n END IF;\n\n RETURN v_response;\nEND;\n$function$\n\n sql 5 t 2025-09-22 20:00:58.476063-05 2025-09-22 20:46:26.771355-05 \N {"type": "function", "hot_reload": true} |
| 2115 | 6 ui/code_editor.html <!DOCTYPE html>\n<html>\n<head>\n <title>PostBlob Code Editor</title>\n <style>\n * { margin: 0; padding: 0; box-sizing: border-box; }\n body { font-family: monospace; background: #1e1e1e; color: #d4d4d4; }\n .container { display: flex; height: 100vh; }\n .sidebar { width: 250px; background: #252526; border-right: 1px solid #3e3e42; overflow-y: auto; }\n .editor-pane { flex: 1; display: flex; flex-direction: column; }\n .toolbar { background: #2d2d30; padding: 10px; border-bottom: 1px solid #3e3e42; }\n .editor { flex: 1; padding: 20px; overflow: auto; }\n textarea { width: 100%; height: 400px; background: #1e1e1e; color: #d4d4d4; border: 1px solid #3e3e42; padding: 10px; font-family: monospace; font-size: 14px; }\n .output { background: #1e1e1e; border-top: 1px solid #3e3e42; padding: 20px; height: 200px; overflow-y: auto; }\n .file-item { padding: 8px 15px; cursor: pointer; border-bottom: 1px solid #3e3e42; }\n .file-item:hover { background: #2d2d30; }\n .file-item.active { background: #094771; }\n button { background: #007acc; color: white; border: none; padding: 8px 16px; margin-right: 10px; cursor: pointer; }\n button:hover { background: #005a9e; }\n .status { padding: 5px 10px; background: #007acc; color: white; display: inline-block; }\n pre { white-space: pre-wrap; word-wrap: break-word; }\n </style>\n</head>\n<body>\n <div class="container">\n <div class="sidebar">\n <h3 style="padding: 15px; border-bottom: 1px solid #3e3e42;">Code Files</h3>\n <div id="fileList"></div>\n </div>\n <div class="editor-pane">\n <div class="toolbar">\n <button onclick="saveCode()">Save</button>\n <button onclick="executeCode()">Execute</button>\n <button onclick="hotReload()">Hot Reload</button>\n <button onclick="newFile()">New File</button>\n <span class="status" id="status">Ready</span>\n </div>\n <div class="editor">\n <input type="text" id="filePath" placeholder="File path..." style="width: 100%; padding: 5px; margin-bottom: 10px; background: #1e1e1e; color: #d4d4d4; border: 1px solid #3e3e42;">\n <textarea id="codeEditor" placeholder="-- Write your SQL or PL/pgSQL code here..."></textarea>\n </div>\n <div class="output">\n <h4 style="margin-bottom: 10px;">Output:</h4>\n <pre id="output"></pre>\n </div>\n </div>\n </div>\n\n <script>\n let currentFile = null;\n\n async function loadFiles() {\n try {\n const response = await fetch("/code/list");\n const files = await response.json();\n const fileList = document.getElementById("fileList");\n\n if (!files || files.error) {\n fileList.innerHTML = "<div>No files found</div>";\n return;\n }\n\n fileList.innerHTML = files.map(f => {\n return `<div class="file-item" data-path="${f.path}">${f.path}<br><small>${f.size} bytes | v${f.version}</small></div>`;\n }).join("");\n\n // Add click handlers\n document.querySelectorAll(".file-item").forEach(item => {\n item.addEventListener("click", function() {\n loadFile(this.getAttribute("data-path"), this);\n });\n });\n } catch (e) {\n console.error("Error loading files:", e);\n document.getElementById("output").textContent = "Error loading files: " + e.message;\n }\n }\n\n async function loadFile(path, element) {\n try {\n currentFile = path;\n document.getElementById("filePath").value = path;\n\n const response = await fetch(`/code/file?path=${encodeURIComponent(path)}`);\n const file = await response.json();\n\n if (file.error) {\n document.getElementById("output").textContent = "Error: " + file.error;\n return;\n }\n\n document.getElementById("codeEditor").value = file.content || "";\n setStatus(`Loaded ${path}`);\n\n // Update active state\n document.querySelectorAll(".file-item").forEach(el => el.classList.remove("active"));\n if (element) element.classList.add("active");\n\n } catch (e) {\n console.error("Error loading file:", e);\n document.getElementById("output").textContent = "Error loading file: " + e.message;\n }\n }\n\n async function saveCode() {\n try {\n const path = document.getElementById("filePath").value;\n const content = document.getElementById("codeEditor").value;\n\n if (!path) {\n alert("Please enter a file path");\n return;\n }\n\n const response = await fetch("/code/save", {\n method: "POST",\n headers: {"Content-Type": "application/json"},\n body: JSON.stringify({path, content})\n });\n\n const result = await response.json();\n document.getElementById("output").textContent = JSON.stringify(result, null, 2);\n setStatus(result.hot_reloaded ? "Saved & Reloaded!" : "Saved!");\n loadFiles();\n } catch (e) {\n console.error("Error saving:", e);\n document.getElementById("output").textContent = "Error saving: " + e.message;\n }\n }\n\n async function executeCode() {\n try {\n const code = document.getElementById("codeEditor").value;\n\n if (!code) {\n alert("No code to execute");\n return;\n }\n\n const response = await fetch("/code/execute", {\n method: "POST",\n headers: {"Content-Type": "application/json"},\n body: JSON.stringify({code, language: "sql"})\n });\n\n const result = await response.json();\n document.getElementById("output").textContent = JSON.stringify(result, null, 2);\n setStatus(result.success ? "Executed!" : "Error!");\n } catch (e) {\n console.error("Error executing:", e);\n document.getElementById("output").textContent = "Error executing: " + e.message;\n }\n }\n\n async function hotReload() {\n try {\n const response = await fetch("/code/reload", {method: "POST"});\n const result = await response.json();\n document.getElementById("output").textContent = JSON.stringify(result, null, 2);\n setStatus("Hot Reloaded!");\n } catch (e) {\n console.error("Error reloading:", e);\n document.getElementById("output").textContent = "Error reloading: " + e.message;\n }\n }\n\n function newFile() {\n document.getElementById("filePath").value = "functions/new_function.sql";\n document.getElementById("codeEditor").value = `-- New function\nCREATE OR REPLACE FUNCTION new_function()\nRETURNS text AS $$\nBEGIN\n RETURN 'Hello from new function!';\nEND;\n$$ LANGUAGE plpgsql;`;\n currentFile = null;\n setStatus("New file");\n }\n\n function setStatus(text) {\n document.getElementById("status").textContent = text;\n }\n\n // Load files on startup\n window.addEventListener("load", loadFiles);\n </script>\n</body>\n</html> html 1 t 2025-09-22 20:00:58.493006-05 2025-09-22 20:00:58.493006-05 \N {"type": "ui", "component": "code_editor"} |
| 2116 | 7 functions/self_modify.sql CREATE OR REPLACE FUNCTION get_version()\nRETURNS text AS $$\nBEGIN\n RETURN 'Version 2.0 - Hot Reloaded!';\nEND;\n$$ LANGUAGE plpgsql; sql 3 t 2025-09-22 20:00:58.496326-05 2025-09-22 20:02:58.560927-05 \N {"type": "function", "self_modifying": true} |
| 2117 | 8 functions/html_encode.sql -- HTML encode function (like PHP's htmlspecialchars)\nCREATE OR REPLACE FUNCTION html_encode(input text)\nRETURNS text AS $func$\nBEGIN\n IF input IS NULL THEN\n RETURN NULL;\n END IF;\n\n RETURN replace(\n replace(\n replace(\n replace(\n replace(input, '&', '&'),\n '<', '<'),\n '>', '>'),\n '"', '"'),\n '''', ''');\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.206989-05 2025-09-22 20:41:10.206989-05 \N {} |
| 2118 | 9 functions/url_encode.sql -- URL encode function (like PHP's urlencode)\nCREATE OR REPLACE FUNCTION url_encode(input text)\nRETURNS text AS $func$\nDECLARE\n result text := '';\n char_code int;\n i int;\nBEGIN\n IF input IS NULL THEN\n RETURN NULL;\n END IF;\n\n FOR i IN 1..length(input) LOOP\n char_code := ascii(substring(input, i, 1));\n\n IF (char_code >= 48 AND char_code <= 57) OR -- 0-9\n (char_code >= 65 AND char_code <= 90) OR -- A-Z\n (char_code >= 97 AND char_code <= 122) OR -- a-z\n char_code IN (45, 46, 95, 126) THEN -- - . _ ~\n result := result || substring(input, i, 1);\n ELSIF char_code = 32 THEN\n result := result || '+';\n ELSE\n result := result || '%' || upper(to_hex(char_code));\n END IF;\n END LOOP;\n\n RETURN result;\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.208676-05 2025-09-22 20:41:10.208676-05 \N {} |
| 2119 | 10 functions/url_decode.sql -- URL decode function (like PHP's urldecode)\nCREATE OR REPLACE FUNCTION url_decode(input text)\nRETURNS text AS $func$\nDECLARE\n result text := '';\n i int := 1;\n len int;\n hex_str text;\nBEGIN\n IF input IS NULL THEN\n RETURN NULL;\n END IF;\n\n len := length(input);\n\n WHILE i <= len LOOP\n IF substring(input, i, 1) = '%' AND i + 2 <= len THEN\n hex_str := substring(input, i + 1, 2);\n IF hex_str ~ '^[0-9A-Fa-f]{2}$' THEN\n result := result || chr(('x' || hex_str)::bit(8)::int);\n i := i + 3;\n ELSE\n result := result || '%';\n i := i + 1;\n END IF;\n ELSIF substring(input, i, 1) = '+' THEN\n result := result || ' ';\n i := i + 1;\n ELSE\n result := result || substring(input, i, 1);\n i := i + 1;\n END IF;\n END LOOP;\n\n RETURN result;\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.20939-05 2025-09-22 20:41:10.20939-05 \N {} |
| 2120 | 11 functions/parse_query_string.sql -- Query string parse function (like PHP's parse_str)\nCREATE OR REPLACE FUNCTION parse_query_string(query_string text)\nRETURNS jsonb AS $func$\nDECLARE\n params jsonb := '{}'::jsonb;\n pair text;\n key text;\n value text;\n pos int;\nBEGIN\n IF query_string IS NULL OR query_string = '' THEN\n RETURN params;\n END IF;\n\n IF left(query_string, 1) = '?' THEN\n query_string := substring(query_string, 2);\n END IF;\n\n FOREACH pair IN ARRAY string_to_array(query_string, '&') LOOP\n pos := position('=' IN pair);\n IF pos > 0 THEN\n key := url_decode(substring(pair, 1, pos - 1));\n value := url_decode(substring(pair, pos + 1));\n ELSE\n key := url_decode(pair);\n value := '';\n END IF;\n\n params := params || jsonb_build_object(key, value);\n END LOOP;\n\n RETURN params;\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.209636-05 2025-09-22 20:41:10.209636-05 \N {} |
| 2121 | 12 functions/slugify.sql -- Slugify function (convert text to URL-friendly slug)\nCREATE OR REPLACE FUNCTION slugify(input text)\nRETURNS text AS $func$\nBEGIN\n IF input IS NULL THEN\n RETURN NULL;\n END IF;\n\n RETURN lower(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n input,\n '[^a-zA-Z0-9\\s-]', '', 'g'\n ),\n '\\s+', '-', 'g'\n ),\n '-+', '-', 'g'\n )\n );\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.209899-05 2025-09-22 20:41:10.209899-05 \N {} |
| 2122 | 13 functions/format_bytes.sql -- Format bytes as human-readable size\nCREATE OR REPLACE FUNCTION format_bytes(bytes bigint)\nRETURNS text AS $func$\nDECLARE\n units text[] := ARRAY['B', 'KB', 'MB', 'GB', 'TB'];\n unit_index int := 1;\n size numeric := bytes;\nBEGIN\n WHILE size >= 1024 AND unit_index < array_length(units, 1) LOOP\n size := size / 1024;\n unit_index := unit_index + 1;\n END LOOP;\n\n IF unit_index = 1 THEN\n RETURN bytes || ' B';\n ELSE\n RETURN round(size, 2) || ' ' || units[unit_index];\n END IF;\nEND;\n$func$ LANGUAGE plpgsql IMMUTABLE; sql 1 t 2025-09-22 20:41:10.210188-05 2025-09-22 20:41:10.210188-05 \N {} |
| 2123 | \. |
| 2124 | |
| 2125 | |
| 2126 | -- |
| 2127 | -- Data for Name: code_history; Type: TABLE DATA; Schema: public; Owner: danq |
| 2128 | -- |
| 2129 | |
| 2130 | COPY public.code_history (id, file_id, content, version, changed_at, changed_by) FROM stdin; |
| 2131 | \. |
| 2132 | |
| 2133 | |
| 2134 | -- |
| 2135 | -- Data for Name: exceptions; Type: TABLE DATA; Schema: public; Owner: danq |
| 2136 | -- |
| 2137 | |
| 2138 | COPY public.exceptions (id, error_type, message, traceback, context, function_name, occurred_at, resolved) FROM stdin; |
| 2139 | \. |
| 2140 | |
| 2141 | |
| 2142 | -- |
| 2143 | -- Data for Name: jobs; Type: TABLE DATA; Schema: public; Owner: danq |
| 2144 | -- |
| 2145 | |
| 2146 | COPY public.jobs (id, type, payload, status, result, attempts, created_at, started_at, completed_at) FROM stdin; |
| 2147 | 1 cleanup {"description": "Initial cleanup"} completed {"status": "success", "message": "Cleanup completed"} 0 2025-09-22 19:48:50.241994-05 2025-09-22 19:49:45.260654-05 2025-09-22 19:49:45.260654-05 |
| 2148 | 2 analyze_blobs {"description": "Analyze storage"} completed {"avg_size": 51.0000000000000000, "total_size": 51, "total_blobs": 1} 0 2025-09-22 19:48:50.241994-05 2025-09-22 19:49:50.275851-05 2025-09-22 19:49:50.275851-05 |
| 2149 | 3 compute {"value": 42} completed {"result": 84} 0 2025-09-22 19:50:17.605383-05 2025-09-22 19:50:17.640306-05 2025-09-22 19:50:17.640306-05 |
| 2150 | \. |
| 2151 | |
| 2152 | |
| 2153 | -- |
| 2154 | -- Data for Name: routes; Type: TABLE DATA; Schema: public; Owner: danq |
| 2155 | -- |
| 2156 | |
| 2157 | COPY public.routes (id, method, path_pattern, handler, description) FROM stdin; |
| 2158 | 1 GET / home Welcome page |
| 2159 | 2 GET /stats stats Database statistics |
| 2160 | 3 GET /blobs list_blobs List all blobs |
| 2161 | 4 GET /blob/:id get_blob Get specific blob |
| 2162 | 5 POST /blob create_blob Upload new blob |
| 2163 | 6 DELETE /blob/:id delete_blob Delete blob |
| 2164 | 7 GET /jobs list_jobs List all jobs |
| 2165 | 8 POST /job create_job Create new job |
| 2166 | 9 GET /job/:id get_job Get job status |
| 2167 | \. |
| 2168 | |
| 2169 | |
| 2170 | -- |
| 2171 | -- Data for Name: sessions; Type: TABLE DATA; Schema: public; Owner: danq |
| 2172 | -- |
| 2173 | |
| 2174 | COPY public.sessions (id, data, created_at, expires_at, last_access) FROM stdin; |
| 2175 | \. |
| 2176 | |
| 2177 | |
| 2178 | -- |
| 2179 | -- Name: code_files_id_seq; Type: SEQUENCE SET; Schema: public; Owner: danq |
| 2180 | -- |
| 2181 | |
| 2182 | SELECT pg_catalog.setval('public.code_files_id_seq', 13, true); |
| 2183 | |
| 2184 | |
| 2185 | -- |
| 2186 | -- Name: code_history_id_seq; Type: SEQUENCE SET; Schema: public; Owner: danq |
| 2187 | -- |
| 2188 | |
| 2189 | SELECT pg_catalog.setval('public.code_history_id_seq', 1, false); |
| 2190 | |
| 2191 | |
| 2192 | -- |
| 2193 | -- Name: exceptions_id_seq; Type: SEQUENCE SET; Schema: public; Owner: danq |
| 2194 | -- |
| 2195 | |
| 2196 | SELECT pg_catalog.setval('public.exceptions_id_seq', 1, false); |
| 2197 | |
| 2198 | |
| 2199 | -- |
| 2200 | -- Name: jobs_id_seq; Type: SEQUENCE SET; Schema: public; Owner: danq |
| 2201 | -- |
| 2202 | |
| 2203 | SELECT pg_catalog.setval('public.jobs_id_seq', 3, true); |
| 2204 | |
| 2205 | |
| 2206 | -- |
| 2207 | -- Name: routes_id_seq; Type: SEQUENCE SET; Schema: public; Owner: danq |
| 2208 | -- |
| 2209 | |
| 2210 | SELECT pg_catalog.setval('public.routes_id_seq', 9, true); |
| 2211 | |
| 2212 | |
| 2213 | -- |
| 2214 | -- Name: blobs blobs_pkey; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2215 | -- |
| 2216 | |
| 2217 | ALTER TABLE ONLY public.blobs |
| 2218 | ADD CONSTRAINT blobs_pkey PRIMARY KEY (id); |
| 2219 | |
| 2220 | |
| 2221 | -- |
| 2222 | -- Name: cache cache_pkey; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2223 | -- |
| 2224 | |
| 2225 | ALTER TABLE ONLY public.cache |
| 2226 | ADD CONSTRAINT cache_pkey PRIMARY KEY (key); |
| 2227 | |
| 2228 | |
| 2229 | -- |
| 2230 | -- Name: circuit_breakers circuit_breakers_pkey; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2231 | -- |
| 2232 | |
| 2233 | ALTER TABLE ONLY public.circuit_breakers |
| 2234 | ADD CONSTRAINT circuit_breakers_pkey PRIMARY KEY (function_name); |
| 2235 | |
| 2236 | |
| 2237 | -- |
| 2238 | -- Name: code_files code_files_path_key; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2239 | -- |
| 2240 | |
| 2241 | ALTER TABLE ONLY public.code_files |
| 2242 | ADD CONSTRAINT code_files_path_key UNIQUE (path); |
| 2243 | |
| 2244 | |
| 2245 | -- |
| 2246 | -- Name: code_files code_files_pkey; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2247 | -- |
| 2248 | |
| 2249 | ALTER TABLE ONLY public.code_files |
| 2250 | ADD CONSTRAINT code_files_pkey PRIMARY KEY (id); |
| 2251 | |
| 2252 | |
| 2253 | -- |
| 2254 | -- Name: code_history code_history_pkey; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2255 | -- |
| 2256 | |
| 2257 | ALTER TABLE ONLY public.code_history |
| 2258 | ADD CONSTRAINT code_history_pkey PRIMARY KEY (id); |
| 2259 | |
| 2260 | |
| 2261 | -- |
| 2262 | -- Name: exceptions exceptions_pkey; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2263 | -- |
| 2264 | |
| 2265 | ALTER TABLE ONLY public.exceptions |
| 2266 | ADD CONSTRAINT exceptions_pkey PRIMARY KEY (id); |
| 2267 | |
| 2268 | |
| 2269 | -- |
| 2270 | -- Name: jobs jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2271 | -- |
| 2272 | |
| 2273 | ALTER TABLE ONLY public.jobs |
| 2274 | ADD CONSTRAINT jobs_pkey PRIMARY KEY (id); |
| 2275 | |
| 2276 | |
| 2277 | -- |
| 2278 | -- Name: routes routes_method_path_pattern_key; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2279 | -- |
| 2280 | |
| 2281 | ALTER TABLE ONLY public.routes |
| 2282 | ADD CONSTRAINT routes_method_path_pattern_key UNIQUE (method, path_pattern); |
| 2283 | |
| 2284 | |
| 2285 | -- |
| 2286 | -- Name: routes routes_pkey; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2287 | -- |
| 2288 | |
| 2289 | ALTER TABLE ONLY public.routes |
| 2290 | ADD CONSTRAINT routes_pkey PRIMARY KEY (id); |
| 2291 | |
| 2292 | |
| 2293 | -- |
| 2294 | -- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: danq |
| 2295 | -- |
| 2296 | |
| 2297 | ALTER TABLE ONLY public.sessions |
| 2298 | ADD CONSTRAINT sessions_pkey PRIMARY KEY (id); |
| 2299 | |
| 2300 | |
| 2301 | -- |
| 2302 | -- Name: idx_blobs_created; Type: INDEX; Schema: public; Owner: danq |
| 2303 | -- |
| 2304 | |
| 2305 | CREATE INDEX idx_blobs_created ON public.blobs USING btree (created_at DESC); |
| 2306 | |
| 2307 | |
| 2308 | -- |
| 2309 | -- Name: idx_cache_expires; Type: INDEX; Schema: public; Owner: danq |
| 2310 | -- |
| 2311 | |
| 2312 | CREATE INDEX idx_cache_expires ON public.cache USING btree (expires_at); |
| 2313 | |
| 2314 | |
| 2315 | -- |
| 2316 | -- Name: idx_exceptions_occurred; Type: INDEX; Schema: public; Owner: danq |
| 2317 | -- |
| 2318 | |
| 2319 | CREATE INDEX idx_exceptions_occurred ON public.exceptions USING btree (occurred_at DESC); |
| 2320 | |
| 2321 | |
| 2322 | -- |
| 2323 | -- Name: idx_jobs_status; Type: INDEX; Schema: public; Owner: danq |
| 2324 | -- |
| 2325 | |
| 2326 | CREATE INDEX idx_jobs_status ON public.jobs USING btree (status, created_at); |
| 2327 | |
| 2328 | |
| 2329 | -- |
| 2330 | -- Name: idx_sessions_expires; Type: INDEX; Schema: public; Owner: danq |
| 2331 | -- |
| 2332 | |
| 2333 | CREATE INDEX idx_sessions_expires ON public.sessions USING btree (expires_at); |
| 2334 | |
| 2335 | |
| 2336 | -- |
| 2337 | -- Name: jobs job_created; Type: TRIGGER; Schema: public; Owner: danq |
| 2338 | -- |
| 2339 | |
| 2340 | CREATE TRIGGER job_created AFTER INSERT ON public.jobs FOR EACH ROW EXECUTE FUNCTION public.notify_new_job(); |
| 2341 | |
| 2342 | |
| 2343 | -- |
| 2344 | -- Name: code_history code_history_file_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: danq |
| 2345 | -- |
| 2346 | |
| 2347 | ALTER TABLE ONLY public.code_history |
| 2348 | ADD CONSTRAINT code_history_file_id_fkey FOREIGN KEY (file_id) REFERENCES public.code_files(id); |
| 2349 | |
| 2350 | |
| 2351 | -- |
| 2352 | -- PostgreSQL database dump complete |
| 2353 | -- |
| 2354 | |
| 2355 | \unrestrict ZHowxS4FzILvodLbuq1kLjCcpmcpSQ5reyQdJ8KCxiHbPVSQVdN99Uni14IBD26 |