4 ms·

You can achieve function-like behavior for vertical or horizontal slicing of tables by using views - I use them all the time, and mine are updateable. Some vend

by DenisM 17y ago

You can achieve function-like behavior for vertical or horizontal slicing of tables by using views - I use them all the time, and mine are updateable. Some vendors allow parametrized views, e.g. Table Valued Functions in MS SQL (sadly those aren't updateable I think?) and Common Table Subexpressions. Between these three spaghetti SQL doesn't have to exist. I spent a lot of time writing SQL and the ordering of SELECT ... FROM doesn't bother me at all. Frankly, I find this argument silly and I do not want to entertain it - when buying offroad SUV I will only compare the color of door handles when all else is equal, which it isn't. The only serious part of it is intellisense problems which admittedly could use some work (this is why LINQ is the other way around - table name is always spelled first). The other two points are entirely valid. The integration is a real pain. The opaqueness can be mitigated by vendors as well - MSSSQL allows hints to force desired behavior - but that doesn't so much drop down in abstraction level as it bastardizes the existing one.

weavejester 17y ago
It's not a matter of aesthetics; it's a matter of power. If each table transformation is a function, the language becomes considerably more flexible. Consider the following syntax: users where(u => u.login = "foo" and u.passwd = SHA1("secret" + u.salt)) Since "where" is a function, we can isolate just this transformation: authenticate(login, password) := where(u => u.login = login and u.passwd = SHA1(password + u.salt)) Now "authenticate" is a table transformation as well: users authenticate("foo", "secret") This is a trivial example, but this could just as easily apply to more complex queries. One could construct functions to manage "nested set" tables, or create custom aggregation functions. Everything that is currently difficult or impossible under SQL. Because SQL is so limited in what you can abstract, there's very little room to factor out common code. Views, stored procedures and even T-SQL functions just operator on set tables. With a more flexible syntax, one could have third-party libraries containing more complex table transformations. Personally, I wonder whether we need a separate language for database queries at all. It would be nice if there were a just a library we could use to create our own database server inline, using whatever language we wish.
- DenisM 17y ago
  I see what you mean, and yes it would be nice. In essence what you advocate is a table type and a number of tables conforming to that type could be then passed into a function which operates over the parameter given what it knows about it by the type description. It's a very appealing idea and many times I wished it was possible. My guess as to why it isn't possible is that in the database world there is an assumption that all records of the same type lie in one table, i.e. the table is the type. And if you look at it form theoretical relational theory point of view, that is so. Alas in practice we do have things like third-party components and we have to worry about lifetime (e.g. transient table-typed variables). To be fair Oracle I think has something called "package" which allows writing reusable code. Don't know much about it, but supposedly that's what it does. Also, MSSQL has notion of Table Type which can be used to declare variables. I actually implemented (a part of) it while I worked there. :)
mistermann 17y ago
How about the syntactical difference between "insert into <x> ..." and "update <x> set...". I've always wondered why no vendor has an "insertorupdate" function. This issue, from an application development perspective, is another reason I love ORM's. The idea that, in the year 2010, I have to write at least two stored proc's for each table in my database, just for persisting data, aggravates me.
- DenisM 17y ago
  But they do! MERGE works in both SQL Server and Oracle. See, this goes back to my point - people aren't learning their tools and then complain about perceived lack of functionality. I suggest hitting stackoverflow with this type of questions in the future. That's what I always do when I wonder "can I do X with Y?".

by DenisM 17y ago

weavejester 17y ago

It's not a matter of aesthetics; it's a matter of power. If each table transformation is a function, the language becomes considerably more flexible. Consider the following syntax: users where(u => u.login = "foo" and u.passwd = SHA1("secret" + u.salt)) Since "where" is a function, we can isolate just this transformation: authenticate(login, password) := where(u => u.login = login and u.passwd = SHA1(password + u.salt)) Now "authenticate" is a table transformation as well: users authenticate("foo", "secret") This is a trivial example, but this could just as easily apply to more complex queries. One could construct functions to manage "nested set" tables, or create custom aggregation functions. Everything that is currently difficult or impossible under SQL. Because SQL is so limited in what you can abstract, there's very little room to factor out common code. Views, stored procedures and even T-SQL functions just operator on set tables. With a more flexible syntax, one could have third-party libraries containing more complex table transformations. Personally, I wonder whether we need a separate language for database queries at all. It would be nice if there were a just a library we could use to create our own database server inline, using whatever language we wish.

DenisM 17y ago
I see what you mean, and yes it would be nice. In essence what you advocate is a table type and a number of tables conforming to that type could be then passed into a function which operates over the parameter given what it knows about it by the type description. It's a very appealing idea and many times I wished it was possible. My guess as to why it isn't possible is that in the database world there is an assumption that all records of the same type lie in one table, i.e. the table is the type. And if you look at it form theoretical relational theory point of view, that is so. Alas in practice we do have things like third-party components and we have to worry about lifetime (e.g. transient table-typed variables). To be fair Oracle I think has something called "package" which allows writing reusable code. Don't know much about it, but supposedly that's what it does. Also, MSSQL has notion of Table Type which can be used to declare variables. I actually implemented (a part of) it while I worked there. :)

mistermann 17y ago

How about the syntactical difference between "insert into <x> ..." and "update <x> set...". I've always wondered why no vendor has an "insertorupdate" function. This issue, from an application development perspective, is another reason I love ORM's. The idea that, in the year 2010, I have to write at least two stored proc's for each table in my database, just for persisting data, aggravates me.

DenisM 17y ago
But they do! MERGE works in both SQL Server and Oracle. See, this goes back to my point - people aren't learning their tools and then complain about perceived lack of functionality. I suggest hitting stackoverflow with this type of questions in the future. That's what I always do when I wonder "can I do X with Y?".