Bad idea.

Post #154,679 by mmoffitt 5/11/04 5:53:04 PM Reply	Bad idea. Knee-jerk, sorry. Any design that allows access to base tables is to be dismissed out of hand, imo. I realize you said "updateable views" but that too is not a good idea imo. My general rule has always been Views are for viewing/selecting, stored procedures are for writing (insert/update/delete). Yeah, you can write update/delete/insert triggers all over the place to sort of replace the functionality/security of stored procedures, but I maintain that my rule is best (of course, that goes without saying ;0) for the simple reason that if they only way a user can insert a row into TableA is by using the stored procedure ins_TableA then you know for certain how every row got into your table. If you're going to use "Bound Controls", then you better write 3 triggers for all your tables otherwise some joker will come along with MS Access, attach a table or view and whack your data for you. bcnu, Mikem If you can read this, you are not the President.
Post #154,690 by ben_tilly 5/11/04 6:37:19 PM Reply	Implementation vs effect There is no contradiction between saying that updates/inserts/etc should go through stored procedure and achieving a user interface where many form elements map directly to database fields. Incidentally, despite having seen many claims of how superior it is to have stored procedures involved, I've yet to see an argument for using stored procedures that I find to be very convincing. Particularly when you add the headaches caused during software rollout. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,733 by mmoffitt 5/11/04 11:46:05 PM Reply	Consider what goes on in most such setups. You have several textboxes on a form, each bound to a column in a table. You allow the user to edit the values in those textboxes. If the updates are "immediate" what has to happen? A separate Update query is fired each time the data in the textboxes is changed. I admit, this is the situ when MSFT tools are used, but what do you guess is the most prominent "bound control" tool publisher? Surely you'd agree that base table access is taboo, no? bcnu, Mikem If you can read this, you are not the President.
Post #154,790 by ben_tilly 5/12/04 10:01:53 AM Reply	Don't be sure about the surely First of all I'm unclear whether the discussion is about a "live bound control" or merely a UI with direct mappings between form elements and the database. The former adds load, round trip latency, and similar issues. The UI is not an unreasonable idea if the database and UI that result fits your usage pattern. But on direct access to data, your surely is wrong. I am convinced that routing all table access through layers of stored procedures adds development complexity. I'm not nearly as convinced that it adds real value. If you think that I should be convinced, then you need to provide me with better reasons to be convinced than just shock and outrage that I would not be convinced of orthodox opinion. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,794 by admin 5/12/04 10:09:33 AM Reply	Re: Don't be sure about the surely As I mentioned elsewhere, we have a good million lines of stored procedures here. There is a mix of direct DML in the code, and DML wrapped with stored procedures. Without exception the wrapped DML is easier to maintain. I recently had to recode a large subsystem that was rife with direct DML calls that was quickly becoming a maintenance nightmare, especially since it was competing with other portions of the code using direct DML on the same tables. Any change to the tables required changes to several different pieces of code. Now, I'm not saying that things have to be wrapped in stored procedures, but they should be wrapped in some fashion. There should be only one access point to the underlying data, whether that's a stored procedure or some wrapper class in the external code. Using stored procedures, however, ensures that multiple external programs have a consistent interface. This is a big deal here, where we have PL/SQL, C++, Perl, and Java all accessing the same tables. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #154,799 by ben_tilly 5/12/04 10:29:19 AM Reply	That I agree with Yes, you need a wrapper. And if you have multiple languages, then the only clean place to wrap it is in the database. However here we only have one development language - Perl. It is unclear to me that there are significant differences for us between choosing to wrap in the database or the application. And there is no question that developers here would find it easier to have that wrapper be in our usual development language. We actually do wrap in the database. I just haven't seen any payoff from doing it there rather than in the application. But it does mean that adding a new field to a table involves editing a lot more files than it would otherwise... Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,801 by admin 5/12/04 10:32:32 AM Reply	Why more? How does putting the wrapper in the database cause more edits than a wrapper out of the database? Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #154,804 by ben_tilly 5/12/04 10:43:55 AM Reply	Duplication of information Suppose that I want to add a field. Someone else adds it to the table. I have to then add it to the view, insert procedure, update procedure, and possibly a null procedure. I further add it to the object-relational mapping layer, and then to the unit test. That's 5-6 files. If the object-relational mapping layer could also do the job that the stored procedure does. That would cut out 2-3 edits. Furthermore those are the biggest edits. If I was reworking the system, I think that you could still have stored procedures, and have them be autogenerated from code. However the system already exists, and at present it is easier not to have to rebuild it. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,810 by admin 5/12/04 11:06:48 AM Reply	Must depend on what you're doing. If you're always updating the entire row, that's different than DML that does specific things (which is what our system does, for the most part). For situations where we do make changes wholesale, we are moving to a PL/SQL object that handles the inserts/updates/etc., which is autogenerated from the table DDL. We also create a Java proxy for this object using the same script. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #154,798 by mmoffitt 5/12/04 10:27:12 AM Reply	I can't add much to what Scott said. It may be that I favor stored procedures because of familiarity, but I would hasten to add that if you don't use them as your single point of entry, then you may be susceptible to a developer/user using a non-standard tool to go around your single point of entry. bcnu, Mikem If you can read this, you are not the President.
Post #154,802 by ben_tilly 5/12/04 10:34:04 AM Reply	Then see my response to Scott However I find your argument for using stored procedures to be a non sequitor. If you're using stored procedures as your single point of entry, then you are still vulnerable to developers using a non-standard tool to get around them. If you want guaranteed safety that can't be worked around, then you need to use triggers. However once you get into the game of trying to protect yourself from your developers, you have a lot of other problems that follow directly. I'm not saying that in some environments you don't have to take that step. (In fact the larger the group, the more likely it is that it is a good choice to not trust freely.) But it adds a lot of immediate overhead and complexity when you do. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,803 by mmoffitt 5/12/04 10:38:38 AM Reply	I did. And how? If you're using stored procedures as your single point of entry, then you are still vulnerable to developers using a non-standard tool to get around them. Only if the crack the db admin's password. If no one has access to the base data and no one has anything but select permissions on views, excluding a crack in the database engine/password for the admin/etc. how am I vulnerable to a developer "getting around" the stored procedures to manipulate the data directly? In short, if you can only manipulate data via the stored procedures you have execute only permissions on, how are you going to get around that? bcnu, Mikem If you can read this, you are not the President.
Post #154,812 by ben_tilly 5/12/04 11:22:16 AM Reply	Ah, good point However how much that accomplishes depends on how much logic you have in the database layer, and who maintains it. At my job, developers are responsible both for application code and stored procedures. Furthermore the stored procedures are responsible for rather little. Keeping update_dm accurate. Making sure that basic required fields are filled. The business logic, authorization logic, and most data validation is kept in the application. Locking down the stored procedures buys you rather little. Reversing this would require having a lot of development work move into PL/SQL. It also requires having a lot of development work move from developers to DBAs. And it requires a lot of tight integration during the development cycle between different people. All moves that are going to significantly hinder productivity. And in the end what it means is that you still have to trust someone, you're just choosing to trust a different set of people. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,842 by drewk 5/12/04 1:10:54 PM Reply	Problem is rollback With source code you can easily roll back to a previous revision. Once you break data, you're hosed. It's changing constantly, so you don't want to go back to last night's version. That's why the goal (in this context) of stored precedures is explicitly to make it harder for anyone to change how the application interacts with the DB. Worst case, a delete statement without a where. So yes, you are just trusting a different group of developers. But hopefully the DBAs are more concerned with, and more trained in, maintaining the data than with adding features. === Implicitly condoning stupidity since 2001.
Post #154,848 by ben_tilly 5/12/04 1:44:31 PM Reply	That's only an argument for a wrapper As soon as you have a wrapper that all access goes through, which prevents you from accidentally dealing with all of the data at once, then using that wrapper solves the "delete world" issue. As for DBAs being better equipped to deal with data than development, I agree. Which is why I question making DBAs even more critical path on interactive development than they need to be. Pushing responsibility for more and more features and complex requirements makes them do a job they aren't supposed to be doing, and puts up roadblocks for developers. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,774 by admin 5/12/04 9:11:56 AM Reply	Er, what headaches? We have a million lines of stored procedures, deployed nightly into 5 different environments. What headaches are you referring to? Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #154,796 by ben_tilly 5/12/04 10:22:56 AM Reply	Depends how you're set up Where I've worked, code is stored in CVS, and then rollouts happen by specific releases getting copied to production. (At my current job, followed by restarting Apache.) Rollbacks can happen by copying the old release out. (At my current job, machines are taken out of the load balancer, restarted, put back into the pool and then others are taken out.) This can happen while the system is live and under use. In fact sometimes you want it to happen under moderate use so that you can monitor for potential issues right away in the error logs. (A developer's unit tests tend to reflect the same assumptions that go into that developer's code. Real users may use the system in ways that violate those assumptions...) This is a very simple way to work, and it seems to work well. But synchronizing that with database state is harder, it is nontrivial just to verify that all views and stored procedures in databases match what is in CVS. And if you have multiple developers connecting to the same database, you can't have each one with their own versions of things. The most reasonable solution for a small group seems to be to ensure that view and stored procedure changes are always forwards compatible - the new database stuff works with old code. This lets database changes be rolled out ahead of time. Which is doable, just a bit of a PITA. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #154,805 by admin 5/12/04 10:44:03 AM Reply	Re: Depends how you're set up I don't see how this is any more of a headache than making sure that table changes are all synchronized with the external code. If you change a table, and then change the wrapping stored procedure, this has to be matched with the external code, natch. If you change a table, and the wrapping is in the external code, you still have to match the changes. No real difference there that I can see. We do things a bit differently here: 4 environments, 3 of which (test, qa, prod) can be seen by clients. Every night dev, test and qa are rebuilt out of the source code control system using an automated SQL loading tool that ensures that the database is consistent. Since clients are in qa all day long, we catch unintended aspects of the system like you mentioned. Production roll-outs can therefore take the whole system down at once, replace everything, then come back up without inconsistencies. We don't have an absolute requirement for 24x7 operation; I assume we would have a mirror system to update, then flip a switch when we needed to convert, if that were the case. And if you have multiple developers connecting to the same database, you can't have each one with their own versions of things. Seems to me this would be an argument for putting the wrappers in stored procedures. :-) Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #154,820 by ben_tilly 5/12/04 11:39:48 AM Reply	More moving parts I don't see how this is any more of a headache than making sure that table changes are all synchronized with the external code. If you change a table, and then change the wrapping stored procedure, this has to be matched with the external code, natch. If you change a table, and the wrapping is in the external code, you still have to match the changes. No real difference there that I can see. Option 1: Change the table. Then the view. Then rollout code. Option 2: Change the table. Then the view. Then each stored procedure. Then rollout code. Both options add complexity. But the second one adds somewhat more because you have more pieces that got changed. We do things a bit differently here: 4 environments, 3 of which (test, qa, prod) can be seen by clients. Every night dev, test and qa are rebuilt out of the source code control system using an automated SQL loading tool that ensures that the database is consistent. Since clients are in qa all day long, we catch unintended aspects of the system like you mentioned. Production roll-outs can therefore take the whole system down at once, replace everything, then come back up without inconsistencies. We don't have an absolute requirement for 24x7 operation; I assume we would have a mirror system to update, then flip a switch when we needed to convert, if that were the case. Our requirement for 24x7 is somewhat soft. We sometimes take down the system for an hour or two for database maintainance tasks that are easier done offline. However we don't like scheduling any more outages than we need, since the system is always doing something. During the day (we have long days since people are in different timezones accessing us) we have people on the website. At night the website is quieter, but we send emails out. As for simulating usage, given what we do, it is hard for us to come up with a realistic mix in QA that matches the variety of random cases that thousands of people using the site provide. And if you have multiple developers connecting to the same database, you can't have each one with their own versions of things. Seems to me this would be an argument for putting the wrappers in stored procedures. :-) We're talking past each other. What I'm saying is that stored procedures prevent people from keeping their private development completely private. Which is a strike against stored procedures. I think that you're saying that if you can't keep development environments private, then that is an argument for pushing people to not even maintain the illusion of trying to do so. Which logic, taken to its logical conclusion, suggests that all developers should be working on a shared server, editing a shared source-code tree. Which is an obviously absurd conclusion that I hope you don't advocate. It may well be that what I think you're saying is not what you meant. In which case I'd appreciate clarification. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]

Welcome to IWETHEY!