Schemamigration

As progress is made through any project, there comes a time when the creators realize that the models that they had in mind for the final product aren't sufficient to complete the task. At this point, the models must be revised in order to accomplish the goals of the project.

Unfortunately, if the project that is being built involves a database, this adapting of the model can be a difficult process. In our case, we decided to add in a schedule to every light, room, and house. The Django framework is under strict instructions to create tables, but not to update them. Then, when you choose to add something, the Django website will throw an error because it doesn't understand why there is an extra thing added into its design. If the story ends there, that means that every time we adapt our model we will have to re-create all of the test data that we have generated, adding in the schedule information as we build it new.

Fortunately, the story doesn't end there. The good folks at Aeracode have developed South, a data migration tool for Django projects. South takes care of the difficult task of adapting the database for you. All that one needs to do is set up an initial state of the database with the South tool and then when a change has been made, create and apply a data migration. For our purposes, it worked very smoothly and the tutorial was easy to follow. 

After installing South, there were just four simple commands to run:

./manage.py syncdb

./manage.py convert_to_south myapp

./manage.py schemamigration myapp --auto

./manage.py migrate myapp

The only minor hiccup in the migration was that the database did not know what value to fill in for the schedule for each of the lights. Since the lights are allowed to not be on a schedule, a quick null=true fixed this problem and our app was fully functional again.

Let Me Just Write That Into My Schedule ... Not

I knew when I was assigned the task to develop a schedule for our project it wouldn't be easy. I just didn't have any idea how not easy it would be. I'll review a few of the decisions that we made regarding how schedules will be implemented in our project.

1. Lights, Rooms, and Houses will have schedules. This is opposed to a system where a schedule would be in charge of managing all of the parts of a house. If a schedule were to be in charge of managing the house, it would simplify things because all of the schedule information would be located in one place. On the other hand, Lights, Rooms, and Houses each having their own schedule feels like a better reflection of how the world works. I like to think that I have my own personal schedule that I choose to follow in going to classes and appointments, rather than my schedule ruling over me and dictating what I must do.

2. Triggered events are represented by a start time AND an end time. This decision was primarily made based upon how we plan to implement checking whether or not the light should be on a schedule. The plan is to examine the current time and the light's schedule, and if the current time indicates that the light is scheduled to be in a certain state, then the light will be switched to that state. Then if a user changes the state of the light from its scheduled state, a variable will be set indicating that the light is intended to be in the state that it is, rather than its scheduled state. Once the end time has been reached, this "override" switch can be reset to off so that the schedule will work as planned in the future.

In an ideal world, we would like the database to push information regarding whether the lights should be on or off to the client rather than having the client constantly fetch what state the lights are in on the database. For this reason, our decisions on how to implement the schedule may change in the future. 

Accessing Databases via Python

In order for our Raspberry Pis to be able to turn off and on their lights according to the state on the website, some sort of interfacing with the database outside of the Django framework will be required. This can easily be accomplished using the same PyMySQL module that allowed the Django framework on Python3 to be compatible with MySQL. What follows is a basic script which we used to access our database and get information about the states of the lights. By modifying this basic script to include code that Arik has been posting about GPIO reading and writing, our application should be nearing a usable state where modifying the database equates to a change "in the real world."

import sys

import time

import pymysql

lights = {'bathroom light 1':0,'bathroom light 2':1,

        'kitchen light':2, 'bedroom light':3}

conn = pymysql.connect(

db='database',

user='user',

passwd='password',

host='123.45.67.890',

)

cur = conn.cursor()

cur.execute("SELECT * FROM lightDB WHERE user_Id = <user_id>") 

for line in cur:

    for key,val in lights.items():

        if key==line[1] and line[3]==1:

            print("turning",key,"on")

        elif key==line[1] and line[3]==0:

            print("turning",key,"off")

Each line in cur is a tuple that has various information about the lights, the 1st (line[1]) item in the tuple is the description of the light, and the 3rd (line[3]) item is the state of whether the light is off or on.

There are some obvious security concerns at this point if this database were a database maintained by the company which contained all of its users lights. For this and other reasons, we are considering restructuring our design regarding where the server for the lights is run and how the information is accessed/backed up. Look for more on this in a later post.