Code

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 __init__.py :

# 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:
        db_table=model._meta.db_table
        if db_table not in skip:
            skip.add(db_table)
            engine = storage.get(model._meta.db_table,default_storage)
            stmt = 'ALTER TABLE %s ENGINE=%s' % (db_table,engine)
            if verbosity > 1: print '  ',stmt
            cursor.execute(stmt)

# Modify SQL storage after any app installs
dispatcher.connect(modify_MySQL_storage,signal=signals.post_syncdb)

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.

And another patch against django mainline

Attached a patch that modifies django to understand per table the table engine desired.

Last modified 2 years ago Last modified on 04/20/12 06:54:58

Attachments (1)

Download all attachments as: .zip