Schinckel.net

There was a recent question on Stack Overflow about Django Single Table Inheritance (STI). It got me thinking about how to use my FSM-proxy stuff to just be about STI.

Note: this only works when all sub-classes have the same fields: the example we are going to use here is different to a state machine, in that an object may not change state after it has been created.

class Sheep(models.Model):
  type = models.CharField(max_length=4)
  tag_number = models.CharField(max_length=64)

class Ram(Sheep):
  class Meta:
    proxy = True
  
class Ewe(Sheep):
  class Meta:
    proxy = True

In this case, we can fetch all sheep as Sheep.objects.all(). However, this gives us the objects as Sheep instances, when we want those with type='ram' to return Ram instances, and those with type='ewe' to return Ewe instances.

We can do this, by the magic of type().__subclasses__().

class Sheep(models.Model):
  # fields as above
  
  def __init__(self, *args, **kwargs):
    super(Sheep, self).__init__(*args, **kwargs)
    # If we don't have a subclass at all, then we need the type attribute to match
    # our current class. 
    if not self.__class__.__subclasses__():
      self.type = self.__class__.__name__.lower()
    else:
      subclass = [x for x in self.__class__.__subclasses__() if x.__name__.lower() == self.type]
      if subclass:
        self.__class__ = subclass[0]
      else:
        self.type = self.__class__.__name__.lower()
    

This will automatically downcast Sheep objects to the correct subclass, based upon the type field.

It also sets the type field on objects that are instantiated without one (based on the current instance class). This enables us to do things like:

# Fetch all Sheep, downcast to correct subclass.
>>> Sheep.objects.all()
[<Ram: Ram object>, <Ram: Ram object>, <Ewe: Ewe object>]

# Automatically set the type on a class.
>>> Ram()
<Ram: Ram object>
>>> Ram().type
'ram'
>>> Sheep()
<Sheep: Sheep object>
>>> Sheep().type
'sheep'

# Automatically set the class on a valid subclass/type
>>> Sheep(type='ram')
<Ram: Ram object>
# Force the type field on an invalid type argument. [see below]
>>> Ram(type='ewe')
<Ram: Ram object>
>>> Sheep(type='foo')
<Sheep: Sheep object>
>>> Sheep(type='foo').type
'sheep'

The assumption I have made here is that when instantiating a class, and the type value is not a valid value (our class, or one of our subclasses), then it changes the type field to the current class.

The other assumption is that the parent class is also valid. In this case, it wouldn’t be, as sheep must be either ewes or rams (or wethers, but that’s another story).

We also need to be able to fetch Ewe and Ram objects using their manager. This is just as simple as filtering on the type.

class ProxyManager(models.Manager):
  def get_query_set(self): # Note: get_queryset in Django1.6+
    return super(ProxyManager, self).get_query_set().filter(type=self.model.__name__.lower())

class Ram(Sheep):
  objects = ProxyManager()
  class Meta:
    proxy = True

class Ewe(Sheep):
  objects = ProxyManager()
  class Meta:
    proxy = True

Now, we can do:

>>> Ram.objects.all()
[<Ram: Ram object>, <Ram: Ram object>]

Clearly, the models have been simplified: I have not shown any model methods that would be the different behaviours that the subclasses have.

The latest thing I have been working on is notifications for our project. One of the required notification types is upcoming (and today’s) birthdays (and expiring work visas, but that’s a much easier problem).

This actually turns out to be quite a hard problem. There are some simple solutions, but they all do not meet our requirements:

1. Store only the month and day of a person’s birthday. This is unsatisfactory as we use their age to calculate their wage, if applicable.
2. Create a pseudo-column that contains their upcoming birthday. This gets hard when you take leap-day birthdays into account.

We need to fetch all people who have a birthday coming up in the next X days. This is a requirement because if we just matched people who had a birthday in X days, (a) leap days are easy to miss, and (b) changes to either the query period or a person’s birthday could mean some events were missed.

Instead, we will query for birthdays in a range, and see if we have already sent a notification for this instance of their birthday. If not, we will send a notification.

One solution is to look at all of the dates in the given range, in the format -%m-%d, and query using contains against this list:

dates = [start+datetime.timedelta(i) for i in range((finish-start).days)]
filters = [Q(dob__contains=x.strftime('-%m-%d')) for x in dates]
Person.objects.filter(reduce(operator.or_, filters))

But, this too fails when a birthday on a leap day exists, and this year is not a leap year.

(We use the -%m-%d format instead of %m-%d so we don’t get false matches from the year part of the date).

