7 ms·

Please, please, never do this. Programming like this in SQL is a maintainability nightmare. Can you even imagine how difficult it would be to modify this to ad

by RyanZAG 13y ago

Please, please, never do this. Programming like this in SQL is a maintainability nightmare. Can you even imagine how difficult it would be to modify this to add in new features? I've worked on projects where people have done 'clever' tricks like this, and it always ends in terrible awful terrible. The SQL itself is also non-portable which makes it even worse. Follow the advice of Justin in the comments there and rather denormalize - which basically means caching the results of deep tree queries at the top level where they can be easily and simply understood. EDIT: If you don't like the idea of denormalizing, another simple alternative is to create a separate lookup table/model which you can use to easily map back the items to the correct parent.

bifel 13y ago
Common Table expressions are part of the SQL:1999 standard. Most RDBMS can handle them.
tta 13y ago
Absolutely. We aren't using this code in production. It's sitting on a branch. I just thought recursive queries were really cool...and I had an example to illustrate it with. So hey, why not?
lucian1900 13y ago
There's a better way: create a view backed by the recursive query. This way if they ever wish to move away from Postgres (although why would they?), they can denormalise manually.
aidos 13y ago
Yep. For a portable solution there are a number of way of storing the hierarchy so that it's efficient to select a specific subtree. One way I've done it before is to have a field that contains the path (eg node1/sub_n/sub_n2 etc) and another field containing the ordering (in my case I did it across the whole tree). You can then use simple single selects to get ancestors / descendants / subtrees / siblings / all sorts of things you need (eg where path like '/path/to/subtree/%s'). Obviously not totally efficient but easy to work with and fine for small trees. The trade-off is that the operations to manipulate the tree are more fiddly - they make for a great little brain teaser though :)
- dragonwriter 13y ago
  Recursive CTEs are a portable solution; they are part of the SQL:1999 standard and are supported by current versions of Postgres, DB2, Oracle, SQL Server, Firebird, etc. The only two widely used SQL-using relational databases I can think of off the top of my head that don't support them are SQLite and MySQL. (PostgreSQL arrays aren't portable, though in other DBs I've seen strings used for similar purposes, though that is truly an abomination.)
nadaviv 13y ago
I make heavy use of SQL in my daily work. A lot of our business logic lives in the database as views, triggers and various functions. I actually find that taking this logic away from the app code and into the database is one of the best architectural decisions we took. I think the primary issue some people have with doing that is that they don't know working with databases well enough, and consider them a dumb place to store data, and nothing more. This is what makes it difficult to maintain. One thing that I do agree with is that the SQL code isn't portable - but its the same as your Ruby/PHP/NodeJs/whatever code not being portable. I don't see that as an issue.
- umsm 13y ago
  In my experience: most people that don't know proper DB design and SQL will do two things: - Over-utilize stored procedures, views, etc. - Implement many obscure "hacks" There is nothing wrong with using built-in database functionality, but it can be over done.
- btilly 13y ago
  From a software engineering perspective, this is entirely backwards. Databases offer a great way of looking at your data. However they have very poor stories around version control, rollout, rollback, separating metadata from data, and creating multiple parallel environments for production, qa, and every dev engineer. Yes, I know that database vendors claim otherwise, but compared to what is standard for most programming environments these days, it is a major limitation. But it doesn't stop there. If you are lucky enough to get a busy site, you will find that the database is the inevitable bottleneck that can't simply be scaled by putting machines in parallel. The more work you push down to that, the sooner you'll hit that bottleneck, and the harder it is to fix once you do. When you've experienced, as I have, the joys of periodic outages because the database choked at a million dynamic pages/day, you'll value moving logic out of the database, the ability to put caching in front of it, etc. Now to all of this the database vendors have a trump card, what happens if multiple applications access the database? If your business logic is there, then you're fine, otherwise you're reimplementing that logic multiple times! The thing is that in the 15 years that I've seen people making that argument, I've not encountered a lot of organizations opening up a business critical transactional database for random applications. And in practice it is easier and saner to put an RPC layer on top of of your existing app, in which case you get the business logic that way. I guess I should somewhere note that I spent several years of my life with my primary job being to write fancy SQL. I was also the go to person on the software development side for "get us to scale the database better". I do not criticize databases out of a lack of knowledge of how to use them. I criticize them based on a large amount of experience with how to make the most effective use of their capabilities.
