Database Function Load

[Michael Morris]

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.

[Ren]

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.

[Michael Morris]

was... Has this situation changed?

[Ren]

was... Has this situation changed?

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.

[lampcms.com]

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.

[dc dalton]

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.

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.

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.

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

[r937]

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
, ... )

option 2

CREATE TABLE foo
( ...
, status CHAR(1) NOT NULL
, CONSTRAINT valid_status
     FOREIGN KEY (status) REFERENCES statuses (status)
, ... )

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

[Doug G]

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.

[TomB]

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
, ... )

option 2

CREATE TABLE foo
( ...
, status CHAR(1) NOT NULL
, CONSTRAINT valid_status
     FOREIGN KEY (status) REFERENCES statuses (status)
, ... )

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

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:

1) It makes TDD far more difficult

2) 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.

3) 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.

4) On any large site I've worked on, the database server always has a higher load than the application server. Why make this worse?

5) 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.

[r937]

I'd say there's a difference. That's the database handling data integrity, rather than pure business logic.

so in your world, data integrity is somehow not business logic?

as for your points, they seem to be issues with stored procedures, and not issues with business logic

[TomB]

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.

[r937]

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?

um, with my application logic

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

[TomB]

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".

[r937]

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?)

[Michael Morris]

so in your world, data integrity is somehow not business logic?

My understanding of 'business logic' is the storage of the data and the management of access privileges.

Yes my issues are mostly with stored procedures, but that is what Michael was referring to in the original post.

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.

[r937]

... the true depth of what the database can and cannot do on its own is something I'm just now really getting into.

this is most admirable, and i am confident that you will be very pleasantly surprised

[Michael Morris]

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.

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.

[lampcms.com]

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.

[TomB]

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.

[r937]

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

like i said, a good proportion ...

[Ren]

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.

Depends how trivial the application is. The more trivial the easier it is to switch RDBMs. The whole point of picking a RDBMs over another in the first place is for its unique features. Limiting yourself to some low common denominator of features doesn't make sense.

[webaddictz]

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?

I have. I've moved a lot of logic to the database, although unlike most people in this topic, I use PostgreSQL instead of MySQL, which is ever so slightly more sophisticated in stored functions. My database now performs most of the "intelligent functionality": logging in users, generating lists, doing CRUD, etc.

This decision has had benefits: first of all, it's trivial to change the database scheme without having to fix your code, which is a big plus. Your application code becomes easier to write and read, as it is no longer cluttered with either 80+ classes of an ORM which obscures my queries, or with a lot of SQL that you have to read before understanding what is actually happening.

Another benefit is added security. Granted, this point is moot, as you *can* develop applications that are secure with PHP itself, but I find it easier to deal with that in the database. When a user is logged in, that session on the database gets a customer id, which my views and functions then take into account. That means it's impossible for a customer to ever see something that doesn't strictly belong to him, instead of doing that check in either every query from PHP, or having another if statement in PHP.

Also, I tend to write web-applications, but also iPhone, iPad and Android apps. This means I'd have to write the business logic in Java, PHP, and Objective-C if I weren't moving it to the database, or at the very least behind a common API. The common API is now my database, so creating a webservice to do exactly the same as the web application can do is extremely simple and straightforward.

Furthermore, there is a clean split between responsibilities. I can spend a day writing stored functions, while my colleague writes the front-end and the PHP code that interfaces with the database. I can write stubs so he can make sure he's calling the correct functions. My colleague can mock out the entire database, and only assert he's calling the correct function with the correct parameters: beyond that, there's nothing left to test on the PHP side!

Those of you who've mentioned a lack of ability to test, please take a look at pgtap, a unit testing framework for PostgreSQL. Yes, you can actually stick that in your deployment and your database will be tested separately, which also means that if you get a failure somewhere, you know exactly where it is, because the boundary is clearer than ever before.

Those of you who state that a database should be used solely to store data and ensure the correct existence of relationships, allow me to quote Martin Fowler, when he's talking about Table Module, on that one:

One of the problems with Domain Model (116) is the interface with relational databases. In many ways this approach treats the relational database like a crazy aunt who's shut up in an attic and whom nobody wants to talk about. As a result you often need considerable programmatic gymnastics to pull data in and out of the database, transforming between two different representations of the data.

Databases are more than a key-value store, if it's slightly more advanced than Berkeley. PostgreSQL is, and so is MySQL. The database can do pretty much all you can imagine: I've even seen an Oracle database that would parse an e-mail template and send the e-mail through SMTP. One of my buddies had a PHP script to connect to a database and call a select query with the URL, and rest was done by the database. Dynamic pages, a blog, comments, a forum. Yes, all in the database. Now, I'm not saying that's such a good idea per se, but at least it demonstrates the possibilities.

It has downsides though. While I state above that the boundary is clear, from my point of view, the database shouldn't do everything: sending e-mails being one of them. However, part of the logic in creating a user could be to send an e-mail to verify the e-mail address. In those cases, the boundary becomes unclear, although this is easily solved: I store the users' information, I let the database generate a token, I let PHP retrieve said token, and PHP sends it. Later, when the user clicks on the link with the token, the database will verify and (if all is well) activate the user. Still, the boundary is unclear in a few cases.

