An experimental web framework where PostgreSQL does everything: routing, HTML, file storage, jobs and sessions.
add_helpers_to_code.sql · 5.6 KB · 190 lines
| 1 | -- Add helper functions to code_files table for hot reload access |
| 2 | |
| 3 | -- HTML encode |
| 4 | INSERT INTO code_files (path, content, language) |
| 5 | VALUES ('functions/html_encode.sql', $$-- HTML encode function (like PHP's htmlspecialchars) |
| 6 | CREATE OR REPLACE FUNCTION html_encode(input text) |
| 7 | RETURNS text AS $func$ |
| 8 | BEGIN |
| 9 | IF input IS NULL THEN |
| 10 | RETURN NULL; |
| 11 | END IF; |
| 12 | |
| 13 | RETURN replace( |
| 14 | replace( |
| 15 | replace( |
| 16 | replace( |
| 17 | replace(input, '&', '&'), |
| 18 | '<', '<'), |
| 19 | '>', '>'), |
| 20 | '"', '"'), |
| 21 | '''', '''); |
| 22 | END; |
| 23 | $func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql') |
| 24 | ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1; |
| 25 | |
| 26 | -- URL encode |
| 27 | INSERT INTO code_files (path, content, language) |
| 28 | VALUES ('functions/url_encode.sql', $$-- URL encode function (like PHP's urlencode) |
| 29 | CREATE OR REPLACE FUNCTION url_encode(input text) |
| 30 | RETURNS text AS $func$ |
| 31 | DECLARE |
| 32 | result text := ''; |
| 33 | char_code int; |
| 34 | i int; |
| 35 | BEGIN |
| 36 | IF input IS NULL THEN |
| 37 | RETURN NULL; |
| 38 | END IF; |
| 39 | |
| 40 | FOR i IN 1..length(input) LOOP |
| 41 | char_code := ascii(substring(input, i, 1)); |
| 42 | |
| 43 | IF (char_code >= 48 AND char_code <= 57) OR -- 0-9 |
| 44 | (char_code >= 65 AND char_code <= 90) OR -- A-Z |
| 45 | (char_code >= 97 AND char_code <= 122) OR -- a-z |
| 46 | char_code IN (45, 46, 95, 126) THEN -- - . _ ~ |
| 47 | result := result || substring(input, i, 1); |
| 48 | ELSIF char_code = 32 THEN |
| 49 | result := result || '+'; |
| 50 | ELSE |
| 51 | result := result || '%' || upper(to_hex(char_code)); |
| 52 | END IF; |
| 53 | END LOOP; |
| 54 | |
| 55 | RETURN result; |
| 56 | END; |
| 57 | $func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql') |
| 58 | ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1; |
| 59 | |
| 60 | -- URL decode |
| 61 | INSERT INTO code_files (path, content, language) |
| 62 | VALUES ('functions/url_decode.sql', $$-- URL decode function (like PHP's urldecode) |
| 63 | CREATE OR REPLACE FUNCTION url_decode(input text) |
| 64 | RETURNS text AS $func$ |
| 65 | DECLARE |
| 66 | result text := ''; |
| 67 | i int := 1; |
| 68 | len int; |
| 69 | hex_str text; |
| 70 | BEGIN |
| 71 | IF input IS NULL THEN |
| 72 | RETURN NULL; |
| 73 | END IF; |
| 74 | |
| 75 | len := length(input); |
| 76 | |
| 77 | WHILE i <= len LOOP |
| 78 | IF substring(input, i, 1) = '%' AND i + 2 <= len THEN |
| 79 | hex_str := substring(input, i + 1, 2); |
| 80 | IF hex_str ~ '^[0-9A-Fa-f]{2}$' THEN |
| 81 | result := result || chr(('x' || hex_str)::bit(8)::int); |
| 82 | i := i + 3; |
| 83 | ELSE |
| 84 | result := result || '%'; |
| 85 | i := i + 1; |
| 86 | END IF; |
| 87 | ELSIF substring(input, i, 1) = '+' THEN |
| 88 | result := result || ' '; |
| 89 | i := i + 1; |
| 90 | ELSE |
| 91 | result := result || substring(input, i, 1); |
| 92 | i := i + 1; |
| 93 | END IF; |
| 94 | END LOOP; |
| 95 | |
| 96 | RETURN result; |
| 97 | END; |
| 98 | $func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql') |
| 99 | ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1; |
| 100 | |
| 101 | -- Parse query string |
| 102 | INSERT INTO code_files (path, content, language) |
| 103 | VALUES ('functions/parse_query_string.sql', $$-- Query string parse function (like PHP's parse_str) |
| 104 | CREATE OR REPLACE FUNCTION parse_query_string(query_string text) |
| 105 | RETURNS jsonb AS $func$ |
| 106 | DECLARE |
| 107 | params jsonb := '{}'::jsonb; |
| 108 | pair text; |
| 109 | key text; |
| 110 | value text; |
| 111 | pos int; |
| 112 | BEGIN |
| 113 | IF query_string IS NULL OR query_string = '' THEN |
| 114 | RETURN params; |
| 115 | END IF; |
| 116 | |
| 117 | IF left(query_string, 1) = '?' THEN |
| 118 | query_string := substring(query_string, 2); |
| 119 | END IF; |
| 120 | |
| 121 | FOREACH pair IN ARRAY string_to_array(query_string, '&') LOOP |
| 122 | pos := position('=' IN pair); |
| 123 | IF pos > 0 THEN |
| 124 | key := url_decode(substring(pair, 1, pos - 1)); |
| 125 | value := url_decode(substring(pair, pos + 1)); |
| 126 | ELSE |
| 127 | key := url_decode(pair); |
| 128 | value := ''; |
| 129 | END IF; |
| 130 | |
| 131 | params := params || jsonb_build_object(key, value); |
| 132 | END LOOP; |
| 133 | |
| 134 | RETURN params; |
| 135 | END; |
| 136 | $func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql') |
| 137 | ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1; |
| 138 | |
| 139 | -- Slugify |
| 140 | INSERT INTO code_files (path, content, language) |
| 141 | VALUES ('functions/slugify.sql', $$-- Slugify function (convert text to URL-friendly slug) |
| 142 | CREATE OR REPLACE FUNCTION slugify(input text) |
| 143 | RETURNS text AS $func$ |
| 144 | BEGIN |
| 145 | IF input IS NULL THEN |
| 146 | RETURN NULL; |
| 147 | END IF; |
| 148 | |
| 149 | RETURN lower( |
| 150 | regexp_replace( |
| 151 | regexp_replace( |
| 152 | regexp_replace( |
| 153 | input, |
| 154 | '[^a-zA-Z0-9\s-]', '', 'g' |
| 155 | ), |
| 156 | '\s+', '-', 'g' |
| 157 | ), |
| 158 | '-+', '-', 'g' |
| 159 | ) |
| 160 | ); |
| 161 | END; |
| 162 | $func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql') |
| 163 | ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1; |
| 164 | |
| 165 | -- Format bytes |
| 166 | INSERT INTO code_files (path, content, language) |
| 167 | VALUES ('functions/format_bytes.sql', $$-- Format bytes as human-readable size |
| 168 | CREATE OR REPLACE FUNCTION format_bytes(bytes bigint) |
| 169 | RETURNS text AS $func$ |
| 170 | DECLARE |
| 171 | units text[] := ARRAY['B', 'KB', 'MB', 'GB', 'TB']; |
| 172 | unit_index int := 1; |
| 173 | size numeric := bytes; |
| 174 | BEGIN |
| 175 | WHILE size >= 1024 AND unit_index < array_length(units, 1) LOOP |
| 176 | size := size / 1024; |
| 177 | unit_index := unit_index + 1; |
| 178 | END LOOP; |
| 179 | |
| 180 | IF unit_index = 1 THEN |
| 181 | RETURN bytes || ' B'; |
| 182 | ELSE |
| 183 | RETURN round(size, 2) || ' ' || units[unit_index]; |
| 184 | END IF; |
| 185 | END; |
| 186 | $func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql') |
| 187 | ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1; |
| 188 | |
| 189 | -- Return success |
| 190 | SELECT 'Helper functions added to code_files!' as result; |