An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.
simple_code_routes.sql · 2.9 KB · 98 lines
| 1 | -- Simplified code_routes |
| 2 | CREATE OR REPLACE FUNCTION code_routes( |
| 3 | p_method text, |
| 4 | p_path text, |
| 5 | p_body jsonb DEFAULT '{}'::jsonb |
| 6 | ) RETURNS jsonb AS $$ |
| 7 | DECLARE |
| 8 | v_result jsonb; |
| 9 | v_file_path text; |
| 10 | v_content text; |
| 11 | v_crud_result jsonb; |
| 12 | v_query_start integer; |
| 13 | BEGIN |
| 14 | -- Route: GET /code (Editor UI) |
| 15 | IF p_method = 'GET' AND p_path = '/code' THEN |
| 16 | SELECT content INTO v_content |
| 17 | FROM code_files |
| 18 | WHERE path = 'ui/code_editor.html'; |
| 19 | |
| 20 | RETURN jsonb_build_object( |
| 21 | 'status', 200, |
| 22 | 'headers', jsonb_build_object('Content-Type', 'text/html'), |
| 23 | 'body', v_content, |
| 24 | 'html', true |
| 25 | ); |
| 26 | |
| 27 | -- Route: GET /code/list |
| 28 | ELSIF p_method = 'GET' AND p_path = '/code/list' THEN |
| 29 | v_crud_result := code_crud('LIST', NULL); |
| 30 | RETURN jsonb_build_object( |
| 31 | 'status', 200, |
| 32 | 'body', v_crud_result |
| 33 | ); |
| 34 | |
| 35 | -- Route: GET /code/file |
| 36 | ELSIF p_method = 'GET' AND p_path LIKE '/code/file?%' THEN |
| 37 | -- Simple extraction: find position of 'path=' |
| 38 | v_query_start := position('path=' in p_path); |
| 39 | |
| 40 | IF v_query_start > 0 THEN |
| 41 | -- Extract everything after 'path=' |
| 42 | v_file_path := substring(p_path from v_query_start + 5); |
| 43 | |
| 44 | -- URL decode |
| 45 | v_file_path := replace(v_file_path, '%2F', '/'); |
| 46 | v_file_path := replace(v_file_path, '%20', ' '); |
| 47 | v_file_path := replace(v_file_path, '+', ' '); |
| 48 | |
| 49 | -- Remove any trailing parameters |
| 50 | IF position('&' in v_file_path) > 0 THEN |
| 51 | v_file_path := substring(v_file_path from 1 for position('&' in v_file_path) - 1); |
| 52 | END IF; |
| 53 | END IF; |
| 54 | |
| 55 | -- Get the file |
| 56 | v_crud_result := code_crud('GET', v_file_path); |
| 57 | |
| 58 | RETURN jsonb_build_object( |
| 59 | 'status', 200, |
| 60 | 'body', v_crud_result |
| 61 | ); |
| 62 | |
| 63 | -- Route: POST /code/save |
| 64 | ELSIF p_method = 'POST' AND p_path = '/code/save' THEN |
| 65 | v_crud_result := code_crud('PUT', p_body->>'path', p_body->>'content'); |
| 66 | RETURN jsonb_build_object( |
| 67 | 'status', 200, |
| 68 | 'body', v_crud_result |
| 69 | ); |
| 70 | |
| 71 | -- Route: POST /code/execute |
| 72 | ELSIF p_method = 'POST' AND p_path = '/code/execute' THEN |
| 73 | v_crud_result := execute_code( |
| 74 | COALESCE(p_body->>'language', 'sql'), |
| 75 | p_body->>'code', |
| 76 | p_body->>'save_as' |
| 77 | ); |
| 78 | RETURN jsonb_build_object( |
| 79 | 'status', 200, |
| 80 | 'body', v_crud_result |
| 81 | ); |
| 82 | |
| 83 | -- Route: POST /code/reload |
| 84 | ELSIF p_method = 'POST' AND p_path = '/code/reload' THEN |
| 85 | v_crud_result := hot_reload(p_body->>'path'); |
| 86 | RETURN jsonb_build_object( |
| 87 | 'status', 200, |
| 88 | 'body', v_crud_result |
| 89 | ); |
| 90 | |
| 91 | ELSE |
| 92 | RETURN jsonb_build_object( |
| 93 | 'status', 404, |
| 94 | 'body', jsonb_build_object('error', 'Route not found', 'path', p_path) |
| 95 | ); |
| 96 | END IF; |
| 97 | END; |
| 98 | $$ LANGUAGE plpgsql; |