trac.db.api

1 # -*- coding: utf-8 -*- 2 # 3 # Copyright (C) 2005-2020 Edgewall Software 4 # Copyright (C) 2005 Christopher Lenz <cmlenz@gmx.de> 5 # All rights reserved. 6 # 7 # This software is licensed as described in the file COPYING, which 8 # you should have received as part of this distribution. The terms 9 # are also available at https://trac.edgewall.org/wiki/TracLicense. 10 # 11 # This software consists of voluntary contributions made by many 12 # individuals. For the exact contribution history, see the revision 13 # history and logs, available at https://trac.edgewall.org/log/. 14 # 15 # Author: Christopher Lenz <cmlenz@gmx.de> 16 17 import importlib 18 import os 19 import time 20 import urllib 21 from abc import ABCMeta, abstractmethod 22 23 from trac import db_default 24 from trac.api import IEnvironmentSetupParticipant, ISystemInfoProvider 25 from trac.config import BoolOption, ConfigurationError, IntOption, Option 26 from trac.core import * 27 from trac.db.pool import ConnectionPool 28 from trac.db.schema import Table 29 from trac.db.util import ConnectionWrapper 30 from trac.util.concurrency import ThreadLocal 31 from trac.util.html import tag 32 from trac.util.text import unicode_passwd 33 from trac.util.translation import _, tag_

34 35 36 -class DbContextManager(object):

37 """Database Context Manager 38 39 The outermost `DbContextManager` will close the connection. 40 """ 41 42 db = None 43

44 - def __init__(self, env):

45 self.dbmgr = DatabaseManager(env)

46

47 - def execute(self, query, params=None):

48 """Shortcut for directly executing a query.""" 49 with self as db: 50 return db.execute(query, params)

51 52 __call__ = execute 53

54 - def executemany(self, query, params=None):

55 """Shortcut for directly calling "executemany" on a query.""" 56 with self as db: 57 return db.executemany(query, params)

58

59 60 -class TransactionContextManager(DbContextManager):

61 """Transactioned Database Context Manager for retrieving a 62 `~trac.db.util.ConnectionWrapper`. 63 64 The outermost such context manager will perform a commit upon 65 normal exit or a rollback after an exception. 66 """ 67

68 - def __enter__(self):

69 db = self.dbmgr._transaction_local.wdb # outermost writable db 70 if not db: 71 db = self.dbmgr._transaction_local.rdb # reuse wrapped connection 72 if db: 73 db = ConnectionWrapper(db.cnx, db.log) 74 else: 75 db = self.dbmgr.get_connection() 76 self.dbmgr._transaction_local.wdb = self.db = db 77 return db

78

79 - def __exit__(self, et, ev, tb):

80 if self.db: 81 self.dbmgr._transaction_local.wdb = None 82 if et is None: 83 self.db.commit() 84 else: 85 self.db.rollback() 86 if not self.dbmgr._transaction_local.rdb: 87 self.db.close()

88

89 90 -class QueryContextManager(DbContextManager):

91 """Database Context Manager for retrieving a read-only 92 `~trac.db.util.ConnectionWrapper`. 93 """ 94

95 - def __enter__(self):

96 db = self.dbmgr._transaction_local.rdb # outermost readonly db 97 if not db: 98 db = self.dbmgr._transaction_local.wdb # reuse wrapped connection 99 if db: 100 db = ConnectionWrapper(db.cnx, db.log, readonly=True) 101 else: 102 db = self.dbmgr.get_connection(readonly=True) 103 self.dbmgr._transaction_local.rdb = self.db = db 104 return db

105

106 - def __exit__(self, et, ev, tb):

107 if self.db: 108 self.dbmgr._transaction_local.rdb = None 109 if not self.dbmgr._transaction_local.wdb: 110 self.db.close()

111

112 113 -class ConnectionBase(object):

114 """Abstract base class for database connection classes.""" 115 116 __metaclass__ = ABCMeta 117 118 @abstractmethod

119 - def cast(self, column, type):

120 """Returns a clause casting `column` as `type`.""" 121 pass

