In order to get Django to output innodb tables in MySQL, you either need to

1. output ALL tables as innodb or
2. selectively issue alter table commands

The first is sub-optimal (MyISAM is faster for query-dominant tables) and the second is a pain and hack-ish.

Here's a simple patch to Django 1.1 to allow you to specify table creation parameters via the Meta object, like so:

class test(models.Model):
    price = models.IntegerField()
 
    class Meta:
      db_table_creation_suffix = "ENGINE=InnoDB"

Its easy, with only 2 files to patch. Here are the steps.

1. Hunt down the django installation directory. On debian, using the python egg installation method, django is located /usr/lib/python2.5/site-packages/Django-1.1-py2.5.egg/django

2. Open db/models/options.py in a text editor. We'll be adding the "db_table_creation_suffix" field to the Meta object.

3. Near the top of the file, you'll see DEFAULT_NAMES = ('verbose_name', …)

Change this to look like this:

DEFAULT_NAMES = ('verbose_name', 'db_table', 'ordering',
                 'unique_together', 'permissions', 'get_latest_by',
                 'order_with_respect_to', 'app_label', 'db_tablespace',
                 'abstract', 'managed', 'proxy', 'db_table_creation_suffix')

The addition is

'db_table_creation_suffix'

4. Now scroll a couple lines down and you'll see this:

        self.duplicate_targets = {}
 
        # To handle various inheritance situations, we need to track where
        # managers came from (concrete or abstract base classes).
        self.abstract_managers = []
        self.concrete_managers = []

Change this to look like this:

        self.duplicate_targets = {}
        self.db_table_creation_suffix = None
 
        # To handle various inheritance situations, we need to track where
        # managers came from (concrete or abstract base classes).
        self.abstract_managers = []
        self.concrete_managers = []

5. Open db/backends/creation.py in a text editor. We'll be performing the actual appending of the db_table_creation_suffix parameter.

6. Search for

full_statement.append(')')

7. BELOW this line, add this:

        if opts.db_table_creation_suffix:
            full_statement.append(opts.db_table_creation_suffix)

That block of code should now look like this:

        for i, line in enumerate(table_output): # Combine and add commas.
            full_statement.append('    %s%s' % (line, i < len(table_output)-1 and ',' or ''))
        full_statement.append(')')
 
        if opts.db_table_creation_suffix:
            full_statement.append(opts.db_table_creation_suffix)
 
        if opts.db_tablespace:
            full_statement.append(self.connection.ops.tablespace_sql(opts.db_tablespace))

8. Save and you're done!

The SQL now generated looks like this:

CREATE TABLE `test` (
    `id` INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `price` INTEGER NOT NULL DEFAULT 0
)
ENGINE=InnoDB
;

No Comments »