Hi Mike,
The message has been truncated.
The CREATE TABLE statements is :
CREATE TABLE users (
id BIGSERIAL NOT NULL,
email VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
hashed_password VARCHAR(100) NOT NULL,
is_admin BOOLEAN,
is_active BOOLEAN,
created TIMESTAMP WITHOUT TIME ZONE,
modified TIMESTAMP WITHOUT TIME ZONE,
PRIMARY KEY (id),
UNIQUE (email)
)
CREATE TABLE permissions (
id BIGSERIAL NOT NULL,
name VARCHAR(100) NOT NULL,
description VARCHAR(255),
PRIMARY KEY (id),
UNIQUE (name)
)
CREATE TABLE roles (
id BIGSERIAL NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)
CREATE TABLE users_roles (
users_id BIGINT NOT NULL,
roles_id BIGINT NOT NULL,
PRIMARY KEY (users_id, roles_id),
FOREIGN KEY(users_id) REFERENCES users (id),
FOREIGN KEY(roles_id) REFERENCES roles (id)
)
CREATE TABLE roles_permissions (
roles_id BIGINT NOT NULL,
permissions_id BIGINT NOT NULL,
PRIMARY KEY (roles_id, permissions_id),
FOREIGN KEY(roles_id) REFERENCES roles (id),
FOREIGN KEY(permissions_id) REFERENCES permissions (id)
)
CREATE TABLE users_permissions (
users_id BIGINT NOT NULL,
permissions_id BIGINT NOT NULL,
PRIMARY KEY (users_id, permissions_id),
FOREIGN KEY(users_id) REFERENCES users (id),
FOREIGN KEY(permissions_id) REFERENCES permissions (id)
)
My mistake is to have mixed in my mind the information of the object
mapping and the relational constraints of the database.
But why Alembic can’t create this model in auto-generation mode?
Best regards
Le jeu. 13 déc. 2018 à 18:12, patrick payet <papa...@gmail.com> a écrit :
> PRIMARY KEY (id),
> UNIQUE (email)
> )
>
> CREATE TABLE permissions (
> id BIGSERIAL NOT NULL,
> name VARCHAR(100) NOT NULL,
> description VARCHAR(255),
> PRIMARY KEY (id),
> UNIQUE (name)
> )
>
> CREATE TABLE roles (
> id BIGSERIAL NOT NULL,
> name VARCHAR(100) NOT NULL,
> PRIMARY KEY (id),
> UNIQUE (name)
> )
>
> CREATE TABLE users_roles (
> users_id BIGINT NOT NULL,
> roles_id BIGINT NOT NULL,
> PRIMARY KEY (users_id, roles_id),
> FOREIGN KEY(users_id) REFERENCES users (id),
> FOREIGN KEY(roles_id) REFERENCES roles (id)
> )
>
> CREATE TABLE roles_permissions (
> roles_id BIGINT NOT NULL,
> permissions_id BIGINT NOT NULL,
> PRIMARY KEY (roles_id, permissions_id),
> FOREIGN KEY(roles_id) REFERENCES roles (id),
> FOREIGN KEY(permissions_id) REFERENCES permissions (id)
> )
>
> CREATE TABLE users_permissions (
> users_id BIGINT NOT NULL,
> permissions_id BIGINT NOT NULL,
> PRIMARY KEY (users_id, permissions_id),
> FOREIGN KEY(users_id) REFERENCES users (id),
> FOREIGN KEY(permissions_id) REFERENCES permissions (id)
> )
>
> My mistake is to have mixed in my mind the information of the object
> mapping and the relational constraints of the database.
> But why Alembic can’t create this model in auto-generation mode?
>
> Best regards
>
> Le jeu. 13 déc. 2018 à 14:44, Mike Bayer <mike...@zzzcomputing.com> a
> écrit :
>
>> On Thu, Dec 13, 2018 at 7:11 AM patrick payet <papa...@gmail.com> wrote:
>> >
>> > Hi Mike,
>> > Thanks for your answer.
>> > My problem it’s a classical problem for application development, I want
>> to make authentication and authorisation for a web application.
>> > I want to understand why Alembic can’t create the model in
>> autogeneration mode and how modify this model to resolve this problem.
>> > With this model I have a join table to provide a many-to-many
>> relationship between users and roles (users group) for example.
>> > This allows me to access all roles for one user. For this, I declare a
>> variable roles in users class and I want to make a foreign key with this
>> join table
>> > to reach the roles.
>> > This joins table has a composite primary key (users.id foreign key and
>> roles.id foreign key) and I don’t know how to make the link (foreign
>> key) with alembic for the users.roles and this composite primary key.
>>
>> the link is the user_roles table and the FOREIGN KEY constraints it
>> has between users and roles tables. you can't locate a unique row
>> in user_roles given only the user table because it has no column that
>> refers to user_roles.roles_id. at least I'm trying to parse your
>> words as given. however CREATE TABLE statements will show exactly
>> what you mean in case it is something different that just happens to
>> sound the same when described.
>>
>> When I declare just one element of this primary key, I have the error
>> quoted previously ( there is no unique constraint matching given keys
>> for referenced table "users_roles").
>> > I will send you the CREATE TABLE statements by email in a moment.
>>
>> yup
>>
>>
>>
>> > Best regards,
>> > Patrick
>> >
>> > Le mer. 12 déc. 2018 à 19:40, Mike Bayer <mike...@zzzcomputing.com> a
>> écrit :
>> >>
>> >> On Wed, Dec 12, 2018 at 1:01 PM patrick payet <papa...@gmail.com>
>> wrote:
>> >> >
>> >> > I had a SQLAlchemy model like -
>> >> > <code>
>> >> > class User(DeclarativeBase):
>> >> > __tablename__ = 'users'
>> >> >
>> >> > id = Column(BigInteger, primary_key=True)
>> >> > email = Column(String(100), unique=True, nullable=False)
>> >> > name = Column(String(100), nullable=False)
>> >> > hashed_password = Column(String(100), nullable=False)
>> >> > is_admin = Column(BOOLEAN, default=False)
>> >> > is_active = Column(BOOLEAN, default=True)
>> >> > created = Column(DateTime, default=datetime.now)
>> >> > modified = Column(DateTime, default=datetime.now,
>> onpudate=datetime.datetime.now)
>> >> > roles = relationship('Role', secondary=users_roles,
>> back_populates='users', cascade="all, delete-orphan")
>> >> > permissions = relationship('Permission',
>> secondary=users_permissions, back_populates='users',
>> >> > cascade="all, delete-orphan")
>> >> >
>> >> > def __repr__(self):
>> >> > return "<User(name='%s', email='%s', hashed_password='%s')>"
>> % (self.name, self.email, self.hashed_password)
>> >> >
>> >> >
>> >> > class Role(DeclarativeBase):
>> >> > __tablename__ = 'roles'
>> >> >
>> >> > id = Column(BigInteger, primary_key=True)
>> >> > name = Column(String(100), unique=True, nullable=False)
>> >> > users = relationship('User', secondary=users_roles,
>> back_populates='roles',cascade="all, delete-orphan")
>> >> > permissions = relationship('Permission',
>> secondary=roles_permissions, back_populates='roles',
>> >> > cascade="all, delete-orphan")
>> >> >
>> >> > def __repr__(self):
>> >> > return "<Role(name='%s')>" % self.name
>> >> >
>> >> >
>> >> > class Permission(DeclarativeBase):
>> >> > __tablename__ = 'permissions'
>> >> >
>> >> > id = Column(BigInteger, primary_key=True)
>> >> > name = Column(String(100), unique=True, nullable=False)
>> >> > description = Column(String(255))
>> >> > users = relationship('User', secondary=users_permissions,
>> back_populates='permissions',
>> >> > cascade="all, delete-orphan")
>> >> > roles = relationship('Role', secondary=roles_permissions,
>> back_populates='permissions',
>> >> > cascade="all, delete-orphan")
>> >> >
>> >> > def __repr__(self):
>> >> > return "<Permission(name='%s', description='%s')>" % (
>> self.name, self.description)
>> >> > </code>
>> >> > However, Alembic is not generating the correct upgrade, i try to
>> make it
>> >> > <code>
>> >> >
>> >> > def upgrade():
>> >> > # ### commands auto generated by Alembic - please adjust! ###
>> >> > # op.drop_table('users')
>> >> > # ### end Alembic commands ###
>> >> >
>> >> > op.create_table('roles',
>> >> > sa.Column('id', sa.BIGINT(), autoincrement=True,
>> nullable=False),
>> >> > sa.Column('name', sa.String(100),
>> autoincrement=False, nullable=False),
>> >> > sa.Column('users', BigInteger),
>> >> > sa.Column('permissions', BigInteger),
>> >> > sa.PrimaryKeyConstraint('id'),
>> >> > sa.UniqueConstraint('name',
>> name='roles_name_key')
>> >> > # sa.ForeignKeyConstraint(['users'],
>> ['users_roles.users_id'], ondelete='CASCADE'),
>> >> > # sa.ForeignKeyConstraint(['permissions'],
>> ['roles_permissions.permissions_id'], ondelete='CASCADE')
>> >> > )
>> >> > op.create_table('permissions',
>> >> > sa.Column('id', sa.BIGINT(), autoincrement=True,
>> nullable=False),
>> >> > sa.Column('name', sa.String(100),
>> autoincrement=False, nullable=False),
>> >> > sa.Column('description', sa.String(255)),
>> >> > sa.Column('users', BigInteger),
>> >> > sa.Column('roles', BigInteger),
>> >> > sa.PrimaryKeyConstraint('id')
>> >> > # sa.ForeignKeyConstraint(['users'],
>> ['users_permissions.users_id'], ondelete='CASCADE'),
>> >> > # sa.ForeignKeyConstraint(['roles'],
>> ['role_permissions.roles_id'], ondelete='CASCADE')
>> >> > )
>> >> > op.create_table('users_roles',
>> >> > sa.Column('users_id', BigInteger, sa.ForeignKey('
>> users.id'), primary_key=True),
>> >> > sa.Column('roles_id', BigInteger, sa.ForeignKey('
>> roles.id'), primary_key=True)
>> >> > )
>> >> > op.create_table('users_permissions',
>> >> > sa.Column('users_id', BigInteger, sa.ForeignKey('
>> users.id'), primary_key=True),
>> >> > sa.Column('permissions_id', BigInteger,
>> sa.ForeignKey('permissions.id'), primary_key=True)
>> >> > )
>> >> > op.create_table('roles_permissions',
>> >> > sa.Column('roles_id', BigInteger, sa.ForeignKey('
>> roles.id'), primary_key=True),
>> >> > sa.Column('permissions_id', BigInteger,
>> sa.ForeignKey('permissions.id'), primary_key=True)
>> >> > )
>> >> > op.drop_constraint('users_name_key', 'users')
>> >> > op.add_column('users', sa.Column('is_admin', sa.BOOLEAN,
>> autoincrement=False, nullable=False, default=False))
>> >> > op.add_column('users', sa.Column('is_active', sa.BOOLEAN,
>> autoincrement=False, nullable=False, default=True))
>> >> > op.add_column('users', sa.Column('created', sa.DateTime,
>> autoincrement=False, nullable=False,
>> >> > default=datetime.datetime.now))
>> >> > op.add_column('users', sa.Column('modified', sa.DateTime,
>> autoincrement=False, nullable=False,
>> >> > default=datetime.datetime.now))
>> >> > op.add_column('users', sa.Column('roles', BigInteger))
>> >> > op.add_column('users', sa.Column('permissions', BigInteger))
>> >> >
>> >> > op.create_foreign_key(constraint_name="users_roles_fk",
>> source_table="users", referent_table="users_roles",
>> >> > local_cols=["roles"],
>> remote_cols=["roles_id"], ondelete='CASCADE')
>> >> > op.create_foreign_key("users_permissions_fk", "users",
>> "users_permissions", ["permissions"], ["permissions_id"],
>> >> > ondelete='CASCADE')
>> >> > op.create_foreign_key("permissions_users_fk", "permissions",
>> "users_permissions", ["users"], ["users_id"],
>> >> > ondelete='CASCADE')
>> >> > op.create_foreign_key("permissions_roles_fk", "permissions",
>> "roles_permissions", ["roles"], ["roles_id"],
>> >> > ondelete='CASCADE')
>> >> > op.create_foreign_key("roles_users_fk", "roles", "users_roles",
>> ["users"], ["users_id"],
>> >> > ondelete='CASCADE')
>> >> > op.create_foreign_key("roles_permissions_fk", "roles",
>> "roles_permissions", ["permissions"], ["permissions_id"],
>> >> > ondelete='CASCADE')
>> >> > </code>
>> >> > i have an error, primary key for users_roles is a composite keys and
>> i dont know how declare it, how make that
>> >>
>> >> it's not clear here if you're saying alembic is adding foreign keys
>> >> where they dont belong during autogenerate, or if you dont know why
>> >> you are getting the error you are getting. The error is because you
>> >> don't need FOREIGN KEY constraints on roles, permissions, or users -
>> >> the FOREIGN KEY is only on the many-to-many tables, in this case
>> >> users_roles, users_permissions, roles_permissions.
>> >>
>> >> otherwise if you can illustrate the exact CREATE TABLE statements
>> >> you'd like to see, I can illustrate a correct model for that which
>> >> Alembic will generate.
>> >>
>> >>
>> >>
>> >> > <code>
>> >> > sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there
>> is no unique constraint matching given keys for referenced table
>> "users_roles"
>> >> > [SQL: 'ALTER TABLE users ADD CONSTRAINT users_roles_fk FOREIGN
>> KEY(roles) REFERENCES users_roles (roles_id) ON DELETE CASCADE']
>> (Background on this error at: http://sqlalche.me/e/f405)
>> >> > </code>
>> >> >
>> >> > --
>> >> > You received this message because you are subscribed to the Google
>> Groups "sqlalchemy-alembic" group.
>> >> > To unsubscribe from this group and stop receiving emails from it,
>> send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>> >> > For more options, visit https://groups.google.com/d/optout.
>> >>
>> >> --
>> >> You received this message because you are subscribed to the Google
>> Groups "sqlalchemy-alembic" group.
>> >> To unsubscribe from this group and stop receiving emails from it, send
>> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>> >> For more options, visit https://groups.google.com/d/optout.
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> Groups "sqlalchemy-alembic" group.
>> > To unsubscribe from this group and stop receiving emails from it, send
>> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy-alembic" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
