askrobots / postblob Public

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

0 downloads

postblob/natural_sql.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
natural_sql.sql · 18.5 KB · 597 lines
1-- Natural Language SQL Parser for PostgreSQL
2-- Converts English-like queries to SQL and executes them
3
4-- Add to your PostBlob database
5-- psql postblob < natural_sql.sql
6
7-- =============================================
8-- NATURAL LANGUAGE QUERY PARSER
9-- =============================================
10
11CREATE OR REPLACE FUNCTION natural_query(query_text text)
12RETURNS TABLE(result jsonb) AS $$
13import re
14import json
15
16def parse_natural_query(text):
17 """
18 Parse natural language queries into SQL
19
20 Examples:
21 - "get all users"
22 - "show me users where name is john"
23 - "find products with price greater than 100"
24 - "give me the first 5 orders from yesterday"
25 - "count users who signed up this week"
26 - "delete expired sessions"
27 - "update user dan set status to active"
28 """
29
30 # Normalize the text
31 text = text.lower().strip()
32 text = re.sub(r'\s+', ' ', text) # Multiple spaces to single
33
34 # Common word replacements
35 replacements = {
36 'get all': 'SELECT * FROM',
37 'get': 'SELECT',
38 'show me': 'SELECT * FROM',
39 'show': 'SELECT',
40 'find': 'SELECT * FROM',
41 'give me': 'SELECT',
42 'fetch': 'SELECT * FROM',
43 'list': 'SELECT * FROM',
44 'display': 'SELECT * FROM',
45
46 # Conditions
47 'with': 'WHERE',
48 'having': 'WHERE',
49 'that have': 'WHERE',
50 'who have': 'WHERE',
51 'which have': 'WHERE',
52 'that has': 'WHERE',
53 'who has': 'WHERE',
54 'which has': 'WHERE',
55
56 # Operators
57 'equals to': '=',
58 'equal to': '=',
59 'equals': '=',
60 'is': '=',
61 'are': '=',
62 'greater than': '>',
63 'more than': '>',
64 'above': '>',
65 'less than': '<',
66 'fewer than': '<',
67 'below': '<',
68 'at least': '>=',
69 'at most': '<=',
70 'contains': 'LIKE',
71 'includes': 'LIKE',
72 'starts with': 'LIKE',
73 'ends with': 'LIKE',
74 'between': 'BETWEEN',
75
76 # Logical
77 'and': 'AND',
78 'or': 'OR',
79 'not': 'NOT',
80 'isnt': 'IS NOT',
81 "isn't": 'IS NOT',
82 'is not': 'IS NOT',
83
84 # Aggregates
85 'count': 'COUNT(*)',
86 'sum of': 'SUM',
87 'total': 'SUM',
88 'average': 'AVG',
89 'mean': 'AVG',
90 'maximum': 'MAX',
91 'minimum': 'MIN',
92 'biggest': 'MAX',
93 'smallest': 'MIN',
94 'largest': 'MAX',
95
96 # Ordering
97 'sorted by': 'ORDER BY',
98 'ordered by': 'ORDER BY',
99 'sort by': 'ORDER BY',
100 'ascending': 'ASC',
101 'descending': 'DESC',
102 'newest first': 'ORDER BY created_at DESC',
103 'oldest first': 'ORDER BY created_at ASC',
104 'alphabetically': 'ORDER BY',
105
106 # Limits
107 'first': 'LIMIT',
108 'top': 'LIMIT',
109 'last': 'ORDER BY id DESC LIMIT',
110 'bottom': 'ORDER BY id ASC LIMIT',
111
112 # Time
113 'today': "date_trunc('day', now())",
114 'yesterday': "date_trunc('day', now() - interval '1 day')",
115 'this week': "date_trunc('week', now())",
116 'last week': "date_trunc('week', now() - interval '1 week')",
117 'this month': "date_trunc('month', now())",
118 'last month': "date_trunc('month', now() - interval '1 month')",
119 'this year': "date_trunc('year', now())",
120
121 # Tables (add your tables here)
122 'users': 'users',
123 'user': 'users',
124 'people': 'users',
125 'person': 'users',
126 'files': 'blobs',
127 'file': 'blobs',
128 'blob': 'blobs',
129 'blobs': 'blobs',
130 'jobs': 'jobs',
131 'job': 'jobs',
132 'tasks': 'jobs',
133 'task': 'jobs',
134 'errors': 'exceptions',
135 'error': 'exceptions',
136 'exceptions': 'exceptions',
137 'exception': 'exceptions',
138 'session': 'sessions',
139 'sessions': 'sessions',
140 }
141
142 # Build SQL query
143 sql = text
144
145 # Apply replacements
146 for eng, sql_term in replacements.items():
147 sql = re.sub(r'\b' + eng + r'\b', sql_term, sql, flags=re.IGNORECASE)
148
149 # Handle specific patterns
150
151 # Pattern: "users named john" -> "users WHERE name = 'john'"
152 sql = re.sub(r'(\w+) named (\w+)', r"\1 WHERE name = '\2'", sql)
153
154 # Pattern: "users with name john" -> "users WHERE name = 'john'"
155 sql = re.sub(r'WHERE (\w+) (\w+)', r"WHERE \1 = '\2'", sql)
156
157 # Pattern: "5 users" -> "users LIMIT 5"
158 sql = re.sub(r'(\d+) (\w+)', r'\2 LIMIT \1', sql)
159
160 # Pattern: "count of users" -> "COUNT(*) FROM users"
161 sql = re.sub(r'COUNT\(\*\) of (\w+)', r'COUNT(*) FROM \1', sql)
162
163 # Pattern: "sum salary from employees" -> "SUM(salary) FROM employees"
164 sql = re.sub(r'SUM (\w+) from (\w+)', r'SUM(\1) FROM \2', sql)
165
166 # Add SELECT if missing
167 if not any(sql.startswith(cmd) for cmd in ['SELECT', 'INSERT', 'UPDATE', 'DELETE']):
168 if 'FROM' in sql:
169 sql = 'SELECT * ' + sql
170 else:
171 # Assume first word is table name
172 parts = sql.split(' ', 1)
173 if len(parts) == 1:
174 sql = f'SELECT * FROM {parts[0]}'
175 else:
176 sql = f'SELECT * FROM {sql}'
177
178 # Handle LIKE patterns
179 sql = re.sub(r"LIKE '(\w+)'", r"LIKE '%\1%'", sql)
180 sql = re.sub(r"LIKE '(\w+)%'", r"LIKE '\1%'", sql)
181 sql = re.sub(r"LIKE '%(\w+)'", r"LIKE '%\1'", sql)
182
183 # Clean up extra spaces
184 sql = re.sub(r'\s+', ' ', sql).strip()
185
186 # Add semicolon if missing
187 if not sql.endswith(';'):
188 sql += ';'
189
190 return sql
191
192def execute_parsed_query(sql):
193 """Execute the parsed SQL and return results"""
194 try:
195 # Log the generated SQL for debugging
196 plpy.notice(f"Generated SQL: {sql}")
197
198 # Execute the query
199 result = plpy.execute(sql)
200
201 # Format results as JSON
202 if result:
203 return [dict(row) for row in result]
204 else:
205 return {'message': 'Query executed successfully', 'affected_rows': result.nrows() if hasattr(result, 'nrows') else 0}
206
207 except Exception as e:
208 return {'error': str(e), 'sql': sql}
209
210# Parse and execute
211sql = parse_natural_query(query_text)
212results = execute_parsed_query(sql)
213
214# Return as JSON rows
215if isinstance(results, list):
216 for row in results:
217 yield (json.dumps(row),)
218else:
219 yield (json.dumps(results),)
220
221$$ LANGUAGE plpython3u;
222
223-- =============================================
224-- SIMPLER VERSION - PATTERN MATCHING
225-- =============================================
226
227CREATE OR REPLACE FUNCTION ask(question text)
228RETURNS jsonb AS $$
229DECLARE
230 sql_query text;
231 result jsonb;
232BEGIN
233 -- Simple pattern matching for common queries
234 sql_query := CASE
235 -- "show users"
236 WHEN question ~* '^show\s+users?$' THEN
237 'SELECT * FROM users'
238
239 -- "count users"
240 WHEN question ~* '^count\s+users?$' THEN
241 'SELECT COUNT(*) as count FROM users'
242
243 -- "show user dan"
244 WHEN question ~* '^show\s+user\s+(\w+)$' THEN
245 format('SELECT * FROM users WHERE username = %L',
246 (regexp_match(question, 'show\s+user\s+(\w+)', 'i'))[1])
247
248 -- "show 5 users"
249 WHEN question ~* '^show\s+(\d+)\s+users?$' THEN
250 format('SELECT * FROM users LIMIT %s',
251 (regexp_match(question, 'show\s+(\d+)', 'i'))[1])
252
253 -- "users where age > 25"
254 WHEN question ~* '^users?\s+where\s+(.+)$' THEN
255 format('SELECT * FROM users WHERE %s',
256 (regexp_match(question, 'where\s+(.+)$', 'i'))[1])
257
258 -- "delete old sessions"
259 WHEN question ~* '^delete\s+old\s+sessions?$' THEN
260 'DELETE FROM sessions WHERE expires_at < now()'
261
262 -- "biggest file"
263 WHEN question ~* '^biggest\s+file$' THEN
264 'SELECT * FROM blobs ORDER BY size DESC LIMIT 1'
265
266 -- "newest jobs"
267 WHEN question ~* '^newest\s+jobs?$' THEN
268 'SELECT * FROM jobs ORDER BY created_at DESC LIMIT 10'
269
270 -- "failed jobs"
271 WHEN question ~* '^failed\s+jobs?$' THEN
272 'SELECT * FROM jobs WHERE status = ''failed'''
273
274 ELSE
275 NULL
276 END;
277
278 IF sql_query IS NULL THEN
279 RETURN jsonb_build_object(
280 'error', 'Could not understand query',
281 'original', question,
282 'hint', 'Try: "show users", "count jobs", "show user dan", etc.'
283 );
284 END IF;
285
286 -- Execute the query
287 BEGIN
288 EXECUTE 'SELECT jsonb_agg(row_to_json(t)) FROM (' || sql_query || ') t' INTO result;
289
290 IF result IS NULL THEN
291 result := '[]'::jsonb;
292 END IF;
293
294 RETURN jsonb_build_object(
295 'success', true,
296 'sql', sql_query,
297 'data', result
298 );
299 EXCEPTION WHEN OTHERS THEN
300 RETURN jsonb_build_object(
301 'error', SQLERRM,
302 'sql', sql_query,
303 'original', question
304 );
305 END;
306END;
307$$ LANGUAGE plpgsql;
308
309-- =============================================
310-- ADVANCED ENGLISH-LIKE SYNTAX
311-- =============================================
312
313CREATE OR REPLACE FUNCTION english_sql(command text)
314RETURNS jsonb AS $$
315import re
316import json
317
318class EnglishSQLParser:
319 def __init__(self):
320 self.patterns = [
321 # "from users get username and email where age exceeds 21"
322 (r"from (\w+) get ([\w\s,]+) where (.*)", self.parse_from_get_where),
323
324 # "in the users table find all records where name contains john"
325 (r"in the (\w+) table find (.*) where (.*)", self.parse_in_table_find),
326
327 # "grab the username and uuid from users for username dan"
328 (r"grab the ([\w\s,]+) from (\w+) for (\w+) (.*)", self.parse_grab_from_for),
329
330 # "pull all data about dan from the users collection"
331 (r"pull (.*) about (\w+) from the (\w+)", self.parse_pull_about_from),
332
333 # "how many users have status active"
334 (r"how many (\w+) have (\w+) (.*)", self.parse_how_many_have),
335
336 # "what is the total size of all blobs"
337 (r"what is the (.*) of all (\w+)", self.parse_what_is_aggregate),
338
339 # "store username:john email:john@example.com in users"
340 (r"store (.*) in (\w+)", self.parse_store_in),
341
342 # "change status to active for user dan"
343 (r"change (\w+) to (.*) for (\w+) (.*)", self.parse_change_to_for),
344
345 # "remove all expired items from cache"
346 (r"remove all (.*) from (\w+)", self.parse_remove_from),
347 ]
348
349 def parse_from_get_where(self, match):
350 table = match.group(1)
351 columns = match.group(2).replace(' and ', ', ').replace(' ', '')
352 condition = self.parse_condition(match.group(3))
353 return f"SELECT {columns} FROM {table} WHERE {condition}"
354
355 def parse_in_table_find(self, match):
356 table = match.group(1)
357 what = match.group(2)
358 condition = self.parse_condition(match.group(3))
359
360 if 'all' in what:
361 columns = '*'
362 else:
363 columns = what.replace('records', '*').replace('rows', '*')
364
365 return f"SELECT {columns} FROM {table} WHERE {condition}"
366
367 def parse_grab_from_for(self, match):
368 columns = match.group(1).replace(' and ', ', ').replace(' ', '')
369 table = match.group(2)
370 field = match.group(3)
371 value = match.group(4).strip()
372
373 # Handle quotes if not present
374 if not (value.startswith("'") or value.startswith('"')):
375 value = f"'{value}'"
376
377 return f"SELECT {columns} FROM {table} WHERE {field} = {value}"
378
379 def parse_pull_about_from(self, match):
380 what = match.group(1)
381 identifier = match.group(2)
382 table = match.group(3).replace(' collection', '').replace(' table', '')
383
384 columns = '*' if 'all' in what else what
385
386 # Guess the ID field
387 id_field = 'username' if table == 'users' else 'name'
388
389 return f"SELECT {columns} FROM {table} WHERE {id_field} = '{identifier}'"
390
391 def parse_how_many_have(self, match):
392 table = match.group(1)
393 field = match.group(2)
394 value = match.group(3).strip()
395
396 if not (value.startswith("'") or value.startswith('"')):
397 value = f"'{value}'"
398
399 return f"SELECT COUNT(*) as count FROM {table} WHERE {field} = {value}"
400
401 def parse_what_is_aggregate(self, match):
402 aggregate = match.group(1)
403 table = match.group(2)
404
405 if 'total size' in aggregate:
406 return f"SELECT SUM(size) as total_size FROM {table}"
407 elif 'count' in aggregate:
408 return f"SELECT COUNT(*) as count FROM {table}"
409 elif 'average' in aggregate or 'mean' in aggregate:
410 field = aggregate.split()[-1]
411 return f"SELECT AVG({field}) as average FROM {table}"
412 else:
413 return f"SELECT * FROM {table}"
414
415 def parse_store_in(self, match):
416 data = match.group(1)
417 table = match.group(2)
418
419 # Parse key:value pairs
420 pairs = re.findall(r'(\w+):([^\s]+)', data)
421 columns = ', '.join([p[0] for p in pairs])
422 values = ', '.join([f"'{p[1]}'" if not p[1].isdigit() else p[1] for p in pairs])
423
424 return f"INSERT INTO {table} ({columns}) VALUES ({values})"
425
426 def parse_change_to_for(self, match):
427 field = match.group(1)
428 value = match.group(2)
429 entity_type = match.group(3)
430 identifier = match.group(4).strip()
431
432 # Map entity type to table
433 table = entity_type + 's' if not entity_type.endswith('s') else entity_type
434 id_field = 'username' if table == 'users' else 'id'
435
436 if not (value.startswith("'") or value.startswith('"')) and not value.isdigit():
437 value = f"'{value}'"
438
439 return f"UPDATE {table} SET {field} = {value} WHERE {id_field} = '{identifier}'"
440
441 def parse_remove_from(self, match):
442 what = match.group(1)
443 table = match.group(2)
444
445 if 'expired' in what:
446 return f"DELETE FROM {table} WHERE expires_at < now()"
447 elif 'old' in what:
448 return f"DELETE FROM {table} WHERE created_at < now() - interval '30 days'"
449 else:
450 condition = self.parse_condition(what)
451 return f"DELETE FROM {table} WHERE {condition}"
452
453 def parse_condition(self, text):
454 """Parse condition text into SQL WHERE clause"""
455 text = text.strip()
456
457 # Replace English operators
458 text = re.sub(r'\bexceeds\b', '>', text)
459 text = re.sub(r'\bis greater than\b', '>', text)
460 text = re.sub(r'\bis less than\b', '<', text)
461 text = re.sub(r'\bcontains\b', 'LIKE', text)
462 text = re.sub(r'\bstarts with\b', 'LIKE', text)
463 text = re.sub(r'\bends with\b', 'LIKE', text)
464 text = re.sub(r'\bis\b', '=', text)
465 text = re.sub(r'\bequals\b', '=', text)
466
467 # Handle LIKE patterns
468 if 'LIKE' in text:
469 # Extract the value after LIKE
470 parts = text.split('LIKE')
471 if len(parts) == 2:
472 value = parts[1].strip()
473 if not (value.startswith("'") or value.startswith('"')):
474 value = f"'%{value}%'"
475 text = f"{parts[0].strip()} LIKE {value}"
476
477 # Add quotes to string values
478 words = text.split()
479 for i, word in enumerate(words):
480 if i > 0 and words[i-1] in ['=', '>', '<', '>=', '<=', '!=']:
481 if not (word.startswith("'") or word.startswith('"')) and not word.isdigit():
482 words[i] = f"'{word}'"
483
484 return ' '.join(words)
485
486 def parse(self, command):
487 """Main parsing method"""
488 command = command.strip()
489
490 # Try each pattern
491 for pattern, handler in self.patterns:
492 match = re.match(pattern, command, re.IGNORECASE)
493 if match:
494 return handler(match)
495
496 # Fallback to simple interpretation
497 return self.simple_parse(command)
498
499 def simple_parse(self, command):
500 """Simple fallback parser"""
501 command_lower = command.lower()
502
503 if command_lower.startswith('show'):
504 parts = command_lower.replace('show', '').strip().split()
505 if parts:
506 table = parts[0]
507 return f"SELECT * FROM {table}"
508
509 elif command_lower.startswith('count'):
510 parts = command_lower.replace('count', '').strip().split()
511 if parts:
512 table = parts[0]
513 return f"SELECT COUNT(*) as count FROM {table}"
514
515 return None
516
517# Parse the command
518parser = EnglishSQLParser()
519sql = parser.parse(command)
520
521if sql is None:
522 return json.dumps({
523 'error': 'Could not parse command',
524 'command': command,
525 'hint': 'Try: "from users get username where age exceeds 21"'
526 })
527
528try:
529 # Execute the SQL
530 plpy.notice(f"Executed SQL: {sql}")
531
532 if sql.upper().startswith('SELECT'):
533 result = plpy.execute(sql)
534 data = [dict(row) for row in result]
535 return json.dumps({
536 'success': True,
537 'sql': sql,
538 'data': data,
539 'count': len(data)
540 })
541 else:
542 # INSERT, UPDATE, DELETE
543 result = plpy.execute(sql)
544 return json.dumps({
545 'success': True,
546 'sql': sql,
547 'affected_rows': result.nrows() if hasattr(result, 'nrows') else 0
548 })
549
550except Exception as e:
551 return json.dumps({
552 'error': str(e),
553 'sql': sql,
554 'command': command
555 })
556
557$$ LANGUAGE plpython3u;
558
559-- =============================================
560-- EXAMPLE USAGE
561-- =============================================
562
563/*
564-- Simple queries:
565SELECT * FROM natural_query('show all users');
566SELECT * FROM natural_query('get users where name is john');
567SELECT * FROM natural_query('find the first 5 blobs');
568SELECT * FROM natural_query('count jobs that are failed');
569
570-- Pattern matching:
571SELECT ask('show users');
572SELECT ask('count users');
573SELECT ask('show user dan');
574SELECT ask('failed jobs');
575
576-- English-like syntax:
577SELECT english_sql('from users get username and email where age exceeds 21');
578SELECT english_sql('grab the username and uuid from users for username dan');
579SELECT english_sql('in the users table find all records where status is active');
580SELECT english_sql('how many users have status active');
581SELECT english_sql('what is the total size of all blobs');
582SELECT english_sql('remove all expired items from cache');
583*/
584
585-- Create a helper view for testing
586CREATE OR REPLACE VIEW natural_sql_examples AS
587SELECT
588 'natural_query(''show all users'')' as example,
589 'Lists all users' as description
590UNION ALL
591SELECT
592 'ask(''show user dan'')' as example,
593 'Show specific user' as description
594UNION ALL
595SELECT
596 'english_sql(''from users get username and email where age exceeds 21'')' as example,
597 'English-like syntax' as description;