4 ms·

>Frankly, it seems like some sort of macro system is needed Views, materialized views, CTEs.

fny 4y ago
No, these are all highly coupled to the underlying data structure. Even stored procedures tend to know a bit too much. Say I have a table with the following properties: - It has a column that corresponds to an id - It has a column that corresponds to a category - It has a column that corresponds to a value - It has a column that corresponds to a timestamp - It has a bunch of other columns I want to have a general transformation that allows me to: Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max. The stored procedure ends up being a mashup of string concatenation. Meanwhile, it just feels something is missing to make the language expressive enough.
- zasdffaa 4y ago
  > No, these are all highly coupled to the underlying data structure Difficult to be sure what you mean, but I think you're wrong. You can select from any table source (edit: result set, if you prefer that term). > Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max. select min(category) as mincat, max(category) as maxcat, min(value) as minval, -- etc where ts between <start> and <end> I am not sure what you're asking for if the above is not it. Can you give an example please?
  - setr 4y ago
    He wants to do the same thing, but on 100 different tables. The only thing that changes is the tablename and column names involved. You can copy, paste and edit 100 times and create 100 view objects Or in eg numpy-python, you’d write a single function taking the table/columns as arguments, and reuse it. I think the usual SQL solution would be to implement a stored function and start smashing strings together
    - dmux 4y ago
      That makes sense -- in that case my first instinct would be to attempt using the "information schema" (metadata about tables and their columns) to get the tables I'm interested in (and their columns). Luckily all that metadata is presented as regular old tables itself, so it's pretty straightforward to query!
      - setr 4y ago
        That’s what he means by it’s coupled to the underlying data structure; you can’t simply define a general purpose utility function to accomplish this (without smashing strings), that could be applied to arbitrary tables — eg on an arbitrary schema, or even on unnamed tables (eg resultset of a select query) Which doesn’t matter in many of these simple examples, but this is the thing that really stops you from getting to have your import gravity ecosystem of utilities that modern application programming enjoys. And the continuing buildup of libraries on top of libraries for further complexity. Instead you’re limited to extensions/packages, which are coupled tightly to the RDBMS internals (which is why you don’t see much of SQL extensions shared across DBs, despite at the end of the day operating all on the same shared framework [relations]) and whatever functionality the database provider has deemed fit to provide (perhaps at cost)
    - zasdffaa 4y ago
      So you do that on 100 different tables - then what? Join them, return them to the client, what? Stuff's missing here.
      - setr 4y ago
        Let’s say three usecases: 1. To Union them into multiple views 2. To instantiate individual views per query, to reuse in other queries 3. To return to client directly I don’t see how this expansion will help you, but go wild. The problem remains that you can’t separate the logic of the view from the schema it’s acting upon
- dmux 4y ago
  >Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max. The min/max aggregations over a period of time per category is pretty straight forward (aggregating rows), but what do you mean by "along with the other columns linked to min/max"? How would other columns come into play after aggregating rows?
- emidln 4y ago
  Perhaps you're looking for a way of arranging SQL as an AST represented by data structures (or objects) that can be fed to a compiler. HoneySQL[0] is one such implementation of this idea and it makes your general transformation trivial for Clojure programs. You don't need to mess around with string concatenation because you have a predictable and extensible compiler for data structures (which are themselves easily composable/transformable/storable with Clojure) that you can trust to do the right thing. If you're using some weird database or need an esoteric syntax, extending the compiler to your clause is easy to do[1]. [0] https://github.com/seancorfield/honeysql https://github.com/seancorfield/honeysql [1] https://github.com/seancorfield/honeysql#extensibility https://github.com/seancorfield/honeysql#extensibility
  - fny 4y ago
    I've started to toy with this already and using a tool like sql parse to abstract existing queries. Like say you have a query that applies to one table and potentially others where the column names are slightly different. Why cant you just port that query?

fny 4y ago

No, these are all highly coupled to the underlying data structure. Even stored procedures tend to know a bit too much. Say I have a table with the following properties: - It has a column that corresponds to an id - It has a column that corresponds to a category - It has a column that corresponds to a value - It has a column that corresponds to a timestamp - It has a bunch of other columns I want to have a general transformation that allows me to: Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max. The stored procedure ends up being a mashup of string concatenation. Meanwhile, it just feels something is missing to make the language expressive enough.

zasdffaa 4y ago
> No, these are all highly coupled to the underlying data structure Difficult to be sure what you mean, but I think you're wrong. You can select from any table source (edit: result set, if you prefer that term). > Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max. select min(category) as mincat, max(category) as maxcat, min(value) as minval, -- etc where ts between <start> and <end> I am not sure what you're asking for if the above is not it. Can you give an example please?
- setr 4y ago
  He wants to do the same thing, but on 100 different tables. The only thing that changes is the tablename and column names involved. You can copy, paste and edit 100 times and create 100 view objects Or in eg numpy-python, you’d write a single function taking the table/columns as arguments, and reuse it. I think the usual SQL solution would be to implement a stored function and start smashing strings together
  - dmux 4y ago
    That makes sense -- in that case my first instinct would be to attempt using the "information schema" (metadata about tables and their columns) to get the tables I'm interested in (and their columns). Luckily all that metadata is presented as regular old tables itself, so it's pretty straightforward to query!
    - setr 4y ago
      That’s what he means by it’s coupled to the underlying data structure; you can’t simply define a general purpose utility function to accomplish this (without smashing strings), that could be applied to arbitrary tables — eg on an arbitrary schema, or even on unnamed tables (eg resultset of a select query) Which doesn’t matter in many of these simple examples, but this is the thing that really stops you from getting to have your import gravity ecosystem of utilities that modern application programming enjoys. And the continuing buildup of libraries on top of libraries for further complexity. Instead you’re limited to extensions/packages, which are coupled tightly to the RDBMS internals (which is why you don’t see much of SQL extensions shared across DBs, despite at the end of the day operating all on the same shared framework [relations]) and whatever functionality the database provider has deemed fit to provide (perhaps at cost)
  - zasdffaa 4y ago
    So you do that on 100 different tables - then what? Join them, return them to the client, what? Stuff's missing here.
    - setr 4y ago
      Let’s say three usecases: 1. To Union them into multiple views 2. To instantiate individual views per query, to reuse in other queries 3. To return to client directly I don’t see how this expansion will help you, but go wild. The problem remains that you can’t separate the logic of the view from the schema it’s acting upon
dmux 4y ago
>Select an aggregate min, max over a period of time for a given category along with the other columns linked to min/max. The min/max aggregations over a period of time per category is pretty straight forward (aggregating rows), but what do you mean by "along with the other columns linked to min/max"? How would other columns come into play after aggregating rows?
emidln 4y ago
Perhaps you're looking for a way of arranging SQL as an AST represented by data structures (or objects) that can be fed to a compiler. HoneySQL[0] is one such implementation of this idea and it makes your general transformation trivial for Clojure programs. You don't need to mess around with string concatenation because you have a predictable and extensible compiler for data structures (which are themselves easily composable/transformable/storable with Clojure) that you can trust to do the right thing. If you're using some weird database or need an esoteric syntax, extending the compiler to your clause is easy to do[1]. [0] https://github.com/seancorfield/honeysql https://github.com/seancorfield/honeysql [1] https://github.com/seancorfield/honeysql#extensibility https://github.com/seancorfield/honeysql#extensibility
- fny 4y ago
  I've started to toy with this already and using a tool like sql parse to abstract existing queries. Like say you have a query that applies to one table and potentially others where the column names are slightly different. Why cant you just port that query?