Login

Simple solution for model schema evolution / database changelog

Author:
kahless
Posted:
June 16, 2007
Language:
Python
Version:
.96
Score:
5 (after 5 ratings)

This is a very simple approach to "schema evolution" (Not sure if you can even call it so) - I use it for my project: SCT and it seems to work quite nicely.

The idea is, that if you add or change your models, you add a 'changelog' attribute to your class which defines the SQL queries required to update your models. Of course this only works for one database type...

An example 'syncdb' call if a new changelog entry was detected:

kahless@localhost ~/dev/python-new/sphenecommunity/sphenecommunity $ ./manage.py syncdb
Executing module body.
2007-06-16 00: SQL Statement: ALTER TABLE "sphboard_post" ADD markup varchar(250) NULL
Detected changes - Do you want to execute SQL Statements ? (yes,no):

So if the SQL statement won't work with the database the user is currently running, he at least knows exactly what he is expected to change in his models. and he can stop automatic execution by simply entering 'no' here.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
# management.py:

from django.dispatch import dispatcher
from django.db.models import signals, get_apps, get_models
from django.db import backend, connection, transaction
from sphene.community.models import ApplicationChangelog
from datetime import datetime

def do_changelog(app, created_models, verbosity, **kwargs):
    app_models = get_models( app )
    if app_models == None: return

    sql = ()
    for clazz in app_models:
        changelog = getattr(clazz, 'changelog', None)
        if not changelog: continue
        #changelog = get_changelog(None)

        version = None
        currentversion = changelog[-1][0]
        currentcl = ApplicationChangelog( app_label = clazz._meta.app_label,
                                          model = clazz._meta.object_name.lower(),
                                          version = currentversion,
                                          applied = datetime.today(), )
        try:
            appcl = ApplicationChangelog.objects.filter( app_label = clazz._meta.app_label,
                                                         model = clazz._meta.object_name.lower(), ).latest()
            version = appcl.version
            if currentversion == version:
                continue
        except ApplicationChangelog.DoesNotExist:
            # See if model was just created...
            if clazz in created_models:
                # Store latest version in changelog ...
                currentcl.save()
                continue # No need to do anything ...
            else:
                # We need to do the whole changelog ...
                version = None


        for change in changelog:
            date, changetype, stmt = change
            if version != None and version >= date:
                # This change was already applied ...
                continue

            if changetype == 'alter':
                sqlstmt = 'ALTER TABLE %s %s' % (backend.quote_name(clazz._meta.db_table), stmt)
                sql += (sqlstmt,)
                print "%s: SQL Statement: %s" % (date, sqlstmt)
            elif changetype == 'update':
                sqlstmt = 'UPDATE %s %s' % (backend.quote_name(clazz._meta.db_table), stmt)
                sql += (sqlstmt,)
                print "%s: SQL Statement: %s" % (date, sqlstmt)
            else:
                print "Unknown changetype: %s - %s" % (changetype, str(change))

        # Store new version ...
        currentcl.save()

    if len(sql) > 0:
        confirm = 'x'
        while confirm not in ('yes', 'no'):
            confirm = raw_input("Detected changes - Do you want to execute SQL Statements ? (yes,no): ")
        if confirm == 'yes':
            curs = connection.cursor()
            for sqlstmt in sql:
                curs.execute( sqlstmt )
            transaction.commit_unless_managed()
        else:
            print "Not updating database. You have to do this by hand !"


dispatcher.connect(do_changelog, signal=signals.post_syncdb)




# models.py

class ApplicationChangelog(models.Model):
        app_label = models.CharField(maxlength = 250)
        model = models.CharField(maxlength = 250)
        version = models.CharField(maxlength = 250)
        applied = models.DateTimeField()

        class Meta:
                get_latest_by = 'applied'



# Example changelog entries:
class Post(models.Model):
    # your model fields & co come here
    # ....

    # the changelog entry define the schema evolution over time
    # The list arguments are:
    #   1. A unique and incrementing ID of the changelog entry
    #   2. The type of statement ('alter' and 'update' supported)
    #   3. The SQL statement from AFTER the table name. the 'ALTER TABLE xxxx' or 'UPDATE xxxx' is autogenerated.
    # i usually use '<date> <incrementing number>' to identify each changelog entry
    # but since it uses simple string comparsion anything which simply counts whould be
    # sufficient.
    
    changelog = ( ( '2007-04-07 00', 'alter', 'ALTER author_id DROP NOT NULL', ),
                  ( '2007-06-16 00', 'alter', 'ADD markup varchar(250) NULL', ),
                  )

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 3 months, 1 week ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 3 months, 2 weeks ago
  3. Serializer factory with Django Rest Framework by julio 10 months, 2 weeks ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 11 months ago
  5. Help text hyperlinks by sa2812 12 months ago

Comments

thirteenpixels (on June 18, 2007):

When you say management.py which one did you mean to change?

#

kahless (on June 18, 2007):

just create a file called management.py in your application folder (beside models.py and view.py, etc.)

this will get executed by manger.py if you run syncdb for example.

btw. - you will also need to change the line: 

from sphene.community.models import ApplicationChangelog

to reflect the name of your application where you copied the model code to.

#

thirteenpixels (on June 18, 2007):

Thanks that worked like a champ! Just a heads up, if the query fails, then the entry still gets added to the application changelog, at least it did for me.

#

kahless (on June 18, 2007):

well .. this is kind of intended.. if the query fails, the user has to manually modify the database schema.. (the same is the case if he answers 'no' to the question if the queries should be executed..)

#

Please login first before commenting.