Archive

databases

I’ve become a really big fan of the PickledObjectField provided by this django snippet.  So much so that I use it in almost every django model I create these days.

Basically it serves as the best way to do an object store in your database and perfectly translates in any JSON conversion.  Its an essential tool in any javascript heavy application.

In my current project, Chart.io we’re aspiring to be google analytics for your database.  We’re basically creating a system that will handle all of your dashboard/analytics needs in the easiest way possible.  This means that we have a LOT of different charts and more in the making.

Its not feasible to put all of those extra parameters that each chart type requires into the model as different columns.  We would end up with an incredible mess in short order.  So I instead create a PickledObjectField called ‘params’ in the model.

class Chart(models.Model):
    ...
    params = PickledObjectField(default={'just': 'some', 'default': 'parameters'} )

The params variable then takes most any dictionary of parameters and automatically converts it to string to be stored in the database.

The following command for example will save a params value of something like “KGRwMQpWa2V……” to the database, but you can still use it just like any dict object.

>>> chart = Chart(params = {"type": "scatter", "dot_size":, 4, "color_list": ["red", "green", "orange"], });
>>> chart.save()
>>> chart.parms
{"type": "scatter", "dot_size":, 4, "color_list": ["red", "green", "orange"], }
#You can also treat the field just like a dict
>>> chart.parms['awesome'] = 'for sure'

Its worth noting that there is a similar snippet to this that uses JSON object to string conversion instead of Pickle.  I find that when using Javascript so heavily its easier to use some other string conversion so as not to get confused and I’ve been really impressed with the way that this particular snippet works.

Its incredibly rare that a django snippet becomes such a major tool.  With the exception of my subdomain middleware, I can’t think of another snippet that I use more regularly which leads me to think that it should really get moved into the core fields that django provides.  Object store is essential element to many applications and the PickledObjectField is the best way to do it.

The Django manager is a really handy tool.  I wrote earlier about making your own custom managers and there is a lot of other great documentation on it.

Django comes with a bunch of helpful management commands like ‘flush’, ‘syncdb’, ‘test’, etc.

I’ve created a generic ‘drop’ command as I felt it was missing.  I often found myself going into mysql to drop and re-create a database.  This is needed whenever you significantly change your models and need to start over.  The ‘drop’ command does that automatically using the database information in your settings file.

The following code is from ‘drop.py’

from django.conf import settingsfrom django.conf import settings

from django.core.management.base import NoArgsCommand

class Command(NoArgsCommand):
 help = "Drop and re-create the database"
 def handle_noargs(self, **options):

 import MySQLdb

 print "Connecting..."
 db=MySQLdb.connect(host=settings.DATABASE_HOST or "localhost" ,user=settings.DATABASE_USER,
 passwd=settings.DATABASE_PASSWORD, port=int(settings.DATABASE_PORT or 3306))

 cursor = db.cursor()
 print "Dropping database %s" % settings.DATABASE_NAME
 cursor.execute("drop database %s; create database %s;" % (settings.DATABASE_NAME, settings.DATABASE_NAME))
 print "Dropped"

To install simply place this code in a file called ‘drop.py’ and add it to a management comands folder.  If you don’t have a management command folder yet you simply need to create the following file structure in one of your app directories (MY-APP-DIR).

MY-APP-DIR/
  management/
    __init__.py
    commands/
      __init__.py
      drop.py

Now, whenever you’ need to whipe your database and start fresh you can simply run:

./manage.py drop

This isn’t an article on good practices in database design.  We definitely wouldn’t be the guys to ask about that.  However, after many days of deliberation, and by deliberation I mean yelling, calling each other various names, and wasting several tablets of paper we have finally completed and have some suggestions on how to keep organized in the designing process.  The suggestions are organized in the following steps.  Hopefully when you’re done you’ll have a database to be proud of like these nerdy guys here:

nerds doing database design

1. Realization:  First you need to realize that the database design is a lengthy task that grows exponentially with the complexity of your web app.  Make sure you allot yourself a large chunk of time and patience for the task.

2. Site Planning:  Before making your tables you have to have your entire site organized.  Its best to draw out all of the viewable page types the viewer will see and get a concrete set of high level functions that the user will be able to do.  Its best to minimize these down to base functionality.  Forget about the million bells and whistles that you might attach on in a year or two, and concentrate on making just the bike …. err webapp.  After simplifying the main functionality you’ll find the database design much simpler to keep in your head and its usually easy to attach on the extra stuff later.

3. Research: If you’re not too good at database design, know very little about how SQL servers work or don’t know what manytomany relationships are you’d do yourself a favor by reading up on it.  Here are a few resources.

4. Divide and Conquer:  Having a good grasp of what you’re making and a feel for SQL servers its time to get to it!  We want to emphasize, that no matter how big or small your group is, no matter how smart or dumb they are, even if you have a pacifist on your team, its best to split up when designing the database.  Each of you go to your own corners and design what you think will work.  Then, after a day or however long it takes you, get back together and share what you’ve got.  You’ll find that each of you are missing a few things and many different ways of looking at the same thing.  Its best to merge these ideas at the end when everyone has a good grasp of what they’ve designed.  Its bad to merge them as you build, sparking debates that go on way too long and make everything feel larger and more complicated than it really is.

5. Cards:  When designing your tables we found it worked best to write them on note cards and rearrange them on the table.  Its really easy to edit or throw out cards.  Its also easy to visualize the different components and rearrange them in-front of you to fit better in your brain.  When you’re done, tape them to a piece of tag board and draw lines to visually demonstrate the relationships between the different tables.  It will also look much prettier when you go back and meet with the rest of your group.

6. Celebrate: You’ve accomplished a large amount of work!  It might not seem so.  You were expecting to be well into your hacking by now, but hey, it takes a really long time and now you have a great platform to build from.  Make sure you listen to a happenin’ tune and do a little jig.  This part is best done with the group.

Dave and Priyesh