MySQL and several other db engines support user defined functions in a database, and have for awhile (MySQL being one of the last db engines to do so). Implementations differ slightly, making the porting of code between sites more difficult. But who here has offloaded some of PHP’s workload onto the db engine by writing SQL functions that can be called as part of a query?
Obviously this is perhaps the ultimate expression of MVC paradigm, where business logic has moved out of the PHP runtime and into the db storage engine itself.
What kind of functionality gets ported into these functions. Experiences with testing them? I’m quite curious about the topic since recently I dipped my toe into the pool so to speak and had to write a query function to make a query resolve in a reasonable amount of time.
Advice, tips and also requested. This is a fairly broad topic, and one that crosses over with the database forum. I wish there was a way to get a thread to belong to more than one forum, but alas, vbulletin doesn’t allow for that. I am going to make a pointer thread though.
In the olden days of fat client applications, everything (certainly everything I and rest of team) wrote only interfaced with the database via stored procedures.
So even simple single inserts, updates or deletes, were not done directly. This meant that you could drop/revoke insert/update/delete/select/drop permissions on the base tables, thereby limiting the damage anyone could do to the data.
This means that SQL injection exploits become harder.
Problem with PHP was the databases APIs weren’t upto scratch when using stored procedures, partly due to everyone building SQL statements.
Well tendency is now for web apps. Coupled with the fact that PHPs’ stored procedure handling has been ropey in the past kind of forced the use of raw SQL statements.
Not a good idea to let MySQL handle logic (stored procedures and triggers). It will make an app not portable, almost impossible to just switch to another database in the future. Better to keep the code in php, use database only for SQL statements.
Of cause if you are certain that you not going to migrate to different database, if this is not an open source project, then it may speed up the program a bit to let MySQL do its own processing since it will probably do it in a more optimized way than php.
I for one could never understand that way of thinking. I took over a big app about a year ago and everything, and I do mean everything was in stored procedures … something as simple as selecting an id from a table. Truly maddening trying to manage.
Agreed to a point. There are times when a stored procedure can make sense, especially for some repetitive work.
I do agree though that letting the database manage business logic is a horrible idea … a database should do what a database does best so your code can do what your code does best
if i have a status code that is used to govern how a particular row should be processed, and that status code can take several values, and each value has a different meaning in the application…
… are all you don’t-put-app-logic-into-the-database guys really saying that i should choose option 1 instead of option 2 here?
option 1
CREATE TABLE foo
( ...
, status CHAR(1) NOT NULL -- valid values defined in the app
, ... )
because, you know, i think that this kind of business logic ~does~ belong in the database
this thread admirably points out once again the folly of trying to come up with hard and fast, black and white, my way or the highway rules like “not a good idea to let MySQL handle logic” and “letting the database manage business logic is a horrible idea”
like it or not, there is ~no~ clean divide between business logic and database implementation
Besides the technical, there are also privacy, security and administrative reasons to keep logic within the database, where data can be partitioned and filtered so only authorized folks get to see it.
I’d say there’s a difference. That’s the database handling data integrity, rather than pure business logic.
In my last job, there were hundreds of long stored procedures and it made development far more difficult. Business logic in the database is bad because:
It makes TDD far more difficult
Lack of debugging. Often saving a SP will throw an error at a seemingly arbitrary line number. It’s also far too tricky to isolate part of the code and test just that or get any kind of useful debug output.
The database does not support many (read: lots) of useful programming features, often meaning you often need to do more than you would in PHP.
On any large site I’ve worked on, the database server always has a higher load than the application server. Why make this worse?
Whenever we updated MySQL at least one would break.
Ok never say never, but I personally find them a burden to work with.
Occasionally triggers are useful for things like automatically backing up deleted records or an audit trail but that’s about as far as I’d let the database handle any business logic.
Yes my issues are mostly with stored procedures, but that is what Michael was referring to in the original post.
As for business logic, the database may not be your only data source. E.g. you might have a shopping cart which uses sessions. How are you going to handle things such as linking files (e.g. images) with database records if you don’t want to store them in the DB? Business logic might include reading from a web service, etc. You simply can’t put this in the DB.
i’m not sure if i gave the impression that app logic should be moved into the database but that is certainly not what i intended
i merely wanted to point out that people who say stuff like “never put app logic into the database” are deluding themselves that there’s a clean divide, because a good proportion of the business logic which deals with data and relationships in the data should be in the database
See to me, that just makes for an incredibly inconsistent approach. I want to change the way something works, I have to at least look in two places. The application logic and database.
I’ve not seen any actual reason for putting this in the database, only reasons against it.
You’re right on disagreeing with “never” but I can’t see any reason to say a “good proportion”.
i did say a good proportion of the business logic which deals with data and relationships in the data
please refer to post #7 – i think option 2 is far superior to option 1, and in my opinion it would be feeble-minded to choose option 1 based on some silly rule not to put business rules into the database (did i use enough pejorative adjectives?)
My understanding of ‘business logic’ is the storage of the data and the management of access privileges.
I cites a stored procedure because that was an example of this sort of thing I am familiar with. Constraints I’m not familiar with. I need to do further study on what is and is not possible given the technology, because the true depth of what the database can and cannot do on its own is something I’m just now really getting into.
Stored procedures, constraints et al are SQL statements. At some point you must choose a feature floor for your ap I think. SQL is implemented many different ways but there is a core to the language that is consistent between databases.
I don’t think this is a good argument against using stored procedures or constraints. Multiple databases engines support them. Also, say you use an ENUM field type in MySQL - that isn’t supported by other db engines so even though it has no logic to it, it will create compatibility problems if you change database engines.
Also, how many times in the life of an application does the db engine really get changed? I’d not seen a transfer between database engines once in 8 years - yet even if the engine isn’t changed I’ve had cases where I’ve had to write import scripts because of major changes in data structures where made due to lack of experience or foresight by the original developer.
I’m working through Gazelle’s db implementation and I want it to use MySQL 5.0 INNODB as a base. I am studying what that can do and wanting to make sure I don’t use anything not implemented in MSSQL, Postgre or Oracle. This should allow the framework to move between these engines.
This is a situation not unlike that with browsers - you write to the lowest common denominator. For a long while MySQL was that lowest common denominator because its widespread but didn’t implement a lot of db features common to other SQL implementations prior to 5.0. If you want to write a PHP ap that can be used by multiple folks out there you can’t ignore MySQL compatibility just as, for the longest while, you couldn’t ignore IE 6 compatibility on the output side.
So as long as the feature set employed is available to all db engines you intend to support I feel it should be considered and/or used.
Actually it’s not uncommon to switch from MySQL to PostgeSQL or even to Oracle, but I’ve seen many people switching to Postgre.
As for ENUM, I stopped using it awhile ago just for this reason.
But for a proprietory non open source project, I would use stored proceedures and triggers in mysql, no problem there. It is probably faster than doing it in php.
The thing is - I started disliking MySQL, the more I use it, the more I dislike it, so now I reached a point that I decided to never use it unless I have to.
There are many reasons for it, not the least is that I think Oracle wants to destroy it.
Oracle is evil, so I don’t deal with evil companies. that’s a whole other story though.
From personal experience with enum, I wouldn’t recommend it (especially if you’re using it as an index which is quite likely because you might want to query all of one type.). If you ever need to add an enum value, the whole table must be re-indexed. It’s difficult to get a list of the possible values, then there’s issues regarding localisation.
the last few posts about the evil ENUM are right on the money, but think the real question is where to define the values that an ENUM may take on
for my status code of post #7, which i had as a more or less generic CHAR(1), my option 2 used a FOREIGN KEY reference to a table of valid status codes, which is by far a better solution than ENUM, but still similar, in that the valid values are actually defined in the database
my interpretation of business or application logic includes questions such as which status codes are actually valid
~not~ storing them in the database, in my opinion, would be a mistake
as would not storing things like “it is not permitted to create an order for a customer that doesn’t exist” if you could, and especially if the database could enforce it for you