jeffdavis 13y ago
"Programming like this in SQL is a maintainability nightmare." How does it compare to the application code required to do the same thing?
nbouscal 13y ago
I agree with you that in this particular instance, this is probably not the optimal solution to the problem at hand. I would argue, however, that you are significantly overreaching by implying that one should never take advantage of advanced features of your RDBMS. While the OP's problem was rather easy to solve in the application layer, there are many problems where that approach is absolutely unreasonable. As an example, I'm currently working on a project that involves modeling a directed cyclic graph in the database. Trying to pull the logic out into the application layer is simply unfeasible. There is no performant way to do it, period. However, it's easy enough to work with it in the database, either by using a function that performs a recursive query, making a materialized view out of that same query, or using triggers to maintain the transitive closure in a table proper. You also seem to have a pretty narrow perspective of the situations in which RDBMSs are used. As it happens, there are a lot more use cases than 'persistence layer for web apps', and in many of those use cases the idea of not using advanced features of your RDBMS would be absolutely ludicrous. I do agree with one complaint made in another reply to your post, which is that version control, testing, etc. are much more difficult when your logic is residing in the database. There are a lot of solutions out there for that, though (migrations alone solve many of the headaches), so it's really not a knockdown argument. As for portability, the SQL itself is as portable as any SQL is. WITH RECURSIVE was added to the standard in '99.
masklinn 13y ago
> The SQL itself is also non-portable ? CTE is part of SQL:1999 and most RDBMS support it: http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression http://en.wikipedia.org/wiki/Hierarchical_and_recursive_quer... Or are you using some odd custom definition of the word "portable"?
- dragonwriter 13y ago
  CTEs are portable, but the sorting piece of the solution relies on PostgreSQL's arrays, which are not portable. Even with CTEs, I think doing sort-by-path in a portable way is going to be ugly (arrays make it very nice.)

by RyanZAG 13y ago

bifel 13y ago

Common Table expressions are part of the SQL:1999 standard. Most RDBMS can handle them.

tta 13y ago

Absolutely. We aren't using this code in production. It's sitting on a branch. I just thought recursive queries were really cool...and I had an example to illustrate it with. So hey, why not?

lucian1900 13y ago

There's a better way: create a view backed by the recursive query. This way if they ever wish to move away from Postgres (although why would they?), they can denormalise manually.

aidos 13y ago

Yep. For a portable solution there are a number of way of storing the hierarchy so that it's efficient to select a specific subtree. One way I've done it before is to have a field that contains the path (eg node1/sub_n/sub_n2 etc) and another field containing the ordering (in my case I did it across the whole tree). You can then use simple single selects to get ancestors / descendants / subtrees / siblings / all sorts of things you need (eg where path like '/path/to/subtree/%s'). Obviously not totally efficient but easy to work with and fine for small trees. The trade-off is that the operations to manipulate the tree are more fiddly - they make for a great little brain teaser though :)

dragonwriter 13y ago
Recursive CTEs are a portable solution; they are part of the SQL:1999 standard and are supported by current versions of Postgres, DB2, Oracle, SQL Server, Firebird, etc. The only two widely used SQL-using relational databases I can think of off the top of my head that don't support them are SQLite and MySQL. (PostgreSQL arrays aren't portable, though in other DBs I've seen strings used for similar purposes, though that is truly an abomination.)

nadaviv 13y ago

