| 1 | # -*- coding: utf-8 -*-
|
|---|
| 2 | # $Id: db.py 56808 2015-07-05 17:00:05Z vboxsync $
|
|---|
| 3 |
|
|---|
| 4 | """
|
|---|
| 5 | Test Manager - Database Interface.
|
|---|
| 6 | """
|
|---|
| 7 |
|
|---|
| 8 | __copyright__ = \
|
|---|
| 9 | """
|
|---|
| 10 | Copyright (C) 2012-2015 Oracle Corporation
|
|---|
| 11 |
|
|---|
| 12 | This file is part of VirtualBox Open Source Edition (OSE), as
|
|---|
| 13 | available from http://www.alldomusa.eu.org. This file is free software;
|
|---|
| 14 | you can redistribute it and/or modify it under the terms of the GNU
|
|---|
| 15 | General Public License (GPL) as published by the Free Software
|
|---|
| 16 | Foundation, in version 2 as it comes in the "COPYING" file of the
|
|---|
| 17 | VirtualBox OSE distribution. VirtualBox OSE is distributed in the
|
|---|
| 18 | hope that it will be useful, but WITHOUT ANY WARRANTY of any kind.
|
|---|
| 19 |
|
|---|
| 20 | The contents of this file may alternatively be used under the terms
|
|---|
| 21 | of the Common Development and Distribution License Version 1.0
|
|---|
| 22 | (CDDL) only, as it comes in the "COPYING.CDDL" file of the
|
|---|
| 23 | VirtualBox OSE distribution, in which case the provisions of the
|
|---|
| 24 | CDDL are applicable instead of those of the GPL.
|
|---|
| 25 |
|
|---|
| 26 | You may elect to license modified versions of this file under the
|
|---|
| 27 | terms and conditions of either the GPL or the CDDL or both.
|
|---|
| 28 | """
|
|---|
| 29 | __version__ = "$Revision: 56808 $"
|
|---|
| 30 |
|
|---|
| 31 |
|
|---|
| 32 | # Standard python imports.
|
|---|
| 33 | import datetime;
|
|---|
| 34 | import os;
|
|---|
| 35 | import psycopg2;
|
|---|
| 36 | import psycopg2.extensions;
|
|---|
| 37 | import sys;
|
|---|
| 38 |
|
|---|
| 39 | # Validation Kit imports.
|
|---|
| 40 | from common import utils, webutils;
|
|---|
| 41 | from testmanager import config;
|
|---|
| 42 |
|
|---|
| 43 | # Fix psycho unicode handling in psycopg2 with python 2.x.
|
|---|
| 44 | if sys.version_info[0] < 3:
|
|---|
| 45 | psycopg2.extensions.register_type(psycopg2.extensions.UNICODE);
|
|---|
| 46 | psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY);
|
|---|
| 47 |
|
|---|
| 48 |
|
|---|
| 49 | def isDbTimestampInfinity(tsValue):
|
|---|
| 50 | """
|
|---|
| 51 | Checks if tsValue is an infinity timestamp.
|
|---|
| 52 | """
|
|---|
| 53 | ## @todo improve this test...
|
|---|
| 54 | return tsValue.year >= 9999;
|
|---|
| 55 |
|
|---|
| 56 | def isDbTimestamp(oValue):
|
|---|
| 57 | """
|
|---|
| 58 | Checks if oValue is a DB timestamp object.
|
|---|
| 59 | """
|
|---|
| 60 | if isinstance(oValue, datetime.datetime):
|
|---|
| 61 | return True;
|
|---|
| 62 | if utils.isString(oValue):
|
|---|
| 63 | ## @todo detect strings as well.
|
|---|
| 64 | return False;
|
|---|
| 65 | return getattr(oValue, 'pydatetime', None) != None;
|
|---|
| 66 |
|
|---|
| 67 | def dbTimestampToDatetime(oValue):
|
|---|
| 68 | """
|
|---|
| 69 | Converts a database timestamp to a datetime instance.
|
|---|
| 70 | """
|
|---|
| 71 | if isinstance(oValue, datetime.datetime):
|
|---|
| 72 | return oValue;
|
|---|
| 73 | if utils.isString(oValue):
|
|---|
| 74 | raise Exception('TODO');
|
|---|
| 75 | return oValue.pydatetime();
|
|---|
| 76 |
|
|---|
| 77 | def dbTimestampToZuluDatetime(oValue):
|
|---|
| 78 | """
|
|---|
| 79 | Converts a database timestamp to a zulu datetime instance.
|
|---|
| 80 | """
|
|---|
| 81 | tsValue = dbTimestampToDatetime(oValue);
|
|---|
| 82 |
|
|---|
| 83 | if tsValue.tzinfo is not None:
|
|---|
| 84 | class UTC(datetime.tzinfo):
|
|---|
| 85 | """UTC TZ Info Class"""
|
|---|
| 86 | def utcoffset(self, _):
|
|---|
| 87 | return datetime.timedelta(0);
|
|---|
| 88 | def tzname(self, _):
|
|---|
| 89 | return "UTC";
|
|---|
| 90 | def dst(self, _):
|
|---|
| 91 | return datetime.timedelta(0);
|
|---|
| 92 | tsValue = tsValue.astimezone(UTC());
|
|---|
| 93 |
|
|---|
| 94 | return tsValue;
|
|---|
| 95 |
|
|---|
| 96 | def isDbInterval(oValue):
|
|---|
| 97 | """
|
|---|
| 98 | Checks if oValue is a DB interval object.
|
|---|
| 99 | """
|
|---|
| 100 | if isinstance(oValue, datetime.timedelta):
|
|---|
| 101 | return True;
|
|---|
| 102 | return False;
|
|---|
| 103 |
|
|---|
| 104 |
|
|---|
| 105 | class TMDatabaseIntegrityException(Exception):
|
|---|
| 106 | """
|
|---|
| 107 | Herolds a database integrity error up the callstack.
|
|---|
| 108 |
|
|---|
| 109 | Do NOT use directly, only thru TMDatabaseConnection.integrityException.
|
|---|
| 110 | Otherwise, we won't be able to log the issue.
|
|---|
| 111 | """
|
|---|
| 112 | pass;
|
|---|
| 113 |
|
|---|
| 114 |
|
|---|
| 115 | class TMDatabaseCursor(object):
|
|---|
| 116 | """ Cursor wrapper class. """
|
|---|
| 117 |
|
|---|
| 118 | def __init__(self, oDb, oCursor):
|
|---|
| 119 | self._oDb = oDb;
|
|---|
| 120 | self._oCursor = oCursor;
|
|---|
| 121 |
|
|---|
| 122 | def execute(self, sOperation, aoArgs = None):
|
|---|
| 123 | """ See TMDatabaseConnection.execute()"""
|
|---|
| 124 | return self._oDb.executeInternal(self._oCursor, sOperation, aoArgs, utils.getCallerName());
|
|---|
| 125 |
|
|---|
| 126 | def callProc(self, sProcedure, aoArgs = None):
|
|---|
| 127 | """ See TMDatabaseConnection.callProc()"""
|
|---|
| 128 | return self._oDb.callProcInternal(self._oCursor, sProcedure, aoArgs, utils.getCallerName());
|
|---|
| 129 |
|
|---|
| 130 | def insertList(self, sInsertSql, aoList, fnEntryFmt):
|
|---|
| 131 | """ See TMDatabaseConnection.insertList. """
|
|---|
| 132 | return self._oDb.insertListInternal(self._oCursor, sInsertSql, aoList, fnEntryFmt, utils.getCallerName());
|
|---|
| 133 |
|
|---|
| 134 | def fetchOne(self):
|
|---|
| 135 | """Wrapper around Psycopg2.cursor.fetchone."""
|
|---|
| 136 | return self._oCursor.fetchone();
|
|---|
| 137 |
|
|---|
| 138 | def fetchMany(self, cRows = None):
|
|---|
| 139 | """Wrapper around Psycopg2.cursor.fetchmany."""
|
|---|
| 140 | return self._oCursor.fetchmany(cRows if cRows is not None else self._oCursor.arraysize);
|
|---|
| 141 |
|
|---|
| 142 | def fetchAll(self):
|
|---|
| 143 | """Wrapper around Psycopg2.cursor.fetchall."""
|
|---|
| 144 | return self._oCursor.fetchall();
|
|---|
| 145 |
|
|---|
| 146 | def getRowCount(self):
|
|---|
| 147 | """Wrapper around Psycopg2.cursor.rowcount."""
|
|---|
| 148 | return self._oCursor.rowcount;
|
|---|
| 149 |
|
|---|
| 150 | def formatBindArgs(self, sStatement, aoArgs):
|
|---|
| 151 | """Wrapper around Psycopg2.cursor.mogrify."""
|
|---|
| 152 | oRet = self._oCursor.mogrify(sStatement, aoArgs);
|
|---|
| 153 | if sys.version_info[0] >= 3 and not isinstance(oRet, str):
|
|---|
| 154 | oRet = oRet.decode('utf-8');
|
|---|
| 155 | return oRet;
|
|---|
| 156 |
|
|---|
| 157 | @staticmethod
|
|---|
| 158 | def isTsInfinity(tsValue):
|
|---|
| 159 | """ Checks if tsValue is an infinity timestamp. """
|
|---|
| 160 | return isDbTimestampInfinity(tsValue);
|
|---|
| 161 |
|
|---|
| 162 |
|
|---|
| 163 | class TMDatabaseConnection(object):
|
|---|
| 164 | """
|
|---|
| 165 | Test Manager Database Access class.
|
|---|
| 166 |
|
|---|
| 167 | This class contains no logic, just raw access abstraction and utilities,
|
|---|
| 168 | as well as some debug help and some statistics.
|
|---|
| 169 | """
|
|---|
| 170 |
|
|---|
| 171 | def __init__(self, fnDPrint = None, oSrvGlue = None):
|
|---|
| 172 | """
|
|---|
| 173 | Database connection wrapper.
|
|---|
| 174 | The fnDPrint is for debug logging of all database activity.
|
|---|
| 175 |
|
|---|
| 176 | Raises an exception on failure.
|
|---|
| 177 | """
|
|---|
| 178 |
|
|---|
| 179 | sAppName = '%s-%s' % (os.getpid(), os.path.basename(sys.argv[0]),)
|
|---|
| 180 | if len(sAppName) >= 64:
|
|---|
| 181 | sAppName = sAppName[:64];
|
|---|
| 182 | os.environ['PGAPPNAME'] = sAppName;
|
|---|
| 183 |
|
|---|
| 184 | dArgs = \
|
|---|
| 185 | { \
|
|---|
| 186 | 'database': config.g_ksDatabaseName,
|
|---|
| 187 | 'user': config.g_ksDatabaseUser,
|
|---|
| 188 | 'password': config.g_ksDatabasePassword,
|
|---|
| 189 | # 'application_name': sAppName, - Darn stale debian! :/
|
|---|
| 190 | };
|
|---|
| 191 | if config.g_ksDatabaseAddress is not None:
|
|---|
| 192 | dArgs['host'] = config.g_ksDatabaseAddress;
|
|---|
| 193 | if config.g_ksDatabasePort is not None:
|
|---|
| 194 | dArgs['port'] = config.g_ksDatabasePort;
|
|---|
| 195 | self._oConn = psycopg2.connect(**dArgs); # pylint: disable=W0142
|
|---|
| 196 | self._oConn.set_client_encoding('UTF-8');
|
|---|
| 197 | self._oCursor = self._oConn.cursor();
|
|---|
| 198 | self._oExplainConn = None;
|
|---|
| 199 | self._oExplainCursor = None;
|
|---|
| 200 | if config.g_kfWebUiSqlTraceExplain and config.g_kfWebUiSqlTrace:
|
|---|
| 201 | self._oExplainConn = psycopg2.connect(**dArgs); # pylint: disable=W0142
|
|---|
| 202 | self._oExplainConn.set_client_encoding('UTF-8');
|
|---|
| 203 | self._oExplainCursor = self._oExplainConn.cursor();
|
|---|
| 204 | self._fTransaction = False;
|
|---|
| 205 | self._tsCurrent = None;
|
|---|
| 206 | self._tsCurrentMinusOne = None;
|
|---|
| 207 |
|
|---|
| 208 | assert self.isAutoCommitting() is False;
|
|---|
| 209 |
|
|---|
| 210 | # Debug and introspection.
|
|---|
| 211 | self._fnDPrint = fnDPrint;
|
|---|
| 212 | self._aoTraceBack = [];
|
|---|
| 213 |
|
|---|
| 214 | # Exception class handles.
|
|---|
| 215 | self.oXcptError = psycopg2.Error;
|
|---|
| 216 |
|
|---|
| 217 | if oSrvGlue is not None:
|
|---|
| 218 | oSrvGlue.registerDebugInfoCallback(self.debugInfoCallback);
|
|---|
| 219 |
|
|---|
| 220 | def isAutoCommitting(self):
|
|---|
| 221 | """ Work around missing autocommit attribute in older versions."""
|
|---|
| 222 | return getattr(self._oConn, 'autocommit', False);
|
|---|
| 223 |
|
|---|
| 224 | def close(self):
|
|---|
| 225 | """
|
|---|
| 226 | Closes the connection and renders all cursors useless.
|
|---|
| 227 | """
|
|---|
| 228 | if self._oCursor is not None:
|
|---|
| 229 | self._oCursor.close();
|
|---|
| 230 | self._oCursor = None;
|
|---|
| 231 |
|
|---|
| 232 | if self._oConn is not None:
|
|---|
| 233 | self._oConn.close();
|
|---|
| 234 | self._oConn = None;
|
|---|
| 235 |
|
|---|
| 236 | if self._oExplainCursor is not None:
|
|---|
| 237 | self._oExplainCursor.close();
|
|---|
| 238 | self._oExplainCursor = None;
|
|---|
| 239 |
|
|---|
| 240 | if self._oExplainConn is not None:
|
|---|
| 241 | self._oExplainConn.close();
|
|---|
| 242 | self._oExplainConn = None;
|
|---|
| 243 |
|
|---|
| 244 |
|
|---|
| 245 | def _startedTransaction(self):
|
|---|
| 246 | """
|
|---|
| 247 | Called to work the _fTransaction and related variables when starting
|
|---|
| 248 | a transaction.
|
|---|
| 249 | """
|
|---|
| 250 | self._fTransaction = True;
|
|---|
| 251 | self._tsCurrent = None;
|
|---|
| 252 | self._tsCurrentMinusOne = None;
|
|---|
| 253 | return None;
|
|---|
| 254 |
|
|---|
| 255 | def _endedTransaction(self):
|
|---|
| 256 | """
|
|---|
| 257 | Called to work the _fTransaction and related variables when ending
|
|---|
| 258 | a transaction.
|
|---|
| 259 | """
|
|---|
| 260 | self._fTransaction = False;
|
|---|
| 261 | self._tsCurrent = None;
|
|---|
| 262 | self._tsCurrentMinusOne = None;
|
|---|
| 263 | return None;
|
|---|
| 264 |
|
|---|
| 265 | def begin(self):
|
|---|
| 266 | """
|
|---|
| 267 | Currently just for marking where a transaction starts in the code.
|
|---|
| 268 | """
|
|---|
| 269 | assert self._oConn is not None;
|
|---|
| 270 | assert self.isAutoCommitting() is False;
|
|---|
| 271 | self._aoTraceBack.append([utils.timestampNano(), 'START TRANSACTION', 0, 0, utils.getCallerName(), None]);
|
|---|
| 272 | self._startedTransaction();
|
|---|
| 273 | return True;
|
|---|
| 274 |
|
|---|
| 275 | def commit(self, sCallerName = None):
|
|---|
| 276 | """ Wrapper around Psycopg2.connection.commit."""
|
|---|
| 277 | assert self._fTransaction is True;
|
|---|
| 278 |
|
|---|
| 279 | nsStart = utils.timestampNano();
|
|---|
| 280 | oRc = self._oConn.commit();
|
|---|
| 281 | cNsElapsed = utils.timestampNano() - nsStart;
|
|---|
| 282 |
|
|---|
| 283 | if sCallerName is None:
|
|---|
| 284 | sCallerName = utils.getCallerName();
|
|---|
| 285 | self._aoTraceBack.append([nsStart, 'COMMIT', cNsElapsed, 0, sCallerName, None]);
|
|---|
| 286 | self._endedTransaction();
|
|---|
| 287 | return oRc;
|
|---|
| 288 |
|
|---|
| 289 | def maybeCommit(self, fCommit):
|
|---|
| 290 | """
|
|---|
| 291 | Commits if fCommit is True.
|
|---|
| 292 | Returns True if committed, False if not.
|
|---|
| 293 | """
|
|---|
| 294 | if fCommit is True:
|
|---|
| 295 | self.commit(utils.getCallerName());
|
|---|
| 296 | return True;
|
|---|
| 297 | return False;
|
|---|
| 298 |
|
|---|
| 299 | def rollback(self):
|
|---|
| 300 | """ Wrapper around Psycopg2.connection.rollback."""
|
|---|
| 301 | nsStart = utils.timestampNano();
|
|---|
| 302 | oRc = self._oConn.rollback();
|
|---|
| 303 | cNsElapsed = utils.timestampNano() - nsStart;
|
|---|
| 304 |
|
|---|
| 305 | self._aoTraceBack.append([nsStart, 'ROLLBACK', cNsElapsed, 0, utils.getCallerName(), None]);
|
|---|
| 306 | self._endedTransaction();
|
|---|
| 307 | return oRc;
|
|---|
| 308 |
|
|---|
| 309 | #
|
|---|
| 310 | # Internal cursor workers.
|
|---|
| 311 | #
|
|---|
| 312 |
|
|---|
| 313 | def executeInternal(self, oCursor, sOperation, aoArgs, sCallerName):
|
|---|
| 314 | """
|
|---|
| 315 | Execute a query or command.
|
|---|
| 316 |
|
|---|
| 317 | Mostly a wrapper around the psycopg2 cursor method with the same name,
|
|---|
| 318 | but collect data for traceback.
|
|---|
| 319 | """
|
|---|
| 320 | if aoArgs is None:
|
|---|
| 321 | aoArgs = list();
|
|---|
| 322 |
|
|---|
| 323 | sBound = oCursor.mogrify(unicode(sOperation), aoArgs);
|
|---|
| 324 | if sys.version_info[0] >= 3 and not isinstance(sBound, str):
|
|---|
| 325 | sBound = sBound.decode('utf-8');
|
|---|
| 326 |
|
|---|
| 327 | aasExplain = None;
|
|---|
| 328 | if self._oExplainCursor is not None:
|
|---|
| 329 | try:
|
|---|
| 330 | if config.g_kfWebUiSqlTraceExplainTiming:
|
|---|
| 331 | self._oExplainCursor.execute('EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE, TIMING) ' + sBound);
|
|---|
| 332 | else:
|
|---|
| 333 | self._oExplainCursor.execute('EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE) ' + sBound);
|
|---|
| 334 | except Exception as oXcpt:
|
|---|
| 335 | aasExplain = [ ['Explain exception: '], [str(oXcpt)]];
|
|---|
| 336 | try: self._oExplainConn.rollback();
|
|---|
| 337 | except: pass;
|
|---|
| 338 | else:
|
|---|
| 339 | aasExplain = self._oExplainCursor.fetchall();
|
|---|
| 340 |
|
|---|
| 341 | nsStart = utils.timestampNano();
|
|---|
| 342 | try:
|
|---|
| 343 | oRc = oCursor.execute(sBound);
|
|---|
| 344 | except Exception as oXcpt:
|
|---|
| 345 | cNsElapsed = utils.timestampNano() - nsStart;
|
|---|
| 346 | self._aoTraceBack.append([nsStart, 'oXcpt=%s; Statement: %s' % (oXcpt, sBound), cNsElapsed, 0, sCallerName, None]);
|
|---|
| 347 | if self._fnDPrint is not None:
|
|---|
| 348 | self._fnDPrint('db::execute %u ns, caller %s: oXcpt=%s; Statement: %s'
|
|---|
| 349 | % (cNsElapsed, sCallerName, oXcpt, sBound));
|
|---|
| 350 | raise;
|
|---|
| 351 | cNsElapsed = utils.timestampNano() - nsStart;
|
|---|
| 352 |
|
|---|
| 353 | if self._fTransaction is False and not self.isAutoCommitting(): # Even SELECTs starts transactions with psycopg2, see FAQ.
|
|---|
| 354 | self._aoTraceBack.append([nsStart, '[START TRANSACTION]', 0, 0, sCallerName, None]);
|
|---|
| 355 | self._startedTransaction();
|
|---|
| 356 | self._aoTraceBack.append([nsStart, sBound, cNsElapsed, oCursor.rowcount, sCallerName, aasExplain]);
|
|---|
| 357 | if self._fnDPrint is not None:
|
|---|
| 358 | self._fnDPrint('db::execute %u ns, caller %s: "\n%s"' % (cNsElapsed, sCallerName, sBound));
|
|---|
| 359 | if self.isAutoCommitting():
|
|---|
| 360 | self._aoTraceBack.append([nsStart, '[AUTO COMMIT]', 0, 0, sCallerName, None]);
|
|---|
| 361 |
|
|---|
| 362 | return oRc;
|
|---|
| 363 |
|
|---|
| 364 | def callProcInternal(self, oCursor, sProcedure, aoArgs, sCallerName):
|
|---|
| 365 | """
|
|---|
| 366 | Call a stored procedure.
|
|---|
| 367 |
|
|---|
| 368 | Mostly a wrapper around the psycopg2 cursor method 'callproc', but
|
|---|
| 369 | collect data for traceback.
|
|---|
| 370 | """
|
|---|
| 371 | if aoArgs is None:
|
|---|
| 372 | aoArgs = list();
|
|---|
| 373 |
|
|---|
| 374 | nsStart = utils.timestampNano();
|
|---|
| 375 | try:
|
|---|
| 376 | oRc = oCursor.callproc(sProcedure, aoArgs);
|
|---|
| 377 | except Exception as oXcpt:
|
|---|
| 378 | cNsElapsed = utils.timestampNano() - nsStart;
|
|---|
| 379 | self._aoTraceBack.append([nsStart, 'oXcpt=%s; Calling: %s(%s)' % (oXcpt, sProcedure, aoArgs),
|
|---|
| 380 | cNsElapsed, 0, sCallerName, None]);
|
|---|
| 381 | if self._fnDPrint is not None:
|
|---|
| 382 | self._fnDPrint('db::callproc %u ns, caller %s: oXcpt=%s; Calling: %s(%s)'
|
|---|
| 383 | % (cNsElapsed, sCallerName, oXcpt, sProcedure, aoArgs));
|
|---|
| 384 | raise;
|
|---|
| 385 | cNsElapsed = utils.timestampNano() - nsStart;
|
|---|
| 386 |
|
|---|
| 387 | if self._fTransaction is False and not self.isAutoCommitting(): # Even SELECTs starts transactions with psycopg2, see FAQ.
|
|---|
| 388 | self._aoTraceBack.append([nsStart, '[START TRANSACTION]', 0, 0, sCallerName, None]);
|
|---|
| 389 | self._startedTransaction();
|
|---|
| 390 | self._aoTraceBack.append([nsStart, '%s(%s)' % (sProcedure, aoArgs), cNsElapsed, oCursor.rowcount, sCallerName, None]);
|
|---|
| 391 | if self._fnDPrint is not None:
|
|---|
| 392 | self._fnDPrint('db::callproc %u ns, caller %s: "%s(%s)"' % (cNsElapsed, sCallerName, sProcedure, aoArgs));
|
|---|
| 393 | if self.isAutoCommitting():
|
|---|
| 394 | self._aoTraceBack.append([nsStart, '[AUTO COMMIT]', 0, 0, sCallerName, sCallerName, None]);
|
|---|
| 395 |
|
|---|
| 396 | return oRc;
|
|---|
| 397 |
|
|---|
| 398 | def insertListInternal(self, oCursor, sInsertSql, aoList, fnEntryFmt, sCallerName):
|
|---|
| 399 | """
|
|---|
| 400 | Optimizes the insertion of a list of values.
|
|---|
| 401 | """
|
|---|
| 402 | oRc = None;
|
|---|
| 403 | asValues = [];
|
|---|
| 404 | for aoEntry in aoList:
|
|---|
| 405 | asValues.append(fnEntryFmt(aoEntry));
|
|---|
| 406 | if len(asValues) > 256:
|
|---|
| 407 | oRc = self.executeInternal(oCursor, sInsertSql + 'VALUES' + ', '.join(asValues), None, sCallerName);
|
|---|
| 408 | asValues = [];
|
|---|
| 409 | if len(asValues) > 0:
|
|---|
| 410 | oRc = self.executeInternal(oCursor, sInsertSql + 'VALUES' + ', '.join(asValues), None, sCallerName);
|
|---|
| 411 | return oRc
|
|---|
| 412 |
|
|---|
| 413 | def _fetchOne(self, oCursor):
|
|---|
| 414 | """Wrapper around Psycopg2.cursor.fetchone."""
|
|---|
| 415 | oRow = oCursor.fetchone()
|
|---|
| 416 | if self._fnDPrint is not None:
|
|---|
| 417 | self._fnDPrint('db:fetchOne returns: %s' % (oRow,));
|
|---|
| 418 | return oRow;
|
|---|
| 419 |
|
|---|
| 420 | def _fetchMany(self, oCursor, cRows):
|
|---|
| 421 | """Wrapper around Psycopg2.cursor.fetchmany."""
|
|---|
| 422 | return oCursor.fetchmany(cRows if cRows is not None else oCursor.arraysize);
|
|---|
| 423 |
|
|---|
| 424 | def _fetchAll(self, oCursor):
|
|---|
| 425 | """Wrapper around Psycopg2.cursor.fetchall."""
|
|---|
| 426 | return oCursor.fetchall()
|
|---|
| 427 |
|
|---|
| 428 | def _getRowCountWorker(self, oCursor):
|
|---|
| 429 | """Wrapper around Psycopg2.cursor.rowcount."""
|
|---|
| 430 | return oCursor.rowcount;
|
|---|
| 431 |
|
|---|
| 432 |
|
|---|
| 433 | #
|
|---|
| 434 | # Default cursor access.
|
|---|
| 435 | #
|
|---|
| 436 |
|
|---|
| 437 | def execute(self, sOperation, aoArgs = None):
|
|---|
| 438 | """
|
|---|
| 439 | Execute a query or command.
|
|---|
| 440 |
|
|---|
| 441 | Mostly a wrapper around the psycopg2 cursor method with the same name,
|
|---|
| 442 | but collect data for traceback.
|
|---|
| 443 | """
|
|---|
| 444 | return self.executeInternal(self._oCursor, sOperation, aoArgs, utils.getCallerName());
|
|---|
| 445 |
|
|---|
| 446 | def callProc(self, sProcedure, aoArgs = None):
|
|---|
| 447 | """
|
|---|
| 448 | Call a stored procedure.
|
|---|
| 449 |
|
|---|
| 450 | Mostly a wrapper around the psycopg2 cursor method 'callproc', but
|
|---|
| 451 | collect data for traceback.
|
|---|
| 452 | """
|
|---|
| 453 | return self.callProcInternal(self._oCursor, sProcedure, aoArgs, utils.getCallerName());
|
|---|
| 454 |
|
|---|
| 455 | def insertList(self, sInsertSql, aoList, fnEntryFmt):
|
|---|
| 456 | """
|
|---|
| 457 | Optimizes the insertion of a list of values.
|
|---|
| 458 | """
|
|---|
| 459 | return self.insertListInternal(self._oCursor, sInsertSql, aoList, fnEntryFmt, utils.getCallerName());
|
|---|
| 460 |
|
|---|
| 461 | def fetchOne(self):
|
|---|
| 462 | """Wrapper around Psycopg2.cursor.fetchone."""
|
|---|
| 463 | return self._oCursor.fetchone();
|
|---|
| 464 |
|
|---|
| 465 | def fetchMany(self, cRows = None):
|
|---|
| 466 | """Wrapper around Psycopg2.cursor.fetchmany."""
|
|---|
| 467 | return self._oCursor.fetchmany(cRows if cRows is not None else self._oCursor.arraysize);
|
|---|
| 468 |
|
|---|
| 469 | def fetchAll(self):
|
|---|
| 470 | """Wrapper around Psycopg2.cursor.fetchall."""
|
|---|
| 471 | return self._oCursor.fetchall();
|
|---|
| 472 |
|
|---|
| 473 | def getRowCount(self):
|
|---|
| 474 | """Wrapper around Psycopg2.cursor.rowcount."""
|
|---|
| 475 | return self._oCursor.rowcount;
|
|---|
| 476 |
|
|---|
| 477 | def formatBindArgs(self, sStatement, aoArgs):
|
|---|
| 478 | """Wrapper around Psycopg2.cursor.mogrify."""
|
|---|
| 479 | oRet = self._oCursor.mogrify(sStatement, aoArgs);
|
|---|
| 480 | if sys.version_info[0] >= 3 and not isinstance(oRet, str):
|
|---|
| 481 | oRet = oRet.decode('utf-8');
|
|---|
| 482 | return oRet;
|
|---|
| 483 |
|
|---|
| 484 | def getCurrentTimestamps(self):
|
|---|
| 485 | """
|
|---|
| 486 | Returns the current timestamp and the current timestamp minus one tick.
|
|---|
| 487 | This will start a transaction if necessary.
|
|---|
| 488 | """
|
|---|
| 489 | if self._tsCurrent is None:
|
|---|
| 490 | self.execute('SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP - INTERVAL \'1 microsecond\'');
|
|---|
| 491 | (self._tsCurrent, self._tsCurrentMinusOne) = self.fetchOne();
|
|---|
| 492 | return (self._tsCurrent, self._tsCurrentMinusOne);
|
|---|
| 493 |
|
|---|
| 494 | def getCurrentTimestamp(self):
|
|---|
| 495 | """
|
|---|
| 496 | Returns the current timestamp.
|
|---|
| 497 | This will start a transaction if necessary.
|
|---|
| 498 | """
|
|---|
| 499 | if self._tsCurrent is None:
|
|---|
| 500 | self.getCurrentTimestamps();
|
|---|
| 501 | return self._tsCurrent;
|
|---|
| 502 |
|
|---|
| 503 | def getCurrentTimestampMinusOne(self):
|
|---|
| 504 | """
|
|---|
| 505 | Returns the current timestamp minus one tick.
|
|---|
| 506 | This will start a transaction if necessary.
|
|---|
| 507 | """
|
|---|
| 508 | if self._tsCurrentMinusOne is None:
|
|---|
| 509 | self.getCurrentTimestamps();
|
|---|
| 510 | return self._tsCurrentMinusOne;
|
|---|
| 511 |
|
|---|
| 512 |
|
|---|
| 513 | #
|
|---|
| 514 | # Additional cursors.
|
|---|
| 515 | #
|
|---|
| 516 | def openCursor(self):
|
|---|
| 517 | """
|
|---|
| 518 | Opens a new cursor (TMDatabaseCursor).
|
|---|
| 519 | """
|
|---|
| 520 | oCursor = self._oConn.cursor();
|
|---|
| 521 | return TMDatabaseCursor(self, oCursor);
|
|---|
| 522 |
|
|---|
| 523 | #
|
|---|
| 524 | # Utilities.
|
|---|
| 525 | #
|
|---|
| 526 |
|
|---|
| 527 | @staticmethod
|
|---|
| 528 | def isTsInfinity(tsValue):
|
|---|
| 529 | """ Checks if tsValue is an infinity timestamp. """
|
|---|
| 530 | return isDbTimestampInfinity(tsValue);
|
|---|
| 531 |
|
|---|
| 532 | #
|
|---|
| 533 | # Error stuff.
|
|---|
| 534 | #
|
|---|
| 535 | def integrityException(self, sMessage):
|
|---|
| 536 | """
|
|---|
| 537 | Database integrity reporter and exception factory.
|
|---|
| 538 | Returns an TMDatabaseIntegrityException which the caller can raise.
|
|---|
| 539 | """
|
|---|
| 540 | ## @todo Create a new database connection and log the issue in the SystemLog table.
|
|---|
| 541 | ## Alternatively, rollback whatever is going on and do it using the current one.
|
|---|
| 542 | return TMDatabaseIntegrityException(sMessage);
|
|---|
| 543 |
|
|---|
| 544 |
|
|---|
| 545 | #
|
|---|
| 546 | # Debugging.
|
|---|
| 547 | #
|
|---|
| 548 |
|
|---|
| 549 | def dprint(self, sText):
|
|---|
| 550 | """
|
|---|
| 551 | Debug output.
|
|---|
| 552 | """
|
|---|
| 553 | if not self._fnDPrint:
|
|---|
| 554 | return False;
|
|---|
| 555 | self._fnDPrint(sText);
|
|---|
| 556 | return True;
|
|---|
| 557 |
|
|---|
| 558 | def debugHtmlReport(self, tsStart = 0):
|
|---|
| 559 | """
|
|---|
| 560 | Used to get a SQL activity dump as HTML, usually for WuiBase._sDebug.
|
|---|
| 561 | """
|
|---|
| 562 | cNsElapsed = 0;
|
|---|
| 563 | for aEntry in self._aoTraceBack:
|
|---|
| 564 | cNsElapsed += aEntry[2];
|
|---|
| 565 |
|
|---|
| 566 | sDebug = '<h3>SQL Debug Log (total time %s ns):</h3>\n' \
|
|---|
| 567 | '<table class="tmsqltable">\n' \
|
|---|
| 568 | ' <tr>\n' \
|
|---|
| 569 | ' <th>No.</th>\n' \
|
|---|
| 570 | ' <th>Timestamp (ns)</th>\n' \
|
|---|
| 571 | ' <th>Elapsed (ns)</th>\n' \
|
|---|
| 572 | ' <th>Rows Returned</th>\n' \
|
|---|
| 573 | ' <th>Command</th>\n' \
|
|---|
| 574 | ' <th>Caller</th>\n' \
|
|---|
| 575 | ' </tr>\n' \
|
|---|
| 576 | % (utils.formatNumber(cNsElapsed, ' '),);
|
|---|
| 577 |
|
|---|
| 578 | iEntry = 0;
|
|---|
| 579 | for aEntry in self._aoTraceBack:
|
|---|
| 580 | iEntry += 1;
|
|---|
| 581 | sDebug += ' <tr>\n' \
|
|---|
| 582 | ' <td align="right">%s</td>\n' \
|
|---|
| 583 | ' <td align="right">%s</td>\n' \
|
|---|
| 584 | ' <td align="right">%s</td>\n' \
|
|---|
| 585 | ' <td align="right">%s</td>\n' \
|
|---|
| 586 | ' <td><pre>%s</pre></td>\n' \
|
|---|
| 587 | ' <td>%s</td>\n' \
|
|---|
| 588 | ' </tr>\n' \
|
|---|
| 589 | % (iEntry,
|
|---|
| 590 | utils.formatNumber(aEntry[0] - tsStart, ' '),
|
|---|
| 591 | utils.formatNumber(aEntry[2], ' '),
|
|---|
| 592 | utils.formatNumber(aEntry[3], ' '),
|
|---|
| 593 | webutils.escapeElem(aEntry[1]),
|
|---|
| 594 | webutils.escapeElem(aEntry[4]),
|
|---|
| 595 | );
|
|---|
| 596 | if aEntry[5] is not None:
|
|---|
| 597 | sDebug += ' <tr>\n' \
|
|---|
| 598 | ' <td colspan="6"><pre style="white-space: pre-wrap;">%s</pre></td>\n' \
|
|---|
| 599 | ' </tr>\n' \
|
|---|
| 600 | % (webutils.escapeElem('\n'.join([aoRow[0] for aoRow in aEntry[5]])),);
|
|---|
| 601 |
|
|---|
| 602 | sDebug += '</table>';
|
|---|
| 603 | return sDebug;
|
|---|
| 604 |
|
|---|
| 605 | def debugTextReport(self, tsStart = 0):
|
|---|
| 606 | """
|
|---|
| 607 | Used to get a SQL activity dump as text.
|
|---|
| 608 | """
|
|---|
| 609 | cNsElapsed = 0;
|
|---|
| 610 | for aEntry in self._aoTraceBack:
|
|---|
| 611 | cNsElapsed += aEntry[2];
|
|---|
| 612 |
|
|---|
| 613 | sHdr = 'SQL Debug Log (total time %s ns)' % (utils.formatNumber(cNsElapsed),);
|
|---|
| 614 | sDebug = sHdr + '\n' + '-' * len(sHdr) + '\n';
|
|---|
| 615 |
|
|---|
| 616 | iEntry = 0;
|
|---|
| 617 | for aEntry in self._aoTraceBack:
|
|---|
| 618 | iEntry += 1;
|
|---|
| 619 | sHdr = 'Query #%s Timestamp: %s ns Elapsed: %s ns Rows: %s Caller: %s' \
|
|---|
| 620 | % ( iEntry,
|
|---|
| 621 | utils.formatNumber(aEntry[0] - tsStart),
|
|---|
| 622 | utils.formatNumber(aEntry[2]),
|
|---|
| 623 | utils.formatNumber(aEntry[3]),
|
|---|
| 624 | aEntry[4], );
|
|---|
| 625 | sDebug += '\n' + sHdr + '\n' + '-' * len(sHdr) + '\n';
|
|---|
| 626 |
|
|---|
| 627 | sDebug += aEntry[1];
|
|---|
| 628 | if sDebug[-1] != '\n':
|
|---|
| 629 | sDebug += '\n';
|
|---|
| 630 |
|
|---|
| 631 | if aEntry[5] is not None:
|
|---|
| 632 | sDebug += 'Explain:\n' \
|
|---|
| 633 | ' %s\n' \
|
|---|
| 634 | % ( '\n'.join([aoRow[0] for aoRow in aEntry[5]]),);
|
|---|
| 635 |
|
|---|
| 636 | return sDebug;
|
|---|
| 637 |
|
|---|
| 638 | def debugInfoCallback(self, oGlue, fHtml):
|
|---|
| 639 | """ Called back by the glue code on error. """
|
|---|
| 640 | oGlue.write('\n');
|
|---|
| 641 | if not fHtml: oGlue.write(self.debugTextReport());
|
|---|
| 642 | else: oGlue.write(self.debugHtmlReport());
|
|---|
| 643 | oGlue.write('\n');
|
|---|
| 644 | return True;
|
|---|
| 645 |
|
|---|
| 646 | def debugEnableExplain(self):
|
|---|
| 647 | """ Enabled explain. """
|
|---|
| 648 | if self._oExplainConn is None:
|
|---|
| 649 | dArgs = \
|
|---|
| 650 | { \
|
|---|
| 651 | 'database': config.g_ksDatabaseName,
|
|---|
| 652 | 'user': config.g_ksDatabaseUser,
|
|---|
| 653 | 'password': config.g_ksDatabasePassword,
|
|---|
| 654 | # 'application_name': sAppName, - Darn stale debian! :/
|
|---|
| 655 | };
|
|---|
| 656 | if config.g_ksDatabaseAddress is not None:
|
|---|
| 657 | dArgs['host'] = config.g_ksDatabaseAddress;
|
|---|
| 658 | if config.g_ksDatabasePort is not None:
|
|---|
| 659 | dArgs['port'] = config.g_ksDatabasePort;
|
|---|
| 660 | self._oExplainConn = psycopg2.connect(**dArgs); # pylint: disable=W0142
|
|---|
| 661 | self._oExplainCursor = self._oExplainConn.cursor();
|
|---|
| 662 | return True;
|
|---|
| 663 |
|
|---|
| 664 | def debugDisableExplain(self):
|
|---|
| 665 | """ Disables explain. """
|
|---|
| 666 | self._oExplainCursor = None;
|
|---|
| 667 | self._oExplainConn = None
|
|---|
| 668 | return True;
|
|---|
| 669 |
|
|---|
| 670 | def debugIsExplainEnabled(self):
|
|---|
| 671 | """ Check if explaining of SQL statements is enabled. """
|
|---|
| 672 | return self._oExplainConn is not None;
|
|---|
| 673 |
|
|---|
