def _replace_expr(self, table_name: str, linked_col: str, source_row: dict, expr: str):
elem_list = expr.split('.')
if len(elem_list) == 0:
return None, 'empty brackets'
replaced = ''
reference_id = None
current_row = source_row
while len(elem_list) > 0:
elem = elem_list[0]
remaining_elem = len(elem_list) - 1
if elem == '<file-ext>':
if linked_col not in source_row:
return None, 'trying to extract file extension out of column \'{0}\' but it\'s not found: {1}'.\
format(linked_col, current_row)
filename, file_extension = os.path.splitext(source_row[linked_col])
if len(file_extension) == 0:
return None, 'trying to extract file extension out of string \'{0}\' but it\'s empty: {1}'.\
format(linked_col, current_row)
if file_extension[0] == '.':
file_extension = file_extension[1:]
replaced += file_extension
elif (remaining_elem > 0) and (elem in current_row):
# this is a reference primary key to an other table -> store the ID/key and use it the next time
reference_id = current_row[elem]
elif reference_id and (elem in self.db_spec):
# an ID/primary key has been set previously and now a table name has been set
table_name = elem
primarykey_name = get_primarykey_colname(table_name, self.db_spec)
table: Table = self.db_reflection[table_name]
sel = table.select(whereclause=sqlalchemy.sql.text('{0}.{1}={2}'.format(table_name, primarykey_name, reference_id)))
try:
result = self.db_connect.execute(sel)
except sqlalchemy.exc.IntegrityError as e:
return None, 'SQL error reading table \'{0}\' where \'{1}={2}\': {0}'.format(table_name, primarykey_name, reference_id, e)
row = result.fetchone()
if not row:
return None, 'SQL error reading table \'{0}\' where \'{1}={2}\' (empty results)'.\
format(table_name, primarykey_name, reference_id)
current_row = row
elif elem not in current_row:
return None, 'element \'{0}\' not found in dict: {1}'.format(elem, current_row)
else:
# the 'elem' seems to be something directly in the row...
replaced += str(current_row[elem])
# this element has been used; continue with remaining elements
del elem_list[0] # ok, handled, do next
return replaced, None
def _load_row_by_id(self, row_id):
table: Table = self.db_reflection[self.table_name]
primarykey_name = get_primarykey_colname(self.table_name, self.db_spec)
sel = table.select(whereclause=sqlalchemy.sql.text('{0}.{1}={2}'.format(self.table_name, primarykey_name, row_id)))
try:
result = self.db_connect.execute(sel)
except sqlalchemy.exc.IntegrityError as e:
return None, 'SQL error reading table \'{0}\' where \'{1}={2}\': {0}'.format(self.table_name, primarykey_name,
row_id, e)
row = result.fetchone()
if not row:
return None, 'SQL error reading table \'{0}\' where \'{1}={2}\' (empty results)'. \
format(self.table_name, primarykey_name, row_id)
return row, None
def get_primary_id(self, reply, db_spec):
if type(reply) == list:
reply_data = reply[0]
elif type(reply) == dict:
reply_data = reply
primary_colname = get_primarykey_colname(self.resource_name, db_spec)
assert primary_colname in reply_data[
self.
resource_name], 'Primary key {0} not found in reply {1}'.format(
primary_colname, reply_data)
primary_id = reply_data[self.resource_name][primary_colname]
assert type(
primary_id
) == int, 'Primary key {0} is not of type int! (type: {1})'.format(
primary_colname, type(primary_id))
return primary_id
def extended_check_db_content(db_spec, customview_spec, table_name,
db_entry, source_list):
from flask_squirrel.table import dbutil
primary_key = dbutil.get_primarykey_colname(table_name, db_spec)
assert primary_key in db_entry, 'Query response does not contain the primary key \'{0}\': {1}'.format(
primary_key, db_entry)
primary_id = db_entry[primary_key]
assert primary_key is not None, 'Query response contains an empty primary key \'{0}\': {1}'.format(
primary_key, db_entry)
source_entry = [
entry for prim_id, entry in source_list if prim_id == primary_id
]
assert len(
source_entry
) == 1, 'There is an entry in the query result which is not expected! Is the source dict complete? Unexpected DB entry: {0}'.format(
db_entry)
# now compare all source dict columns/field source_entry against db_entry!
source_dict = source_entry[0]
for field in source_dict:
assert field in db_entry, 'Query response does not contain the column \'{0}\': {1}'.format(
field, db_entry)
is_password = False
if field in customview_spec[table_name]:
if '_attributes' in customview_spec[table_name][field]:
if 'password' in customview_spec[table_name][field][
'_attributes']:
is_password = True
if not is_password:
assert db_entry[field] == source_dict[
field], 'Query response column \'{0}\' is not equal to \'{1}\': {2}'.format(
field, source_dict[field], db_entry)
else:
# check if password is empty!
str_len = len(db_entry[field])
assert (str_len
== 0) or (db_entry[field].count('*') == str_len) or (
db_entry[field].count('-')
== str_len), 'Password field is not empty!'
def _handle_post_request(self, action, request_data, parameters):
# Go through each column and check if its available in the JSON request. If not, the field will be set to null
# and it depends whether the SQL server accepts it or not (flag 'NN' = not null).
if action == 'create':
sql_cmd_pre = 'INSERT INTO `{0}`'.format(self.table_name)
sql_cmd_post = ''
elif action == 'edit':
sql_cmd_pre = 'UPDATE `{0}`'.format(self.table_name)
elif action == 'remove':
sql_cmd_pre = 'DELETE FROM `{0}`'.format(self.table_name)
else:
msg = 'Action type {0} unknown!'.format(action)
log.error(msg)
return {'status': 'error', 'error': msg}, 400
primarykey_colname = get_primarykey_colname(self.table_name, self.db_spec)
sql_cmd_values_str = ''
for row_id in request_data:
try:
int(row_id)
except ValueError:
msg = 'Received ID is not a number: {0} (len:{1})'.format(row_id, len(row_id))
log.error(msg)
return {'status': 'error', 'error': msg}, 400
# here, the whole SQL command will be generated step by step
item = request_data[row_id]
unique_query_col = None
unique_query_val = None
if (action == 'edit') or (action == 'remove'):
sql_cmd_post = 'WHERE {0}={1}'.format(primarykey_colname, row_id, primarykey_colname)
# 1. generate SQL values for writing
if action != 'remove':
# do nothing here on 'remove' (will generate errors)
ret_dict, ret_code, sql_cmd_values_str, unique_query_col, unique_query_val =\
self._generate_create_edit_values(action, item, primarykey_colname)
if ret_code != 200:
return ret_dict, ret_code
# 2. generate SQL string
sql_cmd = '{0} {1} {2}'.format(sql_cmd_pre, sql_cmd_values_str, sql_cmd_post)
log.info(sql_cmd)
# 3. optional check if a value has to be unique
if unique_query_col and unique_query_val:
ret_dict, ret_code = self._check_for_unique(action, primarykey_colname, row_id, unique_query_col, unique_query_val)
if ret_code != 200:
return ret_dict, ret_code
# 4. check if linked resource will accept this operation or not
ret_dict, ret_code, previous_row = self._pre_check_linked_resource(action, item, row_id)
if ret_code != 200:
return ret_dict, ret_code
# 5. execute SQL command
ret_dict, ret_code, updated_row_ids, field_errors = self._execute_sql_post_request_item(action, item, sql_cmd, sql_cmd_post, row_id)
if ret_code != 200:
return ret_dict, ret_code
# 6. handle linked resources
# 'previous_row' is the row dict in the DB before it has been changed. This is used for modifying linked
# resources (edit and delete).
ret_dict, ret_code = self._execute_linked_resource_action(action, item, row_id, previous_row)
if ret_code != 200:
# TODO: revert the last SQL statement on errors!
return ret_dict, ret_code
# now read the changed rows and pass it back to the table
sqlfilter = ''
for row_id in updated_row_ids:
if sqlfilter:
sqlfilter += ' OR '
sqlfilter += '({0}={1})'.format(primarykey_colname, row_id)
# update all rows which have been updated, so do not apply any predefined filters in this request
rows_main_table = self._get_rows_main_table(sqlfilter=sqlfilter, predef_filter_arg=None, column_filters=None)
return {'status': 'success', 'data': rows_main_table, 'fieldErrors': field_errors}, 200