Another downside is SQL. Developers tend to hate SQL. I don't know why, as it's a versatile and pretty easy to learn language, but if you're not "into SQL", working with this strategy becomes rather complex. Then again, when you put your logic in the database, all the developers that hate SQL should know is what function to call and with what parameters they should call it. The rest can be done by people who like SQL. I have my own company, so I do have to admit: once the SQL gurus leave, you're up for some trouble as it's easier to find people that really know PHP than it is to find people who really know SQL. It's a business risk, but nothing that can't be overcome in my opinion.

Your milage may vary, but all in all I'm glad I've made the move. Taking away the need for an ORM made it all worthwhile.

[webaddictz]

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.

Sorry for following up my own post, but I just saw this comment and I have to reply. In my opinion, trying to write SQL that *all* RDBMS'es support usually isn't the best of ideas. It can be done, but it leaves you with many headaches and you can't take advantage of any of the "cool stuff" that some RDBMS'es provide. I actually write and host my applications, so for me, just writing SQL that PostgreSQL can swallow is sufficient.

If you're writing distributed applications however, having portable SQL is a nightmare. Simply put, you have three options:

1. Try to write everything in ANSI SQL. Downside: you can't use cool features that save you time, and have to write everything in "pure" SQL.
2. Write many SQL files, one for each RDBMS. Downside: more maintanance and it's hard to keep track of changes.
3. Leave the abstraction to another layer (ORM). Downside: ORM is the Vietnam of computer science.

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've only ever made one transition: from MySQL to PostgreSQL. As I haven't used the "stored functions for almost everything" before I moved to PostgreSQL, this was actually easy, apart from the NOT NULL columns where MySQL somehow had allowed NULL, and the "0000-00-00" date issues The main problem lies with applications that are distributed and should offer a possibility for supporting multiple RDBMS'es.

[TomB]

I'm just playing devils advocate here...

Also, I tend to write web-applications, but also iPhone, iPad and Android apps. This means I'd have to write the business logic in Java, PHP, and Objective-C if I weren't moving it to the database, or at the very least behind a common API. The common API is now my database, so creating a webservice to do exactly the same as the web application can do is extremely simple and straightforward.

If you're allowing multiple sets of client code direct access to the DB I can see the benefit entirely... but if you're going through a webservice anyway, how does that help?

This decision has had benefits: first of all, it's trivial to change the database scheme without having to fix your code, which is a big plus.

I'm not sure I agree with this... say I add a coulmn to a table. With an ad-hoc query, I update the query in the application code and I'm done. If It's in a stored procedure, I need to update the query in the stored procedure, the parameter list for the stored procedure and the application code to send the new parameter.


I'll have to look into pgtap, do you know of anything similar for mysql? On a similar note, how do you handle version control?

[webaddictz]

I'm just playing devils advocate here...

That makes the world interesting, so no worries!

If you're allowing multiple sets of client code direct access to the DB I can see the benefit entirely... but if you're going through a webservice anyway, how does that help?

Well, you don't have to duplicate the logic. Agreed, you could obviously write an application that extracts the logic out to a Domain Model, and you'd be off with the same. Nonetheless, extracting it to the database does the same thing and keeps things simpler, at least, for me. Then, I'm not afraid of SQL

I'm not sure I agree with this... say I add a coulmn to a table. With an ad-hoc query, I update the query in the application code and I'm done. If It's in a stored procedure, I need to update the query in the stored procedure, the parameter list for the stored procedure and the application code to send the new parameter.

When you add a column to your table, you're screwed either way. If you've ever tried to accomplish this, regardless of where you've placed your business logic, it's hard to do this with minimal effort. If you're using ad hoc queries, you'll have to update those. I don't agree that you're off with updating just one query though: you'll likely have one that can update the table, one that inserts into the table, a few that select from that table, etc. If you're using MVC, you'll have to update your view as well, by the way.

If you're using stored functions, you'll have to add a parameter, and you'll have to update all queries that call the stored function. The point I was trying to get across is that by changing the underlying structure of your database will not break your application in any way, as the application itself is fully unaware of the underlying scheme.

I'll have to look into pgtap, do you know of anything similar for mysql? On a similar note, how do you handle version control?

I don't know a Unit Testing framework for MySQL, sorry. Version control is actually simpler than you would imagine. I use phing for deployment of applications to servers, and phing has a "task" called dbdeploy. That task is ridiculously simple, I'll try to explain the gist of how it works.

First off, your database contains a dedicated table for version tracking (table public.version). You run phing and it connects to the database to determine which version it's based on. After that, it will look into a folder of your choice (for us, the standard is /deploy/deltas) and look for .sql files. Each .sql file begins with a number, so the first commit will usually contain the file 1-create-initial-structure.sql.

If you want to change the scheme, you'll have to write a new SQL file for that. You pick the next consecutive number, write a description, and write the SQL that should be executed. Also, you write a script that does the exact opposite: an undo, in case anything goes wrong. The dbdeploy task will know that the currently deployed version is 1, and see that there's a file that starts with 2, and so it will execute that.

You'll have to make rules to make this work. First off, you'll have to have either central tracking of the next consecutive version number (we have a board to write it on), or you'll have to make one man responsible for assigning those version numbers.

Second: you'll have to make sure that - no matter how much you want to - you never change a committed SQL file (except for syntax errors). If you want to undo what you did, you'll write another SQL file that will undo the previous. If you fail to live by that rule, you'll possibly still have different states the database might be in, which is a bad thing (tm). Changing a previously committed SQL file should at the very least mean you buy your colleagues a beer

That's about it.