def upgrade():
op.add_column(
'request',
sa.Column('payout',
sa.Numeric(precision=15, scale=2),
index=True,
nullable=True))
bind = op.get_bind()
absolute = select([abs_table.c.value.label('value'),
mod_table.c.request_id.label('request_id')])\
.select_from(join(abs_table, mod_table,
mod_table.c.id == abs_table.c.id))\
.where(mod_table.c.voided_user_id == None)\
.alias()
relative = select([rel_table.c.value.label('value'),
mod_table.c.request_id.label('request_id')])\
.select_from(join(rel_table, mod_table,
mod_table.c.id == rel_table.c.id))\
.where(mod_table.c.voided_user_id == None)\
.alias()
abs_sum = select([request.c.id.label('request_id'),
request.c.base_payout.label('base_payout'),
func.sum(absolute.c.value).label('sum')])\
.select_from(outerjoin(request, absolute,
request.c.id == absolute.c.request_id))\
.group_by(request.c.id)\
.alias()
rel_sum = select([request.c.id.label('request_id'),
func.sum(relative.c.value).label('sum')])\
.select_from(outerjoin(request, relative,
request.c.id == relative.c.request_id))\
.group_by(request.c.id)\
.alias()
total_sum = select([abs_sum.c.request_id.label('request_id'),
((
abs_sum.c.base_payout +
case([(abs_sum.c.sum == None, Decimal(0))],
else_=abs_sum.c.sum)) *
(
1 +
case([(rel_sum.c.sum == None, Decimal(0))],
else_=rel_sum.c.sum))).label('payout')])\
.select_from(join(abs_sum, rel_sum,
abs_sum.c.request_id == rel_sum.c.request_id))
payouts = bind.execute(total_sum)
for request_id, payout in payouts:
up = update(request).where(request.c.id == request_id).values(
payout=payout)
bind.execute(up)
op.alter_column('request',
'payout',
nullable=False,
existing_type=sa.Numeric(precision=15, scale=2))
def upgrade():
op.add_column('request',
sa.Column('payout', sa.Numeric(precision=15, scale=2), index=True,
nullable=True))
bind = op.get_bind()
absolute = select([abs_table.c.value.label('value'),
mod_table.c.request_id.label('request_id')])\
.select_from(join(abs_table, mod_table,
mod_table.c.id == abs_table.c.id))\
.where(mod_table.c.voided_user_id == None)\
.alias()
relative = select([rel_table.c.value.label('value'),
mod_table.c.request_id.label('request_id')])\
.select_from(join(rel_table, mod_table,
mod_table.c.id == rel_table.c.id))\
.where(mod_table.c.voided_user_id == None)\
.alias()
abs_sum = select([request.c.id.label('request_id'),
request.c.base_payout.label('base_payout'),
func.sum(absolute.c.value).label('sum')])\
.select_from(outerjoin(request, absolute,
request.c.id == absolute.c.request_id))\
.group_by(request.c.id)\
.alias()
rel_sum = select([request.c.id.label('request_id'),
func.sum(relative.c.value).label('sum')])\
.select_from(outerjoin(request, relative,
request.c.id == relative.c.request_id))\
.group_by(request.c.id)\
.alias()
total_sum = select([abs_sum.c.request_id.label('request_id'),
((
abs_sum.c.base_payout +
case([(abs_sum.c.sum == None, Decimal(0))],
else_=abs_sum.c.sum)) *
(
1 +
case([(rel_sum.c.sum == None, Decimal(0))],
else_=rel_sum.c.sum))).label('payout')])\
.select_from(join(abs_sum, rel_sum,
abs_sum.c.request_id == rel_sum.c.request_id))
payouts = bind.execute(total_sum)
for request_id, payout in payouts:
up = update(request).where(request.c.id == request_id).values(
payout=payout)
bind.execute(up)
op.alter_column('request', 'payout', nullable=False,
existing_type=sa.Numeric(precision=15, scale=2))
async def verify_channel(client: ClientConnection, message: dict):
if not client.is_authorized():
API._log("Unauthorized client tried to verify a channel")
await client.send_error(
message['id'], 401,
"client must login before attempting to verify a channel")
return
sel_q = select([Channel]).select_from(outerjoin(Channel, ChannelAdmin)).\
where(and_(ChannelAdmin.admin_id == client.session['client_id'],
Channel.username == message['username']))
channel = await pg.fetchrow(sel_q)
if not channel:
await client.send_error(message['id'], 401,
'Only channel admin can do that')
return
upd_q = update(Channel).where(Channel.id == channel['id']).values(
verified=True)
await pg.fetchrow(upd_q)
await client.send_response({
'id': message['id'],
'action': message['action']
})
def search_query(swords, filters=None, verbose=False):
# Create the join..
sel = sql.outerjoin(customers_table, machines_table).outerjoin(services_table)
if filters:
# Remove filters that are just None
filters = dict([(x, filters[x]) for x in filters if filters[x]])
if not isinstance(filters, dict):
raise RuntimeError("filters param must be a dict, or None")
if [True for x in filters if not isinstance(filters[x], list)]:
raise RuntimeError("filters themselves must be a list of ints")
if 'groups' in filters:
sel = sel.join(servicegroups_table)
sel = sel.select(use_labels=True)
if filters:
if 'groups' in filters:
sel = sel.where(ServiceGroup.group_id.in_(filters['groups']))
if 'machines' in filters:
sel = sel.where(Machine.id.in_(filters['machines']))
if 'customers' in filters:
sel = sel.where(Customer.id.in_(filters['customers']))
# Fields to search in..
textfields = [Customer.name,
Machine.name,
Machine.fqdn,
Machine.ip,
Machine.location,
Machine.notes,
Service.url,
Service.notes]
numfields = [Customer.id,
Machine.id,
Service.id]
# TODO: distinguish between INTEGER fields and STRINGS and search
# differently (check only ==, and only if word can be converted to int())
andlist = []
for word in swords:
orlist = [field.ilike('%%%s%%' % word) for field in textfields]
if word.isdigit():
# Search numeric fields too
orlist += [field == int(word) for field in numfields]
orword = sql.or_(*orlist)
andlist.append(orword)
sel = sel.where(sql.and_(*andlist))
sel = sel.order_by(Machine.name, Service.url)
return meta.Session.execute(sel)
def search_query(swords, filters=None, verbose=False):
# Create the join..
sel = sql.outerjoin(customers_table, machines_table).outerjoin(services_table)
if filters:
# Remove filters that are just None
filters = dict([(x, filters[x]) for x in filters if filters[x]])
if not isinstance(filters, dict):
raise RuntimeError("filters param must be a dict, or None")
if [True for x in filters if not isinstance(filters[x], list)]:
raise RuntimeError("filters themselves must be a list of ints")
if 'groups' in filters:
sel = sel.join(servicegroups_table)
sel = sel.select(use_labels=True)
if filters:
if 'groups' in filters:
sel = sel.where(ServiceGroup.group_id.in_(filters['groups']))
if 'machines' in filters:
sel = sel.where(Machine.id.in_(filters['machines']))
if 'customers' in filters:
sel = sel.where(Customer.id.in_(filters['customers']))
# Fields to search in..
textfields = [Customer.name,
Machine.name,
Machine.fqdn,
Machine.ip,
Machine.location,
Machine.notes,
Service.url,
Service.notes]
numfields = [Customer.id,
Machine.id,
Service.id]
# TODO: distinguish between INTEGER fields and STRINGS and search
# differently (check only ==, and only if word can be converted to int())
andlist = []
for word in swords:
orlist = [field.ilike('%%%s%%' % word) for field in textfields]
if word.isdigit():
# Search numeric fields too
orlist += [field == int(word) for field in numfields]
orword = sql.or_(*orlist)
andlist.append(orword)
sel = sel.where(sql.and_(*andlist))
sel = sel.order_by(Machine.name, Service.url)
return meta.Session.execute(sel)
def setup_query(self, context, eagertable=None, parentclauses=None, parentmapper=None, **kwargs):
"""Add a left outer join to the statement thats being constructed."""
if parentmapper is None:
localparent = context.mapper
else:
localparent = parentmapper
if self.mapper in context.recursion_stack:
return
else:
context.recursion_stack.add(self.parent)
statement = context.statement
if hasattr(statement, '_outerjoin'):
towrap = statement._outerjoin
elif isinstance(localparent.mapped_table, schema.Table):
# if the mapper is against a plain Table, look in the from_obj of the select statement
# to join against whats already there.
for (fromclause, finder) in [(x, sql_util.TableFinder(x)) for x in statement.froms]:
# dont join against an Alias'ed Select. we are really looking either for the
# table itself or a Join that contains the table. this logic still might need
# adjustments for scenarios not thought of yet.
if not isinstance(fromclause, sql.Alias) and localparent.mapped_table in finder:
towrap = fromclause
break
else:
raise exceptions.InvalidRequestError("EagerLoader cannot locate a clause with which to outer join to, in query '%s' %s" % (str(statement), self.localparent.mapped_table))
else:
# if the mapper is against a select statement or something, we cant handle that at the
# same time as a custom FROM clause right now.
towrap = localparent.mapped_table
try:
clauses = self.clauses[parentclauses]
except KeyError:
clauses = EagerLoader.AliasedClauses(self, parentclauses)
self.clauses[parentclauses] = clauses
if context.mapper not in self.clauses_by_lead_mapper:
self.clauses_by_lead_mapper[context.mapper] = clauses
if self.secondaryjoin is not None:
statement._outerjoin = sql.outerjoin(towrap, clauses.eagersecondary, clauses.eagerprimary).outerjoin(clauses.eagertarget, clauses.eagersecondaryjoin)
if self.order_by is False and self.secondary.default_order_by() is not None:
statement.order_by(*clauses.eagersecondary.default_order_by())
else:
statement._outerjoin = towrap.outerjoin(clauses.eagertarget, clauses.eagerprimary)
if self.order_by is False and clauses.eagertarget.default_order_by() is not None:
statement.order_by(*clauses.eagertarget.default_order_by())
if clauses.eager_order_by:
statement.order_by(*util.to_list(clauses.eager_order_by))
statement.append_from(statement._outerjoin)
for value in self.select_mapper.props.values():
value.setup(context, eagertable=clauses.eagertarget, parentclauses=clauses, parentmapper=self.select_mapper)
async def init(client: ClientConnection, message: dict):
# TODO: Need to implement some throttling on init method to prevent brute-force attack
response = {
"id": message["id"],
"action": message["action"],
"expires_in": 172800,
}
# TODO: check expiration or implement deletion
session = None
if 'session_id' in message:
sel_q = select([Session, Client],
from_obj=[outerjoin(Session, Client)],
use_labels=True).where(
Session.session_id == message['session_id'])
session = await pg.fetchrow(sel_q)
if session is not None:
# Got session from DB, sent it's info to client
API._log("Existing session initialization")
client.session = dict(session.items())
user_id = client.session[
'client_user_id'] if 'client_user_id' in client.session else None
if user_id is not None:
await client.send_response({
"action":
"AUTH",
"user_id":
client.session['client_user_id'],
"first_name":
client.session['client_first_name'],
"username":
client.session['client_username'],
"language_code":
client.session['client_language_code'],
"photo":
client.session['client_photo'],
})
else:
# Session is not found, need to generate one
API._log("New session init")
# TODO: there is a possibility that UUID will match with existing one, need to try to generate the new one
session_dict = {
'session_id': str(uuid4()),
'expiration': datetime.now() + timedelta(days=2)
}
ins_q = insert(Session).values(**session_dict)
await pg.fetchrow(ins_q)
client.session = {
'session_' + k: v
for k, v in iter(session_dict.items())
}
response["session_id"] = client.session['session_session_id']
response["connection_id"] = client.connection_id
await client.send_response(response)
def __create_eager_join(self, context, path, parentclauses, parentmapper, **kwargs):
if parentmapper is None:
localparent = context.mapper
else:
localparent = parentmapper
if context.eager_joins:
towrap = context.eager_joins
else:
towrap = context.from_clause
# create AliasedClauses object to build up the eager query. this is cached after 1st creation.
try:
clauses = self.clauses[path]
except KeyError:
clauses = mapperutil.PropertyAliasedClauses(self.parent_property, self.parent_property.primaryjoin, self.parent_property.secondaryjoin, parentclauses)
self.clauses[path] = clauses
# place the "row_decorator" from the AliasedClauses into the QueryContext, where it will
# be picked up in create_row_processor() when results are fetched
context.attributes[("eager_row_processor", path)] = clauses.row_decorator
if self.secondaryjoin is not None:
context.eager_joins = sql.outerjoin(towrap, clauses.secondary, clauses.primaryjoin).outerjoin(clauses.alias, clauses.secondaryjoin)
# TODO: check for "deferred" cols on parent/child tables here ? this would only be
# useful if the primary/secondaryjoin are against non-PK columns on the tables (and therefore might be deferred)
if self.order_by is False and self.secondary.default_order_by() is not None:
context.eager_order_by += clauses.secondary.default_order_by()
else:
context.eager_joins = towrap.outerjoin(clauses.alias, clauses.primaryjoin)
# ensure all the cols on the parent side are actually in the
# columns clause (i.e. are not deferred), so that aliasing applied by the Query propagates
# those columns outward. This has the effect of "undefering" those columns.
for col in sql_util.find_columns(clauses.primaryjoin):
if localparent.mapped_table.c.contains_column(col):
context.primary_columns.append(col)
if self.order_by is False and clauses.alias.default_order_by() is not None:
context.eager_order_by += clauses.alias.default_order_by()
if clauses.order_by:
context.eager_order_by += util.to_list(clauses.order_by)
return clauses
async def fetch_channel(client: ClientConnection, message: dict):
sel_q = select([
Channel
]).select_from(Channel).where(Channel.username == message['username'])
res = await pg.fetchrow(sel_q)
if not res:
await client.send_error(message['id'], 404, 'No such channel')
return
channel = dict(res.items())
sel_q = select([Tag]).select_from(outerjoin(
ChannelTag, Tag)).where(ChannelTag.channel_id == channel['id'])
tags = await pg.fetch(sel_q)
channel['tags'] = [tag['name'] for tag in tags]
message['data'] = channel
await client.send_response(message)
def setup(self, key, statement, eagertable=None, **options):
"""add a left outer join to the statement thats being constructed"""
# initialize the "eager" chain of EagerLoader objects
# this can't quite be done in the do_init_mapper() step
self._create_eager_chain()
if hasattr(statement, '_outerjoin'):
towrap = statement._outerjoin
else:
towrap = self.localparent.mapped_table
# print "hello, towrap", str(towrap)
if self.secondaryjoin is not None:
statement._outerjoin = sql.outerjoin(towrap, self.eagersecondary,
self.eagerprimary).outerjoin(
self.eagertarget,
self.eagersecondaryjoin)
if self.order_by is False and self.secondary.default_order_by(
) is not None:
statement.order_by(*self.eagersecondary.default_order_by())
else:
statement._outerjoin = towrap.outerjoin(self.eagertarget,
self.eagerprimary)
if self.order_by is False and self.eagertarget.default_order_by(
) is not None:
statement.order_by(*self.eagertarget.default_order_by())
if self.eager_order_by:
statement.order_by(*util.to_list(self.eager_order_by))
elif getattr(statement, 'order_by_clause', None):
self._aliasize_orderby(statement.order_by_clause, False)
statement.append_from(statement._outerjoin)
for key, value in self.mapper.props.iteritems():
value.setup(key, statement, eagertable=self.eagertarget)
def _check_capacity_exceeded(conn, allocs):
"""Checks to see if the supplied allocation records would result in any of
the inventories involved having their capacity exceeded.
Raises an InvalidAllocationCapacityExceeded exception if any inventory
would be exhausted by the allocation. If no inventories would be exceeded
by the allocation, the function returns a list of `ResourceProvider`
objects that contain the generation at the time of the check.
:param conn: SQLalchemy Connection object to use
:param allocs: List of `Allocation` objects to check
"""
# The SQL generated below looks like this:
# SELECT
# rp.id,
# rp.uuid,
# rp.generation,
# inv.resource_class_id,
# inv.total,
# inv.reserved,
# inv.allocation_ratio,
# allocs.used
# FROM resource_providers AS rp
# JOIN inventories AS i1
# ON rp.id = i1.resource_provider_id
# LEFT JOIN (
# SELECT resource_provider_id, resource_class_id, SUM(used) AS used
# FROM allocations
# WHERE resource_class_id IN ($RESOURCE_CLASSES)
# GROUP BY resource_provider_id, resource_class_id
# ) AS allocs
# ON inv.resource_provider_id = allocs.resource_provider_id
# AND inv.resource_class_id = allocs.resource_class_id
# WHERE rp.uuid IN ($RESOURCE_PROVIDERS)
# AND inv.resource_class_id IN ($RESOURCE_CLASSES)
#
# We then take the results of the above and determine if any of the
# inventory will have its capacity exceeded.
rc_ids = set([_RC_CACHE.id_from_string(a.resource_class)
for a in allocs])
provider_uuids = set([a.resource_provider.uuid for a in allocs])
usage = sa.select([_ALLOC_TBL.c.resource_provider_id,
_ALLOC_TBL.c.consumer_id,
_ALLOC_TBL.c.resource_class_id,
sql.func.sum(_ALLOC_TBL.c.used).label('used')])
usage = usage.where(_ALLOC_TBL.c.resource_class_id.in_(rc_ids))
usage = usage.group_by(_ALLOC_TBL.c.resource_provider_id,
_ALLOC_TBL.c.resource_class_id)
usage = sa.alias(usage, name='usage')
inv_join = sql.join(_RP_TBL, _INV_TBL,
sql.and_(_RP_TBL.c.id == _INV_TBL.c.resource_provider_id,
_INV_TBL.c.resource_class_id.in_(rc_ids)))
primary_join = sql.outerjoin(inv_join, usage,
sql.and_(
_INV_TBL.c.resource_provider_id == usage.c.resource_provider_id,
_INV_TBL.c.resource_class_id == usage.c.resource_class_id)
)
cols_in_output = [
_RP_TBL.c.id.label('resource_provider_id'),
_RP_TBL.c.uuid,
_RP_TBL.c.generation,
_INV_TBL.c.resource_class_id,
_INV_TBL.c.total,
_INV_TBL.c.reserved,
_INV_TBL.c.allocation_ratio,
usage.c.used,
]
sel = sa.select(cols_in_output).select_from(primary_join)
sel = sel.where(
sa.and_(_RP_TBL.c.uuid.in_(provider_uuids),
_INV_TBL.c.resource_class_id.in_(rc_ids)))
records = conn.execute(sel)
# Create a map keyed by (rp_uuid, res_class) for the records in the DB
usage_map = {}
provs_with_inv = set()
for record in records:
map_key = (record['uuid'], record['resource_class_id'])
if map_key in usage_map:
raise KeyError("%s already in usage_map, bad query" % str(map_key))
usage_map[map_key] = record
provs_with_inv.add(record["uuid"])
# Ensure that all providers have existing inventory
missing_provs = provider_uuids - provs_with_inv
if missing_provs:
class_str = ', '.join([_RC_CACHE.string_from_id(rc_id)
for rc_id in rc_ids])
provider_str = ', '.join(missing_provs)
raise exception.InvalidInventory(resource_class=class_str,
resource_provider=provider_str)
res_providers = {}
for alloc in allocs:
rc_id = _RC_CACHE.id_from_string(alloc.resource_class)
rp_uuid = alloc.resource_provider.uuid
key = (rp_uuid, rc_id)
usage = usage_map[key]
amount_needed = alloc.used
allocation_ratio = usage['allocation_ratio']
# usage["used"] can be returned as None
used = usage['used'] or 0
capacity = (usage['total'] - usage['reserved']) * allocation_ratio
if capacity < (used + amount_needed):
LOG.warning(
_LW("Over capacity for %(rc)s on resource provider %(rp)s. "
"Needed: %(needed)s, Used: %(used)s, Capacity: %(cap)s"),
{'rc': alloc.resource_class,
'rp': rp_uuid,
'needed': amount_needed,
'used': used,
'cap': capacity})
raise exception.InvalidAllocationCapacityExceeded(
resource_class=alloc.resource_class,
resource_provider=rp_uuid)
if rp_uuid not in res_providers:
rp = ResourceProvider(id=usage['resource_provider_id'],
uuid=rp_uuid,
generation=usage['generation'])
res_providers[rp_uuid] = rp
return list(res_providers.values())
def setup_query(self,
context,
parentclauses=None,
parentmapper=None,
**kwargs):
"""Add a left outer join to the statement thats being constructed."""
path = context.path
# check for join_depth or basic recursion,
# if the current path was not explicitly stated as
# a desired "loaderstrategy" (i.e. via query.options())
if ("loaderstrategy", path) not in context.attributes:
if self.join_depth:
if len(path) / 2 > self.join_depth:
return
else:
if self.mapper.base_mapper in path:
return
if parentmapper is None:
localparent = context.mapper
else:
localparent = parentmapper
if context.eager_joins:
towrap = context.eager_joins
else:
towrap = context.from_clause
# create AliasedClauses object to build up the eager query. this is cached after 1st creation.
try:
clauses = self.clauses[path]
except KeyError:
clauses = mapperutil.PropertyAliasedClauses(
self.parent_property, self.parent_property.primaryjoin,
self.parent_property.secondaryjoin, parentclauses)
self.clauses[path] = clauses
# place the "row_decorator" from the AliasedClauses into the QueryContext, where it will
# be picked up in create_row_processor() when results are fetched
context.attributes[("eager_row_processor",
path)] = clauses.row_decorator
if self.secondaryjoin is not None:
context.eager_joins = sql.outerjoin(towrap, clauses.secondary,
clauses.primaryjoin).outerjoin(
clauses.alias,
clauses.secondaryjoin)
# TODO: check for "deferred" cols on parent/child tables here ? this would only be
# useful if the primary/secondaryjoin are against non-PK columns on the tables (and therefore might be deferred)
if self.order_by is False and self.secondary.default_order_by(
) is not None:
context.eager_order_by += clauses.secondary.default_order_by()
else:
context.eager_joins = towrap.outerjoin(clauses.alias,
clauses.primaryjoin)
# ensure all the cols on the parent side are actually in the
# columns clause (i.e. are not deferred), so that aliasing applied by the Query propagates
# those columns outward. This has the effect of "undefering" those columns.
for col in sql_util.find_columns(clauses.primaryjoin):
if localparent.mapped_table.c.contains_column(col):
context.primary_columns.append(col)
if self.order_by is False and clauses.alias.default_order_by(
) is not None:
context.eager_order_by += clauses.alias.default_order_by()
if clauses.order_by:
context.eager_order_by += util.to_list(clauses.order_by)
for value in self.mapper._iterate_polymorphic_properties():
context.exec_with_path(self.mapper,
value.key,
value.setup,
context,
parentclauses=clauses,
parentmapper=self.mapper)
def setup_query(self, context, parentclauses=None, parentmapper=None, **kwargs):
"""Add a left outer join to the statement thats being constructed."""
path = context.path
# check for join_depth or basic recursion,
# if the current path was not explicitly stated as
# a desired "loaderstrategy" (i.e. via query.options())
if ("loaderstrategy", path) not in context.attributes:
if self.join_depth:
if len(path) / 2 > self.join_depth:
return
else:
if self.mapper.base_mapper in path:
return
if parentmapper is None:
localparent = context.mapper
else:
localparent = parentmapper
if context.eager_joins:
towrap = context.eager_joins
else:
towrap = context.from_clause
# create AliasedClauses object to build up the eager query. this is cached after 1st creation.
try:
clauses = self.clauses[path]
except KeyError:
clauses = mapperutil.PropertyAliasedClauses(self.parent_property, self.parent_property.primaryjoin, self.parent_property.secondaryjoin, parentclauses)
self.clauses[path] = clauses
# place the "row_decorator" from the AliasedClauses into the QueryContext, where it will
# be picked up in create_row_processor() when results are fetched
context.attributes[("eager_row_processor", path)] = clauses.row_decorator
if self.secondaryjoin is not None:
context.eager_joins = sql.outerjoin(towrap, clauses.secondary, clauses.primaryjoin).outerjoin(clauses.alias, clauses.secondaryjoin)
# TODO: check for "deferred" cols on parent/child tables here ? this would only be
# useful if the primary/secondaryjoin are against non-PK columns on the tables (and therefore might be deferred)
if self.order_by is False and self.secondary.default_order_by() is not None:
context.eager_order_by += clauses.secondary.default_order_by()
else:
context.eager_joins = towrap.outerjoin(clauses.alias, clauses.primaryjoin)
# ensure all the cols on the parent side are actually in the
# columns clause (i.e. are not deferred), so that aliasing applied by the Query propagates
# those columns outward. This has the effect of "undefering" those columns.
for col in sql_util.find_columns(clauses.primaryjoin):
if localparent.mapped_table.c.contains_column(col):
context.primary_columns.append(col)
if self.order_by is False and clauses.alias.default_order_by() is not None:
context.eager_order_by += clauses.alias.default_order_by()
if clauses.order_by:
context.eager_order_by += util.to_list(clauses.order_by)
for value in self.mapper._iterate_polymorphic_properties():
context.exec_with_path(self.mapper, value.key, value.setup, context, parentclauses=clauses, parentmapper=self.mapper)
def setup_query(self,
context,
eagertable=None,
parentclauses=None,
parentmapper=None,
**kwargs):
"""Add a left outer join to the statement thats being constructed."""
if parentmapper is None:
localparent = context.mapper
else:
localparent = parentmapper
if self.mapper in context.recursion_stack:
return
else:
context.recursion_stack.add(self.parent)
statement = context.statement
if hasattr(statement, '_outerjoin'):
towrap = statement._outerjoin
elif isinstance(localparent.mapped_table, schema.Table):
# if the mapper is against a plain Table, look in the from_obj of the select statement
# to join against whats already there.
for (fromclause, finder) in [(x, sql_util.TableFinder(x))
for x in statement.froms]:
# dont join against an Alias'ed Select. we are really looking either for the
# table itself or a Join that contains the table. this logic still might need
# adjustments for scenarios not thought of yet.
if not isinstance(
fromclause,
sql.Alias) and localparent.mapped_table in finder:
towrap = fromclause
break
else:
raise exceptions.InvalidRequestError(
"EagerLoader cannot locate a clause with which to outer join to, in query '%s' %s"
% (str(statement), self.localparent.mapped_table))
else:
# if the mapper is against a select statement or something, we cant handle that at the
# same time as a custom FROM clause right now.
towrap = localparent.mapped_table
try:
clauses = self.clauses[parentclauses]
except KeyError:
clauses = EagerLoader.AliasedClauses(self, parentclauses)
self.clauses[parentclauses] = clauses
if context.mapper not in self.clauses_by_lead_mapper:
self.clauses_by_lead_mapper[context.mapper] = clauses
if self.secondaryjoin is not None:
statement._outerjoin = sql.outerjoin(
towrap, clauses.eagersecondary,
clauses.eagerprimary).outerjoin(clauses.eagertarget,
clauses.eagersecondaryjoin)
if self.order_by is False and self.secondary.default_order_by(
) is not None:
statement.order_by(*clauses.eagersecondary.default_order_by())
else:
statement._outerjoin = towrap.outerjoin(clauses.eagertarget,
clauses.eagerprimary)
if self.order_by is False and clauses.eagertarget.default_order_by(
) is not None:
statement.order_by(*clauses.eagertarget.default_order_by())
if clauses.eager_order_by:
statement.order_by(*util.to_list(clauses.eager_order_by))
statement.append_from(statement._outerjoin)
for value in self.select_mapper.props.values():
value.setup(context,
eagertable=clauses.eagertarget,
parentclauses=clauses,
parentmapper=self.select_mapper)
def _check_capacity_exceeded(conn, allocs):
"""Checks to see if the supplied allocation records would result in any of
the inventories involved having their capacity exceeded.
Raises an InvalidAllocationCapacityExceeded exception if any inventory
would be exhausted by the allocation. If no inventories would be exceeded
by the allocation, the function returns a list of `ResourceProvider`
objects that contain the generation at the time of the check.
:param conn: SQLalchemy Connection object to use
:param allocs: List of `Allocation` objects to check
"""
# The SQL generated below looks like this:
# SELECT
# rp.id,
# rp.uuid,
# rp.generation,
# inv.resource_class_id,
# inv.total,
# inv.reserved,
# inv.allocation_ratio,
# allocs.used
# FROM resource_providers AS rp
# JOIN inventories AS i1
# ON rp.id = i1.resource_provider_id
# LEFT JOIN (
# SELECT resource_provider_id, resource_class_id, SUM(used) AS used
# FROM allocations
# WHERE resource_class_id IN ($RESOURCE_CLASSES)
# GROUP BY resource_provider_id, resource_class_id
# ) AS allocs
# ON inv.resource_provider_id = allocs.resource_provider_id
# AND inv.resource_class_id = allocs.resource_class_id
# WHERE rp.uuid IN ($RESOURCE_PROVIDERS)
# AND inv.resource_class_id IN ($RESOURCE_CLASSES)
#
# We then take the results of the above and determine if any of the
# inventory will have its capacity exceeded.
res_classes = set([fields.ResourceClass.index(a.resource_class)
for a in allocs])
provider_uuids = set([a.resource_provider.uuid for a in allocs])
usage = sa.select([_ALLOC_TBL.c.resource_provider_id,
_ALLOC_TBL.c.consumer_id,
_ALLOC_TBL.c.resource_class_id,
sql.func.sum(_ALLOC_TBL.c.used).label('used')])
usage = usage.where(_ALLOC_TBL.c.resource_class_id.in_(res_classes))
usage = usage.group_by(_ALLOC_TBL.c.resource_provider_id,
_ALLOC_TBL.c.resource_class_id)
usage = sa.alias(usage, name='usage')
inv_join = sql.join(_RP_TBL, _INV_TBL,
sql.and_(_RP_TBL.c.id == _INV_TBL.c.resource_provider_id,
_INV_TBL.c.resource_class_id.in_(res_classes)))
primary_join = sql.outerjoin(inv_join, usage,
_INV_TBL.c.resource_provider_id == usage.c.resource_provider_id)
cols_in_output = [
_RP_TBL.c.id.label('resource_provider_id'),
_RP_TBL.c.uuid,
_RP_TBL.c.generation,
_INV_TBL.c.resource_class_id,
_INV_TBL.c.total,
_INV_TBL.c.reserved,
_INV_TBL.c.allocation_ratio,
usage.c.used,
]
sel = sa.select(cols_in_output).select_from(primary_join)
sel = sel.where(
sa.and_(_RP_TBL.c.uuid.in_(provider_uuids),
_INV_TBL.c.resource_class_id.in_(res_classes)))
records = conn.execute(sel)
# Create a map keyed by (rp_uuid, res_class) for the records in the DB
usage_map = {}
provs_with_inv = set()
for record in records:
usage_map[(record['uuid'], record['resource_class_id'])] = record
provs_with_inv.add(record["uuid"])
# Ensure that all providers have existing inventory
missing_provs = provider_uuids - provs_with_inv
if missing_provs:
raise exception.InvalidInventory(resource_class=str(res_classes),
resource_provider=missing_provs)
res_providers = {}
for alloc in allocs:
res_class = fields.ResourceClass.index(alloc.resource_class)
rp_uuid = alloc.resource_provider.uuid
key = (rp_uuid, res_class)
usage = usage_map[key]
amount_needed = alloc.used
allocation_ratio = usage['allocation_ratio']
# usage["used"] can be returned as None
used = usage['used'] or 0
capacity = (usage['total'] - usage['reserved']) * allocation_ratio
if capacity < (used + amount_needed):
raise exception.InvalidAllocationCapacityExceeded(
resource_class=fields.ResourceClass.from_index(res_class),
resource_provider=rp_uuid)
if rp_uuid not in res_providers:
rp = ResourceProvider(id=usage['resource_provider_id'],
uuid=rp_uuid,
generation=usage['generation'])
res_providers[rp_uuid] = rp
return list(res_providers.values())
def placement_get_select(args, res_template, worker_number):
"""
Returns a sqlalchemy.Select object representing the query against the
resource_providers schema tables, using derived table queries against the
allocations and inventories tables. The SQL generated from this looks like
the following. (If the filter_strategy is not 'db', then the WHERE clause
is not appended to the SQL):
SELECT cn.id, cn.generation
FROM resource_providers AS cn
INNER JOIN inventories AS ram_filtered
ON cn.id = ram_filtered.resource_provider_id
AND ram_filtered.resource_class_id = 2
LEFT OUTER JOIN (
SELECT
allocations.resource_provider_id AS resource_provider_id,
sum(allocations.used) AS used
FROM allocations
WHERE allocations.resource_class_id = 2
GROUP BY allocations.resource_provider_id
) AS ram_usage
ON ram_filtered.resource_provider_id = ram_usage.resource_provider_id
INNER JOIN inventories AS cpu_filtered
ON ram_filtered.resource_provider_id = cpu_filtered.resource_provider_id
AND cpu_filtered.resource_class_id = 1
LEFT OUTER JOIN (
SELECT
allocations.resource_provider_id AS resource_provider_id,
sum(allocations.used) AS used
FROM allocations
WHERE allocations.resource_class_id = 1
GROUP BY allocations.resource_provider_id
) AS cpu_usage
ON cpu_filtered.resource_provider_id = cpu_usage.resource_provider_id
WHERE
ram_filtered.min_unit <= 64
AND ram_filtered.max_unit >= 64
AND floor((ram_filtered.total - ram_filtered.reserved) * ram_filtered.allocation_ratio) - ifnull(ram_usage.used, 0) >= 64
AND cpu_filtered.min_unit <= 1
AND cpu_filtered.max_unit >= 1
AND floor((cpu_filtered.total - cpu_filtered.reserved) * cpu_filtered.allocation_ratio) - ifnull(cpu_usage.used, 0) >= 1
LIMIT 1
Depending on the partition and placement strategy, there may be additional
WHERE clauses that look like the following:
# 'modulo' partition strategy
AND (cn.id + $NUM_WORKERS) % NUM_WORKERS == 0
or:
# 'random' placement strategy
AND cn.id >= $RANDOM_COMPUTE_NODE_ID
The ORDER BY clause will depend on the placement strategy and may look like
the following:
# 'pack' placement strategy
ORDER BY IFNULL(ram_usage.used, 0) ASC, cn.id ASC
or:
# 'spread' placement strategy
ORDER BY IFNULL(ram_usage.used, 0) DESC, cn.id ASC
"""
(rp_tbl, agg_tbl, rp_agg_tbl, inv_tbl, alloc_tbl) = placement_get_tables()
cn_tbl = sa.alias(rp_tbl, name='cn')
ram_filtered = sa.alias(inv_tbl, name='ram_filtered')
cpu_filtered = sa.alias(inv_tbl, name='cpu_filtered')
ram_usage = sa.select([
alloc_tbl.c.resource_provider_id,
sql.func.sum(alloc_tbl.c.used).label('used')
])
ram_usage = ram_usage.where(alloc_tbl.c.resource_class_id == const.RAM_MB)
ram_usage = ram_usage.group_by(alloc_tbl.c.resource_provider_id)
ram_usage = sa.alias(ram_usage, name='ram_usage')
cpu_usage = sa.select([
alloc_tbl.c.resource_provider_id,
sql.func.sum(alloc_tbl.c.used).label('used')
])
cpu_usage = cpu_usage.where(alloc_tbl.c.resource_class_id == const.VCPU)
cpu_usage = cpu_usage.group_by(alloc_tbl.c.resource_provider_id)
cpu_usage = sa.alias(cpu_usage, name='cpu_usage')
ram_inv_join = sql.join(
cn_tbl, ram_filtered,
sql.and_(cn_tbl.c.id == ram_filtered.c.resource_provider_id,
ram_filtered.c.resource_class_id == const.RAM_MB))
ram_join = sql.outerjoin(
ram_inv_join, ram_usage, ram_filtered.c.resource_provider_id ==
ram_usage.c.resource_provider_id)
cpu_inv_join = sql.join(
ram_join, cpu_filtered,
sql.and_(
ram_filtered.c.resource_provider_id ==
cpu_filtered.c.resource_provider_id,
cpu_filtered.c.resource_class_id == const.VCPU))
cpu_join = sql.outerjoin(
cpu_inv_join, cpu_usage, cpu_filtered.c.resource_provider_id ==
cpu_usage.c.resource_provider_id)
cols_in_output = [cn_tbl.c.id, cn_tbl.c.generation]
if args.filter_strategy == 'python':
# When we don't do stuff on the DB side, we need to pass back
# a whole lot more columns since we have Python code loops
# that need to process these data fields.
cols_in_output.extend([
ram_filtered.c.total.label('ram_total'),
ram_filtered.c.reserved.label('ram_reserved'),
ram_filtered.c.min_unit.label('ram_min_unit'),
ram_filtered.c.max_unit.label('ram_max_unit'),
ram_filtered.c.allocation_ratio.label('ram_allocation_ratio'),
ram_usage.c.used.label('ram_used'),
cpu_filtered.c.total.label('cpu_total'),
cpu_filtered.c.reserved.label('cpu_reserved'),
cpu_filtered.c.min_unit.label('cpu_min_unit'),
cpu_filtered.c.max_unit.label('cpu_max_unit'),
cpu_filtered.c.allocation_ratio.label('cpu_allocation_ratio'),
cpu_usage.c.used.label('cpu_used'),
])
select = sa.select(cols_in_output).select_from(cpu_join)
if args.filter_strategy == 'db':
where_conds = (
ram_filtered.c.min_unit <= res_template[const.RAM_MB],
ram_filtered.c.max_unit >= res_template[const.RAM_MB],
sql.func.floor((ram_filtered.c.total - ram_filtered.c.reserved) *
ram_filtered.c.allocation_ratio) -
sql.func.ifnull(ram_usage.c.used, 0) >= res_template[const.VCPU],
cpu_filtered.c.min_unit <= res_template[const.VCPU],
cpu_filtered.c.max_unit >= res_template[const.VCPU],
sql.func.floor((cpu_filtered.c.total - cpu_filtered.c.reserved) *
cpu_filtered.c.allocation_ratio) -
sql.func.ifnull(cpu_usage.c.used, 0) >= res_template[const.VCPU])
if args.partition_strategy == 'modulo':
where_conds += ((cn_tbl.c.id + args.workers) %
worker_number == 0, )
if args.placement_strategy == 'pack':
select = select.order_by(sql.func.ifnull(ram_usage.c.used, 0),
cn_tbl.c.id)
if args.placement_strategy == 'spread':
select = select.order_by(
sql.func.ifnull(ram_usage.c.used, 0).desc(), cn_tbl.c.id)
if args.placement_strategy == 'random':
# The scheduler could keep a cache of the number of compute
# nodes in the system. But here, we emulate that cache by
# simply picking a random compute node ID by selecting a
# random ID since we know the compute nodes are
# sequentially ordered and a fixed number.
num_compute_nodes = (args.rows * args.racks * args.nodes)
num_shared_resource_pools = args.rows * (
1 if args.shared_storage_per_row else 0)
random_compute_node_id = random.randint(
1 + num_shared_resource_pools,
num_compute_nodes + num_shared_resource_pools)
where_conds += (cn_tbl.c.id >= random_compute_node_id, )
select = select.where(sql.and_(*where_conds))
select = select.limit(1)
return select
def setup_query(self,
context,
parentclauses=None,
parentmapper=None,
**kwargs):
"""Add a left outer join to the statement thats being constructed."""
# build a path as we setup the query. the format of this path
# matches that of interfaces.LoaderStack, and will be used in the
# row-loading phase to match up AliasedClause objects with the current
# LoaderStack position.
if parentclauses:
path = parentclauses.path + (self.parent.base_mapper, self.key)
else:
path = (self.parent.base_mapper, self.key)
if self.join_depth:
if len(path) / 2 > self.join_depth:
return
else:
if self.mapper in path:
return
#print "CREATING EAGER PATH FOR", "->".join([str(s) for s in path])
if parentmapper is None:
localparent = context.mapper
else:
localparent = parentmapper
statement = context.statement
if hasattr(statement, '_outerjoin'):
towrap = statement._outerjoin
elif isinstance(localparent.mapped_table, sql.Join):
towrap = localparent.mapped_table
else:
# look for the mapper's selectable expressed within the current "from" criterion.
# this will locate the selectable inside of any containers it may be a part of (such
# as a join). if its inside of a join, we want to outer join on that join, not the
# selectable.
for fromclause in statement.froms:
if fromclause is localparent.mapped_table:
towrap = fromclause
break
elif isinstance(fromclause, sql.Join):
if localparent.mapped_table in sql_util.TableFinder(
fromclause, include_aliases=True):
towrap = fromclause
break
else:
raise exceptions.InvalidRequestError(
"EagerLoader cannot locate a clause with which to outer join to, in query '%s' %s"
% (str(statement), localparent.mapped_table))
# create AliasedClauses object to build up the eager query. this is cached after 1st creation.
try:
clauses = self.clauses[path]
except KeyError:
clauses = mapperutil.PropertyAliasedClauses(
self.parent_property,
self.parent_property.polymorphic_primaryjoin,
self.parent_property.polymorphic_secondaryjoin, parentclauses)
self.clauses[path] = clauses
# place the "row_decorator" from the AliasedClauses into the QueryContext, where it will
# be picked up in create_row_processor() when results are fetched
context.attributes[("eager_row_processor",
path)] = clauses.row_decorator
if self.secondaryjoin is not None:
statement._outerjoin = sql.outerjoin(
towrap, clauses.secondary,
clauses.primaryjoin).outerjoin(clauses.alias,
clauses.secondaryjoin)
if self.order_by is False and self.secondary.default_order_by(
) is not None:
statement.append_order_by(
*clauses.secondary.default_order_by())
else:
statement._outerjoin = towrap.outerjoin(clauses.alias,
clauses.primaryjoin)
if self.order_by is False and clauses.alias.default_order_by(
) is not None:
statement.append_order_by(*clauses.alias.default_order_by())
if clauses.order_by:
statement.append_order_by(*util.to_list(clauses.order_by))
statement.append_from(statement._outerjoin)
for value in self.select_mapper.iterate_properties:
value.setup(context,
parentclauses=clauses,
parentmapper=self.select_mapper)
def test_sql_persistence():
db = SQLADTDatabase({"DB_NAME": "test", "ECHO": False})
db.add_adt_table(Card, "cards")
db.add_adt_table(Deck, "decks")
db.create_all_tables()
with db_context(db) as context:
db.truncate_all_tables(context)
deck = db.insert_adt(context, db.decks, Deck(name="Test deck"))
card_1 = db.insert_adt(
context, db.cards,
Card(
deck_id=deck.id,
title="Test card #1",
strength=10,
defense=1,
))
card_2 = db.insert_adt(
context, db.cards,
Card(
deck_id=deck.id,
title="Test card #2",
strength=8,
defense=7,
))
with db_context(db) as context:
r_deck = db.retrieve_single_adt(
context, Deck,
select([db.decks]).where(db.decks.c.id == deck.id))
assert r_deck.id == deck.id
assert r_deck.name == deck.name
with db_context(db) as context:
r_cards = db.retrieve_adts(context, Card, select([db.cards]))
assert len(r_cards) == 2
assert card_1.id in [card.id for card in r_cards]
assert card_2.id in [card.id for card in r_cards]
with db_context(db) as context:
r_decks = db.retrieve_joined_adts(
context, Deck, {
"decks": Deck,
"cards": Card
},
select([db.decks, db.cards], use_labels=True).select_from(
outerjoin(db.decks, db.cards,
db.decks.c.id == db.cards.c.deck_id)).where(
db.decks.c.id == deck.id))
assert len(r_decks) == 1
r_deck = r_decks[0]
assert r_deck.id == deck.id
assert r_deck.name == deck.name
assert len(context.cards(r_deck)) == 2
assert card_1.id in [card.id for card in context.cards(r_deck)]
assert card_2.id in [card.id for card in context.cards(r_deck)]
async def fetch_channels(client: ClientConnection, message: dict):
filters = []
from_obj = Channel
if message.get('title', None):
filters.append(
or_(Channel.title.ilike(f'%{message["title"]}%'),
Channel.description.ilike('%s' % message['title']),
func.lower(Tag.name).startswith(message['title'].lower())))
from_obj = outerjoin(outerjoin(Channel, ChannelTag), Tag)
if "category_id" in message:
filters.append(Channel.category_id == message["category_id"])
if "members" in message:
filters.append(
Channel.members.between(message["members"][0],
message["members"][1]))
if "cost" in message:
filters.append(
Channel.cost.between(message["cost"][0], message["cost"][1]))
if "likes" in message:
filters.append(
Channel.likes.between(message["likes"][0],
message["likes"][1]))
if "mut_promo" in message:
filters.append(Channel.mutual_promotion == message['mut_promo'])
if "verified" in message:
filters.append(Channel.verified == message['verified'])
if "partner" in message:
# TODO: proper premium functions implementation required
filters.append(Channel.vip == message['partner'])
if 'language' in message:
filters.append(Channel.language == message['language'].lower())
total = await pg.fetchval(
select([count(Channel.id.distinct())
]).select_from(from_obj).where(and_(*filters)))
if total:
sel_q = select([Channel
]).select_from(from_obj).where(and_(*filters))
# Apply ordering
# TODO: proper premium functions implementation required
# TODO: manage sorting
sel_q = sel_q.order_by(desc(Channel.vip), desc(Channel.members),
desc(Channel.cost))
# Apply Limit/Offset
sel_q = sel_q.offset(message['offset']).limit(message['count'])
res = await pg.fetch(sel_q)
# And finally fetch channel tags
tag_q = select([ChannelTag, Tag]).\
select_from(outerjoin(ChannelTag, Tag)).\
where(ChannelTag.channel_id.in_([item['id'] for item in res]))
tags_raw = await pg.fetch(tag_q)
# Serialize all the stuff
tags_dict = {item['id']: [] for item in res}
for entry in tags_raw:
tags_dict[entry['channel_id']].append(entry['name'])
channels = [
dict(list(item.items()) + [('tags', tags_dict[item['id']])])
for item in res
]
else:
channels = []
stat_q = select(
[max(Channel.members),
max(Channel.cost),
max(Channel.likes)])
stats = await pg.fetchrow(stat_q)
message["data"] = {
"items": channels,
"total": total,
"max_members": stats['max_1'],
"max_cost": stats['max_2'],
"max_likes": stats['max_3'],
}
await client.send_response(message)
async def modify_channel(client: ClientConnection, message: dict):
"""
Get available channel tags list
:param client:
:param dict message:
"""
# Check is user authorized
if not client.is_authorized():
await client.send_error(
message['id'], 401,
"client must login before attempting to modify a channel")
return
# Check channel exists in our DB and verified
# Check user is admin
sel_q = select([Channel]).\
select_from(outerjoin(Channel, ChannelAdmin)).\
where(and_(Channel.username == message['username'],
Channel.verified == True,
ChannelAdmin.admin_id == client.session['client_id']))
API._log('DBG: %s' % sel_q)
res = await pg.fetch(sel_q)
if not res:
await client.send_error(
message['id'], 404,
'Channel is not found or not verified or user is not admin')
return
channel = dict(res.items())
updated = {}
# Check category change
if 'category_id' in message:
updated['category_id'] = message['category_id']
# Check mutual promotion changed
if 'mut_promo' in message:
updated['mutual_promotion'] = message['mut_promo']
# Check cost changed
if 'cost' in message:
updated['cost'] = message['cost']
# Check language changed
if 'language' in message:
updated['language'] = message['language']
# Check description changed
if 'description' in message:
updated['description'] = message['description']
# Check tags changed
if 'tags' in message:
# TODO: custom tags can be defined for premium channels
# istartswith analog here
sel_q = select([Tag]).select_from(Tag).\
where(func.lower(Tag.name).in_([tag.lower() for tag in message['tags']]))
API._log('DBG: %s' % sel_q)
tags = await pg.fetch(sel_q)
async with pg.transaction() as conn:
# Delete current tags
del_q = delete(ChannelTag).where(
ChannelTag.channel_id == channel['id'])
await conn.fetchrow(del_q)
# Insert new ones
for tag in tags:
ins_q = insert(ChannelTag).values(channel_id=channel['id'],
tag_id=tag['id'])
await conn.fetchrow(ins_q)
else:
# if not changed, just get current ones
sel_q = select([Tag]).select_from(outerjoin(
ChannelTag, Tag)).where(ChannelTag.channel_id == channel['id'])
tags = await pg.fetch(sel_q)
channel['tags'] = [tag['name'] for tag in tags]
if updated:
upd_q = update(Channel).where(Channel.id == channel['id']).values(
**updated)
await pg.fetchrow(upd_q)
channel.update(updated)
# Return channel object
await client.send_response({
'data': channel,
'id': message['id'],
'action': message['action']
})
def _check_capacity_exceeded(ctx, allocs):
"""Checks to see if the supplied allocation records would result in any of
the inventories involved having their capacity exceeded.
Raises an InvalidAllocationCapacityExceeded exception if any inventory
would be exhausted by the allocation. Raises an
InvalidAllocationConstraintsViolated exception if any of the `step_size`,
`min_unit` or `max_unit` constraints in an inventory will be violated
by any one of the allocations.
If no inventories would be exceeded or violated by the allocations, the
function returns a list of `ResourceProvider` objects that contain the
generation at the time of the check.
:param ctx: `placement.context.RequestContext` that has an oslo_db
Session
:param allocs: List of `Allocation` objects to check
"""
# The SQL generated below looks like this:
# SELECT
# rp.id,
# rp.uuid,
# rp.generation,
# inv.resource_class_id,
# inv.total,
# inv.reserved,
# inv.allocation_ratio,
# allocs.used
# FROM resource_providers AS rp
# JOIN inventories AS i1
# ON rp.id = i1.resource_provider_id
# LEFT JOIN (
# SELECT resource_provider_id, resource_class_id, SUM(used) AS used
# FROM allocations
# WHERE resource_class_id IN ($RESOURCE_CLASSES)
# AND resource_provider_id IN ($RESOURCE_PROVIDERS)
# GROUP BY resource_provider_id, resource_class_id
# ) AS allocs
# ON inv.resource_provider_id = allocs.resource_provider_id
# AND inv.resource_class_id = allocs.resource_class_id
# WHERE rp.id IN ($RESOURCE_PROVIDERS)
# AND inv.resource_class_id IN ($RESOURCE_CLASSES)
#
# We then take the results of the above and determine if any of the
# inventory will have its capacity exceeded.
rc_ids = set(
[rc_cache.RC_CACHE.id_from_string(a.resource_class) for a in allocs])
provider_uuids = set([a.resource_provider.uuid for a in allocs])
provider_ids = set([a.resource_provider.id for a in allocs])
usage = sa.select([
_ALLOC_TBL.c.resource_provider_id, _ALLOC_TBL.c.resource_class_id,
sql.func.sum(_ALLOC_TBL.c.used).label('used')
])
usage = usage.where(
sa.and_(_ALLOC_TBL.c.resource_class_id.in_(rc_ids),
_ALLOC_TBL.c.resource_provider_id.in_(provider_ids)))
usage = usage.group_by(_ALLOC_TBL.c.resource_provider_id,
_ALLOC_TBL.c.resource_class_id)
usage = sa.alias(usage, name='usage')
inv_join = sql.join(
_RP_TBL, _INV_TBL,
sql.and_(_RP_TBL.c.id == _INV_TBL.c.resource_provider_id,
_INV_TBL.c.resource_class_id.in_(rc_ids)))
primary_join = sql.outerjoin(
inv_join, usage,
sql.and_(
_INV_TBL.c.resource_provider_id == usage.c.resource_provider_id,
_INV_TBL.c.resource_class_id == usage.c.resource_class_id))
cols_in_output = [
_RP_TBL.c.id.label('resource_provider_id'),
_RP_TBL.c.uuid,
_RP_TBL.c.generation,
_INV_TBL.c.resource_class_id,
_INV_TBL.c.total,
_INV_TBL.c.reserved,
_INV_TBL.c.allocation_ratio,
_INV_TBL.c.min_unit,
_INV_TBL.c.max_unit,
_INV_TBL.c.step_size,
usage.c.used,
]
sel = sa.select(cols_in_output).select_from(primary_join)
sel = sel.where(
sa.and_(_RP_TBL.c.id.in_(provider_ids),
_INV_TBL.c.resource_class_id.in_(rc_ids)))
records = ctx.session.execute(sel)
# Create a map keyed by (rp_uuid, res_class) for the records in the DB
usage_map = {}
provs_with_inv = set()
for record in records:
map_key = (record['uuid'], record['resource_class_id'])
if map_key in usage_map:
raise KeyError("%s already in usage_map, bad query" % str(map_key))
usage_map[map_key] = record
provs_with_inv.add(record["uuid"])
# Ensure that all providers have existing inventory
missing_provs = provider_uuids - provs_with_inv
if missing_provs:
class_str = ', '.join(
[rc_cache.RC_CACHE.string_from_id(rc_id) for rc_id in rc_ids])
provider_str = ', '.join(missing_provs)
raise exception.InvalidInventory(resource_class=class_str,
resource_provider=provider_str)
res_providers = {}
rp_resource_class_sum = collections.defaultdict(
lambda: collections.defaultdict(int))
for alloc in allocs:
rc_id = rc_cache.RC_CACHE.id_from_string(alloc.resource_class)
rp_uuid = alloc.resource_provider.uuid
if rp_uuid not in res_providers:
res_providers[rp_uuid] = alloc.resource_provider
amount_needed = alloc.used
rp_resource_class_sum[rp_uuid][rc_id] += amount_needed
# No use checking usage if we're not asking for anything
if amount_needed == 0:
continue
key = (rp_uuid, rc_id)
try:
usage = usage_map[key]
except KeyError:
# The resource class at rc_id is not in the usage map.
raise exception.InvalidInventory(
resource_class=alloc.resource_class, resource_provider=rp_uuid)
allocation_ratio = usage['allocation_ratio']
min_unit = usage['min_unit']
max_unit = usage['max_unit']
step_size = usage['step_size']
# check min_unit, max_unit, step_size
if (amount_needed < min_unit or amount_needed > max_unit
or amount_needed % step_size != 0):
LOG.warning(
"Allocation for %(rc)s on resource provider %(rp)s "
"violates min_unit, max_unit, or step_size. "
"Requested: %(requested)s, min_unit: %(min_unit)s, "
"max_unit: %(max_unit)s, step_size: %(step_size)s", {
'rc': alloc.resource_class,
'rp': rp_uuid,
'requested': amount_needed,
'min_unit': min_unit,
'max_unit': max_unit,
'step_size': step_size
})
raise exception.InvalidAllocationConstraintsViolated(
resource_class=alloc.resource_class, resource_provider=rp_uuid)
# usage["used"] can be returned as None
used = usage['used'] or 0
capacity = (usage['total'] - usage['reserved']) * allocation_ratio
if (capacity < (used + amount_needed) or capacity <
(used + rp_resource_class_sum[rp_uuid][rc_id])):
LOG.warning(
"Over capacity for %(rc)s on resource provider %(rp)s. "
"Needed: %(needed)s, Used: %(used)s, Capacity: %(cap)s", {
'rc': alloc.resource_class,
'rp': rp_uuid,
'needed': amount_needed,
'used': used,
'cap': capacity
})
raise exception.InvalidAllocationCapacityExceeded(
resource_class=alloc.resource_class, resource_provider=rp_uuid)
return res_providers
def test_sql_persistence():
repo = SQLADTRepository({
"DB_NAME": "test",
"ECHO": False
})
repo.add_adt_table(Card, "cards")
repo.add_adt_table(Deck, "decks")
repo.create_all_tables()
repo.truncate_all_tables()
with repo.context() as context:
deck = repo.insert_adt(context,
repo.decks,
Deck(
name="Test deck"
)
)
card_1 = repo.insert_adt(context,
repo.cards,
Card(
deck_id=deck.id,
title="Test card #1",
strength=10,
defense=1,
)
)
card_2 = repo.insert_adt(context,
repo.cards,
Card(
deck_id=deck.id,
title="Test card #2",
strength=8,
defense=7,
)
)
with repo.context() as context:
r_deck = repo.retrieve_single_adt(context,
Deck,
select([repo.decks])
.where(repo.decks.c.id == deck.id)
)
assert r_deck.id == deck.id
assert r_deck.name == deck.name
with repo.context() as context:
r_cards = repo.retrieve_adts(context,
Card,
select([repo.cards])
)
assert len(r_cards) == 2
assert card_1.id in [card.id for card in r_cards]
assert card_2.id in [card.id for card in r_cards]
with repo.context() as context:
r_decks = repo.retrieve_joined_adts(context,
Deck, {"decks": Deck, "cards": Card},
select([repo.decks, repo.cards], use_labels=True)
.select_from(outerjoin(
repo.decks, repo.cards, repo.decks.c.id == repo.cards.c.deck_id
))
.where(repo.decks.c.id == deck.id)
)
assert len(r_decks) == 1
r_deck = r_decks[0]
assert r_deck.id == deck.id
assert r_deck.name == deck.name
assert len(context.cards(r_deck)) == 2
assert card_1.id in [card.id for card in context.cards(r_deck)]
assert card_2.id in [card.id for card in context.cards(r_deck)]
def placement_get_select(args, res_template, worker_number):
"""
Returns a sqlalchemy.Select object representing the query against the
resource_providers schema tables, using derived table queries against the
allocations and inventories tables. The SQL generated from this looks like
the following. (If the filter_strategy is not 'db', then the WHERE clause
is not appended to the SQL):
SELECT cn.id, cn.generation
FROM resource_providers AS cn
INNER JOIN inventories AS ram_filtered
ON cn.id = ram_filtered.resource_provider_id
AND ram_filtered.resource_class_id = 2
LEFT OUTER JOIN (
SELECT
allocations.resource_provider_id AS resource_provider_id,
sum(allocations.used) AS used
FROM allocations
WHERE allocations.resource_class_id = 2
GROUP BY allocations.resource_provider_id
) AS ram_usage
ON ram_filtered.resource_provider_id = ram_usage.resource_provider_id
INNER JOIN inventories AS cpu_filtered
ON ram_filtered.resource_provider_id = cpu_filtered.resource_provider_id
AND cpu_filtered.resource_class_id = 1
LEFT OUTER JOIN (
SELECT
allocations.resource_provider_id AS resource_provider_id,
sum(allocations.used) AS used
FROM allocations
WHERE allocations.resource_class_id = 1
GROUP BY allocations.resource_provider_id
) AS cpu_usage
ON cpu_filtered.resource_provider_id = cpu_usage.resource_provider_id
WHERE
ram_filtered.min_unit <= 64
AND ram_filtered.max_unit >= 64
AND floor((ram_filtered.total - ram_filtered.reserved) * ram_filtered.allocation_ratio) - ifnull(ram_usage.used, 0) >= 64
AND cpu_filtered.min_unit <= 1
AND cpu_filtered.max_unit >= 1
AND floor((cpu_filtered.total - cpu_filtered.reserved) * cpu_filtered.allocation_ratio) - ifnull(cpu_usage.used, 0) >= 1
LIMIT 1
Depending on the partition and placement strategy, there may be additional
WHERE clauses that look like the following:
# 'modulo' partition strategy
AND (cn.id + $NUM_WORKERS) % NUM_WORKERS == 0
or:
# 'random' placement strategy
AND cn.id >= $RANDOM_COMPUTE_NODE_ID
The ORDER BY clause will depend on the placement strategy and may look like
the following:
# 'pack' placement strategy
ORDER BY IFNULL(ram_usage.used, 0) ASC, cn.id ASC
or:
# 'spread' placement strategy
ORDER BY IFNULL(ram_usage.used, 0) DESC, cn.id ASC
"""
(rp_tbl, agg_tbl, rp_agg_tbl, inv_tbl, alloc_tbl) = placement_get_tables()
cn_tbl = sa.alias(rp_tbl, name='cn')
ram_filtered = sa.alias(inv_tbl, name='ram_filtered')
cpu_filtered = sa.alias(inv_tbl, name='cpu_filtered')
ram_usage = sa.select([alloc_tbl.c.resource_provider_id,
sql.func.sum(alloc_tbl.c.used).label('used')])
ram_usage = ram_usage.where(alloc_tbl.c.resource_class_id == const.RAM_MB)
ram_usage = ram_usage.group_by(alloc_tbl.c.resource_provider_id)
ram_usage = sa.alias(ram_usage, name='ram_usage')
cpu_usage = sa.select([alloc_tbl.c.resource_provider_id,
sql.func.sum(alloc_tbl.c.used).label('used')])
cpu_usage = cpu_usage.where(alloc_tbl.c.resource_class_id == const.VCPU)
cpu_usage = cpu_usage.group_by(alloc_tbl.c.resource_provider_id)
cpu_usage = sa.alias(cpu_usage, name='cpu_usage')
ram_inv_join = sql.join(cn_tbl, ram_filtered,
sql.and_(
cn_tbl.c.id == ram_filtered.c.resource_provider_id,
ram_filtered.c.resource_class_id == const.RAM_MB))
ram_join = sql.outerjoin(ram_inv_join, ram_usage,
ram_filtered.c.resource_provider_id == ram_usage.c.resource_provider_id)
cpu_inv_join = sql.join(ram_join, cpu_filtered,
sql.and_(
ram_filtered.c.resource_provider_id == cpu_filtered.c.resource_provider_id,
cpu_filtered.c.resource_class_id == const.VCPU))
cpu_join = sql.outerjoin(cpu_inv_join, cpu_usage,
cpu_filtered.c.resource_provider_id == cpu_usage.c.resource_provider_id)
cols_in_output = [cn_tbl.c.id, cn_tbl.c.generation]
if args.filter_strategy == 'python':
# When we don't do stuff on the DB side, we need to pass back
# a whole lot more columns since we have Python code loops
# that need to process these data fields.
cols_in_output.extend([
ram_filtered.c.total.label('ram_total'),
ram_filtered.c.reserved.label('ram_reserved'),
ram_filtered.c.min_unit.label('ram_min_unit'),
ram_filtered.c.max_unit.label('ram_max_unit'),
ram_filtered.c.allocation_ratio.label('ram_allocation_ratio'),
ram_usage.c.used.label('ram_used'),
cpu_filtered.c.total.label('cpu_total'),
cpu_filtered.c.reserved.label('cpu_reserved'),
cpu_filtered.c.min_unit.label('cpu_min_unit'),
cpu_filtered.c.max_unit.label('cpu_max_unit'),
cpu_filtered.c.allocation_ratio.label('cpu_allocation_ratio'),
cpu_usage.c.used.label('cpu_used'),
])
select = sa.select(cols_in_output).select_from(cpu_join)
if args.filter_strategy == 'db':
where_conds = (
ram_filtered.c.min_unit <= res_template[const.RAM_MB],
ram_filtered.c.max_unit >= res_template[const.RAM_MB],
sql.func.floor((ram_filtered.c.total - ram_filtered.c.reserved) * ram_filtered.c.allocation_ratio)
- sql.func.ifnull(ram_usage.c.used, 0) >= res_template[const.RAM_MB],
cpu_filtered.c.min_unit <= res_template[const.VCPU],
cpu_filtered.c.max_unit >= res_template[const.VCPU],
sql.func.floor((cpu_filtered.c.total - cpu_filtered.c.reserved) * cpu_filtered.c.allocation_ratio)
- sql.func.ifnull(cpu_usage.c.used, 0) >= res_template[const.VCPU]
)
if args.partition_strategy == 'modulo':
where_conds += ((cn_tbl.c.id % args.workers) == worker_number,)
if args.placement_strategy == 'pack':
select = select.order_by(sql.func.ifnull(ram_usage.c.used, 0), cn_tbl.c.id)
if args.placement_strategy == 'spread':
select = select.order_by(sql.func.ifnull(ram_usage.c.used, 0).desc(), cn_tbl.c.id)
if args.placement_strategy == 'random':
# The scheduler could keep a cache of the number of compute
# nodes in the system. But here, we emulate that cache by
# simply picking a random compute node ID by selecting a
# random ID since we know the compute nodes are
# sequentially ordered and a fixed number.
num_compute_nodes = (args.rows * args.racks * args.nodes)
num_shared_resource_pools = args.rows * (1 if args.shared_storage_per_row else 0)
random_compute_node_id = random.randint(1 + num_shared_resource_pools,
num_compute_nodes + num_shared_resource_pools)
where_conds += (cn_tbl.c.id >= random_compute_node_id,)
select = select.where(sql.and_(*where_conds))
select = select.limit(1)
return select
def _check_capacity_exceeded(conn, allocs):
"""Checks to see if the supplied allocation records would result in any of
the inventories involved having their capacity exceeded.
Raises an InvalidAllocationCapacityExceeded exception if any inventory
would be exhausted by the allocation. Raises an
InvalidAllocationConstraintsViolated exception if any of the `step_size`,
`min_unit` or `max_unit` constraints in an inventory will be violated
by any one of the allocations.
If no inventories would be exceeded or violated by the allocations, the
function returns a list of `ResourceProvider` objects that contain the
generation at the time of the check.
:param conn: SQLalchemy Connection object to use
:param allocs: List of `Allocation` objects to check
"""
# The SQL generated below looks like this:
# SELECT
# rp.id,
# rp.uuid,
# rp.generation,
# inv.resource_class_id,
# inv.total,
# inv.reserved,
# inv.allocation_ratio,
# allocs.used
# FROM resource_providers AS rp
# JOIN inventories AS i1
# ON rp.id = i1.resource_provider_id
# LEFT JOIN (
# SELECT resource_provider_id, resource_class_id, SUM(used) AS used
# FROM allocations
# WHERE resource_class_id IN ($RESOURCE_CLASSES)
# GROUP BY resource_provider_id, resource_class_id
# ) AS allocs
# ON inv.resource_provider_id = allocs.resource_provider_id
# AND inv.resource_class_id = allocs.resource_class_id
# WHERE rp.uuid IN ($RESOURCE_PROVIDERS)
# AND inv.resource_class_id IN ($RESOURCE_CLASSES)
#
# We then take the results of the above and determine if any of the
# inventory will have its capacity exceeded.
rc_ids = set([_RC_CACHE.id_from_string(a.resource_class)
for a in allocs])
provider_uuids = set([a.resource_provider.uuid for a in allocs])
usage = sa.select([_ALLOC_TBL.c.resource_provider_id,
_ALLOC_TBL.c.consumer_id,
_ALLOC_TBL.c.resource_class_id,
sql.func.sum(_ALLOC_TBL.c.used).label('used')])
usage = usage.where(_ALLOC_TBL.c.resource_class_id.in_(rc_ids))
usage = usage.group_by(_ALLOC_TBL.c.resource_provider_id,
_ALLOC_TBL.c.resource_class_id)
usage = sa.alias(usage, name='usage')
inv_join = sql.join(_RP_TBL, _INV_TBL,
sql.and_(_RP_TBL.c.id == _INV_TBL.c.resource_provider_id,
_INV_TBL.c.resource_class_id.in_(rc_ids)))
primary_join = sql.outerjoin(inv_join, usage,
sql.and_(
_INV_TBL.c.resource_provider_id == usage.c.resource_provider_id,
_INV_TBL.c.resource_class_id == usage.c.resource_class_id)
)
cols_in_output = [
_RP_TBL.c.id.label('resource_provider_id'),
_RP_TBL.c.uuid,
_RP_TBL.c.generation,
_INV_TBL.c.resource_class_id,
_INV_TBL.c.total,
_INV_TBL.c.reserved,
_INV_TBL.c.allocation_ratio,
_INV_TBL.c.min_unit,
_INV_TBL.c.max_unit,
_INV_TBL.c.step_size,
usage.c.used,
]
sel = sa.select(cols_in_output).select_from(primary_join)
sel = sel.where(
sa.and_(_RP_TBL.c.uuid.in_(provider_uuids),
_INV_TBL.c.resource_class_id.in_(rc_ids)))
records = conn.execute(sel)
# Create a map keyed by (rp_uuid, res_class) for the records in the DB
usage_map = {}
provs_with_inv = set()
for record in records:
map_key = (record['uuid'], record['resource_class_id'])
if map_key in usage_map:
raise KeyError("%s already in usage_map, bad query" % str(map_key))
usage_map[map_key] = record
provs_with_inv.add(record["uuid"])
# Ensure that all providers have existing inventory
missing_provs = provider_uuids - provs_with_inv
if missing_provs:
class_str = ', '.join([_RC_CACHE.string_from_id(rc_id)
for rc_id in rc_ids])
provider_str = ', '.join(missing_provs)
raise exception.InvalidInventory(resource_class=class_str,
resource_provider=provider_str)
res_providers = {}
for alloc in allocs:
rc_id = _RC_CACHE.id_from_string(alloc.resource_class)
rp_uuid = alloc.resource_provider.uuid
key = (rp_uuid, rc_id)
usage = usage_map[key]
amount_needed = alloc.used
allocation_ratio = usage['allocation_ratio']
min_unit = usage['min_unit']
max_unit = usage['max_unit']
step_size = usage['step_size']
# check min_unit, max_unit, step_size
if (amount_needed < min_unit or amount_needed > max_unit or
amount_needed % step_size != 0):
LOG.warning(
_LW("Allocation for %(rc)s on resource provider %(rp)s "
"violates min_unit, max_unit, or step_size. "
"Requested: %(requested)s, min_unit: %(min_unit)s, "
"max_unit: %(max_unit)s, step_size: %(step_size)s"),
{'rc': alloc.resource_class,
'rp': rp_uuid,
'requested': amount_needed,
'min_unit': min_unit,
'max_unit': max_unit,
'step_size': step_size})
raise exception.InvalidAllocationConstraintsViolated(
resource_class=alloc.resource_class,
resource_provider=rp_uuid)
# usage["used"] can be returned as None
used = usage['used'] or 0
capacity = (usage['total'] - usage['reserved']) * allocation_ratio
if capacity < (used + amount_needed):
LOG.warning(
_LW("Over capacity for %(rc)s on resource provider %(rp)s. "
"Needed: %(needed)s, Used: %(used)s, Capacity: %(cap)s"),
{'rc': alloc.resource_class,
'rp': rp_uuid,
'needed': amount_needed,
'used': used,
'cap': capacity})
raise exception.InvalidAllocationCapacityExceeded(
resource_class=alloc.resource_class,
resource_provider=rp_uuid)
if rp_uuid not in res_providers:
rp = ResourceProvider(id=usage['resource_provider_id'],
uuid=rp_uuid,
generation=usage['generation'])
res_providers[rp_uuid] = rp
return list(res_providers.values())