How to Handle Database Views in Django/South

Mon 14 November 2011 · code · Tags:

In a recent Django project I had to work with Postgres views quite extensively. The problem is that while South does a great job handling model related stuff, you’re on your own when handling views and other database level stuff. So I thought I’d share the simple/simplistic solution I came up with.

Hand written migrations

Since South doesn’t provide much help in writing views for you, you will write your migrations by hand. So let’s add a dandy new view to our database.

# migration 0001
# (…) imports and such

class Migration(SchemaMigration):

def forwards(self, orm):
    query = """
        CREATE VIEW fancy_view AS
        SELECT aCol, bCol
        FROM stuff
        ;
    """
    db.execute(query)

def backwards(self, orm):
    query = """
        DROP VIEW fancy_view;
    """
    db.execute(query)

# (…) loads of frozen models

Now that wasn’t too bad, right? Oh, but now we forgot all about cCol. No sweat, let’s just write another migration to fix that.

# migration 0002
# (…) imports and such

class Migration(SchemaMigration):

def forwards(self, orm):
    query = """
        DROP VIEW fancy_view;
    """
    db.execute(query)
    query = """
        DROP VIEW fancy_view;
        CREATE VIEW fancy_view AS
        SELECT aCol, bCol, cCol
        FROM stuff
        ;
    """
    db.execute(query)

def backwards(self, orm):
    query = """
        DROP VIEW fancy_view;
    """
    query = """
        CREATE VIEW fancy_view AS
        SELECT acol, bcol
        FROM stuff
        ;
    """
    db.execute(query)

# loads of frozen models (…)

Okay, that kinda sucks. Now we have to repeat the forward SQL part of migration 0001 in the backwards part of migration 0002.

What about dependent views?

And the above example didn’t mention views that depend on each other. Let’s say we have two views, fancy_view and depending_view. The drill would look something like this:

  1. Write migration 0001 for fancy_view.
  2. Find out you need depending_view which depends on fancy_view because it joins in some stuff from there.
  3. Write migration 0002 for depending_view.
  4. Find out you need to change something in fancy_view.
  5. Write migration 0003 which takes care of this:
    1. Drop fancy_view and all that depends on it.
    2. Recreate fancy_view with changes.
    3. Recreate depending_view.
    4. Does the same stuff migration 0002 does in forwards in backwards.

This is anything but simple anymore and as such prone to error. Sure you can do a lot with copy and paste but it’s really easy to make mistakes there. (Trust me on that one…)

Why can’t my computer do that for me?

Here’s what I came up with: I put my views in SQL files named after the migration they occur in. So for migration 0001_add_fancy_view.py there could be a 0001_fancy_view.sql and a 0001_other_fancy_view.sql.

So let’s assume the situation described above: In migration 0002 we added a view named depending_view that somehow depends on fancy_view which was introduced in migration 0001. Now in migration 0003 we want to change something in fancy_view. For that we have to drop fancy_view and everything that depends on it and build it up from the ground again.

This is what that migration could look like:

# 0003_change_fancy_view.py
# (…) imports and such

def run_file(file_name):
    import os
    f = open(os.path.join(os.path.dirname(__file__), file_name))
    query = f.read()
    db.execute(query)

class Migration(SchemaMigration):
    def forwards(self, orm):
        # get rid of fancy_view and all depending views
        db.execute("DROP VIEW fancy_view CASCADE;")
        # update fancy_view
        run_file('0003_fancy_view.sql')
        # re-add depending_view
        run_file('0002_depending_view.sql')

    def backwards(self, orm):
        # get rid of fancy_view and all depending views
        db.execute("DROP VIEW fancy_view CASCADE;")
        # re-add the old version of fancy view
        run_file('0001_fancy_view.sql')
        # re-add depending_view
        run_file('0002_depending_view.sql')

# (…) loads of frozen models

Much nicer, in my humble opinion. We let the computer handle most of the boring parts and concentrate only on the order that things should be handled in. Sure there’s loads of room for improvement but it gets the job done.

Want to talk about it?

Photo of Christian Kaula

I make stuff simple.
Code, interfaces, things.
You can hire me.

Christian Kaula · Thieraufstrasse 2 · 95028 Hof · Germany
Xing · Twitter · XMPP · GitHub · BitBucket · Djangogigs
· Mobile +49 179 9709833
http://christiankaula.com