Then I came across a post by Zoltán Böszörményi, that contains the following useful function:

CREATE OR REPLACE FUNCTION indexable_month_day(date) RETURNS TEXT as $BODY$
  SELECT to_char($1, 'MM-DD');
$BODY$ language 'sql' IMMUTABLE STRICT;

There are a couple of things to notice: it does MM-DD, not the other way around. This allows us to sort lexically. Also, declaring it as IMMUTABLE means we will be able to create an index using it. And since we are querying against it, having an index may be useful:

CREATE INDEX person_birthday_idx ON people (indexable_month_day(dob));

Now, we can also query against this. I like to use django queryset methods (see building a higher-level query API), so my stuff looks like:

class BirthdayQuerySetMixin(object):
    def birthday_between(self, start, finish):
        assert start <= finish, "Start must be less than or equal to finish"
        start = start - datetime.timedelta(1)
        finish = finish + datetime.timedelta(1)
        return self.extra(where=[
            """
            indexable_month_day(dob) < '%(finish)s' 
            %(andor)s 
            indexable_month_day(dob) > '%(start)s'
            """ % {
                'start': start.strftime('%m-%d'),
                'finish': finish.strftime('%m-%d'),
                'andor': 'and' if start.year == finish.year else 'or'
            }
        ])
      
    def birthday_on(self, date):
        return self.birthday_between(date, date)

This has a caveat: it returns two matches for leap-day birthdays during non-leap-years. This is intentional, as other logic will prevent duplicate notifications, and we don’t know which offsetting method people will prefer.

The logic behind it is that it offsets the start and the finish by one day each, and then filters using less_than and greater_than. This is what allows us to find leap-day birthdays. The other tricky part is using AND when the years of the start and finish are the same, and OR if the finish is in the next year. This allows it to match over year boundaries.

Oh, there should also be a check to ensure that we have less than a full year between start and finish: if it’s a year or more, we can just return everyone!

Otherwise, it’s all good, and we can use it to filter a queryset. I’ve put those methods in my PersonManager and PersonQuerySet (via PassThroughManager), so I can do things like:

>>> today = datetime.date.today()
>>> Person.objects.birthday_between(today, today + datetime.timedelta(7))

… which provides me with a list of people who have a birthday within the next seven days.

We have an interesting set of requirements regarding session timeouts.

Our application is currently split into two parts: the newer stuff runs in a browser, but the older parts consume a JSON api, and are part of a native app. We recently stopped using HTTP Basic authentication, and instead use session-based authentication in both places. This was handy, as it allows us to:

1. Not store the user’s password, even in memory on the local machine.
2. Automatically have the user logged in when the native client links to an HTML page (by passing the session id through).

This is all well and good, but we have discovered a slight possible issue.

1. User logs in to native client.
2. User clicks on a button that loads a page in the browser (logging them in automatically).
3. User closes browser window, but does not quit browser.
4. Native client does not cleanly exit, and logout code is not called.

This means that the browser session is still logged in, even though the user would have no idea of this. This is a very bad thing(tm), as the next person to use the computer could have access to all of the previous user’s data.

So, we need the following to happen:

• Logging out of the client logs out all of the linked (same session id) browser instances.
• Closing a given browser window does not log out the session (the client may still be open, or there may be other linked browser windows).
• When no requests are receieved within a given time period, the session expires.

So, we need a short session expiry time, but this should refresh every time a request occurs. The browser pages fetch notifications every 30 seconds, but the native client will also need to ping the server with some frequency for this to work.

This is somewhat different to the way django-session-security works. However, this does add a feature that may also be useful: if no user input is receieved on a given page within a timeout period, the session should expire. However, this may be hard to manage, as no activity may occur on one page, but another page may be getting lots of activity. For now, we might leave this out as a requirement.

It turns out Django can do everything that is required, out of the box. All you need to do is configure it correctly:

# settings.py

SESSION_SAVE_EVERY_REQUEST = True
SESSION_COOKIE_AGE = 60

The key is to understanding that the session expire time is only refreshed if the session is saved. Most requests will not save this (my fetch of unread notifications doesn’t for instance), so after the expiry time, the session would expire, even if requests had been made in the meantime.

HTML forms contain a construct called a fieldset. These are generally used to segment a form: splitting a form into groups of fields that are logically grouped. Each fieldset may also have a legend.

Django’s forms have no concept of a fieldset natively, but with a bit of patching, we can make every django form capable of rendering itself using fieldsets, yet still be backwards compatible with non-fieldset-aware templates.