122 123 @abstractmethod

124 - def concat(self, *args):

125 """Returns a clause concatenating the sequence `args`.""" 126 pass

127 128 @abstractmethod

129 - def drop_column(self, table, column):

130 """Drops the `column` from `table`.""" 131 pass

132 133 @abstractmethod

134 - def drop_table(self, table):

135 """Drops the `table`.""" 136 pass

137 138 @abstractmethod

139 - def get_column_names(self, table):

140 """Returns the list of the column names in `table`.""" 141 pass

142 143 @abstractmethod

144 - def get_last_id(self, cursor, table, column='id'):

145 """Returns the current value of the primary key sequence for `table`. 146 The `column` of the primary key may be specified, which defaults 147 to `id`.""" 148 pass

149 150 @abstractmethod

151 - def get_sequence_names(self):

152 """Returns a list of the sequence names.""" 153 pass

154 155 @abstractmethod

156 - def get_table_names(self):

157 """Returns a list of the table names.""" 158 pass

159 160 @abstractmethod

161 - def has_table(self, table):

162 """Returns whether the table exists.""" 163 pass

164 165 @abstractmethod

166 - def like(self):

167 """Returns a case-insensitive `LIKE` clause.""" 168 pass

169 170 @abstractmethod

171 - def like_escape(self, text):

172 """Returns `text` escaped for use in a `LIKE` clause.""" 173 pass

174 175 @abstractmethod

176 - def prefix_match(self):

177 """Return a case sensitive prefix-matching operator.""" 178 pass

179 180 @abstractmethod

181 - def prefix_match_value(self, prefix):

182 """Return a value for case sensitive prefix-matching operator.""" 183 pass

184 185 @abstractmethod

186 - def quote(self, identifier):

187 """Returns the quoted `identifier`.""" 188 pass

189 190 @abstractmethod

191 - def reset_tables(self):

192 """Deletes all data from the tables and resets autoincrement indexes. 193 194 :return: list of names of the tables that were reset. 195 """ 196 pass

197 198 @abstractmethod

199 - def update_sequence(self, cursor, table, column='id'):

200 """Updates the current value of the primary key sequence for `table`. 201 The `column` of the primary key may be specified, which defaults 202 to `id`.""" 203 pass

204

205 206 -class IDatabaseConnector(Interface):

207 """Extension point interface for components that support the 208 connection to relational databases. 209 """ 210

211 - def get_supported_schemes():

212 """Return the connection URL schemes supported by the 213 connector, and their relative priorities as an iterable of 214 `(scheme, priority)` tuples. 215 216 If `priority` is a negative number, this is indicative of an 217 error condition with the connector. An error message should be 218 attached to the `error` attribute of the connector. 219 """

220

221 - def get_connection(path, log=None, **kwargs):

222 """Create a new connection to the database."""

223

224 - def get_exceptions():

225 """Return an object (typically a module) containing all the 226 backend-specific exception types as attributes, named 227 according to the Python Database API 228 (http://www.python.org/dev/peps/pep-0249/). 229 """

230

231 - def init_db(path, schema=None, log=None, **kwargs):

232 """Initialize the database."""

233

234 - def destroy_db(self, path, log=None, **kwargs):

235 """Destroy the database."""

236

237 - def db_exists(self, path, log=None, **kwargs):

238 """Return `True` if the database exists."""

239

240 - def to_sql(table):

241 """Return the DDL statements necessary to create the specified 242 table, including indices."""

243

244 - def backup(dest):

245 """Backup the database to a location defined by 246 trac.backup_dir"""

247

248 - def get_system_info():

249 """Yield a sequence of `(name, version)` tuples describing the 250 name and version information of external packages used by the 251 connector. 252 """

253

254 255 -class DatabaseManager(Component):

