Page 1 of 3
Business Logic & Stored Procedures
Posted: Tue Feb 10, 2009 12:09 pm
by VladSun
josh wrote:Preferably. Each entity in your system should be an object, and all relevent knowledge about that object should be expressed directly in code in 1 place, its not about code quality as much as its about semantic unambiguity. You don't want to keep modifying data in different layers, its better to express that as domain knowledge directly on the object so it can be re-used in other parts of the system more easily.
An object may fetch it's data from several tables. Also, an update of an object may update several tables and there may be some DB constraints. I think that using stored procedures can encapsulate such "DB-side logic" out of the business logic. I don't think an object should care how it is stored in the DB.
Also, the usage of stored procedures increases the security.
EDIT:
Well, I think this topic is too big to be put in this thread

Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 1:07 am
by josh
VladSun wrote:Also, the usage of stored procedures increases the security.
Stored procedures are useful, not sure how it has _anything_ to do with security. In domain driven design stored procedures are bad practice. Here's an article I found interesting
http://codebetter.com/blogs/jeremy.mill ... 30093.aspx
from the article
Security is another advantage that is often cited as an advantage of using stored procedures. On one hand, stored procedures quickly mitigate vulnerability to SQL injection attacks. On the other hand, the database can be tightened down so that no ad hoc SQL can be executed, stopping security breaches right at the start. Personally, I abhor this idea myself because it drags down the development team’s velocity. I don’t see why it’s necessary, but it is an option.
ok fine, but you should be escaping your inputs either way. Stored procedures don't make you immune and I don't really think this argument carries a lot of practicality.
This is my argument, worded better.
Stored procedures are a terrible place to create business logic. Development environments for the .Net languages or Java are far better suited for developing applications. Intellisense support, debugging, and refactoring are all inferior with stored procedure languages. T-SQL is a procedural language that pretty well forces you into the transaction script style of organizing business logic.
An OO language can do a much better job of code reuse.
Do you not agree?
Another article:
http://www.paragoncorporation.com/Artic ... ticleID=28
Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 5:03 am
by VladSun
I haven't said "Put business logic into stored procedures", right ?

As I mentioned, I use stored procedures for two main purposes:
1. Some CRUD operations
Let's have an object User and an object Agent which extends User. So, we have
- a User table where the data for all users, including agents, is put;
- a Agent table where additional data for users who are agents is put;
- (optional) a relational table which links the User and Agent table;
I don't want my Agent object to know about how it's stored, so I use a stored procedures which operate on the three (or two) tables. One may also use triggers but I think stored procedures are a better way.
2. Reports
Reports must have really optimized queries, so I think they are more appropriate to be done by the DBA and not the PHP developer. Also, (especially in the beginning of the development) reports are often subject to changes, because of changes in the user requirements. I think a good PHP report system should use Model/View which are almost independent from the data (type, number of fields, etc.) fetched by the report query. I think the best way to do this is by using stored procedures.
Stored procedure are more secure than plain SQL queries - that's for sure. And
any added security increase the security of the whole system. Don't you agree?
@Moderators - could you split the thread, please

Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 7:47 am
by josh
VladSun wrote:I haven't said "Put business logic into stored procedures", right ?

As I mentioned, I use stored procedures for two main purposes:
1. Some CRUD operations
That's business logic. What else would you consider it presentation logic?
VladSun wrote:2. Reports
For optimization I'd prefer a view, but like I was pointing out theres nothing inherently evil about these features, just the potential for abuse, so of course there will be times when using them is appropriate.
VladSun wrote:Stored procedure are more secure than plain SQL queries - that's for sure. And any added security increase the security of the whole system. Don't you agree?
I agree added security increases the security of the whole system, but the fact of the matter is stored procedures stop SQL injection, if you read my link you'll see that blogger pointed out this can lead to a false sense of security which actually decreases system wide security. I would not consider security a reason for moving to stored procedures. Any competent developer should be escaping inputs already. Stored procedures does not catch a user inputting "../.../../../../backup_script_containting_passwords.sh" into the database, which then might be included on later in application code. Security is an overarching concern, and IMO has nothing to do with using stored procedures.
Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 8:39 am
by VladSun
josh wrote:VladSun wrote:I haven't said "Put business logic into stored procedures", right ?