I make heavy use of SQL in my daily work. A lot of our business logic lives in the database as views, triggers and various functions. I actually find that taking this logic away from the app code and into the database is one of the best architectural decisions we took. I think the primary issue some people have with doing that is that they don't know working with databases well enough, and consider them a dumb place to store data, and nothing more. This is what makes it difficult to maintain. One thing that I do agree with is that the SQL code isn't portable - but its the same as your Ruby/PHP/NodeJs/whatever code not being portable. I don't see that as an issue.

umsm 13y ago
In my experience: most people that don't know proper DB design and SQL will do two things: - Over-utilize stored procedures, views, etc. - Implement many obscure "hacks" There is nothing wrong with using built-in database functionality, but it can be over done.
btilly 13y ago
From a software engineering perspective, this is entirely backwards. Databases offer a great way of looking at your data. However they have very poor stories around version control, rollout, rollback, separating metadata from data, and creating multiple parallel environments for production, qa, and every dev engineer. Yes, I know that database vendors claim otherwise, but compared to what is standard for most programming environments these days, it is a major limitation. But it doesn't stop there. If you are lucky enough to get a busy site, you will find that the database is the inevitable bottleneck that can't simply be scaled by putting machines in parallel. The more work you push down to that, the sooner you'll hit that bottleneck, and the harder it is to fix once you do. When you've experienced, as I have, the joys of periodic outages because the database choked at a million dynamic pages/day, you'll value moving logic out of the database, the ability to put caching in front of it, etc. Now to all of this the database vendors have a trump card, what happens if multiple applications access the database? If your business logic is there, then you're fine, otherwise you're reimplementing that logic multiple times! The thing is that in the 15 years that I've seen people making that argument, I've not encountered a lot of organizations opening up a business critical transactional database for random applications. And in practice it is easier and saner to put an RPC layer on top of of your existing app, in which case you get the business logic that way. I guess I should somewhere note that I spent several years of my life with my primary job being to write fancy SQL. I was also the go to person on the software development side for "get us to scale the database better". I do not criticize databases out of a lack of knowledge of how to use them. I criticize them based on a large amount of experience with how to make the most effective use of their capabilities.

jeffdavis 13y ago

"Programming like this in SQL is a maintainability nightmare." How does it compare to the application code required to do the same thing?

nbouscal 13y ago

I agree with you that in this particular instance, this is probably not the optimal solution to the problem at hand. I would argue, however, that you are significantly overreaching by implying that one should never take advantage of advanced features of your RDBMS. While the OP's problem was rather easy to solve in the application layer, there are many problems where that approach is absolutely unreasonable. As an example, I'm currently working on a project that involves modeling a directed cyclic graph in the database. Trying to pull the logic out into the application layer is simply unfeasible. There is no performant way to do it, period. However, it's easy enough to work with it in the database, either by using a function that performs a recursive query, making a materialized view out of that same query, or using triggers to maintain the transitive closure in a table proper. You also seem to have a pretty narrow perspective of the situations in which RDBMSs are used. As it happens, there are a lot more use cases than 'persistence layer for web apps', and in many of those use cases the idea of not using advanced features of your RDBMS would be absolutely ludicrous. I do agree with one complaint made in another reply to your post, which is that version control, testing, etc. are much more difficult when your logic is residing in the database. There are a lot of solutions out there for that, though (migrations alone solve many of the headaches), so it's really not a knockdown argument. As for portability, the SQL itself is as portable as any SQL is. WITH RECURSIVE was added to the standard in '99.

masklinn 13y ago

> The SQL itself is also non-portable ? CTE is part of SQL:1999 and most RDBMS support it: http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression http://en.wikipedia.org/wiki/Hierarchical_and_recursive_quer... Or are you using some odd custom definition of the word "portable"?

dragonwriter 13y ago
CTEs are portable, but the sorting piece of the solution relies on PostgreSQL's arrays, which are not portable. Even with CTEs, I think doing sort-by-path in a portable way is going to be ugly (arrays make it very nice.)