Wrong order for table drop

[antoyo]

trafficstars

Hi. It seems the order of tables with relations is wrong.

For instance, it's trying to do:

  DROP TABLE "payer"; 
  DROP TABLE "transaction";

for the following models:

class Payer(BaseModel):
    pass


class Transaction(BaseModel):
    payer = ForeignKeyField(Payer, backref="transactions")

(which were deleted from the code)

Actually, it seems random, because a subsequent run was in the right order:

  DROP TABLE "transaction"; 
  DROP TABLE "payer";

So, I guess the tool does not check for relations in the database tables. Thanks to fix this.

[keredson]

Hmm, the order shouldn't matter because the constraint is dropped before the table. Did it not drop the constraint first?

Thanks for reporting!

On Mon, Aug 3, 2020, 5:57 PM antoyo [email protected] wrote:

Hi. It seems the order of tables with relations is wrong.

For instance, it's trying to do:

DROP TABLE "payer"; DROP TABLE "transaction";

for the following models:

class Payer(BaseModel): pass

class Transaction(BaseModel): payer = ForeignKeyField(Payer, backref="transactions")

(which were deleted from the code)

Actually, it seems random, because a subsequent run was in the right order:

DROP TABLE "transaction"; DROP TABLE "payer";

So, I guess the tool does not check for relations in the database tables. Thanks to fix this.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/keredson/peewee-db-evolve/issues/50, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAPUNANGF2DU6KHGWDGQTRTR65MIZANCNFSM4PT3XJMQ .

[antoyo]

It does not seem so:

  BEGIN TRANSACTION;

  DROP TABLE "payer"; 
  DROP TABLE "transaction"; 

  COMMIT;

So, here's a reproducer for this bug:

from peewee import AutoField, ForeignKeyField, Model, PostgresqlDatabase
import peeweedbevolve


db = PostgresqlDatabase(
    "test",
    user="test",
    password="",
    host="localhost",
    port=5432
)


class BaseModel(Model):
    class Meta:
        database = db

class Payer(BaseModel):
    id = AutoField()


class Transaction(BaseModel):
    id = AutoField()
    payer = ForeignKeyField(Payer, backref="transactions")

db.evolve(ignore_tables=["basemodel"])

First create the tables by running this script. Then, comment the classes Payer and Transaction and run the script again. If it wants to drop transaction before payer, answer no (because it works correctly). Rerun the script until it asks you to drop payer before transaction and answer yes.

Here's my output:

Making updates to database: test

Your database needs the following changes:

  BEGIN TRANSACTION;

  DROP TABLE "payer"; 
  DROP TABLE "transaction"; 

  COMMIT;

Do you want to run these commands? (type yes, no or test) yes
Running in 3... 2... 1... 

 DROP TABLE "payer"; 

------------------------------------------
 SQL EXCEPTION - ROLLING BACK ALL CHANGES
------------------------------------------

Traceback (most recent call last):
  File "/home/antoyo/.local/lib/python3.8/site-packages/peewee.py", line 3099, in execute_sql
    cursor.execute(sql, params or ())
psycopg2.errors.DependentObjectsStillExist: ERREUR:  n'a pas pu supprimer table payer car d'autres objets en dépendent
DETAIL:  contrainte fk_transaction_payer_id_refs_payer sur table transaction dépend de table payer
HINT:  Utilisez DROP ... CASCADE pour supprimer aussi les objets dépendants.


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "main.py", line 26, in <module>
    db.evolve(ignore_tables=["basemodel"])
  File "/home/antoyo/.local/lib/python3.8/site-packages/peeweedbevolve.py", line 738, in evolve
    _execute(db, to_run, interactive=interactive, commit=commit)
  File "/home/antoyo/.local/lib/python3.8/site-packages/peeweedbevolve.py", line 765, in _execute
    raise e
  File "/home/antoyo/.local/lib/python3.8/site-packages/peeweedbevolve.py", line 748, in _execute
    db.execute_sql(sql, params)
  File "/home/antoyo/.local/lib/python3.8/site-packages/peewee.py", line 3106, in execute_sql
    self.commit()
  File "/home/antoyo/.local/lib/python3.8/site-packages/peewee.py", line 2873, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/antoyo/.local/lib/python3.8/site-packages/peewee.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "/home/antoyo/.local/lib/python3.8/site-packages/peewee.py", line 3099, in execute_sql
    cursor.execute(sql, params or ())
peewee.InternalError: ERREUR:  n'a pas pu supprimer table payer car d'autres objets en dépendent
DETAIL:  contrainte fk_transaction_payer_id_refs_payer sur table transaction dépend de table payer
HINT:  Utilisez DROP ... CASCADE pour supprimer aussi les objets dépendants.

(Sorry, the output is in French and I don't know how to make it in english as LANG=en does not work for this script.)

[antoyo]

Any news about this?