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):
# 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 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 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
