askrobots / postblob Public

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

0 downloads

postblob/add_helpers_to_code.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
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
4INSERT INTO code_files (path, content, language)
5VALUES ('functions/html_encode.sql', $$-- HTML encode function (like PHP's htmlspecialchars)
6CREATE OR REPLACE FUNCTION html_encode(input text)
7RETURNS text AS $func$
8BEGIN
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 '<', '&lt;'),
19 '>', '&gt;'),
20 '"', '&quot;'),
21 '''', '&#39;');
22END;
23$func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql')
24ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1;
25
26-- URL encode
27INSERT INTO code_files (path, content, language)
28VALUES ('functions/url_encode.sql', $$-- URL encode function (like PHP's urlencode)
29CREATE OR REPLACE FUNCTION url_encode(input text)
30RETURNS text AS $func$
31DECLARE
32 result text := '';
33 char_code int;
34 i int;
35BEGIN
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;
56END;
57$func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql')
58ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1;
59
60-- URL decode
61INSERT INTO code_files (path, content, language)
62VALUES ('functions/url_decode.sql', $$-- URL decode function (like PHP's urldecode)
63CREATE OR REPLACE FUNCTION url_decode(input text)
64RETURNS text AS $func$
65DECLARE
66 result text := '';
67 i int := 1;
68 len int;
69 hex_str text;
70BEGIN
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;
97END;
98$func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql')
99ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1;
100
101-- Parse query string
102INSERT INTO code_files (path, content, language)
103VALUES ('functions/parse_query_string.sql', $$-- Query string parse function (like PHP's parse_str)
104CREATE OR REPLACE FUNCTION parse_query_string(query_string text)
105RETURNS jsonb AS $func$
106DECLARE
107 params jsonb := '{}'::jsonb;
108 pair text;
109 key text;
110 value text;
111 pos int;
112BEGIN
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;
135END;
136$func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql')
137ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1;
138
139-- Slugify
140INSERT INTO code_files (path, content, language)
141VALUES ('functions/slugify.sql', $$-- Slugify function (convert text to URL-friendly slug)
142CREATE OR REPLACE FUNCTION slugify(input text)
143RETURNS text AS $func$
144BEGIN
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 );
161END;
162$func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql')
163ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1;
164
165-- Format bytes
166INSERT INTO code_files (path, content, language)
167VALUES ('functions/format_bytes.sql', $$-- Format bytes as human-readable size
168CREATE OR REPLACE FUNCTION format_bytes(bytes bigint)
169RETURNS text AS $func$
170DECLARE
171 units text[] := ARRAY['B', 'KB', 'MB', 'GB', 'TB'];
172 unit_index int := 1;
173 size numeric := bytes;
174BEGIN
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;
185END;
186$func$ LANGUAGE plpgsql IMMUTABLE;$$, 'sql')
187ON CONFLICT (path) DO UPDATE SET content = EXCLUDED.content, version = code_files.version + 1;
188
189-- Return success
190SELECT 'Helper functions added to code_files!' as result;