4 ms·

I share the author's point of view, which led me to start a new relational programming language that compiles to SQL. It's a way to build on existing databases,

by erezsh 5y ago

I share the author's point of view, which led me to start a new relational programming language that compiles to SQL. It's a way to build on existing databases, like postgres or mysql, with all of their advantages, but improve on many of SQL's limitations. If that sounds interesting, you can find it here: https://github.com/erezsh/Preql https://github.com/erezsh/Preql

dvdkon 5y ago
Looks interesting. I've been thinking about trying this myself and one of my goals has been to create a language that's easily introspectible. I think it's much more important for a query language as opposed to an application language, since you'll want to see what code in the former does without running it for integration into application code. My approach has been to design a very simple (in the lisp sense) syntax, kind of the opposite to SQL where everything is hard-coded into the parser. I've adopted a "pipeline programming"-like approach, where the operations are just (special) functions, which also helps with extensibility. Have you thought about this? From a cursory look, it seems Preql does rely on keywords. Admittedly fewer than SQL, but it also doesn't cover all of its features.
- erezsh 5y ago
  I don't have a lot of experience writing Lisp-y code, so perhaps I'm speaking from ignorance, but I think there is a reason that syntax never gained huge traction. Imho a syntax that's concise and expressive is important for effective coding. Having operators for the most common operations is just a small complication that yields a big reward. Having said that, the amount of keywords and operators that you see in Preql right now, isn't likely to grow by much. I have the basic and common operators covered, and for the most part, the rest can be done with regular functions. I agree about introspection, which is why in Preql you can ask for the type of any value, list the columns of a table, get the resulting SQL of an expression as a string, and so on. And certainly more can and should be done towards that.
  - dvdkon 5y ago
    Thanks for the reply. I'm happy that others are also tackling the problem of a better query language. My approach isn't actually a full-on Lisp with parentheses and all, rather it's based on a single compound data structure (like Lisp's cons cell, but more like Lua tables). I call it an "arg-tuple", and it's basically a function's arguments in Python, but as a data type (which allows nesting). Add in a simple function call syntax, infix operartors and a "pipeline" operator (like F#'s |>) and you get something like this: from stops | let region :be case(zoneid, :when ("P", "0", "B") :then "Prague", :when ("1", "2") :then "Almost-Prague", :else "Regional") | where lat != 0 && lon != 0 && region != "Prague" | select name, region "from", "let", "where" and "select" are filters, functions that take a query description object and return a new one. "name, region" is just an arg-tuple with two unnamed elements and "case" is a function taking one polymorphic value and a variable number of nested arg-tuples with ":when: and ":then" named elements. Filters can be arranged in any order, unlike SQL, so this would work: from stops | group_by lat, lon | group_by lat | select min(group.lon)
    - erezsh 5y ago
      Python has a data structure like this called NamedTuple. I also noticed it's popping up everywhere, in this case sql rows (kind of). Conceptually I like the idea of using it as the mechanism for function arguments, but I didn't want the syntax to be too confusing for beginners. Although yours seems a little bit different, because different fields can have the same name. Just for the heck of it, here's how your second example would look in preql: (stops { lat, lon => } { lat => lon: min(lon) } { lon } // not necessary. just drops the lat column )
      - dvdkon 5y ago
        It's not quite that arg-tuples can contain multiple values under the same name, but rather ":a 1, :a 2" is actually an arg-tuple containing two nested arg-tuples as unnamed values. I agree that basing all of syntax on a single element (especially one that's so "light" on syntax), it's hard making the resulting syntax intuitive. I think it's worth it, but I can see other approaches' benefits.
culebron21 5y ago
I was sketching a syntax for python to work with pandas, and avoid extra annoying code, and surprisingly some of the elements and approaches I see in your code are similar. My syntax for subsetting was df{\*, -column1} df{column1: new_name1, column2, column3: new_name3} I noticed in working with pandas you often need to do lookups into other dataframes. It's partially solved by assignment operator if left field equals right index, or .map method, the same way. But often you need a lookup with merging by an arbitrary column, then grouping and aggregation by the left table items. This is partially doable without special functions. But this can be a killer feature if one makes this for spatial joins. Very often you need to do the following: gdf1 = geodataframe of points gdf2 = geodataframe of points need to make gdf1.geometry.buffer(500 m) and sjoin it with gdf2.geometry, then lookup gdf2 fields and bring them to gdf1, and keep original gdf1.geometry (points). This operation takes a dozen of lines and leaves lots of variable garbage if not put into a separate function. But IMO it could be condensed to something more natively supported.
nextaccountic 5y ago
What I really wanted was something compatible with SQL, but that augmented it (and compiled to plain sql). Like typescript is for javascript, but instead of adding a type system, adding constructs for better composability. Something like.. BQL http://intelligiblebabble.com/a-better-query-language-bql-language-specification/ http://intelligiblebabble.com/a-better-query-language-bql-la... which went nowhere https://github.com/lelandrichardson/BQL https://github.com/lelandrichardson/BQL

