Simple solution for model schema evolution / database changelog

  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. Dynamic Models Revisited by Ben 6 years, 6 months ago
  2. model instance to sql insert statement by gsoltis 3 years, 4 months ago
  3. Binding pre-existing tables with dynamically created class models by rodsenra 6 years, 9 months ago
  4. Creating MySQL Alter table commands for Foreign Keys by vidyanand 5 years, 10 months ago
  5. Custom SQL via subquery by drg006 6 years, 11 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..)

#

(Forgotten your password?)