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.

Codonics – Final Co-op Report – Spring 2012

During my co-op at Codonics, I have been heavily involved in implementing wireless and networking support for Safe Label System, a medical device used in Operating Rooms to help reduce the number of medical mistakes in syringe labeling.

Responsibilities

            During my second co-op at Codonics, my responsibilities were focused on software development; creating new features that were required in order stabilize and market the Safe Label System. Sometimes I was given a very specific set of requested features, and asked to implement them exactly as specified. Other times, I was given room to prototype possible solutions to common requests, and design a solution that would meet customer needs in a safe and effective way. My role as a software engineer began to extend into product development, where I was designing new features without specific requirements, trying to find ways in which I could improve the existing system.

            Near the end of my second co-op I began to help setup the SLS System at various hospitals across the United States. I worked with Massachusetts General Hospital, Porter Medical Center, Northbay Medical Center, and St. Vincent Charity Medical Center to install the Codonics Safe Label System at their sites. In all cases, I was responsible for diagnosing any problems during the setup, including wireless connectivity, network setup, and email alerts. I had to think on my feet when issues arose during these installs, because I had no way of changing any code if a defect was found. I was responsible for ensuring the success of the technical part of the installation. In some case, I was flown to the site, or participated in conference calls with IT Administrators.

Skills

            My co-op at Codonics has helped to hone my skills in programming, as well as understand advanced concepts like multi-threaded applications and wireless networking protocols. By writing the networking code for the SLS in Java, I have gained a lot of knowledge of how wireless networks work, can be configured, and optimized.  I have spent many hours testing wireless devices, building drivers, and setting up various server configurations to test various types of wireless networks. By designing the networking code in Java, I became familiar with how the underlying Linux OS works with networking, and since then I have began thinking of how it could be improved. I even started my own open source project that communicates with the Linux kernel at a very low level in order to get information about existing network connections, and relay that information to Java through a Java Native Interface.

During the times that I was on site at various hospitals, I improved my communication skills by talking with nurses, doctors, and IT administrations about their needs and existing network infrastructure. This allowed me to improve my communication skills with non-engineers, who may not understand some of the technical lingo used by engineers. The ability to communicate effectively is not a skill that is easily learned in a classroom environment, so my experience at Codonics has been very valuable.

Reflections

My co-op at Codonics has helped me to develop many skills related to Computer and Software Engineering. I have spent much of time developing new software features that have helped to fuel sales of a medical product that has the capability to save lives in hospitals across the world. There is a lot more to being a software developer than just writing code. It’s important to go out in the real world to meet your customers; getting to know the customer will help you define new features for your product that will continue to make it relevant in your industry.

Continual improvement of the Safe Label System will benefit not only the customer, but the nurses and patients as well. Establishing relationships with customers helps to gain their feedback about what features work well, and which ones need reconsidered. It’s a continual feedback process that develops and matures the product over many cycles. The software must be designed in a way that is atheistically pleasing, easy to use, and efficient. Sometimes decision revolving around the UI can take several weeks or even months, but it is important to choose the best option for customers.

My co-op has allowed me to apply many courses to real-world applications. My Advanced Game Design course gave me a deeper understanding of the Java language, which improved my ability to write code for the Safe Label System. I have become very proficient in Java, and I hope to continue that skill into creating mobile applications for Android. Additionally, my Operating Systems course helped to me to understand the complications that can arise in multi-threaded applications. This knowledge helped me to write performance enhancement and fix bugs relating to concurrency.

I am excited to be returning back to school to complete my Bachelors degree. My current plans are to continue one extra semester to obtain a Masters degree as well. My co-op at Codonics has helped give me an idea of what software engineering is like, and I will be excited to obtain a full-time position in software when I have completed my schoolwork. My co-op has shown to me that I am well adept in a field that I enjoy very much. I look forward to the completion of my studies at Case Western, and I am confident I will be ready to work as a software engineer upon graduation.

Codonics Final Co-op Report for Fall 2011

