9
\$\begingroup\$

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

Sandbox Link

\$\endgroup\$
9
  • 4
    \$\begingroup\$ May AND and OR be mixed together? If so, what is their precedence? \$\endgroup\$ – Arnauld May 18 '20 at 15:06
  • 1
    \$\begingroup\$ I guess column1 = column2 comparisons are not supported by the language. \$\endgroup\$ – manatwork May 18 '20 at 15:11
  • \$\begingroup\$ Left to right, so A=1 AND B=2 OR C=3 AND D=4 is ((A==1 and B==2) or C==3) and D==4 \$\endgroup\$ – bigyihsuan May 18 '20 at 15:12
  • 1
    \$\begingroup\$ Can we assume there won't be any spaces between the column = value checks? \$\endgroup\$ – Kevin Cruijssen May 18 '20 at 15:39
  • 1
    \$\begingroup\$ What does a row like [123, "", [980, "abc"], "false"], mean, in particular, the third value. How does that work with the = operator? \$\endgroup\$ – Abigail May 18 '20 at 16:07
7
\$\begingroup\$

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.

\$\endgroup\$
4
  • \$\begingroup\$ Assuming a global variable is not allowed. \$\endgroup\$ – Bubbler May 3 at 8:31
  • \$\begingroup\$ I see. Updated as instructed. Thanks. \$\endgroup\$ – blhsing May 3 at 8:37
  • \$\begingroup\$ -5 bytes \$\endgroup\$ – caird coinheringaahing May 3 at 21:53
  • \$\begingroup\$ @cairdcoinheringaahing Ah, did not realize that tuple assignment is more wasteful than semicolon-separated simple statements. The redundant space before the star too. Thanks! \$\endgroup\$ – blhsing May 4 at 0:43
6
+400
\$\begingroup\$

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.

\$\endgroup\$
3
  • 1
    \$\begingroup\$ c is only used once so you can inline c.split \$\endgroup\$ – Razetime Apr 29 at 5:14
  • 1
    \$\begingroup\$ [] is perfectly fine for arrays \$\endgroup\$ – bigyihsuan Apr 29 at 6:07
  • 1
    \$\begingroup\$ -18 bytes \$\endgroup\$ – caird coinheringaahing May 3 at 21:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.