Fate of sql* management commands

[Pkl]

Hello all,

I've noticed that all sql* commands that were in django core management have disappeared from the latest sources.

However, even though Django now has builtin south-style migrations now, these sql* commands (especially sql-all) were priceless to debug differences between the models and the actual DB schema.
Especially as long as some django apps haven't caught up with all these recent changes - I myself ended up with missing tables after scrupulously following upgrade instructions, with "manage.py migrate" saying "all is fine".

What is the new way to dump the sql schema of currently installed django appz ? It'd maybe be worth that I provide a doc patch to inform users about it.
If there is none, is there an agreement to restore at least sqlall and sqlclear ?

Also what was the reasoning behind, on the first, place, blocking access to these utilities in django1.7 (they seemed to work, when I forced them), and now removing them ? A simple warning about the new "migrations" stuffs (or a "--force" argument) would most probably have sufficed to prevent improper usage of these sql* commands, while letting django users getting the info they need to move forward, wouldn't it ?

Here is the related ticket : https://code.djangoproject.com/ticket/24481

thanks a lot,
regards,
Pascal

[Russell Keith-Magee]

On Mon, Mar 30, 2015 at 1:36 AM, Pkl <chambon...@gmail.com> wrote:

Hello all,

I've noticed that all sql* commands that were in django core management have disappeared from the latest sources.

However, even though Django now has builtin south-style migrations now, these sql* commands (especially sql-all) were priceless to debug differences between the models and the actual DB schema.
Especially as long as some django apps haven't caught up with all these recent changes - I myself ended up with missing tables after scrupulously following upgrade instructions, with "manage.py migrate" saying "all is fine".

What is the new way to dump the sql schema of currently installed django appz ? It'd maybe be worth that I provide a doc patch to inform users about it.
If there is none, is there an agreement to restore at least sqlall and sqlclear ?

 

No, there isn't :-)

Also what was the reasoning behind, on the first, place, blocking access to these utilities in django1.7 (they seemed to work, when I forced them), and now removing them ? A simple warning about the new "migrations" stuffs (or a "--force" argument) would most probably have sufficed to prevent improper usage of these sql* commands, while letting django users getting the info they need to move forward, wouldn't it ?

 

Right now, there are two (almost) completely independent implementations of SQL table generation logic: 

 * The legacy version, used by syncdb, sqlall, et al

 * The new version, used by migrate.

In the past, there was a good reason for sqlall to exist - you needed to be able to run syncdb and create tables. The only time you ever called "CREATE TABLE" was when a table was fresh, and after that, you never issued any command related to the table schema. It didn't hurt anyone to allow sqlall to generate "what would the CREATE TABLE be if we had clean databases", and it was at least partially useful as a migration assistant, so the sqlall functionality was available.

In the era of migrations, there's still a need to do a CREATE TABLE, but the table creation logic is a lot more complex, because it's not *just* a CREATE TABLE that is needed; all the ALTER TABLE statements are needed to add/remove columns as well. 

It would probably be *possible* to refactor manage.py sqlall to use the new table creation logic; but the question would then be "why do you want it"? manage.py migration *should* be handling all your table creation and migration functionality, so the need to manually diff a schema shouldn't exist. If you've hit problems with "manage.py migrate" *not* identifying migrations, then that's a much bigger problem, and one that needs to be reported as a bug - because it's a huge bug. Anything you can do to narrow down the situation that led to the problems you've observed would be very helpful.

Yours,

Russ Magee %-)

[Andrew Godwin]

I must also point out that the sqlmigrate command will still get you SQL from migrations, though it sadly lacks the ability to take a range of migrations, optimise them down, and output the SQL for _that_ - that's probably the best thing for us to aim towards, and will almost entirely recreate the functionality of sqlall without actually being wrong (sqlall doesn't understand things like data migrations or custom SQL sections of migrations, and so will sadly be wrong in if you have anything like a moderately complex migration set).

