Create Database Views from the Model

[martin.marcher@…]

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

[mir@…]

Please describe how this feature could look like.

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

[mir@…]

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!

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

[James Bennett]

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.

[Jacob]

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

[Ben Finney]

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.

[Luke Plant]

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.