Code

Opened 7 years ago

Closed 18 months ago

#3361 closed New feature (wontfix)

Create Database Views from the Model

Reported by: martin.marcher@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: bignose Triage Stage: Design decision needed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Currently it is not possible to create database views from the Models with django.

This would greatly improve usability of the project since views are a cenral design principle in DBMS

Attachments (0)

Change History (10)

comment:1 Changed 7 years ago by mir@…

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

Please describe how this feature could look like.

comment:2 Changed 7 years ago by martin.marcher@…

Note this is just a quick write down with a short thest of the SQL shema, I tried to keep the formating and naming according to what in looks like at http://www.djangoproject.com/documentation/tutorial1/#activating-models

An approach I could imagine would be:

from django.db import models

# Standard Database Tables...
class User(models.Model):
    username = models.CharField(maxlength=200)

class Password(models.Model):
    user = models.ForeignKey(User)
    password = models.CharField(maxlength=30)

# The View in the Database
# note the models.View I think
# separating it would ease up
# how to create that
# (I may be totally wrong also)
class Account(models.View):
    user = User.username
    password = Password.password

This could result in SQL code like:

CREATE TABLE "users_user"(
    id SERIAL PRIMARY KEY,
    username VARCHAR(200)
);

CREATE TABLE "passwords_password"(
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES "users_user" ("id"),
    password VARCHAR(30)
);

CREATE VIEW "accounts_account" AS
    SELECT
        "users_user"."username" AS "user",
        "passwords_password"."password" AS "password"
    FROM
        "users_user" NATURAL JOIN "passwords_password";

Of course the models.View would by default do nothing (or raise an execption) upon save or delete, I'd even think it would be an error to wrap the save, delete, update functions in a way so that the underlying tables are accessed. If that is wanted one could write a trigger in the database for that and implement the standard functionality.

Please note I'm neither what I'd call a database expert or a python hacker and I'm well aware that this example is quite simple but this is at the moment the best I can come up with.

I guess there are quite a few people that know much better how to do this but I find the idea of being allowed to work with views very nice...

And now that I made this example up I just figured out I have no idea wether the underlying OR wrappers you use could even do something like that (personally I'm especially interested in postgresql)

comment:3 Changed 7 years ago by mir@…

  • Triage Stage changed from Unreviewed to Design decision needed

Hmm, what what do you need it for? As long as you work within Django, the ORM allows to access all the related fields very easy. Of course, it would be nice to create views for direct sql access, but this is *very* sophisticated, I guess, and a bit pie-in-the-sky. Anyway, I only put this through to the next stage, and it will wait until someone from the core makes a decision about it. Thanks for sharing it!

comment:4 Changed 7 years ago by robertmlamarca@…

Just a point in support of allowing the model to create views. I think that allowing one to utilize all the internal tools possible in the database is always favorable in terms of performance and even ease of use.

While the database manager may allow us to accomplish many of the same things, it is still simpler to just get something straight out of a view rather than having to follow all the key fields with python.

Seems even to help with do not repeat and possibly reducing some overhead.

comment:5 Changed 7 years ago by ubernostrum

I'd be in favor of just saying that you should create the view manually and avoid having the model try to generate it (especially since the syntax can vary wildly). #3163 is related.

comment:6 Changed 7 years ago by jacob

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

James is right -- this is a duplicate of #3163.

comment:7 Changed 18 months ago by bignose

  • Easy pickings unset
  • Severity set to Normal
  • Type set to Uncategorized
  • UI/UX unset

Setting managed = False on a model does not meet this need.

A database view created by Django's syncdb will allow the application to be deployed without special steps. This is not satisfied by an unmanaged model.

A database view created by Django's syncdb will create the entity in the test database, allowing app code to use that view without breaking tests. This is not satisfied by an unmanaged model.

Re-opening, since this is not a duplicate of #3163.

comment:8 Changed 18 months ago by bignose

  • Resolution duplicate deleted
  • Status changed from closed to new

comment:9 Changed 18 months ago by bignose

  • Cc bignose added
  • Type changed from Uncategorized to New feature

comment:10 Changed 18 months ago by lukeplant

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

I'm going to WONTFIX, for the following reasons:

  • Django's ORM does not aim to provide a wrapper for every RDMS feature. For example, there are indexes that you cannot create from syncdb. It's expected that you create these manually using low-level methods.
  • If we support something, we need to support it fully. That includes, in this case, having appropriate implementation of Model.save() and the support for it in all backends etc. This is a large amount of work, given the need to add new operations to all the backends (for the syntax for creating views, which, according to ubernostrum, can vary a lot). Given that you can use either the post_syncdb signal or something like South to run the necessary SQL code, both for tests and in production, the gains for this feature seem small compared to the cost.

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.