256 """Component used to manage the `IDatabaseConnector` implementations.""" 257 258 implements(IEnvironmentSetupParticipant, ISystemInfoProvider) 259 260 connectors = ExtensionPoint(IDatabaseConnector) 261 262 connection_uri = Option('trac', 'database', 'sqlite:db/trac.db', 263 """Database connection 264 [wiki:TracEnvironment#DatabaseConnectionStrings string] for this 265 project""") 266 267 backup_dir = Option('trac', 'backup_dir', 'db', 268 """Database backup location""") 269 270 timeout = IntOption('trac', 'timeout', '20', 271 """Timeout value for database connection, in seconds. 272 Use '0' to specify ''no timeout''.""") 273 274 debug_sql = BoolOption('trac', 'debug_sql', False, 275 """Show the SQL queries in the Trac log, at DEBUG level. 276 """) 277

278 - def __init__(self):

279 self._cnx_pool = None 280 self._transaction_local = ThreadLocal(wdb=None, rdb=None)

281

282 - def init_db(self):

283 connector, args = self.get_connector() 284 args['schema'] = db_default.schema 285 connector.init_db(**args) 286 version = db_default.db_version 287 self.set_database_version(version, 'initial_database_version') 288 self.set_database_version(version)

289

290 - def insert_default_data(self):

291 self.insert_into_tables(db_default.get_data)

292

293 - def destroy_db(self):

294 connector, args = self.get_connector() 295 # Connections to on-disk db must be closed before deleting it. 296 self.shutdown() 297 connector.destroy_db(**args)

298

299 - def db_exists(self):

300 connector, args = self.get_connector() 301 return connector.db_exists(**args)

302

303 - def create_tables(self, schema):

304 """Create the specified tables. 305 306 :param schema: an iterable of table objects. 307 308 :since: version 1.0.2 309 """ 310 connector = self.get_connector()[0] 311 with self.env.db_transaction as db: 312 for table in schema: 313 for sql in connector.to_sql(table): 314 db(sql)

315

316 - def drop_columns(self, table, columns):

317 """Drops the specified columns from table. 318 319 :since: version 1.2 320 """ 321 table_name = table.name if isinstance(table, Table) else table 322 with self.env.db_transaction as db: 323 if not db.has_table(table_name): 324 raise self.env.db_exc.OperationalError('Table %s not found' % 325 db.quote(table_name)) 326 for col in columns: 327 db.drop_column(table_name, col)

328

329 - def drop_tables(self, schema):

330 """Drop the specified tables. 331 332 :param schema: an iterable of `Table` objects or table names. 333 334 :since: version 1.0.2 335 """ 336 with self.env.db_transaction as db: 337 for table in schema: 338 table_name = table.name if isinstance(table, Table) else table 339 db.drop_table(table_name)

340

341 - def insert_into_tables(self, data_or_callable):

342 """Insert data into existing tables. 343 344 :param data_or_callable: Nested tuples of table names, column names 345 and row data:: 346 347 (table1, 348 (column1, column2), 349 ((row1col1, row1col2), 350 (row2col1, row2col2)), 351 table2, ...) 352 353 or a callable that takes a single parameter 354 `db` and returns the aforementioned nested 355 tuple. 356 :since: version 1.1.3 357 """ 358 with self.env.db_transaction as db: 359 data = data_or_callable(db) if callable(data_or_callable) \ 360 else data_or_callable 361 for table, cols, vals in data: 362 db.executemany("INSERT INTO %s (%s) VALUES (%s)" 363 % (db.quote(table), ','.join(cols), 364 ','.join(['%s'] * len(cols))), vals)

365

366 - def reset_tables(self):

367 """Deletes all data from the tables and resets autoincrement indexes. 368 369 :return: list of names of the tables that were reset. 370 371 :since: version 1.1.3 372 """ 373 with self.env.db_transaction as db: 374 return db.reset_tables()

375

376 - def upgrade_tables(self, new_schema):