by erezsh 5y ago

dvdkon 5y ago

Looks interesting. I've been thinking about trying this myself and one of my goals has been to create a language that's easily introspectible. I think it's much more important for a query language as opposed to an application language, since you'll want to see what code in the former does without running it for integration into application code. My approach has been to design a very simple (in the lisp sense) syntax, kind of the opposite to SQL where everything is hard-coded into the parser. I've adopted a "pipeline programming"-like approach, where the operations are just (special) functions, which also helps with extensibility. Have you thought about this? From a cursory look, it seems Preql does rely on keywords. Admittedly fewer than SQL, but it also doesn't cover all of its features.

erezsh 5y ago
I don't have a lot of experience writing Lisp-y code, so perhaps I'm speaking from ignorance, but I think there is a reason that syntax never gained huge traction. Imho a syntax that's concise and expressive is important for effective coding. Having operators for the most common operations is just a small complication that yields a big reward. Having said that, the amount of keywords and operators that you see in Preql right now, isn't likely to grow by much. I have the basic and common operators covered, and for the most part, the rest can be done with regular functions. I agree about introspection, which is why in Preql you can ask for the type of any value, list the columns of a table, get the resulting SQL of an expression as a string, and so on. And certainly more can and should be done towards that.
- dvdkon 5y ago
  Thanks for the reply. I'm happy that others are also tackling the problem of a better query language. My approach isn't actually a full-on Lisp with parentheses and all, rather it's based on a single compound data structure (like Lisp's cons cell, but more like Lua tables). I call it an "arg-tuple", and it's basically a function's arguments in Python, but as a data type (which allows nesting). Add in a simple function call syntax, infix operartors and a "pipeline" operator (like F#'s |>) and you get something like this: from stops | let region :be case(zoneid, :when ("P", "0", "B") :then "Prague", :when ("1", "2") :then "Almost-Prague", :else "Regional") | where lat != 0 && lon != 0 && region != "Prague" | select name, region "from", "let", "where" and "select" are filters, functions that take a query description object and return a new one. "name, region" is just an arg-tuple with two unnamed elements and "case" is a function taking one polymorphic value and a variable number of nested arg-tuples with ":when: and ":then" named elements. Filters can be arranged in any order, unlike SQL, so this would work: from stops | group_by lat, lon | group_by lat | select min(group.lon)
  - erezsh 5y ago
    Python has a data structure like this called NamedTuple. I also noticed it's popping up everywhere, in this case sql rows (kind of). Conceptually I like the idea of using it as the mechanism for function arguments, but I didn't want the syntax to be too confusing for beginners. Although yours seems a little bit different, because different fields can have the same name. Just for the heck of it, here's how your second example would look in preql: (stops { lat, lon => } { lat => lon: min(lon) } { lon } // not necessary. just drops the lat column )
    - dvdkon 5y ago
      It's not quite that arg-tuples can contain multiple values under the same name, but rather ":a 1, :a 2" is actually an arg-tuple containing two nested arg-tuples as unnamed values. I agree that basing all of syntax on a single element (especially one that's so "light" on syntax), it's hard making the resulting syntax intuitive. I think it's worth it, but I can see other approaches' benefits.

culebron21 5y ago

I was sketching a syntax for python to work with pandas, and avoid extra annoying code, and surprisingly some of the elements and approaches I see in your code are similar. My syntax for subsetting was df{\*, -column1} df{column1: new_name1, column2, column3: new_name3} I noticed in working with pandas you often need to do lookups into other dataframes. It's partially solved by assignment operator if left field equals right index, or .map method, the same way. But often you need a lookup with merging by an arbitrary column, then grouping and aggregation by the left table items. This is partially doable without special functions. But this can be a killer feature if one makes this for spatial joins. Very often you need to do the following: gdf1 = geodataframe of points gdf2 = geodataframe of points need to make gdf1.geometry.buffer(500 m) and sjoin it with gdf2.geometry, then lookup gdf2 fields and bring them to gdf1, and keep original gdf1.geometry (points). This operation takes a dozen of lines and leaves lots of variable garbage if not put into a separate function. But IMO it could be condensed to something more natively supported.

nextaccountic 5y ago

What I really wanted was something compatible with SQL, but that augmented it (and compiled to plain sql). Like typescript is for javascript, but instead of adding a type system, adding constructs for better composability. Something like.. BQL http://intelligiblebabble.com/a-better-query-language-bql-language-specification/ http://intelligiblebabble.com/a-better-query-language-bql-la... which went nowhere https://github.com/lelandrichardson/BQL https://github.com/lelandrichardson/BQL