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 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())