Why are database features being ignored, and instead reinvented in the middle tier?
database
Solution
Because stored procedures:
- add another development language, increasing complexity and potentially redundant code (logic written in both languages);
- generally have worse tooling, monitoring and debugging capabilities than PHP, C#, Java, Python, etc;
- are generally less capable than most middle tier languages;
- only have an advantage with high volume data transformation (where you avoid the server roundtrip), which tends to only be a minimum of actual usage.
That being said, it's a common methodology on C# ASP.NET applications.
As Jeff Atwood put it, stored procedures are the assembly language of databases and people don't tend to code in assembly language unless they need to.
I've frequently used materialized views and sometimes used CONNECT BY in Oracle, neither of which I believe exist in MySQL.
I don't tend to use XML/XSLT in the database because, well, that means I'm using XML and XSLT.
As for geographical or spatial data structures, the reason there is probably that they're hard to just "pick up". It's a fairly specialist area. I've read the MySQL manual on spatial data structures and I'm sure it makes sense to someone with extensive GIS experience but to me and my limited needs (which tend to be around marking the latitude/longitude of a point) it just doesn't seem worth the time investment to figure it out.
Another issue is that if you go beyond ANSI SQL (much) then you've just tied yourself somewhat to a particular database vendor and possibly to a specific version. For that reason you'll often find application developers will tend to treat their databases at the lowest common denominator, which means treating them as a dumping ground for relational data.
Problem
What are the top reasons (apart from "database independence") that most IT projects today seem to ignore the wealth of features that exist in modern database engines such as Oracle 11g and SQL Server 2008? Or, to borrow from the Helsinki Declaration blog which puts it this way: In the past twenty years we observe that the functionality (features) that is available to us inside the DBMS, has exponentially grown. These features enabled us to build database applications. Which is what we all started doing in the booming nineties. But then at the dawn of the new millennium, something happened. And that something mysteriously made the role of the DBMS inside a database application project diminish to insignificant. (...) As of the new millennium we are pushing all application logic out of the DBMS into middle tier servers. The functionality of stuff implemented outside the DBMS has exploded, and the feature rich DBMS is hardly used for anything but row-storage. We are talking about stuff like - Stored procedures used as data APIs (for security and to avoid excessive network traffic) - Materialized views - Instead-Of triggers - Hierarchical queries (connect by) - Geography (spatial data types) - Analytics (lead, lag, rollup, cube, etc.) - Virtual Private Database (VPD) - Database-level Auditing - Flashback queries - XML generation and XSL transformation in database - HTTP callouts from database - Background job scheduler Why are these features not being used? Why are most Java, .NET and PHP developers sticking with the "SELECT * FROM mytable" approach?