- 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 is not None: self.host = host
- if user is not None: self.user = user
- if passwd is not None: self.passwd = passwd
- if database is not None: self.database = database
-
- self.con = mdb.connect(self.host, self.user, self.passwd, self.database)
- print "DB: connected to %s@%s -> %s" % (self.user, self.host, self.database)
- return 0
- except mdb.Error, e:
- print "nfvo_db.connect Error connecting to DB %s@%s -> %s Error %d: %s" % (self.user, self.host, self.database, e.args[0], e.args[1])
- return -1
-
- 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,openmano_ver FROM schema_version"
- for retry_ in range(0,2):
- try:
- with self.con:
- self.cur = self.con.cursor()
- #print cmd
- self.cur.execute(cmd)
- rows = self.cur.fetchall()
- highest_version_int=0
- highest_version=""
- #print rows
- 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), e:
- #print cmd
- print "get_db_version 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
-
- def disconnect(self):
- '''disconnect from specific data base'''
- try:
- self.con.close()
- del self.con
- except mdb.Error, e:
- print "Error disconnecting from DB: Error %d: %s" % (e.args[0], e.args[1])
- return -1
- except AttributeError, e: #self.con not defined
- if e[0][-5:] == "'con'": return -1, "Database internal error, no connection."
- else: raise
-
- def format_error(self, e, command=None, extra=None):
- if type(e[0]) is str:
- if e[0][-5:] == "'con'": return -HTTP_Internal_Server_Error, "DB Exception, no connection."
- else: raise
- 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
- #reconnect
- self.connect()
- return -HTTP_Request_Timeout,"Database reconnection. Try Again"
-
- fk=e.args[1].find("foreign key constraint fails")
- if fk>=0:
- if command=="update": return -HTTP_Bad_Request, "tenant_id %s not found." % extra
- elif command=="delete": return -HTTP_Bad_Request, "Resource is not free. There are %s that prevent deleting it." % extra
- 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
- return -HTTP_Conflict, "Value %s already in use for %s" % (e.args[1][de+15:fk], e.args[1][fk+7:])
- if uk>=0:
- if wc>=0:
- return -HTTP_Bad_Request, "Field %s can not be used for filtering" % e.args[1][uk+14:wc]
- if fl>=0:
- return -HTTP_Bad_Request, "Field %s does not exist" % e.args[1][uk+14:wc]
- return -HTTP_Internal_Server_Error, "Database internal Error %d: %s" % (e.args[0], e.args[1])
-
- 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==None:
- return 'Null'
- out=str(data)
- if "'" not in out:
- return "'" + out + "'"
- elif '"' not in out:
- return '"' + out + '"'
- else:
- return json.dumps(out)
-
- 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.
- '''
- if data[1]==None:
- return str(data[0]) + "=Null"
- out=str(data[1])
- if "'" not in out:
- return str(data[0]) + "='" + out + "'"
- elif '"' not in out:
- return str(data[0]) + '="' + out + '"'
- else:
- return str(data[0]) + '=' + json.dumps(out)
-
- 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"
-
-# if type(data[1]) is tuple: #this can only happen in a WHERE_OR clause
-# text =[]
-# for d in data[1]:
-# if d==None:
-# text.append(str(data[0]) + " is Null")
-# continue
-# out=str(d)
-# if "'" not in out:
-# text.append( str(data[0]) + "='" + out + "'" )
-# elif '"' not in out:
-# text.append( str(data[0]) + '="' + out + '"' )
-# else:
-# text.append( str(data[0]) + '=' + json.dumps(out) )
-# return " OR ".join(text)
-
- out=str(data[1])
- if "'" not in out:
- return str(data[0]) + "='" + out + "'"
- elif '"' not in out:
- return str(data[0]) + '="' + out + '"'
- else:
- return str(data[0]) + '=' + json.dumps(out)
-
- 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"
- out=str(data[1])
- if "'" not in out:
- return str(data[0]) + "<>'" + out + "'"
- elif '"' not in out:
- return str(data[0]) + '<>"' + out + '"'
- else:
- return str(data[0]) + '<>' + json.dumps(out)
-
- 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, log=False, 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, negative if error
- '''
- #gettting uuid
- uuid = WHERE['uuid'] if 'uuid' in WHERE else None
- values = ",".join(map(self.__tuple2db_format_set, UPDATE.iteritems() ))
- if modified_time:
- values += ",modified_at=%f" % modified_time
- cmd= "UPDATE " + table +" SET " + values +\
- " WHERE " + " and ".join(map(self.__tuple2db_format_where, WHERE.iteritems() ))
- print cmd
- self.cur.execute(cmd)
- nb_rows = self.cur.rowcount
- if nb_rows > 0 and log:
- #inserting new log
- if uuid is None: uuid_k = uuid_v = ""
- else: uuid_k=",uuid"; uuid_v=",'" + str(uuid) + "'"
- cmd = "INSERT INTO logs (related,level%s,description) VALUES ('%s','debug'%s,\"updating %d entry %s\")" \
- % (uuid_k, table, uuid_v, nb_rows, (str(UPDATE)).replace('"','-') )
- print cmd
- self.cur.execute(cmd)
- return nb_rows, "%d updated from %s" % (nb_rows, table[:-1] )
-
- def _new_row_internal(self, table, INSERT, tenant_id=None, add_uuid=False, root_uuid=None, log=False, created_time=0):
- ''' 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
- 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
- '''
-
- 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)" % (uuid, root_uuid, table, created_at)
- print 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=%f" % created_time
- print cmd
- self.cur.execute(cmd)
- nb_rows = self.cur.rowcount
- #inserting new log
- if nb_rows > 0 and log:
- if add_uuid: del INSERT['uuid']
- if uuid is None: uuid_k = uuid_v = ""
- else: uuid_k=",uuid"; uuid_v=",'" + str(uuid) + "'"
- if tenant_id is None: tenant_k = tenant_v = ""
- else: tenant_k=",nfvo_tenant_id"; tenant_v=",'" + str(tenant_id) + "'"
- cmd = "INSERT INTO logs (related,level%s%s,description) VALUES ('%s','debug'%s%s,\"new %s %s\")" \
- % (uuid_k, tenant_k, table, uuid_v, tenant_v, table[:-1], str(INSERT).replace('"','-'))
- print cmd
- self.cur.execute(cmd)
- return nb_rows, uuid
-
- def __get_rows(self,table,uuid):
- self.cur.execute("SELECT * FROM " + str(table) +" where uuid='" + str(uuid) + "'")
- rows = self.cur.fetchall()
- return self.cur.rowcount, rows
-
- def new_row(self, table, INSERT, tenant_id=None, add_uuid=False, log=False, created_time=0):
- ''' 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: (result, uuid) where result can be 0 if error, or 1 if ok
- '''
- if table in tables_with_created_field and created_time==0:
- created_time=time.time()
- for retry_ in range(0,2):
- try:
- with self.con:
- self.cur = self.con.cursor()
- return self._new_row_internal(table, INSERT, tenant_id, add_uuid, None, log, created_time)
-
- except (mdb.Error, AttributeError), e:
- print "nfvo_db.new_row 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
-
- def update_rows(self, table, UPDATE, WHERE, log=False, 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 tables_with_created_field and modified_time==0:
- modified_time=time.time()
- for retry_ in range(0,2):
- try:
- with self.con:
- self.cur = self.con.cursor()
- return self.__update_rows(table, UPDATE, WHERE, log)
-
- except (mdb.Error, AttributeError), e:
- print "nfvo_db.update_rows 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
-
- def delete_row(self, table, uuid, tenant_id=None, log=True):
- for retry_ in range(0,2):
- try:
- with self.con:
- #delete host
- self.cur = self.con.cursor()
- cmd = "DELETE FROM %s WHERE uuid = '%s'" % (table, uuid)
- print cmd
- self.cur.execute(cmd)
- deleted = self.cur.rowcount
- if deleted == 1:
- #delete uuid
- if table == 'tenants': tenant_str=uuid
- elif tenant_id:
- tenant_str = tenant_id
- else:
- tenant_str = 'Null'
- self.cur = self.con.cursor()
- cmd = "DELETE FROM uuids WHERE root_uuid = '%s'" % uuid
- print cmd
- self.cur.execute(cmd)
- #inserting new log
- if log:
- cmd = "INSERT INTO logs (related,level,uuid,nfvo_tenant_id,description) VALUES ('%s','debug','%s','%s','delete %s')" % (table, uuid, tenant_str, table[:-1])
- print cmd
- self.cur.execute(cmd)
- return deleted, table[:-1] + " '%s' %s" %(uuid, "deleted" if deleted==1 else "not found")
- except (mdb.Error, AttributeError), e:
- print "nfvo_db.delete_row DB Exception %d: %s" % (e.args[0], e.args[1])
- r,c = self.format_error(e, "delete", 'instances' if table=='hosts' or table=='tenants' else 'dependencies')
- if r!=-HTTP_Request_Timeout or retry_==1: return r,c
-
- def delete_row_by_dict(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)
- 'LIMIT': limit of number of rows (Optional)
- Return: the (number of items deleted, descriptive test) if ok; (negative, descriptive text) 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_) )
- print cmd
- for retry_ in range(0,2):
- try:
- with self.con:
- #delete host
- self.cur = self.con.cursor()
- self.cur.execute(cmd)
- deleted = self.cur.rowcount
- return deleted, "%d deleted from %s" % (deleted, sql_dict['FROM'][:-1] )
- except (mdb.Error, AttributeError), e:
- print "nfvo_db.delete_row DB Exception %d: %s" % (e.args[0], e.args[1])
- r,c = self.format_error(e, "delete", 'dependencies')
- if r!=-HTTP_Request_Timeout or retry_==1: return r,c
-
- def get_rows(self,table,uuid):
- '''get row from a table based on uuid'''
- for retry_ in range(0,2):
- try:
- with self.con:
- self.cur = self.con.cursor(mdb.cursors.DictCursor)
- self.cur.execute("SELECT * FROM " + str(table) +" where uuid='" + str(uuid) + "'")
- rows = self.cur.fetchall()
- return self.cur.rowcount, rows
- except (mdb.Error, AttributeError), e:
- print "nfvo_db.get_rows 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
-
- def get_table(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 + ")"
- 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
- 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_) )
- for retry_ in range(0,2):
- try:
- with self.con:
- self.cur = self.con.cursor(mdb.cursors.DictCursor)
- print cmd
- self.cur.execute(cmd)
- rows = self.cur.fetchall()
- return self.cur.rowcount, rows
- except (mdb.Error, AttributeError), e:
- print "nfvo_db.get_table 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
-
- def get_table_by_uuid_name(self, table, uuid_name, error_item_text=None, allow_serveral=False, WHERE_OR={}, WHERE_AND_OR="OR"):
- ''' 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
- '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 %s WHERE %s='%s'" % (table, what, uuid_name)
- if WHERE_OR:
- where_or = " OR ".join(map(self.__tuple2db_format_where, WHERE_OR.iteritems() ))
- if WHERE_AND_OR == "AND":
- cmd += " AND (" + where_or + ")"
- else:
- cmd += " OR " + where_or
-
-
- for retry_ in range(0,2):
- try:
- with self.con:
- self.cur = self.con.cursor(mdb.cursors.DictCursor)
- print 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 number, rows
- except (mdb.Error, AttributeError), e:
- print "nfvo_db.get_table_by_uuid_name 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
-
- 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), 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
-