Relation lost for tables in schema

[yabb85]

Hi,

I work with sqlalchemy on PostgreSQL database and we use schema to classify our tables. erlalchemy2 lost relation in this case because in function relation_to_intermediary the left_col use table name without schema name.

A quick fix to use correct name in left_col but not tested on another environment than PostgreSQL.

if len(fk._column_tokens) == 2:
    left_col = format_name(fk._column_tokens[1])
else:
    left_col = format_name(f'{fk._column_tokens[0]}.{fk._column_tokens[1]}')

[maurerle]

Can you provide a Minimum working example (MWE) of how you are using this?

Might also be helpful for #18 which is about support for multiple schemas at once? I currently do not have time to reproduce this, but will do in the future.

[qwenger]

Same here.

MWE attempt

CREATE SCHEMA someschema;
CREATE TABLE someschema.sometable (
  id INTEGER NOT NULL,
  CONSTRAINT sometableid PRIMARY KEY (id)
);
CREATE TABLE someschema.someothertable (
  id INTEGER NOT NULL,
  tid INTEGER,
  CONSTRAINT someothertableid PRIMARY KEY (id),
  CONSTRAINT someothertabletid FOREIGN KEY (tid) REFERENCES someschema.sometable (id)
);

from eralchemy2 import render_er
render_er("postgresql+psycopg2://user:password@host:5432/db", "tmp.png", schema="someschema")

NOTE

I'm not sure that OP's fix is correct. AFAICS in https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/sql/schema.py#L3081, _column_tokens should always return a 3-element tuple, but the first item (the schema) can be None. Instead I think that the more correct way is (not tested on other DB backends or without schema)

def relation_to_intermediary(fk: sa.ForeignKey) -> Relation:
    """Transform an SQLAlchemy ForeignKey object to its intermediary representation."""
    return Relation(
        right_col=format_name(fk.parent.table.fullname),
        left_col=format_name(fk.column.table.fullname),
        right_cardinality="1" if fk.parent.primary_key or fk.parent.unique else "*",
        left_cardinality="?" if fk.parent.nullable else "1",
    )

(Which also avoids relying on the internal _column_tokens attribute.)

EDIT: also fixed a typo in the docstring.