MySQL Triggers and Django

Django is a great Web Framework to build websites in. It handles so many things for you that sometimes it can’t handle the most basic things. If you have ever tried to use Triggers in MySQL and Django, you know what I mean (or will soon find out). Now, you may not have many uses for Triggers, especially when Django handles the majority of the work for your automatically, but in some cases, it is neccessary to define your own triggers at the database level (like for a Database course in college).

Django provides you with this really nice “syncdb” command, but there isn’t an obvious way to to insert custom triggers. Searching around, I found that you can provide “custom” sql during the process, which would seem like a great place to insert triggers. Just add a “sql/” folder to your Django app, and create a file called <model_name>.sql. Or, if you want to be more specific, <model_name>.mysql.sql. At first I thought this would be a great place for the trigger, but it didn’t work. Django kept getting hungup on the ‘;’ in the Trigger.

When creating a Trigger using a GUI like MySQL Workbench or ProSequel, the application sends SQL commands separately,  as delimited by the ‘;’. The problem is that a ‘;’ may exist within a Trigger statement. For example, the following would not work:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER validate_enrollment_hours
BEFORE INSERT ON service_serviceenrollment
FOR EACH ROW
BEGIN
DECLARE event_start datetime;
DECLARE event_end datetime;
SELECT start_time, end_time INTO event_start, event_end FROM events_event WHERE id = NEW.event_id;
IF NEW.start < event_start OR NEW.end > event_end THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid Start/End Time', MYSQL_ERRNO = 1001;
END IF;
END;

I spent the better part of an hour trying to figure out why. Turns out the ‘;’s really confused SequelPro and MySQL Workbench. The solution was to change the delimiter and execute the following statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
delimiter |
CREATE TRIGGER validate_enrollment_hours
BEFORE INSERT ON service_serviceenrollment
FOR EACH ROW
BEGIN
DECLARE event_start datetime;
DECLARE event_end datetime;
SELECT start_time, end_time INTO event_start, event_end FROM events_event WHERE id = NEW.event_id;
IF NEW.start < event_start OR NEW.end > event_end THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid Start/End Time', MYSQL_ERRNO = 1001;
END IF;
END;
|

Great! Now I can actually get this Trigger in the database, but if I use the latter example in the <model_name>.mysql.sql file, it still doesn’t work! Finally, I stumbled upon ticket #3214 on the Django website. While I wasn’t too keen on doing any kind of patch, there was a interesting little snippet I read in there.

“As a workaround, multi-line SQL statements have to have someting other than whitespace between their semicolons and newline characters.” -Sam Morris

Eureka! Finally!

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER validate_enrollment_hours
BEFORE INSERT ON service_serviceenrollment
FOR EACH ROW
BEGIN
DECLARE event_start datetime; --
DECLARE event_end datetime; --
SELECT start_time, end_time INTO event_start, event_end FROM events_event WHERE id = NEW.event_id; --
IF NEW.start < event_start OR NEW.end > event_end THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid Start/End Time', MYSQL_ERRNO = 1001; --
END IF; --
END;

All is well now. The apocalypse has been diverted, and I can finally move on to the next million other things I need to do.

Taming Web Development

Lets face it… we all need Source Control in our projects, even for small scale projects with just one developer. There are too many times when we need to revert to a past revision of our code. In a typical computer application or game, source control makes sense; but what about web development? When you factor in an FTP server and MySQL database, how do you accomplish your version control? How do you keep everything in sync when you are deploying to an external server? Most web projects are tracked via SVN, but SVN is very limiting compared to other options such as Git. Git provides powerful team collaboration tracking and revision control, but how can it be implemented to work with a web server? The answer is surprisingly easy.

