def add_arch(self, arch): """ Adds the given arch to this OSVersion if it's not already present. """ session.connection(self.__class__).execute(ConditionalInsert( osversion_arch_map, {osversion_arch_map.c.osversion_id: self.id, osversion_arch_map.c.arch_id: arch.id}))
def add_tag(self, tag): """ Adds the given tag to this distro if it's not already present. """ tagobj = DistroTag.lazy_create(tag=tag) session.connection(self.__class__).execute(ConditionalInsert( distro_tag_map, {distro_tag_map.c.distro_id: self.id, distro_tag_map.c.distro_tag_id: tagobj.id}))
def lazy_create(cls, _extra_attrs={}, **kwargs): """ Returns the instance identified by the given uniquely-identifying attributes. If it doesn't exist yet, it is inserted first. _extra_attrs is for attributes which do not make up the unique identity, but which need to be specified during creation because they are not NULLable. Subclasses should override this and pass attributes up in _extra_attrs if needed. BEWARE: this method bypasses SQLAlchemy's ORM machinery. Pass column properties instead of relationship properties (for example, osmajor_id not osmajor). """ # scan the class for any validators that need triggering for key, method in util.iterate_attributes(cls): if hasattr(method, '__sa_validators__'): for arg_key, arg_val in kwargs.iteritems(): if arg_key in method.__sa_validators__: method(cls, arg_key, arg_val) # We do a conditional INSERT, which will always succeed or # silently have no effect. # http://stackoverflow.com/a/6527838/120202 # Note that (contrary to that StackOverflow answer) on InnoDB the # INSERT must come before the UPDATE to avoid deadlocks. unique_params = {} extra_params = {} assert len(class_mapper(cls).tables) == 1 table = class_mapper(cls).tables[0] for k, v in kwargs.iteritems(): unique_params[table.c[k]] = v for k, v in _extra_attrs.iteritems(): extra_params[table.c[k]] = v succeeded = False for attempt in range(1, 7): if attempt > 1: delay = random.uniform(0.001, 0.1) log.debug( 'Backing off %0.3f seconds for insertion into table %s' % (delay, table)) time.sleep(delay) try: session.connection(cls).execute( ConditionalInsert(table, unique_params, extra_params)) succeeded = True break except OperationalError, e: # This seems like a reasonable way to check the string. # We could use a regex, but this is more straightforward. # XXX MySQL-specific if '(OperationalError) (1213' not in unicode(e): raise
def lazy_create(cls, _extra_attrs={}, **kwargs): """ Returns the instance identified by the given uniquely-identifying attributes. If it doesn't exist yet, it is inserted first. _extra_attrs is for attributes which do not make up the unique identity, but which need to be specified during creation because they are not NULLable. Subclasses should override this and pass attributes up in _extra_attrs if needed. BEWARE: this method bypasses SQLAlchemy's ORM machinery. Pass column properties instead of relationship properties (for example, osmajor_id not osmajor). """ # scan the class for any validators that need triggering for key, method in util.iterate_attributes(cls): if hasattr(method, '__sa_validators__'): for arg_key, arg_val in kwargs.iteritems(): if arg_key in method.__sa_validators__: method(cls, arg_key, arg_val) # We do a conditional INSERT, which will always succeed or # silently have no effect. # http://stackoverflow.com/a/6527838/120202 # Note that (contrary to that StackOverflow answer) on InnoDB the # INSERT must come before the UPDATE to avoid deadlocks. unique_params = {} extra_params = {} assert len(class_mapper(cls).tables) == 1 table = class_mapper(cls).tables[0] for k, v in kwargs.iteritems(): unique_params[table.c[k]] = v for k, v in _extra_attrs.iteritems(): extra_params[table.c[k]] = v succeeded = False for attempt in range(1, 7): if attempt > 1: delay = random.uniform(0.001, 0.1) log.debug('Backing off %0.3f seconds for insertion into table %s' % (delay, table)) time.sleep(delay) try: session.connection(cls).execute(ConditionalInsert(table, unique_params, extra_params)) succeeded = True break except OperationalError, e: # This seems like a reasonable way to check the string. # We could use a regex, but this is more straightforward. # XXX MySQL-specific if '(OperationalError) (1213' not in unicode(e): raise
def execute_reporting_query(self, name): sql = pkg_resources.resource_string('bkr.server', 'reporting-queries/%s.sql' % name) return session.connection(System).execute(text(sql))
def generate_data(self, whiteboard, job_ids, toggle_nacks): """Return matrix details generate_data() returns a nested tuple which represents tasks->arches->whiteboards and their data objects """ self.arches_used = {} self.whiteboards_used = {} whiteboard_data = {} # If somehow both are passed, use the whiteboard if whiteboard: if isinstance(whiteboard, basestring): whiteboards = [whiteboard] else: whiteboards = whiteboard # If the whiteboard contains an embedded newline the browser will # have converted it to CRLF, convert it back here. whiteboards = [w.replace('\r\n', '\n') for w in whiteboards] job_ids = [] job_query = model.Job.by_whiteboard(whiteboards).filter(not_(model.Job.is_deleted)) for job in job_query: job_ids.append(job.id) recipes = model.Recipe.query.join(model.Recipe.distro_tree, model.DistroTree.arch)\ .join(model.Recipe.recipeset, model.RecipeSet.job)\ .filter(model.RecipeSet.job_id.in_(job_ids))\ .add_column(model.Arch.arch) # if we're here we are potentially trying to hide naked RS' if toggle_nacks: recipes = recipes.filter(model.RecipeSet.waived == False) # Let's get all the tasks that will be run, and the arch/whiteboard the_tasks = {} for recipe,arch in recipes: the_tasks.update(dict([(rt.name,{}) for rt in recipe.tasks])) if arch in whiteboard_data: if recipe.whiteboard not in whiteboard_data[arch]: whiteboard_data[arch].append(recipe.whiteboard) else: whiteboard_data[arch] = [recipe.whiteboard] case0 = case([(model.RecipeTask.result == u'New',1)],else_=0) case1 = case([(model.RecipeTask.result == u'Pass',1)],else_=0) case2 = case([(model.RecipeTask.result == u'Warn',1)],else_=0) case3 = case([(model.RecipeTask.result == u'Fail',1)],else_=0) case4 = case([(model.RecipeTask.result == u'Panic',1)],else_=0) case5 = case([(model.RecipeTask.result == u'None',1)],else_=0) case6 = case([(model.RecipeTask.result == u'Skip',1)],else_=0) arch_alias = model.Arch.__table__.alias() recipe_table_alias = model.Recipe.__table__.alias() my_select = [model.RecipeTask.name, model.RecipeTask.result, recipe_table_alias.c.whiteboard, arch_alias.c.arch, arch_alias.c.id.label('arch_id'), case0.label('rc0'), case1.label('rc1'), case2.label('rc2'), case3.label('rc3'), case4.label('rc4'), case5.label('rc5'), case6.label('rc6'), ] my_from = [model.RecipeSet.__table__.join(recipe_table_alias). join(model.DistroTree.__table__, model.DistroTree.id == recipe_table_alias.c.distro_tree_id). join(arch_alias, arch_alias.c.id == model.DistroTree.arch_id). join(model.RecipeTask.__table__, model.RecipeTask.recipe_id == recipe_table_alias.c.id)] #If this query starts to bog down and slow up, we could create a view for the inner select (s2) #SQLAlchemy Select object does not really support this,I think you would have to use SQLAlchemy text for s2, and then #build a specific table for it #eng = database.get_engine() #c = s2.compile(eng) #eng.execute("CREATE VIEW foobar AS %s" % c) for arch_val,whiteboard_set in whiteboard_data.iteritems(): for whiteboard_val in whiteboard_set: if whiteboard_val is not None: my_and = [model.RecipeSet.job_id.in_(job_ids), arch_alias.c.arch == bindparam('arch'), recipe_table_alias.c.whiteboard == bindparam('recipe_whiteboard')] else: my_and = [model.RecipeSet.job_id.in_(job_ids), arch_alias.c.arch == bindparam('arch'), recipe_table_alias.c.whiteboard==None] if toggle_nacks: my_and.append(model.RecipeSet.waived == False) s2 = select(my_select,from_obj=my_from,whereclause=and_(*my_and)).alias('foo') s2 = s2.params(arch=arch_val) if whiteboard_val is not None: s2 = s2.params(recipe_whiteboard=whiteboard_val) s1 = select([func.count(s2.c.result), func.sum(s2.c.rc0).label('New'), func.sum(s2.c.rc1).label('Pass'), func.sum(s2.c.rc2).label('Warn'), func.sum(s2.c.rc3).label('Fail'), func.sum(s2.c.rc4).label('Panic'), func.sum(s2.c.rc5).label('None'), func.sum(s2.c.rc6).label('Skip'), s2.c.whiteboard, s2.c.arch, s2.c.arch_id, s2.c.name.label('task_name')], from_obj=[s2]).group_by(s2.c.name).order_by(s2.c.name).alias() results = session.connection(model.Recipe).execute(s1) for task_details in results: if task_details.arch in the_tasks[task_details.task_name]: if (whiteboard_val not in the_tasks[task_details.task_name][task_details.arch]): (the_tasks[task_details.task_name][task_details.arch] [whiteboard_val]) = [task_details] else: (the_tasks[task_details.task_name] [task_details.arch][whiteboard_val]. append(task_details)) else: the_tasks[task_details.task_name][task_details.arch] = \ { whiteboard_val : [task_details] } # Here we append TaskR objects to an array. Each TaskR object # will have a nested dict accessable by a arch/whiteboard key, which will # return a InnerDynamo object. There should be one TaskR object for each # task name self.whiteboards_used = whiteboard_data self.arches_used = dict.fromkeys(whiteboard_data.keys(),1) result_data = [] sorted_tasks = sorted(the_tasks.items()) for task_name,arch_whiteboard_val in sorted_tasks: n = TaskR(task_name) for arch,whiteboard_val in arch_whiteboard_val.items(): for whiteboard,dynamo_objs in whiteboard_val.items(): n.add_result(arch,whiteboard,dynamo_objs) result_data.append(n) self.result_data = result_data return result_data
class MappedObject(object): query = session.query_property() @classmethod def lazy_create(cls, _extra_attrs={}, **kwargs): """ Returns the instance identified by the given uniquely-identifying attributes. If it doesn't exist yet, it is inserted first. _extra_attrs is for attributes which do not make up the unique identity, but which need to be specified during creation because they are not NULLable. Subclasses should override this and pass attributes up in _extra_attrs if needed. BEWARE: this method bypasses SQLAlchemy's ORM machinery. Pass column properties instead of relationship properties (for example, osmajor_id not osmajor). """ # We do a conditional INSERT, which will always succeed or # silently have no effect. # http://stackoverflow.com/a/6527838/120202 # Note that (contrary to that StackOverflow answer) on InnoDB the # INSERT must come before the UPDATE to avoid deadlocks. unique_params = {} extra_params = {} assert len(class_mapper(cls).tables) == 1 table = class_mapper(cls).tables[0] for k, v in kwargs.iteritems(): unique_params[table.c[k]] = v for k, v in _extra_attrs.iteritems(): extra_params[table.c[k]] = v succeeded = False for attempt in range(1, 7): if attempt > 1: delay = random.uniform(0.001, 0.1) log.debug( 'Backing off %0.3f seconds for insertion into table %s' % (delay, table)) time.sleep(delay) try: session.connection(cls).execute( ConditionalInsert(table, unique_params, extra_params)) succeeded = True break except OperationalError, e: # This seems like a reasonable way to check the string. # We could use a regex, but this is more straightforward. # XXX MySQL-specific if '(OperationalError) (1213' not in unicode(e): raise if not succeeded: log.debug('Exhausted maximum attempts of conditional insert') raise e if extra_params: session.connection(cls).execute( table.update().values(extra_params).where( and_(*[ col == value for col, value in unique_params.iteritems() ]))) return cls.query.with_lockmode('update').filter_by(**kwargs).one()