Django

###Custom Queries

Although django's database querySets are very powerful and most of the time remove the need for writing SQL queries yourself, it happens that you sometimes do have to write your own for efficiency reasons (most often to limit the number of queries sent to the database engine).

One technique I have found useful in such case is the following (from geneapro/models/__init__.py):

        self._p2p_query = \
           "SELECT %(p2p_assert.subj1)s FROM %(p2p_assert)s, %(assert)s" + \
           " WHERE %(p2p_assert.subj2)s=%(persona.id)s" + \
           " AND %(assert.pk)s=%(p2p_assert.pk)s" + \
           " AND %(assert.value)s='%%s' LIMIT 1"
        self._p2p_query = self._p2p_query % all_fields
        self._event_query = self._event_query % all_fields

     Persona.objects.extra (select={
           'father_id': self._p2p_query % ("father of",)})

Where all_fields is declared as such

def sql_table_name (cls):
    return connection.ops.quote_name (cls._meta.db_table)

def sql_field_name (cls, field_name):
    """Help write custom SQL queries"""
    if field_name == "pk":
       f = cls._meta.pk
    else:
       f = cls._meta.get_field (field_name)
    return "%s.%s" % (
       sql_table_name (cls), connection.ops.quote_name (f.column))

all_fields = {
   'assert':         sql_table_name (Assertion),
   'p2p_assert':     sql_table_name (P2P_Assertion),
   'assert.pk':      sql_field_name (Assertion, "pk"),
   'p2p_assert.pk':  sql_field_name (P2P_Assertion, "pk"),
   'p2p_assert.subj2': sql_field_name (P2P_Assertion, "subject2"),
   'p2p_assert.subj1': sql_field_name (P2P_Assertion, "subject1"),
   'persona.id'    : sql_field_name (Persona, "pk"),
   'assert.value'  : sql_field_name (Assertion, "value"),
}

So the trick here is to use a global dict, initialized once when the application starts. wThis array hides the actual name of the fields in the database, and keeps the logical name of the model instead. If a table or a field is removed or renamed, the definition of all_fields becomes invalid and the application does not even start.

Using a dict with your own names also makes the custom query more readable (using %(assert)s instead of %s for instance).

###Automatic fields

One of the data that I often need to store in my database are dates (quite obviously for genealogical software!) However, it is in general recommended to store dates exactly as they are found in the documents, which of course makes them harder to reuse programmatically. The solution I am using is to have two date fields in each table: one contains a TEXT of the date as found in the document, and the second one a DateTimeField which represents the result of parsing the former. That second date is in general used for sorting.

It would be tedious to add the two fields every time, so instead I created a new field type. This automatically adds a second field to the table, and whenever the data is stored into the database the second field is also updated.

class PartialDateField (models.CharField):
    __metaclass__ = models.SubfieldBase

    def __init__ (self, max_length=0, null=True, *args, **kwargs):
        kwargs["null"]=null
        super (PartialDateField, self).__init__ (
           self, max_length=100, *args, **kwargs)

    def contribute_to_class (self, cls, name):
        sortfield = models.DateTimeField ('used to sort', null=True)
        self._sortfield = name + "_sort"
        cls.add_to_class (self._sortfield, sortfield)
        super (PartialDateField, self).contribute_to_class (cls, name)

    def pre_save(self, model_instance, add):
        val = super (PartialDateField, self).pre_save (model_instance, add)
        if val:
           sort = date.DateRange (val).sort_date ()
           setattr (model_instance, self._sortfield, sort)
        return val