As I mentioned, I use stored procedures for two main purposes:
1. Some CRUD operations
That's business logic. What else would you consider it presentation logic?
No, it's not. If you change the storage (e.g. flat file or another DB engine), does your business logic change? I don't think so.
Also, try partitioning with plain SQL in PGSQL ...
josh wrote:VladSun wrote:2. Reports
For optimization I'd prefer a view, but like I was pointing out theres nothing inherently evil about these features, just the potential for abuse, so of course there will be times when using them is appropriate.
Views ... they are
too poor of features.
Try to use a subselect in MySQL for example.
Try to make a crosstable with a view ...
Dynamic queries ...
Etc ...
josh wrote:... blogger pointed out this can lead to a false sense of security which actually decreases system wide security. I would not consider security a reason for moving to stored procedures. Any competent developer should be escaping inputs already. Stored procedures does not catch a user inputting "../.../../../../backup_script_containing_passwords.sh" into the database, which then might be included on later in application code. Security is an overarching concern, and IMO has nothing to do with using stored procedures.
Security is never enough. It should be applied to each layer of the system. One should not rely on other layers to be secure (that's for the "false sense of security").
I remember you like the "lazy" integer validation ( intval('123abc') is considered 123) - well, stored procedures and I don't like it

Also, a lot of developers use prepared statements, but it doesn't make them not escaping the passed values.
Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 9:48 am
by josh
Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 10:04 am
by VladSun
While presenting this at conferences and to companies I have become aware of the fact that not everyone agrees to what business logic actually is, and in many cases it's not even been well thought out what business logic is and what it is not.

It seems that I'm one of those not willing to agree with the author.
Apart from the referential integrity constraints, data types, null ability, and indexes that make data retrieval more expedient, the database should have no functional knowledge of what exactly constitutes a customer in the business layer.
This statement is a self-conflicting, IMHO. Integrity constraints are indeed the "knowledge" of what an business object is.
The system becomes database portable with less effort as each of these stored procedures need not be ported to each database.
Not going to agree with this etiher.I even think it's the opposite.
Let me sumarize what I meant by DB side CRUD:
stored procedures which imeplement the same logic as it was implemented with code and DB constraints. Not more and not less.
Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 10:15 am
by josh
You're disagreeing with a fact not an opinion. In that case I disagree that stored procedures exist
Business logic:
* models real life business objects (such as accounts, loans, itineraries, and inventories)
* prescribes how business objects interact with one another
* enforces the routes and the methods by which business objects are accessed and updated
Business logic comprises:[1]
* business rules that express business policy (such as channels, location, logistics, prices, and products); and
* workflows that are the ordered tasks of passing documents or data from one participant (a person or a software system) to another.
What is your definition? Structure / Shape is not the same thing as behavior either
Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 10:40 am
by VladSun
josh wrote:You're disagreeing with a fact not an opinion. In that case I disagree that stored procedures exist
Business logic:
* models real life business objects (such as accounts, loans, itineraries, and inventories)
* prescribes how business objects interact with one another
* enforces the routes and the methods by which business objects are accessed and updated
Business logic comprises:[1]
* business rules that express business policy (such as channels, location, logistics, prices, and products); and
* workflows that are the ordered tasks of passing documents or data from one participant (a person or a software system) to another.
What is your definition? Structure / Shape is not the same thing as behavior either
I do agree it could be a definition for a business logic.
Though, I don't see any storage related stuff mentioned, so I can't see how it's related to our discussion

.
It's interesting what you and Kudzu will say about "table inheritance" features in the modern RDB?
Also, it's interesting to hear Kudzu (
Formerly the Regional Developer Adviser (DPE) for Microsoft MEA (Middle East and Africa)) explanation about choosing between SQL query and stored procedures in datasources, Visual studio

It's interesting to hear about code layer defined DB stored procedures/functions in VS/MSSQL.
Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 11:02 am
by VladSun
Also, in a discussion it's strange when one writes a thousand of words and the other answers by using a single "1 letter" quotation ...

Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 11:05 am
by VladSun
Lookin to Kudzu's article, one shold not use JavaScript at client side because some of the business logic will be put there ...
Funny, what I'm going to do know with my ExtJS applications

Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 12:42 pm
by josh
Inheritance is shape not behavior. He mentions duplicating business logic in both client tier and database tier but mentions the important of the business tier containing the conjuncts of all business logic in the system.
Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 12:50 pm
by VladSun
VladSun wrote:Also, in a discussion it's strange when one writes a thousand of words and the other answers by using a single "1 letter" quotation ...

Willing to discuss all of the other things I mentioned?
I do try to answer all of the aspects you pointed at, but I can't say the same for you.
I'm not going to participate in "piece-per-post" discussion ...
Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 12:57 pm
by VladSun
josh wrote:Inheritance is shape not behavior.
OK, show me its appropriate usage then.
josh wrote:He mentions duplicating business logic in both client tier and database tier but mentions the important of the business tier containing the conjuncts of all business logic in the system.
Consolidation
Instead the business layer should contain all of the business rules.

If i follow your logic, I don't see when DB constraints should be used...
Re: MySQL IF statement/function
Posted: Wed Feb 11, 2009 1:54 pm
by josh
The paragraph immediately following the content you just pasted reads:
'The above scenario is the goal; however some duplication, especially for validation purposes should exist in the client as well. Such rules should be reinforced by the business layer. Furthermore in some systems for reasons of extreme performance benefits such as batch updates may cause an overriding exception and should be placed in the database. So a more realistic approach appears as follows. Note that 100% still exists in the business layer and that the minimal pieces that exist in other layers are actually duplications and exist solely for the purpose of performance or disabling user interface fields according to selections, etc.'
And actually I personally handle cascading deletes / etc.. in the business tier personally. That is actually preferable. Constraint validations if implemented should be in addition to the business logic in the business tier, IMO. Once a single piece of business logic is involved in an operation it should be moved into the business tier entirely in 99.99999% of situations.