DB Schema

[Lukasa]

This is predominantly aimed at @peterbrittain, who is AFAIR responsible for the web interface.

I think we need to take another look at the DB schema, and see if it's suitable. This is particularly true now that the tornado webserver is going to be hitting it as well. The current schema is (stripped of irrelevant bits, in Django ORM style):

class CongaUser(models.Model):
    """Represents a single user in the system"""
    user = models.OneToOneField(User)
    mac = models.CharField(max_length=20)
    host = models.CharField(max_length=20)

class Conga(models.Model):
    """Represents a single conga"""
    name = models.CharField(max_length=80)
    password = models.CharField(max_length=80)
    owner = models.ForeignKey(User)

    class Meta(object):
        unique_together = ["name"]

class CongaMember(models.Model):
    """Represents a member of the conga"""
    conga = models.ForeignKey(Conga)
    member = models.ForeignKey(User)
    index = models.IntegerField()

class Message(models.Model):
    """Represents a message being transferred around a conga"""
    message = models.CharField(max_length=1024)
    conga = models.ForeignKey(Conga)

From a tornado perspective, there are a few things missing or unclear.

1. Conga participants are not ordered in this DB. This makes congas fundamentally unordered. Are we happy with that?
2. Right now I require conga users to identify themselves when they first connect, so I can correctly map them to a conga. However, it's not clear what their user ID should be: is it the ID of the CongaUser table or the user ID stored on the table rows in that table?
3. More importantly, there's no way to track which conga a user should actually be in. I think each CongaUser needs a ForeignKey field pointing to a Conga.

[peterbrittain]

@lukasa Good questions... Answers are as follows.

1. CongaMembers have an index field - the original owner of the conga always zero for now. As other members join the conga, they are assigned the next free index.
2. The CongaUser is an extension of the built-in Django User authentication (using one of the recommended ways of extending their schema). Effectively it is the extra stuff we need to know about users over and above what the Django framework needs to know. Either ID would work, but it's probably better for consistency to use the ID in the base table (i.e. the user field).
3. The CongaMember table is the one that tracks membership of congas. It wasn't clear to me that we wanted to allow users to be members of more than one conga or not. This schema gave more flexibility given the uncertainty. No reason we can't change it, though.

[Lukasa]

1. Ah, so we're using creation order? That's easy enough to enforce.
2. Base user ID is easy enough for me.
3. Mm, yes, I think I missed that because I'm used to the implicit creation of that field (using a Django ManyToManyField). We should discuss today to get a hard line on whether we allow users to be members of multiple congas or not so we can tie down the SQL.

[peterbrittain]

Current schematic is now covered in the wiki.