[patch] Optionally disable DB table creation from model

[wolfram.kriesing@…]

I am using DB-views but also want them to be available as a model inside the app, but I dont want "syncdb" to create any DB table for it.
So I added an option "create_db_table" to the Meta class, like so:

class MyModel(models.Model):
    ...

    class Meta:
        create_db_table = False # prevent table creation in DB

[James Bennett]

Unless I'm misunderstanding, this is moot; you don't have to run syncdb if you don't want to (you can instead create the DB tables and views yourself, and then lay out model classes to match). If I've misread what you're asking for, please re-open this.

[anonymous]

It seems you are misunderstanding. This meta attribute is just for NOT creating a DB table for a certain model (which is not the default!). I have created the model (in model.py) so I can access the view from within django too, but syncdb shall not create anything when being run, I am creating the view directly via SQL. Letting syncdb create the DB table would just clash with the creation of the view by SQL and would be useless (in my case).

[wolfram.kriesing@…]

just an example:

class Users(models.Model):
   id = AutoField()
   name = CharField()
   since = DateTimeField()

class Users2006(models.Model):
   """This is just a view on the users table but only for the users of the year 2006, 
    that is done by using a DB view, so no need for creating a db table.
   """
   id = AutoField()
   name = CharField()
   since = DateTimeField()
   class Meta:
        create_db_table = False # prevent table creation in DB

[Russell Keith-Magee]

I don't have much use for this myself, but I like the idea. Extra kudos for delivering the whole package (documentation and all).

However, I would lean towards using the existing db_table variable, rather than introducing a new one. You should be able to interpret db_table=None as 'don't create table' without too much trouble.

Longer term, it would be nice to have support for creating views...

[Jacob]

Although I think it's a good thought, db_table=None currently means "use the default name for the table" (i.e. appname_modelname)...

[wolfram.kriesing@…]

what if i want to set the db_table to a different one than the default and still DONT want the table created :-) i think an extra value is required

[Marc Fargas <telenieko@…>]

db_table is used anyway, isn't it?

class Meta:
   db_table = 'my_view'
   create_db_table = False

Howto write the test for this ?

[Gary Wilson <gary.wilson@…>]

Another idea, maybe we have a special class that you inherit that tells django that this is a view and should not create a db table.

class Users2006(models.View):
    ...

And maybe this could even create the view if it doesn't already exist.

[Marc Fargas <telenieko@…>]

I don't see how Django would "guess" the view code unless the view was a simple select from another table :)
Maybe the "models.View" approach is more clear, but the create_db_table is simplier.

[Gary Wilson <gary.wilson@…>]

Yes, you would have to specify the SQL somehow.

[wolfram.kriesing@…]

I also had this models.View implemented once, but replaced it again, by the simpler approach posted here.
But the idea of letting django create the view is very compelling, currently I am doing that via custom SQL.
I could imagine the models.View approach achieve more things, but I feel that the property
create_db_table=False might not only be useful for views.

[wolfram]

We had found a better alternative, which does not interfer with the current models and lets you separate out models that actually are just DB-views. This option is much better concerning separation of concerns and maintenance see here ​http://wolfram.kriesing.de/blog/index.php/2007/django-nice-and-critical-article#comment-48425

Should I write a piece of documentation for it?

regards

wolfram

[wolfram]

no need to follow the link above, this is the copy/pasted text:

what we did first was implementing just normal models in the models.py, but that always created the tables in the DB upon “syncdb” (which we deleted then and created views for). Actually I even wrote a patch for django where you can mark a model as “create_table=False” but the patch never made it in.

So we went another actually much better way, after discussing it a lot in the team. We simply did not create the models in models.py, which are used for syncdb but we create a file dbviews.py where we put the views’ models. This is very nice separation of code too.
The next step is writing the view itself, which we just did in pure SQL of course. I.e. if we have the model Forum and we want some specialized ForumActivity-view then we created a view “CREATE VIEW core_forumactivityview” (we are on mysql). We then fired that onto our DB and the model that matched it (make sure to use the same column names as the view does!!!) simply looks like this:

class ForumActiviy(models.Model):
   all the fields
   class Meta:
       db_table = “core_forumactivityview”

now you can simply do

import project.core.dbviews

and they just look like models :-).
Depending on how you wrote the view you might even be able to update the data.

[James Bennett]

#3361 is related.

[James Bennett]

#5858 was a duplicate, and had an alternate patch.

[honeyman]

As my #5858 bug was closed, I am joining the discussion here.
Idea of dbviews.py rather than models.py is good, but restricts in importing the views from models. When I tried to remove the custom-created model for my SQL views from models.py though still imported it from another model, this model still made it into the output of manage.py.
Therefore a Meta option seems the best possible solution.
But create_db_table logic seems insufficient to me - a better solution should cover all the bases - DROP TABLE in sqldelete, CREATE INDEX/DROP INDEX, even sqlflush.
I just regenerated the patch proposed in #5858, and now it includes the documentation, is based on the trunk Django, and covers hopefully all the commands (except sqlcustom obviously).

