# -*- 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
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):
        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"
        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.warn("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.warn("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, 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
        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, 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 __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:
            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.iteritems() ))
        if modified_time:
            values += ",modified_at={:f}".format(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.iteritems() ))
        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 %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"]