X-Git-Url: https://osm.etsi.org/gitweb/?a=blobdiff_plain;f=RO%2Fosm_ro%2Fdb_base.py;fp=RO%2Fosm_ro%2Fdb_base.py;h=0000000000000000000000000000000000000000;hb=e4d1a3d01fee5d5e234bd3e2d9b26ec0ae4eef44;hp=1db72e9c49fffb032ed4969795e85c1c40d6233f;hpb=ee6a62063244ab492bc4d09cc5fe89388a5d1ea6;p=osm%2FRO.git diff --git a/RO/osm_ro/db_base.py b/RO/osm_ro/db_base.py deleted file mode 100644 index 1db72e9c..00000000 --- a/RO/osm_ro/db_base.py +++ /dev/null @@ -1,815 +0,0 @@ -# -*- coding: utf-8 -*- - -## -# Copyright 2015 Telefonica Investigacion y Desarrollo, S.A.U. -# This file is part of openmano -# All Rights Reserved. -# -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. -# -# For those usages not covered by the Apache License, Version 2.0 please -# contact with: nfvlabs@tid.es -## - -''' -Base class for openmano database manipulation -''' -__author__="Alfonso Tierno" -__date__ ="$4-Apr-2016 10:05:01$" - -import MySQLdb as mdb -import uuid as myUuid -from osm_ro import utils as af -import json -#import yaml -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 - -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}$"} - id_schema2 = {"type" : "string", "pattern": "^[a-fA-F0-9]{32}$"} - try: - js_v(uuid, id_schema) - return True - except js_e.ValidationError: - try: - js_v(uuid, id_schema2) - return True - except js_e.ValidationError: - return False - return False - -def _convert_datetime2str(var): - '''Converts a datetime variable to a string with the format '%Y-%m-%dT%H:%i:%s' - It enters recursively in the dict var finding this kind of variables - ''' - if type(var) is dict: - 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: - _convert_datetime2str(v) - if len(var) == 0: return True - elif type(var) is list or type(var) is tuple: - for v in 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 - It assumes that bandwidth is well formed - Attributes: - 'data': dictionary bottle.FormsDict variable to be checked. None or empty is consideted valid - 'reverse': by default convert form str to int (Mbps), if True it convert from number to units - Return: - None - ''' - if type(data) is dict: - for k in data.keys(): - if type(data[k]) is dict or type(data[k]) is tuple or type(data[k]) is list: - _convert_bandwidth(data[k], reverse, logger) - if "bandwidth" in data: - try: - value=str(data["bandwidth"]) - if not reverse: - pos = value.find("bps") - if pos>0: - if value[pos-1]=="G": data["bandwidth"] = int(data["bandwidth"][:pos-1]) * 1000 - elif value[pos-1]=="k": data["bandwidth"]= int(data["bandwidth"][:pos-1]) // 1000 - else: data["bandwidth"]= int(data["bandwidth"][:pos-1]) - else: - value = int(data["bandwidth"]) - if value % 1000 == 0: - data["bandwidth"] = str(value // 1000) + " Gbps" - else: - data["bandwidth"] = str(value) + " Mbps" - except: - if logger: - logger.error("convert_bandwidth exception for type '%s' data '%s'", type(data["bandwidth"]), data["bandwidth"]) - return - if type(data) is tuple or type(data) is list: - for k in data: - if type(k) is dict or type(k) is tuple or type(k) is list: - _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 - Done recursively - Attributes: - 'data': dictionary variable to be checked. None or empty is considered valid - 'items': tuple of keys to convert - Return: - None - ''' - if type(data) is dict: - for k in data.keys(): - if type(data[k]) is dict or type(data[k]) is tuple or type(data[k]) is list: - _convert_str2boolean(data[k], items) - if k in items: - if type(data[k]) is str: - if data[k]=="false" or data[k]=="False" or data[k]=="0": data[k]=False - elif data[k]=="true" or data[k]=="True" or data[k]=="1": data[k]=True - elif type(data[k]) is int: - if data[k]==0: data[k]=False - elif data[k]==1: data[k]=True - if type(data) is tuple or type(data) is list: - for k in data: - if type(k) is dict or type(k) is tuple or type(k) is list: - _convert_str2boolean(k, items) - -class db_base_Exception(httperrors.HttpMappedError): - '''Common Exception for all database exceptions''' - - 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, lock=None): - self.host = host - self.user = user - self.passwd = passwd - self.database = database - self.con = None - self.log_level=log_level - 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. - The first time a valid host, user, passwd and database must be provided, - Following calls can skip this parameters - ''' - try: - if host: self.host = host - if user: self.user = user - if passwd: self.passwd = passwd - if database: self.database = database - - self.con = mdb.connect(self.host, self.user, self.passwd, self.database) - self.logger.debug("DB: connected to '%s' at '%s@%s'", self.database, self.user, self.host) - 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 = httperrors.Unauthorized ) - - def escape(self, value): - return self.con.escape(value) - - def escape_string(self, value): - 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" - 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''' - try: - self.con.close() - self.con = None - except mdb.Error as e: - self.logger.error("while disconnecting from DB: Error %d: %s",e.args[0], e.args[1]) - return - except AttributeError as e: #self.con not defined - if e[0][-5:] == "'con'": - self.logger.warning("while disconnecting from DB: Error %d: %s",e.args[0], e.args[1]) - return - else: - raise - - 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 - retry: in case of timeout, if reconnecting to database and retry, or raise and exception - cmd: database command that produce the exception - command: if the intention is update or delete - 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 ): - 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 - # Let's aways reconnect if the connection is lost - # so future calls are not affected. - self.reconnect() - - if tries > 1: - self.logger.warning("DB Exception '%s'. Retry", str(e)) - return - else: - 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), httperrors.Not_Found) - elif command=="delete": - 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") - wc = e.args[1].find("in 'where clause'") - fl = e.args[1].find("in 'field list'") - #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:], 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], table_info), - httperrors.Bad_Request) - if fl>=0: - 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. - If data is None it returns the 'Null' text, - otherwise it returns the text surrounded by quotes ensuring internal quotes are escaped. - """ - if data is None: - return 'Null' - elif isinstance(data[1], str): - 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 - 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. - 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]) - 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 __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): - cmd2.append(k + json.dumps(v2)) - else: - cmd2.append(k + json.dumps(str(v2))) - cmd.append("(" + " OR ".join(cmd2) + ")") - elif isinstance(v, str): - 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: - 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: - data[k] = data[k].replace("'","_") - - def _update_rows(self, table, UPDATE, WHERE, modified_time=0): - """ 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.items() )) - if modified_time: - values += "{}modified_at={:f}".format("," if values else "", modified_time) - cmd = "UPDATE " + table + " SET " + values + " WHERE " + self.__create_where(WHERE) - self.logger.debug(cmd) - self.cur.execute(cmd) - return self.cur.rowcount - - def _new_uuid(self, root_uuid=None, used_table=None, created_time=0): - """ - Generate a new uuid. It DOES NOT begin or end the transaction, so self.con.cursor must be created - :param root_uuid: master uuid of the transaction - :param used_table: the table this uuid is intended for - :param created_time: time of creation - :return: the created uuid - """ - - uuid = str(myUuid.uuid1()) - # defining root_uuid if not provided - if root_uuid is None: - root_uuid = uuid - if created_time: - created_at = created_time - else: - created_at = time.time() - # inserting new uuid - cmd = "INSERT INTO uuids (uuid, root_uuid, used_at, created_at) VALUES ('{:s}','{:s}','{:s}', {:f})".format( - uuid, root_uuid, used_table, created_at) - self.logger.debug(cmd) - self.cur.execute(cmd) - return uuid - - 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 - 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_at column - It checks presence of uuid and add one automatically otherwise - Return: uuid - ''' - - if add_uuid: - #create uuid if not provided - if 'uuid' not in INSERT: - uuid = INSERT['uuid'] = str(myUuid.uuid1()) # create_uuid - else: - uuid = str(INSERT['uuid']) - else: - uuid=None - if add_uuid: - #defining root_uuid if not provided - if root_uuid is None: - root_uuid = uuid - if created_time: - created_at = created_time - else: - created_at=time.time() - #inserting new uuid - cmd = "INSERT INTO uuids (uuid, root_uuid, used_at, created_at) VALUES ('{:s}','{:s}','{:s}', {:f})".format(uuid, root_uuid, table, created_at) - self.logger.debug(cmd) - self.cur.execute(cmd) - #insertion - cmd= "INSERT INTO " + table +" SET " + \ - ",".join(map(self.__tuple2db_format_set, INSERT.items() )) - if created_time: - 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 - - def _get_rows(self,table,uuid): - cmd = "SELECT * FROM {} WHERE uuid='{}'".format(str(table), str(uuid)) - self.logger.debug(cmd) - self.cur.execute(cmd) - rows = self.cur.fetchall() - return rows - - @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 - 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: uuid - ''' - if table in self.tables_with_created_field and created_time==0: - created_time=time.time() - 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): - 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''' - 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: - cmd += sql_dict['SELECT'] - else: - 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_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 - 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 - ''' - - 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) - if WHERE_OR: - where_or = self.__create_where(WHERE_OR, use_or=True) - if WHERE_AND_OR == "AND": - cmd += " AND (" + where_or + ")" - else: - cmd += " OR " + where_or - - 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''' - 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 - ''' - 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 {} not found in table {}".format(name, table) - elif self.cur.rowcount>1: - return self.cur.rowcount, "More than one VNF with name {} found in table {}".format(name, table) - return self.cur.rowcount, rows[0]["uuid"]