Codonics

During my co-op at Codonics, I have been heavily involved in implementing wireless and networking support for the Safe Label System, a medical device used in Operating Rooms to help reduce the number of medical mistakes in syringe labeling.

 

Responsibilities

At the beginning of my co-op at Codonics, my responsibilities were focused on testing software. I used a system called TestTrack to record bugs and related information. During development, developers updated these TestTrack tickets, and it was my job to verify the bugs were fixed.  In addition, I wrote up Test Reports, which detailed tests that I preformed to verify some type of functionality as defined in the functional specifications. One of my tests involved the creation of a webserver that would display barcodes on a web page. This webpage was displayed on an iPod Touch, and placed directly underneath a scanner. My test helped to verify the long-term functionality of the Safe Label System by printing over 1,000 labels automatically.

My largest responsibility at Codonics by far has been to oversee the development of the Safe Label System’s networking capabilities. I spent a few weeks testing multiple wireless adapters, and determining what software was needed to utilize them. Then, I began to write the software that would allow users to connect to wireless networks on the device. As the requirements changed, I have been updating the networking code. Recently, I have been implementing support for WPA/WPA2 Enterprise networks, such as EAP-TLS and PEAPv0-MSCHAPv2. These networks use certificates to encrypt and authenticate connections, which adds complexity to the software. I had to consider the best way to store this information, without letting it get into the hands of unauthorized users. Since these types of networks are used in hospitals, it is important that the software correctly handles such network authentication protocols.

 

Skills

Codonics has helped me to further supplement skills that I would not have received through classes at Case Western Reserve University. I gained experience with writing test procedures using LaTeX, a typesetting language used to create professional looking documents. I also learned how to write scripts using AutoIt, in order to automate many of the test procedures. Codonics also introduced me to JUnit tests, which are small pieces of code that test production code in order to look for regressions in functionality. These test are useful when multiple programmers are working on a project, or when a large refactoring of logic or code occurs.

I have used many skills that I learned in class while at my co-op with Codonics. My Advanced Game Design class helped me further familiarize myself with Java, a programming language used extensively during my co-op. I used the knowledge in my Operating Systems class to help develop the wireless networking code, as well as the restricted shell environment used in the Safe Label System. Operating Systems class helped to further my knowledge of concurrent processes and thread-safety, which I incorporated into my networking code. If multiple processes or threads attempted to manage a single wireless device, the adapter could end up getting configured improperly. In order to prevent such an occurrence, I implemented the use of a single thread pool that executes task synchronously, based on a given priority. Operating Systems, as well as Compilers, increased my knowledge of programming languages such as C and C++. These two languages are used in creating the restricted shell environment, which only allows authorized users to execute pre-specified commands, while blocking the use of others. There are cases in which we want to allow the user to run a pre-defined script, which could execute commands not normally allowed. In that case, the restricted environment is able to break out of the restrictions in order to use those specific commands, as well as access specific files and directories that are not normally permissible.

 

Reflections

My co-op at Codonics has helped me develop real-world experience in developing software as part of a team. I spent a majority of time at the beginning of my co-op doing testing, and it allowed me to understand how the products functioned in respect to the end user.  Before my co-op at Codonics, I never realized how much effort goes into testing, especially for medical products. For example, there are many test procedures that need to be executed on every release candidate to ensure there have been no regressions. New test procedures are created from test reports, which are sometimes planned out before the functionality has even been implemented. As I became familiar with the products, I began writing my own tests. My first test report covered printing 1,000 labels on an SLS, to ensure there were no memory leaks. In order to run the test, I created a webserver that generated barcode images, and loaded it onto an iPod Touch. The plan worked successfully, and soon my SLS unit was printing barcodes non-stop, automatically. My test interested many of my coworkers and supervisors, who stopped by to see the test in action. Before my barcode server, tests were done manually by testers, which consumed valuable time. In addition, testers were only able to test a limited number of drug vials that were around during the testing. My barcode scanner was able to test every drug known to the SLS, because it generated the barcodes automatically from a list of drugs in the SLS system. I hope that my code helps to shorten the time it takes to test the Safe Label System, and give testers more time to investigate other functional aspects of the device.

