def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata debug = False migrate_engine.echo=debug meta.bind = migrate_engine real_meta = MetaData() real_meta.bind = migrate_engine real_meta.reflect() rt = real_meta.tables['authentication_userrole'] for ctraint in deepcopy(rt.foreign_keys): if 'fk_userrole_user' in ctraint.name: column = ctraint.column parent = ctraint.parent fk = ForeignKeyConstraint([parent], [column], **{'table': rt}) fk.name = ctraint.name fk.drop() fkp = [a for a in UserRole.__table__.foreign_keys if a.name == 'fk_userrole_users'][0] fk = ForeignKeyConstraint([fkp.parent], [fkp.column], **{'table': fkp.parent.table}) fk.name = fkp.name fk.use_alter = fkp.use_alter fk.ondelete = fkp.ondelete fk.onupdate = fkp.onupdate fk.create() new_meta = MetaData(bind=migrate_engine) new_meta.reflect()
def upgrade(migrate_engine): class AuthUserLog(Base): """ event: L - Login R - Register P - Password F - Forgot """ __tablename__ = 'auth_user_log' __table_args__ = {"sqlite_autoincrement": True} id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey("auth_users.id", onupdate='CASCADE', ondelete='CASCADE'), index=True) time = Column(DateTime(), default=func.now()) ip_addr = Column(Unicode(39), nullable=False) internal_user = Column(Boolean, nullable=False, default=False) external_user = Column(Boolean, nullable=False, default=False) event = Column(Enum(u'L',u'R',u'P',u'F', name=u"event"), default=u'L') recreate_constraints = [AuthUserLog] # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata debug = True session.configure(bind=migrate_engine) migrate_engine.echo=debug metadata.bind = migrate_engine metadata.reflect(only=['auth_users']) r_meta = s.MetaData(migrate_engine, True) def commit(): session.commit() r_meta.bind.execute ('COMMIT;') metadata.bind.execute('COMMIT;') # create constraints fks = [] for md in recreate_constraints: t = md.__table__ rt = r_meta.tables[t.name] rt_constraints = [a for a in rt.foreign_keys] for cs in deepcopy(t.foreign_keys): if cs.__class__.__name__ == 'ForeignKey': table, column = cs.target_fullname.split('.') target = [r_meta.tables[table].c[column]] parent = [r_meta.tables[cs.parent.table.name].c[cs.parent.name]] fk = ForeignKeyConstraint(columns=parent,refcolumns=target) fk.use_alter = cs.use_alter fk.ondelete = 'CASCADE' fk.onupdate = 'CASCADE' fk.name = cs.name fks.append(fk) if (cs.name in [a.name for a in rt_constraints] or (cs.target_fullname in [a.target_fullname for a in rt_constraints])): fk.drop(migrate_engine) commit() for fk in fks: fk.create(migrate_engine) commit()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata debug = False migrate_engine.echo=debug meta.bind = migrate_engine real_meta = MetaData() real_meta.bind = migrate_engine real_meta.reflect() # finally i decided to go to a separate permission table if 'authentication_permission' not in real_meta.tables: permission.create() for acl, item in ((aclusers, 'users'), (aclprojects, 'projects')): rt = real_meta.tables[acl.name] for ctraint in deepcopy(rt.foreign_keys): if ('perm' in ctraint.name) or ('perm' in ctraint.parent.name): column = ctraint.column parent = ctraint.parent fk = ForeignKeyConstraint([parent], [column], **{'table': rt}) fk.name = ctraint.name fk.drop() if 'permission' in rt.c: if len(rt.c["permission"].foreign_keys) > 0: rt.c["permission"].drop() if 'permission' in rt.c: ctype = rt.c['permission'].type.__class__.__name__ drop = False if 'CHAR' in ctype: drop = True if 'INTEGER' in ctype: drop = True if drop: rt.c["permission"].drop() if not ('permission' in rt.c): acl.c["permission"].create() # refresh metA fkp = {"users":ForeignKey("authentication_permission.id", name="fk_userssacl_permission", use_alter=True, ondelete="CASCADE", onupdate="CASCADE"), "projects":ForeignKey("authentication_permission.id", name="fk_projectsacl_permission", use_alter=True, ondelete="CASCADE", onupdate="CASCADE"), }.get(item) fk = ForeignKeyConstraint([acl.c.permission], [permission.c.id], **{'table': acl}) fk.name = fkp.name fk.use_alter = fkp.use_alter fk.ondelete = fkp.ondelete fk.onupdate = fkp.onupdate fk.create() new_meta = MetaData(bind=migrate_engine) new_meta.reflect() nt = new_meta.tables[acl.name] columns = [] if 'project' in item: columns.append(nt.c['rid']) columns.extend([nt.c['role'], nt.c['permission']]) pk = PrimaryKeyConstraint(*columns) pk.create()
def recreate_table_fkeys(table, session,): """Recreate all foreign keys in the table or declarative object""" if not isinstance(table, Table): table = table.__table__ migrate_engine = session.bind metadata = table.metadata r_meta = s.MetaData(migrate_engine, True) def commit(): session.commit() r_meta.bind.execute ('COMMIT;') metadata.bind.execute('COMMIT;') fks = [] commit() t = table rt = r_meta.tables[t.name] rt_constraints = [a for a in rt.foreign_keys] for cs in deepcopy(t.foreign_keys): if cs.__class__.__name__ == 'ForeignKey': table, column = cs.target_fullname.split('.') target = [r_meta.tables[table].c[column]] parent_table = r_meta.tables[cs.parent.table.name] parent = [parent_table.c[cs.parent.name]] fk = ForeignKeyConstraint(columns=parent,refcolumns=target) fk.use_alter = cs.use_alter fk.ondelete = 'CASCADE' fk.onupdate = 'CASCADE' fk.name = cs.name fks.append(fk) if (cs.name in [a.name for a in rt_constraints] or (cs.target_fullname in [a.target_fullname for a in rt_constraints])): try: fk.drop(migrate_engine) commit() except:pass for fk in fks: fk.create(migrate_engine) commit()
def upgrade(migrate_engine): """r_meta must be reflected from the current database whereas metadata is the current constructed metadata for the migration purpose""" class Server(Base): __tablename__ = 'servers' id = Column(Integer, primary_key=True) name = Column(Unicode(50), unique=True) url = Column(Unicode(255)) help_mail = Column(Unicode(255)) def __init__(self, name=None, url=None, help_mail=None, ): self.name = name self.url = url self.help_mail = help_mail class Acl(Base): __tablename__ = 'authentication_acl' role = Column(Integer, ForeignKey("authentication_role.id", name="fk_acl_role", use_alter=True, ondelete="CASCADE", onupdate="CASCADE"), primary_key=True) permission = Column(Integer, ForeignKey("authentication_permission.id", name="fk_acl_permission", use_alter=True, ondelete="CASCADE", onupdate="CASCADE"), primary_key=True) class Role(Base): __tablename__ = 'authentication_role' id = Column(Integer, primary_key=True) name = Column(Unicode(50), unique=True) description = Column(Unicode(2500)) global_permissions = relationship( "Permission", uselist=True, secondary="authentication_acl", secondaryjoin="Acl.permission==Permission.id") def __init__(self, id=None, name=None, description=None, global_permissions=None): self.id = id self.description = description self.name = name if global_permissions is not None: self.global_permissions.extend(global_permissions) class Permission(Base): __tablename__ = 'authentication_permission' id = Column(Integer, primary_key=True) name = Column(Unicode(50), unique=True) description = Column(Unicode(2500)) roles = relationship( "Role", uselist=True, secondary="authentication_acl", secondaryjoin="Acl.permission==Role.id") def __init__(self, id=None, name=None, description=None, roles=None): self.id = id self.name = name self.description=description if roles is not None: self.roles.extends(roles) # Upgrade operations go here. Don't create your own engine; bind # migrate_engine to your metadata debug = True session.configure(bind=migrate_engine) migrate_engine.echo=debug metadata.bind = migrate_engine def commit(): session.commit() r_meta.bind.execute ('COMMIT;') metadata.bind.execute('COMMIT;') """ Reload all permissions """ real_perms = Permission.all() for p in real_perms[:]: if not p.name in default_permissions: session.delete(p) session.commit() nreal_perms = [p.name for p in real_perms] for p in default_permissions: if not p in nreal_perms: perm = Permission(name=p, description=default_permissions[p]) session.add(perm) session.commit() """ Reload all ACLS """ for p in default_acls: default_acls try: perm = Permission.by_name(p) except: pass roles = default_acls[p] for role in roles: access = roles[role] orole = Role.by_name(role) if access: if not perm in orole.global_permissions: orole.global_permissions.append(perm) session.add(orole) session.commit() else: if perm in orole.global_permissions: del orole.global_permissions[orole.global_permissions.index(perm)] session.add(orole) session.commit() session.flush() """ Create remote servers table """ r_meta = s.MetaData(migrate_engine, True) Base.metadata.bind = migrate_engine for t in [Server.__table__]: if not t.name in r_meta.tables: t.create(migrate_engine) """Recreate all authentication_acl foreign keys """ fks = [] commit() for md in [Acl]: t = md.__table__ rt = r_meta.tables[t.name] rt_constraints = [a for a in rt.foreign_keys] for cs in deepcopy(t.foreign_keys): if cs.__class__.__name__ == 'ForeignKey': table, column = cs.target_fullname.split('.') target = [r_meta.tables[table].c[column]] parent = [r_meta.tables[cs.parent.table.name].c[cs.parent.name]] fk = ForeignKeyConstraint(columns=parent,refcolumns=target) fk.use_alter = cs.use_alter fk.ondelete = 'CASCADE' fk.onupdate = 'CASCADE' fk.name = cs.name fks.append(fk) if (cs.name in [a.name for a in rt_constraints] or (cs.target_fullname in [a.target_fullname for a in rt_constraints])): fk.drop(migrate_engine) commit() for fk in fks: fk.create(migrate_engine) commit()
pass commit() r_meta = s.MetaData(migrate_engine, True) # create constraints fks = [] for md in recreate_constraints: t = md.__table__ rt = r_meta.tables[t.name] rt_constraints = [a for a in rt.foreign_keys] for cs in deepcopy(t.foreign_keys): if cs.__class__.__name__ == "ForeignKey": if not cs.name in [a.name for a in rt_constraints]: table, column = cs.target_fullname.split(".") target = [r_meta.tables[table].c[column]] parent = [r_meta.tables[cs.parent.table.name].c[cs.parent.name]] fk = ForeignKeyConstraint(columns=parent, refcolumns=target) fk.use_alter = cs.use_alter fk.ondelete = "CASCADE" fk.onupdate = "CASCADE" fk.name = cs.name fks.append(fk) for fk in fks: fk.create(migrate_engine) commit() def downgrade(migrate_engine): # Operations to reverse the above upgrade go here. pass