askrobots / postblob Public

An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.

0 downloads

postblob/hot_deploy.sql
Download zip
Clone
Clone with git
git clone https://gitstable.com/git/askrobots/postblob.git
HTTPS
https://gitstable.com/git/askrobots/postblob.git
SSH
ssh://git@gitstable.com:2222/askrobots/postblob.git

Cloning needs no sign-in. Pushing over HTTPS asks for your username and an access token. SSH needs your key under SSH keys.

danq9 docs: Add screenshots to README ae44e69 1 year, 1 month ago History
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
9CREATE 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
23CREATE 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
37INSERT INTO code_files (path, content, language, metadata)
38VALUES (
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
44SET content = EXCLUDED.content,
45 version = code_files.version + 1,
46 updated_at = now();
47
48-- Store other functions
49INSERT INTO code_files (path, content, language, metadata)
50SELECT
51 'functions/' || p.proname || '.sql',
52 pg_get_functiondef(p.oid),
53 'sql',
54 jsonb_build_object('type', 'function', 'hot_reload', true)
55FROM pg_proc p
56JOIN pg_namespace n ON p.pronamespace = n.oid
57WHERE n.nspname = 'public'
58AND p.proname IN ('process_job', 'ask', 'cache_get', 'cache_set')
59ON CONFLICT (path) DO NOTHING;
60
61-- =============================================
62-- HOT RELOAD FUNCTION
63-- =============================================
64
65CREATE OR REPLACE FUNCTION hot_reload(file_path text DEFAULT NULL)
66RETURNS jsonb AS $$
67DECLARE
68 v_code text;
69 v_result jsonb;
70 v_file record;
71BEGIN
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;
116END;
117$$ LANGUAGE plpgsql;
118
119-- =============================================
120-- CODE CRUD OPERATIONS
121-- =============================================
122
123CREATE 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 $$
129DECLARE
130 v_result jsonb;
131 v_file_id integer;
132 v_old_content text;
133 v_version integer;
134BEGIN
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'));
245END;
246$$ LANGUAGE plpgsql;
247
248-- =============================================
249-- LIVE CODE EXECUTION
250-- =============================================
251
252CREATE OR REPLACE FUNCTION execute_code(
253 p_language text,
254 p_code text,
255 p_save_as text DEFAULT NULL
256) RETURNS jsonb AS $$
257DECLARE
258 v_result jsonb;
259BEGIN
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;
313END;
314$$ LANGUAGE plpgsql;
315
316-- =============================================
317-- ADD CODE ENDPOINTS TO ROUTER
318-- =============================================
319
320-- Store the code editor UI
321INSERT INTO code_files (path, content, language, metadata)
322VALUES (
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
451SET 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
461CREATE OR REPLACE FUNCTION code_routes(
462 p_method text,
463 p_path text,
464 p_body jsonb DEFAULT '{}'::jsonb
465) RETURNS jsonb AS $$
466DECLARE
467 v_result jsonb;
468 v_path text;
469 v_content text;
470BEGIN
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;
529END;
530$$ LANGUAGE plpgsql;
531
532-- =============================================
533-- DEMONSTRATION: SELF-MODIFYING CODE
534-- =============================================
535
536-- Store a function that can modify itself
537INSERT INTO code_files (path, content, language, metadata)
538VALUES (
539 'functions/self_modify.sql',
540 'CREATE OR REPLACE FUNCTION get_version()
541RETURNS text AS $$
542BEGIN
543 RETURN ''Version 1.0'';
544END;
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
551CREATE OR REPLACE FUNCTION increment_version()
552RETURNS jsonb AS $$
553DECLARE
554 v_current_version text;
555 v_new_version text;
556 v_new_code text;
557BEGIN
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()
568RETURNS text AS $$
569BEGIN
570 RETURN %L;
571END;
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 );
589END;
590$$ LANGUAGE plpgsql;
591
592-- Success message
593DO $$
594BEGIN
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 '';
606END $$;