X-Git-Url: https://osm.etsi.org/gitweb/?a=blobdiff_plain;f=osm_ro%2Fdb_base.py;h=e6e1134d854f21383114c35aa19a93f9eda85931;hb=c19c5653c9ca00d27f4a9247973e5a811f14dfde;hp=26e4c002c96d92eb724c0125512cd4815055f0e6;hpb=07a88ed2e8985d3001766247baad258e264b5338;p=osm%2FRO.git diff --git a/osm_ro/db_base.py b/osm_ro/db_base.py index 26e4c002..e6e1134d 100644 --- a/osm_ro/db_base.py +++ b/osm_ro/db_base.py @@ -1,7 +1,7 @@ # -*- coding: utf-8 -*- ## -# Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U. +# Copyright 2015 Telefonica Investigacion y Desarrollo, S.A.U. # This file is part of openmano # All Rights Reserved. # @@ -35,16 +35,85 @@ import json import time import logging import datetime +from contextlib import contextmanager +from functools import wraps, partial +from threading import Lock from jsonschema import validate as js_v, exceptions as js_e -HTTP_Bad_Request = 400 -HTTP_Unauthorized = 401 -HTTP_Not_Found = 404 -HTTP_Method_Not_Allowed = 405 -HTTP_Request_Timeout = 408 -HTTP_Conflict = 409 -HTTP_Service_Unavailable = 503 -HTTP_Internal_Server_Error = 500 +from .http_tools import errors as httperrors +from .utils import Attempt, get_arg, inject_args + + +RECOVERY_TIME = 3 + +_ATTEMPT = Attempt() + + +def with_transaction(fn=None, cursor=None): + """Decorator that can be used together with instances of the ``db_base`` + class, to perform database actions wrapped in a commit/rollback fashion + + This decorator basically executes the function inside the context object + given by the ``transaction`` method in ``db_base`` + + Arguments: + cursor: [Optional] cursor class + """ + if fn is None: # Allows calling the decorator directly or with parameters + return partial(with_transaction, cursor=cursor) + + @wraps(fn) + def _wrapper(self, *args, **kwargs): + cursor_type = None + if cursor == 'dict': + # MySQLdB define the "cursors" module attribute lazily, + # so we have to defer references to mdb.cursors.DictCursor + cursor_type = mdb.cursors.DictCursor + + with self.transaction(cursor_type): + return fn(self, *args, **kwargs) + + return _wrapper + + +def retry(fn=None, max_attempts=Attempt.MAX, **info): + """Decorator that can be used together with instances of the ``db_base`` + class, to replay a method again after a unexpected error. + + The function being decorated needs to either be a method of ``db_base`` + subclasses or accept an ``db_base`` instance as the first parameter. + + All the extra keyword arguments will be passed to the ``_format_error`` + method + """ + if fn is None: # Allows calling the decorator directly or with parameters + return partial(retry, max_attempts=max_attempts, **info) + + @wraps(fn) + def _wrapper(*args, **kwargs): + self = args[0] + info.setdefault('table', get_arg('table', fn, args, kwargs)) + attempt = Attempt(max_attempts=max_attempts, info=info) + while attempt.countdown >= 0: + try: + return inject_args(fn, attempt=attempt)(*args, **kwargs) + except (mdb.Error, AttributeError) as ex: + self.logger.debug("Attempt #%d", attempt.number) + try: + # The format error will throw exceptions, however it can + # tolerate a certain amount of retries if it judges that + # the error can be solved with retrying + self._format_error(ex, attempt.countdown, **attempt.info) + # Anyway, unexpected/unknown errors can still be retried + except db_base_Exception as db_ex: + if (attempt.countdown < 0 or db_ex.http_code != + httperrors.Internal_Server_Error): + raise + + attempt.count += 1 + + return _wrapper + def _check_valid_uuid(uuid): id_schema = {"type" : "string", "pattern": "^[a-fA-F0-9]{8}(-[a-fA-F0-9]{4}){3}-[a-fA-F0-9]{12}$"} @@ -68,7 +137,7 @@ def _convert_datetime2str(var): for k,v in var.items(): if type(v) is datetime.datetime: var[k]= v.strftime('%Y-%m-%dT%H:%M:%S') - elif type(v) is dict or type(v) is list or type(v) is tuple: + elif type(v) is dict or type(v) is list or type(v) is tuple: _convert_datetime2str(v) if len(var) == 0: return True elif type(var) is list or type(var) is tuple: @@ -76,7 +145,7 @@ def _convert_datetime2str(var): _convert_datetime2str(v) def _convert_bandwidth(data, reverse=False, logger=None): - '''Check the field bandwidth recursivelly and when found, it removes units and convert to number + '''Check the field bandwidth recursivelly and when found, it removes units and convert to number It assumes that bandwidth is well formed Attributes: 'data': dictionary bottle.FormsDict variable to be checked. None or empty is consideted valid @@ -111,7 +180,7 @@ def _convert_bandwidth(data, reverse=False, logger=None): _convert_bandwidth(k, reverse, logger) def _convert_str2boolean(data, items): - '''Check recursively the content of data, and if there is an key contained in items, convert value from string to boolean + '''Check recursively the content of data, and if there is an key contained in items, convert value from string to boolean Done recursively Attributes: 'data': dictionary variable to be checked. None or empty is considered valid @@ -135,17 +204,17 @@ def _convert_str2boolean(data, items): if type(k) is dict or type(k) is tuple or type(k) is list: _convert_str2boolean(k, items) -class db_base_Exception(Exception): +class db_base_Exception(httperrors.HttpMappedError): '''Common Exception for all database exceptions''' - - def __init__(self, message, http_code=HTTP_Bad_Request): - Exception.__init__(self, message) - self.http_code = http_code + + def __init__(self, message, http_code=httperrors.Bad_Request): + super(db_base_Exception, self).__init__(message, http_code) class db_base(): tables_with_created_field=() - - def __init__(self, host=None, user=None, passwd=None, database=None, log_name='db', log_level=None): + + def __init__(self, host=None, user=None, passwd=None, database=None, + log_name='db', log_level=None, lock=None): self.host = host self.user = user self.passwd = passwd @@ -155,9 +224,10 @@ class db_base(): self.logger = logging.getLogger(log_name) if self.log_level: self.logger.setLevel( getattr(logging, log_level) ) - + self.lock = lock or Lock() + def connect(self, host=None, user=None, passwd=None, database=None): - '''Connect to specific data base. + '''Connect to specific data base. The first time a valid host, user, passwd and database must be provided, Following calls can skip this parameters ''' @@ -172,32 +242,33 @@ class db_base(): except mdb.Error as e: raise db_base_Exception("Cannot connect to DataBase '{}' at '{}@{}' Error {}: {}".format( self.database, self.user, self.host, e.args[0], e.args[1]), - http_code = HTTP_Unauthorized ) - + http_code = httperrors.Unauthorized ) + + def escape(self, value): + return self.con.escape(value) + + def escape_string(self, value): + if isinstance(value, unicode): + value = value.encode("utf8") + return self.con.escape_string(value) + + @retry + @with_transaction def get_db_version(self): ''' Obtain the database schema version. Return: (negative, text) if error or version 0.0 where schema_version table is missing (version_int, version_text) if ok ''' cmd = "SELECT version_int,version FROM schema_version" - tries = 2 - while tries: - try: - with self.con: - self.cur = self.con.cursor() - self.logger.debug(cmd) - self.cur.execute(cmd) - rows = self.cur.fetchall() - highest_version_int=0 - highest_version="" - for row in rows: #look for the latest version - if row[0]>highest_version_int: - highest_version_int, highest_version = row[0:2] - return highest_version_int, highest_version - except (mdb.Error, AttributeError) as e: - #self.logger.error("get_db_version DB Exception %d: %s. Command %s",e.args[0], e.args[1], cmd) - self._format_error(e, tries) - tries -= 1 + self.logger.debug(cmd) + self.cur.execute(cmd) + rows = self.cur.fetchall() + highest_version_int=0 + highest_version="" + for row in rows: #look for the latest version + if row[0]>highest_version_int: + highest_version_int, highest_version = row[0:2] + return highest_version_int, highest_version def disconnect(self): '''disconnect from specific data base''' @@ -211,10 +282,76 @@ class db_base(): if e[0][-5:] == "'con'": self.logger.warn("while disconnecting from DB: Error %d: %s",e.args[0], e.args[1]) return - else: + else: raise - def _format_error(self, e, tries=1, command=None, extra=None): + def reconnect(self): + """Try to gracefully to the database in case of error""" + try: + self.con.ping(True) # auto-reconnect if the server is available + except: + # The server is probably not available... + # Let's wait a bit + time.sleep(RECOVERY_TIME) + self.con = None + self.connect() + + def fork_connection(self): + """Return a new database object, with a separated connection to the + database (and lock), so it can act independently + """ + obj = self.__class__( + host=self.host, + user=self.user, + passwd=self.passwd, + database=self.database, + log_name=self.logger.name, + log_level=self.log_level, + lock=Lock() + ) + + obj.connect() + + return obj + + @contextmanager + def transaction(self, cursor_type=None): + """DB changes that are executed inside this context will be + automatically rolled back in case of error. + + This implementation also adds a lock, so threads sharing the same + connection object are synchronized. + + Arguments: + cursor_type: default: MySQLdb.cursors.DictCursor + + Yields: + Cursor object + + References: + https://www.oreilly.com/library/view/mysql-cookbook-2nd/059652708X/ch15s08.html + https://github.com/PyMySQL/mysqlclient-python/commit/c64915b1e5c705f4fb10e86db5dcfed0b58552cc + """ + # Previously MySQLdb had built-in support for that using the context + # API for the connection object. + # This support was removed in version 1.40 + # https://github.com/PyMySQL/mysqlclient-python/blob/master/HISTORY.rst#whats-new-in-140 + with self.lock: + try: + if self.con.get_autocommit(): + self.con.query("BEGIN") + + self.cur = self.con.cursor(cursor_type) + yield self.cur + except: # noqa + self.con.rollback() + raise + else: + self.con.commit() + + + def _format_error(self, e, tries=1, command=None, + extra=None, table=None, cmd=None, **_): '''Creates a text error base on the produced exception Params: e: mdb exception @@ -224,24 +361,32 @@ class db_base(): extra: extra information to add to some commands Return HTTP error in negative, formatted error text - ''' + ''' # the **_ ignores extra kwargs + table_info = ' (table `{}`)'.format(table) if table else '' + if cmd: + self.logger.debug("Exception '%s' with command '%s'%s", + e, cmd, table_info) + if isinstance(e,AttributeError ): - raise db_base_Exception("DB Exception " + str(e), HTTP_Internal_Server_Error) + self.logger.debug(str(e), exc_info=True) + raise db_base_Exception("DB Exception " + str(e), httperrors.Internal_Server_Error) if e.args[0]==2006 or e.args[0]==2013 : #MySQL server has gone away (((or))) Exception 2013: Lost connection to MySQL server during query - if tries>1: + # Let's aways reconnect if the connection is lost + # so future calls are not affected. + self.reconnect() + + if tries > 1: self.logger.warn("DB Exception '%s'. Retry", str(e)) - #reconnect - self.connect() return else: - raise db_base_Exception("Database connection timeout Try Again", HTTP_Request_Timeout) - + raise db_base_Exception("Database connection timeout Try Again", httperrors.Request_Timeout) + fk=e.args[1].find("foreign key constraint fails") if fk>=0: if command=="update": - raise db_base_Exception("tenant_id '{}' not found.".format(extra), HTTP_Not_Found) + raise db_base_Exception("tenant_id '{}' not found.".format(extra), httperrors.Not_Found) elif command=="delete": - raise db_base_Exception("Resource is not free. There are {} that prevent deleting it.".format(extra), HTTP_Conflict) + raise db_base_Exception("Resource is not free. There are {} that prevent deleting it.".format(extra), httperrors.Conflict) de = e.args[1].find("Duplicate entry") fk = e.args[1].find("for key") uk = e.args[1].find("Unknown column") @@ -250,91 +395,141 @@ class db_base(): #print de, fk, uk, wc,fl if de>=0: if fk>=0: #error 1062 - raise db_base_Exception("Value {} already in use for {}".format(e.args[1][de+15:fk], e.args[1][fk+7:]), HTTP_Conflict) + raise db_base_Exception( + "Value {} already in use for {}{}".format( + e.args[1][de+15:fk], e.args[1][fk+7:], table_info), + httperrors.Conflict) if uk>=0: if wc>=0: - raise db_base_Exception("Field {} can not be used for filtering".format(e.args[1][uk+14:wc]), HTTP_Bad_Request) + raise db_base_Exception( + "Field {} can not be used for filtering{}".format( + e.args[1][uk+14:wc], table_info), + httperrors.Bad_Request) if fl>=0: - raise db_base_Exception("Field {} does not exist".format(e.args[1][uk+14:wc]), HTTP_Bad_Request) - raise db_base_Exception("Database internal Error {}: {}".format(e.args[0], e.args[1]), HTTP_Internal_Server_Error) - + raise db_base_Exception( + "Field {} does not exist{}".format( + e.args[1][uk+14:wc], table_info), + httperrors.Bad_Request) + raise db_base_Exception( + "Database internal Error{} {}: {}".format( + table_info, e.args[0], e.args[1]), + httperrors.Internal_Server_Error) + def __str2db_format(self, data): - '''Convert string data to database format. + """Convert string data to database format. If data is None it returns the 'Null' text, otherwise it returns the text surrounded by quotes ensuring internal quotes are escaped. - ''' - if data==None: + """ + if data is None: return 'Null' - elif isinstance(data[1], str): + elif isinstance(data[1], (str, unicode)): return json.dumps(data) else: return json.dumps(str(data)) - + def __tuple2db_format_set(self, data): - '''Compose the needed text for a SQL SET, parameter 'data' is a pair tuple (A,B), - and it returns the text 'A="B"', where A is a field of a table and B is the value + """Compose the needed text for a SQL SET, parameter 'data' is a pair tuple (A,B), + and it returns the text 'A="B"', where A is a field of a table and B is the value If B is None it returns the 'A=Null' text, without surrounding Null by quotes If B is not None it returns the text "A='B'" or 'A="B"' where B is surrounded by quotes, and it ensures internal quotes of B are escaped. - ''' - if data[1]==None: + B can be also a dict with special keys: + {"INCREMENT": NUMBER}, then it produce "A=A+NUMBER" + """ + if data[1] is None: return str(data[0]) + "=Null" - elif isinstance(data[1], str): - return str(data[0]) + '=' + json.dumps(data[1]) - else: - return str(data[0]) + '=' + json.dumps(str(data[1])) - - def __tuple2db_format_where(self, data): - '''Compose the needed text for a SQL WHERE, parameter 'data' is a pair tuple (A,B), - and it returns the text 'A="B"', where A is a field of a table and B is the value - If B is None it returns the 'A is Null' text, without surrounding Null by quotes - If B is not None it returns the text "A='B'" or 'A="B"' where B is surrounded by quotes, - and it ensures internal quotes of B are escaped. - ''' - if data[1]==None: - return str(data[0]) + " is Null" - elif isinstance(data[1], str): + elif isinstance(data[1], (str, unicode)): return str(data[0]) + '=' + json.dumps(data[1]) + elif isinstance(data[1], dict): + if "INCREMENT" in data[1]: + return "{A}={A}{N:+d}".format(A=data[0], N=data[1]["INCREMENT"]) + raise db_base_Exception("Format error for UPDATE field: {!r}".format(data[0])) else: return str(data[0]) + '=' + json.dumps(str(data[1])) - def __tuple2db_format_where_not(self, data): - '''Compose the needed text for a SQL WHERE(not). parameter 'data' is a pair tuple (A,B), - and it returns the text 'A<>"B"', where A is a field of a table and B is the value - If B is None it returns the 'A is not Null' text, without surrounding Null by quotes - If B is not None it returns the text "A<>'B'" or 'A<>"B"' where B is surrounded by quotes, - and it ensures internal quotes of B are escaped. - ''' - if data[1]==None: - return str(data[0]) + " is not Null" - elif isinstance(data[1], str): - return str(data[0]) + '<>' + json.dumps(data[1]) + def __create_where(self, data, use_or=None): + """ + Compose the needed text for a SQL WHERE, parameter 'data' can be a dict or a list of dict. By default lists are + concatenated with OR and dict with AND, unless parameter 'use_or' indicates other thing. + If a dict it will generate 'key1="value1" AND key2="value2" AND ...'. + If value is None, it will produce 'key is null' + If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...' + keys can be suffixed by >,<,<>,>=,<=,' LIKE ' so that this is used to compare key and value instead of "=" + The special keys "OR", "AND" with a dict value is used to create a nested WHERE + If a list, each item will be a dictionary that will be concatenated with OR by default + :param data: dict or list of dicts + :param use_or: Can be None (use default behaviour), True (use OR) or False (use AND) + :return: a string with the content to send to mysql + """ + cmd = [] + if isinstance(data, dict): + for k, v in data.items(): + if k == "OR": + cmd.append("(" + self.__create_where(v, use_or=True) + ")") + continue + elif k == "AND": + cmd.append("(" + self.__create_where(v, use_or=False) + ")") + continue + + if k.endswith(">") or k.endswith("<") or k.endswith("=") or k.endswith(" LIKE "): + pass + else: + k += "=" + + if v is None: + cmd.append(k.replace("=", " is").replace("<>", " is not") + " Null") + elif isinstance(v, (tuple, list)): + cmd2 = [] + for v2 in v: + if v2 is None: + cmd2.append(k.replace("=", " is").replace("<>", " is not") + " Null") + elif isinstance(v2, (str, unicode)): + cmd2.append(k + json.dumps(v2)) + else: + cmd2.append(k + json.dumps(str(v2))) + cmd.append("(" + " OR ".join(cmd2) + ")") + elif isinstance(v, (str, unicode)): + cmd.append(k + json.dumps(v)) + else: + cmd.append(k + json.dumps(str(v))) + elif isinstance(data, (tuple, list)): + if use_or is None: + use_or = True + for k in data: + cmd.append("(" + self.__create_where(k) + ")") else: - return str(data[0]) + '<>' + json.dumps(str(data[1])) - + raise db_base_Exception("invalid WHERE clause at '{}'".format(data)) + if use_or: + return " OR ".join(cmd) + return " AND ".join(cmd) + def __remove_quotes(self, data): '''remove single quotes ' of any string content of data dictionary''' for k,v in data.items(): if type(v) == str: - if "'" in v: + if "'" in v: data[k] = data[k].replace("'","_") - + def _update_rows(self, table, UPDATE, WHERE, modified_time=0): - ''' Update one or several rows into a table. - Atributes - UPDATE: dictionary with the key: value to change - table: table where to update - WHERE: dictionary of elements to update - Return: the number of updated rows, exception if error - ''' - #gettting uuid + """ Update one or several rows of a table. + :param UPDATE: dictionary with the changes. dict keys are database columns that will be set with the dict values + :param table: database table to update + :param WHERE: dict or list of dicts to compose the SQL WHERE clause. + If a dict it will generate 'key1="value1" AND key2="value2" AND ...'. + If value is None, it will produce 'key is null' + If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...' + keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "=" + The special keys "OR", "AND" with a dict value is used to create a nested WHERE + If a list, each item will be a dictionary that will be concatenated with OR + :return: the number of updated rows, raises exception upon error + """ + # gettting uuid values = ",".join(map(self.__tuple2db_format_set, UPDATE.iteritems() )) if modified_time: values += ",modified_at={:f}".format(modified_time) - cmd= "UPDATE " + table +" SET " + values +\ - " WHERE " + " and ".join(map(self.__tuple2db_format_where, WHERE.iteritems() )) + cmd= "UPDATE " + table + " SET " + values + " WHERE " + self.__create_where(WHERE) self.logger.debug(cmd) - self.cur.execute(cmd) + self.cur.execute(cmd) return self.cur.rowcount def _new_uuid(self, root_uuid=None, used_table=None, created_time=0): @@ -361,13 +556,13 @@ class db_base(): self.cur.execute(cmd) return uuid - def _new_row_internal(self, table, INSERT, add_uuid=False, root_uuid=None, created_time=0): + def _new_row_internal(self, table, INSERT, add_uuid=False, root_uuid=None, created_time=0, confidential_data=False): ''' Add one row into a table. It DOES NOT begin or end the transaction, so self.con.cursor must be created - Attribute + Attribute INSERT: dictionary with the key:value to insert table: table where to insert add_uuid: if True, it will create an uuid key entry at INSERT if not provided - created_time: time to add to the created_time column + created_time: time to add to the created_at column It checks presence of uuid and add one automatically otherwise Return: uuid ''' @@ -376,7 +571,7 @@ class db_base(): #create uuid if not provided if 'uuid' not in INSERT: uuid = INSERT['uuid'] = str(myUuid.uuid1()) # create_uuid - else: + else: uuid = str(INSERT['uuid']) else: uuid=None @@ -394,10 +589,15 @@ class db_base(): self.cur.execute(cmd) #insertion cmd= "INSERT INTO " + table +" SET " + \ - ",".join(map(self.__tuple2db_format_set, INSERT.iteritems() )) + ",".join(map(self.__tuple2db_format_set, INSERT.iteritems() )) if created_time: - cmd += ",created_at=%f" % created_time - self.logger.debug(cmd) + cmd += ",created_at={time:.9f},modified_at={time:.9f}".format(time=created_time) + if confidential_data: + index = cmd.find("SET") + subcmd = cmd[:index] + 'SET...' + self.logger.debug(subcmd) + else: + self.logger.debug(cmd) self.cur.execute(cmd) self.cur.rowcount return uuid @@ -408,197 +608,160 @@ class db_base(): self.cur.execute(cmd) rows = self.cur.fetchall() return rows - - def new_row(self, table, INSERT, add_uuid=False, created_time=0): + + @retry + @with_transaction + def new_row(self, table, INSERT, add_uuid=False, created_time=0, confidential_data=False): ''' Add one row into a table. - Attribute + Attribute INSERT: dictionary with the key: value to insert table: table where to insert tenant_id: only useful for logs. If provided, logs will use this tenant_id add_uuid: if True, it will create an uuid key entry at INSERT if not provided It checks presence of uuid and add one automatically otherwise - Return: (result, uuid) where result can be 0 if error, or 1 if ok + Return: uuid ''' if table in self.tables_with_created_field and created_time==0: created_time=time.time() - tries = 2 - while tries: - try: - with self.con: - self.cur = self.con.cursor() - return self._new_row_internal(table, INSERT, add_uuid, None, created_time) - - except (mdb.Error, AttributeError) as e: - self._format_error(e, tries) - tries -= 1 - - def update_rows(self, table, UPDATE, WHERE, modified_time=0): - ''' Update one or several rows into a table. - Atributes - UPDATE: dictionary with the key: value to change - table: table where to update - WHERE: dictionary of elements to update - Return: (result, descriptive text) where result indicates the number of updated files - ''' - if table in self.tables_with_created_field and modified_time==0: - modified_time=time.time() - tries = 2 - while tries: - try: - with self.con: - self.cur = self.con.cursor() - return self._update_rows(table, UPDATE, WHERE) - - except (mdb.Error, AttributeError) as e: - self._format_error(e, tries) - tries -= 1 + return self._new_row_internal(table, INSERT, add_uuid, None, created_time, confidential_data) + + @retry + @with_transaction + def update_rows(self, table, UPDATE, WHERE, modified_time=None, attempt=_ATTEMPT): + """ Update one or several rows of a table. + :param UPDATE: dictionary with the changes. dict keys are database columns that will be set with the dict values + :param table: database table to update + :param WHERE: dict or list of dicts to compose the SQL WHERE clause. + If a dict it will generate 'key1="value1" AND key2="value2" AND ...'. + If value is None, it will produce 'key is null' + If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...' + keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "=" + The special keys "OR", "AND" with a dict value is used to create a nested WHERE + If a list, each item will be a dictionary that will be concatenated with OR + :param modified_time: Can contain the time to be set to the table row. + None to set automatically, 0 to do not modify it + :return: the number of updated rows, raises exception upon error + """ + if table in self.tables_with_created_field and modified_time is None: + modified_time = time.time() + + return self._update_rows(table, UPDATE, WHERE, modified_time) + def _delete_row_by_id_internal(self, table, uuid): + cmd = "DELETE FROM {} WHERE uuid = '{}'".format(table, uuid) + self.logger.debug(cmd) + self.cur.execute(cmd) + deleted = self.cur.rowcount + # delete uuid + self.cur = self.con.cursor() + cmd = "DELETE FROM uuids WHERE root_uuid = '{}'".format(uuid) + self.logger.debug(cmd) + self.cur.execute(cmd) + return deleted + + @retry(command='delete', extra='dependencies') + @with_transaction def delete_row_by_id(self, table, uuid): - tries = 2 - while tries: - try: - with self.con: - #delete host - self.cur = self.con.cursor() - cmd = "DELETE FROM {} WHERE uuid = '{}'".format(table, uuid) - self.logger.debug(cmd) - self.cur.execute(cmd) - deleted = self.cur.rowcount - if deleted: - #delete uuid - self.cur = self.con.cursor() - cmd = "DELETE FROM uuids WHERE root_uuid = '{}'".format(uuid) - self.logger.debug(cmd) - self.cur.execute(cmd) - return deleted - except (mdb.Error, AttributeError) as e: - self._format_error(e, tries, "delete", "dependencies") - tries -= 1 - - def delete_row(self, **sql_dict): - ''' Deletes rows from a table. - Attribute sql_dir: dictionary with the following key: value - 'FROM': string of table name (Mandatory) - 'WHERE': dict of key:values, translated to key=value AND ... (Optional) - 'WHERE_NOT': dict of key:values, translated to key<>value AND ... (Optional) - if value is None, it is translated to key is not null - 'LIMIT': limit of number of rows (Optional) - Return: the number of deleted or exception if error - ''' - #print sql_dict - from_ = "FROM " + str(sql_dict['FROM']) - #print 'from_', from_ - if 'WHERE' in sql_dict and len(sql_dict['WHERE']) > 0: - w=sql_dict['WHERE'] - where_ = "WHERE " + " AND ".join(map(self.__tuple2db_format_where, w.iteritems())) - else: where_ = "" - if 'WHERE_NOT' in sql_dict and len(sql_dict['WHERE_NOT']) > 0: - w=sql_dict['WHERE_NOT'] - where_2 = " AND ".join(map(self.__tuple2db_format_where_not, w.iteritems())) - if len(where_)==0: where_ = "WHERE " + where_2 - else: where_ = where_ + " AND " + where_2 - #print 'where_', where_ - limit_ = "LIMIT " + str(sql_dict['LIMIT']) if 'LIMIT' in sql_dict else "" - #print 'limit_', limit_ - cmd = " ".join( ("DELETE", from_, where_, limit_) ) - tries = 2 - while tries: - try: - with self.con: - self.cur = self.con.cursor() - self.logger.debug(cmd) - self.cur.execute(cmd) - deleted = self.cur.rowcount - return deleted - except (mdb.Error, AttributeError) as e: - self._format_error(e, tries) - tries -= 1 - - def get_rows_by_id(self, table, uuid): + return self._delete_row_by_id_internal(table, uuid) + + @retry + def delete_row(self, attempt=_ATTEMPT, **sql_dict): + """ Deletes rows from a table. + :param UPDATE: dictionary with the changes. dict keys are database columns that will be set with the dict values + :param FROM: string with table name (Mandatory) + :param WHERE: dict or list of dicts to compose the SQL WHERE clause. (Optional) + If a dict it will generate 'key1="value1" AND key2="value2" AND ...'. + If value is None, it will produce 'key is null' + If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...' + keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "=" + The special keys "OR", "AND" with a dict value is used to create a nested WHERE + If a list, each item will be a dictionary that will be concatenated with OR + :return: the number of deleted rows, raises exception upon error + """ + # print sql_dict + cmd = "DELETE FROM " + str(sql_dict['FROM']) + if sql_dict.get('WHERE'): + cmd += " WHERE " + self.__create_where(sql_dict['WHERE']) + if sql_dict.get('LIMIT'): + cmd += " LIMIT " + str(sql_dict['LIMIT']) + + attempt.info['cmd'] = cmd + + with self.transaction(): + self.logger.debug(cmd) + self.cur.execute(cmd) + deleted = self.cur.rowcount + return deleted + + @retry + @with_transaction(cursor='dict') + def get_rows_by_id(self, table, uuid, attempt=_ATTEMPT): '''get row from a table based on uuid''' - tries = 2 - while tries: - try: - with self.con: - self.cur = self.con.cursor(mdb.cursors.DictCursor) - cmd="SELECT * FROM {} where uuid='{}'".format(str(table), str(uuid)) - self.logger.debug(cmd) - self.cur.execute(cmd) - rows = self.cur.fetchall() - return rows - except (mdb.Error, AttributeError) as e: - self._format_error(e, tries) - tries -= 1 - - def get_rows(self, **sql_dict): - ''' Obtain rows from a table. - Attribute sql_dir: dictionary with the following key: value - 'SELECT': list or tuple of fields to retrieve) (by default all) - 'FROM': string of table name (Mandatory) - 'WHERE': dict of key:values, translated to key=value (key is null) AND ... (Optional) - 'WHERE_NOT': dict of key:values, translated to key<>value (key is not null) AND ... (Optional) - 'WHERE_OR': dict of key:values, translated to key=value OR ... (Optional) - 'WHERE_AND_OR: str 'AND' or 'OR'(by default) mark the priority to 'WHERE AND (WHERE_OR)' or (WHERE) OR WHERE_OR' (Optional) - 'LIMIT': limit of number of rows (Optional) - 'ORDER_BY': list or tuple of fields to order - Return: a list with dictionaries at each row - ''' - #print sql_dict - select_= "SELECT " + ("*" if 'SELECT' not in sql_dict else ",".join(map(str,sql_dict['SELECT'])) ) - #print 'select_', select_ - from_ = "FROM " + str(sql_dict['FROM']) - #print 'from_', from_ - where_and = "" - where_or = "" - w=sql_dict.get('WHERE') - if w: - where_and = " AND ".join(map(self.__tuple2db_format_where, w.iteritems() )) - w=sql_dict.get('WHERE_NOT') - if w: - if where_and: where_and += " AND " - where_and += " AND ".join(map(self.__tuple2db_format_where_not, w.iteritems() ) ) - w=sql_dict.get('WHERE_OR') - if w: - where_or = " OR ".join(map(self.__tuple2db_format_where, w.iteritems() )) - if where_and and where_or: - if sql_dict.get("WHERE_AND_OR") == "AND": - where_ = "WHERE " + where_and + " AND (" + where_or + ")" + cmd="SELECT * FROM {} where uuid='{}'".format(str(table), str(uuid)) + attempt.info['cmd'] = cmd + self.logger.debug(cmd) + self.cur.execute(cmd) + rows = self.cur.fetchall() + return rows + + @retry + def get_rows(self, attempt=_ATTEMPT, **sql_dict): + """ Obtain rows from a table. + :param SELECT: list or tuple of fields to retrieve) (by default all) + :param FROM: string with table name (Mandatory) + :param WHERE: dict or list of dicts to compose the SQL WHERE clause. (Optional) + If a dict it will generate 'key1="value1" AND key2="value2" AND ...'. + If value is None, it will produce 'key is null' + If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...' + keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "=" + The special keys "OR", "AND" with a dict value is used to create a nested WHERE + If a list, each item will be a dictionary that will be concatenated with OR + :param LIMIT: limit the number of obtained entries (Optional) + :param ORDER_BY: list or tuple of fields to order, add ' DESC' to each item if inverse order is required + :return: a list with dictionaries at each row, raises exception upon error + """ + # print sql_dict + cmd = "SELECT " + if 'SELECT' in sql_dict: + if isinstance(sql_dict['SELECT'], (tuple, list)): + cmd += ",".join(map(str, sql_dict['SELECT'])) else: - where_ = "WHERE (" + where_and + ") OR " + where_or - elif where_and and not where_or: - where_ = "WHERE " + where_and - elif not where_and and where_or: - where_ = "WHERE " + where_or + cmd += sql_dict['SELECT'] else: - where_ = "" - #print 'where_', where_ - limit_ = "LIMIT " + str(sql_dict['LIMIT']) if 'LIMIT' in sql_dict else "" - order_ = "ORDER BY " + ",".join(map(str,sql_dict['SELECT'])) if 'ORDER_BY' in sql_dict else "" - - #print 'limit_', limit_ - cmd = " ".join( (select_, from_, where_, limit_, order_) ) - tries = 2 - while tries: - try: - with self.con: - self.cur = self.con.cursor(mdb.cursors.DictCursor) - self.logger.debug(cmd) - self.cur.execute(cmd) - rows = self.cur.fetchall() - return rows - except (mdb.Error, AttributeError) as e: - self._format_error(e, tries) - tries -= 1 - - def get_table_by_uuid_name(self, table, uuid_name, error_item_text=None, allow_serveral=False, WHERE_OR={}, WHERE_AND_OR="OR"): + cmd += "*" + + cmd += " FROM " + str(sql_dict['FROM']) + if sql_dict.get('WHERE'): + cmd += " WHERE " + self.__create_where(sql_dict['WHERE']) + + if 'ORDER_BY' in sql_dict: + cmd += " ORDER BY " + if isinstance(sql_dict['ORDER_BY'], (tuple, list)): + cmd += ",".join(map(str, sql_dict['ORDER_BY'])) + else: + cmd += str(sql_dict['ORDER_BY']) + + if 'LIMIT' in sql_dict: + cmd += " LIMIT " + str(sql_dict['LIMIT']) + + attempt.info['cmd'] = cmd + + with self.transaction(mdb.cursors.DictCursor): + self.logger.debug(cmd) + self.cur.execute(cmd) + rows = self.cur.fetchall() + return rows + + @retry + def get_table_by_uuid_name(self, table, uuid_name, error_item_text=None, allow_several=False, WHERE_OR={}, WHERE_AND_OR="OR", attempt=_ATTEMPT): ''' Obtain One row from a table based on name or uuid. Attribute: table: string of table name uuid_name: name or uuid. If not uuid format is found, it is considered a name - allow_severeral: if False return ERROR if more than one row are founded - error_item_text: in case of error it identifies the 'item' name for a proper output text + allow_several: if False return ERROR if more than one row are found + error_item_text: in case of error it identifies the 'item' name for a proper output text 'WHERE_OR': dict of key:values, translated to key=value OR ... (Optional) - 'WHERE_AND_OR: str 'AND' or 'OR'(by default) mark the priority to 'WHERE AND (WHERE_OR)' or (WHERE) OR WHERE_OR' (Optional + 'WHERE_AND_OR: str 'AND' or 'OR'(by default) mark the priority to 'WHERE AND (WHERE_OR)' or (WHERE) OR WHERE_OR' (Optional Return: if allow_several==False, a dictionary with this row, or error if no item is found or more than one is found if allow_several==True, a list of dictionaries with the row or rows, error if no item is found ''' @@ -606,67 +769,48 @@ class db_base(): if error_item_text==None: error_item_text = table what = 'uuid' if af.check_valid_uuid(uuid_name) else 'name' - cmd = " SELECT * FROM {} WHERE {}='{}'".format(table, what, uuid_name) + cmd = " SELECT * FROM {} WHERE {}='{}'".format(table, what, uuid_name) if WHERE_OR: - where_or = " OR ".join(map(self.__tuple2db_format_where, WHERE_OR.iteritems() )) + where_or = self.__create_where(WHERE_OR, use_or=True) if WHERE_AND_OR == "AND": cmd += " AND (" + where_or + ")" else: cmd += " OR " + where_or - - tries = 2 - while tries: - try: - with self.con: - self.cur = self.con.cursor(mdb.cursors.DictCursor) - self.logger.debug(cmd) - self.cur.execute(cmd) - number = self.cur.rowcount - if number==0: - return -HTTP_Not_Found, "No %s found with %s '%s'" %(error_item_text, what, uuid_name) - elif number>1 and not allow_serveral: - return -HTTP_Bad_Request, "More than one %s found with %s '%s'" %(error_item_text, what, uuid_name) - if allow_serveral: - rows = self.cur.fetchall() - else: - rows = self.cur.fetchone() - return rows - except (mdb.Error, AttributeError) as e: - self._format_error(e, tries) - tries -= 1 + attempt.info['cmd'] = cmd + + with self.transaction(mdb.cursors.DictCursor): + self.logger.debug(cmd) + self.cur.execute(cmd) + number = self.cur.rowcount + if number == 0: + raise db_base_Exception("No {} found with {} '{}'".format(error_item_text, what, uuid_name), http_code=httperrors.Not_Found) + elif number > 1 and not allow_several: + raise db_base_Exception("More than one {} found with {} '{}'".format(error_item_text, what, uuid_name), http_code=httperrors.Conflict) + if allow_several: + rows = self.cur.fetchall() + else: + rows = self.cur.fetchone() + return rows + @retry(table='uuids') + @with_transaction(cursor='dict') def get_uuid(self, uuid): '''check in the database if this uuid is already present''' - for retry_ in range(0,2): - try: - with self.con: - self.cur = self.con.cursor(mdb.cursors.DictCursor) - self.cur.execute("SELECT * FROM uuids where uuid='" + str(uuid) + "'") - rows = self.cur.fetchall() - return self.cur.rowcount, rows - except (mdb.Error, AttributeError) as e: - print "nfvo_db.get_uuid DB Exception %d: %s" % (e.args[0], e.args[1]) - r,c = self._format_error(e) - if r!=-HTTP_Request_Timeout or retry_==1: return r,c + self.cur.execute("SELECT * FROM uuids where uuid='" + str(uuid) + "'") + rows = self.cur.fetchall() + return self.cur.rowcount, rows + @retry + @with_transaction(cursor='dict') def get_uuid_from_name(self, table, name): '''Searchs in table the name and returns the uuid - ''' - tries = 2 - while tries: - try: - with self.con: - self.cur = self.con.cursor(mdb.cursors.DictCursor) - where_text = "name='" + name +"'" - self.cur.execute("SELECT * FROM " + table + " WHERE "+ where_text) - rows = self.cur.fetchall() - if self.cur.rowcount==0: - return 0, "Name %s not found in table %s" %(name, table) - elif self.cur.rowcount>1: - return self.cur.rowcount, "More than one VNF with name %s found in table %s" %(name, table) - return self.cur.rowcount, rows[0]["uuid"] - except (mdb.Error, AttributeError) as e: - self._format_error(e, tries) - tries -= 1 - + ''' + where_text = "name='" + name +"'" + self.cur.execute("SELECT * FROM " + table + " WHERE "+ where_text) + rows = self.cur.fetchall() + if self.cur.rowcount==0: + return 0, "Name %s not found in table %s" %(name, table) + elif self.cur.rowcount>1: + return self.cur.rowcount, "More than one VNF with name %s found in table %s" %(name, table) + return self.cur.rowcount, rows[0]["uuid"]