def upgrade():
context = get_context()
if isinstance(context.connection.engine.dialect, PGDialect):
# add 'sync_type' column if missing
op.add_column('services',
sa.Column('sync_type', sa.UnicodeText(), nullable=True))
services = table(
'services',
sa.Column('url', sa.UnicodeText()),
sa.Column('type', sa.UnicodeText()),
sa.Column('sync_type', sa.UnicodeText()),
)
# transfer 'api' service types
op.execute(services.update().where(
services.c.type == op.inline_literal('project-api')).values({
'type':
op.inline_literal('api'),
'url':
services.c.url + "/api",
'sync_type':
op.inline_literal('project-api')
}))
op.execute(services.update().where(
services.c.type == op.inline_literal('geoserver-api')).values({
'type':
op.inline_literal('api'),
'sync_type':
op.inline_literal('geoserver-api')
}))
class RemoteResourcesSyncInfo(BaseModel, Base):
__tablename__ = "remote_resources_sync_info"
id = sa.Column(sa.Integer(), primary_key=True, nullable=False, autoincrement=True)
service_id = sa.Column(sa.Integer(),
sa.ForeignKey("services.resource_id",
onupdate="CASCADE",
ondelete="CASCADE"),
index=True,
nullable=False)
service = relationship("Service", foreign_keys=[service_id])
remote_resource_id = sa.Column(sa.Integer(),
sa.ForeignKey("remote_resources.resource_id", onupdate="CASCADE",
ondelete="CASCADE"))
last_sync = sa.Column(sa.DateTime(), nullable=True)
@staticmethod
def by_service_id(service_id, session):
condition = RemoteResourcesSyncInfo.service_id == service_id
service_info = session.query(RemoteResourcesSyncInfo).filter(condition).first()
return service_info
def __repr__(self):
last_modified = self.last_sync.strftime("%Y-%m-%dT%H:%M:%S") if self.last_sync else None
info = self.service_id, last_modified, self.id
return "<RemoteResourcesSyncInfo service_id: %s, last_sync: %s, id: %s>" % info
class Resource(ResourceMixin, Base):
# required resource type identifier (unique)
resource_type_name = None # type: Str
child_resource_allowed = True
resource_display_name = sa.Column(sa.Unicode(100), nullable=True)
# reference to top-most service under which the resource is nested
# if the resource is the service, id is None (NULL)
@declared_attr
def root_service_id(self):
return sa.Column(sa.Integer,
sa.ForeignKey("services.resource_id",
onupdate="CASCADE",
ondelete="SET NULL"), index=True)
@property
def __acl__(self):
acl = []
if self.owner_user_id:
acl.extend([(ALLOW, self.owner_user_id, ALL_PERMISSIONS,), ])
if self.owner_group_id:
acl.extend([(ALLOW, "group:%s" % self.owner_group_id, ALL_PERMISSIONS,), ])
return acl
def parent_id(self):
return sa.Column(
sa.Integer(),
sa.ForeignKey("resources.resource_id",
onupdate="CASCADE",
ondelete="SET NULL"),
)
def upgrade():
op.create_table(
'remote_resources',
sa.Column('resource_id',
sa.Integer(),
primary_key=True,
nullable=False,
autoincrement=True),
sa.Column('service_id',
sa.Integer(),
sa.ForeignKey('services.resource_id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True,
nullable=False),
sa.Column('parent_id',
sa.Integer(),
sa.ForeignKey('remote_resources.resource_id',
onupdate='CASCADE',
ondelete='SET NULL'),
nullable=True),
sa.Column('ordering', sa.Integer(), default=0, nullable=False),
sa.Column('resource_name', sa.Unicode(100), nullable=False),
sa.Column('resource_type', sa.Unicode(30), nullable=False),
)
op.create_table(
'remote_resources_sync_info',
sa.Column('id',
sa.Integer(),
primary_key=True,
nullable=False,
autoincrement=True),
sa.Column('service_id',
sa.Integer(),
sa.ForeignKey('services.resource_id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True,
nullable=False),
sa.Column(
'remote_resource_id', sa.Integer(),
sa.ForeignKey('remote_resources.resource_id',
onupdate='CASCADE',
ondelete='CASCADE')),
sa.Column('last_sync', sa.DateTime(), nullable=True),
)
class RemoteResource(BaseModel, Base):
__tablename__ = "remote_resources"
__possible_permissions__ = ()
_ziggurat_services = [ResourceTreeService]
resource_id = sa.Column(sa.Integer(), primary_key=True, nullable=False, autoincrement=True)
service_id = sa.Column(sa.Integer(),
sa.ForeignKey("services.resource_id",
onupdate="CASCADE",
ondelete="CASCADE"),
index=True,
nullable=False)
parent_id = sa.Column(sa.Integer(),
sa.ForeignKey("remote_resources.resource_id",
onupdate="CASCADE",
ondelete="SET NULL"),
nullable=True)
ordering = sa.Column(sa.Integer(), default=0, nullable=False)
resource_name = sa.Column(sa.Unicode(100), nullable=False)
resource_display_name = sa.Column(sa.Unicode(100), nullable=True)
resource_type = sa.Column(sa.Unicode(30), nullable=False)
def __repr__(self):
info = self.resource_type, self.resource_name, self.resource_id, self.ordering, self.parent_id
return "<RemoteResource: %s, %s, id: %s position: %s, parent_id: %s>" % info
def downgrade():
service = table(
'services',
sa.Column('url', sa.UnicodeText()),
sa.Column('type', sa.UnicodeText()),
sa.Column('sync_type', sa.UnicodeText()),
)
# transfer 'api' service types
op.execute(service.update().where(
service.c.sync_type == op.inline_literal('project-api')).values({
'type':
op.inline_literal('project-api'),
'url':
func.replace(service.c.url, '/api', ''),
}))
op.execute(service.update().where(
service.c.sync_type == op.inline_literal('geoserver-api')).values({
'type':
op.inline_literal('geoserver-api'),
}))
op.drop_column('services', 'sync_type')
class Service(Resource):
"""
Resource of `service` type.
"""
__tablename__ = u"services"
resource_id = sa.Column(sa.Integer(),
sa.ForeignKey("resources.resource_id",
onupdate="CASCADE",
ondelete="CASCADE", ),
primary_key=True, )
resource_type_name = u"service"
__mapper_args__ = {u"polymorphic_identity": resource_type_name,
u"inherit_condition": resource_id == Resource.resource_id}
@property
def permissions(self):
raise TypeError("Service permissions must be accessed by 'magpie.services.ServiceInterface' "
"instead of 'magpie.models.Service'.")
@declared_attr
def url(self):
# http://localhost:8083
return sa.Column(sa.UnicodeText(), unique=True)
@declared_attr
def type(self):
"""
Identifier matching ``magpie.services.ServiceInterface.service_type``.
"""
# wps, wms, thredds,...
return sa.Column(sa.UnicodeText())
@declared_attr
def sync_type(self):
"""
Identifier matching ``magpie.helpers.SyncServiceInterface.sync_type``.
"""
# project-api, geoserver-api,...
return sa.Column(sa.UnicodeText(), nullable=True)
@staticmethod
def by_service_name(service_name, db_session):
db = get_db_session(db_session)
service = db.query(Service).filter(Resource.resource_name == service_name).first()
return service
def upgrade():
context = get_context()
session = Session(bind=op.get_bind())
# two following lines avoids double 'DELETE' erroneous call when deleting group due to incorrect checks
# https://stackoverflow.com/questions/28824401
context.connection.engine.dialect.supports_sane_rowcount = False
context.connection.engine.dialect.supports_sane_multi_rowcount = False
if isinstance(context.connection.engine.dialect, PGDialect):
op.add_column(
'resources',
sa.Column('root_service_id', sa.Integer(), nullable=True))
# add existing resource references to their root service, loop through reference tree chain
all_resources = session.query(BaseResource)
for resource in all_resources:
# same resource is returned if it is directly the service
# otherwise, the resource chain is resolved to the top service
service_resource = get_resource_root_service(resource, session)
if service_resource.resource_id != resource.resource_id:
resource.root_service_id = service_resource.resource_id
session.commit()
def upgrade():
c = get_context()
insp = Inspector.from_engine(c.connection.engine)
# existing migration
# pre naming convention keys
groups_permissions_pkey = 'groups_permissions_pkey'
groups_pkey = 'groups_pkey'
groups_resources_permissions_pkey = 'groups_resources_permissions_pkey'
users_groups_pkey = 'users_groups_pkey'
users_permissions_pkey = 'users_permissions_pkey'
users_resources_permissions_pkey = 'users_resources_permissions_pkey'
# inspected keys
groups_permissions_pkey = insp.get_pk_constraint('groups_permissions')['name']
groups_pkey = insp.get_pk_constraint('groups')['name']
groups_resources_permissions_pkey = insp.get_pk_constraint('groups_resources_permissions')['name']
users_groups_pkey = insp.get_pk_constraint('users_groups')['name']
users_permissions_pkey = insp.get_pk_constraint('users_permissions')['name']
users_resources_permissions_pkey = insp.get_pk_constraint('users_resources_permissions')['name']
op.drop_constraint('groups_pkey', 'groups', type_='primary')
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column('groups', sa.Column('id', sa.Integer, primary_key=True,
autoincrement=False))
op.create_primary_key(groups_pkey, 'groups', cols=['id'])
op.alter_column('groups', 'id', type_=sa.Integer,
existing_type=sa.Integer, autoincrement=True,
existing_autoincrement=False, nullable=False)
else:
op.add_column('groups', sa.Column('id', sa.Integer, primary_key=True,
autoincrement=True))
op.create_primary_key(groups_pkey, 'groups', cols=['id'])
if isinstance(c.connection.engine.dialect, MySQLDialect):
for t in ['groups_permissions', 'groups_resources_permissions',
'users_groups']:
for constraint in insp.get_foreign_keys(t):
if constraint['referred_columns'] == ['group_name']:
op.drop_constraint(constraint['name'], t,
type_='foreignkey')
for t in ['users_resources_permissions', 'users_permissions',
'users_groups']:
for constraint in insp.get_foreign_keys(t):
if constraint['referred_columns'] == ['user_name']:
op.drop_constraint(constraint['name'], t,
type_='foreignkey')
for constraint in insp.get_foreign_keys('resources'):
if constraint['referred_columns'] in [['user_name'],
['group_name']]:
op.drop_constraint(constraint['name'], 'resources',
type_='foreignkey')
op.add_column('resources', sa.Column('owner_user_id', sa.Integer(),
sa.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='SET NULL')))
op.add_column('resources', sa.Column('owner_group_id', sa.Integer(),
sa.ForeignKey('groups.id',
onupdate='CASCADE',
ondelete='SET NULL')))
# update the data
op.execute('update resources set owner_user_id = (select id from users where users.user_name=owner_user_name)')
op.execute('update resources set owner_group_id = (select id from users where users.user_name=owner_group_name)')
# mysql is stupid as usual so we cant create FKEY and add PKEY later,
# need to set PKEY first and then set FKEY
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column('groups_permissions', sa.Column('group_id', sa.Integer()))
else:
op.add_column('groups_permissions', sa.Column('group_id', sa.Integer(),
sa.ForeignKey('groups.id',
onupdate='CASCADE',
ondelete='CASCADE')))
op.execute('''
update groups_permissions set group_id =
(select id from groups where groups.group_name=groups_permissions.group_name)''')
op.drop_constraint(groups_permissions_pkey, 'groups_permissions',
type_='primary')
op.create_primary_key(groups_permissions_pkey, 'groups_permissions',
cols=['group_id', 'perm_name'])
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(None, 'groups_permissions', 'groups',
remote_cols=['id'],
local_cols=['group_id'], onupdate='CASCADE',
ondelete='CASCADE')
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column('groups_resources_permissions',
sa.Column('group_id', sa.Integer()))
else:
op.add_column('groups_resources_permissions',
sa.Column('group_id', sa.Integer(),
sa.ForeignKey('groups.id', onupdate='CASCADE',
ondelete='CASCADE')))
op.execute('''
update groups_resources_permissions set group_id =
(select id from groups where groups.group_name=groups_resources_permissions.group_name)''')
op.drop_constraint(groups_resources_permissions_pkey,
'groups_resources_permissions',
type_='primary')
op.create_primary_key(groups_resources_permissions_pkey,
'groups_resources_permissions',
cols=['group_id', 'resource_id', 'perm_name'])
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(None, 'groups_resources_permissions', 'groups',
remote_cols=['id'],
local_cols=['group_id'], onupdate='CASCADE',
ondelete='CASCADE')
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column('users_groups', sa.Column('group_id', sa.Integer()))
else:
op.add_column('users_groups', sa.Column('group_id', sa.Integer(),
sa.ForeignKey('groups.id',
onupdate='CASCADE',
ondelete='CASCADE')))
op.execute('''
update users_groups set group_id =
(select id from groups where groups.group_name=users_groups.group_name)''')
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column('users_groups', sa.Column('user_id', sa.Integer()))
else:
op.add_column('users_groups', sa.Column('user_id', sa.Integer(),
sa.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='CASCADE')))
op.execute('''
update users_groups set user_id =
(select id from users where users.user_name=users_groups.user_name)''')
op.drop_constraint(users_groups_pkey, 'users_groups', type='primary')
op.create_primary_key(users_groups_pkey, 'users_groups',
cols=['user_id', 'group_id'])
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(None, 'users_groups', 'groups',
remote_cols=['id'],
local_cols=['group_id'], onupdate='CASCADE',
ondelete='CASCADE')
op.create_foreign_key(None, 'users_groups', 'users',
remote_cols=['id'],
local_cols=['user_id'], onupdate='CASCADE',
ondelete='CASCADE')
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column('users_permissions', sa.Column('user_id', sa.Integer()))
else:
op.add_column('users_permissions', sa.Column('user_id', sa.Integer(),
sa.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='CASCADE')))
op.execute('''
update users_permissions set user_id =
(select id from groups where groups.group_name=users_permissions.user_name)''')
op.drop_constraint(users_permissions_pkey, 'users_permissions',
type='primary')
op.create_primary_key(users_permissions_pkey, 'users_permissions',
cols=['user_id', 'perm_name'])
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(None, 'users_permissions', 'users',
remote_cols=['id'],
local_cols=['user_id'], onupdate='CASCADE',
ondelete='CASCADE')
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.add_column('users_resources_permissions',
sa.Column('user_id', sa.Integer()))
else:
op.add_column('users_resources_permissions',
sa.Column('user_id', sa.Integer(),
sa.ForeignKey('users.id', onupdate='CASCADE',
ondelete='CASCADE')))
op.execute('''
update users_resources_permissions set user_id =
(select id from users where users.user_name=users_resources_permissions.user_name)''')
op.drop_constraint(users_resources_permissions_pkey,
'users_resources_permissions',
type='primary')
op.create_primary_key(users_resources_permissions_pkey,
'users_resources_permissions',
cols=['user_id', 'resource_id', 'perm_name'])
if isinstance(c.connection.engine.dialect, MySQLDialect):
op.create_foreign_key(None, 'users_resources_permissions', 'users',
remote_cols=['id'],
local_cols=['user_id'], onupdate='CASCADE',
ondelete='CASCADE')
op.drop_column('resources', 'owner_user_name')
op.drop_column('resources', 'owner_group_name')
op.drop_column('groups_permissions', 'group_name')
op.drop_column('groups_resources_permissions', 'group_name')
op.drop_column('users_resources_permissions', 'user_name')
op.drop_column('users_groups', 'group_name')
op.drop_column('users_groups', 'user_name')
op.drop_column('users_permissions', 'user_name')
def root_service_id(self):
return sa.Column(sa.Integer,
sa.ForeignKey("services.resource_id",
onupdate="CASCADE",
ondelete="SET NULL"), index=True)
def sync_type(self):
"""
Identifier matching ``magpie.helpers.SyncServiceInterface.sync_type``.
"""
# project-api, geoserver-api,...
return sa.Column(sa.UnicodeText(), nullable=True)
def type(self):
"""
Identifier matching ``magpie.services.ServiceInterface.service_type``.
"""
# wps, wms, thredds,...
return sa.Column(sa.UnicodeText())
def url(self):
# http://localhost:8083
return sa.Column(sa.UnicodeText(), unique=True)
def resource_id(self):
return sa.Column(sa.Integer(),
primary_key=True,
nullable=False,
autoincrement=True)
