Ramblings from the Creator of WilsonDotNet.com
Nie article and amen to a debate that will unfortunately never die.
Apparently many in the Stored Proc only crowd have never coded in the Java community where O/RM is much more prevalent.
Without religious wars, some people will have nothing to talk about. Well said!
Does multiple db login mean that the application on end-user's computer has a direct connection to the database? I am guessing that if the same company has gotten blasted (or I should "blaster" -ed) by virus attacks if they are using MS SQL Server (even other DB platforms are not immune to worms and other attacks).
David: That depends on the implementation. A traditional client/server app would have each client computer having a direct connection to the database, but a web app or other distributed enterprise app would have all the connections coming from a server computer.
The one thing I will say for stored procedures is that they can be used to better define a contract for the data source which isn't always a bad thing.
That said there is no reason why the two should be mutually exclusive .. only use a hammer when you have nails :)
I have been using your mapper for 18 months now in both personal and professional projects, without stored procedures (not because I am necessarily anti-sproc, but because it is just easier and I have not needed them generally) and have 'converted' a lot of my peers to using it as well. None of them complain about not being burdened with writing TSQL and ADO.NET code (except where it makes sense) - I am continuing to win people over because, as you point out, their "apps are just as secure and performant as they were before O/RM, but far easier to build/maintain." I just figure those that want to let their fears get the best of them are just going to make it to market that much later than I will, with a product that isn't necessarily any better. I can't believe people don't do the very little research necessary to discover the real truth about things, but would rather remain in the dark; to each his own!
Is it because stored procs can offer an 'interface' to a complex normalized database ?
I agree with this statement:
<i>Because of that fact, most all web apps, as well as those enterprise apps that have a distributed design, make the decision to have just a single db login that the application uses for all users and db interaction. This approach is used almost universally</i>
The place I work at seems to be stuck in the past and doesn't like the idea of a trusted subsystem model. Do you or anyone have any documentation on who out in the world is following this model?
Very good argument. We are using the O/RMapper at our client within clustred environment in java world and sometimes we feel this layer is somewhat slower than direct connection to database. Even with this approach we are using single db login approach and ACLs.
This discussion is another one of those holy wars with staunch supporters on both sides of the fence fiercely defending their views until the bitter end.
But the reality is that O/R mappers are just a tool like all of the other tools we use in order to get the job done.
Each tool has inherent advantages and disadvantages and no single tool has been invented that gets the job done in every situation.
As developers we are guilty of having a limited view of the architecture and design of systems. What is perceived as “best” to us would not always be agreed upon by the corporate DBA, Systems Architect, or CIO.
How well does the tool (O/R Mapper) work in a corporate environment with DBA’s in total control of the database?
How well does the tool work in LARGE applications that have several hundred programmers designing them? How well does it coordinate change or even versioning with that many programmers?
How do you guarantee security with the tool? How do you prevent the evil programmer (or DBA) from simply inserting or modifying database data and paying himself a million dollar salary? Performance is secondary and worrying about the actual performance difference between dynamic SQL and stored procedures is a moot point if you can’t prove the application is secure.
Removing all direct access to tables and limiting users to accessing stored procedures simplifies things to a point that your manager, DBA, or company owner can understand. Yes, the SetSalary() procedure is only callable by the Human Resource Manager.
I know you can set table and column permissions on a granular level but you have just increased the complexity of the system tremendously and reduced buy-in from your security and RDBMS departments. Security Auditing with this tool becomes a non trivial task.
Single login versus multiple login is just an authentication issue. There are many ways to authenticate the user. What percentage of corporate intranet applications use Windows authentication where every user is uniquely identified ( or certificates, or card readers, or biometric scanners)? Not everyone is building and using Internet apps with basic authentication on a single login.
Does the RDBMS in the application have to run standalone, or does it have multiple applications accessing it?
I suspect that the question of whether or not to have stored procedures is really “where do you want your business logic”. And that is a topic worthy of its own discussion.
Note that I am not bashing the tool. In environments where the programmer is totally in control (and probably serving all the other roles I mentioned) they can use such a tool without all the political and corporate hoops that many of us have to jump through to have such a privilege.