Please have view support for databases

[benedict.m.holland@…]

I am linking django to a legacy app database with views. The views have primary keys associated with them and can not be synced using the ./managed.py syncdb if and when I need to recreate this db on my test environment. I can create the views with manual sql code but when I run the syncdb command the view doesn't exist and it throws and error saying that the foreign key to this view can not be created because the table doesn't exist.

I think the solution is a signal which is sent directly after the model is saved to the database which would allow me to hook off of it to create the views. Currently the REALLY nasty hack I am doing is creating my own view, subclassing off an unmanaged model which is the view, and after the syncdb running custom sql to create the view. I have to foreign key off of the actual table (not the view) which means that none of the associated view columns are available with that object when I do foreign object referencing in code like: foo.object = object where foo.object is a foreign key. The only work around is to alter every single reference to foo.object to foo.object = View.objects.get() which is prohibitively expensive and could in fact break a lot of stuff.

[Aymeric Augustin]

In this situation, I think you should stick with unmanaged models and create your tables manually in the test environment, for example with initial SQL.

Your use case looks rather specific; I can't tell if a per-model post-syncdb signal would be generally useful.

[benedict.m.holland@…]

I can not imagine that linking up django to a database containing views is a project specific task. Views will also almost always contain foreign keys. Also the ability to create views is a standard database practice with any database in a production environment (and it just good programing practice anyway) and I do believe that since that is standard in any database that django should be able to create views and manipulate them. If anything having the ability to manually create them directly after specific tables have been created still seems like sort of a hack but less terrible.

Let us say for example that I have a table and I want to put an is_active, or is_current column. While I could in fact put this in model code, it doesn't belong there, it belongs on the database and it has to be inside a view because it needs to be able to change whenever time changes (think is_active is true when start >= now() >= end).

As I said before, unmanaged models do not work because I don't have a way to access the columns in the view (for example, is_active). If I forenkey off of the table containing the data, I will get an error trying to access is_active, but I have to foreignkey off of the table data because the view/table isn't created yet since it will only run my sql code after the syncdb.

Basically, assume that you have a DB view which combines two tables in your model, A and B. The view is select * from A, select * from B and is called myView. I now want to access the foo column from B from the view and foreign key off of the view instead of table B.

How can I currently do this in django as this is very common practice.

[Michael Manfre]

I've had to code around this limitation a few times. At first I had a test runner that would make all of the unmanaged models managed before the test database was built. All tests would populate the views with expected data. This had the nice benefit of not testing the views and only testing the python code.

This is not practical for some more complex views, which forced me to evolve the set up. I now have a management command that builds the test database from SQL schema files. The db backend (django-mssql) I use supports an optional parameter TEST_CREATE, which instructs the backend whether or not is should build the test database before running tests. If your backend doesn't support a similar parameter, you can get the same behavior with a custom test runner.

[Anssi Kääriäinen]

I have this exact same problem at the moment. A quick design idea:

Models get a new Meta option 'view_sql'. This can be a string, or this can be a callable. If it is a callable, it will need to accept one argument, connection (this way you can have different SQL for different backends). Modifications (at least save(), delete() and queryset.update()) are disallowed. This restriction could be lifted later on, although at that point an unmanaged model looks to be more like what is wanted. If a model is deleted, and there is a foreign key to that model in a view, the related model is not deleted.

The view will be created when syncdb is run. The view_sql needs to be a complete create view command, and it needs to be named the same as the db_table name is.

The main point why this is needed is because testing Django applications which have models based on views is a little painful. The view SQL needs to be installed at some point, but initial sql is really painful (one line per command). Also, dumpdata will try to dump the views and reloading them to the test DB is naturally completely wrong. So you need to skip the views in dumpdata also. It can be also a little painful to delete models and then discover that Django tries to delete from the related views. On the other hand this feature does not add anything that can not be done with unmanaged models already. It just makes everything much more convenient.

[Jacob]

I think this is a usage/support question, not really a feature request or bug report. You probably should take this to django-users where you'll probably get good suggestions how to deal with this (me, I make models-representing-views as managed=False and create the view in an ​initial SQL file).

Thanks!