[honeyman]

sql_generation_required implementation (svn diff, Django trunk 6635)

[wolfram]

Replying to honeyman:

As my #5858 bug was closed, I am joining the discussion here.
Idea of dbviews.py rather than models.py is good, but restricts in importing the views from models. When I tried to remove the custom-created model for my SQL views from models.py though still imported it from another model, this model still made it into the output of manage.py.
Therefore a Meta option seems the best possible solution.
But create_db_table logic seems insufficient to me - a better solution should cover all the bases - DROP TABLE in sqldelete, CREATE INDEX/DROP INDEX, even sqlflush.
I just regenerated the patch proposed in #5858, and now it includes the documentation, is based on the trunk Django, and covers hopefully all the commands (except sqlcustom obviously).

Can you please explain what you mean here? Especially by "but restricts in importing the views from models"!
Actually using dbviews would never generate a table in the DB, so there is no need for removing it. Create the views by using the sqlcustom files.
hth

Wolfram

[honeyman]

Replying to wolfram:

Can you please explain what you mean here? Especially by "but restricts in importing the views from models"!
Actually using dbviews would never generate a table in the DB, so there is no need for removing it. Create the views by using the sqlcustom files.

If I do "from db_myview1 import myview1" from another model (imported from models.py) rather than from dbviews.py, it still will be imported and used in sql generation (i.e. imported from models.py transitively). And in my case, it would be good to do such import from another imported model, to use it for foreign key from the other model. I didn't try to legally use the view as a foreign key on SQL level (and I doubt it'll work), but it works like a charm in Django and it is pretty convenient :)
Though I agree, if someone does not need to refer to the view on the model class declaration, "dbviews" approach should work.

[wolfram]

Replying to honeyman:

Replying to wolfram:

Can you please explain what you mean here? Especially by "but restricts in importing the views from models"!
Actually using dbviews would never generate a table in the DB, so there is no need for removing it. Create the views by using the sqlcustom files.

If I do "from db_myview1 import myview1" from another model (imported from models.py) rather than from dbviews.py, it still will be imported and used in sql generation (i.e. imported from models.py transitively). And in my case, it would be good to do such import from another imported model, to use it for foreign key from the other model. I didn't try to legally use the view as a foreign key on SQL level (and I doubt it'll work), but it works like a charm in Django and it is pretty convenient :)
Though I agree, if someone does not need to refer to the view on the model class declaration, "dbviews" approach should work.

If I understand you right you are doing the following inside models.py

    from db_myview1 import myview1

if thats the case, remove it form there.

You must not do it in the models,py but only in the place where you need teh db-views.
So for example inside app/core/views.py you can do the import, but not inside the models.py!

Wolfram

[honeyman]

Replying to wolfram:

If I understand you right you are doing the following inside models.py

    from db_myview1 import myview1

if thats the case, remove it form there.

You must not do it in the models,py but only in the place where you need teh db-views.
So for example inside app/core/views.py you can do the import, but not inside the models.py!

Not quite so. I do something close to this:

models.py:

from db_model1 import model1
from db_model2 import model2

db_model2.py:

from django.db import models
from db_myview1 import myview1

class MyView1(models.Model):
  class Meta:
    sql_generation_required = False # by my patch
    
  fkey = models.ForeignKey(myview1, db_column = 'id') # This is why I need "import myview1" at the top

  # other fields, presented by the SQL view

[honeyman]

Replying to wolfram:

If I understand you right you are doing the following inside models.py

    from db_myview1 import myview1

if thats the case, remove it form there.

You must not do it in the models,py but only in the place where you need teh db-views.
So for example inside app/core/views.py you can do the import, but not inside the models.py!

Not quite so. I do something close to this:

models.py:

from db_model1 import model1
from db_model2 import model2

db_model1.py:

class MyModel1(models.Model):
   f1 = models.# ...
   f2 = models.# ...
   f3 = models.# ...

db_myview1:

# Myview1 SQL view is based upon the Model1 table
class MyView1(models.Model):
  class Meta:
    sql_generation_required = False # by my patch
    
  # Fields similar to Model1
  f1 = models.# ...
  f2 = models.# ...
  f3 = models.# ...
  # Some fields calculated by the SQL view
  xf1 = models.# ...
  xf2 = models.# ...
  xf3 = models.# ...

db_model2.py:

from db_myview1 import myview1

class MyModel2(models.Model):
  fkey = models.ForeignKey(myview1, db_column = 'id') # This is why I need "import myview1" at the top
  # I could have done fkey = models.ForeignKey(db_model1), but then fkey wouldn't have xf1, xf2 and xf3,
  # and more complex queries would be needed to retrieve them.

[honeyman]

