Models over SQL views - disable regeneration of model

[Honeyman]

Hello.

I recently tried to create a custom SQL view on my Django-powered site
and use it via usual Django ORM approach; I created a view and then
manually built the Django model resembling this view, but quickly
noticed that any attempt to request Django to regenerate the Django-
generated tables causes a try to DROP this view as if it was a table,
and then create a new table with the same name.
My opinion is that the most trivial way to let Django-users use the
custom SQL views in a Django-way and at the same time allow them to
automatically manage the SQL tables by Django is to let them specify
that particular models do not need any SQL actions for them during
usual sqlall/sqlreset/reset/etc manage commands.
I raised a ticket #5858 for it and applied a quick patch which works
for me and hopefully covers all the places where an SQL could be
generated for such model. This patch creates a new Meta option called
sql_generation_required (name is just tentative) which is set to True
by default for any models (what stands for the existing behaviour) but
the user may change it to False, and then this model will not be
touched by any manage.py SQL commands.
But note that this patch does not alter the behaviour of sqlcustom; if
the user does not want sqlcustom to touch the view, one should remove
the custom SQL files rather than mark the model with Meta option.

With best regards,
Alex Myodov

[Honeyman]

Hello,

Patch propagated to trunk Django version (original patch was for 0.9.6
release) and resubmitted under the #3163 ticket originally raising
this problem (ticket #5858 was closed as a duplicate to #3163).
Any comments on the approach (good idea or not? is any "more djangy"
approach to handle SQL views is planned already?), as well as testing
(especially in the area of many-to-many tables) are appreciated.

On 2 нояб, 00:27, Honeyman <amyo...@gmail.com> wrote:

...

> My opinion is that the most trivial way to let Django-users use the
> custom SQL views in a Django-way and at the same time allow them to
> automatically manage the SQL tables by Django is to let them specify
> that particular models do not need any SQL actions for them during
> usual sqlall/sqlreset/reset/etc manage commands.

...

[Jeffrey]

I cannot comment on the quality of your patch because I have not
looked at it; nor am I likely qualified to pass judgment on it.

But I do want to add my support behind any effort to allow Django
models to integrate easily with SQL views. I think that this can be
another useful way to integrate custom SQL code into a Django app
where the Django ORM is not up to the task.

It also separates the custom SQL code from the Django app code, which
is a "clean" approach.

With PostgreSQL's re-write rules, I can imagine this becoming a very
powerful technique for those cases thatORM was not planned to handle.
After all, an SQL view in PostgreSQL is just a simple re-write rule
that is created automatically for you when you execute "CREATE
VIEW ...". If views can be supported by Django, there is nothing
stopping users from writing more extensive re-write rules to support
even "updatable views". In this way, views can also be used for
creating/updating records in addition to just querying for them.

All this, of course, is only recommended for those that are qualified
and comfortable to be working with SQL views and PostgreSQL re-write
rules in the first place.

Jeff

[Alex Myodov]

Hi all.

Can someone experienced please have a look at the #3163 ticket
("sql_generation_required" Meta option) and hint me what other steps
should I do to move the patch forward? I have a suspicion that the
testcases are the only remaining thing - but then, can someone hint me
how these testcases should verify this functionality? The
functionality is set up by the Meta option (and we can do that in the
test easily) - but how can a testcase verify that the SQL generated by
the django-admin commands does contain or do not contain the needed
tables? Can it run the django-admin.py directly "on itself" - but
then, how can we specify the application being tested? Is there any
other way to test the execution of django-admin.py within the Django
own test suites?

[James Bennett]

I'm still curious why it is, that if people want to define models
without having Django create tables, those people don't just avoid
running 'manage.py syncdb'.

--
"Bureaucrat Conrad, you are technically correct -- the best kind of correct."

[Alex Myodov]

James, the answer is - "cause these people may still want to run
"manage.py syncdb" for other models in the same app". You use
"manage.py syncdb/reset/something" for the whole application (cause
you are still doing the development, and the table structure changes
from day to day), and all the tables are regenerated as you want - but
some tables aren't; cause, for example, you've tuned them manually in
the RDBMS already, created cunning indices, datafilled them in some
way which cannot be easily reproduced via "custom SQL" concept, or
cause you want to protect the unique gathered datafill from occasional
change; or the most obvious and simple (and the main reason which this
patch was born to ground), if the table behing the model is an SQL
view rather than a table. This simple Meta option - and you can use
the whole power of custom SQL views (with compex queries and cunning
dependencies - even stored views if necessary) via Django ORM queries.
I am running this patch

[Malcolm Tredinnick]

On Wed, 2007-12-12 at 05:29 -0800, Alex Myodov wrote:
> Hi all.
>
> Can someone experienced please have a look at the #3163 ticket
> ("sql_generation_required" Meta option) and hint me what other steps
> should I do to move the patch forward?

The name is silly. Call it create_db_table and have it default to True.
For the (very few) users who need this functionality, they can set it to
False and it won't generate any SQL for that model. That's all that's
needed here.

Tests will be very painful and I wouldn't worry about it too much, since
it's such an edge case and will just add overhead. If you run
"./manage.py sql <model_name>" and see any output, it doesn't work.
That's really all that's required there.

Keep in mind, though, that at that point, might still take a bit of time
to get final review and then be committed. There are hundreds of open
tickets in the system, many of much greater impact on the user base than
this one. We're working towards getting a 1.0 release out the door and
need to focus a lot of energy on the features that impact 100% of the
userbase at the temporary expense of those that impact 1%. That's the
way life works. So get the patch up to as good as it can get and people
who really, really need the feature can apply hte patch themselves and
we'll get it into the tree at some point when we have a moment.

Regards,
Malcolm

--
Remember that you are unique. Just like everyone else.
http://www.pointy-stick.com/blog/

[Alex Myodov]

Thanks for ideas, Malcolm.
I am slightly against "create_db_table", cause the name implies that
it controls "creating DB tables", while in fact it controls ANY
operations on DB tables - CREATE/ALTER/DROP TABLE, CREATE/DROP INDEX,
etc. But I fully agree that my name is not better and I am open for
suggestions. Maybe... "manage_db_table", and the explanation in the
documents like "this Meta option whether the table can be managed when
using manage.py commands"?
Also I'll see if I can add any tests using call_command() function,
this could probably work (according to some other testcases).

[Malcolm Tredinnick]

On Wed, 2007-12-12 at 23:15 -0800, Alex Myodov wrote:
> Thanks for ideas, Malcolm.
> I am slightly against "create_db_table", cause the name implies that
> it controls "creating DB tables", while in fact it controls ANY
> operations on DB tables - CREATE/ALTER/DROP TABLE, CREATE/DROP INDEX,
> etc.

Meh.

We don't really do a lot of "management", so I slightly prefer the
fairly intuitive "create" over "manage". If you use manage as you
intend, it opens the big can of worms about what 'manage' means and we
already have enough tickets opened to resolve relatively trivial points
that I'm not interested in opening up that particularly slippery slope
for skiing.

You can call it manage_db_table in your patch if you like. If I commit
it, I'll probably change that, but it's really neither here nor there.

Malcolm
--
A conclusion is the place where you got tired of thinking.
http://www.pointy-stick.com/blog/

[Alex Myodov]

Ok, I see your point (I tried to do the "just apply and commit it"
patch myself, but if the committers have nothing strong against some
manual editing of the code before submission, that's even better).
Let's stuck with some more-or-less sane name (and let it be
"create_db_schema" for the time being - please don't consider that I
just want the last word to be mine - I just look forward to decrease
the confusion as low as possible, so I do not want to bring up an
oversimplified association with just "CREATE TABLE" actions), and
let's do the most with the patch itself rather than with the variable/
option names. If I find any better-sounding names, I'll put them in
the ticket discussion, so that the committer will be able to select
one.

(And hope that the word "schema" in RDBMS context is clear enough for
not to be another can of worms :) )