Opened 17 years ago

Closed 15 years ago

#3163 closed enhancement (fixed)

[patch] Optionally disable DB table creation from model

Reported by: wolfram.kriesing@… Owned by: Malcolm Tredinnick
Component: Database layer (models, ORM) Version: dev
Severity: normal Keywords: raw SQL view readonly
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Attachments (12)

meta-option-create_db_table.diff (2.2 KB ) - added by wolfram.kriesing@… 17 years ago.
sql_generation_required-trunk6635.diff (13.0 KB ) - added by honeyman 16 years ago.
sql_generation_required implementation (svn diff, Django trunk 6635)
create_db_schema-trunk6977.diff (13.4 KB ) - added by honeyman 16 years ago.
create_db_schema Mera option implementation (svn diff, Django trunk 6977)
create_db_schema-trunk7020-withtests.diff (20.2 KB ) - added by honeyman 16 years ago.
create_db_schema Mera option implementation (svn diff, Django trunk 7020, tests included)
create_db_schema-trunk7027-withtests.diff (24.7 KB ) - added by honeyman 16 years ago.
create_db_schema Mera option implementation (svn diff, Django trunk 7027, tests included)
create_db_schema-trunk7027-withtests.2.diff (24.7 KB ) - added by honeyman 16 years ago.
create_db_schema Mera option implementation (svn diff, Django trunk 7027, tests included)
create_db_schema-trunk7442-withtests.diff (24.9 KB ) - added by honeyman 16 years ago.
create_db_schema Mera option implementation (svn diff, Django trunk 7442, tests included)
create_db_schema-trunk7512-qsrf_ready.diff (25.0 KB ) - added by honeyman 16 years ago.
create_db_schema Meta option implementation (Django trunk 7512, qsrf-ready)
create_db_schema-trunk.diff (20.4 KB ) - added by honeyman 16 years ago.
create_db_schema Meta option implementation (Django trunk 8599)
traceback.txt (2.2 KB ) - added by guneeyoufix 16 years ago.
I had a problem after updating to the latest version on SVN. This is the traceback
traceback2.txt (2.5 KB ) - added by guneeyoufix 16 years ago.
This one is a problem. I just updated to revision 8621, and apparently, the parameter doesn't exist.
unmanaged_models.diff (12.0 KB ) - added by Ryan Kelly 15 years ago.
patch for unmanaged models, including tests and doc update

Download all attachments as: .zip

Change History (49)

by wolfram.kriesing@…, 17 years ago

comment:1 by anonymous, 17 years ago

Summary: Optionally disable DB table creation from model[patch] Optionally disable DB table creation from model

comment:2 by James Bennett, 17 years ago

Resolution: worksforme
Status: newclosed

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.

comment:3 by anonymous, 17 years ago

Resolution: worksforme
Status: closedreopened

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).

comment:4 by wolfram.kriesing@…, 17 years ago

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

comment:5 by Russell Keith-Magee, 17 years ago

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...

comment:6 by Jacob, 17 years ago

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

comment:7 by wolfram.kriesing@…, 17 years ago

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

comment:8 by Marc Fargas <telenieko@…>, 17 years ago

Needs tests: set
Version: SVN

db_table is used anyway, isn't it?

class Meta:
   db_table = 'my_view'
   create_db_table = False

Howto write the test for this ?

comment:9 by anonymous, 17 years ago

Triage Stage: UnreviewedDesign decision needed

comment:10 by Gary Wilson <gary.wilson@…>, 17 years ago

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.

comment:11 by Marc Fargas <telenieko@…>, 17 years ago

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.

comment:12 by Gary Wilson <gary.wilson@…>, 17 years ago

Yes, you would have to specify the SQL somehow.

comment:13 by wolfram.kriesing@…, 17 years ago

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.

comment:14 by wolfram, 17 years ago

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

comment:15 by wolfram, 17 years ago

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.

comment:16 by James Bennett, 17 years ago

#3361 is related.

comment:17 by James Bennett, 16 years ago

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

comment:18 by honeyman, 16 years ago

Keywords: raw SQL view added

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).

by honeyman, 16 years ago

sql_generation_required implementation (svn diff, Django trunk 6635)

in reply to:  18 ; comment:19 by wolfram, 16 years ago

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

in reply to:  19 ; comment:20 by honeyman, 16 years ago

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.

in reply to:  20 ; comment:21 by wolfram, 16 years ago

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

in reply to:  21 comment:22 by honeyman, 16 years ago

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

in reply to:  21 comment:23 by honeyman, 16 years ago

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.

comment:24 by honeyman, 16 years ago

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.

comment:25 by Jacob, 16 years ago

Triage Stage: Design decision neededAccepted

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

comment:26 by honeyman, 16 years ago

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)?

by honeyman, 16 years ago

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

comment:27 by honeyman, 16 years ago

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".

by honeyman, 16 years ago

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

comment:28 by honeyman, 16 years ago

Keywords: readonly added
Needs tests: unset

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.

by honeyman, 16 years ago

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

by honeyman, 16 years ago

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

comment:29 by honeyman, 16 years ago

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.

comment:30 by Philippe Raoult, 16 years ago

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

by honeyman, 16 years ago

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

comment:31 by honeyman, 16 years ago

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...

by honeyman, 16 years ago

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

comment:32 by dannychen, 16 years ago

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".

comment:33 by honeyman, 16 years ago

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

by honeyman, 16 years ago

Attachment: create_db_schema-trunk.diff added

create_db_schema Meta option implementation (Django trunk 8599)

by guneeyoufix, 16 years ago

Attachment: traceback.txt added

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

comment:34 by guneeyoufix, 16 years ago

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

by guneeyoufix, 16 years ago

Attachment: traceback2.txt added

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

comment:35 by Malcolm Tredinnick, 15 years ago

Owner: changed from nobody to Malcolm Tredinnick
Status: reopenednew

comment:36 by Ryan Kelly, 15 years ago

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.

by Ryan Kelly, 15 years ago

Attachment: unmanaged_models.diff added

patch for unmanaged models, including tests and doc update

comment:37 by Malcolm Tredinnick, 15 years ago

Resolution: fixed
Status: newclosed

(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.

Note: See TracTickets for help on using tickets.
Back to Top