I had been searching for an answer to this question, when I stumbled upon an article by Joe Maller (http://joemaller.com/990/a-web-focused-git-workflow/). He offered a great explanation of how I could use Git to control not only my project, but eliminate FTP altogether. I have my own server that i use for hosting a website, and having full control over that server opens up the possibilities for a very handy work flow. Using Eclipse as my Primary IDE, I can develop websites, commit the code, and push it to my web sever. I don’t need to leave Eclipse or use FTP. Whats even better? I don’t have to worry about losing my code if my web server goes down, because I’m not push my code to my web server. If my web server were to suddenly kick the bucket, my code would be safe and sound on GitHub.

One problem I had though with this method was getting my server to pull the latest code from GitHub. After doing some investigative detective work, I found that the account my apache and php were running on didn’t have enough permissions to execute a git pull. To be honest, that is probably a good thing for security reasons, but not exactly want I wanted at this point. I needed to execute git pull somehow, but outside the limited permission scope of my Apache web service. Instead of a php script calling the git pull, I need something else to execute the script with all permissions needed. Since I knew exactly what needed to be executed, and what would happen, I eventually settled on writing a simple program to do the git pull.

1
2
3
4
5
6
7
8
9
10
11
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <unistd.h>

int main()
{
setuid(0);
system("/home/dev/post-update-hook.sh");
return 0;
}

Great! Now just compile that and execute:’

1
2
sudo chown root /path/to/program
sudo chmod u+s /path/to/program

 

And for the PHP file:

1
2
3
4
5
<?php
ini_set('max_execution_time', 300);
system("/home/dev/gitPullExec");
echo "Complete";
?>

Great! In retrospect, it would be better to setup the permissions so that it is based on a group, and not the user. Now I just opened a security hole right? Probably, after all, if someone was to gain access to the machine, they could change the post-update-hook.sh file into something else and then execute it. But you know.. I don’t really need to worry about that. This server is only used to host one site. If user was to gain access to the one account I setup, which is where the site is located, then there really is no need for them to use root, they would have access to the entire site anyways. The site doesn’t really host any mission critical data. Not to mention, the only way the script could be executed without authenticating, would be to execute the PHP script that calls this program. If I wanted to make it more secure, I could have it check the hash of the script, and verify it is correct before it runs it. Of course, if a user had access to my GitHub account, they could inject code into the site by committing the code there, and then waiting for this hook to run. However I’ll assume that if that were to happen, then I would have more problems than just security on the server, wouldn’t you agree?

MySQL – MEMORY

One of the things that I enjoy setting up are MySQL databases, its like my anti-drug. I enjoy the feeling of setting up tables, related via “PRIMARY_KEYS”. There is just a thrill to setting it all up, although I can admit the data entry part isn’t always fun if you have a large set of data. I think I started using MySQL databases when I was a kid, and as I grew, learned more and more about them. I will not pretend to be an expert by any means, but I feel as though I have grown over the years to really become familiar with  PHP+MySQL ( soon to include Python + MySQL I hope).

During the Fall 2010 Career Fair at CWRU, I heard about one of the visiting companies, Yelp.com, was giving a talk on MySQL database optimization and use in a large scale environment. I was fascinated by the very though of it. I have always used MySQL for my own personal projects, but have never gotten a chance to see how it would withstand to a large base of millions of users. All the queries, page views, tracking, logs. The database must be huge, and the larger the database, the slower it will become. How do they manage to handle such a load, not to mention the backup servers they must use to prevent the loss of such critical data.

As a kid, I stuck with MyISAM as my default storage engine for MySQL, it seemed the defacto standard, and I didn’t need to worry about transaction locks (unlike in Android, where problems always occurred without them, but thats’ a rant for another time). — So you can imagine my glee when I learned that non-crucial information, could be stored in a very fast table that uses the MEMORY storage engine. Now, this information is extremely volatile, and if the server crashes or goes down, that data will be lost. For some statistical data however, it could be all you need, and the performance benefit would be valuable. If you wanted, you can have a cron job or back scheduled to collect this data at defined intervals. As long as this data is require or crucial to your website, you may want to consider using this type of table storage for your tracking or statistics.

For a list of storage engines in MySQL 5.5, check out Chapter 13 in the MySQL Reference Manual

Moodle (Modular Object-Oriented Dynamic Learning Environment)

If you have every taken a course at Case Western Reserve University, you professor has probably used Blackboard at some point in time. Professors use Blackboard to provide course supplements and materials, supposedly to help the student learn better. Blackboard however, is nothing more than a fancy upload and storage facility. True, blackboard contains social features such as discussion boards and wikis, and can even provide digital quizzes, but that’s as far as it goes. This piece of software costs thousands of dollars in licensing fees, and for what? Its mainly used to provide storage for course documents. It is a waste of money  when compared to things like Dropbox. If professors want to supplement student learning, they need to involve the student in activities that stimulate the learning experience.

That’s where Moodle comes in…

The focus of the Moodle project is always on giving educators the best tools to manage and promote learning, but there are many ways to use Moodle:

  • Moodle has features that allow it to scale to very large deployments and hundreds of thousands of students, yet it can also be used for a primary school or an education hobbyist.
  • Many institutions use it as their platform to conduct fully online courses, while some use it simply to augment face-to-face courses (known as blended learning).
  • Many of our users love to use the activity modules (such as forums, databases and wikis) to build richly collaborative communities of learning around their subject matter (in the social constructionist tradition), while others prefer to use Moodle as a way to deliver content to students (such as standard SCORM packages) and assess learning using assignments or quizzes.

Working as the Lead Developer & Architect in the Moodle Pilot at Case Western Reserve University, I have been impressed with Moodle’s ability to be customized with our own modules and plugins. I have been able to extend Moodle’s capabilities far beyond Blackboard to meet the demand of professors who wish to teach their students in a very social construct. Moodle can organize a course in a variety of ways:

  1. Topics – Course sections are given a topic number, and can also be given a name
  2. Weeks – Each week of the course corresponds to a course section
  3. Discussion – The course revolves around a discussion board

Topics and Weeks are probably the most widely used course formats in our pilot today. Professors are making some very beautiful course sites, filled with informative and fun activities for students to do every week. Using plugins such as VoiceThread, Moodle offers our students multimedia presentations that allow for questions, comments, and feedback. As a developer, Moodle offers me a wide range of ways to expand the social structure of learning for students. I have learn so much from working with Moodle, in both coding practices, and social communication.

Privacy Policy