And surely this is not the only use case for this feature (when the separating of SQLview-based models to a different "namespace" lacks convenience).
Even more useful would be the capability to have several models in the same Python file, some of the models requiring the SQL regeneration and some of them not requiring.
Example: we have a Django model Order (generating the table dbOrder). We could have manually created SQL views like vwOrder_InProcess, vwOrder_PartnershipProgram, vwOrder_AttentionRequired, and reengineer the appropriate Django models for them. Having a capability to mark the view-based models as "sql_generation_required = False", we could have put the definitions of all these "special order models" into the same Python file:

class Order(models.Model):
  ...
class Order_InProcess(models.Model):
  class Meta:
    sql_generation_required = False
  ...

All these classes could easily share the code, interact - and this is less convenient if class Order and class Order_InProcess are defined in separate files.

[Jacob]

I'm not thrilled with the name, I think, but that's just bikeshedding.

[honeyman]

Jacob, sorry.... but which one of the two names (and therefore approaches) is accepted? One with "create_db_table" name and the code which blocks only the CREATE TABLE (for the marked table) or the one with the "sql_generation_required" name and the code which blocks CREATE TABLE/ALTER TABLE/DROP TABLE/CREATE INDEX (for the marked table as well)?

[honeyman]

create_db_schema Mera option implementation (svn diff, Django trunk 6977)

[honeyman]

I haven't succeeded in using call_command() to create tests for this functionality, so probably this should stay without tests.
Anyway, the patch is updated to the latest trunk, and also (after the discussion on ​http://groups.google.com/group/django-developers/browse_thread/thread/598a48b091fceaf0/0ec8745adacfd958) renamed the Meta option name from "sql_generation_required" to slightly niftier "create_db_schema".

[honeyman]

create_db_schema Mera option implementation (svn diff, Django trunk 7020, tests included)

[honeyman]

I am not quite sure who should consider whether the tests are sufficient; but for the time being since the tests are already present, I am removing the "needs tests" flag.

[honeyman]

create_db_schema Mera option implementation (svn diff, Django trunk 7027, tests included)

[honeyman]

create_db_schema Mera option implementation (svn diff, Django trunk 7027, tests included)

[honeyman]

As noticed by Wanrong Lin at ​http://groups.google.com/group/django-developers/browse_thread/thread/22259e09485c8a21/be9e5dd4a48fe52f, the 7020 patch had some issues with "manage.py syncdb" command. 7027 patch doesn't.
Sorry for duplication of *7027-withtests.diff and *7027-withtests.2.diff (got some issues with internet link) - both patches should be equal.

[Philippe Raoult]

I've read the patch out of curiosity and it looks nice. To prevent indenting code further than necessary you might want to reformat some sections using the pattern:

if not meta.create_db_table:
    continue

regular code here

[honeyman]

create_db_schema Mera option implementation (svn diff, Django trunk 7442, tests included)

[honeyman]

PhiR, I'm afraid I cannot agree with you. It seems to me that this indeed simplifies the patch - but in most cases of this patch, it makes the result code a bit less readable. And I believe that the readable code is better than the readable patch.

By the way, it's about three months since accepting the patch for triage. Can anyone suggest any specific actions to speedup moving it further? It would be very great to have Django in its next version (won't it be 1.0?) supporting SQL views, at least as such basic level. Of course this feature works for me, but I am not enough representative and may not have tested all the possible cases...

[honeyman]

create_db_schema Meta option implementation (Django trunk 7512, qsrf-ready)

[dannychen]

Just for remind, create_db_schema Meta option may also impact "sqlflush" command.

In django test framework, sqlflush command generate "TRUNCATE TABLE" sql statement for django tables. So, sqlflush command should also skip to "truncate" DB_views with "create_db_schema = false".

[honeyman]

I believe my patch covers sqlflush (as well as any other commands which affect the DB tables) already.

[honeyman]

create_db_schema Meta option implementation (Django trunk 8599)

[guneeyoufix]

I had a problem after updating to the latest version on SVN. This is the traceback

[guneeyoufix]

Sorry about last comment. I'm not really familiar with SVN.

[guneeyoufix]

This one is a problem. I just updated to revision 8621, and apparently, the parameter doesn't exist.

[Ryan Kelly]

Following some comments from Malcolm on django-users, I've attached an attempted "minification" of this patch. This uses the meta-attribute name "managed" instead of "create_db_schema" and tries to keep the logic contained within db.backends.creation as much as possible. I've also tweaked the testcases to run successfully against the latest trunk.

[Ryan Kelly]

patch for unmanaged models, including tests and doc update

[Malcolm Tredinnick]

(In [10008]) Fixed #3163 -- Add a "Meta.managed" option to models.

This allows a model to be defined which is not subject to database table
creation and removal. Useful for models that sit over existing tables or
database views.

Thanks to Alexander Myodov, Wolfgang Kriesing and Ryan Kelly for the bulk of
this patch.