As I moved to more of a developer role, I began to learn the importance of writing unit tests. I was given a book on JUnit, and from there began writing and updating unit tests as I worked on the SLS project. Unit tests are small pieces of code that test the functionality of production code. These tests are very valuable when multiple developers are working on a project, and help to detect regressions quickly. There are some downsides however, because time must be taken to update the unit tests when the logic has changed. During my co-op I wrote the networking system for both wired and wireless network interfaces, and was forced to refactor my code as the input specifications changed. Unit testing helped to ensure the overall functionality of my design did not regress after refactoring the logic, and served to increase my confidence in my code changes.

I learned the important of writing software to technical specifications, which allows multiple programmers to work on various parts of the code at separate times. When code is written as agreed upon, it makes for an easier integration when developers begin to merge their code together. At first, such specifications can seem tedious, especially when they change as the software matures, but these specifications help to ensure the software behaves as expected, and can also serve as an outline that can be used by the Testing department to ensure a thorough examination of the software.

While working on Codonics, I was given the opportunity to do some business travel, an opportunity rarely given to co-op students. My first time traveling as a Codonics employee was to Anesthesiology 2011 in Chicago. While working at the tradeshow, I learned about various vendors in the medical industry, and how the Safe Label System positioned itself among them. During off the clock hours, I relaxed with fellow employees and learned more about the company, employees, and culture.

My second trip representing Codonics was to Boston, and it was my first time flying by myself. It was a very new experience for me, because I was responsible for taking along a Safe Label System to demonstrate the new networking capabilities at Massachusetts General Hospital. Once in Boston, I worked with MGH staff in order to verify our software was able to connect to their EAP-TLS network. I caught a glimpse of how a hospital’s IT staff was structured, and how different people in the chain were able to help push agendas along. We discussed networking requirements, user interface preferences, and likely use cases for how the Safe Label System would be networked in the operating rooms. I was awe-struck at how large the hospital complex was, which impacted my view of how important networking support would be for our next release of software. If a hospital buys hundreds of units, and places them in various buildings, it would be a challenge to update and verify their all working properly.  Networking support will give administrators control of all the units from one computer, with the ability to push updates out to all the systems with a push of a button. In addition, network administrators will be able to monitor the status of the units, and receive email notifications if something unexpected occurs, such as an unknown drug being scanned. The software I developed will help to ease the introduction of the Safe Label System into large hospitals, and reduce medical mistakes by improperly labeled drugs. Being given the chance to travel with Codonics has been a great privilege that has helped me to see other parts of the company that I would not have normally be exposed to.

I have been very happy with my co-o p at Codonics, and the experience has helped to boost my confidence in my programming skills. Codonics has helped me grow as a computer engineer, and I hope to continue learning more during my second co-op with them.

Codonics Mid Co-op Report

Check out my Final Co-op Report Fall 2011

Codonics

During my Co-op at Codonics, I have gained experience in testing software, and developing new features for the Codonics Safe Label System, a medical device used in Operating Rooms to help reduce the number of medical mistakes in syringe labeling.

Responsibilities

At the beginning of my co-op with Codonics, many of my responsibilities revolved around testing software that was near its release. I verified that known bugs were successfully fixed, and performed tests that revealed the existence of new bugs. As the software was updated, I updated test procedures to reflect new changes.  After I had become more familiar with the software, I formulated and wrote my own test reports, and developed custom scripts that were needed for the tests.

Once I had familiarized myself with the software, I was given task of fixing a few bugs with the software. Once the software was released, I began adding new features in order to allow the hardware to connect to wireless networks. Currently, I am continuing to improve and stabilize the wireless code, as well as develop a solution to restrict user access in the Linux terminal.

Continue reading “Codonics Mid Co-op Report” »

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

Privacy Policy