377 """Upgrade table schema to `new_schema`, preserving data in 378 columns that exist in the current schema and `new_schema`. 379 380 :param new_schema: tuple or list of `Table` objects 381 382 :since: version 1.2 383 """ 384 with self.env.db_transaction as db: 385 cursor = db.cursor() 386 for new_table in new_schema: 387 temp_table_name = new_table.name + '_old' 388 has_table = self.has_table(new_table) 389 if has_table: 390 old_column_names = set(self.get_column_names(new_table)) 391 new_column_names = {col.name for col in new_table.columns} 392 column_names = old_column_names & new_column_names 393 if column_names: 394 cols_to_copy = ','.join(db.quote(name) 395 for name in column_names) 396 cursor.execute(""" 397 CREATE TEMPORARY TABLE %s AS SELECT * FROM %s 398 """ % (db.quote(temp_table_name), 399 db.quote(new_table.name))) 400 self.drop_tables((new_table,)) 401 self.create_tables((new_table,)) 402 if has_table and column_names: 403 cursor.execute(""" 404 INSERT INTO %s (%s) SELECT %s FROM %s 405 """ % (db.quote(new_table.name), cols_to_copy, 406 cols_to_copy, db.quote(temp_table_name))) 407 for col in new_table.columns: 408 if col.auto_increment: 409 db.update_sequence(cursor, new_table.name, 410 col.name) 411 self.drop_tables((temp_table_name,))

412

413 - def get_connection(self, readonly=False):

414 """Get a database connection from the pool. 415 416 If `readonly` is `True`, the returned connection will purposely 417 lack the `rollback` and `commit` methods. 418 """ 419 if not self._cnx_pool: 420 connector, args = self.get_connector() 421 self._cnx_pool = ConnectionPool(5, connector, **args) 422 db = self._cnx_pool.get_cnx(self.timeout or None) 423 if readonly: 424 db = ConnectionWrapper(db, readonly=True) 425 return db

426

427 - def get_database_version(self, name='database_version'):

428 """Returns the database version from the SYSTEM table as an int, 429 or `False` if the entry is not found. 430 431 :param name: The name of the entry that contains the database version 432 in the SYSTEM table. Defaults to `database_version`, 433 which contains the database version for Trac. 434 """ 435 with self.env.db_query as db: 436 for value, in db(""" 437 SELECT value FROM {0} WHERE name=%s 438 """.format(db.quote('system')), (name,)): 439 return int(value) 440 else: 441 return False

442

443 - def get_exceptions(self):

444 return self.get_connector()[0].get_exceptions()

445

446 - def get_sequence_names(self):

447 """Returns a list of the sequence names. 448 449 :since: 1.3.2 450 """ 451 with self.env.db_query as db: 452 return db.get_sequence_names()

453

454 - def get_table_names(self):

455 """Returns a list of the table names. 456 457 :since: 1.1.6 458 """ 459 with self.env.db_query as db: 460 return db.get_table_names()

461

462 - def get_column_names(self, table):

463 """Returns a list of the column names for `table`. 464 465 :param table: a `Table` object or table name. 466 467 :since: 1.2 468 """ 469 table_name = table.name if isinstance(table, Table) else table 470 with self.env.db_query as db: 471 if not db.has_table(table_name): 472 raise self.env.db_exc.OperationalError('Table %s not found' % 473 db.quote(table_name)) 474 return db.get_column_names(table_name)

475

476 - def has_table(self, table):

477 """Returns whether the table exists.""" 478 table_name = table.name if isinstance(table, Table) else table 479 with self.env.db_query as db: 480 return db.has_table(table_name)

481

482 - def set_database_version(self, version, name='database_version'):

483 """Sets the database version in the SYSTEM table. 484 485 :param version: an integer database version. 486 :param name: The name of the entry that contains the database version 487 in the SYSTEM table. Defaults to `database_version`, 488 which contains the database version for Trac. 489 """ 490 current_database_version = self.get_database_version(name) 491 if current_database_version is False: 492 with self.env.db_transaction as db: 493 db(""" 494 INSERT INTO {0} (name, value) VALUES (%s, %s) 495 """.format(db.quote('system')), (name, version)) 496 elif version != self.get_database_version(name): 497 with self.env.db_transaction as db: 498 db(""" 499 UPDATE {0} SET value=%s WHERE name=%s 500 """.format(db.quote('system')), (version, name)) 501 self.log.info("Upgraded %s from %d to %d", 502 name, current_database_version, version)

