MySQL Views, Triggers and Procs.

Since I have PHP5 and MySQL5 installed on my new PC, I decided to play around with some of the new features (a bit late, I know). Three that really caught my attention were triggers, views and stored procedures.

Views

A view is basically a result set that acts like a table. Instead of having complex queries with several joins (etc) you can just select * from the view instead. An example,

CREATE VIEW latest_threads asSELECT
thread.lastpostid as postid,
thread.threadid,
thread.title,
thread.replycount,
thread.postusername as username,
thread.dateline,
thread.iconid,
thread.lastpost,
thread.forumid,
post.pagetext as preview
FROM thread
LEFT JOIN post on (thread.lastpostid = post.postid)
WHERE
thread.forumid in (2, 4, 6, 8) and
thread.lastpost > 1168559808 and
thread.visible = 1
ORDER BY thread.lastpost DESC;

Now, to select some data from our view, we can perform a simple, easy to remember query:

SELECT * FROM latest_threads LIMIT 10;

So how does that help us?[LIST][*]Optimization: The query is only optimized once - upon creating the view.

[*]Maintenance: You can use this query in several locations without having to update each one.

[*]Application Layering: One of the goals is to separating presentation, logic and data. The logic in selecting * from latest_threads is very simple, and it doesn't really go that deep into the database. If are reading from the actual table, it becomes much more complex. This also greatly aides ease of maintenance.[/LIST]Currently, for vBlogetin, I am mimicking similar behaviour with the $blog->viewX() functions - not fun, because the class becomes very large! I have another method for this now which I will implement later on...

Triggers

Triggers are sort of like plugins/hooks for the database. You can add a trigger (aka plugin) on common events (hooks) such as inserting a row or deleting a row. Why is this helpful? Many applications require keeping counters and other data up to date to simplify other queries in heavy traffic spots.

For example: updating a users post count. Usually you would have to add some (PHP) code to increment the 'posts' field in the user database with every new post created. While in this example, that is quite easy because it only happens in one place, in other places it can get very messy. So instead of finding every section of code that creates posts, we can just add a trigger to the post table that does it for us!

Here is an example query that will add a trigger to update the post count when creating a post, and when removing one.

CREATE TRIGGER update_posts_ins BEFORE INSERT ON post
FOR EACH ROW
UPDATE user
SET posts = posts + 1
WHERE userid = NEW.userid;
CREATE TRIGGER update_posts_rem BEFORE DELETE ON post
FOR EACH ROW
UPDATE user
SET posts = posts - 1
WHERE userid = OLD.userid;

Again, one of the key benefits is simpler code, and less redundant code. We are essentially simulating this with all the rebuild functions we have, but if we could completely automate it that would be outstanding!

Stored Procedures

A stored procedure is like a (PHP) function, meaning you call it, and then it performs a bunch of tasks that you define, optionally passing parameters, and optionally returning values.

Unfortunately, I've had some troubles handling the return values from PHP without MySQLi (specifically sets, but I guess that is what views are for!). But, for writing data, these are still great.

OK so for an example.... if you want a function that will create a new post, while updating any forum counters, etc you can do it. I only have time for a simple example, but that'll have to do. CREATE procedure p_name() to create it, then CALL p_name() to execute it.

CREATE procedure add_post(IN post_title CHAR(40), IN post_contents TEXT, IN post_userid INT)INSERT INTO post (userid, title, pagetext) VALUES (
post_userid,
post_title,
post_contents
);

then to execute it,

call addPost('Whoa!', 'whoa I am tired', 1);

Again, the benefit is simplifying the queries we are executing in the scripts. Instead of performing 3-4 queries to do a simple task you can just call the thing that does them all at once. Of course the queries are still being executed, but at least now they are all grouped together and are easy to edit.

Now, in theory, we could add a trigger that calls a stored procedure to do any data rebuilding / updating, like updating the forum counters, and the user post count, etc. That would save a TON of code - especially in vBlogetin.

The big downside to this stuff is that most hosts aren't using MySQL 5 yet (or PHP 5 for that matter) and this is a huge bummer. I think to add stored procedures, triggers and views you need to have quite a bit of MySQL access granted (not for shared hosting). But, if you have a dedicated server or are a developer, these things rock! I'm already using them on a few projects now!