Version 2 (modified by dnozay, 3 years ago) (diff)

trivial; syntax coloring.

I have a situation where I want to modify each database table as it is created. The solution I created should work for PostgreSQL or any backend. In this particular case, my project is using a MySQL database, and requires transactions- that is, InnoDB storage. Alas, my hosting service defaults all tables to MyISAM, and I can't change that.

The usual method is to create a custom sql file "app_model.sql" that alters the table. That gets tedious when one has dozens, or hundreds of models.

Here's an 80% solution. Add code like the below to your app's :

# use a consistant MySQL storage type for all apps, all models

from django.dispatch import dispatcher
from django.db.models import signals

default_storage = 'InnoDB'
storage = { 'app_model_has_different_storage':'MyISAM'}
skip = set(('app_model_should_go_unchanged',))

def modify_MySQL_storage(sender, app, created_models, verbosity, interactive):
    from django.db import connection
    cursor = connection.cursor()

    for model in created_models:
        if db_table not in skip:
            engine = storage.get(model._meta.db_table,default_storage)
            stmt = 'ALTER TABLE %s ENGINE=%s' % (db_table,engine)
            if verbosity > 1: print '  ',stmt

# Modify SQL storage after any app installs

I call this an 80% solution because it won't touch the intermediary M2M tables. For that, we need a signal that fires on the creation of each database table, or lists all tables that were created, which doesn't exist.

If most tables are InnoDB but you try setting one or a few to MyISAM using storage, MySQL may complain about foreign keys... rather than making that work, I found it simpler to add the table to skip and write out the alter statement the old way, with the raw sql in app/sql/app_model.sql.

The code could be refined to only alter models in a particular app.

Attachments (1)

Download all attachments as: .zip

Back to Top