503

504 - def needs_upgrade(self, version, name='database_version'):

505 """Checks the database version to determine if an upgrade is needed. 506 507 :param version: the expected integer database version. 508 :param name: the name of the entry in the SYSTEM table that contains 509 the database version. Defaults to `database_version`, 510 which contains the database version for Trac. 511 512 :return: `True` if the stored version is less than the expected 513 version, `False` if it is equal to the expected version. 514 :raises TracError: if the stored version is greater than the expected 515 version. 516 """ 517 dbver = self.get_database_version(name) 518 if dbver == version: 519 return False 520 elif dbver > version: 521 raise TracError(_("Need to downgrade %(name)s.", name=name)) 522 self.log.info("Need to upgrade %s from %d to %d", 523 name, dbver, version) 524 return True

525

526 - def upgrade(self, version, name='database_version', pkg='trac.upgrades'):

527 """Invokes `do_upgrade(env, version, cursor)` in module 528 `"%s/db%i.py" % (pkg, version)`, for each required version upgrade. 529 530 :param version: the expected integer database version. 531 :param name: the name of the entry in the SYSTEM table that contains 532 the database version. Defaults to `database_version`, 533 which contains the database version for Trac. 534 :param pkg: the package containing the upgrade modules. 535 536 :raises TracError: if the package or module doesn't exist. 537 """ 538 dbver = self.get_database_version(name) 539 for i in xrange(dbver + 1, version + 1): 540 module = '%s.db%i' % (pkg, i) 541 try: 542 upgrader = importlib.import_module(module) 543 except ImportError: 544 raise TracError(_("No upgrade module %(module)s.py", 545 module=module)) 546 with self.env.db_transaction as db: 547 cursor = db.cursor() 548 upgrader.do_upgrade(self.env, i, cursor) 549 self.set_database_version(i, name)

550

551 - def shutdown(self, tid=None):

552 if self._cnx_pool: 553 self._cnx_pool.shutdown(tid) 554 if not tid: 555 self._cnx_pool = None

556

557 - def backup(self, dest=None):

558 """Save a backup of the database. 559 560 :param dest: base filename to write to. 561 562 Returns the file actually written. 563 """ 564 connector, args = self.get_connector() 565 if not dest: 566 backup_dir = self.backup_dir 567 if not os.path.isabs(backup_dir): 568 backup_dir = os.path.join(self.env.path, backup_dir) 569 db_str = self.config.get('trac', 'database') 570 db_name, db_path = db_str.split(":", 1) 571 dest_name = '%s.%i.%d.bak' % (db_name, self.env.database_version, 572 int(time.time())) 573 dest = os.path.join(backup_dir, dest_name) 574 else: 575 backup_dir = os.path.dirname(dest) 576 if not os.path.exists(backup_dir): 577 os.makedirs(backup_dir) 578 return connector.backup(dest)

579

580 - def get_connector(self):

581 scheme, args = parse_connection_uri(self.connection_uri) 582 candidates = [ 583 (priority, connector) 584 for connector in self.connectors 585 for scheme_, priority in connector.get_supported_schemes() 586 if scheme_ == scheme 587 ] 588 if not candidates: 589 raise TracError(_('Unsupported database type "%(scheme)s"', 590 scheme=scheme)) 591 priority, connector = max(candidates) 592 if priority < 0: 593 raise TracError(connector.error) 594 595 if scheme == 'sqlite': 596 if args['path'] == ':memory:': 597 # Special case for SQLite in-memory database, always get 598 # the /same/ connection over 599 pass 600 elif not os.path.isabs(args['path']): 601 # Special case for SQLite to support a path relative to the 602 # environment directory 603 args['path'] = os.path.join(self.env.path, 604 args['path'].lstrip('/')) 605 606 if self.debug_sql: 607 args['log'] = self.log 608 return connector, args

609 610 # IEnvironmentSetupParticipant methods 611

612 - def environment_created(self):

613 pass

614

615 - def environment_needs_upgrade(self):

616 return self.needs_upgrade(db_default.db_version)