The plus side is that it would be possible to do this sort of thing as a third-party command/backport if we wanted to have it usable on 1.8 and even 1.7 (the 1.8 feature set is frozen now, or I'd suggest quickly squeezing it in).

Andrew

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAJxq84_Ww_ZcuhEx8MmfcxwY%3DGpMzuPGh1maGxBDJFCikUO5MA%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[Pascal Chambon]

Thanks for your answers,

l'm confused nonetheless, because their are two notions mixing up, here, that we should probably separate:

- the HISTORY of SQL schemas, aka "django/south migrations"
- the CURRENT STATE of SQL schemas, that I'll call "ORM Models Dump"

Let's leave the SQL data (and its migrations) out of the equation please, here i'm only caring about the structure of data, the schemas, and their corresponding SQL DDL.

I completely understand and agree that migrations are the way to go to upgrade between app version, not only because it allows adding/removing columns (which syncdb didn't do, previously), but also doing all sorts of on-the-fly custom conversions.

However, what I am seeking, is not a dump of successive migrations (even aggregated), it's an "ORM models dump", i.e a "transliteration" of django ORM classes to their corresponding SQL CREATE statements. Just like a "mysqldump --no-data", except that it's the logical, theoretical schema that I want here, not the real, SQL-side one.

I really do not believe that we need all the history of schema changes to get this schema dump ; AFAIK, it's the django ORM models which define the logical schema of data, not the migrations ; if a class MyUser defines a field "my_adress", and migrations have no track of it, it's the migrations that are wrong/incomplete, not the python code. Both "current models" and "the sum of all migrations" are supposed to be in sync at any time, arent they ? Custom SQL  code is thus not a problem, since we don't care about the path taken, just were we are currently.

So if django was probably able to dump all the DDL for its ORM models in the past, why couldn't it continue to do it ?

As for the question:

"why do you want it"? manage.py migration *should* be handling all your table creation and migration functionality, so the need to manually diff a schema shouldn't exist

My answer is : for the same reason that I need all other debug tools available, from django-debug-toolbar to lowest-level cpython debuggers. ^^

I see tons of ways how stuffs could go wrong, from an accidental interruption of migrations (DDL instructions often having the flaw of being non-rollbackable), to bugs in migrations' code, without forgetting unorthodoxes events which may have happend to a project's conf (eg. switching from a USER_MODEL to another, and thus having to fix stuffs the hard way, with "--fake" migrations if necessary), or the handling of apps that have no support for migrations.

Sure, people encountering troubles might make like I did - start a brand new project and mysqldump it after whole migration - but if we can expose that information in a straightforward way, like it was before, it's all benefits for everyone, isn't it ?

regards,

Pascal

--
You received this message because you are subscribed to a topic in the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-developers/h92CcblbYfk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-develop...@googlegroups.com.

To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAFwN1upDReQpmz%2BVDaHy_SVa8Q_KtEikO-YTyz7RyDcHJAVD%3DQ%40mail.gmail.com.

[Carl Meyer]

Hi Pascal,

I understand what you are saying, but your understanding is no longer
correct. Migrations are (intentionally) powerful, and you are permitted
to include arbitrary SQL DDL in your migrations (via the RunSQL
migration). This DDL is part of your schema just as much as any DDL
auto-generated by Django in a CreateModel operation is, and your code
may depend on it. So if we dumped a schema directly from the models,
ignoring migrations, it could be missing crucial elements of your actual
schema.

So it is not true that the Python models are the canonical
representation of your schema, and the SQL DDL is just a translation of
them. In fact, your migrations are the (only) canonical representation
of your schema, which may include things (specialized indices, views,
SQL functions, triggers...) that don't have any direct representation in
the Python models at all. The Python models just need to be in-sync
enough to generate the correct SQL for queries at run-time, but they
often will not fully represent your schema.

There should be only one canonical representation of your schema. This
canonical representation cannot possibly be anything but your
migrations, unless we removed the ability to run arbitrary DDL in
migrations (which is not an option; that's too useful). Therefore,
migrations are the canonical schema. Therefore, we won't introduce any
way to "dump SQL schema" unless it is based on the contents of your
migrations (the approach Andrew mentioned).

> So if django was probably able to dump all the DDL for its ORM models in
> the past, why couldn't it continue to do it ?

Because in the past we did not have migrations, and we don't want two
different (possibly inconsistent) canonical representations of your
schema. The old way of generating SQL DDL directly from models is
deprecated, and will disappear entirely in a future version of Django.
The new way of generating SQL DDL from models _is_ the migrations system.

Carl

[Andrew Godwin]

Ah, but the point I'm trying to make is that they're not two separate notions at all.

The current migrations system was deliberately designed in a way such that it is both - it's a representation of changes through time, sure, but it's also an entire statement of how the database is made. Migrations actually runs by running through the migrations in memory to build up models and using those for operations (it's theoretically possible to throw away models.py files and just run from migrations' end state, but that wouldn't be very useable).

This is important as there are some DDL things that Django cannot easily extract from the database. For example, if you made a partial index in a migration with RunSQL, "sqlall" and Django's introspection library didn't know how to get that out of the database, and the place it used to go before migrations (custom SQL files) are gone, so it would miss it out and give you incorrect results.

However, running through migrations and using those as a source of truth does give you the right results - the migrations _are_ what the database should be. Django sees them as the exact set of statements needed to make a new database, and so if you want a schema, where better to get one from? The current set of models isn't expressive enough to contain all DDL changes (though some projects are working on improving that, e.g. Marc Tamlyn's Postgres work), and why throw away the data when we already have it?

The code inside Django that makes sqlall/sqlclear/etc is now a deprecated codepath, since migrations uses a brand new and improved codepath to do all its changes; the plan was always to introduce migrations and this new DDL codepath, and slowly retire the old one. If we were to keep sqlall it would need rewriting to run on top of the new schema editing code - which is entirely possible, but realise that you're asking us for a lot more work, rather than just simply asking to leave existing code in place.

My preferred solution, instead of reimplementing all these sql* commands, is to just improve upon sqlmigrate so it can give you this singular schema dump easily, and take the place of sqlall and sqlclear (which are the forwards and backwards directions respectively). flush/sqlflush are different, as they're not DDL but DML, and so less affected.

I'm open to someone reimplementing the old sql commands on the new system, but realise it's not easy work to make it so it doesn't miss out things here and there and does it properly (it's also not too much more than wiring the autodetector and the SQL output system together in a few different ways, kind of like makemigrations + sqlmigrate but without the middle step of saving migrations to disk)

Andrew

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CA%2BXNUS2qpByi%3D0n%3D35Hn-HhXg0xAJxY72EeiBrA4P1DEpjS7rQ%40mail.gmail.com.

[Jon Dufresne]

On Sun, Mar 29, 2015 at 4:57 PM, Russell Keith-Magee
<rus...@keith-magee.com> wrote:

> It would probably be *possible* to refactor manage.py sqlall to use the new
> table creation logic; but the question would then be "why do you want it"?

Speaking from experience, I have found these commands to be a
extremely helpful when migrating a legacy application to Django. In my
situation, the legacy application would do whatever it wanted with the
schema and was not very opinionated nor well maintained. When moving
to Django having a quick way to dump Django's *ideal* SQL
representation was a very useful way to debug what needed to change in
the legacy application. There may even be a interim where SQL
migrations are handled outside Django but with the goal of moving
towards Django's representation. Having a way to diff actual vs
expected is very useful for this situation.

[Joachim Jablon]

Not sure about this but wouldn't "RunPython" actually keep you from getting an proven accurate result ? Even if we could imagine running the migration in a transaction and capturing the SQL that would be run, it would be dependent on the current data in the DB among other thing (could even be dependant on the meteo, or random variables or anything)...

And given that you can (as the doc says) use the scheme editor inside a RunPython migration, who knows what the proper SQL would be ?

(again, I may be missing a point there)

-- 

Joachim Jablon

[Andrew Godwin]

That is correct - RunPython and some other operations (definitely DeleteIndex) don't have single SQL statements that can be output. There's not much that can be done here, really - if you want to use SQL to set up databases, then you can't use RunPython to do it, you should just use migrations directly (as they were intended).

Andrew

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/c33d5b7f-9f05-4a04-b27a-7580ec72ca1d%40googlegroups.com.

[Aymeric Augustin]

On 30 mars 2015, at 23:10, Carl Meyer <ca...@oddbird.net> wrote:

> So it is not true that the Python models are the canonical
> representation of your schema, and the SQL DDL is just a translation of
> them. In fact, your migrations are the (only) canonical representation
> of your schema, which may include things (specialized indices, views,
> SQL functions, triggers...) that don't have any direct representation in
> the Python models at all. The Python models just need to be in-sync
> enough to generate the correct SQL for queries at run-time, but they
> often will not fully represent your schema.

While I understand this argument in theory, I'm not sure we can stop there in
practice. I know that anecdote != data but here's my story and what I think it
means for Django.

I started a moderately complex Django-based e-commerce project about one year
ago. We switched to Django 1.7 right after it was released and went live a few
days later. We made the most basic use of migrations: add a model here, run
makemigrations, commit; change a field there, run makemigrations, commit.

We did only one complicated thing. We moved our custom user model because of a
circular import problem. (Pro-tip #1: put your custom user model in an app
that doesn't depend on anything else. Perhaps we should document that.) We
papered over the resulting mess by squashing migrations and life was good.
(Pro-tip #2: if you change AUTH_USER_MODEL, throw your migrations history away
and regenerate it. It's easy. We should definitely document how to do that.)

A few weeks ago, we ran into issues I can't describe exactly when generating
or applying new migrations. I'm sorry, I don't remember the details because
I'm not monitoring development very closely. Judging by how much time had been
spent on the issue and how messy it looked, I decided to simply scratch our
migrations history, which we should have done earlier. (See pro-tip #2.)

I wanted to check that the resulting schema matched what we had in production
in order to make sure that I wouldn't introduce inconsistencies. On my
machine, I emptied all migrations folders, ran makemigrations, created a fresh
database, ran migrate, and dumped the schema. Then I grabbed a dump of the
production schema. The dumps were about 11 000 lines long. The diff produced
by apgdiff -- a great tool -- was 2 000 lines long.

Much of the diff was noise created by non-deterministic index and constraint
names. I'm almost sure we fixed that in 1.8. I hacked a script to renormalize
hashes and was still left with significant differences. Oops.

These differences happen when Django produces a different schema:

- if you simply create a model
- if you make a series of changes that result in the same model

These differences appear not only when you reset migrations like I did, but
also if you squash migrations, since the squashed migration is pretty much a
fresh migration that declares that it replaces a bunch of previous migrations.
The history of migrations runs a series of alterations. The squashed migration
runs a simple creation.

Here are the differences I found and couldn't explain by problems in our code:

- Sequences aren't handled correctly when changing an AutoField into an
IntegerField. I filed #24533. This could result in different behavior in
dev/test and production, which I find dangerous. I could probably present it
as a crashing or data loss issue.

- Some unique indexes on OneToOne fields were missing. This may be a variant
of #24163. I haven't investigated it fully. This is about as bad as the
previous one.

- Many varchar_pattern_ops indexes were missing in production. See #23954.
This could result in performance issues.

- I had a constraint generated twice locally, once in prod. I'm not sure why.

Given that basic use of makemigrations & migrate can result in significant
errors in the resulting database schema, in the current state of migrations,
we cannot promote them as the single way to manage the database schema. The
risk of not creating the expected schema -- or, worse, not having created the
expected schema in the past -- is simply too high. Every project that ever
used 1.7 or 1.7.1 suffers from such issues.

Therefore I think we must document how our users with basic needs, like
myself, can obtain the reference database schema corresponding to their
models, assuming that they never used RunSQL or that they know what they
did with it.

I’m arguing that there’s a practical need here and practicality beats purity.

--
Aymeric.

[Marc Tamlyn]

As far as I'm aware, we have some code paths which still work in 1.9 which generate tables directly from the models. We use this when running Django's own test suite, and it is also used now by djangobench. I haven't looked into exactly how to turn this into logged SQL rather than run SQL but it should be possible.

I think we should document such a tool as a debugging tool rather than a development tool, but it would be useful nonetheless. Something like `sqlmigrate --from-clean` might be appropriate.

Marc

--
You received this message because you are subscribed to the Google Groups "Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/D3FF8860-764D-48EB-AAEE-8CE64B9ABED5%40polytechnique.org.

[Pascal Chambon]

Hello,

sorry for the delay (I'm moving home),

thanks for the detailed explanations, I've learned a lot about the new status of migrations, and I guess every django users might benefit from this "shift of canonical representation", that's why there shouldbe room for a proper introduction in django docs, explaining all that and especially discarding the belief (which I guess is true, for example, in Sqlalchemy) that ORM classes ARE the source of all other schema representations.

I should draft something like that, but before that, we should advance on the points raised previously.

Migrations are a very complex subject (to me at least), and there is a risk of incoherence between the SQL DB schema (as altered by migrations), and what the ORM sees via ORM classes. So having public debugging tools, as proposed by Marc and Aymeric, would be very appreciable. It could be:

- an SQL dump of the structure of ORM classes (we would just have to explain that it's an INCOMPLETE view of what might be in real DB, due to RUNSQL/RUNPYTHON and the like) ; a separate command would be better I think, so that people understand it's only "hints".

- an autocheck, which ensures for example that at least simple selects() on all known columns fields don't crash, and if possible a column-type check too ; I added somethings like this to an SQLAlchemy-based soft, dunno how hard it'd be in Django, but it saved my life on numerous occasions.

Do these propositions sound acceptable ?

regards,

Pascal

--
You received this message because you are subscribed to a topic in the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-developers/h92CcblbYfk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-develop...@googlegroups.com.

To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAMwjO1F8VO04xEG9-PEP7SojvG%2BZmtGZHbhpV12f%3DFEZKEBJ2A%40mail.gmail.com.

[Aymeric Augustin]

2015-04-02 23:03 GMT+02:00 Marc Tamlyn <marc....@gmail.com>:

As far as I'm aware, we have some code paths which still work in 1.9 which generate tables directly from the models. We use this when running Django's own test suite, and it is also used now by djangobench. I haven't looked into exactly how to turn this into logged SQL rather than run SQL but it should be possible.

An alternative would be to ignore migrations files, regenerate a fresh set of migrations, and dump the corresponding SQL.

--

Aymeric.

[Carl Meyer]

On 04/03/2015 03:17 AM, Aymeric Augustin wrote:
> 2015-04-02 23:03 GMT+02:00 Marc Tamlyn <marc....@gmail.com

> <mailto:marc....@gmail.com>>:

I think this approach would be much preferable to using the totally
separate legacy code path. Presented as a tool for debugging migrations
issues, and with the appropriate documentation notes about RunSQL etc, I
think it would be a useful addition.

Carl

[Andrew Godwin]

>
> An alternative would be to ignore migrations files, regenerate a fresh
> set of migrations, and dump the corresponding SQL.

I think this approach would be much preferable to using the totally
separate legacy code path. Presented as a tool for debugging migrations
issues, and with the appropriate documentation notes about RunSQL etc, I
think it would be a useful addition.

This is in fact what I was suggesting when I said "tie the autodetector into the SQL writer directly in-memory". It would do 99% of what people want, and more than the old one did.

Andrew

[Collin Anderson]

And the auto-generated in-memory migrations should work for tests too, right?

[Carl Meyer]

On 04/03/2015 02:30 PM, Collin Anderson wrote:
> And the auto-generated in-memory migrations should work for tests too,
> right?

Well, that would depend on whether you have anything important in a
RunSQL migration, or otherwise in a migration but not reflected in your
model files. So, maybe. But if we had that technique, it could be
available as an option for tests.

Carl

[Marcin Nowak]

On Monday, March 30, 2015 at 1:58:02 AM UTC+2, Russell Keith-Magee wrote:

What is the new way to dump the sql schema of currently installed django appz ? It'd maybe be worth that I provide a doc patch to inform users about it.
If there is none, is there an agreement to restore at least sqlall and sqlclear ?

 

No, there isn't :-)

We need old sql /sqlall behaviour. Currently we can't generate sql for contrib apps. 

Calling `sql auth` raises error:

CommandError: App 'auth' has migrations. Only the sqlmigrate and sqlflush commands can be used when an app has migrations.

We're expecting SQL output instead.

We need to bypass migration mechanism for some specific projects. 

Please make life easier but not harder. Django is unusable from v1.7 for some of us! 

Or please give us hint how to achieve old behaviour.

/BR

Marcin

[Tim Graham]

There's a ticket waiting someone to implement what has been discussed:

https://code.djangoproject.com/ticket/24481

[Marcin Nowak]

Thanks, I didn't saw it.

But I've found quick&dirty ad-hoc solution using monkey patching.

Just include this snippet in project`s __init__:

from django.core.management import sql

def check_for_migrations_bypassed(*args, **kw):

    pass

sql.check_for_migrations = check_for_migration_bypassed

(Tested with v1.8)

/BR

Marcin

[Marcin Nowak]

Tim,

I've just realised that there is no sql* commands in the newest Django.

Generating SQL from models was very useful. Why do you decided to drop that functionality? Because of builit-in migrations? 

Can you just bring them back? 

Thanks.

Marcin.

[Tim Graham]

They were dropped as part of the removal of the old code that supported syncing apps without migrations.

https://github.com/django/django/commit/7e8cf74dc74539f40f4cea53c1e8bba82791fcb6

You could probably reimplement them at least to some extent using the new schema backends. By the way, I suspect this could be implemented as a third-party app if there is opposition to including it in Django itself.

[Marcin Nowak]

On Sunday, May 31, 2015 at 1:47:32 AM UTC+2, Tim Graham wrote:

They were dropped as part of the removal of the old code that supported syncing apps without migrations.

But you removed a feature, not just old code. 

In v1.8 (1.7 maybe?) this feature was broken (issue #24876) and it was completely removed in master (issue #24481).

This is not good.

 

By the way, I suspect this could be implemented as a third-party app if there is opposition to including it in Django itself.

 

I know I can reimplement it myself, but core feature should be nearest to core source code, because it highly depends on internals.

This can be provided as a contrib app, for example. 

Why contrib.gis is supported for years? This app is used rarely. In contrast to gis - we generate/create SQLs every day. 

I don't know what you're doing with Django, but latests changes looks fearfull.. :(

/BR

Marcin

[Tim Graham]

As far as justification for dropping the old commands:

1. Andrew said earlier in the thread, "the sqlmigrate command will still get you SQL from migrations, though it sadly lacks the ability to take a range of migrations, optimise them down, and output the SQL for _that_ - that's probably the best thing for us to aim towards, and will almost entirely recreate the functionality of sqlall without actually being wrong (sqlall doesn't understand things like data migrations or custom SQL sections of migrations, and so will sadly be wrong in if you have anything like a moderately complex migration set)."

2. The sql* commands that were removed relied on SQL generation code in django/db/backends/base/creation.py and has been superseded by similar functionality in schema.py (and removed in 1.9). Django shouldn't maintain two ways of generating SQL for models.

We welcome your contribution if you can form a consensus on a proposal.

[Andrew Godwin]

OK, so I've just gone ahead and done the initial work on this: https://github.com/django/django/pull/4729

I'd appreciate testing by people to see how well that new sql command works; it's fine for me in dev here against a simple project but there's probably some edge cases. Nevertheless, it seems to prove the idea and is also the way we can handle test database setup potentially, too.

--

You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/ac575d9c-eeb4-473b-a0ca-fcc73051b903%40googlegroups.com.

[Marcin Nowak]

On Monday, June 1, 2015 at 5:11:27 PM UTC+2, Andrew Godwin wrote:

OK, so I've just gone ahead and done the initial work on this: https://github.com/django/django/pull/4729

They shot not only in my head - http://stackoverflow.com/questions/35455706/equivalent-of-sqlall-in-django-1-9

Generating sql from migrations is useful for translating python migrations into SQL (the only one good way to handle database schema).

But old `sql` command generates SQL directly from models, so an application must not have new migrations defined.

The old-style commands are still required. I heard from my fellows that they are downgrading their projects from Django 1.9 to 1.8 due to missing sql commands. Not so funny.

Marcin

[Tim Graham]

Marcin, what are you hoping to accomplish with your latest mail? As Aymeric said in another thread, repeated complaining is not going to help. There's already an accepted ticket for this feature. If you want to be productive and helpful, pick up Andrew's patch and try to complete it. Alternatively, if the business needs justify it, you could offer to pay someone to implement it if that suits you better. Tim

[Marcin Nowak]

On Tuesday, June 21, 2016 at 3:28:26 PM UTC+2, Tim Graham wrote:

Marcin, what are you hoping to accomplish with your latest mail? As Aymeric said in another thread, repeated complaining is not going to help.

Sorry for that. I'm just overhelmed and tired of "fighting" with missing things which were good, then removed, and now someone should reimplement them again.

 

There's already an accepted ticket for this feature. If you want to be productive and helpful, pick up Andrew's patch and try to complete it. Alternatively, if the business needs justify it, you could offer to pay someone to implement it if that suits you better. Tim

Well, I'll do something with pleasure. And please don't worry about money. 

But I will not give any patch until you're rejecting change at a concept/design stage.

The old sql* commands were good and helpful. To bring them back someone must "revert" commits with removals, so there will be an old code generating SQL directly from models (ommiting builitin migrations). Do you agree?

 

Marcin.

[Tim Graham]

No, it's not as simple as reverting the removal commit [0]. All the legacy schema generation methods are removed too [1]. We don't want to add them back but rather use the SQL generation from the migrations system (SchemaEditor classes). Please read this thread closely and look at Andrew's pull request [2].

[0] https://github.com/django/django/commit/7e8cf74dc74539f40f4cea53c1e8bba82791fcb6
[1] https://github.com/django/django/commit/2b039d966f6e61a5ffb5ffac25aa198f9043de3d
[2] https://github.com/django/django/pull/4729

[Marcin Nowak]

On Tuesday, June 21, 2016 at 4:00:51 PM UTC+2, Tim Graham wrote:

No, it's not as simple as reverting the removal commit [0]. All the legacy schema generation methods are removed too [1]. 

We don't want to add them back

I know.  

 

but rather use the SQL generation from the migrations system (SchemaEditor classes). Please read this thread closely and look at Andrew's pull request [2].

It is complicated for such simple job (dumping model schema to sql) but I'll give a try, just later. 

Currently I must backport JSONField from 1.9 to 1.8, because I can't upgrade 1.8.6 to 1.9, because it has removed `sql*` commands.  

 

Marcin.

[Marcin Nowak]

On Tuesday, June 21, 2016 at 5:49:08 PM UTC+2, Marcin Nowak wrote:

On Tuesday, June 21, 2016 at 4:00:51 PM UTC+2, Tim Graham wrote:

No, it's not as simple as reverting the removal commit [0]. All the legacy schema generation methods are removed too [1]. 

We don't want to add them back

I know.  

 

but rather use the SQL generation from the migrations system (SchemaEditor classes). Please read this thread closely and look at Andrew's pull request [2].

It is complicated for such simple job (dumping model schema to sql) but I'll give a try, just later. 

No, Tim. I will not go through the Andrew's path. It is a "dead end".  

What the `sql` command should do is just simple dump model meta to desired sql dialect.

Migrations are designed to manage and apply model changes, so it is quite different use case. 

`sql` command should not depend on migrations, because:

• they may be corrupted  (common case with south/django migrations; they are unreliable)
• achieving final state is a long journey through winding road,
• app may not include migrations itself,
• it is about SQL but not about managing model state/changes.

The only way is to fix SchemaEditor by extractnig sql from create_model(), without executing it.  But it's not so easy, because SE requires to be run as a context manager and executes deffered sqls at __exit__().  There are other SE`s methods which depends on defferend sql execution at `__exit__()`. Sorry, but it looks like code written in big hurry.

If SchemaEditor will not be removed in near future, I can try to patch it and use as a base of new `sql` command. I think there is no other reasonable way to do that. 

Somebody wrote eariler that `sql*` commands can be written as an external app/package. They can't. Django internals must be fixed before this, or new app must provide complete solution including sql generator like SE (but this is a second nonsense, because SE is strongly coupled with db backends).

And finally I would like to remind you, that `sql*` commands were removed by you. This was a mistake. Not the first, not the last.  But when you're asking me what I'm hoping writing such mails, just please stand in my (and others) shoes and think a little about it.

Marcin

[Marcin Nowak]

On Tuesday, June 21, 2016 at 10:55:13 PM UTC+2, Marcin Nowak wrote:

Somebody wrote eariler that `sql*` commands can be written as an external app/package. They can't. Django internals must be fixed before this, or new app must provide complete solution including sql generator like SE (but this is a second nonsense, because SE is strongly coupled with db backends).

Errata - I've missed "collect_sql" mode, where sql is not executed but collected. The patching should not be neccessary. The name of "execute()" method is misleading in such context. 

Marcin

[Shai Berger]

Marcin,

You can write the "sqlall" you want as a shell script -- remove any existing
migrations, use "makemigrations" to create an initial migratgion, which will
exactly reflect your models, and run sqlmigrate on that. You want to do that
against a clean database, to make sure no migration has already been
registered for the app in the migration history table.

I repeat Aymeric's advice that spreading your frustrations over this mailing
list is counter-productive.

HTH,
Shai.

[Marcin Nowak]

You can write the "sqlall" you want as a shell script -- remove any existing
migrations,  use "makemigrations" to create an initial migratgion,

Not exactly. I'm not using migrations [.. cut ..]  

 

I repeat Aymeric's advice that spreading your frustrations over this mailing
list is counter-productive.

Same as removing useful tools. Let's remove `runserver` command for a year or two, ok?

In the other thread I just gave advise to guy, who is going through path similar to mine. 

[.. cut ..]  

Marcin

[Shai Berger]

On Wednesday 22 June 2016 00:48:58 Marcin Nowak wrote:
> > You can write the "sqlall" you want as a shell script -- remove any
> > existing
> > migrations, use "makemigrations" to create an initial migratgion,
>
> Not exactly. I'm not using migrations [.. cut ..]
>

So add another step in the end to remove the temporary migrations folder
created just in order to enable sqlmigrate. If you're a real purist, add yet
another one to make sure you don't have a django_migrations table left over.

Really, this is django-users turf.

Shai.

[Marcin Nowak]

So add another step in the end to remove the temporary migrations folder
created just in order to enable sqlmigrate. If you're a real purist, add yet
another one to make sure you don't have a django_migrations table left over.

Sorry, Shai, I doubt you understand the problem.

Really, this is django-users turf. 

Do what you want. IMO it isn't.

I'm talking here about ressurecting killed tools. And currently I'm working on a patch.

I think that redirecting discussion about Django internals to django-users list is more counter productive than my earlier thoughts. 

PS. I'm not a purist, but I don't like django_migrations table because it is created automatically in foreign database, which Django should not touch without direct command from dba architect.

   

Marcin

[Tim Graham]

I'll be happy to look at whatever you come up with or review whatever changes you think need to be made in Django to solve the ticket, however, I fail to understand how Andrew's proposal isn't suitable. Maybe you could give more specific details than "migrations may be corrupted" and "they are unreliable". As far as I see, Andrew's proposal doesn't use any on-disk migrations so it doesn't require an app to have migrations.

Yes, I did remove the sql* commands because they were scheduled for removal when the migrations system was introduced. We didn't want to maintain two methods for generating schema. Your previous mail was just rehashing old complaints about the lack of this functionality. If I were in your shoes and needed this functionality, I would get involved and try to engage in a discussion to learn about the reasons for this. Then I would make a proposal for how to solve the problem and then implement it. I wouldn't complain about it, disparage the design of the Django, and expect someone else to write the code that I need. Anyway, it sounds like you're off on the right path now.

You have a lot of interesting ideas about where Django could be improved but please share them in a polite and constructive fashion. Avoid pointless statements like "Sorry, but it looks like code written in big hurry" which only serve to offend the people who spend many hours contributing to Django. If you continue down that path, I guess all we can do is ignore you because we don't like to work with people who can't be polite.

[Marcin Nowak]

I can give you many reasons and many details, but I know you will not agree with my conclusions. I remember what you (or someone from the team) said about my ideas. I've shared them already and someone told me why you've decided to do it that way, and why you wouldn't change it. I'm respecting your visions and ideas, but they are becoming more and more distant from mine. And it is OK until you're introducing obstacles (from my perspective, just a regular user). Removing daily used tool is an obstacle, believe me. 

Let's back to the migrations. They are very sensitive to Python imports, dependencies, and used objects.  Some time ago I've got a project to rescue, where deps were poorly managed and the code was trashy. After cleaning up, some apps couldn't migrate and Django was creating initial migration instead of adding migration with differences. If I remember the problem was related to some import error or some missing object. But it does not matter here. The worst thing was that Django skipped all migrations and tried to do somenthing unplanned, something that could damage the database, and something that damaged initial migration file. Just because some old and already applied migration has an some python error. And this is practically uncontrollable and unreliable behavior. 

What output will generate `sql` command based on schema migrations in such case? Should it really be dependent on the complicated system, where it is created to translate current state of model's metadata to DDL? I don't think so. I agree with you that there should be one way to do something. But I'm not sure that the results and use cases of "sql*" commands are same as for migrations.

Schema changes are about adding, removing or altering something in the database. Application layer issues shouldn't affect the sequence of DDL changes. Migration system can be written in Python (as a part of Django), but changesets should not be dependent on any part of an application layer. There is a one fundamental reason for this approach - the application layer is chagning more often than the database (both for schema and data). That's why it is so important for long-term projects. I don't like saying that, but for my 16 yr pro experience I saw this rule almost in every system. Then look at Django which is forcing you to use it's migrations, highly coupled with application layer, sometimes failing, and "owning" my old-and-very-important database... Do you understand now why I'm suggesting Django (from 1.7+) for short-term and smaller (simpler in terms of a complexity) systems? I'm not saying that they are completely wrong. They have advantages and the most I like is autodetection of model changes.

`sql*` commands were decent tools for translating model metadata to DDL. It was very handy to prototype the app layer and generate DDL. Then DDL was used directly in db schema management system. In this scenario we aren't talking about creating Django' migrations, especially not about tracking model changes. "sql*" commands were always releated to the current schema, so there is no necessity to change this. So whenever I'm talking about "sql*" command, I'm thinking about model's current schema and similar use cases.  I'm asking myself again: Should it be dependent on the migration system? What benefits it will give? Is it worth implementing simple function based on such complicated migration engine? How much it will cost? Who will use it? 

The reason of removing two differenct ways of managing db schema is clear for me. I just do not understand removing the tool without providing decent replacement for it.  For example, CBV generics were replacement for FBV generics. There was an incompatibility in the interface, but the generic views functionality was saved. For "sql*" commands there is no replacement.  I think that you're assuming that builtin migrations covers all use cases and you aren't worrying about projects, where migrations aren't used. I understand this, but it narrows the flexibility of Django.

Believe me or not... Whenever fellows asks me "what instead of Django?", I'm answering - Django.  I'm pointing (not disparaging) weakness just because I care and because I'm using Django for a very long time.  Django is good, but may be better. I would like to contribute and make it better, but I'm afraid that my ideas would be dropped due to some kind of "Django ethos". The most counter productive for me is rejecting the idea, then implementing it after X years. It was happened few times for me and I don't fully understand that. 

 

I'm with Django from v0.96. The things went little wrong starting from 1.7, IMO. Some guys on this forum told me, that I'm using Django bad way and it is possible that Django does not fit my needs. I can agree, but older versions were fitting my needs pretty well. My needs are still same, so what's happened? As a result I'm just giving advices like "do not use Django in such project[, because it would not fit your needs]", same as others gave me. I'm not polite? Could be. When you're forced to di something "wrong" way, or when you're kicked out ("stay with 1.4"), and when you can't move away, you can feel a little irritated. I'm sorry about that. Honestly.

Please don't get this personally and do not treat this as a complains. I've just wanted to share my POV. 

I'll pick up your suggestions about being more constructive. 

Marcin

[ludovic coues]

2016-06-22 14:13 GMT+02:00 Marcin Nowak <marcin....@gmail.com>:
>
> The reason of removing two differenct ways of managing db schema is clear
> for me. I just do not understand removing the tool without providing decent
> replacement for it. For example, CBV generics were replacement for FBV
> generics. There was an incompatibility in the interface, but the generic
> views functionality was saved. For "sql*" commands there is no replacement.

I see two reason for not replacing the sql* commands. Lack of interest
and lack of time.

There is a finite amount of time available from volunteer to work on
the project. This time is allocated according to what people want to
do and what is the best for most of the user. I hope.
If theses commands have been dropped, I believe it means they were not
needed by most of the user and they could not be kept without a
non-trivial amount of work.

--

Cordialement, Coues Ludovic
+336 148 743 42

[Shai Berger]

Hi again Marcin,

Thanks for this thoughtful clarification. I think I understand your position
much better now. If I understand correctly, there are two issues you find with
migrations:

- They are designed to deal with schema changes, while you'd rather have a
tool for one-time schema generation;

- Migration files are Python code with potential dependencies, while you prefer
your schema (and even data) changes to be expressed in pure SQL.

In view of the above, your discontent at the removal of the sql* management
commands is quite obvious, but there are two issues that are still unclear to
me, and I think would be helpful if you can explain:

On Wednesday 22 June 2016 15:13:18 Marcin Nowak wrote:
> It was very handy to prototype the app layer and generate DDL. Then DDL was
> used directly in db schema management system.

- Isn't it, then, possible to generate a schema by evolving it while
prototyping on your development machine, and when done, use the db schema
management tools to extract the DDL directly from the database? Isn't it
actually better than generating DDL in Python, according to your views?

On Tuesday, June 21, 2016 at 5:49:08 PM UTC+2, Marcin Nowak wrote:
> I will not go through the Andrew's path. It is a "dead end".

> [...]

> `sql` command should not depend on migrations, because:

> - they may be corrupted (common case with south/django migrations; they
> are unreliable)
> - achieving final state is a long journey through winding road,
> - app may not include migrations itself,
> - it is about SQL but not about managing model state/changes.

- But Andrew's patch[1] completely ignores any existing migration files,
bypassing all problems related to migration files as well as the "journey
through winding road". What he suggests is to use the migrations
infrastructure to generate just the SQL for the difference from "nothing" to
"current models" (so, no "model state changes" either). Even the issue of
SchemaEditor's deferred_sql is handled by "collect_sql" mode, as you've
already noted yourself (and by the way, the deferring mechanism is required in
order to resolve circular dependencies between models). Do you have other
objections to this patch's approach?

Please feel free to correct me if I misrepresented your views. My goal here is
to try to help find the best way to handle the issues you raised, as I'm sure
you're not alone in your positions.

Thanks,
Shai.

[1] https://github.com/django/django/pull/4729

[Marcin Nowak]

On Thursday, June 23, 2016 at 12:12:51 AM UTC+2, Shai Berger wrote:

Thanks for this thoughtful clarification. I think I understand your position
much better now. If

Thanks for reading, Shai. I know my English is far from perfect, so I appreciate your involvement.

I understand correctly, there are two issues you find with
migrations:

- They are designed to deal with schema changes, while you'd rather have a
tool for one-time schema generation;

In most cases, yes. But I see now new possibilities (I'll wrote about my thoughts another time)

 

- Migration files are Python code with potential dependencies, while you prefer
your schema (and even data) changes to be expressed in pure SQL.

This is just one of all reasons, but yes.

  

> It was very handy to prototype the app layer and generate DDL. Then DDL was
> used directly in db schema management system.

- Isn't it, then, possible to generate a schema by evolving it while
prototyping on your development machine, and when done, use the db schema
management tools to extract the DDL directly from the database? Isn't it
actually better than generating DDL in Python, according to your views?

It depends on what "better" means. Django is very helpful for generating initial sqls, especially for indexes and foreign keys.  Further changes we make using db mgmt tool. This is part of our workflow for years - prototype python code, make some tests, generate sql for new models, make additional sqls (views, triggers, etc), finish feature, send to qa, deliver. 

Writing Django models in Python is faster than writing plain SQL or using db GUI tools. And we are writing them just only once. Using other DDL tools and making diffs between databases will slow down our workflow. We're accepting Django's table/columns/m2m/constraints naming conventions, and we're accepting all limitiations. The db schema is compatible with Django. We didn't found faster method yet (except Django's changes autodetection).

- But Andrew's patch[1] completely ignores any existing migration files,
bypassing all problems related to migration files as well as the "journey
through winding road".

If it will not use migration files when they already exists, it sounds ok.

I know too little about migations internals, so I can't be sure for now. 

 

What he suggests is to use the migrations
infrastructure to generate just the SQL for the difference from "nothing" to
"current models" (so, no "model state changes" either).

I thought that diff from nothing to current will be achieved by applying migrations sequentially.

It sound ok if this will be ommitted.  

 

Even the issue of
SchemaEditor's deferred_sql is handled by "collect_sql" mode, as you've
already noted yourself (and by the way, the deferring mechanism is required in
order to resolve circular dependencies between models). Do you have other
objections to this patch's approach?

Well.. yes and no.

I just have almost finished patch which brings back sql* commads based directly on SchemaEditor. The difference is in ommitting django.db.migrations completely, so there is straightforward path to get current db state (without using whole complex migrations abstraction layer), less dependencies and less risk of failure due to some db.migrations issues/regression. The drawback is that output sql may little differ comparing to output from migrations-based alternative, even if both methods will use same SchemaEditor. There also will be new association to SE, so further changes of SE will require attention not only to migrations subsystem. Please share your thougths because I can miss something.

I don't how important is to use django.db.migrations for every sql-related use case. It is about your preferences and design decisions. For me it is not a problem until both (sql* and migrations) depends on same sql factory (SchemaEditor here). For me, the sql* world is quite different from the migrations, and it is ok to not mix up these two things. The cost of maintaining additional association seems to be low, but I might be wrong.     

The "dead end" term used by me in the context of Andrew's patch was mostly related to the migration files issues. Having already better knoweldge I will look at his pull rq again.

Marcin

[Aymeric Augustin]

Hi Marcin,

Thanks for taking the time to clarify constructively your use case.

On 23 Jun 2016, at 13:10, Marcin Nowak <marcin....@gmail.com> wrote:
> If it will not use migration files when they already exists, it sounds ok.

I believe that this point was a big misunderstanding in the discussion.

As far as I understand, Andrew’s proposal works pretty much like Django’s
former SQL generation tools, except it builds upon the robust abstractions
that support the migrations system — specifically the schema editor class,
which is really better at generating SQL that the pre-1.7 implementation.

Since migrations are designed to perform schema changes, we have to ask
"create the SQL to turn <nothing> into <current state of models>" instead of
"create the SQL to generate <current state of models>". The result is the
same, however: Django will introspect the current state of models, build an
in-memory representation of them, and generate the corresponding SQL.
This doesn’t require writing out migration files to disk.

So, the only outstanding issue is to complete the patch that implements this
feature :-)

Best regards,

--
Aymeric.