Changes between Initial Version and Version 1 of AlterModelOnSyncDB

Timestamp:

Feb 26, 2007, 10:46:08 AM (18 years ago)

Author:

yary h <nospam@…>

Comment:

--

AlterModelOnSyncDB

@@ +1 @@
+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.