An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.
hot_deploy.sql · 19.6 KB · 606 lines
| 1 | -- Hot Deploy System for PostBlob |
| 2 | -- Store and execute code from the database itself! |
| 3 | -- psql postblob < hot_deploy.sql |
| 4 | |
| 5 | -- ============================================= |
| 6 | -- CODE STORAGE TABLE |
| 7 | -- ============================================= |
| 8 | |
| 9 | CREATE TABLE IF NOT EXISTS code_files ( |
| 10 | id serial PRIMARY KEY, |
| 11 | path text UNIQUE NOT NULL, |
| 12 | content text NOT NULL, |
| 13 | language text DEFAULT 'sql', |
| 14 | version integer DEFAULT 1, |
| 15 | is_active boolean DEFAULT true, |
| 16 | created_at timestamptz DEFAULT now(), |
| 17 | updated_at timestamptz DEFAULT now(), |
| 18 | executed_at timestamptz, |
| 19 | metadata jsonb DEFAULT '{}'::jsonb |
| 20 | ); |
| 21 | |
| 22 | -- Version history |
| 23 | CREATE TABLE IF NOT EXISTS code_history ( |
| 24 | id serial PRIMARY KEY, |
| 25 | file_id integer REFERENCES code_files(id), |
| 26 | content text NOT NULL, |
| 27 | version integer NOT NULL, |
| 28 | changed_at timestamptz DEFAULT now(), |
| 29 | changed_by text DEFAULT current_user |
| 30 | ); |
| 31 | |
| 32 | -- ============================================= |
| 33 | -- STORE FUNCTIONS AS CODE |
| 34 | -- ============================================= |
| 35 | |
| 36 | -- Store the current handle_request function as code |
| 37 | INSERT INTO code_files (path, content, language, metadata) |
| 38 | VALUES ( |
| 39 | 'functions/handle_request.sql', |
| 40 | pg_get_functiondef('handle_request'::regproc), |
| 41 | 'sql', |
| 42 | '{"type": "function", "hot_reload": true}'::jsonb |
| 43 | ) ON CONFLICT (path) DO UPDATE |
| 44 | SET content = EXCLUDED.content, |
| 45 | version = code_files.version + 1, |
| 46 | updated_at = now(); |
| 47 | |
| 48 | -- Store other functions |
| 49 | INSERT INTO code_files (path, content, language, metadata) |
| 50 | SELECT |
| 51 | 'functions/' || p.proname || '.sql', |
| 52 | pg_get_functiondef(p.oid), |
| 53 | 'sql', |
| 54 | jsonb_build_object('type', 'function', 'hot_reload', true) |
| 55 | FROM pg_proc p |
| 56 | JOIN pg_namespace n ON p.pronamespace = n.oid |
| 57 | WHERE n.nspname = 'public' |
| 58 | AND p.proname IN ('process_job', 'ask', 'cache_get', 'cache_set') |
| 59 | ON CONFLICT (path) DO NOTHING; |
| 60 | |
| 61 | -- ============================================= |
| 62 | -- HOT RELOAD FUNCTION |
| 63 | -- ============================================= |
| 64 | |
| 65 | CREATE OR REPLACE FUNCTION hot_reload(file_path text DEFAULT NULL) |
| 66 | RETURNS jsonb AS $$ |
| 67 | DECLARE |
| 68 | v_code text; |
| 69 | v_result jsonb; |
| 70 | v_file record; |
| 71 | BEGIN |
| 72 | v_result := jsonb_build_object('reloaded', jsonb_array()); |
| 73 | |
| 74 | -- Reload specific file or all active files |
| 75 | FOR v_file IN |
| 76 | SELECT * FROM code_files |
| 77 | WHERE is_active = true |
| 78 | AND (file_path IS NULL OR path = file_path) |
| 79 | AND language = 'sql' |
| 80 | ORDER BY path |
| 81 | LOOP |
| 82 | BEGIN |
| 83 | -- Execute the SQL code |
| 84 | EXECUTE v_file.content; |
| 85 | |
| 86 | -- Update executed timestamp |
| 87 | UPDATE code_files |
| 88 | SET executed_at = now() |
| 89 | WHERE id = v_file.id; |
| 90 | |
| 91 | -- Add to result |
| 92 | v_result := jsonb_set( |
| 93 | v_result, |
| 94 | '{reloaded, 999999}', |
| 95 | jsonb_build_object( |
| 96 | 'path', v_file.path, |
| 97 | 'version', v_file.version, |
| 98 | 'success', true |
| 99 | ) |
| 100 | ); |
| 101 | |
| 102 | EXCEPTION WHEN OTHERS THEN |
| 103 | -- Log error but continue |
| 104 | v_result := jsonb_set( |
| 105 | v_result, |
| 106 | '{errors, 999999}', |
| 107 | jsonb_build_object( |
| 108 | 'path', v_file.path, |
| 109 | 'error', SQLERRM |
| 110 | ) |
| 111 | ); |
| 112 | END; |
| 113 | END LOOP; |
| 114 | |
| 115 | RETURN v_result; |
| 116 | END; |
| 117 | $$ LANGUAGE plpgsql; |
| 118 | |
| 119 | -- ============================================= |
| 120 | -- CODE CRUD OPERATIONS |
| 121 | -- ============================================= |
| 122 | |
| 123 | CREATE OR REPLACE FUNCTION code_crud( |
| 124 | p_method text, |
| 125 | p_path text, |
| 126 | p_content text DEFAULT NULL, |
| 127 | p_metadata jsonb DEFAULT NULL |
| 128 | ) RETURNS jsonb AS $$ |
| 129 | DECLARE |
| 130 | v_result jsonb; |
| 131 | v_file_id integer; |
| 132 | v_old_content text; |
| 133 | v_version integer; |
| 134 | BEGIN |
| 135 | CASE p_method |
| 136 | WHEN 'GET' THEN |
| 137 | -- Get code file |
| 138 | SELECT jsonb_build_object( |
| 139 | 'id', id, |
| 140 | 'path', path, |
| 141 | 'content', content, |
| 142 | 'language', language, |
| 143 | 'version', version, |
| 144 | 'metadata', metadata, |
| 145 | 'updated_at', updated_at |
| 146 | ) INTO v_result |
| 147 | FROM code_files |
| 148 | WHERE path = p_path; |
| 149 | |
| 150 | IF v_result IS NULL THEN |
| 151 | v_result := jsonb_build_object('error', 'File not found'); |
| 152 | END IF; |
| 153 | |
| 154 | WHEN 'LIST' THEN |
| 155 | -- List all code files |
| 156 | SELECT jsonb_agg( |
| 157 | jsonb_build_object( |
| 158 | 'path', path, |
| 159 | 'language', language, |
| 160 | 'version', version, |
| 161 | 'size', length(content), |
| 162 | 'updated_at', updated_at |
| 163 | ) ORDER BY path |
| 164 | ) INTO v_result |
| 165 | FROM code_files |
| 166 | WHERE is_active = true; |
| 167 | |
| 168 | WHEN 'PUT', 'POST' THEN |
| 169 | -- Update or create code file |
| 170 | SELECT id, content, version |
| 171 | INTO v_file_id, v_old_content, v_version |
| 172 | FROM code_files |
| 173 | WHERE path = p_path; |
| 174 | |
| 175 | IF v_file_id IS NOT NULL THEN |
| 176 | -- Save to history |
| 177 | INSERT INTO code_history (file_id, content, version) |
| 178 | VALUES (v_file_id, v_old_content, v_version); |
| 179 | |
| 180 | -- Update file |
| 181 | UPDATE code_files |
| 182 | SET content = p_content, |
| 183 | version = version + 1, |
| 184 | metadata = COALESCE(p_metadata, metadata), |
| 185 | updated_at = now() |
| 186 | WHERE id = v_file_id |
| 187 | RETURNING jsonb_build_object( |
| 188 | 'id', id, |
| 189 | 'path', path, |
| 190 | 'version', version, |
| 191 | 'updated', true |
| 192 | ) INTO v_result; |
| 193 | ELSE |
| 194 | -- Create new file |
| 195 | INSERT INTO code_files (path, content, metadata) |
| 196 | VALUES (p_path, p_content, COALESCE(p_metadata, '{}'::jsonb)) |
| 197 | RETURNING jsonb_build_object( |
| 198 | 'id', id, |
| 199 | 'path', path, |
| 200 | 'version', version, |
| 201 | 'created', true |
| 202 | ) INTO v_result; |
| 203 | END IF; |
| 204 | |
| 205 | -- Auto hot-reload if it's a SQL function |
| 206 | IF p_path LIKE 'functions/%.sql' THEN |
| 207 | PERFORM hot_reload(p_path); |
| 208 | v_result := jsonb_set(v_result, '{hot_reloaded}', 'true'::jsonb); |
| 209 | END IF; |
| 210 | |
| 211 | WHEN 'DELETE' THEN |
| 212 | -- Soft delete |
| 213 | UPDATE code_files |
| 214 | SET is_active = false, |
| 215 | updated_at = now() |
| 216 | WHERE path = p_path |
| 217 | RETURNING jsonb_build_object( |
| 218 | 'path', path, |
| 219 | 'deleted', true |
| 220 | ) INTO v_result; |
| 221 | |
| 222 | IF v_result IS NULL THEN |
| 223 | v_result := jsonb_build_object('error', 'File not found'); |
| 224 | END IF; |
| 225 | |
| 226 | WHEN 'HISTORY' THEN |
| 227 | -- Get version history |
| 228 | SELECT jsonb_agg( |
| 229 | jsonb_build_object( |
| 230 | 'version', h.version, |
| 231 | 'changed_at', h.changed_at, |
| 232 | 'changed_by', h.changed_by, |
| 233 | 'size', length(h.content) |
| 234 | ) ORDER BY h.version DESC |
| 235 | ) INTO v_result |
| 236 | FROM code_history h |
| 237 | JOIN code_files f ON h.file_id = f.id |
| 238 | WHERE f.path = p_path; |
| 239 | |
| 240 | ELSE |
| 241 | v_result := jsonb_build_object('error', 'Invalid method'); |
| 242 | END CASE; |
| 243 | |
| 244 | RETURN COALESCE(v_result, jsonb_build_object('error', 'Unknown error')); |
| 245 | END; |
| 246 | $$ LANGUAGE plpgsql; |
| 247 | |
| 248 | -- ============================================= |
| 249 | -- LIVE CODE EXECUTION |
| 250 | -- ============================================= |
| 251 | |
| 252 | CREATE OR REPLACE FUNCTION execute_code( |
| 253 | p_language text, |
| 254 | p_code text, |
| 255 | p_save_as text DEFAULT NULL |
| 256 | ) RETURNS jsonb AS $$ |
| 257 | DECLARE |
| 258 | v_result jsonb; |
| 259 | BEGIN |
| 260 | -- Execute based on language |
| 261 | CASE p_language |
| 262 | WHEN 'sql' THEN |
| 263 | BEGIN |
| 264 | EXECUTE p_code; |
| 265 | v_result := jsonb_build_object( |
| 266 | 'success', true, |
| 267 | 'message', 'SQL executed successfully' |
| 268 | ); |
| 269 | |
| 270 | -- Save if requested |
| 271 | IF p_save_as IS NOT NULL THEN |
| 272 | PERFORM code_crud('PUT', p_save_as, p_code, |
| 273 | jsonb_build_object('language', 'sql', 'auto_saved', true)); |
| 274 | v_result := jsonb_set(v_result, '{saved_as}', to_jsonb(p_save_as)); |
| 275 | END IF; |
| 276 | |
| 277 | EXCEPTION WHEN OTHERS THEN |
| 278 | v_result := jsonb_build_object( |
| 279 | 'success', false, |
| 280 | 'error', SQLERRM, |
| 281 | 'detail', SQLSTATE |
| 282 | ); |
| 283 | END; |
| 284 | |
| 285 | WHEN 'plpgsql' THEN |
| 286 | BEGIN |
| 287 | -- For PL/pgSQL, wrap in DO block if needed |
| 288 | IF NOT (p_code ILIKE 'CREATE%FUNCTION%' OR p_code ILIKE 'DO%') THEN |
| 289 | EXECUTE 'DO $$ BEGIN ' || p_code || ' END $$;'; |
| 290 | ELSE |
| 291 | EXECUTE p_code; |
| 292 | END IF; |
| 293 | |
| 294 | v_result := jsonb_build_object( |
| 295 | 'success', true, |
| 296 | 'message', 'PL/pgSQL executed successfully' |
| 297 | ); |
| 298 | |
| 299 | EXCEPTION WHEN OTHERS THEN |
| 300 | v_result := jsonb_build_object( |
| 301 | 'success', false, |
| 302 | 'error', SQLERRM |
| 303 | ); |
| 304 | END; |
| 305 | |
| 306 | ELSE |
| 307 | v_result := jsonb_build_object( |
| 308 | 'error', 'Unsupported language: ' || p_language |
| 309 | ); |
| 310 | END CASE; |
| 311 | |
| 312 | RETURN v_result; |
| 313 | END; |
| 314 | $$ LANGUAGE plpgsql; |
| 315 | |
| 316 | -- ============================================= |
| 317 | -- ADD CODE ENDPOINTS TO ROUTER |
| 318 | -- ============================================= |
| 319 | |
| 320 | -- Store the code editor UI |
| 321 | INSERT INTO code_files (path, content, language, metadata) |
| 322 | VALUES ( |
| 323 | 'ui/code_editor.html', |
| 324 | '<!DOCTYPE html> |
| 325 | <html> |
| 326 | <head> |
| 327 | <title>PostBlob Code Editor</title> |
| 328 | <style> |
| 329 | * { margin: 0; padding: 0; box-sizing: border-box; } |
| 330 | body { font-family: monospace; background: #1e1e1e; color: #d4d4d4; } |
| 331 | .container { display: flex; height: 100vh; } |
| 332 | .sidebar { width: 250px; background: #252526; border-right: 1px solid #3e3e42; overflow-y: auto; } |
| 333 | .editor-pane { flex: 1; display: flex; flex-direction: column; } |
| 334 | .toolbar { background: #2d2d30; padding: 10px; border-bottom: 1px solid #3e3e42; } |
| 335 | .editor { flex: 1; padding: 20px; overflow: auto; } |
| 336 | textarea { width: 100%; height: 400px; background: #1e1e1e; color: #d4d4d4; border: 1px solid #3e3e42; padding: 10px; font-family: monospace; font-size: 14px; } |
| 337 | .output { background: #1e1e1e; border-top: 1px solid #3e3e42; padding: 20px; height: 200px; overflow-y: auto; } |
| 338 | .file-item { padding: 8px 15px; cursor: pointer; border-bottom: 1px solid #3e3e42; } |
| 339 | .file-item:hover { background: #2d2d30; } |
| 340 | .file-item.active { background: #094771; } |
| 341 | button { background: #007acc; color: white; border: none; padding: 8px 16px; margin-right: 10px; cursor: pointer; } |
| 342 | button:hover { background: #005a9e; } |
| 343 | .status { padding: 5px 10px; background: #007acc; color: white; display: inline-block; } |
| 344 | pre { white-space: pre-wrap; word-wrap: break-word; } |
| 345 | </style> |
| 346 | </head> |
| 347 | <body> |
| 348 | <div class="container"> |
| 349 | <div class="sidebar"> |
| 350 | <h3 style="padding: 15px; border-bottom: 1px solid #3e3e42;">Code Files</h3> |
| 351 | <div id="fileList"></div> |
| 352 | </div> |
| 353 | <div class="editor-pane"> |
| 354 | <div class="toolbar"> |
| 355 | <button onclick="saveCode()">💾 Save</button> |
| 356 | <button onclick="executeCode()">▶️ Execute</button> |
| 357 | <button onclick="hotReload()">🔄 Hot Reload</button> |
| 358 | <button onclick="newFile()">📄 New File</button> |
| 359 | <span class="status" id="status">Ready</span> |
| 360 | </div> |
| 361 | <div class="editor"> |
| 362 | <input type="text" id="filePath" placeholder="File path..." style="width: 100%; padding: 5px; margin-bottom: 10px; background: #1e1e1e; color: #d4d4d4; border: 1px solid #3e3e42;"> |
| 363 | <textarea id="codeEditor" placeholder="-- Write your SQL or PL/pgSQL code here..."></textarea> |
| 364 | </div> |
| 365 | <div class="output"> |
| 366 | <h4 style="margin-bottom: 10px;">Output:</h4> |
| 367 | <pre id="output"></pre> |
| 368 | </div> |
| 369 | </div> |
| 370 | </div> |
| 371 | |
| 372 | <script> |
| 373 | let currentFile = null; |
| 374 | |
| 375 | async function loadFiles() { |
| 376 | const response = await fetch("/code/list"); |
| 377 | const files = await response.json(); |
| 378 | const fileList = document.getElementById("fileList"); |
| 379 | fileList.innerHTML = files.map(f => |
| 380 | `<div class="file-item" onclick="loadFile(''${f.path}'')">${f.path}<br><small>${f.size} bytes | v${f.version}</small></div>` |
| 381 | ).join(""); |
| 382 | } |
| 383 | |
| 384 | async function loadFile(path) { |
| 385 | currentFile = path; |
| 386 | document.getElementById("filePath").value = path; |
| 387 | const response = await fetch(`/code/file?path=${encodeURIComponent(path)}`); |
| 388 | const file = await response.json(); |
| 389 | document.getElementById("codeEditor").value = file.content; |
| 390 | setStatus(`Loaded ${path}`); |
| 391 | |
| 392 | document.querySelectorAll(".file-item").forEach(el => el.classList.remove("active")); |
| 393 | event.target.classList.add("active"); |
| 394 | } |
| 395 | |
| 396 | async function saveCode() { |
| 397 | const path = document.getElementById("filePath").value; |
| 398 | const content = document.getElementById("codeEditor").value; |
| 399 | |
| 400 | const response = await fetch("/code/save", { |
| 401 | method: "POST", |
| 402 | headers: {"Content-Type": "application/json"}, |
| 403 | body: JSON.stringify({path, content}) |
| 404 | }); |
| 405 | |
| 406 | const result = await response.json(); |
| 407 | document.getElementById("output").textContent = JSON.stringify(result, null, 2); |
| 408 | setStatus(result.hot_reloaded ? "Saved & Reloaded!" : "Saved!"); |
| 409 | loadFiles(); |
| 410 | } |
| 411 | |
| 412 | async function executeCode() { |
| 413 | const code = document.getElementById("codeEditor").value; |
| 414 | const response = await fetch("/code/execute", { |
| 415 | method: "POST", |
| 416 | headers: {"Content-Type": "application/json"}, |
| 417 | body: JSON.stringify({code, language: "sql"}) |
| 418 | }); |
| 419 | |
| 420 | const result = await response.json(); |
| 421 | document.getElementById("output").textContent = JSON.stringify(result, null, 2); |
| 422 | setStatus(result.success ? "Executed!" : "Error!"); |
| 423 | } |
| 424 | |
| 425 | async function hotReload() { |
| 426 | const response = await fetch("/code/reload", {method: "POST"}); |
| 427 | const result = await response.json(); |
| 428 | document.getElementById("output").textContent = JSON.stringify(result, null, 2); |
| 429 | setStatus("Hot Reloaded!"); |
| 430 | } |
| 431 | |
| 432 | function newFile() { |
| 433 | document.getElementById("filePath").value = "functions/new_function.sql"; |
| 434 | 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;"; |
| 435 | currentFile = null; |
| 436 | setStatus("New file"); |
| 437 | } |
| 438 | |
| 439 | function setStatus(text) { |
| 440 | document.getElementById("status").textContent = text; |
| 441 | } |
| 442 | |
| 443 | // Load files on startup |
| 444 | loadFiles(); |
| 445 | </script> |
| 446 | </body> |
| 447 | </html>', |
| 448 | 'html', |
| 449 | '{"type": "ui", "component": "code_editor"}'::jsonb |
| 450 | ) ON CONFLICT (path) DO UPDATE |
| 451 | SET content = EXCLUDED.content, |
| 452 | updated_at = now(); |
| 453 | |
| 454 | -- ============================================= |
| 455 | -- UPDATE HANDLE_REQUEST FOR CODE ROUTES |
| 456 | -- ============================================= |
| 457 | |
| 458 | -- This would be added to handle_request function |
| 459 | -- For now, let's create a separate code_routes function |
| 460 | |
| 461 | CREATE OR REPLACE FUNCTION code_routes( |
| 462 | p_method text, |
| 463 | p_path text, |
| 464 | p_body jsonb DEFAULT '{}'::jsonb |
| 465 | ) RETURNS jsonb AS $$ |
| 466 | DECLARE |
| 467 | v_result jsonb; |
| 468 | v_path text; |
| 469 | v_content text; |
| 470 | BEGIN |
| 471 | -- Route: GET /code (Editor UI) |
| 472 | IF p_method = 'GET' AND p_path = '/code' THEN |
| 473 | SELECT content INTO v_content |
| 474 | FROM code_files |
| 475 | WHERE path = 'ui/code_editor.html'; |
| 476 | |
| 477 | RETURN jsonb_build_object( |
| 478 | 'status', 200, |
| 479 | 'headers', jsonb_build_object('Content-Type', 'text/html'), |
| 480 | 'body', v_content, |
| 481 | 'html', true |
| 482 | ); |
| 483 | |
| 484 | -- Route: GET /code/list |
| 485 | ELSIF p_method = 'GET' AND p_path = '/code/list' THEN |
| 486 | RETURN jsonb_build_object( |
| 487 | 'status', 200, |
| 488 | 'body', code_crud('LIST', NULL) |
| 489 | ); |
| 490 | |
| 491 | -- Route: GET /code/file |
| 492 | ELSIF p_method = 'GET' AND p_path LIKE '/code/file%' THEN |
| 493 | v_path := substring(p_path from 'path=([^&]+)'); |
| 494 | v_path := replace(v_path, '%2F', '/'); |
| 495 | |
| 496 | RETURN jsonb_build_object( |
| 497 | 'status', 200, |
| 498 | 'body', code_crud('GET', v_path) |
| 499 | ); |
| 500 | |
| 501 | -- Route: POST /code/save |
| 502 | ELSIF p_method = 'POST' AND p_path = '/code/save' THEN |
| 503 | RETURN jsonb_build_object( |
| 504 | 'status', 200, |
| 505 | 'body', code_crud('PUT', p_body->>'path', p_body->>'content') |
| 506 | ); |
| 507 | |
| 508 | -- Route: POST /code/execute |
| 509 | ELSIF p_method = 'POST' AND p_path = '/code/execute' THEN |
| 510 | RETURN jsonb_build_object( |
| 511 | 'status', 200, |
| 512 | 'body', execute_code( |
| 513 | COALESCE(p_body->>'language', 'sql'), |
| 514 | p_body->>'code', |
| 515 | p_body->>'save_as' |
| 516 | ) |
| 517 | ); |
| 518 | |
| 519 | -- Route: POST /code/reload |
| 520 | ELSIF p_method = 'POST' AND p_path = '/code/reload' THEN |
| 521 | RETURN jsonb_build_object( |
| 522 | 'status', 200, |
| 523 | 'body', hot_reload(p_body->>'path') |
| 524 | ); |
| 525 | |
| 526 | ELSE |
| 527 | RETURN NULL; |
| 528 | END IF; |
| 529 | END; |
| 530 | $$ LANGUAGE plpgsql; |
| 531 | |
| 532 | -- ============================================= |
| 533 | -- DEMONSTRATION: SELF-MODIFYING CODE |
| 534 | -- ============================================= |
| 535 | |
| 536 | -- Store a function that can modify itself |
| 537 | INSERT INTO code_files (path, content, language, metadata) |
| 538 | VALUES ( |
| 539 | 'functions/self_modify.sql', |
| 540 | 'CREATE OR REPLACE FUNCTION get_version() |
| 541 | RETURNS text AS $$ |
| 542 | BEGIN |
| 543 | RETURN ''Version 1.0''; |
| 544 | END; |
| 545 | $$ LANGUAGE plpgsql;', |
| 546 | 'sql', |
| 547 | '{"type": "function", "self_modifying": true}'::jsonb |
| 548 | ) ON CONFLICT (path) DO NOTHING; |
| 549 | |
| 550 | -- Function to increment its own version |
| 551 | CREATE OR REPLACE FUNCTION increment_version() |
| 552 | RETURNS jsonb AS $$ |
| 553 | DECLARE |
| 554 | v_current_version text; |
| 555 | v_new_version text; |
| 556 | v_new_code text; |
| 557 | BEGIN |
| 558 | -- Get current version |
| 559 | v_current_version := get_version(); |
| 560 | |
| 561 | -- Parse and increment |
| 562 | v_new_version := 'Version ' || ( |
| 563 | substring(v_current_version from '\d+\.\d+')::numeric + 0.1 |
| 564 | ); |
| 565 | |
| 566 | -- Create new function code |
| 567 | v_new_code := format('CREATE OR REPLACE FUNCTION get_version() |
| 568 | RETURNS text AS $$ |
| 569 | BEGIN |
| 570 | RETURN %L; |
| 571 | END; |
| 572 | $$ LANGUAGE plpgsql;', v_new_version); |
| 573 | |
| 574 | -- Update in database |
| 575 | UPDATE code_files |
| 576 | SET content = v_new_code, |
| 577 | version = version + 1, |
| 578 | updated_at = now() |
| 579 | WHERE path = 'functions/self_modify.sql'; |
| 580 | |
| 581 | -- Hot reload |
| 582 | EXECUTE v_new_code; |
| 583 | |
| 584 | RETURN jsonb_build_object( |
| 585 | 'old_version', v_current_version, |
| 586 | 'new_version', v_new_version, |
| 587 | 'reloaded', true |
| 588 | ); |
| 589 | END; |
| 590 | $$ LANGUAGE plpgsql; |
| 591 | |
| 592 | -- Success message |
| 593 | DO $$ |
| 594 | BEGIN |
| 595 | RAISE NOTICE ''; |
| 596 | RAISE NOTICE '========================================'; |
| 597 | RAISE NOTICE 'Hot Deploy System Installed!'; |
| 598 | RAISE NOTICE '========================================'; |
| 599 | RAISE NOTICE 'Code storage and hot reload ready!'; |
| 600 | RAISE NOTICE ''; |
| 601 | RAISE NOTICE 'Try:'; |
| 602 | RAISE NOTICE ' - Visit /code for the code editor'; |
| 603 | RAISE NOTICE ' - SELECT hot_reload(); to reload all functions'; |
| 604 | RAISE NOTICE ' - SELECT increment_version(); to self-modify'; |
| 605 | RAISE NOTICE ''; |
| 606 | END $$; |