Ideally, we would like to be able to render a form in a way similar to:

<form>
  {% for fieldset in form.fieldsets %}
  <fieldset>
    <legend>{{ fieldset.title }}</legend>
    
    <ul>
      {% for field in fieldset %}
        <li>
          {{ field.label_tag }}
          {{ field }}
          {{ field.help_text }}
          {{ field.errors }}
        </li>
      {% endfor %}
    </ul>
  </fieldset>
  {% endfor %}
  
  <!-- submit button -->
</form>

And, it would make sense to be able to declare a form’s fieldsets in a manner such as:

class MyForm(forms.Form):
  field1 = forms.BooleanField(required=False)
  field2 = forms.CharField()
  
  class Meta:
    fieldsets = (
      ('Fieldset title', {
        'fields': ('field1', 'field2')
      }),
    )

This is similar to how fieldsets are declared in the django admin.

We can’t just simply create a subclass of forms.Form, and do everything there, as the metaclass stuff doesn’t work correctly. Instead, we need to duck-punch.

First, we want to redefine the metaclass __init__ method, so it will accept the fieldsets attribute.

from django import forms
from django.forms.models import ModelFormOptions

_old_init = ModelFormOptions.__init__

def _new_init(self, options=None):
  _old_init(self, options)
  self.fieldsets = getattr(options, 'fieldsets', None)

ModelFormOptions.__init__ = _new_init

Next, we will need a Fieldset class:

class Fieldset(object):
  def __init__(self, form, title, fields, classes):
    self.form = form
    self.title = title
    self.fields = fields
    self.classes = classes
  
  def __iter__(self):
    # Similar to how a form can iterate through it's fields...
    for field in self.fields:
      yield field

And finally, we need to give every form a fieldsets method, which will yield each fieldset, as a Fieldset defined above:

def fieldsets(self):
  meta = getattr(self, '_meta', None)
  if not meta:
    meta = getattr(self, 'Meta', None)
  
  if not meta or not meta.fieldsets:
    return
  
  for name, data in meta.fieldsets:
    yield Fieldset(
      form=self,
      title=name,
      fields=(self[f] for f in data.get('fields',(,))),
      classes=data.get('classes', '')
    )

forms.BaseForm.fieldsets = fieldsets

I am using this code (or something very similar to it), in projects. It works for me, but your mileage may vary…

Finite State Machines (fsm) are a great way to model something that has, well, a finite number of known states. You can easily specify the different states, and the transitions between them.

Some time ago, I came across a great way of doing this in python: Dynamic State Machines. This maps well onto an idea I have been toying with lately, replacing a series of linked models representing different phases in a process with one model type. Initially, I had thought to just use a type flag, but actually changing the class seems like a better idea.

One aspect of django’s models that makes it easy to do this is the concept of a Proxy Model. These are models that share the database table, but have different class definitions. However, usually a model instance will be of the type that was used to fetch it:

class ModelOne(models.Model):
  field = models.CharField()
  
class ModelOneProxy(ModelOne):
  class Meta:
    proxy = True

ModelOneProxy.objects.get(pk=1) # Returns a ModelOneProxy object.
ModelOne.objects.all() # Returns all ModelOne objects.

However, by using a type field, we can, at the time it is fetched from the database, turn it into the correct type.

class StateMachineModel(models.Model):
  status = models.CharField(max_length=64)
  
  def __init__(self, *args, **kwargs):
    super(StateMachineModel, self).__init__(*args, **kwargs)
    self.__class__ = class_mapping[self.status]

However, having to store a registry of status : <ProxyModelClass> objects is not much fun.

Enter __subclasses__.

  @property
  def _get_states(self):
    """
    Get a mapping of {status: SubClass, ...}
    
    The status key will be the name of the SubClass, with the
    name of the superclass stripped out.
    
    It is intended that you prefix your subclasses with a meaningful
    name, that will be used as the status value.
    """
    return dict([
      (
        sub.__name__.lower().replace(self.__class__.__name__, ''),
        sub
      ) for sub in self.__class__.__subclasses__()
    ])
  
  # in __init__, above, replace the last line with:
    self.__class__ = self._get_states[self.status]

Now, we need to change the underlying class when the type gets changed

  def __setattr__(self, attr, value):
    if attr == 'status':
      states = self._get_states
      if value in states:
        self.__class__ = states[value]
    return super(StateMachineModel, self).__setattr__(attr, value)

