def add_processing(transform_id, status=ProcessingStatus.New, locking=ProcessingLocking.Idle, submitter=None, granularity=None, granularity_type=None, expired_at=None, processing_metadata=None, output_metadata=None, session=None): """ Add a processing. :param transform_id: Transform id. :param status: processing status. :param locking: processing locking. :param submitter: submitter name. :param granularity: Granularity size. :param granularity_type: Granularity type. :param expired_at: The datetime when it expires. :param processing_metadata: The metadata as json. :raises DuplicatedObject: If a processing with the same name exists. :raises DatabaseException: If there is a database error. :returns: processing id. """ if isinstance(granularity_type, GranularityType): granularity_type = granularity_type.value if isinstance(status, ProcessingStatus): status = status.value if isinstance(locking, ProcessingLocking): locking = locking.value if processing_metadata: processing_metadata = json.dumps(processing_metadata) if output_metadata: output_metadata = json.dumps(output_metadata) insert = """insert into atlas_idds.processings(transform_id, status, locking, submitter, granularity_type, granularity, created_at, updated_at, expired_at, processing_metadata, output_metadata) values(:transform_id, :status, :locking, :submitter, :granularity_type, :granularity, :created_at, :updated_at, :expired_at, :processing_metadata, :output_metadata) returning processing_id into :processing_id """ stmt = text(insert) stmt = stmt.bindparams(outparam("processing_id", type_=BigInteger().with_variant(Integer, "sqlite"))) try: processing_id = None ret = session.execute(stmt, {'transform_id': transform_id, 'status': status, 'locking': locking, 'submitter': submitter, 'granularity_type': granularity_type, 'granularity': granularity, 'created_at': datetime.datetime.utcnow(), 'updated_at': datetime.datetime.utcnow(), 'expired_at': expired_at, 'processing_metadata': processing_metadata, 'output_metadata': output_metadata, 'processing_id': processing_id}) processing_id = ret.out_parameters['processing_id'][0] return processing_id except IntegrityError as error: raise exceptions.DuplicatedObject('Processing already exists!: %s' % (error)) except DatabaseError as error: raise exceptions.DatabaseException(error)
def returning_clause(self, stmt, returning_cols): columns = [] binds = [] for i, column in enumerate(expression._select_iterables(returning_cols)): if column.type._has_column_expression: col_expr = column.type.column_expression(column) else: col_expr = column outparam = sql.outparam("ret_%d" % i, type_=column.type) self.binds[outparam.key] = outparam binds.append(self.bindparam_string(self._truncate_bindparam(outparam))) columns.append(self.process(col_expr, within_columns_clause=False)) self.result_map[outparam.key] = ( outparam.key, (column, getattr(column, "name", None), getattr(column, "key", None)), column.type, ) return "RETURNING " + ", ".join(columns) + " INTO " + ", ".join(binds)
def returning_clause(self, stmt, returning_cols): columns = [] binds = [] for i, column in enumerate(expression._select_iterables(returning_cols)): if column.type._has_column_expression: col_expr = column.type.column_expression(column) else: col_expr = column outparam = sql.outparam("ret_%d" % i, type_=column.type) self.binds[outparam.key] = outparam binds.append(self.bindparam_string(self._truncate_bindparam(outparam))) columns.append(self.process(col_expr, within_columns_clause=False)) self.result_map[outparam.key] = ( outparam.key, (column, getattr(column, 'name', None), getattr(column, 'key', None)), column.type ) return 'RETURNING ' + ', '.join(columns) + " INTO " + ", ".join(binds)
def post(self): connection = db.session.bind.raw_connection() try: cursor = connection.cursor() cursor.callproc("new_user", (None, )) cursor.fetchall() cursor.execute('SELECT @_new_user_0') result = cursor.fetchone() print(result) cursor.close() connection.commit() finally: connection.close() bind = select_bind('coffee02') msg = outparam('msg', type_=types.String) params = [msg] # sp_call = text('CALL new_user)', bindparams=params) result = db.session.execute('CALL new_user(:msg)', params={'msg': 1}) # type: ResultProxy return result.fetchall()
def create_out_param(col, i): bindparam = sql.outparam("ret_%d" % i, type_=col.type) self.binds[bindparam.key] = bindparam return self.bindparam_string(self._truncate_bindparam(bindparam))
def add_collection(scope, name, coll_type=CollectionType.Dataset, transform_id=None, relation_type=CollectionRelationType.Input, bytes=0, status=CollectionStatus.New, locking=CollectionLocking.Idle, total_files=0, retries=0, expired_at=None, coll_metadata=None, session=None): """ Add a collection. :param scope: The scope of the request data. :param name: The name of the request data. :param coll_type: The type of dataset as dataset or container. :param transform_id: The transform id related to this collection. :param relation_type: The relation between this collection and its transform, such as Input, Output, Log and so on. :param size: The size of the collection. :param status: The status. :param locking: The locking. :param total_files: Number of total files. :param retries: Number of retries. :param expired_at: The datetime when it expires. :param coll_metadata: The metadata as json. :raises DuplicatedObject: If a collection with the same name exists. :raises DatabaseException: If there is a database error. :returns: collection id. """ if isinstance(coll_type, CollectionType): coll_type = coll_type.value if isinstance(status, CollectionStatus): status = status.value if isinstance(locking, CollectionLocking): locking = locking.value if isinstance(relation_type, CollectionRelationType): relation_type = relation_type.value if coll_metadata: coll_metadata = json.dumps(coll_metadata) insert_coll_sql = """insert into atlas_idds.collections(scope, name, coll_type, transform_id, relation_type, bytes, status, locking, total_files, retries, created_at, updated_at, expired_at, coll_metadata) values(:scope, :name, :coll_type, :transform_id, :relation_type, :bytes, :status, :locking, :total_files, :retries, :created_at, :updated_at, :expired_at, :coll_metadata) returning coll_id into :coll_id """ stmt = text(insert_coll_sql) stmt = stmt.bindparams(outparam("coll_id", type_=BigInteger().with_variant(Integer, "sqlite"))) try: coll_id = None ret = session.execute(stmt, {'scope': scope, 'name': name, 'coll_type': coll_type, 'transform_id': transform_id, 'relation_type': relation_type, 'bytes': bytes, 'status': status, 'locking': locking, 'total_files': total_files, 'retries': retries, 'created_at': datetime.datetime.utcnow(), 'updated_at': datetime.datetime.utcnow(), 'expired_at': expired_at, 'coll_metadata': coll_metadata, 'coll_id': coll_id}) coll_id = ret.out_parameters['coll_id'][0] return coll_id except IntegrityError as error: raise exceptions.DuplicatedObject('Collection scope:name(%s:%s) with transform_id(%s) already exists!: %s' % (scope, name, transform_id, error)) except DatabaseError as error: raise exceptions.DatabaseException(error)
def add_request(scope, name, requester=None, request_type=None, transform_tag=None, status=RequestStatus.New, locking=RequestLocking.Idle, priority=0, lifetime=30, workload_id=None, request_metadata=None, processing_metadata=None, session=None): """ Add a request. :param scope: The scope of the request data. :param name: The name of the request data. :param requestr: The requester, such as panda, user and so on. :param request_type: The type of the request, such as ESS, DAOD. :param transform_tag: Transform tag, such as ATLAS AMI tag. :param status: The request status as integer. :param locking: The request locking as integer. :param priority: The priority as integer. :param lifetime: The life time as umber of days. :param workload_id: The external workload id. :param request_metadata: The metadata as json. :param processing_metadata: The metadata as json. :raises DuplicatedObject: If an request with the same name exists. :raises DatabaseException: If there is a database error. :returns: request id. """ if isinstance(request_type, RequestType): request_type = request_type.value if isinstance(status, RequestStatus): status = status.value if isinstance(locking, RequestLocking): locking = locking.value if request_metadata: request_metadata = json.dumps(request_metadata) if processing_metadata: processing_metadata = json.dumps(processing_metadata) insert_request_sql = """insert into atlas_idds.requests(scope, name, requester, request_type, transform_tag, priority, status, locking, workload_id, created_at, updated_at, expired_at, request_metadata, processing_metadata) values(:scope, :name, :requester, :request_type, :transform_tag, :priority, :status, :locking, :workload_id, :created_at, :updated_at, :expired_at, :request_metadata, :processing_metadata) RETURNING request_id into :request_id """ stmt = text(insert_request_sql) stmt = stmt.bindparams( outparam("request_id", type_=BigInteger().with_variant(Integer, "sqlite"))) try: request_id = None ret = session.execute( stmt, { "scope": scope, "name": name, "requester": requester, "request_type": request_type, "transform_tag": transform_tag, "priority": priority, 'status': status, 'locking': locking, 'workload_id': workload_id, 'created_at': datetime.datetime.utcnow(), 'updated_at': datetime.datetime.utcnow(), 'expired_at': datetime.datetime.utcnow() + datetime.timedelta(days=lifetime), 'request_metadata': request_metadata, 'processing_metadata': processing_metadata, 'request_id': request_id }) request_id = ret.out_parameters['request_id'][0] return request_id except IntegrityError as error: raise exceptions.DuplicatedObject('Request %s:%s already exists!: %s' % (scope, name, error)) except DatabaseError as error: raise exceptions.DatabaseException(error)
def add_content(coll_id, scope, name, min_id, max_id, content_type=ContentType.File, status=ContentStatus.New, bytes=0, md5=None, adler32=None, processing_id=None, storage_id=None, retries=0, path=None, expired_at=None, content_metadata=None, returning_id=False, session=None): """ Add a content. :param coll_id: collection id. :param scope: The scope of the request data. :param name: The name of the request data. :param min_id: The minimal id of the content. :param max_id: The maximal id of the content. :param content_type: The type of the content. :param status: content status. :param bytes: The size of the content. :param md5: md5 checksum. :param alder32: adler32 checksum. :param processing_id: The processing id. :param storage_id: The storage id. :param retries: The number of retries. :param path: The content path. :param expired_at: The datetime when it expires. :param content_metadata: The metadata as json. :raises DuplicatedObject: If a collection with the same name exists. :raises DatabaseException: If there is a database error. :returns: content id. """ if isinstance(content_type, ContentType): content_type = content_type.value if isinstance(status, ContentStatus): status = status.value if content_metadata: content_metadata = json.dumps(content_metadata) if returning_id: insert_coll_sql = """insert into atlas_idds.contents(coll_id, scope, name, min_id, max_id, content_type, status, bytes, md5, adler32, processing_id, storage_id, retries, path, expired_at, content_metadata) values(:coll_id, :scope, :name, :min_id, :max_id, :content_type, :status, :bytes, :md5, :adler32, :processing_id, :storage_id, :retries, :path, :expired_at, :content_metadata) RETURNING content_id into :content_id """ stmt = text(insert_coll_sql) stmt = stmt.bindparams( outparam("content_id", type_=BigInteger().with_variant(Integer, "sqlite"))) else: insert_coll_sql = """insert into atlas_idds.contents(coll_id, scope, name, min_id, max_id, content_type, status, bytes, md5, adler32, processing_id, storage_id, retries, path, expired_at, content_metadata) values(:coll_id, :scope, :name, :min_id, :max_id, :content_type, :status, :bytes, :md5, :adler32, :processing_id, :storage_id, :retries, :path, :expired_at, :content_metadata) """ stmt = text(insert_coll_sql) try: content_id = None if returning_id: ret = session.execute( stmt, { 'coll_id': coll_id, 'scope': scope, 'name': name, 'min_id': min_id, 'max_id': max_id, 'content_type': content_type, 'status': status, 'bytes': bytes, 'md5': md5, 'adler32': adler32, 'processing_id': processing_id, 'storage_id': storage_id, 'retries': retries, 'path': path, 'created_at': datetime.datetime.utcnow(), 'updated_at': datetime.datetime.utcnow(), 'expired_at': expired_at, 'content_metadata': content_metadata, 'content_id': content_id }) content_id = ret.out_parameters['content_id'][0] else: ret = session.execute( stmt, { 'coll_id': coll_id, 'scope': scope, 'name': name, 'min_id': min_id, 'max_id': max_id, 'content_type': content_type, 'status': status, 'bytes': bytes, 'md5': md5, 'adler32': adler32, 'processing_id': processing_id, 'storage_id': storage_id, 'retries': retries, 'path': path, 'created_at': datetime.datetime.utcnow(), 'updated_at': datetime.datetime.utcnow(), 'expired_at': expired_at, 'content_metadata': content_metadata }) return content_id except IntegrityError as error: raise exceptions.DuplicatedObject( 'Content coll_id:scope:name(%s:%s:%s) already exists!: %s' % (coll_id, scope, name, error)) except DatabaseError as error: raise exceptions.DatabaseException(error)
def add_contents(contents, returning_id=False, bulk_size=100, session=None): """ Add contents. :param contents: dict of contents. :param returning_id: whether to return id. :param session: session. :raises DuplicatedObject: If a collection with the same name exists. :raises DatabaseException: If there is a database error. :returns: content id. """ default_params = { 'coll_id': None, 'scope': None, 'name': None, 'min_id': None, 'max_id': None, 'content_type': ContentType.File, 'status': ContentStatus.New, 'bytes': 0, 'md5': None, 'adler32': None, 'processing_id': None, 'storage_id': None, 'retries': 0, 'path': None, 'expired_at': datetime.datetime.utcnow() + datetime.timedelta(days=30), 'content_metadata': None } if returning_id: insert_coll_sql = """insert into atlas_idds.contents(coll_id, scope, name, min_id, max_id, content_type, status, bytes, md5, adler32, processing_id, storage_id, retries, path, expired_at, content_metadata) values(:coll_id, :scope, :name, :min_id, :max_id, :content_type, :status, :bytes, :md5, :adler32, :processing_id, :storage_id, :retries, :path, :expired_at, :content_metadata) RETURNING content_id into :content_id """ stmt = text(insert_coll_sql) stmt = stmt.bindparams( outparam("content_id", type_=BigInteger().with_variant(Integer, "sqlite"))) else: insert_coll_sql = """insert into atlas_idds.contents(coll_id, scope, name, min_id, max_id, content_type, status, bytes, md5, adler32, processing_id, storage_id, retries, path, expired_at, content_metadata) values(:coll_id, :scope, :name, :min_id, :max_id, :content_type, :status, :bytes, :md5, :adler32, :processing_id, :storage_id, :retries, :path, :expired_at, :content_metadata) """ stmt = text(insert_coll_sql) params = [] for content in contents: param = {} for key in default_params: if key in content: param[key] = content[key] else: param[key] = default_params[key] if isinstance(param['content_type'], ContentType): param['content_type'] = param['content_type'].value if isinstance(param['status'], ContentStatus): param['status'] = param['status'].value if param['content_metadata']: param['content_metadata'] = json.dumps(param['content_metadata']) params.append(param) sub_params = [ params[i:i + bulk_size] for i in range(0, len(params), bulk_size) ] try: content_ids = None if returning_id: content_ids = [] for sub_param in sub_params: content_id = None sub_param['content_id'] = content_id ret = session.execute(stmt, sub_param) content_ids.extend(ret.out_parameters['content_id']) else: for sub_param in sub_params: ret = session.execute(stmt, sub_param) content_ids = [None for _ in range(len(params))] return content_ids except IntegrityError as error: raise exceptions.DuplicatedObject('Duplicated objects: %s' % (error)) except DatabaseError as error: raise exceptions.DatabaseException(error)
def add_transform(transform_type, transform_tag=None, priority=0, status=TransformStatus.New, locking=TransformLocking.Idle, retries=0, expired_at=None, transform_metadata=None, request_id=None, session=None): """ Add a transform. :param transform_type: Transform type. :param transform_tag: Transform tag. :param priority: priority. :param status: Transform status. :param locking: Transform locking. :param retries: The number of retries. :param expired_at: The datetime when it expires. :param transform_metadata: The metadata as json. :raises DuplicatedObject: If a transform with the same name exists. :raises DatabaseException: If there is a database error. :returns: content id. """ if isinstance(transform_type, TransformType): transform_type = transform_type.value if isinstance(status, TransformStatus): status = status.value if isinstance(locking, TransformLocking): locking = locking.value if transform_metadata: transform_metadata = json.dumps(transform_metadata) insert = """insert into atlas_idds.transforms(transform_type, transform_tag, priority, status, locking, retries, created_at, expired_at, transform_metadata) values(:transform_type, :transform_tag, :priority, :status, :locking, :retries, :created_at, :expired_at, :transform_metadata) returning transform_id into :transform_id """ stmt = text(insert) stmt = stmt.bindparams( outparam("transform_id", type_=BigInteger().with_variant(Integer, "sqlite"))) try: transform_id = None ret = session.execute( stmt, { 'transform_type': transform_type, 'transform_tag': transform_tag, 'priority': priority, 'status': status, 'locking': locking, 'retries': retries, 'created_at': datetime.datetime.utcnow(), 'updated_at': datetime.datetime.utcnow(), 'expired_at': expired_at, 'transform_metadata': transform_metadata, 'transform_id': transform_id }) transform_id = ret.out_parameters['transform_id'][0] if request_id: insert_req2transforms = """insert into atlas_idds.req2transforms(request_id, transform_id) values(:request_id, :transform_id) """ stmt = text(insert_req2transforms) session.execute(stmt, { 'request_id': request_id, 'transform_id': transform_id }) return transform_id except IntegrityError as error: raise exceptions.DuplicatedObject('Transform already exists!: %s' % (error)) except DatabaseError as error: raise exceptions.DatabaseException(error)