1 # -*- coding: utf-8 -*-
4 # Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
5 # This file is part of openmano
8 # Licensed under the Apache License, Version 2.0 (the "License"); you may
9 # not use this file except in compliance with the License. You may obtain
10 # a copy of the License at
12 # http://www.apache.org/licenses/LICENSE-2.0
14 # Unless required by applicable law or agreed to in writing, software
15 # distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
16 # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
17 # License for the specific language governing permissions and limitations
20 # For those usages not covered by the Apache License, Version 2.0 please
21 # contact with: nfvlabs@tid.es
25 Base class for openmano database manipulation
27 __author__
="Alfonso Tierno"
28 __date__
="$4-Apr-2016 10:05:01$"
38 from jsonschema
import validate
as js_v
, exceptions
as js_e
40 HTTP_Bad_Request
= 400
41 HTTP_Unauthorized
= 401
43 HTTP_Method_Not_Allowed
= 405
44 HTTP_Request_Timeout
= 408
46 HTTP_Service_Unavailable
= 503
47 HTTP_Internal_Server_Error
= 500
49 def _check_valid_uuid(uuid
):
50 id_schema
= {"type" : "string", "pattern": "^[a-fA-F0-9]{8}(-[a-fA-F0-9]{4}){3}-[a-fA-F0-9]{12}$"}
51 id_schema2
= {"type" : "string", "pattern": "^[a-fA-F0-9]{32}$"}
55 except js_e
.ValidationError
:
57 js_v(uuid
, id_schema2
)
59 except js_e
.ValidationError
:
63 def _convert_datetime2str(var
):
64 '''Converts a datetime variable to a string with the format '%Y-%m-%dT%H:%i:%s'
65 It enters recursively in the dict var finding this kind of variables
68 for k
,v
in var
.items():
69 if type(v
) is datetime
.datetime
:
70 var
[k
]= v
.strftime('%Y-%m-%dT%H:%M:%S')
71 elif type(v
) is dict or type(v
) is list or type(v
) is tuple:
72 _convert_datetime2str(v
)
73 if len(var
) == 0: return True
74 elif type(var
) is list or type(var
) is tuple:
76 _convert_datetime2str(v
)
78 def _convert_bandwidth(data
, reverse
=False, logger
=None):
79 '''Check the field bandwidth recursivelly and when found, it removes units and convert to number
80 It assumes that bandwidth is well formed
82 'data': dictionary bottle.FormsDict variable to be checked. None or empty is consideted valid
83 'reverse': by default convert form str to int (Mbps), if True it convert from number to units
87 if type(data
) is dict:
89 if type(data
[k
]) is dict or type(data
[k
]) is tuple or type(data
[k
]) is list:
90 _convert_bandwidth(data
[k
], reverse
, logger
)
91 if "bandwidth" in data
:
93 value
=str(data
["bandwidth"])
95 pos
= value
.find("bps")
97 if value
[pos
-1]=="G": data
["bandwidth"] = int(data
["bandwidth"][:pos
-1]) * 1000
98 elif value
[pos
-1]=="k": data
["bandwidth"]= int(data
["bandwidth"][:pos
-1]) / 1000
99 else: data
["bandwidth"]= int(data
["bandwidth"][:pos
-1])
101 value
= int(data
["bandwidth"])
102 if value
% 1000 == 0: data
["bandwidth"]=str(value
/1000) + " Gbps"
103 else: data
["bandwidth"]=str(value
) + " Mbps"
106 logger
.error("convert_bandwidth exception for type '%s' data '%s'", type(data
["bandwidth"]), data
["bandwidth"])
108 if type(data
) is tuple or type(data
) is list:
110 if type(k
) is dict or type(k
) is tuple or type(k
) is list:
111 _convert_bandwidth(k
, reverse
, logger
)
113 def _convert_str2boolean(data
, items
):
114 '''Check recursively the content of data, and if there is an key contained in items, convert value from string to boolean
117 'data': dictionary variable to be checked. None or empty is considered valid
118 'items': tuple of keys to convert
122 if type(data
) is dict:
123 for k
in data
.keys():
124 if type(data
[k
]) is dict or type(data
[k
]) is tuple or type(data
[k
]) is list:
125 _convert_str2boolean(data
[k
], items
)
127 if type(data
[k
]) is str:
128 if data
[k
]=="false" or data
[k
]=="False" or data
[k
]=="0": data
[k
]=False
129 elif data
[k
]=="true" or data
[k
]=="True" or data
[k
]=="1": data
[k
]=True
130 elif type(data
[k
]) is int:
131 if data
[k
]==0: data
[k
]=False
132 elif data
[k
]==1: data
[k
]=True
133 if type(data
) is tuple or type(data
) is list:
135 if type(k
) is dict or type(k
) is tuple or type(k
) is list:
136 _convert_str2boolean(k
, items
)
138 class db_base_Exception(Exception):
139 '''Common Exception for all database exceptions'''
141 def __init__(self
, message
, http_code
=HTTP_Bad_Request
):
142 Exception.__init
__(self
, message
)
143 self
.http_code
= http_code
146 tables_with_created_field
=()
148 def __init__(self
, host
=None, user
=None, passwd
=None, database
=None, log_name
='db', log_level
=None):
152 self
.database
= database
154 self
.log_level
=log_level
155 self
.logger
= logging
.getLogger(log_name
)
157 self
.logger
.setLevel( getattr(logging
, log_level
) )
159 def connect(self
, host
=None, user
=None, passwd
=None, database
=None):
160 '''Connect to specific data base.
161 The first time a valid host, user, passwd and database must be provided,
162 Following calls can skip this parameters
165 if host
: self
.host
= host
166 if user
: self
.user
= user
167 if passwd
: self
.passwd
= passwd
168 if database
: self
.database
= database
170 self
.con
= mdb
.connect(self
.host
, self
.user
, self
.passwd
, self
.database
)
171 self
.logger
.debug("DB: connected to '%s' at '%s@%s'", self
.database
, self
.user
, self
.host
)
172 except mdb
.Error
as e
:
173 raise db_base_Exception("Cannot connect to DataBase '{}' at '{}@{}' Error {}: {}".format(
174 self
.database
, self
.user
, self
.host
, e
.args
[0], e
.args
[1]),
175 http_code
= HTTP_Unauthorized
)
177 def get_db_version(self
):
178 ''' Obtain the database schema version.
179 Return: (negative, text) if error or version 0.0 where schema_version table is missing
180 (version_int, version_text) if ok
182 cmd
= "SELECT version_int,version FROM schema_version"
187 self
.cur
= self
.con
.cursor()
188 self
.logger
.debug(cmd
)
189 self
.cur
.execute(cmd
)
190 rows
= self
.cur
.fetchall()
191 highest_version_int
=0
193 for row
in rows
: #look for the latest version
194 if row
[0]>highest_version_int
:
195 highest_version_int
, highest_version
= row
[0:2]
196 return highest_version_int
, highest_version
197 except (mdb
.Error
, AttributeError) as e
:
198 self
.logger
.error("Exception '{}' with command '{}'".format(e
, cmd
))
199 #self.logger.error("get_db_version DB Exception %d: %s. Command %s",e.args[0], e.args[1], cmd)
200 self
._format
_error
(e
, tries
)
203 def disconnect(self
):
204 '''disconnect from specific data base'''
208 except mdb
.Error
as e
:
209 self
.logger
.error("while disconnecting from DB: Error %d: %s",e
.args
[0], e
.args
[1])
211 except AttributeError as e
: #self.con not defined
212 if e
[0][-5:] == "'con'":
213 self
.logger
.warn("while disconnecting from DB: Error %d: %s",e
.args
[0], e
.args
[1])
218 def _format_error(self
, e
, tries
=1, command
=None, extra
=None):
219 '''Creates a text error base on the produced exception
222 retry: in case of timeout, if reconnecting to database and retry, or raise and exception
223 cmd: database command that produce the exception
224 command: if the intention is update or delete
225 extra: extra information to add to some commands
227 HTTP error in negative, formatted error text
229 if isinstance(e
,AttributeError ):
230 raise db_base_Exception("DB Exception " + str(e
), HTTP_Internal_Server_Error
)
231 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
233 self
.logger
.warn("DB Exception '%s'. Retry", str(e
))
238 raise db_base_Exception("Database connection timeout Try Again", HTTP_Request_Timeout
)
240 fk
=e
.args
[1].find("foreign key constraint fails")
242 if command
=="update":
243 raise db_base_Exception("tenant_id '{}' not found.".format(extra
), HTTP_Not_Found
)
244 elif command
=="delete":
245 raise db_base_Exception("Resource is not free. There are {} that prevent deleting it.".format(extra
), HTTP_Conflict
)
246 de
= e
.args
[1].find("Duplicate entry")
247 fk
= e
.args
[1].find("for key")
248 uk
= e
.args
[1].find("Unknown column")
249 wc
= e
.args
[1].find("in 'where clause'")
250 fl
= e
.args
[1].find("in 'field list'")
251 #print de, fk, uk, wc,fl
253 if fk
>=0: #error 1062
254 raise db_base_Exception("Value {} already in use for {}".format(e
.args
[1][de
+15:fk
], e
.args
[1][fk
+7:]), HTTP_Conflict
)
257 raise db_base_Exception("Field {} can not be used for filtering".format(e
.args
[1][uk
+14:wc
]), HTTP_Bad_Request
)
259 raise db_base_Exception("Field {} does not exist".format(e
.args
[1][uk
+14:wc
]), HTTP_Bad_Request
)
260 raise db_base_Exception("Database internal Error {}: {}".format(e
.args
[0], e
.args
[1]), HTTP_Internal_Server_Error
)
262 def __str2db_format(self
, data
):
263 '''Convert string data to database format.
264 If data is None it returns the 'Null' text,
265 otherwise it returns the text surrounded by quotes ensuring internal quotes are escaped.
269 elif isinstance(data
[1], str):
270 return json
.dumps(data
)
272 return json
.dumps(str(data
))
274 def __tuple2db_format_set(self
, data
):
275 """Compose the needed text for a SQL SET, parameter 'data' is a pair tuple (A,B),
276 and it returns the text 'A="B"', where A is a field of a table and B is the value
277 If B is None it returns the 'A=Null' text, without surrounding Null by quotes
278 If B is not None it returns the text "A='B'" or 'A="B"' where B is surrounded by quotes,
279 and it ensures internal quotes of B are escaped.
280 B can be also a dict with special keys:
281 {"INCREMENT": NUMBER}, then it produce "A=A+NUMBER"
284 return str(data
[0]) + "=Null"
285 elif isinstance(data
[1], str):
286 return str(data
[0]) + '=' + json
.dumps(data
[1])
287 elif isinstance(data
[1], dict):
288 if "INCREMENT" in data
[1]:
289 return "{A}={A}{N:+d}".format(A
=data
[0], N
=data
[1]["INCREMENT"])
290 raise db_base_Exception("Format error for UPDATE field")
292 return str(data
[0]) + '=' + json
.dumps(str(data
[1]))
294 def __create_where(self
, data
, use_or
=None):
296 Compose the needed text for a SQL WHERE, parameter 'data' can be a dict or a list of dict. By default lists are
297 concatenated with OR and dict with AND, unless parameter 'use_or' indicates other thing.
298 If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
299 If value is None, it will produce 'key is null'
300 If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
301 keys can be suffixed by >,<,<>,>=,<=,' LIKE ' so that this is used to compare key and value instead of "="
302 The special keys "OR", "AND" with a dict value is used to create a nested WHERE
303 If a list, each item will be a dictionary that will be concatenated with OR by default
304 :param data: dict or list of dicts
305 :param use_or: Can be None (use default behaviour), True (use OR) or False (use AND)
306 :return: a string with the content to send to mysql
309 if isinstance(data
, dict):
310 for k
, v
in data
.items():
312 cmd
.append("(" + self
.__create
_where
(v
, use_or
=True) + ")")
315 cmd
.append("(" + self
.__create
_where
(v
, use_or
=False) + ")")
318 if k
.endswith(">") or k
.endswith("<") or k
.endswith("=") or k
.endswith(" LIKE "):
324 cmd
.append(k
.replace("=", " is").replace("<>", " is not") + " Null")
325 elif isinstance(v
, (tuple, list)):
329 cmd2
.append(k
.replace("=", " is").replace("<>", " is not") + " Null")
331 cmd2
.append(k
+ json
.dumps(str(v2
)))
332 cmd
.append("(" + " OR ".join(cmd2
) + ")")
334 cmd
.append(k
+ json
.dumps(str(v
)))
335 elif isinstance(data
, (tuple, list)):
339 cmd
.append("(" + self
.__create
_where
(k
) + ")")
341 raise db_base_Exception("invalid WHERE clause at '{}'".format(data
))
343 return " OR ".join(cmd
)
344 return " AND ".join(cmd
)
346 def __remove_quotes(self
, data
):
347 '''remove single quotes ' of any string content of data dictionary'''
348 for k
,v
in data
.items():
351 data
[k
] = data
[k
].replace("'","_")
353 def _update_rows(self
, table
, UPDATE
, WHERE
, modified_time
=0):
354 """ Update one or several rows of a table.
355 :param UPDATE: dictionary with the changes. dict keys are database columns that will be set with the dict values
356 :param table: database table to update
357 :param WHERE: dict or list of dicts to compose the SQL WHERE clause.
358 If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
359 If value is None, it will produce 'key is null'
360 If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
361 keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "="
362 The special keys "OR", "AND" with a dict value is used to create a nested WHERE
363 If a list, each item will be a dictionary that will be concatenated with OR
364 :return: the number of updated rows, raises exception upon error
367 values
= ",".join(map(self
.__tuple
2db
_format
_set
, UPDATE
.iteritems() ))
369 values
+= ",modified_at={:f}".format(modified_time
)
370 cmd
= "UPDATE " + table
+ " SET " + values
+ " WHERE " + self
.__create
_where
(WHERE
)
371 self
.logger
.debug(cmd
)
372 self
.cur
.execute(cmd
)
373 return self
.cur
.rowcount
375 def _new_uuid(self
, root_uuid
=None, used_table
=None, created_time
=0):
377 Generate a new uuid. It DOES NOT begin or end the transaction, so self.con.cursor must be created
378 :param root_uuid: master uuid of the transaction
379 :param used_table: the table this uuid is intended for
380 :param created_time: time of creation
381 :return: the created uuid
384 uuid
= str(myUuid
.uuid1())
385 # defining root_uuid if not provided
386 if root_uuid
is None:
389 created_at
= created_time
391 created_at
= time
.time()
393 cmd
= "INSERT INTO uuids (uuid, root_uuid, used_at, created_at) VALUES ('{:s}','{:s}','{:s}', {:f})".format(
394 uuid
, root_uuid
, used_table
, created_at
)
395 self
.logger
.debug(cmd
)
396 self
.cur
.execute(cmd
)
399 def _new_row_internal(self
, table
, INSERT
, add_uuid
=False, root_uuid
=None, created_time
=0, confidential_data
=False):
400 ''' Add one row into a table. It DOES NOT begin or end the transaction, so self.con.cursor must be created
402 INSERT: dictionary with the key:value to insert
403 table: table where to insert
404 add_uuid: if True, it will create an uuid key entry at INSERT if not provided
405 created_time: time to add to the created_time column
406 It checks presence of uuid and add one automatically otherwise
411 #create uuid if not provided
412 if 'uuid' not in INSERT
:
413 uuid
= INSERT
['uuid'] = str(myUuid
.uuid1()) # create_uuid
415 uuid
= str(INSERT
['uuid'])
419 #defining root_uuid if not provided
420 if root_uuid
is None:
423 created_at
= created_time
425 created_at
=time
.time()
427 cmd
= "INSERT INTO uuids (uuid, root_uuid, used_at, created_at) VALUES ('{:s}','{:s}','{:s}', {:f})".format(uuid
, root_uuid
, table
, created_at
)
428 self
.logger
.debug(cmd
)
429 self
.cur
.execute(cmd
)
431 cmd
= "INSERT INTO " + table
+" SET " + \
432 ",".join(map(self
.__tuple
2db
_format
_set
, INSERT
.iteritems() ))
434 cmd
+= ",created_at=%f" % created_time
435 if confidential_data
:
436 index
= cmd
.find("SET")
437 subcmd
= cmd
[:index
] + 'SET...'
438 self
.logger
.debug(subcmd
)
440 self
.logger
.debug(cmd
)
441 self
.cur
.execute(cmd
)
445 def _get_rows(self
,table
,uuid
):
446 cmd
= "SELECT * FROM {} WHERE uuid='{}'".format(str(table
), str(uuid
))
447 self
.logger
.debug(cmd
)
448 self
.cur
.execute(cmd
)
449 rows
= self
.cur
.fetchall()
452 def new_row(self
, table
, INSERT
, add_uuid
=False, created_time
=0, confidential_data
=False):
453 ''' Add one row into a table.
455 INSERT: dictionary with the key: value to insert
456 table: table where to insert
457 tenant_id: only useful for logs. If provided, logs will use this tenant_id
458 add_uuid: if True, it will create an uuid key entry at INSERT if not provided
459 It checks presence of uuid and add one automatically otherwise
460 Return: (result, uuid) where result can be 0 if error, or 1 if ok
462 if table
in self
.tables_with_created_field
and created_time
==0:
463 created_time
=time
.time()
468 self
.cur
= self
.con
.cursor()
469 return self
._new
_row
_internal
(table
, INSERT
, add_uuid
, None, created_time
, confidential_data
)
471 except (mdb
.Error
, AttributeError) as e
:
472 self
._format
_error
(e
, tries
)
475 def update_rows(self
, table
, UPDATE
, WHERE
, modified_time
=0):
476 """ Update one or several rows of a table.
477 :param UPDATE: dictionary with the changes. dict keys are database columns that will be set with the dict values
478 :param table: database table to update
479 :param WHERE: dict or list of dicts to compose the SQL WHERE clause.
480 If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
481 If value is None, it will produce 'key is null'
482 If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
483 keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "="
484 The special keys "OR", "AND" with a dict value is used to create a nested WHERE
485 If a list, each item will be a dictionary that will be concatenated with OR
486 :param modified_time: Can contain the time to be set to the table row
487 :return: the number of updated rows, raises exception upon error
489 if table
in self
.tables_with_created_field
and modified_time
==0:
490 modified_time
=time
.time()
495 self
.cur
= self
.con
.cursor()
496 return self
._update
_rows
(table
, UPDATE
, WHERE
)
498 except (mdb
.Error
, AttributeError) as e
:
499 self
._format
_error
(e
, tries
)
502 def _delete_row_by_id_internal(self
, table
, uuid
):
503 cmd
= "DELETE FROM {} WHERE uuid = '{}'".format(table
, uuid
)
504 self
.logger
.debug(cmd
)
505 self
.cur
.execute(cmd
)
506 deleted
= self
.cur
.rowcount
508 self
.cur
= self
.con
.cursor()
509 cmd
= "DELETE FROM uuids WHERE root_uuid = '{}'".format(uuid
)
510 self
.logger
.debug(cmd
)
511 self
.cur
.execute(cmd
)
514 def delete_row_by_id(self
, table
, uuid
):
519 self
.cur
= self
.con
.cursor()
520 return self
._delete
_row
_by
_id
_internal
(table
, uuid
)
521 except (mdb
.Error
, AttributeError) as e
:
522 self
._format
_error
(e
, tries
, "delete", "dependencies")
525 def delete_row(self
, **sql_dict
):
526 """ Deletes rows from a table.
527 :param UPDATE: dictionary with the changes. dict keys are database columns that will be set with the dict values
528 :param FROM: string with table name (Mandatory)
529 :param WHERE: dict or list of dicts to compose the SQL WHERE clause. (Optional)
530 If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
531 If value is None, it will produce 'key is null'
532 If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
533 keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "="
534 The special keys "OR", "AND" with a dict value is used to create a nested WHERE
535 If a list, each item will be a dictionary that will be concatenated with OR
536 :return: the number of deleted rows, raises exception upon error
539 cmd
= "DELETE FROM " + str(sql_dict
['FROM'])
540 if sql_dict
.get('WHERE'):
541 cmd
+= " WHERE " + self
.__create
_where
(sql_dict
['WHERE'])
542 if sql_dict
.get('LIMIT'):
543 cmd
+= " LIMIT " + str(sql_dict
['LIMIT'])
548 self
.cur
= self
.con
.cursor()
549 self
.logger
.debug(cmd
)
550 self
.cur
.execute(cmd
)
551 deleted
= self
.cur
.rowcount
553 except (mdb
.Error
, AttributeError) as e
:
554 self
._format
_error
(e
, tries
)
557 def get_rows_by_id(self
, table
, uuid
):
558 '''get row from a table based on uuid'''
563 self
.cur
= self
.con
.cursor(mdb
.cursors
.DictCursor
)
564 cmd
="SELECT * FROM {} where uuid='{}'".format(str(table
), str(uuid
))
565 self
.logger
.debug(cmd
)
566 self
.cur
.execute(cmd
)
567 rows
= self
.cur
.fetchall()
569 except (mdb
.Error
, AttributeError) as e
:
570 self
._format
_error
(e
, tries
)
573 def get_rows(self
, **sql_dict
):
574 """ Obtain rows from a table.
575 :param SELECT: list or tuple of fields to retrieve) (by default all)
576 :param FROM: string with table name (Mandatory)
577 :param WHERE: dict or list of dicts to compose the SQL WHERE clause. (Optional)
578 If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
579 If value is None, it will produce 'key is null'
580 If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
581 keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "="
582 The special keys "OR", "AND" with a dict value is used to create a nested WHERE
583 If a list, each item will be a dictionary that will be concatenated with OR
584 :param LIMIT: limit the number of obtianied entries (Optional)
585 :param ORDER_BY: list or tuple of fields to order, add ' DESC' to each item if inverse order is required
586 :return: a list with dictionaries at each row, raises exception upon error
590 if 'SELECT' in sql_dict
:
591 if isinstance(sql_dict
['SELECT'], (tuple, list)):
592 cmd
+= ",".join(map(str, sql_dict
['SELECT']))
594 cmd
+= sql_dict
['SELECT']
598 cmd
+= " FROM " + str(sql_dict
['FROM'])
599 if sql_dict
.get('WHERE'):
600 cmd
+= " WHERE " + self
.__create
_where
(sql_dict
['WHERE'])
602 if 'ORDER_BY' in sql_dict
:
604 if isinstance(sql_dict
['ORDER_BY'], (tuple, list)):
605 cmd
+= ",".join(map(str, sql_dict
['ORDER_BY']))
607 cmd
+= str(sql_dict
['ORDER_BY'])
609 if 'LIMIT' in sql_dict
:
610 cmd
+= " LIMIT " + str(sql_dict
['LIMIT'])
616 self
.cur
= self
.con
.cursor(mdb
.cursors
.DictCursor
)
617 self
.logger
.debug(cmd
)
618 self
.cur
.execute(cmd
)
619 rows
= self
.cur
.fetchall()
621 except (mdb
.Error
, AttributeError) as e
:
622 self
.logger
.error("Exception '{}' with command '{}'".format(e
, cmd
))
623 self
._format
_error
(e
, tries
)
626 def get_table_by_uuid_name(self
, table
, uuid_name
, error_item_text
=None, allow_serveral
=False, WHERE_OR
={}, WHERE_AND_OR
="OR"):
627 ''' Obtain One row from a table based on name or uuid.
629 table: string of table name
630 uuid_name: name or uuid. If not uuid format is found, it is considered a name
631 allow_severeral: if False return ERROR if more than one row are founded
632 error_item_text: in case of error it identifies the 'item' name for a proper output text
633 'WHERE_OR': dict of key:values, translated to key=value OR ... (Optional)
634 'WHERE_AND_OR: str 'AND' or 'OR'(by default) mark the priority to 'WHERE AND (WHERE_OR)' or (WHERE) OR WHERE_OR' (Optional
635 Return: if allow_several==False, a dictionary with this row, or error if no item is found or more than one is found
636 if allow_several==True, a list of dictionaries with the row or rows, error if no item is found
639 if error_item_text
==None:
640 error_item_text
= table
641 what
= 'uuid' if af
.check_valid_uuid(uuid_name
) else 'name'
642 cmd
= " SELECT * FROM {} WHERE {}='{}'".format(table
, what
, uuid_name
)
644 where_or
= self
.__create
_where
(WHERE_OR
, use_or
=True)
645 if WHERE_AND_OR
== "AND":
646 cmd
+= " AND (" + where_or
+ ")"
648 cmd
+= " OR " + where_or
654 self
.cur
= self
.con
.cursor(mdb
.cursors
.DictCursor
)
655 self
.logger
.debug(cmd
)
656 self
.cur
.execute(cmd
)
657 number
= self
.cur
.rowcount
659 raise db_base_Exception("No {} found with {} '{}'".format(error_item_text
, what
, uuid_name
), http_code
=HTTP_Not_Found
)
660 elif number
> 1 and not allow_serveral
:
661 raise db_base_Exception("More than one {} found with {} '{}'".format(error_item_text
, what
, uuid_name
), http_code
=HTTP_Conflict
)
663 rows
= self
.cur
.fetchall()
665 rows
= self
.cur
.fetchone()
667 except (mdb
.Error
, AttributeError) as e
:
668 self
._format
_error
(e
, tries
)
671 def get_uuid(self
, uuid
):
672 '''check in the database if this uuid is already present'''
677 self
.cur
= self
.con
.cursor(mdb
.cursors
.DictCursor
)
678 self
.cur
.execute("SELECT * FROM uuids where uuid='" + str(uuid
) + "'")
679 rows
= self
.cur
.fetchall()
680 return self
.cur
.rowcount
, rows
681 except (mdb
.Error
, AttributeError) as e
:
682 self
._format
_error
(e
, tries
)
685 def get_uuid_from_name(self
, table
, name
):
686 '''Searchs in table the name and returns the uuid
692 self
.cur
= self
.con
.cursor(mdb
.cursors
.DictCursor
)
693 where_text
= "name='" + name
+"'"
694 self
.cur
.execute("SELECT * FROM " + table
+ " WHERE "+ where_text
)
695 rows
= self
.cur
.fetchall()
696 if self
.cur
.rowcount
==0:
697 return 0, "Name %s not found in table %s" %(name
, table
)
698 elif self
.cur
.rowcount
>1:
699 return self
.cur
.rowcount
, "More than one VNF with name %s found in table %s" %(name
, table
)
700 return self
.cur
.rowcount
, rows
[0]["uuid"]
701 except (mdb
.Error
, AttributeError) as e
:
702 self
._format
_error
(e
, tries
)