As the docstring on _get_states indicates, it looks at the subclass name, and compares it to the superclass name to work out the values that will be stored as the status (and used to dynamically change the class).

This has a fairly large implication: you cannot fetch database objects of any of the subclass types directly: you would need to:

SuperClass.objects.filter(status="prefix")

Of course, you could use queryset methods to do this: that’s what I have been doing.

This is still a bit of a work in progress: it’s not well tested, but is an interesting idea.

The full version of this model class, which is slightly different to above:

from django.db import models

class StateMachineModel(models.Model):
    status = models.CharField(max_length=64)
    
    class Meta:
        abstract = True
    
    def __init__(self, *args, **kwargs):
        self._states = dict([
            (sub.__name__.replace(self.__class__.__name__, '').lower(), sub)
            for sub in self.__class__.__subclasses__()
        ])
        super(StateMachineModel, self).__init__(*args, **kwargs)
        self._meta.get_field_by_name('status')[0]._choices = [(x, x) for x in self._states.keys()]
        self._set_state()
            
    def _set_state(self):
        if getattr(self, 'status', None) in self._states:
            self.__class__ = self._states[self.status]
    
    def __setattr__(self, attr, value):
        if attr == 'status':
            self._set_state()
        return super(StateMachineModel, self).__setattr__(attr, value)

I have a recurring pattern I’m seeing, where I have a field in a model that needs to be read-only. It usually is a Company to which an object belongs, but it also occurs in the case where an object belongs to some collection, and isn’t permitted to be moved to a different collection.

Whilst there are some workarounds that apply the field’s value to the instance after creating, it’s nicer to be able to apply the read-only nature declaratively, and not have to remember to do something in the form itself.

Unfortunately, in django, normal field subclasses don’t have access to the initial argument that was used to construct it. But forms.FileField objects do. So we can abuse that a little.

We also need a widget, that will always return False for questions about if the value has been changed, and re-render with the initial value at all times.

from django import forms

class ReadOnlyWidget(forms.HiddenInput):
    def render(self, name, value, attrs):
      value = getattr(self, 'initial', value)
      return super(ReadOnlyWidget, self).render(name, value, attrs)
    
    def _has_changed(self, initial, data):
      return False

class ReadOnlyField(forms.FileField):
  widget = forms.HiddenInput
  
  def __init__(self, *args, **kwargs):
    forms.Field.__init__(self, *args, **kwargs)
  
  def clean(self, value, initial):
    self.widget.initial = initial
    return initial

So, that’s all well and good. But a common use for me was for this field to be a related field: a Company as described above, or a User.

Enter ReadOnlyModelField, and ReadOnlyUserField.

Now, ReadOnlyModelField is a bit tricky: it’s not actually a class, but a factory function, so we will look at ReadOnlyUserField first:

class ReadOnlyUserField(ReadOnlyField):
  def clean(self, value, initial):
    initial = super(ReadOnlyUserField, self).clean(value, initial)
    return User.objects.get(pk=initial)

Note, it will have a database query.

Now, we are ready to look at a more general case:

def ReadOnlyModelField(ModelClass, *args, **kwargs):
  class ReadOnlyModelField(ReadOnlyField):
    def clean(self, value, initial):
      initial = super(ReadOnlyModelField, self).clean(value, initial)
      return ModelClass.objects.get(pk=initial)
  return ReadOnlyModelField(*args, **kwargs)

This is a bit tricky. We create a function that looks like a class, but actually creates a new class when it is called. This is so we can use it in a form definition:

class MyForm(forms.ModelForm):
  company = ReadOnlyModelField(Company)
  
  class Meta:
    model = MyModel

I think the more Django code I write, the more I like one particular feature.

Forms.

Simple as that. Forms are the reason I keep coming back to django, and discard other web frameworks in other languages, even though I really want to try them.

One pattern I have been using a fair bit, which was touched on in another post, is using AJAX to handle form submission, and displaying the response.

Before we continue, a quick recap on what Django’s forms offer us.

• A declarative approach to defining the fields a form has, including validation functions.
• Will render themselves to HTML input elements, as appropriate.
• Handle validation of incoming form-encoded (or otherwise provided) data.
• Fields can validate themselves, and can include validation error messages as part of the HTML output.
• (Model forms) handle instantiation of and updating of model instances.

A normal form-submission cycle contains a POST or GET request to the server, which responds with a fresh HTML page, which the browser renders. The normal pattern for successful POST requests is to redirect to a GET afterwards, to prevent duplicate submission of forms.

By doing an ajax request instead of a full-page request means we can:

