An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.
fix_code_routes.sql · 2.2 KB · 79 lines
| 1 | -- Fix code_routes URL parameter parsing |
| 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_path text; |
| 10 | v_content text; |
| 11 | BEGIN |
| 12 | -- Route: GET /code (Editor UI) |
| 13 | IF p_method = 'GET' AND p_path = '/code' THEN |
| 14 | SELECT content INTO v_content |
| 15 | FROM code_files |
| 16 | WHERE path = 'ui/code_editor.html'; |
| 17 | |
| 18 | RETURN jsonb_build_object( |
| 19 | 'status', 200, |
| 20 | 'headers', jsonb_build_object('Content-Type', 'text/html'), |
| 21 | 'body', v_content, |
| 22 | 'html', true |
| 23 | ); |
| 24 | |
| 25 | -- Route: GET /code/list |
| 26 | ELSIF p_method = 'GET' AND p_path = '/code/list' THEN |
| 27 | RETURN jsonb_build_object( |
| 28 | 'status', 200, |
| 29 | 'body', code_crud('LIST', NULL) |
| 30 | ); |
| 31 | |
| 32 | -- Route: GET /code/file |
| 33 | ELSIF p_method = 'GET' AND p_path LIKE '/code/file%' THEN |
| 34 | -- Better URL parameter parsing |
| 35 | v_path := substring(p_path from '/code/file\?path=(.+)$'); |
| 36 | |
| 37 | -- URL decode |
| 38 | v_path := replace(v_path, '%2F', '/'); |
| 39 | v_path := replace(v_path, '%20', ' '); |
| 40 | v_path := replace(v_path, '+', ' '); |
| 41 | |
| 42 | -- Debug logging |
| 43 | RAISE NOTICE 'Requesting file: %', v_path; |
| 44 | |
| 45 | RETURN jsonb_build_object( |
| 46 | 'status', 200, |
| 47 | 'body', code_crud('GET', v_path) |
| 48 | ); |
| 49 | |
| 50 | -- Route: POST /code/save |
| 51 | ELSIF p_method = 'POST' AND p_path = '/code/save' THEN |
| 52 | RETURN jsonb_build_object( |
| 53 | 'status', 200, |
| 54 | 'body', code_crud('PUT', p_body->>'path', p_body->>'content') |
| 55 | ); |
| 56 | |
| 57 | -- Route: POST /code/execute |
| 58 | ELSIF p_method = 'POST' AND p_path = '/code/execute' THEN |
| 59 | RETURN jsonb_build_object( |
| 60 | 'status', 200, |
| 61 | 'body', execute_code( |
| 62 | COALESCE(p_body->>'language', 'sql'), |
| 63 | p_body->>'code', |
| 64 | p_body->>'save_as' |
| 65 | ) |
| 66 | ); |
| 67 | |
| 68 | -- Route: POST /code/reload |
| 69 | ELSIF p_method = 'POST' AND p_path = '/code/reload' THEN |
| 70 | RETURN jsonb_build_object( |
| 71 | 'status', 200, |
| 72 | 'body', hot_reload(p_body->>'path') |
| 73 | ); |
| 74 | |
| 75 | ELSE |
| 76 | RETURN NULL; |
| 77 | END IF; |
| 78 | END; |
| 79 | $$ LANGUAGE plpgsql; |