It's almost as crazy as the Excel spreadsheet I found to simulate a neural network.
I've always loved seeing how far you could push SQL and databases to do things that (on the surface) would seem extremely difficult or impossible.
What it usually turns out to be is possible - but in the long-term unmaintainable. This FSM is not that - not yet (if it gained even more states, it could get there). But I have seen (and I have unfortunately written) queries in SQL that could make your hair stand up. Insane extreme monstrosities that I am both proud and ashamed of (fortunately, I don't work for that company any longer).
On a different but related note, I do recall one query that a friend of mine wrote to allow the querying of a zip code database (which had lat/lon columns for each zip code), to be able to calculate distances from a given address - using SQL. It was a simple form of geo lookup he had to do for a particular project. I later used the same code as a part of a lookup process for placing markers on a google map (markers would indicate whatever was needed for "show me locations near my address within N miles"). It was a pretty interesting piece of SQL for the reason of doing the specialized distance calculation (can't remember which calc, but one of the simplest ones that didn't take into account certain things about the earth's "roundness" to make things super-accurate - that wasn't needed for short distances and purpose).
Hah, I hear you :). Pushing this much logic into the DB can feel weird at times. And the application this is part of definitely has a lot of very complicated and large SQL queries.
That being said, I think things can be somewhat tamed by applying the same best practices to your SQL as you apply to your regular code: I.e. lots of tests, good comments and documentation, extracting logic into either set returning functions or functions suitable for lateral joins (both can be inlined if done right [1]), keeping things in version control, etc.
But yes, applying those practices can be harder in SQL than it is in your application layer language for various reasons. So I'll always recommend avoiding going too crazy. You can often get the best of both worlds by making pragmatic choices about what should be done at which layer. No need to enslave yourself to a false dichotomy.
It's almost as crazy as the Excel spreadsheet I found to simulate a neural network.
I've always loved seeing how far you could push SQL and databases to do things that (on the surface) would seem extremely difficult or impossible.
What it usually turns out to be is possible - but in the long-term unmaintainable. This FSM is not that - not yet (if it gained even more states, it could get there). But I have seen (and I have unfortunately written) queries in SQL that could make your hair stand up. Insane extreme monstrosities that I am both proud and ashamed of (fortunately, I don't work for that company any longer).
On a different but related note, I do recall one query that a friend of mine wrote to allow the querying of a zip code database (which had lat/lon columns for each zip code), to be able to calculate distances from a given address - using SQL. It was a simple form of geo lookup he had to do for a particular project. I later used the same code as a part of a lookup process for placing markers on a google map (markers would indicate whatever was needed for "show me locations near my address within N miles"). It was a pretty interesting piece of SQL for the reason of doing the specialized distance calculation (can't remember which calc, but one of the simplest ones that didn't take into account certain things about the earth's "roundness" to make things super-accurate - that wasn't needed for short distances and purpose).