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