617

618 - def upgrade_environment(self):

619 self.upgrade(db_default.db_version)

620 621 # ISystemInfoProvider methods 622

623 - def get_system_info(self):

624 connector = self.get_connector()[0] 625 for info in connector.get_system_info(): 626 yield info

627

628 629 -def get_column_names(cursor):

630 """Retrieve column names from a cursor, if possible.""" 631 return [unicode(d[0], 'utf-8') if isinstance(d[0], str) else d[0] 632 for d in cursor.description] if cursor.description else []

633

634 635 -def parse_connection_uri(db_str):

636 """Parse the database connection string. 637 638 The database connection string for an environment is specified through 639 the `database` option in the `[trac]` section of trac.ini. 640 641 :return: a tuple containing the scheme and a dictionary of attributes: 642 `user`, `password`, `host`, `port`, `path`, `params`. 643 :since: 1.1.3 644 """ 645 if not db_str: 646 section = tag.a("[trac]", 647 title=_("TracIni documentation"), 648 class_='trac-target-new', 649 href='https://trac.edgewall.org/wiki/TracIni' 650 '#trac-section') 651 raise ConfigurationError( 652 tag_("Database connection string is empty. Set the %(option)s " 653 "configuration option in the %(section)s section of " 654 "trac.ini. Please refer to the %(doc)s for help.", 655 option=tag.code("database"), section=section, 656 doc=_doc_db_str())) 657 658 try: 659 scheme, rest = db_str.split(':', 1) 660 except ValueError: 661 raise _invalid_db_str(db_str) 662 663 if not rest.startswith('/'): 664 if scheme == 'sqlite' and rest: 665 # Support for relative and in-memory SQLite connection strings 666 host = None 667 path = rest 668 else: 669 raise _invalid_db_str(db_str) 670 else: 671 if not rest.startswith('//'): 672 host = None 673 rest = rest[1:] 674 elif rest.startswith('///'): 675 host = None 676 rest = rest[3:] 677 else: 678 rest = rest[2:] 679 if '/' in rest: 680 host, rest = rest.split('/', 1) 681 else: 682 host = rest 683 rest = '' 684 path = None 685 686 if host and '@' in host: 687 user, host = host.split('@', 1) 688 if ':' in user: 689 user, password = user.split(':', 1) 690 else: 691 password = None 692 if user: 693 user = urllib.unquote(user) 694 if password: 695 password = unicode_passwd(urllib.unquote(password)) 696 else: 697 user = password = None 698 699 if host and ':' in host: 700 host, port = host.split(':', 1) 701 try: 702 port = int(port) 703 except ValueError: 704 raise _invalid_db_str(db_str) 705 else: 706 port = None 707 708 if not path: 709 path = '/' + rest 710 if os.name == 'nt': 711 # Support local paths containing drive letters on Win32 712 if len(rest) > 1 and rest[1] == '|': 713 path = "%s:%s" % (rest[0], rest[2:]) 714 715 params = {} 716 if '?' in path: 717 path, qs = path.split('?', 1) 718 qs = qs.split('&') 719 for param in qs: 720 try: 721 name, value = param.split('=', 1) 722 except ValueError: 723 raise _invalid_db_str(db_str) 724 value = urllib.unquote(value) 725 params[name] = value 726 727 args = zip(('user', 'password', 'host', 'port', 'path', 'params'), 728 (user, password, host, port, path, params)) 729 return scheme, {key: value for key, value in args if value}

730

731 732 -def _invalid_db_str(db_str):

733 return ConfigurationError( 734 tag_("Invalid format %(db_str)s for the database connection string. " 735 "Please refer to the %(doc)s for help.", 736 db_str=tag.code(db_str), doc=_doc_db_str()))

737

738 739 -def _doc_db_str():

740 return tag.a(_("documentation"), 741 title=_("Database Connection Strings documentation"), 742 class_='trac-target-new', 743 href='https://trac.edgewall.org/wiki/' 744 'TracIni#DatabaseConnectionStrings')

745

Source Code for Module trac.db.api