Archive

  1. Rainerhaertl.de

  2. RESTful Web Applications: Code Less, Do More

  3. How to Handle Database Views in Django/South

  4. The 80% Use Case

    misc · Tags:
  5. Entrepreneurship Summit 2011 Thoughts

  6. Improving Copy: EntryDNS

    misc · Tags:
  7. How to Come Up With a Good Product Idea (As a Coder)

    misc · Tags:
  8. Siri is from the Future

    interfaces · Tags:
  9. How to Make a Better Analog Watch

    interfaces · Tags:
  10. Where to Not Put Django Templates

    code · Tags:
  11. Camila-overload.de

  12. Studi-tools.de

  13. REHAU Express Collection

  14. On CSS Structure

    code · Tags:
  15. My Take on Indention

  16. Why Web Development is Done in PHP or Java

    misc · Tags:
  17. Yay for Lanyon

    misc · Tags:
  18. Why My Mac Made My Sansa Clip+ Hang

    misc · Tags:
  19. Python: Decorate a Method That Gets Passed the Class Instance

    code · Tags:
  20. Django Template Tag to Shorten URLs Like Google

    code · Tags:
  21. Studi-tools.de is Online

  22. Nice to Know About Virtualenvwrapper

  23. Having been at Tschitschereengreen

    misc · Tags:
  24. A Howto on Django Syndication

  25. The Difference Between Work and Play

    tools · Tags:
  26. Nginx is Even Better Than I Thought

    misc · Tags:
  27. Clear Up Gelato CMS’ Gibberish

    code · Tags:
  28. Don’t Listen to the Idiot

    misc · Tags:
  29. Snow Leopards’ Bad Sides

    misc · Tags:
  30. Make Django Send Mails to Admins Only

    code · Tags:
  31. How to Fix Portage

    code · Tags:
  32. Archive Twitter Stuff With Python

    code · Tags:
  33. Wee Free Icons

    tools · Tags:
  34. The Problem With Django, Nginx and FCGI

    misc · Tags:
  35. New Design

    misc · Tags:
  36. Pink Ponies for Everybody!

  37. Whoosh Makes the Haystack

  38. Selenium is Pretty Neat

  39. OpenID for Free

    tools · Tags:
  40. Multi-Object-Edit With Django FormSets

    code · Tags:
  41. CSS is the New C - Sass is the Future

  42. Where Asimov was Right

    misc · Tags:
  43. The Webdesigners Arch Fiend: IE6

  44. Die Cache, Die

    code · Tags:
  45. What if Twitter Stopped to be Tomorrow?

    misc · Tags:
  46. Django Performance Tuning - Speed Up!

  47. Django Members That are in Fact Raw SQL

    code · Tags:
  48. What to Do if You Have to ‘Make All Strings Editable’

    code · Tags:
  49. Twitter is Even Worse Than I Thought

    misc · Tags:
  50. A Little Birdy Told Me…

    misc · Tags:
  51. With Django Debug Toolbar You Can Debug Django… Duh

  52. Lackeying Some Trading Card Fun?

    tools · Tags:
  53. By the Power of CSS… I Revisit You

  54. Jabberbot is Coming

  55. MacS are Like Tools

    misc · Tags:
  56. Jabber With Me Over XMPP

    projects · Tags:
  57. By the Power of CSS

  58. Why I Barely Ever Read Comments

    misc · Tags:
  59. Internet Explorer 6 on Mac - Actually Easier Than IE on Windows

  60. Bash and the Secret of the Lost Folders

    misc · Tags:
  61. MAME on Mac - Already Like the Sound

    misc · Tags:
  62. Python - Strings Very Much Attached

    code · Tags:
  63. Fun With Forms in Django

    code · Tags:
  64. Subversion: Ignore More

    code · Tags:
  65. It’s Time for Python Dateutils

    tools · Tags:
  66. That is Why Developers Aren’t Called Testers

  67. Now With Legacy Tag-URLs

    misc · Tags:
  68. Html Validator - Valid HTML - Profit

  69. More Fun With Tracebacks

    tools · Tags:
  70. jQuery That Python

  71. Django Can be Too Convenient

  72. Long Live the South

    code · Tags:
  73. Get Windows for Free (For Internet Explorer Testing on Mac)

  74. Viva! Vista! Worms Armageddon!

    misc · Tags:
  75. Fun With Python Stracktraces

    tools · Tags:
  76. So Sweet the Crons - Sweetcron

    tools · Tags:
  77. News From the South

    code · Tags:
  78. VoodooPad - Its a Wiki on Your Desktop

    tools · Tags:
  79. Heeyoo, SEO

    misc · Tags:
  80. Take a Note on the Side With Sidenote

    tools · Tags:
  81. Piwik Even Sounds Cooler Than Google Analytics

  82. Pixen, Pixel… Editor

    tools · Tags:
  83. Long Hard Road Out of Hell

    misc · Tags:
  84. Suspicious SuspiciousOperation Exceptions

    code · Tags:
  85. Apple Mail AKA Hides-Best-Things

    misc · Tags:
  86. Don’t Bring Me Down…

  87. Me and the Boys

    misc · Tags:
  88. I Have a Dream

    misc · Tags:
  89. Compress This Mess

  90. Ack, Knowledged

    tools · Tags:
  91. Plug in TextMate

    tools · Tags:
  92. Put Into Grand Perspective

  93. Code-A-Robot

    misc · Tags:
  94. Sub Fine Mecum Nemo Non Consentiet

    misc · Tags:
  95. DNS - the DNA of the Web

    misc · Tags:
  96. Open a Can of Worms

    misc · Tags:
  97. You Don’t Print the Web Do You?

    misc · Tags:
  98. Postgres on Mac Revisted

  99. What the Heck is a KeePass?

    tools · Tags:
  100. Saving and Its Massive Effects

    misc · Tags:
  101. I’m Too Cheap for .Mac

    tools · Tags:
  102. You Can Even Calibrate Batteries These Days

    misc · Tags:
  103. Don’t Mind Me - Just Passing Through

    misc · Tags:
  104. Virtual Python Revisited

  105. Going South

    code · Tags:
  106. These Windows Aren’t Clear at All

    misc · Tags:
  107. Database Switcheroo

  108. Django? Isn’t That Something Like Rails?

    misc · Tags:
  109. Magical Ponies

    misc · Tags:
  110. Yay for Automatic Deployment

    misc · Tags:
  111. A Quick Comparison of JavaScript Frameworks

  112. Apples and Pears

    misc · Tags:
  113. What I Forgot to Mention…

    misc · Tags:
  114. Good Friends

  115. Me vs Virtualenv

    misc · Tags:
  116. Where to Buy Apples Cheap

    misc · Tags:
  117. First Post!

    misc · Tags:
  118. MacGre

    misc · Tags: