def delete(cls, process_id, table_param, reg_id):
""" Delete a register in db based on the id field of the process model, obtained from a request's process_id url parameter.
Args:
process_id (str): id field of the process model, obtained from a request's process_id url parameter (processs/<process_id>).
Returns:
res (int): the deleted register id field
"""
# sanitize the input string and limit its length
table_name = sanitize_str(table_param, 256)
register_model = eval("Base.classes." + table_name)
try:
res = db.session.query(register_model).filter_by(
id=reg_id).one()
except SQLAlchemyError as e:
error = str(e)
print("Error : ", error)
return error
# perform register delete
db.session.delete(res)
try:
db.session.commit()
except SQLAlchemyError as e:
error = str(e)
print("Error : ", error)
return error
return reg_id
def create(cls, **register):
""" Create a register in a process' table
Args:
register (dict): dict containing the fields of the new register, obtained from json in the body of the request.
Returns:
res (dict): the newly created register model
"""
# sanitize the input string and limit its length
table_name = sanitize_str(register['table'], 256)
Base.prepare(db.engine, reflect=False)
#register_base = Base.classes.test_table
register_base = eval("Base.classes." + table_name)
# set the new values from the values array
register_model = register_base(**register['values'])
# update the register
try:
db.session.add(register_model)
db.session.commit()
new_id = register_model.id
##db.session.expunge_all()
##db.session.close()
res = as_dict(register_model)
except SQLAlchemyError as e:
error = str(e)
print("Error : ", error)
res = {'error_c': error}
return res
def update(cls, **register):
""" Update a register in db based on a json from a request's body parameter.
Args:
register (dict): dict containing the register values
table_param (dict): name of the table
Returns:
register_model (model): the updated model
"""
table_name = sanitize_str(register['table'], 256)
Base.prepare(db.engine, reflect=True)
register_model = eval("Base.classes." + table_name)
# perform query
model = db.session.query(register_model).filter_by(
id=register['reg_id']).one()
# set the new values from the values array
for property, value in register['values'].items():
setattr(model, property, value)
# update the register
try:
db.session.commit()
##db.session.expunge_all()
##db.session.close()
except SQLAlchemyError as e:
error = str(e)
print("Error : ", error)
res['register'] = {'error_e': error}
return as_dict(model)
def delete(cls, process_id, table_param):
""" Delete a table from a process.
Args:
process_id (str): id field of the process.
table_param (str): name of the table
Returns:
res (str): table deleted confirmation message
"""
# query a process model
# TODO: filter by userid and column,value
#update metadata and tables
Base.prepare(db.engine, reflect=True)
# sanitize the input string and limit its length
table_param = sanitize_str(table_param, 256)
register_model = eval("Base.classes." + table_param)
# TODO: verify that the table is in the tables array of the current process
# delete the table
try:
register_model.__table__.drop(db.engine)
reflect_prepare(db, Base)
return table_param + " table deleted"
except SQLAlchemyError as e:
error = str(e)
print("Error : ", error)
return error
def read(cls, process_id, table_param):
""" Performs a query to a process table.
Args:
process_id (str): id field of the process model.
table_param (str): name of the table
Returns:
res (dict): the requested process table.
"""
# query a process model
# TODO: filter by userid and column,value
# sanitize the input string and limit its length
table_param = sanitize_str(table_param, 256)
# query a table
try:
# TODO: query table by name from process tables array
#ptable.read_all(int(process_param))
proc = as_dict(Process.query.filter_by(id=process_id).one())
res_list = json.loads(proc["tables"])
except SQLAlchemyError as e:
error = str(e)
print("Error : ", error)
return error
# search for the name in the keys of elements of the tables array.
try:
return next(x for x in res_list if table_param in x["name"])
except StopIteration:
raise ValueError("No matching record found")
return None
def read(cls, process_id, table_param, reg_id):
""" Performs a query to a process table register.
Args:
process_id (str): id field of the process model.
table_param (str): name of the table
Returns:
res (model): the requested process table.
"""
table_param = sanitize_str(table_param, 256)
register_model = eval("Base.classes." + table_param)
# perform query
res = db.session.query(register_model).filter_by(id=reg_id).one()
return res
def read_all(cls, process_id, table_param):
""" Query all registers of the process table register.
Args:
process_id (str): id field of the process model.
table_param (str): name of the table
Returns:
res (list): the requested list of registers.
"""
# generate list of registers
# TODO: filter by column,value
# TODO: validate if the table is in the process tables array
table_name = sanitize_str(table_param, 256)
register_model = eval("Base.classes." + table_name)
# perform query
res = db.session.query(register_model).all()
return [as_dict(c) for c in res]
def parse_sqlalchemy_column_type(self, input_str):
# sanitize the input string and limit its length
translated_input = sanitize_str(input_str, 256)
valid_types = [
translated_input == "BigInteger", translated_input == "Boolean",
translated_input == "Date", translated_input == "DateTime",
translated_input == "Enum", translated_input == "Float",
translated_input == "Integer", translated_input == "Interval",
translated_input == "LargeBinary", translated_input == "MatchType",
translated_input == "Numeric", translated_input == "PickleType",
translated_input == "SchemaType",
translated_input == "SmallInteger", translated_input == "String",
translated_input == "Text", translated_input == "Time",
translated_input == "Unicode", translated_input == "UnicodeText"
]
if any(valid_types):
return eval(translated_input)
else:
return Integer
class NewModel(Base):
""" Map the columns to a list of register constructor arguments adn create a statement to be executed by the controller"""
table_name = sanitize_str(table_param, 256)
__tablename__ = table_name
__table_args__ = {'extend_existing': True}
id = Column(Integer, primary_key=True)
def __init__(self, **kwargs):
# extract kwargs into class attributes
for property, value in kwargs.items():
setattr(self, property, value)
# set the table
self.meta_table = db.metadata.tables[self.table]
#print(db.metadata.tables)
def create_stmt(self):
return insert(self.meta_table).values(self.values)
def update_stmt(self):
return update(self.meta_table).where(
self.meta_table.c.id == self.reg_id).values(self.values)
def __repr__(self):
return str(self.table)
@classmethod
def create(cls, **register):
""" Create a register in a process' table
Args:
register (dict): dict containing the fields of the new register, obtained from json in the body of the request.
Returns:
res (dict): the newly created register model
"""
# sanitize the input string and limit its length
table_name = sanitize_str(register['table'], 256)
Base.prepare(db.engine, reflect=False)
#register_base = Base.classes.test_table
register_base = eval("Base.classes." + table_name)
# set the new values from the values array
register_model = register_base(**register['values'])
# update the register
try:
db.session.add(register_model)
db.session.commit()
new_id = register_model.id
##db.session.expunge_all()
##db.session.close()
res = as_dict(register_model)
except SQLAlchemyError as e:
error = str(e)
print("Error : ", error)
res = {'error_c': error}
return res
@classmethod
def read(cls, process_id, table_param, reg_id):
""" Performs a query to a process table register.
Args:
process_id (str): id field of the process model.
table_param (str): name of the table
Returns:
res (model): the requested process table.
"""
table_param = sanitize_str(table_param, 256)
register_model = eval("Base.classes." + table_param)
# perform query
res = db.session.query(register_model).filter_by(id=reg_id).one()
return res
@classmethod
def read_all(cls, process_id, table_param):
""" Query all registers of the process table register.
Args:
process_id (str): id field of the process model.
table_param (str): name of the table
Returns:
res (list): the requested list of registers.
"""
# generate list of registers
# TODO: filter by column,value
# TODO: validate if the table is in the process tables array
table_name = sanitize_str(table_param, 256)
register_model = eval("Base.classes." + table_name)
# perform query
res = db.session.query(register_model).all()
return [as_dict(c) for c in res]
@classmethod
def update(cls, **register):
""" Update a register in db based on a json from a request's body parameter.
Args:
register (dict): dict containing the register values
table_param (dict): name of the table
Returns:
register_model (model): the updated model
"""
table_name = sanitize_str(register['table'], 256)
Base.prepare(db.engine, reflect=True)
register_model = eval("Base.classes." + table_name)
# perform query
model = db.session.query(register_model).filter_by(
id=register['reg_id']).one()
# set the new values from the values array
for property, value in register['values'].items():
setattr(model, property, value)
# update the register
try:
db.session.commit()
##db.session.expunge_all()
##db.session.close()
except SQLAlchemyError as e:
error = str(e)
print("Error : ", error)
res['register'] = {'error_e': error}
return as_dict(model)
@classmethod
def delete(cls, process_id, table_param, reg_id):
""" Delete a register in db based on the id field of the process model, obtained from a request's process_id url parameter.
Args:
process_id (str): id field of the process model, obtained from a request's process_id url parameter (processs/<process_id>).
Returns:
res (int): the deleted register id field
"""
# sanitize the input string and limit its length
table_name = sanitize_str(table_param, 256)
register_model = eval("Base.classes." + table_name)
try:
res = db.session.query(register_model).filter_by(
id=reg_id).one()
except SQLAlchemyError as e:
error = str(e)
print("Error : ", error)
return error
# perform register delete
db.session.delete(res)
try:
db.session.commit()
except SQLAlchemyError as e:
error = str(e)
print("Error : ", error)
return error
return reg_id