SELECT ALL FROM SelectQL WHERE (answer="short" OR NOT length=10000)

Question

I've invented an SQL-like language. Let's call it SelectQL. You can't INSERT anything, you can only SELECT, and I don't have the time for an interpreter. How about you help?

The Challenge

Write a full program or a function that takes in a database of tables and a valid SelectQL SELECT string, interprets the string, and outputs the resulting SELECTion.

SELECT Specification

The SELECT SelectQL statement is as follows (optional portions are in curly brackets):

SELECT [column] FROM [table] {WHERE ( [condition] {[AND/OR] [condition] ... } ) }

• [column] can be ALL for selecting all columns, or a valid column name in the [table].
• [table] is the name of a table.
• The WHERE clause is optional. A WHERE clause is followed by a list of [condition] separated by any of AND or OR, surrounded by parentheses ().
• A [condition] is a key-value pair separated by an equals sign =.
• [condition] can be preceded with NOT to select the complement of the condition.
• Operator Precedence: NOT has higher precedence than AND and OR. AND and OR is executed left to right.
• Conditions involving arrays/lists ({980, "abc"} in data below) will look for the existence of a requested value in the array, and return the whole array if true.
• key must be a column in [table].
• SelectQL query strings are case insensitive.

Table Format

Your program should take in an SelectQL database (array of arrays, list of lists, delimited string, JSON object, etc) in the following format:

[
    [table name, (column names), [(row), (row), ...]],
    [table name, (column names), [(row), (row), ...]]
]

• table name is the name of the table.
• [column names] is a list of unique strings which are column names. These are the keys in [condition].
• Each column's values all must have the same type.
• [row] is a list-like of values. Each row must be the same length as [column names].
• There can be no [row]s.
• Elements in [row] cannot be empty. Empty strings or arrays are not considered empty.

Types

There are three types in SelectQL: number, string, array.

Numbers are at least 1 digit in 0-9, with an optional decimal point with at least 1 digit.

Strings are double-quote-delimited sequences of characters. Empty strings are possible ("").

Arrays are curly-bracket-delimited comma-separated sequences of numbers, strings, or arrays. Arrays can be heterogeneous. Empty arrays are possible ({}).

Input

• A valid SelectQL database, and
• A valid SelectQL SELECT query. It will always SELECT a valid table in the database.

Output

• The result of the query. The result must have the columns as well as the resulting rows.

Examples

The examples will use the following database:

[
    [data, (d1, d2, 3, ~!@#), [(123, "", {980, "abc"}, "false"), (456, "this isn't empty!", {{},{}}, "-123.4444"), (1, "2", {3, 4}, "five")]],
    [no, (yes, a, 1.5, what), []]
]

Format:
string --> result
-----------------
"SELECT ALL FROM data"
        --> [(d1, D2, 3, ~!@#), [(123, "", {980, "aBc"}, "false"), (456, "this isn't empty!", {{},{}}, "-123.4444"), (1, "2", {3, 4}, "five")]]

"SELECT D1 FROM DATA"
        --> [(d1), [(123), (456), (1)]]

"SELECT d1, 3 FROM data"
        --> [(d1, 3), [(123, {980, "aBc"}), (456, {{},{}}), (1, {3,4})]]

"SELECT ALL FROM DATA WHERE (NOT d1=123)"
        --> [(d1, D2, 3, ~!@#), [(456, "this isn't empty!", {{},{}}, "-123.4444"), (1, "2", [3, 4], "five")]]

"SELECT all FROM data WHERE (d1=123)"
        --> [(d1, D2, 3, ~!@#), [(123, "", [980, "aBc"], "false")]

"SELECT ~!@# FROM DATA WHERE (3={} OR d2="")"
        --> [(~!@#), [("false"), ("-123.4444")]

"SELECT all FROM DATA WHERE (d1=2)"
        --> [(d1, d2, 3, ~!@#), []]

"select all from no"
        --> [(yes, a, 1.5, what), []]

"select 3 from data where (3=980)"
        --> [(3),[[{980, "aBc"}]]

Additional Rules/Clarifications

• Standard loopholes are forbidden.
• Input and output can be in any reasonable format.
• Please explain your answers.
• This is code-golf, so the program with the smallest number of bytes wins!

Sandbox Link

Answer 1

Python 3.8 (pre-release), 473 bytes

from re import*
def s(d,q):
 o,f,c=search(r' (?:all|(.*?)) from (\S+)(?:.*?\((.*)\))?',q,I).groups()
 for x,l,r in d:
  u=str.lower;h=bool;m=o and o.split(', ')or l;y=[]
  for g in r:
   p=0;_,*t=split('(?: (?:(and)|or) |^)(not )?(.*?)=',c or'',0,I)
   while t:
    a,n,k,v,*t=t
    p=h(a)&p&(b:=h(n)^any(i==u(k)and(j==eval(v)or list==type(j)and eval(v)in j)for i,j in zip(l,g)))|p|b
   if 1-h(c)|p:y+=[j for i,j in zip(l,g)for z in m if i==u(z)],
  if u(f)==x:print([m,y])

Try it online!

This uses @hyper-neutrino's style of input/output, but is otherwise a completely different implementation.

-5 bytes thanks to @cairdcoinheringaahing.

Answer 2

Python 3, 682 bytes

from regex import*
s=lambda x:str==type(x)and x.lower()or list==type(x)and[*map(s,x)]or x
V=eval
E=lambda x:str(V(x.group()))
def f(d,q):
	c,t,w=match("select (.+) from (.+?)(?: where \((.+)\))?$",s(q)).groups()
	K=[[C for C in map(s,y)if{*c.split(", ")}&{"all",C}]for x,y,z in d if s(x)==t][0];r=[]
	for R in{s(x):[{s(k):l for k,l in zip(y,r)}for r in z]for x,y,z in d}[t]:
		if w:
			v=sub("not \w+",E,sub(r'(\S+)=(\[((?2)(, *(?2))*)?\]|"[^"]*"|\d+)',lambda x:str(e(s(R[x.group(1)]),s(V(x.group(2))))),w))
			while len(v)>5:v=sub("\w+ \w+ \w+",E,v,1)
			if~-V(v):continue
		r+=R,
	print([K,[[q[x]for x in K]for q in r]])
e=lambda x,y:x==y or list==type(x)and any(e(v,y)for v in x)

Try it online!

-5 bytes thanks to Razetime
-18 bytes thanks to caird coinheringaahing

this was painful

This uses [] for arrays because that's Python's list representation. Please let me know if this is invalid. The DB is just taken in the representation given in the question as a native Python object.

Answer 3

Python 3, 583 bytes:

import re, operator as o
def f(a,q):
 [[c,b]],z=[(c,d)for t,c,d in a if t==(r:=re.findall(' (.+) FROM (\w+)',q)[0])[1]],lambda n,m,w:[o.pos,o.not_][w](n in m if type(m)==set else n==m)
 d=[dict(zip(c,i))for i in b]
 p=lambda e,i:(eval((v:=e.pop().split('='))[-1]),i[re.sub('NOT\s','',v[0])],v[0][:3]=='NOT')
 return (g:=[c,r[0].split(', ')][r[0]!='ALL']),[[i[j]for j in g]for i in d if(y:=lambda e,u:True if not e else(z(*p(e,i))if not e[:-1]or not u else[o.and_,o.or_][u.pop()=='OR'](z(*p(e,i)),y(e,u))))(re.findall('(?:(?:NOT\s)*\w+\=(?:\{.*?\}|".*"|\w+))+', q),re.findall('AND|OR',q))]