Issue: I want to change order of table creation for create_tables()
Author: maaaks
State: closed
Created: 2016-09-22 10:45:45

Basically, I need to make Peewee know that some of my tables are [inherited](https://www.postgresql.org/docs/current/static/ddl-inherit.html) from other tables, and this means that the «parents» should be created before «children».

Can you please move some logic out from `sort_models_topologically()`, so that I could override it in my classes and customize list of a table's dependencies (which now includes only those tables to which it has foreign keys referencing)? Or, could you make support for inheritance in Peewee natively?


As I can see, you don't want very much to support inheritance: #616

Well, a unified way to override _any_ dependencies would be even better, because I also have some cases where one table depends on existence of another because of a manually-written (via `SQL()`) constraint, which `sort_models_topologically()` also is not aware of.


I'm not sure how sensible it is to provide a way to override such a fundamental algorithm, but it might work to add an optional "depends_on" attribute to the model meta options, that would inform the sorting algorithm. What do you think?


Yes, this would be a good solution.


Hmm...the more I'm thinking about this the less it makes sense. If you have a preexisting schema, then it's not like you're going to be creating and dropping peewee tables all over the place. It seems more likely that you'd create your tables once in production, on top of the preexisting schema. Right?


I don't really understand the question. Really.

My problem is that some tables should be created after other ones are already created. One example is when table B inherits table A. Another one is when table B has a non-trivial foreign key which refers to columns from table A. In both cases, table creation in wrong order will fail, because PostgreSQL won't know what table A is.

In production, I may already have table A, so yes, sometimes I don't have this problem at all. But when creating a new test environment, I still do need to create all the tables at once, in correct order.

There is a workaround, though, and I use it now:

``` python
db.create_tables(tables1) # tables that don't depend on anything
db.create_tables(tables2) # tables that depend on tables from tables1
db.create_tables(tables3) # tables that depend on tables from tables1 or tables2
```

But I don't like it very much because I have to split list of tables into these «levels» manually.


The nontrivial constraint and test environment are good points. Reopening.


Thank you!
When will this be in the package installable through `pip`? Or, is it already there?


Not on pip yet, but next release will be soon.. I'll shoot for within the next few days


I decided to try it already and installed it:

``` sh
sudo -H pip3 install git+https://github.com/coleifer/peewee.git@master
```

But the order of table creation still looks random and changes from run to run. What is wrong? Should I also somehow uninstall old speedups or something like that?


If it is important, here's override for `create_table()` in my `BaseModel`. It automatically makes the new table inherited from the one referenced in `_meta.parent`.

``` python
class BaseModel(Model):
        database = db
        parent = None

    @classmethod
    def table(cls) -> str:
        return cls._meta.schema + "." + cls._meta.db_table

    @classmethod
    def create_table(cls, fail_silently=False):
        with db.transaction() as transaction:
            try:
                super().create_table()
                if cls._meta.parent:
                    db.execute_sql("alter table "+cls.table()+" inherit "+cls._meta.parent.table())

            except peewee.ProgrammingError as e:
                transaction.rollback()

                if not fail_silently:
                    raise e

                if not re.match(r'^relation "\S+" already exists$', str(e).strip()):
                    raise e
```


Sorry, it was a bug in my code.


Mmm... I'm sorry again, but does it really work? The code doesn't make sense to me.

Here you seem to call `dfs()` on models which _depend on current model_ (am I right?):

```python
for foreign_key in model._meta.reverse_rel.values():
    dfs(foreign_key.model_class)
```

And then tou seem to call `dfs()` on models which _current model depends on_:

```python
if model._meta.depends_on:
    for dependency in model._meta.depends_on:
        dfs(dependency)
```

And it really seems to choose incorrect order of creation for my models.

@coleifer, any news?

Are you seeing errors in the ordering that is generated?

I have seen. I'll try to write a minimal example of what I have in my project some day later, not right now, sorry. :-(

Will I need to open a new issue then?

Looks a lot like it's working right... 76eb220f9b1babc5d2cea85c406454b1f73f99d0

@coleifer, here's what I tried right now:

```python
class D(BaseModel):
    pass

class C(BaseModel):
    d = ForeignKeyField(D)

class B(BaseModel):
    c = ForeignKeyField(C)

class A(BaseModel):
    class Meta:
        depends_on = B,
    
    c = ForeignKeyField(C)

models = [ D, C, B, A ]

ordered = list(models)
for pmodels in permutations(models):
    ordering = sort_models_topologically(pmodels)
    print(", ".join(m._meta.model_class.__name__ for m in ordering))
    assert ordering == ordered
```

This code sometimes successfully runs and prints a series of lines `D, C, B, A` but sometimes it crashes and prints `D, C, A, B`. Please try it multiple times, you'll see. At least, I see it with my Linux Mint 17.3 and Python 3.5.

I am not sure if the anti-alphabetical order or my classes matters, but I couldn't get the fail when they were named alphabetically. Maybe just an accident, I don't know.

The relationships between these classes are pretty much relationships between some of real classes in my project, where Peewee is unable to choose correct order for creating tables. Would be cool to have this fixed, thank you in advance!

I tried that code and it worked every time for me (tried 10K times in a loop) on both python 2.7 and python 3.5.

Lucky you! :-)

Back to [this question](https://github.com/coleifer/peewee/issues/1076#issuecomment-265161314), then: do I really understand everything incorrectly, doesn't that fragment of code even look strange?

BTW, I tried my example not in a loop but by restarting the whole code again and again. Since dictionaries are involved, I suppose some ordering can change on run-to-run basis.

Here, I added the same code I showed before into the unittest. It sometimes runs ok but sometimes fails. I'm not sure how can I make the test consistently failing since sets are involved in the function, so I think we have to run it multiple times manually before seeing that the ordering does not always work properly. Currently I see that it doesn't but I don't know how you should fix it.

Please check this, it seems to fix the problem. By the way, I got rid of the `reversed(reversed())` style of the sorting process because it made harder to understand what's going on.