askrobots / postblob Public

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

0 downloads

postblob/init_postblob.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
init_postblob.sql · 15.6 KB · 532 lines
1-- PostBlob Database: PostgreSQL does everything!
2-- Create database: createdb postblob
3-- Initialize: psql postblob < init_postblob.sql
4
5-- Enable PL/Python extension
6CREATE EXTENSION IF NOT EXISTS plpython3u;
7CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
8
9-- =============================================
10-- CORE TABLES
11-- =============================================
12
13-- File/blob storage
14CREATE TABLE IF NOT EXISTS blobs (
15 id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
16 filename text NOT NULL,
17 mimetype text NOT NULL,
18 data bytea NOT NULL,
19 size bigint NOT NULL,
20 metadata jsonb DEFAULT '{}'::jsonb,
21 created_at timestamptz DEFAULT now(),
22 updated_at timestamptz DEFAULT now()
23);
24
25-- Job queue
26CREATE TABLE IF NOT EXISTS jobs (
27 id serial PRIMARY KEY,
28 type text NOT NULL,
29 payload jsonb DEFAULT '{}'::jsonb,
30 status text DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
31 result jsonb,
32 attempts integer DEFAULT 0,
33 created_at timestamptz DEFAULT now(),
34 started_at timestamptz,
35 completed_at timestamptz
36);
37
38-- Exception storage
39CREATE TABLE IF NOT EXISTS exceptions (
40 id serial PRIMARY KEY,
41 error_type text NOT NULL,
42 message text NOT NULL,
43 traceback text,
44 context jsonb DEFAULT '{}'::jsonb,
45 function_name text,
46 occurred_at timestamptz DEFAULT now(),
47 resolved boolean DEFAULT false
48);
49
50-- Sessions
51CREATE TABLE IF NOT EXISTS sessions (
52 id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
53 data jsonb DEFAULT '{}'::jsonb,
54 created_at timestamptz DEFAULT now(),
55 expires_at timestamptz DEFAULT (now() + interval '24 hours'),
56 last_access timestamptz DEFAULT now()
57);
58
59-- Key-value cache
60CREATE TABLE IF NOT EXISTS cache (
61 key text PRIMARY KEY,
62 value jsonb NOT NULL,
63 expires_at timestamptz,
64 created_at timestamptz DEFAULT now()
65);
66
67-- Circuit breakers
68CREATE TABLE IF NOT EXISTS circuit_breakers (
69 function_name text PRIMARY KEY,
70 state text DEFAULT 'closed' CHECK (state IN ('closed', 'open', 'half_open')),
71 failure_count integer DEFAULT 0,
72 last_failure timestamptz,
73 last_success timestamptz
74);
75
76-- =============================================
77-- MAIN API HANDLER
78-- =============================================
79
80CREATE OR REPLACE FUNCTION handle_request(
81 method text,
82 path text,
83 headers jsonb DEFAULT '{}'::jsonb,
84 body jsonb DEFAULT '{}'::jsonb,
85 session_id uuid DEFAULT NULL
86) RETURNS jsonb AS $$
87import json
88import re
89import base64
90import hashlib
91import datetime
92import mimetypes
93
94try:
95 # Session management
96 session = {}
97 if session_id:
98 result = plpy.execute(
99 "SELECT data FROM sessions WHERE id = %s AND expires_at > now()",
100 [str(session_id)]
101 )
102 if result:
103 session = result[0]['data']
104 # Update last access
105 plpy.execute(
106 "UPDATE sessions SET last_access = now() WHERE id = %s",
107 [str(session_id)]
108 )
109
110 # Route handling
111 response = {'status': 404, 'body': {'error': 'Not found'}}
112
113 # GET routes
114 if method == 'GET':
115 if path == '/':
116 response = {
117 'status': 200,
118 'body': {
119 'message': 'Welcome to PostBlob!',
120 'endpoints': ['/blobs', '/jobs', '/stats']
121 }
122 }
123
124 elif path == '/stats':
125 stats = plpy.execute("""
126 SELECT
127 (SELECT COUNT(*) FROM blobs) as blob_count,
128 (SELECT SUM(size) FROM blobs) as total_size,
129 (SELECT COUNT(*) FROM jobs WHERE status = 'completed') as jobs_completed,
130 (SELECT COUNT(*) FROM exceptions) as total_exceptions
131 """)[0]
132 response = {'status': 200, 'body': dict(stats)}
133
134 elif path.startswith('/blob/'):
135 blob_id = path.split('/')[-1]
136 result = plpy.execute(
137 "SELECT * FROM blobs WHERE id = %s",
138 [blob_id]
139 )
140 if result:
141 blob = result[0]
142 response = {
143 'status': 200,
144 'headers': {
145 'Content-Type': blob['mimetype'],
146 'Content-Disposition': f'inline; filename="{blob["filename"]}"'
147 },
148 'body': base64.b64encode(bytes(blob['data'])).decode('utf-8'),
149 'binary': True
150 }
151 else:
152 response = {'status': 404, 'body': {'error': 'Blob not found'}}
153
154 elif path == '/jobs':
155 jobs = plpy.execute(
156 "SELECT id, type, status, created_at FROM jobs ORDER BY created_at DESC LIMIT 100"
157 )
158 response = {'status': 200, 'body': [dict(j) for j in jobs]}
159
160 # POST routes
161 elif method == 'POST':
162 if path == '/blob':
163 # Upload blob
164 filename = body.get('filename', 'unnamed')
165 data = base64.b64decode(body.get('data', ''))
166 mimetype = body.get('mimetype', mimetypes.guess_type(filename)[0] or 'application/octet-stream')
167 metadata = body.get('metadata', {})
168
169 result = plpy.execute("""
170 INSERT INTO blobs (filename, mimetype, data, size, metadata)
171 VALUES (%s, %s, %s, %s, %s)
172 RETURNING id, filename, size, created_at
173 """, [filename, mimetype, data, len(data), json.dumps(metadata)])
174
175 response = {
176 'status': 201,
177 'body': {
178 'id': str(result[0]['id']),
179 'filename': result[0]['filename'],
180 'size': result[0]['size'],
181 'url': f"/blob/{result[0]['id']}"
182 }
183 }
184
185 elif path == '/job':
186 # Queue job
187 job_type = body.get('type', 'default')
188 payload = body.get('payload', {})
189
190 result = plpy.execute("""
191 INSERT INTO jobs (type, payload)
192 VALUES (%s, %s)
193 RETURNING id, type, status, created_at
194 """, [job_type, json.dumps(payload)])
195
196 # Notify job worker
197 plpy.execute("NOTIFY new_job, %s", [str(result[0]['id'])])
198
199 response = {
200 'status': 201,
201 'body': {
202 'job_id': result[0]['id'],
203 'type': result[0]['type'],
204 'status': result[0]['status']
205 }
206 }
207
208 elif path == '/execute':
209 # Execute arbitrary Python code (for development)
210 code = body.get('code', '')
211 context = body.get('context', {})
212
213 result = plpy.execute(
214 "SELECT safe_execute(%s, %s) as result",
215 [code, json.dumps(context)]
216 )
217 response = {'status': 200, 'body': result[0]['result']}
218
219 # DELETE routes
220 elif method == 'DELETE':
221 if path.startswith('/blob/'):
222 blob_id = path.split('/')[-1]
223 result = plpy.execute(
224 "DELETE FROM blobs WHERE id = %s RETURNING id",
225 [blob_id]
226 )
227 if result:
228 response = {'status': 200, 'body': {'deleted': str(result[0]['id'])}}
229 else:
230 response = {'status': 404, 'body': {'error': 'Blob not found'}}
231
232 return json.dumps(response)
233
234except Exception as e:
235 # Log exception
236 import traceback
237 plpy.execute("""
238 INSERT INTO exceptions (error_type, message, traceback, context, function_name)
239 VALUES (%s, %s, %s, %s, 'handle_request')
240 """, [
241 type(e).__name__,
242 str(e),
243 traceback.format_exc(),
244 json.dumps({'method': method, 'path': path})
245 ])
246
247 return json.dumps({
248 'status': 500,
249 'body': {'error': str(e), 'type': type(e).__name__}
250 })
251$$ LANGUAGE plpython3u;
252
253-- =============================================
254-- JOB PROCESSOR
255-- =============================================
256
257CREATE OR REPLACE FUNCTION process_job(job_id integer) RETURNS void AS $$
258import json
259import time
260
261try:
262 # Get job details
263 job = plpy.execute(
264 "SELECT * FROM jobs WHERE id = %s FOR UPDATE",
265 [job_id]
266 )[0]
267
268 if job['status'] != 'pending':
269 return
270
271 # Mark as processing
272 plpy.execute(
273 "UPDATE jobs SET status = 'processing', started_at = now() WHERE id = %s",
274 [job_id]
275 )
276
277 result = {}
278
279 # Process different job types
280 if job['type'] == 'resize_image':
281 # Simulated image processing
282 blob_id = job['payload'].get('blob_id')
283 width = job['payload'].get('width', 800)
284 height = job['payload'].get('height', 600)
285
286 # In real implementation, would use PIL or similar
287 result = {
288 'status': 'success',
289 'message': f'Resized to {width}x{height}',
290 'blob_id': blob_id
291 }
292
293 elif job['type'] == 'analyze_text':
294 text = job['payload'].get('text', '')
295 result = {
296 'word_count': len(text.split()),
297 'char_count': len(text),
298 'lines': len(text.split('\n'))
299 }
300
301 elif job['type'] == 'cleanup':
302 # Clean old sessions and cache
303 deleted_sessions = plpy.execute(
304 "DELETE FROM sessions WHERE expires_at < now() RETURNING id"
305 )
306 deleted_cache = plpy.execute(
307 "DELETE FROM cache WHERE expires_at < now() RETURNING key"
308 )
309 result = {
310 'sessions_cleaned': len(deleted_sessions),
311 'cache_cleaned': len(deleted_cache)
312 }
313
314 elif job['type'] == 'compute':
315 # Generic computation
316 time.sleep(2) # Simulate work
317 value = job['payload'].get('value', 0)
318 operation = job['payload'].get('operation', 'double')
319
320 if operation == 'double':
321 result = {'result': value * 2}
322 elif operation == 'square':
323 result = {'result': value ** 2}
324 else:
325 result = {'result': value}
326
327 else:
328 # Unknown job type
329 result = {'error': f"Unknown job type: {job['type']}"}
330
331 # Mark as completed
332 plpy.execute("""
333 UPDATE jobs
334 SET status = 'completed',
335 result = %s,
336 completed_at = now()
337 WHERE id = %s
338 """, [json.dumps(result), job_id])
339
340except Exception as e:
341 import traceback
342 # Mark as failed and log exception
343 plpy.execute("""
344 UPDATE jobs
345 SET status = 'failed',
346 result = %s,
347 attempts = attempts + 1
348 WHERE id = %s
349 """, [json.dumps({'error': str(e), 'traceback': traceback.format_exc()}), job_id])
350
351 # Log to exceptions table
352 plpy.execute("""
353 INSERT INTO exceptions (error_type, message, traceback, context, function_name)
354 VALUES (%s, %s, %s, %s, 'process_job')
355 """, [
356 type(e).__name__,
357 str(e),
358 traceback.format_exc(),
359 json.dumps({'job_id': job_id, 'job_type': job.get('type')})
360 ])
361$$ LANGUAGE plpython3u;
362
363-- =============================================
364-- SAFE EXECUTION WRAPPER
365-- =============================================
366
367CREATE OR REPLACE FUNCTION safe_execute(code text, context jsonb DEFAULT '{}'::jsonb)
368RETURNS jsonb AS $$
369import json
370import traceback
371import sys
372
373namespace = {
374 'context': context,
375 'plpy': plpy,
376 'json': json,
377 'result': None
378}
379
380try:
381 exec(code, namespace)
382 return json.dumps({
383 'success': True,
384 'result': namespace.get('result'),
385 'output': namespace.get('output')
386 })
387
388except Exception as e:
389 exc_type, exc_value, exc_tb = sys.exc_info()
390
391 # Log exception
392 plpy.execute("""
393 INSERT INTO exceptions (error_type, message, traceback, context, function_name)
394 VALUES (%s, %s, %s, %s, 'safe_execute')
395 """, [
396 exc_type.__name__,
397 str(exc_value),
398 traceback.format_exc(),
399 json.dumps({'code_preview': code[:500], 'context': context})
400 ])
401
402 return json.dumps({
403 'success': False,
404 'error': str(e),
405 'type': exc_type.__name__,
406 'traceback': traceback.format_exc()
407 })
408$$ LANGUAGE plpython3u;
409
410-- =============================================
411-- CACHE FUNCTIONS
412-- =============================================
413
414CREATE OR REPLACE FUNCTION cache_get(cache_key text)
415RETURNS jsonb AS $$
416BEGIN
417 DELETE FROM cache WHERE expires_at < now();
418 RETURN (SELECT value FROM cache WHERE key = cache_key);
419END;
420$$ LANGUAGE plpgsql;
421
422CREATE OR REPLACE FUNCTION cache_set(
423 cache_key text,
424 cache_value jsonb,
425 ttl_seconds integer DEFAULT 3600
426) RETURNS void AS $$
427BEGIN
428 INSERT INTO cache (key, value, expires_at)
429 VALUES (cache_key, cache_value, now() + (ttl_seconds || ' seconds')::interval)
430 ON CONFLICT (key) DO UPDATE
431 SET value = EXCLUDED.value,
432 expires_at = EXCLUDED.expires_at,
433 created_at = now();
434END;
435$$ LANGUAGE plpgsql;
436
437-- =============================================
438-- BACKGROUND WORKER
439-- =============================================
440
441CREATE OR REPLACE FUNCTION job_worker_loop() RETURNS void AS $$
442import time
443import json
444
445max_runs = 1000 # Prevent infinite loops in development
446runs = 0
447
448while runs < max_runs:
449 runs += 1
450
451 try:
452 # Get next pending job
453 jobs = plpy.execute("""
454 SELECT id FROM jobs
455 WHERE status = 'pending'
456 ORDER BY created_at
457 LIMIT 1
458 FOR UPDATE SKIP LOCKED
459 """)
460
461 if jobs:
462 job_id = jobs[0]['id']
463 plpy.execute("SELECT process_job(%s)", [job_id])
464 plpy.notice(f"Processed job {job_id}")
465 else:
466 # No jobs, wait a bit
467 time.sleep(1)
468
469 except Exception as e:
470 plpy.warning(f"Worker error: {e}")
471 time.sleep(5)
472
473plpy.notice(f"Worker stopped after {runs} runs")
474$$ LANGUAGE plpython3u;
475
476-- =============================================
477-- INDEXES
478-- =============================================
479
480CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status, created_at);
481CREATE INDEX IF NOT EXISTS idx_blobs_created ON blobs(created_at DESC);
482CREATE INDEX IF NOT EXISTS idx_exceptions_occurred ON exceptions(occurred_at DESC);
483CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
484CREATE INDEX IF NOT EXISTS idx_cache_expires ON cache(expires_at);
485
486-- =============================================
487-- NOTIFICATIONS
488-- =============================================
489
490-- Job notification trigger
491CREATE OR REPLACE FUNCTION notify_new_job() RETURNS trigger AS $$
492BEGIN
493 PERFORM pg_notify('new_job', NEW.id::text);
494 RETURN NEW;
495END;
496$$ LANGUAGE plpgsql;
497
498DROP TRIGGER IF EXISTS job_created ON jobs;
499CREATE TRIGGER job_created
500 AFTER INSERT ON jobs
501 FOR EACH ROW
502 EXECUTE FUNCTION notify_new_job();
503
504-- =============================================
505-- INITIAL DATA
506-- =============================================
507
508-- Create a welcome blob
509INSERT INTO blobs (filename, mimetype, data, size, metadata)
510VALUES (
511 'welcome.txt',
512 'text/plain',
513 'Welcome to PostBlob - PostgreSQL does everything!'::bytea,
514 length('Welcome to PostBlob - PostgreSQL does everything!'),
515 '{"type": "system", "description": "Welcome message"}'::jsonb
516) ON CONFLICT DO NOTHING;
517
518-- Create sample job
519INSERT INTO jobs (type, payload, status)
520VALUES (
521 'cleanup',
522 '{"description": "Initial cleanup job"}'::jsonb,
523 'pending'
524) ON CONFLICT DO NOTHING;
525
526-- Success message
527DO $$
528BEGIN
529 RAISE NOTICE 'PostBlob database initialized successfully!';
530 RAISE NOTICE 'Tables created: blobs, jobs, exceptions, sessions, cache, circuit_breakers';
531 RAISE NOTICE 'Main functions: handle_request(), process_job(), safe_execute()';
532END $$;