Code

Opened 7 years ago

Closed 5 years ago

#3163 closed enhancement (fixed)

[patch] Optionally disable DB table creation from model

Reported by: wolfram.kriesing@… Owned by: mtredinnick
Component: Database layer (models, ORM) Version: master
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: UI/UX:

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@… 7 years ago.
sql_generation_required-trunk6635.diff (13.0 KB) - added by honeyman 6 years ago.
sql_generation_required implementation (svn diff, Django trunk 6635)
create_db_schema-trunk6977.diff (13.4 KB) - added by honeyman 6 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 6 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 6 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 6 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 6 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 6 years ago.
create_db_schema Meta option implementation (Django trunk 7512, qsrf-ready)
create_db_schema-trunk.diff (20.4 KB) - added by honeyman 6 years ago.
create_db_schema Meta option implementation (Django trunk 8599)
traceback.txt (2.2 KB) - added by guneeyoufix 6 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 6 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 rfk 5 years ago.
patch for unmanaged models, including tests and doc update

Download all attachments as: .zip

Change History (49)

Changed 7 years ago by wolfram.kriesing@…

comment:1 Changed 7 years ago by anonymous

  • Summary changed from Optionally disable DB table creation from model to [patch] Optionally disable DB table creation from model

comment:2 Changed 7 years ago by ubernostrum

  • Resolution set to worksforme
  • Status changed from new to closed

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 Changed 7 years ago by anonymous

  • Resolution worksforme deleted
  • Status changed from closed to reopened

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 Changed 7 years ago by 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

comment:5 Changed 7 years ago by russellm

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 Changed 7 years ago by 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)...

comment:7 Changed 7 years ago by 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

comment:8 Changed 7 years ago by Marc Fargas <telenieko@…>

  • Needs tests set
  • Version set to 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 Changed 7 years ago by anonymous

  • Triage Stage changed from Unreviewed to Design decision needed

comment:10 Changed 7 years ago by 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.

comment:11 Changed 7 years ago by 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.

comment:12 Changed 7 years ago by Gary Wilson <gary.wilson@…>

Yes, you would have to specify the SQL somehow.

comment:13 Changed 7 years ago by 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.

comment:14 Changed 7 years ago by 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

comment:15 Changed 7 years ago by 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.

comment:16 Changed 7 years ago by ubernostrum

#3361 is related.

comment:17 Changed 6 years ago by ubernostrum

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

comment:18 follow-up: Changed 6 years ago by honeyman

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

Changed 6 years ago by honeyman

sql_generation_required implementation (svn diff, Django trunk 6635)

comment:19 in reply to: ↑ 18 ; follow-up: Changed 6 years ago by 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

comment:20 in reply to: ↑ 19 ; follow-up: Changed 6 years ago by 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.

comment:21 in reply to: ↑ 20 ; follow-ups: Changed 6 years ago by 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

comment:22 in reply to: ↑ 21 Changed 6 years ago by 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

comment:23 in reply to: ↑ 21 Changed 6 years ago by 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.

comment:24 Changed 6 years ago by 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.

comment:25 Changed 6 years ago by jacob

  • Triage Stage changed from Design decision needed to Accepted

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

comment:26 Changed 6 years ago by 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)?

Changed 6 years ago by honeyman

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

comment:27 Changed 6 years ago by 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".

Changed 6 years ago by honeyman

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

comment:28 Changed 6 years ago by honeyman

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

Changed 6 years ago by honeyman

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

Changed 6 years ago by honeyman

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

comment:29 Changed 6 years ago by 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.

comment:30 Changed 6 years ago by PhiR

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

Changed 6 years ago by honeyman

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

comment:31 Changed 6 years ago by 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...

Changed 6 years ago by honeyman

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

comment:32 Changed 6 years ago by 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".

comment:33 Changed 6 years ago by honeyman

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

Changed 6 years ago by honeyman

create_db_schema Meta option implementation (Django trunk 8599)

Changed 6 years ago by guneeyoufix

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

comment:34 Changed 6 years ago by guneeyoufix

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

Changed 6 years ago by guneeyoufix

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

comment:35 Changed 5 years ago by mtredinnick

  • Owner changed from nobody to mtredinnick
  • Status changed from reopened to new

comment:36 Changed 5 years ago by rfk

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.

Changed 5 years ago by rfk

patch for unmanaged models, including tests and doc update

comment:37 Changed 5 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.