• reduce the amount of data that is sent back from the server
• improve apparent performance by only re-rendering the relevant data
• reduce the amount of time spent rendering parts of the page that have not changed, such as menu, etc.

The way I have been doing it, in broad terms, is to have a template just for the form. If the request is an ajax request, then this will be rendered and returned. If it’s not an ajax request, then the full page will be returned.

Some example code, for one way to do this:

def view(request, pk):
  instance = MyModel.objects.get(pk=pk)
  
  if request.is_ajax():
    template = 'form.html'
  else:
    template = 'page.html'
  
  
  if request.method == 'POST':
    form = MyForm(request.POST, instance=instance)
    if form.is_valid():
      form.save()
      if not request.is_ajax():
        return redirect('redirect-name-here')
  else:
    form = MyForm(instance=instance)
  
  return render(request, template, {'form': form})

Our template files. page.html:

{% extends 'base.html' %}

{% block main %}
  {% include 'form.html' %}
{% endblock %}

{% block script %}
{# Assumes jQuery is loaded... #}
{# This should be in a seperate script file #}
<script>
$(document).on('submit', 'form.dynamic-form', function(form) {
  var $form = $(form);
  $.ajax({
    type: form.method,
    url: form.action,
    data: $form.serialize(),
    success: function(data) {
      $form.replace(data);
    }
  });
});
</script>
{% endblock %}

And form.html:

<form action="/path/to/url/" method="POST" class="dynamic-form">
  {% csrf_token %}
  {{ form }}
  <button type="input">Submit</button>
</form>

Obviously, this is a fairly cut-down example, but it gets the message across.

One thing I dislike in general about django is that failed form submissions are returned with a status code of 200: personally I think a 409 is more appropriate in most cases, but returning a 200 actually means this code is simpler.

Until very recently, I was very happy with django-compressor. It does a great job of combining and minifying static media files, specifically JavaScript and CSS files. It will manage compilation, allowing you to use, for example, SASS and CoffeeScript. Not that I do.

But, for me, the best part was the cache invalidation. By combining JavaScript (or CSS) into files that get named according to a hash of their contents, it’s trivial for clients to not have an old cached JS or CSS file.

However, recently I have begun using RequireJS. This enables me to declare dependencies, and greatly simplify the various pages within my site that use specific JavaScript modules. But this does not play so well with django-compressor. The problem lies with the fact that there is no real way to tell RequireJS that “instead of js/file.js, it should use js/file.123ABC.js”, where 123ABC is determined by the static files caching storage. RequireJS will do optimisation, and this includes combining files, but that’s not exactly what I want. I could create a built script for each page that has a require() call in it, but that would mean jQuery etc get downloaded seperately for each different script.

I have tried using django-require, but using the {% require_module %} tag fails spectacularly (with a SuspicousOperation exception). And even then, the files that get required by a dependency hierarchy do not have the relevant version string.

That is, it seems that the only way to get the version numbering is to use django’s templating system over each of the javascript files.

There appear to be two options.

** List every static file in require.config({paths: ...}). **

This could be manually done, but may be possible to rewrite a config.js file, as we do have access to all of the processed files as part of the collectstatic process.

Basically, you need to use {% static 'js/file.js' %}, but strip off the trailing .js.

** Rewrite the static files. **

Since we are uglifying the files anyway, we could look at each require([...], function(){ ... }) call, and replace the required modules. I think this would actually be more work, as you would need to reprocess every file.

So, the former looks like the solution. django-require goes close, but, as mentioned, doesn’t quite get there.

I’ve spent a not insignificant amount of time working on a deployment function for within my fabfile.py (the configuration file used by Fabric). It’s well worth the investment, as being able to deploy with a single command (potentially to many servers) is not only faster, but much less prone to human error.

Currently, I’m using Mercurial as my source control. I’m also using it for deployment, but I’d like to get away from that.

My deployment process looks something like this:

1. Ensure the local repository has no uncommitted changes.
2. Ensure the requirements.txt file is exactly the same as the output from pip freeze.
3. Copy our public key to the remote server, for the user www-data, if it is not already installed there.
4. Create a virtualenv in the desired location on the server, if there is not one already there.
5. Ensure mercurial is installed on the server.
6. Push the local repository to the remote server. This will include any subrepositories. I do a little bit of fancy magic to ensure the remote subrepositories exist.
7. Update the remote server’s repository to the same revision as we are at locally. This means we don’t necessarily need to always deploy to tip.
8. Install the dependencies on the remote server.
9. Run some django management commands to ensure everything is setup correctly.
   • collect static files
   • sync the database
   • run migrations
   • ensure permissions are correct
   • compress static files
10. Restart the various services that need to be restarted.

This process is based around requirements files for a very good reason. pip is very good at recognising which packages are already installed, and not reinstalling them if the version requirements are met. I use pip freeze > requirements.txt to ensure that what will be deployed matches exactly with what I have been developing (and testing) against.

However, this process has some issues.

• Files must be committed to SCM before they can be deployed. This is fine for deployment to production, but is annoying for deploying to test servers. I have plenty of commits that turn on some extra debugging, and then a commit or two later, I turn it off.
• I have some packages that I have installed locally using pip install -e /path/to/package. To deploy these, I need to:
  1. Uninstall the editable installation.
  2. Package up a new version of the app.
  3. Push the package to my package repository (I use localshop).
  4. Install the package from the package repository.
  5. Run pip freeze > requirements.txt.
  6. Commit the changes.
  7. Deploy to the test server.
• Then, I usually need to develop further, so I reinstall using pip install -e ....

Today, I finally got around to spending some time looking at how pip can help improve this workflow.

With pip==1.3.1, we have a command that was not in pip==1.1, which was what I had been using until now. pip bundle.

My ‘deploy-to-development/test’ process now looks something like:

1. Get a list of packages installed as editable: pip list -e
2. Create a bundle, without dependencies, of these packages.
3. Get a list of all packages, other than those installed as editable: pip freeze | grep -v "^-e".
4. Ensure the server is set up (virtualenv, etc)
5. Push the local repository to the remote server.
6. Upload the bundle and requirements files.
7. Install from the requirements file on the server.
8. Force install from the bundle file on the server, without dependencies.
9. Repeat the post-installation stuff from above.

Some of this I’m never going to be able to avoid: ensuring we have the virtualenv, and the post-installation stuff. Migrations gotta migrate. However, I would like to move away from the pushing of the local repository.

My plan: turn my project into a package (complete with setup.py), so that it becomes just another entry in the requirements file. It will be editable, which means it will be bundled up for deployment.

However, it will mean I can get away from having the nested repositories that I currently have. Ultimately, I plan to be able to:

1. Build a bundle of editable packages.
2. Create a requirements file of non-editable packages.
3. Upload both of these files to the server.
4. Install the requirements.
5. Install the bundle.
6. Run the post installation tasks.

That would be bliss.

TL;DR : I made a thing: django-multi-schema. As pointed out in the comments, it’s now known as django-boardinghouse.

Software, as a Service (SaaS)

This is a term that has been around for a while. Basically, providing software that runs on a server, and selling access to that system. Instead of charging people for the software, you charge them a recurring fee for access to the hosted service.

If you have more than one customer, then you need to ensure that each customer can only access data that is ’theirs’, rather than seeing everything. You need some way to partition the data, and there are two main ways to do this.

1. Every customer has their own server/database.
2. All the data is stored in one server/database.

To each their own.

One way of partitioning data is to provision an application server, and a database for each customer.

These web servers, and indeed the databases may not even be on different virtual machines, let alone physical machines. I manage a legacy PHP/MySQL application that runs each ‘server’ as an Apache VirtualHost, shares the codebase, and uses some configuration to route the database connections.

The advantages of this type of system is that it is very easy to move a single instance of the application onto a seperate server, if limits of server performance are reached. Depending upon the configuration, you still only need to upgrade one codebase, although typically there would be a seperate code installation per customer. The advantages of that is that you can upgrade customers individually, perhaps moving specific customers to a beta version.

The disadvantages of this type of setup are similar to what you would have if you had a big enough customer base anyway: multiple servers need to be upgraded, although getting them all done at exactly the same time is not as important if all requests to a given domain always go to a given server (and then to a given database).

However, if you are sharing code between installations, and each has a seperate database, then you do need to migrate all of the databases at the same time, and at the same time as the codebase is updated.

The real disadvantages are about adding a new customer. You need to provision a new server, or at the least, setup a new VirtualHost, and create a new database. You also need to run any database migrations on several databases, but that should be part of the deployment process to each installation anyway.

Another issue that my arise is that each installation requires a seperate set of connections to its own database. If the databases are on the same database server, then this may be a limit that you reach sooner than you would like: but at that time you can just split off some databases to a seperate database server.

There may be only one.

The alternative method of segmenting data is to use Foreign Keys. There is one database table, corresponding to a customer (which may not be a single user). Then, relevant other tables contain a foreign key back to that table, or to a table that links back to that table, or so on.

This is the way my main system I work on works. We have one django installation (or, possibly, several installations that share one database, but are effectively identical clones, just used for load handling). We have a Company table, and everything that should be limited to a given company links back to that, either directly or through a parent. For instance, a RosteredShift does not have a link to a company, but it does link to a RosterUnit, which is linked to a company.

The advantages of this are that you have a single server that needs to be upgraded, or multiple identical servers (that you can just upgrade in parallel). You have a single database, that, again, only needs to be upgraded once. You only have to manage a single database backup, and, importantly, your database connections are equally shared across all of your customers. Indeed, your load is evenly shared, too.

Scaling up is still possible: we can easily stick an extra N app servers into our app server pool, and the load balancer will just farm requests off to that. Sharding databases becomes a bit harder, as you cannot just shift a single customer’s data off onto a seperate database (or indeed push a highly used customer’s app server onto a seperate machine).

The big danger is that a customer may get access to data that belongs to a different customer. In some ways this is the same issue as to within a customer’s users some users seeing data they shouldn’t, but is a bit scarier.

With a single server, and a single domain name, usernames must be unique across all of your customers’ users. This sounds easy: just use email addresses. They are unique. That works well, until an employee of one customer moves to a different employer, that also happens to be your customer, and BANG, email conflict. You don’t want to just move the user, as that would break history for the previous employer. Indeed, they may even be employed by both of your customers at the same time. Possibly without those employers knowing about one another. Privacy, man.

Another useful thing is being able to shift data to a different customer. This is a bit of a double-edged sword - in reality we stopped doing this, and now create a copy of the relevant data for the new customer (for instance, when a Subway store changes hands). That means the previous owner can retain access to their historical data.

Finally, support staff only need to look in one place to see all customer data. They don’t need to extract from a frazzled user who they work for in order to check why their login is not working correctly, for instance.

A middle ground

One of the advantages of a single server is that you share database connections. No more running out of connections because each customer requires X connections. But, Postgres has a feature called schemas, that sits between database and table:

<database>.<schema>.<table>.<column>

Any query can use a fully qualified, or partially qualified name to refer to a database, schema, table or column.

Postgres uses the term schema differently to the idea of an SQL schema. Instead of meaning the table definitions, a schema is a named sub-database. Every postgres database has at least one schema, usually called public.

Postgres determines which schema should be searched for a matching table using a search_path. This can be a list of schemata: the first one in the list with a matching table will be used for queries against that table name. The default search path is "$user","public", which looks in the schema matching the connected user, then the public schema. A schema that does not exist is just ignored in the search path.

So, we can split our data into one schema per customer. That has the nice side effect of preventing data leakage due to programmer error, as a connection to the database (which has a specific search path at that time), cannot possibly return data to which it should not have access.

Starting to narrow

I work in Django, so from here on in, it’s starts to get rather specific to that framework.

Which schema, when?

One solution to this problem is to take the approach mirroring the ‘one server per customer’ approach. That is, each customer gets a seperate domain. Requests coming in are matched against the domain name that was used in the request, and then set the search path. This is quite simple, and is how django-schemata works. Some middleware matches up the incoming domain to the relevant schema settings, and sets the search path. Indeed, this is the simplest possible approach, as was intended. The schemata are set in the django settings file, which means you cannot create a new schema on the fly. django-appschema does allow this, by using a model in the public schema that contains the list of schema.

This is not the approach I want, as I want everything to come in on the same domain.

So, I came up with a different concept. Base the schema selection upon the logged in user.

When a request comes in, use a lookup table of some sort to determine which schema should be used. Now, since this needs to happen after authentication, we will still need to store auth_user and friends in the public schema. But that’s alright. We can have a related object that determines which schema should be used for looking up the rest of the data, and ensure that those requests which should be schema-aware use that schema.

Indeed, it may be possible for a user to be able to choose between schemata, so we also need a way to pass this information. I settled on storing the desired schema in request.session, and the middleware checks that the authenticated user is allowed to access that schema.

That was the easy part

Working out what the search path should be is indeed the easy part. Set it at the start of the request (our middleware should be early in the chain), and away you go.

The hard problems are avoided by django-schemata because each ‘site’ has it’s own schema, and all of it’s tables are stored in there. Thus, you can simply run ./manage.py syncdb or ./manage.py migrate for each schema, and away you go. They provide a manage_schemata tool, which does just this. They also avoid shared apps, to simplify things.

I needed to be able to share models: indeed, by default, a model is shared. They live in the public schema, and queries will be on this schema. The approach I used was that, instead of using the SHARED_APPS, you need to explicitly mark a Model as “schema aware”.

The only time this really matters is at DDL time. When you query a table, Postgres looks in the search path. As long as you have schema_name,public, you will be fine for all reads and writes. However, to create the tables, you need to use some smarts.

syncdb

Whenever a syncdb happens, we need to do a few things:

• Make sure we have our clone_schema SQL function loaded. This will be used to clone the schema structure from __template__ to new schemata.
• Make sure we have a schema with the name __template__.
• Set the search path: in this case it will be public,__template__. Usually, it will be the other way around, but for this case, we want tables to be created in public, unless we explicitly mark them to be created in __template__.
• Run the syncdb command.
• Create schemata for any Schema objects we have in the database.

The syncdb command just runs the normal old syncdb (or south’s, if that is installed). However, we do have a couple of bits of evil, evil magic.

Firstly, we don’t use the standard postgres database backend. Instead, we have one that subtly changes the DatabaseCreation.sql_create_model process. If a model is schema aware, we inject into the CREATE TABLE commands the schema. Thus:

CREATE TABLE "foo" ...;

Becomes:

CREATE TABLE "__template__"."foo" ...;

In fact, it’s a little more than that. We can pass in the name of the schema we are writing to, so, in our second trick, we add a post_syncdb listener, which iterates through all of the schemata, re-running just this command, with the schema passed in.

loaddata

An override of the loaddata command adds the ability to declare the schema: the search path is then set before running the command. Simple.

dumpdata

The override for this command also allows passing in the schema name. Data in schema aware models will only be fetched from here (or the template schema, if nothing is passed in, which should be empty).

Migrations

Unless you are crazy, you probably already use South for migrations. So, the second really hard problem is how to apply the migrations to each schema.

Basically, we want to run each operation, if the model is schema aware, on the template schema, and then on each real schema. But, we can’t just run the migrations multiple times with the search path altered, because (a) South stores it’s migration record in a table in public, so the subsequent runs of the migration would not do anything, and (b) even if we could run them, any operations on the public schema would fail, as they have already been performed.

This looks like a really hard problem, and initially seemed insurmountable. However, there is one thing which makes it really quite simple. South looks in your settings file for SOUTH_DATABASE_ADAPTERS. I’m not sure this normally gets used, but it is required if you are not using a django database backend that South recognises. Like ours.

So, the database adapter is just a thin wrapper over South’s builtin postgres backend. It expects to find a class called DatabaseOperations, and wraps all of the methods that create/rename/delete or whatever tables and columns.

And the wrapper is quite simple. It finds the django model that matches the database table name, and then, if that model is schema aware, repeats the operation on each schema, and then the template schema. If the model is not schema aware, then we set the search path just to the public schema, so create operations will affect that.

More hackery

Admin users are permitted to see data from all schemata, so it’s possible they’ll see a link to an object that is not in their schema. Primary keys are unique across all schemata (but this is simply because the index is shared between them in this implementation), so they’ll get a 404 if they try to follow it. If PKs were not unique across schemata, then they might see the wrong object. Anyway, we can leverage the schema-switching middleware by passing in the correct schema in the URL. But to do this, we need to know at LogEntry creation time which schema is active, if the object is schema aware. So, I monkey-patch LogEntry to store this information, and generate URLs that include it.

I’ve also done a bit of work on adding the schema in when you serialise data. This is mainly for dumpdata, as I don’t think you should be passing around objects for deserialisation to untrusted sources: it really should be going through form validation or similar. But for loaddata/dumpdata, it might be useful. At this stage the schema value is not used, but eventually the deserialiser should look at that, and somehow ensure the object is created in the correct schema. For now, just use loaddata --schema .... That’s better anyway.

ImproperlyConfigured

One really nice thing about django is that it has an ImproperlyConfigured exception, which I have leveraged. If the database engine(s), south database adapter(s) or middleware are missing or incorrect, it refuses to load. This is conservative: you may have a database of a different engine type, or have no schema aware models, but for now it’s not a bad idea.

Also, if South is not installed before us, we need to bail out.

Well, that’s most of it. There’s some more gravy (signals before and after activation of schema, as well as when a new schema is created), but while it has been tested, there is no automated test suite as yet. Nor is there a public example project. But they are coming.

Oh, and it’s up on BitBucket: django-multi-schema. Although, I’m not that happy with the name.