1 # -*- coding: utf-8 -*-
4 # Copyright 2015 Telefonica Investigacion 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 from .http_tools
import errors
as httperrors
42 def _check_valid_uuid(uuid
):
43 id_schema
= {"type" : "string", "pattern": "^[a-fA-F0-9]{8}(-[a-fA-F0-9]{4}){3}-[a-fA-F0-9]{12}$"}
44 id_schema2
= {"type" : "string", "pattern": "^[a-fA-F0-9]{32}$"}
48 except js_e
.ValidationError
:
50 js_v(uuid
, id_schema2
)
52 except js_e
.ValidationError
:
56 def _convert_datetime2str(var
):
57 '''Converts a datetime variable to a string with the format '%Y-%m-%dT%H:%i:%s'
58 It enters recursively in the dict var finding this kind of variables
61 for k
,v
in var
.items():
62 if type(v
) is datetime
.datetime
:
63 var
[k
]= v
.strftime('%Y-%m-%dT%H:%M:%S')
64 elif type(v
) is dict or type(v
) is list or type(v
) is tuple:
65 _convert_datetime2str(v
)
66 if len(var
) == 0: return True
67 elif type(var
) is list or type(var
) is tuple:
69 _convert_datetime2str(v
)
71 def _convert_bandwidth(data
, reverse
=False, logger
=None):
72 '''Check the field bandwidth recursivelly and when found, it removes units and convert to number
73 It assumes that bandwidth is well formed
75 'data': dictionary bottle.FormsDict variable to be checked. None or empty is consideted valid
76 'reverse': by default convert form str to int (Mbps), if True it convert from number to units
80 if type(data
) is dict:
82 if type(data
[k
]) is dict or type(data
[k
]) is tuple or type(data
[k
]) is list:
83 _convert_bandwidth(data
[k
], reverse
, logger
)
84 if "bandwidth" in data
:
86 value
=str(data
["bandwidth"])
88 pos
= value
.find("bps")
90 if value
[pos
-1]=="G": data
["bandwidth"] = int(data
["bandwidth"][:pos
-1]) * 1000
91 elif value
[pos
-1]=="k": data
["bandwidth"]= int(data
["bandwidth"][:pos
-1]) / 1000
92 else: data
["bandwidth"]= int(data
["bandwidth"][:pos
-1])
94 value
= int(data
["bandwidth"])
95 if value
% 1000 == 0: data
["bandwidth"]=str(value
/1000) + " Gbps"
96 else: data
["bandwidth"]=str(value
) + " Mbps"
99 logger
.error("convert_bandwidth exception for type '%s' data '%s'", type(data
["bandwidth"]), data
["bandwidth"])
101 if type(data
) is tuple or type(data
) is list:
103 if type(k
) is dict or type(k
) is tuple or type(k
) is list:
104 _convert_bandwidth(k
, reverse
, logger
)
106 def _convert_str2boolean(data
, items
):
107 '''Check recursively the content of data, and if there is an key contained in items, convert value from string to boolean
110 'data': dictionary variable to be checked. None or empty is considered valid
111 'items': tuple of keys to convert
115 if type(data
) is dict:
116 for k
in data
.keys():
117 if type(data
[k
]) is dict or type(data
[k
]) is tuple or type(data
[k
]) is list:
118 _convert_str2boolean(data
[k
], items
)
120 if type(data
[k
]) is str:
121 if data
[k
]=="false" or data
[k
]=="False" or data
[k
]=="0": data
[k
]=False
122 elif data
[k
]=="true" or data
[k
]=="True" or data
[k
]=="1": data
[k
]=True
123 elif type(data
[k
]) is int:
124 if data
[k
]==0: data
[k
]=False
125 elif data
[k
]==1: data
[k
]=True
126 if type(data
) is tuple or type(data
) is list:
128 if type(k
) is dict or type(k
) is tuple or type(k
) is list:
129 _convert_str2boolean(k
, items
)
131 class db_base_Exception(httperrors
.HttpMappedError
):
132 '''Common Exception for all database exceptions'''
134 def __init__(self
, message
, http_code
=httperrors
.Bad_Request
):
135 super(db_base_Exception
, self
).__init
__(message
, http_code
)
138 tables_with_created_field
=()
140 def __init__(self
, host
=None, user
=None, passwd
=None, database
=None, log_name
='db', log_level
=None):
144 self
.database
= database
146 self
.log_level
=log_level
147 self
.logger
= logging
.getLogger(log_name
)
149 self
.logger
.setLevel( getattr(logging
, log_level
) )
151 def connect(self
, host
=None, user
=None, passwd
=None, database
=None):
152 '''Connect to specific data base.
153 The first time a valid host, user, passwd and database must be provided,
154 Following calls can skip this parameters
157 if host
: self
.host
= host
158 if user
: self
.user
= user
159 if passwd
: self
.passwd
= passwd
160 if database
: self
.database
= database
162 self
.con
= mdb
.connect(self
.host
, self
.user
, self
.passwd
, self
.database
)
163 self
.logger
.debug("DB: connected to '%s' at '%s@%s'", self
.database
, self
.user
, self
.host
)
164 except mdb
.Error
as e
:
165 raise db_base_Exception("Cannot connect to DataBase '{}' at '{}@{}' Error {}: {}".format(
166 self
.database
, self
.user
, self
.host
, e
.args
[0], e
.args
[1]),
167 http_code
= httperrors
.Unauthorized
)
169 def escape(self
, value
):
170 return self
.con
.escape(value
)
172 def escape_string(self
, value
):
173 if isinstance(value
, unicode):
174 value
= value
.encode("utf8")
175 return self
.con
.escape_string(value
)
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, table
=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 self
.logger
.debug(str(e
), exc_info
=True)
231 raise db_base_Exception("DB Exception " + str(e
), httperrors
.Internal_Server_Error
)
232 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
234 self
.logger
.warn("DB Exception '%s'. Retry", str(e
))
239 raise db_base_Exception("Database connection timeout Try Again", httperrors
.Request_Timeout
)
241 fk
=e
.args
[1].find("foreign key constraint fails")
243 if command
=="update":
244 raise db_base_Exception("tenant_id '{}' not found.".format(extra
), httperrors
.Not_Found
)
245 elif command
=="delete":
246 raise db_base_Exception("Resource is not free. There are {} that prevent deleting it.".format(extra
), httperrors
.Conflict
)
247 de
= e
.args
[1].find("Duplicate entry")
248 fk
= e
.args
[1].find("for key")
249 uk
= e
.args
[1].find("Unknown column")
250 wc
= e
.args
[1].find("in 'where clause'")
251 fl
= e
.args
[1].find("in 'field list'")
252 #print de, fk, uk, wc,fl
253 table_info
= ' (table `{}`)'.format(table
) if table
else ''
255 if fk
>=0: #error 1062
256 raise db_base_Exception(
257 "Value {} already in use for {}{}".format(
258 e
.args
[1][de
+15:fk
], e
.args
[1][fk
+7:], table_info
),
262 raise db_base_Exception(
263 "Field {} can not be used for filtering{}".format(
264 e
.args
[1][uk
+14:wc
], table_info
),
265 httperrors
.Bad_Request
)
267 raise db_base_Exception(
268 "Field {} does not exist{}".format(
269 e
.args
[1][uk
+14:wc
], table_info
),
270 httperrors
.Bad_Request
)
271 raise db_base_Exception(
272 "Database internal Error{} {}: {}".format(
273 table_info
, e
.args
[0], e
.args
[1]),
274 httperrors
.Internal_Server_Error
)
276 def __str2db_format(self
, data
):
277 """Convert string data to database format.
278 If data is None it returns the 'Null' text,
279 otherwise it returns the text surrounded by quotes ensuring internal quotes are escaped.
283 elif isinstance(data
[1], (str, unicode)):
284 return json
.dumps(data
)
286 return json
.dumps(str(data
))
288 def __tuple2db_format_set(self
, data
):
289 """Compose the needed text for a SQL SET, parameter 'data' is a pair tuple (A,B),
290 and it returns the text 'A="B"', where A is a field of a table and B is the value
291 If B is None it returns the 'A=Null' text, without surrounding Null by quotes
292 If B is not None it returns the text "A='B'" or 'A="B"' where B is surrounded by quotes,
293 and it ensures internal quotes of B are escaped.
294 B can be also a dict with special keys:
295 {"INCREMENT": NUMBER}, then it produce "A=A+NUMBER"
298 return str(data
[0]) + "=Null"
299 elif isinstance(data
[1], (str, unicode)):
300 return str(data
[0]) + '=' + json
.dumps(data
[1])
301 elif isinstance(data
[1], dict):
302 if "INCREMENT" in data
[1]:
303 return "{A}={A}{N:+d}".format(A
=data
[0], N
=data
[1]["INCREMENT"])
304 raise db_base_Exception("Format error for UPDATE field: {!r}".format(data
[0]))
306 return str(data
[0]) + '=' + json
.dumps(str(data
[1]))
308 def __create_where(self
, data
, use_or
=None):
310 Compose the needed text for a SQL WHERE, parameter 'data' can be a dict or a list of dict. By default lists are
311 concatenated with OR and dict with AND, unless parameter 'use_or' indicates other thing.
312 If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
313 If value is None, it will produce 'key is null'
314 If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
315 keys can be suffixed by >,<,<>,>=,<=,' LIKE ' so that this is used to compare key and value instead of "="
316 The special keys "OR", "AND" with a dict value is used to create a nested WHERE
317 If a list, each item will be a dictionary that will be concatenated with OR by default
318 :param data: dict or list of dicts
319 :param use_or: Can be None (use default behaviour), True (use OR) or False (use AND)
320 :return: a string with the content to send to mysql
323 if isinstance(data
, dict):
324 for k
, v
in data
.items():
326 cmd
.append("(" + self
.__create
_where
(v
, use_or
=True) + ")")
329 cmd
.append("(" + self
.__create
_where
(v
, use_or
=False) + ")")
332 if k
.endswith(">") or k
.endswith("<") or k
.endswith("=") or k
.endswith(" LIKE "):
338 cmd
.append(k
.replace("=", " is").replace("<>", " is not") + " Null")
339 elif isinstance(v
, (tuple, list)):
343 cmd2
.append(k
.replace("=", " is").replace("<>", " is not") + " Null")
344 elif isinstance(v2
, (str, unicode)):
345 cmd2
.append(k
+ json
.dumps(v2
))
347 cmd2
.append(k
+ json
.dumps(str(v2
)))
348 cmd
.append("(" + " OR ".join(cmd2
) + ")")
349 elif isinstance(v
, (str, unicode)):
350 cmd
.append(k
+ json
.dumps(v
))
352 cmd
.append(k
+ json
.dumps(str(v
)))
353 elif isinstance(data
, (tuple, list)):
357 cmd
.append("(" + self
.__create
_where
(k
) + ")")
359 raise db_base_Exception("invalid WHERE clause at '{}'".format(data
))
361 return " OR ".join(cmd
)
362 return " AND ".join(cmd
)
364 def __remove_quotes(self
, data
):
365 '''remove single quotes ' of any string content of data dictionary'''
366 for k
,v
in data
.items():
369 data
[k
] = data
[k
].replace("'","_")
371 def _update_rows(self
, table
, UPDATE
, WHERE
, modified_time
=0):
372 """ Update one or several rows of a table.
373 :param UPDATE: dictionary with the changes. dict keys are database columns that will be set with the dict values
374 :param table: database table to update
375 :param WHERE: dict or list of dicts to compose the SQL WHERE clause.
376 If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
377 If value is None, it will produce 'key is null'
378 If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
379 keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "="
380 The special keys "OR", "AND" with a dict value is used to create a nested WHERE
381 If a list, each item will be a dictionary that will be concatenated with OR
382 :return: the number of updated rows, raises exception upon error
385 values
= ",".join(map(self
.__tuple
2db
_format
_set
, UPDATE
.iteritems() ))
387 values
+= ",modified_at={:f}".format(modified_time
)
388 cmd
= "UPDATE " + table
+ " SET " + values
+ " WHERE " + self
.__create
_where
(WHERE
)
389 self
.logger
.debug(cmd
)
390 self
.cur
.execute(cmd
)
391 return self
.cur
.rowcount
393 def _new_uuid(self
, root_uuid
=None, used_table
=None, created_time
=0):
395 Generate a new uuid. It DOES NOT begin or end the transaction, so self.con.cursor must be created
396 :param root_uuid: master uuid of the transaction
397 :param used_table: the table this uuid is intended for
398 :param created_time: time of creation
399 :return: the created uuid
402 uuid
= str(myUuid
.uuid1())
403 # defining root_uuid if not provided
404 if root_uuid
is None:
407 created_at
= created_time
409 created_at
= time
.time()
411 cmd
= "INSERT INTO uuids (uuid, root_uuid, used_at, created_at) VALUES ('{:s}','{:s}','{:s}', {:f})".format(
412 uuid
, root_uuid
, used_table
, created_at
)
413 self
.logger
.debug(cmd
)
414 self
.cur
.execute(cmd
)
417 def _new_row_internal(self
, table
, INSERT
, add_uuid
=False, root_uuid
=None, created_time
=0, confidential_data
=False):
418 ''' Add one row into a table. It DOES NOT begin or end the transaction, so self.con.cursor must be created
420 INSERT: dictionary with the key:value to insert
421 table: table where to insert
422 add_uuid: if True, it will create an uuid key entry at INSERT if not provided
423 created_time: time to add to the created_time column
424 It checks presence of uuid and add one automatically otherwise
429 #create uuid if not provided
430 if 'uuid' not in INSERT
:
431 uuid
= INSERT
['uuid'] = str(myUuid
.uuid1()) # create_uuid
433 uuid
= str(INSERT
['uuid'])
437 #defining root_uuid if not provided
438 if root_uuid
is None:
441 created_at
= created_time
443 created_at
=time
.time()
445 cmd
= "INSERT INTO uuids (uuid, root_uuid, used_at, created_at) VALUES ('{:s}','{:s}','{:s}', {:f})".format(uuid
, root_uuid
, table
, created_at
)
446 self
.logger
.debug(cmd
)
447 self
.cur
.execute(cmd
)
449 cmd
= "INSERT INTO " + table
+" SET " + \
450 ",".join(map(self
.__tuple
2db
_format
_set
, INSERT
.iteritems() ))
452 cmd
+= ",created_at=%f" % created_time
453 if confidential_data
:
454 index
= cmd
.find("SET")
455 subcmd
= cmd
[:index
] + 'SET...'
456 self
.logger
.debug(subcmd
)
458 self
.logger
.debug(cmd
)
459 self
.cur
.execute(cmd
)
463 def _get_rows(self
,table
,uuid
):
464 cmd
= "SELECT * FROM {} WHERE uuid='{}'".format(str(table
), str(uuid
))
465 self
.logger
.debug(cmd
)
466 self
.cur
.execute(cmd
)
467 rows
= self
.cur
.fetchall()
470 def new_row(self
, table
, INSERT
, add_uuid
=False, created_time
=0, confidential_data
=False):
471 ''' Add one row into a table.
473 INSERT: dictionary with the key: value to insert
474 table: table where to insert
475 tenant_id: only useful for logs. If provided, logs will use this tenant_id
476 add_uuid: if True, it will create an uuid key entry at INSERT if not provided
477 It checks presence of uuid and add one automatically otherwise
480 if table
in self
.tables_with_created_field
and created_time
==0:
481 created_time
=time
.time()
486 self
.cur
= self
.con
.cursor()
487 return self
._new
_row
_internal
(table
, INSERT
, add_uuid
, None, created_time
, confidential_data
)
489 except (mdb
.Error
, AttributeError) as e
:
490 self
._format
_error
(e
, tries
, table
=table
)
493 def update_rows(self
, table
, UPDATE
, WHERE
, modified_time
=0):
494 """ Update one or several rows of a table.
495 :param UPDATE: dictionary with the changes. dict keys are database columns that will be set with the dict values
496 :param table: database table to update
497 :param WHERE: dict or list of dicts to compose the SQL WHERE clause.
498 If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
499 If value is None, it will produce 'key is null'
500 If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
501 keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "="
502 The special keys "OR", "AND" with a dict value is used to create a nested WHERE
503 If a list, each item will be a dictionary that will be concatenated with OR
504 :param modified_time: Can contain the time to be set to the table row
505 :return: the number of updated rows, raises exception upon error
507 if table
in self
.tables_with_created_field
and modified_time
==0:
508 modified_time
=time
.time()
513 self
.cur
= self
.con
.cursor()
514 return self
._update
_rows
(
515 table
, UPDATE
, WHERE
, modified_time
)
516 except (mdb
.Error
, AttributeError) as e
:
517 self
._format
_error
(e
, tries
, table
=table
)
520 def _delete_row_by_id_internal(self
, table
, uuid
):
521 cmd
= "DELETE FROM {} WHERE uuid = '{}'".format(table
, uuid
)
522 self
.logger
.debug(cmd
)
523 self
.cur
.execute(cmd
)
524 deleted
= self
.cur
.rowcount
526 self
.cur
= self
.con
.cursor()
527 cmd
= "DELETE FROM uuids WHERE root_uuid = '{}'".format(uuid
)
528 self
.logger
.debug(cmd
)
529 self
.cur
.execute(cmd
)
532 def delete_row_by_id(self
, table
, uuid
):
537 self
.cur
= self
.con
.cursor()
538 return self
._delete
_row
_by
_id
_internal
(table
, uuid
)
539 except (mdb
.Error
, AttributeError) as e
:
541 e
, tries
, "delete", "dependencies", table
=table
)
544 def delete_row(self
, **sql_dict
):
545 """ Deletes rows from a table.
546 :param UPDATE: dictionary with the changes. dict keys are database columns that will be set with the dict values
547 :param FROM: string with table name (Mandatory)
548 :param WHERE: dict or list of dicts to compose the SQL WHERE clause. (Optional)
549 If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
550 If value is None, it will produce 'key is null'
551 If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
552 keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "="
553 The special keys "OR", "AND" with a dict value is used to create a nested WHERE
554 If a list, each item will be a dictionary that will be concatenated with OR
555 :return: the number of deleted rows, raises exception upon error
558 cmd
= "DELETE FROM " + str(sql_dict
['FROM'])
559 if sql_dict
.get('WHERE'):
560 cmd
+= " WHERE " + self
.__create
_where
(sql_dict
['WHERE'])
561 if sql_dict
.get('LIMIT'):
562 cmd
+= " LIMIT " + str(sql_dict
['LIMIT'])
567 self
.cur
= self
.con
.cursor()
568 self
.logger
.debug(cmd
)
569 self
.cur
.execute(cmd
)
570 deleted
= self
.cur
.rowcount
572 except (mdb
.Error
, AttributeError) as e
:
573 self
._format
_error
(e
, tries
)
576 def get_rows_by_id(self
, table
, uuid
):
577 '''get row from a table based on uuid'''
582 self
.cur
= self
.con
.cursor(mdb
.cursors
.DictCursor
)
583 cmd
="SELECT * FROM {} where uuid='{}'".format(str(table
), str(uuid
))
584 self
.logger
.debug(cmd
)
585 self
.cur
.execute(cmd
)
586 rows
= self
.cur
.fetchall()
588 except (mdb
.Error
, AttributeError) as e
:
589 self
._format
_error
(e
, tries
, table
=table
)
592 def get_rows(self
, **sql_dict
):
593 """ Obtain rows from a table.
594 :param SELECT: list or tuple of fields to retrieve) (by default all)
595 :param FROM: string with table name (Mandatory)
596 :param WHERE: dict or list of dicts to compose the SQL WHERE clause. (Optional)
597 If a dict it will generate 'key1="value1" AND key2="value2" AND ...'.
598 If value is None, it will produce 'key is null'
599 If value is a list or tuple, it will produce 'key="value[0]" OR key="value[1]" OR ...'
600 keys can be suffixed by >,<,<>,>=,<= so that this is used to compare key and value instead of "="
601 The special keys "OR", "AND" with a dict value is used to create a nested WHERE
602 If a list, each item will be a dictionary that will be concatenated with OR
603 :param LIMIT: limit the number of obtained entries (Optional)
604 :param ORDER_BY: list or tuple of fields to order, add ' DESC' to each item if inverse order is required
605 :return: a list with dictionaries at each row, raises exception upon error
609 if 'SELECT' in sql_dict
:
610 if isinstance(sql_dict
['SELECT'], (tuple, list)):
611 cmd
+= ",".join(map(str, sql_dict
['SELECT']))
613 cmd
+= sql_dict
['SELECT']
617 cmd
+= " FROM " + str(sql_dict
['FROM'])
618 if sql_dict
.get('WHERE'):
619 cmd
+= " WHERE " + self
.__create
_where
(sql_dict
['WHERE'])
621 if 'ORDER_BY' in sql_dict
:
623 if isinstance(sql_dict
['ORDER_BY'], (tuple, list)):
624 cmd
+= ",".join(map(str, sql_dict
['ORDER_BY']))
626 cmd
+= str(sql_dict
['ORDER_BY'])
628 if 'LIMIT' in sql_dict
:
629 cmd
+= " LIMIT " + str(sql_dict
['LIMIT'])
635 self
.cur
= self
.con
.cursor(mdb
.cursors
.DictCursor
)
636 self
.logger
.debug(cmd
)
637 self
.cur
.execute(cmd
)
638 rows
= self
.cur
.fetchall()
640 except (mdb
.Error
, AttributeError) as e
:
641 self
.logger
.error("Exception '{}' with command '{}'".format(e
, cmd
))
642 self
._format
_error
(e
, tries
)
645 def get_table_by_uuid_name(self
, table
, uuid_name
, error_item_text
=None, allow_serveral
=False, WHERE_OR
={}, WHERE_AND_OR
="OR"):
646 ''' Obtain One row from a table based on name or uuid.
648 table: string of table name
649 uuid_name: name or uuid. If not uuid format is found, it is considered a name
650 allow_severeral: if False return ERROR if more than one row are founded
651 error_item_text: in case of error it identifies the 'item' name for a proper output text
652 'WHERE_OR': dict of key:values, translated to key=value OR ... (Optional)
653 'WHERE_AND_OR: str 'AND' or 'OR'(by default) mark the priority to 'WHERE AND (WHERE_OR)' or (WHERE) OR WHERE_OR' (Optional
654 Return: if allow_several==False, a dictionary with this row, or error if no item is found or more than one is found
655 if allow_several==True, a list of dictionaries with the row or rows, error if no item is found
658 if error_item_text
==None:
659 error_item_text
= table
660 what
= 'uuid' if af
.check_valid_uuid(uuid_name
) else 'name'
661 cmd
= " SELECT * FROM {} WHERE {}='{}'".format(table
, what
, uuid_name
)
663 where_or
= self
.__create
_where
(WHERE_OR
, use_or
=True)
664 if WHERE_AND_OR
== "AND":
665 cmd
+= " AND (" + where_or
+ ")"
667 cmd
+= " OR " + where_or
673 self
.cur
= self
.con
.cursor(mdb
.cursors
.DictCursor
)
674 self
.logger
.debug(cmd
)
675 self
.cur
.execute(cmd
)
676 number
= self
.cur
.rowcount
678 raise db_base_Exception("No {} found with {} '{}'".format(error_item_text
, what
, uuid_name
), http_code
=httperrors
.Not_Found
)
679 elif number
> 1 and not allow_serveral
:
680 raise db_base_Exception("More than one {} found with {} '{}'".format(error_item_text
, what
, uuid_name
), http_code
=httperrors
.Conflict
)
682 rows
= self
.cur
.fetchall()
684 rows
= self
.cur
.fetchone()
686 except (mdb
.Error
, AttributeError) as e
:
687 self
._format
_error
(e
, tries
, table
=table
)
690 def get_uuid(self
, uuid
):
691 '''check in the database if this uuid is already present'''
696 self
.cur
= self
.con
.cursor(mdb
.cursors
.DictCursor
)
697 self
.cur
.execute("SELECT * FROM uuids where uuid='" + str(uuid
) + "'")
698 rows
= self
.cur
.fetchall()
699 return self
.cur
.rowcount
, rows
700 except (mdb
.Error
, AttributeError) as e
:
701 self
._format
_error
(e
, tries
)
704 def get_uuid_from_name(self
, table
, name
):
705 '''Searchs in table the name and returns the uuid
711 self
.cur
= self
.con
.cursor(mdb
.cursors
.DictCursor
)
712 where_text
= "name='" + name
+"'"
713 self
.cur
.execute("SELECT * FROM " + table
+ " WHERE "+ where_text
)
714 rows
= self
.cur
.fetchall()
715 if self
.cur
.rowcount
==0:
716 return 0, "Name %s not found in table %s" %(name
, table
)
717 elif self
.cur
.rowcount
>1:
718 return self
.cur
.rowcount
, "More than one VNF with name %s found in table %s" %(name
, table
)
719 return self
.cur
.rowcount
, rows
[0]["uuid"]
720 except (mdb
.Error
, AttributeError) as e
:
721 self
._format
_error
(e
, tries
, table
=table
)