def add_new_id_column(table): id_column = Column('id', type='int', auto_increment=True) if using_sqlite_backend(self.env) or using_mysql_backend(self.env): # sqlite and mysql don't support multiple auto increment columns id_column.auto_increment = False table.columns.append(id_column) table.indices.append(Index(['product', 'id'], unique=True))
from trac.db import Table, Column name = 'personalreports' version = 1 tables = [ Table('personal_reports', key=('id', 'user'))[Column('id'), Column('user'), Column('private'), Column('title'), Column('query'), Column('description'), ], ]
# -*- coding: utf-8 -*- # Copyright (C) 2008-2013 Joao Alexandre de Toledo <*****@*****.**> # # All rights reserved. # # This software is licensed as described in the file COPYING, which # you should have received as part of this distribution. from trac.db import Table, Column, Index, DatabaseManager tables = [ Table('releases', key='id')[Column('id', type='integer', auto_increment=True), Column('version'), Column('description'), Column('author'), Column('creation_date', type='integer'), Column('planned_date', type='integer'), Column('install_date', type='integer')], Table('release_tickets')[Column('release_id', type='integer'), Column('ticket_id', type='integer')], Table('release_signatures')[Column('release_id', type='integer'), Column('signature'), Column('sign_date', type='integer')] ] def do_upgrade(env, cursor): db_connector, _ = DatabaseManager(env)._get_connector() # Create tables
class BuildLog(object): """Represents a build log.""" _schema = [ Table('bitten_log', key='id')[Column('id', auto_increment=True), Column('build', type='int'), Column('step'), Column('generator'), Column('orderno', type='int'), Column('filename'), Index(['build', 'step'])], ] # Message levels DEBUG = 'D' INFO = 'I' WARNING = 'W' ERROR = 'E' UNKNOWN = '' def __init__(self, env, build=None, step=None, generator=None, orderno=None, filename=None): """Initialize a new build log with the specified attributes. To actually create this build log in the database, the `insert` method needs to be called. """ self.env = env self.id = None self.build = build self.step = step self.generator = generator or '' self.orderno = orderno and int(orderno) or 0 self.filename = filename or None self.messages = [] self.logs_dir = env.config.get('bitten', 'logs_dir', 'log/bitten') if not os.path.isabs(self.logs_dir): self.logs_dir = os.path.join(env.path, self.logs_dir) if not os.path.exists(self.logs_dir): os.makedirs(self.logs_dir) exists = property(fget=lambda self: self.id is not None, doc='Whether this build log exists in the database') def get_log_file(self, filename): """Returns the full path to the log file""" if filename != os.path.basename(filename): raise ValueError("Filename may not contain path: %s" % (filename, )) return os.path.join(self.logs_dir, filename) def delete(self, db=None): """Remove the build log from the database.""" assert self.exists, 'Cannot delete a non-existing build log' if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False if self.filename: log_file = self.get_log_file(self.filename) if os.path.exists(log_file): try: self.env.log.debug("Deleting log file: %s" % log_file) os.remove(log_file) except Exception, e: self.env.log.warning("Error removing log file %s: %s" % (log_file, e)) level_file = log_file + '.levels' if os.path.exists(level_file): try: self.env.log.debug("Deleting level file: %s" % level_file) os.remove(level_file) except Exception, e: self.env.log.warning("Error removing level file %s: %s" \ % (level_file, e))
class Build(object): """Representation of a build.""" _schema = [ Table('bitten_build', key='id')[Column('id', auto_increment=True), Column('config'), Column('rev'), Column('rev_time', type='int'), Column('platform', type='int'), Column('slave'), Column('started', type='int'), Column('stopped', type='int'), Column('status', size=1), Index(['config', 'rev', 'platform'], unique=True)], Table('bitten_slave', key=('build', 'propname'))[Column('build', type='int'), Column('propname'), Column('propvalue')] ] # Build status codes PENDING = 'P' IN_PROGRESS = 'I' SUCCESS = 'S' FAILURE = 'F' # Standard slave properties IP_ADDRESS = 'ipnr' MAINTAINER = 'owner' OS_NAME = 'os' OS_FAMILY = 'family' OS_VERSION = 'version' MACHINE = 'machine' PROCESSOR = 'processor' TOKEN = 'token' def __init__(self, env, config=None, rev=None, platform=None, slave=None, started=0, stopped=0, rev_time=0, status=PENDING): """Initialize a new build with the specified attributes. To actually create this build in the database, the `insert` method needs to be called. """ self.env = env self.id = None self.config = config self.rev = rev and str(rev) or None self.platform = platform self.slave = slave self.started = started or 0 self.stopped = stopped or 0 self.rev_time = rev_time self.status = status self.slave_info = {} def __repr__(self): return '<%s %r>' % (type(self).__name__, self.id) exists = property(fget=lambda self: self.id is not None, doc='Whether this build exists in the database') completed = property(fget=lambda self: self.status != Build.IN_PROGRESS, doc='Whether the build has been completed') successful = property(fget=lambda self: self.status == Build.SUCCESS, doc='Whether the build was successful') resource = property( fget=lambda self: Resource('build', '%s/%s' % (self.config, self.id)), doc='Build resource identification') def delete(self, db=None): """Remove the build from the database.""" assert self.exists, 'Cannot delete a non-existing build' if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False for step in list(BuildStep.select(self.env, build=self.id)): step.delete(db=db) # Delete attachments Attachment.delete_all(self.env, 'build', self.resource.id, db) cursor = db.cursor() cursor.execute("DELETE FROM bitten_slave WHERE build=%s", (self.id, )) cursor.execute("DELETE FROM bitten_build WHERE id=%s", (self.id, )) if handle_ta: db.commit() def insert(self, db=None): """Insert a new build into the database.""" assert not self.exists, 'Cannot insert an existing build' if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False assert self.config and self.rev and self.rev_time and self.platform assert self.status in (self.PENDING, self.IN_PROGRESS, self.SUCCESS, self.FAILURE) if not self.slave: assert self.status == self.PENDING cursor = db.cursor() cursor.execute( "INSERT INTO bitten_build (config,rev,rev_time,platform," "slave,started,stopped,status) " "VALUES (%s,%s,%s,%s,%s,%s,%s,%s)", (self.config, self.rev, int( self.rev_time), self.platform, self.slave or '', self.started or 0, self.stopped or 0, self.status)) self.id = db.get_last_id(cursor, 'bitten_build') if self.slave_info: cursor.executemany("INSERT INTO bitten_slave VALUES (%s,%s,%s)", [(self.id, name, value) for name, value in self.slave_info.items()]) if handle_ta: db.commit() def update(self, db=None): """Save changes to an existing build.""" assert self.exists, 'Cannot update a non-existing build' if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False assert self.config and self.rev assert self.status in (self.PENDING, self.IN_PROGRESS, self.SUCCESS, self.FAILURE) if not self.slave: assert self.status == self.PENDING cursor = db.cursor() cursor.execute( "UPDATE bitten_build SET slave=%s,started=%s," "stopped=%s,status=%s WHERE id=%s", (self.slave or '', self.started or 0, self.stopped or 0, self.status, self.id)) cursor.execute("DELETE FROM bitten_slave WHERE build=%s", (self.id, )) if self.slave_info: cursor.executemany("INSERT INTO bitten_slave VALUES (%s,%s,%s)", [(self.id, name, value) for name, value in self.slave_info.items()]) if handle_ta: db.commit() def fetch(cls, env, id, db=None): """Retrieve an existing build from the database by ID.""" if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute( "SELECT config,rev,rev_time,platform,slave,started," "stopped,status FROM bitten_build WHERE id=%s", (id, )) row = cursor.fetchone() if not row: return None build = Build(env, config=row[0], rev=row[1], rev_time=int(row[2]), platform=int(row[3]), slave=row[4], started=row[5] and int(row[5]) or 0, stopped=row[6] and int(row[6]) or 0, status=row[7]) build.id = int(id) cursor.execute( "SELECT propname,propvalue FROM bitten_slave " "WHERE build=%s", (id, )) for propname, propvalue in cursor: build.slave_info[propname] = propvalue return build fetch = classmethod(fetch) def select(cls, env, config=None, rev=None, platform=None, slave=None, status=None, db=None, min_rev_time=None, max_rev_time=None): """Retrieve existing builds from the database that match the specified criteria. """ if not db: db = env.get_db_cnx() where_clauses = [] if config is not None: where_clauses.append(("config=%s", config)) if rev is not None: where_clauses.append(("rev=%s", str(rev))) if platform is not None: where_clauses.append(("platform=%s", platform)) if slave is not None: where_clauses.append(("slave=%s", slave)) if status is not None: where_clauses.append(("status=%s", status)) if min_rev_time is not None: where_clauses.append(("rev_time>=%s", min_rev_time)) if max_rev_time is not None: where_clauses.append(("rev_time<=%s", max_rev_time)) if where_clauses: where = "WHERE " + " AND ".join([wc[0] for wc in where_clauses]) else: where = "" cursor = db.cursor() cursor.execute( "SELECT id FROM bitten_build %s " "ORDER BY rev_time DESC,config,slave" % where, [wc[1] for wc in where_clauses]) for (id, ) in cursor: yield Build.fetch(env, id) select = classmethod(select)
class TargetPlatform(object): """Target platform for a build configuration.""" _schema = [ Table('bitten_platform', key='id')[Column('id', auto_increment=True), Column('config'), Column('name')], Table('bitten_rule', key=('id', 'propname'))[Column('id', type='int'), Column('propname'), Column('pattern'), Column('orderno', type='int')] ] def __init__(self, env, config=None, name=None): """Initialize a new target platform with the specified attributes. To actually create this platform in the database, the `insert` method needs to be called. """ self.env = env self.id = None self.config = config self.name = name self.rules = [] def __repr__(self): return '<%s %r>' % (type(self).__name__, self.id) exists = property( fget=lambda self: self.id is not None, doc='Whether this target platform exists in the database') def delete(self, db=None): """Remove the target platform from the database.""" if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False for build in Build.select(self.env, platform=self.id, status=Build.PENDING, db=db): build.delete() cursor = db.cursor() cursor.execute("DELETE FROM bitten_rule WHERE id=%s", (self.id, )) cursor.execute("DELETE FROM bitten_platform WHERE id=%s", (self.id, )) if handle_ta: db.commit() def insert(self, db=None): """Insert a new target platform into the database.""" if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False assert not self.exists, 'Cannot insert existing target platform' assert self.config, 'Target platform needs to be associated with a ' \ 'configuration' assert self.name, 'Target platform requires a name' cursor = db.cursor() cursor.execute( "INSERT INTO bitten_platform (config,name) " "VALUES (%s,%s)", (self.config, self.name)) self.id = db.get_last_id(cursor, 'bitten_platform') if self.rules: cursor.executemany( "INSERT INTO bitten_rule VALUES (%s,%s,%s,%s)", [(self.id, propname, pattern, idx) for idx, (propname, pattern) in enumerate(self.rules)]) if handle_ta: db.commit() def update(self, db=None): """Save changes to an existing target platform.""" assert self.exists, 'Cannot update a non-existing platform' assert self.config, 'Target platform needs to be associated with a ' \ 'configuration' assert self.name, 'Target platform requires a name' if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False cursor = db.cursor() cursor.execute("UPDATE bitten_platform SET name=%s WHERE id=%s", (self.name, self.id)) cursor.execute("DELETE FROM bitten_rule WHERE id=%s", (self.id, )) if self.rules: cursor.executemany( "INSERT INTO bitten_rule VALUES (%s,%s,%s,%s)", [(self.id, propname, pattern, idx) for idx, (propname, pattern) in enumerate(self.rules)]) if handle_ta: db.commit() def fetch(cls, env, id, db=None): """Retrieve an existing target platform from the database by ID.""" if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute( "SELECT config,name FROM bitten_platform " "WHERE id=%s", (id, )) row = cursor.fetchone() if not row: return None platform = TargetPlatform(env, config=row[0], name=row[1]) platform.id = id cursor.execute( "SELECT propname,pattern FROM bitten_rule " "WHERE id=%s ORDER BY orderno", (id, )) for propname, pattern in cursor: platform.rules.append((propname, pattern)) return platform fetch = classmethod(fetch) def select(cls, env, config=None, db=None): """Retrieve existing target platforms from the database that match the specified criteria. """ if not db: db = env.get_db_cnx() where_clauses = [] if config is not None: where_clauses.append(("config=%s", config)) if where_clauses: where = "WHERE " + " AND ".join([wc[0] for wc in where_clauses]) else: where = "" cursor = db.cursor() cursor.execute( "SELECT id FROM bitten_platform %s ORDER BY name" % where, [wc[1] for wc in where_clauses]) for (id, ) in cursor: yield TargetPlatform.fetch(env, id) select = classmethod(select)
def test_upgrade_to_schema_v5(self): # Schema from r9210 - 29-Sep-2010 for announcer-0.12.1 by R. Corsaro. schema = [ Table('subscription', key='id')[Column('id', auto_increment=True), Column('time', type='int64'), Column('changetime', type='int64'), Column('class'), Column('sid'), Column('authenticated', type='int'), Column('distributor'), Column('format'), Column('priority', type='int'), Column('adverb')], Table('subscription_attribute', key='id')[Column('id', auto_increment=True), Column('sid'), Column('class'), Column('realm'), Column('target')] ] self._schema_init(schema) # Populate tables with test data. cursor = self.db.cursor() cursor.executemany( """ INSERT INTO session (sid,authenticated,last_visit) VALUES (%s,%s,%s) """, (('somebody', '0', '0'), ('user', '1', '0'))) cursor.executemany( """ INSERT INTO subscription_attribute (sid,class,realm,target) VALUES (%s,%s,%s,%s) """, (('somebody', 'GeneralWikiSubscriber', 'wiki', '*'), ('somebody', 'UserChangeSubscriber', 'wiki', 'created'), ('user', 'GeneralWikiSubscriber', 'wiki', 'TracWiki'))) self.assertEquals(4, self.an_sys.get_schema_version(self.db)) target = 5 db_default.schema_version = target self.assertTrue(self.an_sys.environment_needs_upgrade(self.db)) # From r9235 - 01-Oct-2010 for announcer-0.12.1 by Robert Corsaro. # + 'subscription_attribute.authenticated' self.an_sys.upgrade_environment(self.db) self.assertEquals(target, self.an_sys.get_schema_version(self.db)) self._verify_version_unregistered() cursor = self.db.cursor() cursor.execute("SELECT * FROM subscription_attribute") columns = [col[0] for col in self._get_cursor_description(cursor)] self.assertTrue('name' not in columns) self.assertTrue('value' not in columns) self.assertEquals( ['id', 'sid', 'authenticated', 'class', 'realm', 'target'], columns) # Check authenticated attribute for session IDs. subscriptions = [(row[1], (row[2])) for row in cursor] for sub in subscriptions: self.assertTrue((sub[0] == 'user' and sub[1] == 1) or sub[1] == 0)
def test_upgrade_to_schema_v2(self): # The initial db schema from r3015 - 10-Jan-2008 by Stephen Hansen. schema = [ Table('subscriptions', key='id')[Column('id', auto_increment=True), Column('sid'), Column('enabled', type='int'), Column('managed', type='int'), Column('realm'), Column('category'), Column('rule'), Column('destination'), Column('format'), Index(['id']), Index(['realm', 'category', 'enabled']), ] ] self._schema_init(schema) # Populate tables with test data. cursor = self.db.cursor() cursor.executemany( """ INSERT INTO session (sid,authenticated,last_visit) VALUES (%s,%s,%s) """, (('somebody', '0', '0'), ('user', '1', '0'))) cursor.executemany( """ INSERT INTO session_attribute (sid,authenticated,name,value) VALUES (%s,1,%s,%s) """, (('user', 'announcer_email_format_ticket', 'text/html'), ('user', 'announcer_specified_email', ''))) cursor.executemany( """ INSERT INTO subscriptions (sid,enabled,managed, realm,category,rule,destination,format) VALUES (%s,%s,0,%s,%s,%s,%s,%s) """, (('somebody', 1, 'ticket', 'changed', '1', '1', 'email'), ('user', 1, 'ticket', 'attachment added', '1', '1', 'email'))) self.assertEquals(1, self.an_sys.get_schema_version(self.db)) target = 2 db_default.schema_version = target self.assertTrue(self.an_sys.environment_needs_upgrade(self.db)) # Change from r3047 - 13-Jan-2008 for announcer-0.2 by Stephen Hansen. # - 'subscriptions.destination', 'subscriptions.format' # + 'subscriptions.authenticated', 'subscriptions.transport' # 'subscriptions.managed' type='int' --> (default == char) self.an_sys.upgrade_environment(self.db) self.assertEquals(target, self.an_sys.get_schema_version(self.db)) self._verify_version_unregistered() cursor = self.db.cursor() cursor.execute("SELECT * FROM subscriptions") columns = [col[0] for col in self._get_cursor_description(cursor)] self.assertEquals([ 'id', 'sid', 'authenticated', 'enabled', 'managed', 'realm', 'category', 'rule', 'transport' ], columns)
## -*- coding: utf-8 -*- # # This software is licensed as described in the file license.txt, which # you should have received as part of this distribution. from trac.core import * from trac.db import Table, Column, Index from trac.db import DatabaseManager from trac.env import IEnvironmentSetupParticipant schema_version = 1 schema = [ Table('asa_version', key='id')[Column('id', type='int64', auto_increment=True), Column('time', type='int64'), Column('author'), Column('ipnr'), Column('comment'), Column('readonly', type='int'), Index(['id'], unique=True), ], # Workaround for sqlite not supporting multiple column primary keys with an auto-increment. # This table as the sole purpose of getting the auto-increment values Table('asa_artifact_id', key='id') [Column('id', type='int64', auto_increment=True), ], Table('asa_artifact', key=['id', 'version_id' ])[Column('id', type='int64'), Column('version_id', type='int64'), Column('spec'), Column('title_expr'), # the "toString()" of artifacts Index(['id', 'version_id'], unique=True), ],
def db13(self, env, cursor): """Convert state serialization type to be more readable. Migrate to slicker named major tables and associated indexes too. """ table = Table('forms', key='id')[Column('id', auto_increment=True), Column('context'), Column('state'), Column('author'), Column('time', type='int'), Column('keep_history', type='int'), Column('track_fields', type='int'), Index(['context'], unique=True), Index(['author']), Index(['time'])] db_connector, _ = DatabaseManager(env)._get_connector() for stmt in db_connector.to_sql(table): cursor.execute(stmt) forms_columns = ('tracform_id', 'context', 'state', 'updater', 'updated_on', 'keep_history', 'track_fields') forms_columns_new = ('id', 'context', 'state', 'author', 'time', 'keep_history', 'track_fields') sql = 'SELECT ' + ', '.join(forms_columns) + ' FROM tracform_forms' cursor.execute(sql) forms = [] for row in cursor: row = dict(zip(forms_columns_new, row)) forms.append(row) # convert current states serialization for form in forms: state_new = _url_to_json(form.get('state')) if state_new == '{}': form['state'] = form.get('state') else: form['state'] = state_new for form in forms: fields = form.keys() values = form.values() sql = "INSERT INTO forms (" + ", ".join(fields) + \ ") VALUES (" + ", ".join(["%s" for I in xrange(len(fields))]) \ + ")" cursor.execute(sql, values) cursor.execute(""" DROP TABLE tracform_forms """) # migrate history table if env.config.get('trac', 'database').startswith('postgres'): cursor.execute(""" CREATE TABLE forms_history AS SELECT tracform_id AS id, updated_on AS time, updater AS author, old_states AS old_state FROM tracform_history """) else: cursor.execute(""" CREATE TABLE forms_history AS SELECT tracform_id 'id', updated_on 'time', updater 'author', old_states 'old_state' FROM tracform_history """) sql = 'SELECT id,time,old_state FROM forms_history' cursor.execute(sql) history = [] for row in cursor: row = dict(zip(('id', 'time', 'old_state'), row)) history.append(row) # convert historic states serialization for row in history: old_state_new = _url_to_json(row.get('old_state')) if old_state_new == '{}': row['old_state'] = row.get('old_state') else: row['old_state'] = old_state_new for row in history: sql = "UPDATE forms_history SET old_state=%s " + \ "WHERE id=%s AND time=%s" cursor.execute(sql, (row['old_state'], row['id'], row['time'])) cursor.execute(""" CREATE INDEX forms_history_id_idx ON forms_history(id) """) # 'DESC' order removed for compatibility with PostgreSQL cursor.execute(""" CREATE INDEX forms_history_time_idx ON forms_history(time) """) cursor.execute(""" CREATE INDEX forms_history_author_idx ON forms_history(author) """) cursor.execute(""" DROP TABLE tracform_history """) # migrate fields table if env.config.get('trac', 'database').startswith('postgres'): cursor.execute(""" CREATE TABLE forms_fields AS SELECT tracform_id AS id, field, updater AS author, updated_on AS time FROM tracform_fields """) else: cursor.execute(""" CREATE TABLE forms_fields AS SELECT tracform_id 'id', field, updater 'author', updated_on 'time' FROM tracform_fields """) cursor.execute(""" CREATE UNIQUE INDEX forms_fields_id_field_idx ON forms_fields(id, field) """) cursor.execute(""" DROP TABLE tracform_fields """) # remove old TracForms version entry cursor.execute(""" DELETE FROM system WHERE name='TracFormDBComponent:version'; """)
def do_upgrade(env, ver, cursor): # Change repository key from reponame to a surrogate id cursor.execute("SELECT id FROM repository " "UNION SELECT repos AS id FROM revision " "UNION SELECT repos AS id FROM node_change " "ORDER BY id") id_name_list = [(i + 1, name) for i, (name, ) in enumerate(cursor)] cursor.execute("CREATE TEMPORARY TABLE repo_old " "AS SELECT * FROM repository") cursor.execute("DROP TABLE repository") cursor.execute("CREATE TEMPORARY TABLE rev_old " "AS SELECT * FROM revision") cursor.execute("DROP TABLE revision") cursor.execute("CREATE TEMPORARY TABLE nc_old " "AS SELECT * FROM node_change") cursor.execute("DROP TABLE node_change") tables = [ Table('repository', key=('id', 'name'))[Column('id', type='int'), Column('name'), Column('value')], Table('revision', key=('repos', 'rev'))[Column('repos', type='int'), Column('rev', key_size=20), Column('time', type='int'), Column('author'), Column('message'), Index(['repos', 'time'])], Table('node_change', key=('repos', 'rev', 'path', 'change_type'))[Column('repos', type='int'), Column('rev', key_size=20), Column('path', key_size=255), Column('node_type', size=1), Column('change_type', size=1, key_size=2), Column('base_path'), Column('base_rev'), Index(['repos', 'rev'])] ] db_connector, _ = DatabaseManager(env).get_connector() for table in tables: for stmt in db_connector.to_sql(table): cursor.execute(stmt) cursor.executemany( "INSERT INTO repository (id,name,value) " "VALUES (%s,'name',%s)", id_name_list) cursor.executemany( "INSERT INTO repository (id,name,value) " "SELECT %s,name,value FROM repo_old WHERE id=%s", id_name_list) cursor.execute("DROP TABLE repo_old") cursor.executemany( "INSERT INTO revision (repos,rev,time,author,message) " "SELECT %s,rev,time,author,message FROM rev_old " "WHERE repos=%s", id_name_list) cursor.execute("DROP TABLE rev_old") cursor.executemany( "INSERT INTO node_change (repos,rev,path,node_type," " change_type,base_path,base_rev) " "SELECT %s,rev,path,node_type,change_type,base_path," " base_rev FROM nc_old WHERE repos=%s", id_name_list) cursor.execute("DROP TABLE nc_old")
# This component manages the revision to ticket map. # # 2011-jan-27 srl adding IRepositoryChangeListener functionality (requires trac 0.12) from trac.core import Component, implements, TracError from trac.env import IEnvironmentSetupParticipant from trac.db import Table, Column, Index, DatabaseManager from trac.config import Option from trac.util.text import exception_to_unicode from trac.versioncontrol.api import IRepositoryChangeListener import re tktmgr_schema = [ Table('rev2ticket', key='rev')[ # map rev->ticket Column('rev', type='int'), # changeset id Column('ticket', type='int'), # ticket # Index(['ticket'])], # index by ticket ] class TicketManager(Component): implements(IEnvironmentSetupParticipant, IRepositoryChangeListener) ticket_pattern = Option( 'icucodetools', 'ticket_pattern', '^ticket:(\d+)', """A regex matching the commit messages. Group 1 must return a number.""" ) def icu_tktmgr(self): return 1
from trac.db import Table, Column, Index, DatabaseManager tables = [ Table('forum', key='id')[Column('id', type='integer', auto_increment=True), Column('name'), Column('time', type='integer'), Column('moderators'), Column('subject'), Column('description')], Table('topic', key='id')[Column('id', type='integer', auto_increment=True), Column('forum', type='integer'), Column('time', type='integer'), Column('author'), Column('subject'), Column('body')], Table('message', key='id')[Column('id', type='integer', auto_increment=True), Column('forum', type='integer'), Column('topic', type='integer'), Column('replyto', type='integer'), Column('time', type='integer'), Column('author'), Column('body')] ] def do_upgrade(env, cursor): db_connector, _ = DatabaseManager(env)._get_connector() print "Upgrading forum database tables to version 1\n"
def do_upgrade(env, ver, cursor): # Make changeset cache multi-repository aware cursor.execute("CREATE TEMPORARY TABLE rev_old " "AS SELECT * FROM revision") cursor.execute("DROP TABLE revision") cursor.execute("CREATE TEMPORARY TABLE nc_old " "AS SELECT * FROM node_change") cursor.execute("DROP TABLE node_change") tables = [Table('repository', key=('id', 'name'))[ Column('id'), Column('name'), Column('value')], Table('revision', key=('repos', 'rev'))[ Column('repos'), Column('rev', key_size=20), Column('time', type='int'), Column('author'), Column('message'), Index(['repos', 'time'])], Table('node_change', key=('repos', 'rev', 'path', 'change_type'))[ Column('repos', key_size=56), Column('rev', key_size=20), Column('path', key_size=255), Column('node_type', size=1), Column('change_type', size=1, key_size=2), Column('base_path'), Column('base_rev'), Index(['repos', 'rev'])]] db_connector, _ = DatabaseManager(env).get_connector() for table in tables: for stmt in db_connector.to_sql(table): cursor.execute(stmt) cursor.execute("INSERT INTO revision (repos,rev,time,author,message) " "SELECT '',rev,time,author,message FROM rev_old") cursor.execute("DROP TABLE rev_old") cursor.execute("INSERT INTO node_change (repos,rev,path,node_type," "change_type,base_path,base_rev) " "SELECT '',rev,path,node_type,change_type,base_path," "base_rev FROM nc_old") cursor.execute("DROP TABLE nc_old") cursor.execute("INSERT INTO repository (id,name,value) " "SELECT '',name,value FROM system " "WHERE name IN ('repository_dir', 'youngest_rev')") cursor.execute("DELETE FROM system " "WHERE name IN ('repository_dir', 'youngest_rev')")
class DownloaderDB: ## ## Downloader plug-in's database schema ## schema = [ # Downloader Table('downloader_category', key='id') [Column('id', auto_increment=True), Column('name'), Column('notes'), Column('sort', type='int'), Column('timestamp', type='int'), Column('deleted', type='int')], Table('downloader_release', key=('id'))[Column('id', auto_increment=True), Column('category'), Column('name'), Column('notes'), Column('sort', type='int'), Column('timestamp', type='int'), Column('deleted', type='int')], Table('downloader_file', key=('id'))[Column('id', auto_increment=True), Column('release'), Column('name'), Column('notes'), Column('architecture'), Column('sort', type='int'), Column('timestamp', type='int'), Column('deleted', type='int')], Table('downloader_downloaded', key=('id'))[Column('id', auto_increment=True), Column('file', type='int'), Column('timestamp', type='int')], Table('downloader_downloaded_attributes', key=(['downloaded', 'name']))[Column('downloaded', type='int'), Column('name'), Column('value')], ] def __init__(self, env): self.env = env # IEnvironmentSetupPart methods """Extension point interface for components that need to participate in the creation and upgrading of Trac environments, for example to create additional database tables.""" def environment_created(self): """Called when a new Trac environment is created.""" pass def upgrade_environment(self, db): """Actually perform an environment upgrade. Implementations of this method should not commit any database transactions. This is done implicitly after all participants have performed the upgrades they need without an error being raised. """ cursor = db.cursor() for table in self.schema: try: from trac.db import DatabaseManager db_connector, _ = DatabaseManager(self.env)._get_connector() except ImportError: db_connector = db.cnx for stmt in db_connector.to_sql(table): cursor.execute(stmt) self.env.log.info("Downloader plug-in DB was created.") def environment_needs_upgrade(self, db): """Called when Trac checks whether the environment needs to be upgraded. Should return `True` if this participant needs an upgrade to be performed, `False` otherwise. """ """ Checks existence of Downloader plug-in's DB. NOTE that it checks only existence of the first table! """ cursor = db.cursor() try: cursor.execute("SELECT 1 FROM downloader_category") except: self.env.log.info("Downloader plug-in DB does not exist.") return True return False
def do_upgrade(env, ver, cursor): cursor.execute( "CREATE TEMPORARY TABLE session_old AS SELECT * FROM session") cursor.execute("DROP TABLE session") cursor.execute( "CREATE TEMPORARY TABLE ticket_change_old AS SELECT * FROM ticket_change" ) cursor.execute("DROP TABLE ticket_change") # A more normalized session schema where the attributes are stored in # a separate table tables = [ Table('session', key=('sid', 'authenticated'))[Column('sid'), Column('authenticated', type='int'), Column('last_visit', type='int'), Index(['last_visit']), Index(['authenticated'])], Table('session_attribute', key=('sid', 'authenticated', 'name'))[Column('sid'), Column('authenticated', type='int'), Column('name'), Column('value')], Table('ticket_change', key=('ticket', 'time', 'field'))[Column('ticket', type='int'), Column('time', type='int'), Column('author'), Column('field'), Column('oldvalue'), Column('newvalue'), Index(['ticket']), Index(['time'])] ] db_connector, _ = DatabaseManager(env).get_connector() for table in tables: for stmt in db_connector.to_sql(table): cursor.execute(stmt) # Add an index to the temporary table to speed up the conversion cursor.execute("CREATE INDEX session_old_sid_idx ON session_old(sid)") # Insert the sessions into the new table with env.db_query as db: cursor.execute(""" INSERT INTO session (sid, last_visit, authenticated) SELECT distinct s.sid,COALESCE(%s,0),s.authenticated FROM session_old AS s LEFT JOIN session_old AS s2 ON (s.sid=s2.sid AND s2.var_name='last_visit') WHERE s.sid IS NOT NULL """ % db.cast('s2.var_value', 'int')) cursor.execute(""" INSERT INTO session_attribute (sid, authenticated, name, value) SELECT s.sid, s.authenticated, s.var_name, s.var_value FROM session_old s WHERE s.var_name <> 'last_visit' AND s.sid IS NOT NULL """) # Insert ticket change data into the new table cursor.execute(""" INSERT INTO ticket_change (ticket, time, author, field, oldvalue, newvalue) SELECT ticket, time, author, field, oldvalue, newvalue FROM ticket_change_old """) cursor.execute("DROP TABLE session_old") cursor.execute("DROP TABLE ticket_change_old")
class MMV_List(object): """Represents milestone list.""" _schema = [ Table('mmv_list', key='id')[ Column('id', auto_increment=True), Column('milestone'), Column('startdate', type='int'), Column('enddate', type='int'), Column('enabled', type='int'), Index(['milestone']) ], Table('mmv_history', key=('date', 'milestone'))[ Column('date', type='int'), Column('milestone'), Column('due'), Column('done'), Index(['date', 'milestone']) ] ] def __init__(self, env, milestone=None): """Initialize a new entry with the specified attributes. To actually create this build log in the database, the `insert` method needs to be called. """ self.env = env self.id = None self.milestone = milestone self.startdate = None self.enddate = None def delete(cls, env, milestone, db=None): """Remove the milestone from the database.""" if not db: db = env.get_db_cnx() handle_ta = True else: handle_ta = False cursor = db.cursor() cursor.execute("DELETE FROM mmv_list WHERE milestone=%s;", (milestone,)) if handle_ta: db.commit() delete = classmethod(delete) def deleteAll(cls, env, db=None): """Remove the milestone from the database.""" if not db: db = env.get_db_cnx() handle_ta = True else: handle_ta = False cursor = db.cursor() cursor.execute("DELETE FROM mmv_list;") if handle_ta: db.commit() deleteAll = classmethod(deleteAll) def deleteAllHistory(cls, env, db=None): """Remove the milestone from the database.""" if not db: db = env.get_db_cnx() handle_ta = True else: handle_ta = False cursor = db.cursor() cursor.execute("DELETE FROM mmv_history;") if handle_ta: db.commit() deleteAllHistory = classmethod(deleteAllHistory) def insert(cls, env, milestone, startdate=None, enddate=None, enabled=False, db=None): """Insert a new milestone into the database.""" if not db: db = env.get_db_cnx() handle_ta = True else: handle_ta = False cursor = db.cursor() cursor.execute("INSERT INTO mmv_list " "(milestone, startdate, enddate, enabled) VALUES (%s, %s, %s, %s);", (milestone, startdate, enddate, int(enabled))) id = db.get_last_id(cursor, 'mmv_list') if handle_ta: db.commit() return id insert = classmethod(insert) # def getMilestones(cls, env, db=None): # """Retrieve from the database that match # the specified criteria. # """ # if not db: # db = env.get_db_cnx() # # cursor = db.cursor() # # cursor.execute("SELECT milestone FROM mmv_list ORDER BY milestone;") # # return [m[0] for m in cursor.fetchall()] # # getMilestones = classmethod(getMilestones) def getEnabledMilestones(cls, env, db=None): """Retrieve from the database that match the specified criteria. """ if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute("SELECT milestone FROM mmv_list " "WHERE enabled = 1 ORDER BY milestone;") return [m[0] for m in cursor.fetchall()] getEnabledMilestones = classmethod(getEnabledMilestones) def getStartdate(cls, env, milestone, db=None): """Get start date of milestone. """ if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute("SELECT startdate FROM mmv_list WHERE milestone=%s LIMIT 1;", (milestone, )) row = cursor.fetchone() if not row or not row[0]: cursor.execute("SELECT min(time) FROM ticket WHERE milestone=%s LIMIT 1;", (milestone, )) row = cursor.fetchone() if row and not row[0]: return 0 return row[0] getStartdate = classmethod(getStartdate) def getEnddate(cls, env, milestone, db=None): """Get end date of milestone. """ if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute("SELECT enddate FROM mmv_list WHERE milestone=%s LIMIT 1;", (milestone, )) row = cursor.fetchone() if not row or not row[0]: cursor.execute("SELECT completed FROM milestone WHERE name=%s LIMIT 1;", (milestone, )) row = cursor.fetchone() if row and not row[0]: return int(time.time()) return row[0] getEnddate = classmethod(getEnddate) def getStartdateFromDb(cls, env, milestone, db=None): """Get start date of milestone. """ if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute("SELECT startdate FROM mmv_list WHERE milestone=%s LIMIT 1;", (milestone, )) row = cursor.fetchone() if not row or not row[0]: return None return row[0] getStartdateFromDb = classmethod(getStartdateFromDb) def getEnddateFromDb(cls, env, milestone, db=None): """Get end date of milestone. """ if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute("SELECT enddate FROM mmv_list WHERE milestone=%s LIMIT 1;", (milestone, )) row = cursor.fetchone() if not row or not row[0]: return None return row[0] getEnddateFromDb = classmethod(getEnddateFromDb) def getDue(cls, env, dateMin, dateMax, milestone, db=None): """Get due days data. """ if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute("SELECT date, due FROM mmv_history " " WHERE milestone=%s AND date >= %s AND date <= %s;", (milestone, dateMin, dateMax, )) rows = cursor.fetchall() return rows getDue = classmethod(getDue) def getMaxHistoryDate(cls, env, milestone, db=None): """ get max history date for a milestone """ if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute("SELECT max(date) FROM mmv_history " "WHERE milestone = %s;", (milestone, )) row = cursor.fetchone() if not row[0]: return 0 else: return int(row[0]) getMaxHistoryDate = classmethod(getMaxHistoryDate) def addHistory(self, env, date, milestone, db=None): """Add due/done to history. """ if not db: db = env.get_db_cnx() cursor = db.cursor() # get max_history_date max_history_date = self.getMaxHistoryDate(env, milestone) if date < max_history_date - 1: # don't update stable history return # due dueDaysYest = self._getDueDays(date - 1, milestone, db) dueDays = self._getDueDays(date, milestone, db) # done doneDaysYest = self._getDoneDays(date - 1, milestone, db) doneDays = self._getDoneDays(date, milestone, db) # update the day before self._updateHistory(dueDaysYest, doneDaysYest, milestone, date - 1, db) # update the day self._updateHistory(dueDays, doneDays, milestone, date, db) def _updateHistory(self, dueDays, doneDays, milestone, date, db): if not db: db = env.get_db_cnx() cursor = db.cursor() exist = self._checkExist(milestone, date, db) if exist: # update cursor.execute("UPDATE mmv_history SET due = %s, done = %s " "WHERE milestone = %s AND date = %s;", (dueDays, doneDays, milestone, date)) else: # insert new cursor.execute("INSERT INTO mmv_history (due, done, milestone, date)" "VALUES (%s, %s, %s, %s);", (dueDays, doneDays, milestone, date)) db.commit() def _checkExist(self, milestone, date, db): """ check if history exist in db """ cursor = db.cursor() cursor.execute("SELECT due, done FROM mmv_history " "WHERE milestone = %s AND date = %s LIMIT 1;", (milestone, date, )) if cursor.fetchone(): return True else: return False def _getDueDays(self, date, milestone, db): """ Get total due days """ cursor = db.cursor() # due sqlString = """ SELECT tc.ticket, tc.name, tc.value FROM ticket_custom tc, ticket t WHERE tc.ticket = t.id AND tc.name = 'duetime' AND t.id IN ( SELECT ta.id FROM (SELECT id FROM ticket WHERE time < %(dateEndTime)s ) AS ta LEFT JOIN (SELECT DISTINCT B.ticket AS ticket, B.time AS time, A.newvalue AS newvalue FROM ticket_change A, (SELECT ticket, max(time) AS time FROM ticket_change WHERE field = 'status' AND time < %(dateEndTime)s GROUP BY ticket ORDER BY ticket) AS B WHERE A.ticket = B.ticket AND A.time = B.time AND A.field = 'status' AND newvalue IN ('closed','reopened') ) AS tb ON ta.id = tb.ticket WHERE tb.newvalue != 'closed' OR tb.newvalue IS NULL ) AND t.milestone = '%(milestone)s'; """ % {'dateEndTime': (date + 1) * SECPERDAY, 'milestone':milestone, } cursor.execute(sqlString) rows = cursor.fetchall() # calculate total due days dueDays = 0 for row in rows: dueDays += dueday(row[2]) return dueDays def _getDoneDays(self, date, milestone, db): """ Get total done days """ cursor = db.cursor() # done sqlString = """ SELECT tc.ticket, tc.name, tc.value FROM ticket_custom tc, ticket t WHERE tc.ticket = t.id AND tc.name = 'duetime' AND t.id IN ( SELECT ta.id FROM (SELECT id FROM ticket WHERE time < %(dateEndTime)s ) AS ta LEFT JOIN (SELECT DISTINCT B.ticket AS ticket, B.time AS time, A.newvalue AS newvalue FROM ticket_change A, (SELECT ticket, max(time) AS time FROM ticket_change WHERE field = 'status' AND time < %(dateEndTime)s GROUP BY ticket ORDER BY ticket) AS B WHERE A.ticket = B.ticket AND A.time = B.time AND A.field = 'status' AND newvalue IN ('closed','reopened') ) AS tb ON ta.id = tb.ticket WHERE tb.newvalue = 'closed' ) AND t.milestone = '%(milestone)s'; """ % {'dateEndTime': (date + 1) * SECPERDAY, 'milestone':milestone, } cursor.execute(sqlString) rows = cursor.fetchall() # calculate total done days doneDays = 0 for row in rows: doneDays += dueday(row[2]) return doneDays
def test_upgrade_to_schema_v4(self): # Schema from r9116 - 25-Sep-2010 for announcer-0.12.1 by R. Corsaro. schema = [ Table('subscriptions', key='id')[Column('id', auto_increment=True), Column('sid'), Column('authenticated', type='int'), Column('enabled', type='int'), Column('managed'), Column('realm'), Column('category'), Column('rule'), Column('transport'), Index(['id']), Index(['realm', 'category', 'enabled']), ], Table('subscription', key='id')[Column('id', auto_increment=True), Column('time', type='int64'), Column('changetime', type='int64'), Column('class'), Column('sid'), Column('authenticated', type='int'), Column('distributor'), Column('format'), Column('priority'), Column('adverb')], Table('subscription_attribute', key='id')[Column('id', auto_increment=True), Column('sid'), Column('class'), Column('name'), Column('value')] ] self._schema_init(schema) # Populate tables with test data. cursor = self.db.cursor() cursor.execute(""" INSERT INTO subscription (time,changetime,class,sid,authenticated, distributor,format,priority,adverb) VALUES ('0','0','GeneralWikiSubscriber','user','1', 'email','text/plain','1','always') """) cursor.executemany( """ INSERT INTO subscription_attribute (sid,class,name,value) VALUES (%s,%s,%s,%s) """, (('somebody', 'GeneralWikiSubscriber', 'wiki', '*'), ('somebody', 'UserChangeSubscriber', 'wiki', 'created'), ('user', 'GeneralWikiSubscriber', 'wiki', 'TracWiki'))) self.assertEquals(3, self.an_sys.get_schema_version(self.db)) target = 4 db_default.schema_version = target self.assertTrue(self.an_sys.environment_needs_upgrade(self.db)) # From r9210 - 29-Sep-2010 for announcer-0.12.1 by Robert Corsaro. # - table 'subscriptions' # 'subscription.priority' type=(default == char) --> 'int' # 'subscription_attribute.name --> 'subscription_attribute.realm' # 'subscription_attribute.value --> 'subscription_attribute.target' self.an_sys.upgrade_environment(self.db) self.assertEquals(target, self.an_sys.get_schema_version(self.db)) # Check type of priority value. cursor = self.db.cursor() cursor.execute("SELECT priority FROM subscription") for priority in cursor: # Shouldn't raise an TypeError with appropriate column type. result = priority[0] + 0
class DefaultCCAdmin(Component): """Allows to setup a default CC list per component through the component admin UI. """ implements(IEnvironmentSetupParticipant, ITemplateStreamFilter, IRequestFilter) SCHEMA = [ Table('component_default_cc', key='name')[Column('name'), Column('cc'), Index(['name']), ] ] # IEnvironmentSetupParticipant methods def environment_created(self): self._upgrade_db() def environment_needs_upgrade(self): return 'component_default_cc' not in self._get_tables() def upgrade_environment(self, db): self._upgrade_db() def _upgrade_db(self): db_backend = DatabaseManager(self.env)._get_connector()[0] with self.env.db_transaction as db: cursor = db.cursor() for table in self.SCHEMA: for stmt in db_backend.to_sql(table): cursor.execute(stmt) def _get_tables(self): dburi = self.config.get('trac', 'database') if dburi.startswith('sqlite:'): query = "SELECT name FROM sqlite_master" \ " WHERE type='table' AND NOT name='sqlite_sequence'" elif dburi.startswith('postgres:'): query = "SELECT tablename FROM pg_tables" \ " WHERE schemaname = ANY (current_schemas(false))" elif dburi.startswith('mysql:'): query = "SHOW TABLES" else: raise TracError('Unsupported %s database' % dburi.split(':')[0]) with self.env.db_query as db: cursor = db.cursor() cursor.execute(query) return sorted(row[0] for row in cursor) # IRequestFilter methods def pre_process_request(self, req, handler): if 'TICKET_ADMIN' in req.perm and req.method == 'POST' \ and req.path_info.startswith('/admin/ticket/components'): if req.args.get('save') and req.args.get('name'): old_name = req.args.get('old_name') new_name = req.args.get('name') old_cc = DefaultCC(self.env, old_name) new_cc = DefaultCC(self.env, new_name) new_cc.cc = req.args.get('defaultcc', '') if old_name == new_name: old_cc.delete() new_cc.insert() else: try: model.Component(self.env, new_name) except ResourceNotFound: old_cc.delete() new_cc.insert() elif req.args.get('add') and req.args.get('name'): name = req.args.get('name') try: model.Component(self.env, name) except ResourceNotFound: cc = DefaultCC(self.env, name) cc.name = name cc.cc = req.args.get('defaultcc', '') cc.insert() elif req.args.get('remove'): if req.args.get('sel'): # If only one component is selected, we don't receive # an array, but a string preventing us from looping in # that case. if isinstance(req.args.get('sel'), basestring): cc = DefaultCC(self.env, req.args.get('sel')) cc.delete() else: for name in req.args.get('sel'): cc = DefaultCC(self.env, name) cc.delete() return handler def post_process_request(self, req, template, data, content_type): if template == 'admin_components.html' and 'components' in data: # Prior to Trac 1.0.2-r11919, components was a generator and # expanding the generator causes the table to not be rendered data['components'] = list(data['components']) return template, data, content_type # ITemplateStreamFilter methods def filter_stream(self, req, method, filename, stream, data): if 'TICKET_ADMIN' in req.perm and \ req.path_info.startswith('/admin/ticket/components'): if data.get('component'): cc = DefaultCC(self.env, data.get('component').name) filter = Transformer('//form[@class="mod"]/fieldset' '/div[@class="field"][2]') filter = filter.after(tag.div("Default CC:", tag.br(), tag.input(type='text', name='defaultcc', value=cc.cc), class_='field')) \ .before(tag.input(type='hidden', name='old_name', value=cc.name)) return stream | filter else: filter = Transformer('//form[@id="addcomponent"]' '/fieldset/div[@class="buttons"]') stream |= filter.before( tag.div("Default CC:", tag.br(), tag.input(type='text', name='defaultcc'), class_='field')) default_ccs = DefaultCC.select(self.env) stream |= Transformer('//table[@id="complist"]/thead' '/tr/th[3]') \ .after(tag.th('Default CC')) components = data.get('components') if components: func = self._inject_default_cc_cols( default_ccs, components) stream |= Transformer('//table[@id="complist"]' '/tbody/tr').apply(func) return stream return stream def _inject_default_cc_cols(self, default_ccs, components): def fn(stream): idx = 0 for mark, event in stream: if mark is None: yield mark, event continue kind, data, pos = event if kind is START: if data[0].localname == 'td' and \ data[1].get('class') == 'default': if idx < len(components): component = components[idx] cc = default_ccs.get(component.name) or '' else: cc = '' idx += 1 for event in tag.td(cc, class_='defaultcc'): yield INSIDE, event yield mark, (kind, data, pos) return fn
from trac.db import Table, Column, Index, DatabaseManager schema = [ Table('crashdump_stack', key=( 'crash', 'threadid', 'frameno'))[Column('crash', type='int'), Column('threadid', type='int'), Column('frameno', type='int'), Column('module'), Column('function'), Column('funcoff', type='int'), Column('source'), Column('line', type='int'), Column('lineoff', type='int'), Index(['crash', 'frameno']), Index(['crash', 'threadid', 'frameno'], unique=True), ], ] def do_upgrade(env, ver, cursor): """adds the table crashdump_stack """ connector = DatabaseManager(env)._get_connector()[0] for table in schema: for stmt in connector.to_sql(table): cursor.execute(stmt)
from trac.db import Table, Column, Index version = 1 name = 'ticketreminder' schema = [ Table('ticketreminder', key='id')[Column('id', auto_increment=True), Column('ticket', type='int'), Column('time', type='int64'), Column('author'), Column('origin', type='int64'), Column('reminded', type='int'), Column('repeat', type='int'), Column('description'), Index(['ticket']), Index(['time'])], ]
class BuildConfig(object): """Representation of a build configuration.""" _schema = [ Table('bitten_config', key='name')[Column('name'), Column('branch'), Column('active', type='int'), Column('recipe'), Column('label'), Column('description')] ] def __init__(self, env, name=None, branch=None, active=False, recipe=None, label=None, description=None): """Initialize a new build configuration with the specified attributes. To actually create this configuration in the database, the `insert` method needs to be called. """ self.env = env self._old_name = None self.name = name self.branch = branch or '' self.active = bool(active) self.recipe = recipe or '' self.label = label or '' self.description = description or '' def __repr__(self): return '<%s %r>' % (type(self).__name__, self.name) exists = property(fget=lambda self: self._old_name is not None, doc='Whether this configuration exists in the database') resource = property(fget=lambda self: Resource('build', '%s' % self.name), doc='Build Config resource identification') def delete(self, db=None): """Remove a build configuration and all dependent objects from the database.""" assert self.exists, 'Cannot delete non-existing configuration' if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False for platform in list(TargetPlatform.select(self.env, self.name, db=db)): platform.delete(db=db) for build in list(Build.select(self.env, config=self.name, db=db)): build.delete(db=db) # Delete attachments Attachment.delete_all(self.env, 'build', self.resource.id, db) cursor = db.cursor() cursor.execute("DELETE FROM bitten_config WHERE name=%s", (self.name, )) if handle_ta: db.commit() self._old_name = None def insert(self, db=None): """Insert a new configuration into the database.""" assert not self.exists, 'Cannot insert existing configuration' assert self.name, 'Configuration requires a name' if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False cursor = db.cursor() cursor.execute( "INSERT INTO bitten_config (name,branch,active," "recipe,label,description) " "VALUES (%s,%s,%s,%s,%s,%s)", (self.name, self.branch, int(self.active or 0), self.recipe or '', self.label or '', self.description or '')) if handle_ta: db.commit() self._old_name = self.name def update(self, db=None): """Save changes to an existing build configuration.""" assert self.exists, 'Cannot update a non-existing configuration' assert self.name, 'Configuration requires a name' if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False cursor = db.cursor() cursor.execute( "UPDATE bitten_config SET name=%s,branch=%s,active=%s," "recipe=%s,label=%s," "description=%s WHERE name=%s", (self.name, self.branch, int(self.active or 0), self.recipe, self.label, self.description, self._old_name)) if self.name != self._old_name: cursor.execute( "UPDATE bitten_platform SET config=%s " "WHERE config=%s", (self.name, self._old_name)) cursor.execute( "UPDATE bitten_build SET config=%s " "WHERE config=%s", (self.name, self._old_name)) if handle_ta: db.commit() self._old_name = self.name def fetch(cls, env, name, db=None): """Retrieve an existing build configuration from the database by name. """ if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute( "SELECT branch,active,recipe,label," "description FROM bitten_config WHERE name=%s", (name, )) row = cursor.fetchone() if not row: return None config = BuildConfig(env) config.name = config._old_name = name config.branch = row[0] or '' config.active = bool(row[1]) config.recipe = row[2] or '' config.label = row[3] or '' config.description = row[4] or '' return config fetch = classmethod(fetch) def select(cls, env, include_inactive=False, db=None): """Retrieve existing build configurations from the database that match the specified criteria. """ if not db: db = env.get_db_cnx() cursor = db.cursor() if include_inactive: cursor.execute( "SELECT name,branch,active,recipe," "label,description FROM bitten_config ORDER BY name") else: cursor.execute("SELECT name,branch,active,recipe," "label,description FROM bitten_config " "WHERE active=1 ORDER BY name") for name, branch, active, recipe, label, description \ in cursor: config = BuildConfig(env, name=name, branch=branch or '', active=bool(active), recipe=recipe or '', label=label or '', description=description or '') config._old_name = name yield config select = classmethod(select)
# -*- coding: utf-8 -*- # # Copyright (c) 2007-2012 Noah Kantrowitz <*****@*****.**> # Copyright (c) 2013-2016 Ryan J Ollos <*****@*****.**> # # All rights reserved. # # This software is licensed as described in the file COPYING, which # you should have received as part of this distribution. # from trac.db import Table, Column name = 'mastertickets' version = 2 tables = [ Table('mastertickets', key=('source', 'dest'))[Column('source', type='integer'), Column('dest', type='integer'), ], ]
class BuildStep(object): """Represents an individual step of an executed build.""" _schema = [ Table('bitten_step', key=('build', 'name'))[Column('build', type='int'), Column('name'), Column('description'), Column('status', size=1), Column('started', type='int'), Column('stopped', type='int')], Table('bitten_error', key=('build', 'step', 'orderno'))[Column('build', type='int'), Column('step'), Column('message'), Column('orderno', type='int')] ] # Step status codes SUCCESS = 'S' FAILURE = 'F' def __init__(self, env, build=None, name=None, description=None, status=None, started=None, stopped=None): """Initialize a new build step with the specified attributes. To actually create this build step in the database, the `insert` method needs to be called. """ self.env = env self.build = build self.name = name self.description = description self.status = status self.started = started self.stopped = stopped self.errors = [] self._exists = False exists = property(fget=lambda self: self._exists, doc='Whether this build step exists in the database') successful = property(fget=lambda self: self.status == BuildStep.SUCCESS, doc='Whether the build step was successful') def delete(self, db=None): """Remove the build step from the database.""" if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False for log in list( BuildLog.select(self.env, build=self.build, step=self.name, db=db)): log.delete(db=db) for report in list( Report.select(self.env, build=self.build, step=self.name, db=db)): report.delete(db=db) cursor = db.cursor() cursor.execute("DELETE FROM bitten_step WHERE build=%s AND name=%s", (self.build, self.name)) cursor.execute("DELETE FROM bitten_error WHERE build=%s AND step=%s", (self.build, self.name)) if handle_ta: db.commit() self._exists = False def insert(self, db=None): """Insert a new build step into the database.""" if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False assert self.build and self.name assert self.status in (self.SUCCESS, self.FAILURE) cursor = db.cursor() cursor.execute( "INSERT INTO bitten_step (build,name,description,status," "started,stopped) VALUES (%s,%s,%s,%s,%s,%s)", (self.build, self.name, self.description or '', self.status, self.started or 0, self.stopped or 0)) if self.errors: cursor.executemany( "INSERT INTO bitten_error (build,step,message," "orderno) VALUES (%s,%s,%s,%s)", [(self.build, self.name, message, idx) for idx, message in enumerate(self.errors)]) if handle_ta: db.commit() self._exists = True def fetch(cls, env, build, name, db=None): """Retrieve an existing build from the database by build ID and step name.""" if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute( "SELECT description,status,started,stopped " "FROM bitten_step WHERE build=%s AND name=%s", (build, name)) row = cursor.fetchone() if not row: return None step = BuildStep(env, build, name, row[0] or '', row[1], row[2] and int(row[2]), row[3] and int(row[3])) step._exists = True cursor.execute( "SELECT message FROM bitten_error WHERE build=%s " "AND step=%s ORDER BY orderno", (build, name)) for row in cursor: step.errors.append(row[0] or '') return step fetch = classmethod(fetch) def select(cls, env, build=None, name=None, status=None, db=None): """Retrieve existing build steps from the database that match the specified criteria. """ if not db: db = env.get_db_cnx() assert status in (None, BuildStep.SUCCESS, BuildStep.FAILURE) where_clauses = [] if build is not None: where_clauses.append(("build=%s", build)) if name is not None: where_clauses.append(("name=%s", name)) if status is not None: where_clauses.append(("status=%s", status)) if where_clauses: where = "WHERE " + " AND ".join([wc[0] for wc in where_clauses]) else: where = "" cursor = db.cursor() cursor.execute( "SELECT build,name FROM bitten_step %s ORDER BY stopped" % where, [wc[1] for wc in where_clauses]) for build, name in cursor: yield BuildStep.fetch(env, build, name, db=db) select = classmethod(select)
# # Copyright 2009, Niels Sascha Reedijk <*****@*****.**> # All rights reserved. Distributed under the terms of the MIT License. # from trac.db import Table, Column name = 'attachmentflags' version = 1 tables = [ Table('attachmentflags', key=('type', 'id', 'filename', 'flag'))[Column('type'), Column('id'), Column('filename'), Column('flag'), Column('value'), Column('updated_on', type="int"), Column('updated_by'), ], ]
class Report(object): """Represents a generated report.""" _schema = [ Table('bitten_report', key='id')[Column('id', auto_increment=True), Column('build', type='int'), Column('step'), Column('category'), Column('generator'), Index(['build', 'step', 'category'])], Table('bitten_report_item', key=('report', 'item', 'name'))[Column('report', type='int'), Column('item', type='int'), Column('name'), Column('value')] ] def __init__(self, env, build=None, step=None, category=None, generator=None): """Initialize a new report with the specified attributes. To actually create this build log in the database, the `insert` method needs to be called. """ self.env = env self.id = None self.build = build self.step = step self.category = category self.generator = generator or '' self.items = [] exists = property(fget=lambda self: self.id is not None, doc='Whether this report exists in the database') def delete(self, db=None): """Remove the report from the database.""" assert self.exists, 'Cannot delete a non-existing report' if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False cursor = db.cursor() cursor.execute("DELETE FROM bitten_report_item WHERE report=%s", (self.id, )) cursor.execute("DELETE FROM bitten_report WHERE id=%s", (self.id, )) if handle_ta: db.commit() self.id = None def insert(self, db=None): """Insert a new build log into the database.""" if not db: db = self.env.get_db_cnx() handle_ta = True else: handle_ta = False assert self.build and self.step and self.category # Enforce uniqueness of build-step-category. # This should be done by the database, but the DB schema helpers in Trac # currently don't support UNIQUE() constraints assert not list( Report.select(self.env, build=self.build, step=self.step, category=self.category, db=db)), 'Report already exists' cursor = db.cursor() cursor.execute( "INSERT INTO bitten_report " "(build,step,category,generator) VALUES (%s,%s,%s,%s)", (self.build, self.step, self.category, self.generator)) id = db.get_last_id(cursor, 'bitten_report') for idx, item in enumerate([item for item in self.items if item]): cursor.executemany( "INSERT INTO bitten_report_item " "(report,item,name,value) VALUES (%s,%s,%s,%s)", [(id, idx, key, value) for key, value in item.items()]) if handle_ta: db.commit() self.id = id def fetch(cls, env, id, db=None): """Retrieve an existing build from the database by ID.""" if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute( "SELECT build,step,category,generator " "FROM bitten_report WHERE id=%s", (id, )) row = cursor.fetchone() if not row: return None report = Report(env, int(row[0]), row[1], row[2] or '', row[3] or '') report.id = id cursor.execute( "SELECT item,name,value FROM bitten_report_item " "WHERE report=%s ORDER BY item", (id, )) items = {} for item, name, value in cursor: items.setdefault(item, {})[name] = value report.items = items.values() return report fetch = classmethod(fetch) def select(cls, env, config=None, build=None, step=None, category=None, db=None): """Retrieve existing reports from the database that match the specified criteria. """ where_clauses = [] joins = [] if config is not None: where_clauses.append(("config=%s", config)) joins.append("INNER JOIN bitten_build ON (bitten_build.id=build)") if build is not None: where_clauses.append(("build=%s", build)) if step is not None: where_clauses.append(("step=%s", step)) if category is not None: where_clauses.append(("category=%s", category)) if where_clauses: where = "WHERE " + " AND ".join([wc[0] for wc in where_clauses]) else: where = "" if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute( "SELECT bitten_report.id FROM bitten_report %s %s " "ORDER BY category" % (' '.join(joins), where), [wc[1] for wc in where_clauses]) for (id, ) in cursor: yield Report.fetch(env, id, db=db) select = classmethod(select)
class TT_Template(object): """Represents a generated tt.""" _schema = [ Table('ticket_template_store')[Column('tt_time', type='int'), Column('tt_user'), Column('tt_name'), Column('tt_field'), Column('tt_value'), ] ] def __init__(self, env): """Initialize a new report with the specified attributes. To actually create this build log in the database, the `insert` method needs to be called. """ self.env = env exists = property(fget=lambda self: self.id is not None, doc='Whether this tt exists in the database') def deleteCustom(cls, env, data): """Remove the tt from the database.""" db = env.get_db_cnx() cursor = db.cursor() sqlString = """DELETE FROM ticket_template_store WHERE tt_user=%s AND tt_name=%s """ cursor.execute(sqlString, ( data["tt_user"], data["tt_name"], )) db.commit() deleteCustom = classmethod(deleteCustom) def insert(cls, env, record): """Insert a new tt into the database.""" db = env.get_db_cnx() cursor = db.cursor() sqlString = """INSERT INTO ticket_template_store (tt_time,tt_user,tt_name,tt_field,tt_value) VALUES (%s,%s,%s,%s,%s)""" cursor.execute(sqlString, record) db.commit() insert = classmethod(insert) def fetchCurrent(cls, env, data): """Retrieve an existing tt from the database by ID.""" db = env.get_db_cnx() cursor = db.cursor() sqlString = """SELECT tt_field, tt_value FROM ticket_template_store WHERE tt_user = %s AND tt_time = (SELECT max(tt_time) FROM ticket_template_store WHERE tt_name=%s) """ cursor.execute(sqlString, ( data["tt_user"], data["tt_name"], )) field_value_mapping = {} for tt_field, tt_value in cursor.fetchall(): if tt_value: field_value_mapping[tt_field] = tt_value return field_value_mapping fetchCurrent = classmethod(fetchCurrent) def fetchAll(cls, env, data): """Retrieve an existing tt from the database by ID. result: { "field_value_mapping":{ "default":{ "summary":"aaa", "description":"bbb", }, }, "field_value_mapping_custom":{ "my_template":{ "summary":"ccc", "description":"ddd", }, }, } """ db = env.get_db_cnx() cursor = db.cursor() real_user = data.get("tt_user") req_args = data.get("req_args") field_value_mapping = {} field_value_mapping_custom = {} # field_value_mapping_custom sqlString = """SELECT tt_name, tt_field, tt_value FROM ticket_template_store WHERE tt_user = %s """ cursor.execute(sqlString, (data["tt_user"], )) for tt_name, tt_field, tt_value in cursor.fetchall(): if not field_value_mapping_custom.has_key(tt_name): field_value_mapping_custom[tt_name] = {} if tt_value: tt_value = formatField(env.config, tt_value, real_user, req_args) field_value_mapping_custom[tt_name][tt_field] = tt_value # field_value_mapping sqlString = """SELECT DISTINCT tt_name FROM ticket_template_store WHERE tt_user = %s """ cursor.execute(sqlString, (SYSTEM_USER, )) tt_name_list = [row[0] for row in cursor.fetchall()] data["tt_user"] = SYSTEM_USER for tt_name in tt_name_list: data["tt_name"] = tt_name sqlString = """SELECT tt_field, tt_value FROM ticket_template_store WHERE tt_user = %s AND tt_name = %s AND tt_time = (SELECT max(tt_time) FROM ticket_template_store WHERE tt_name = %s) """ cursor.execute(sqlString, ( data["tt_user"], data["tt_name"], data["tt_name"], )) for tt_field, tt_value in cursor.fetchall(): if not field_value_mapping.has_key(tt_name): field_value_mapping[tt_name] = {} if tt_value: tt_value = formatField(env.config, tt_value, real_user, req_args) field_value_mapping[tt_name][tt_field] = tt_value result = {} result["field_value_mapping"] = field_value_mapping result["field_value_mapping_custom"] = field_value_mapping_custom return result fetchAll = classmethod(fetchAll) def getCustomTemplate(cls, env, tt_user): """Retrieve from the database that match the specified criteria. """ db = env.get_db_cnx() cursor = db.cursor() sqlString = """SELECT DISTINCT tt_name FROM ticket_template_store WHERE tt_user = %s ORDER BY tt_name """ cursor.execute(sqlString, (tt_user, )) return [row[0] for row in cursor.fetchall()] getCustomTemplate = classmethod(getCustomTemplate) def fetch(cls, env, tt_name, db=None): """Retrieve an existing tt from the database by ID.""" if not db: db = env.get_db_cnx() cursor = db.cursor() sqlString = """SELECT tt_value FROM ticket_template_store WHERE tt_time=( SELECT max(tt_time) FROM ticket_template_store WHERE tt_name=%s and tt_field='description' ) """ cursor.execute(sqlString, (tt_name, )) row = cursor.fetchone() if not row: return None else: return row[0] fetch = classmethod(fetch)
def _create_new_tables(env, cursor, db_connector): backlog = \ Table('backlog', key=('name'))[ Column('name'), Column('b_type', type='integer'), Column('description'), Column('ticket_types'), Column('sorting_keys'), Column('b_strict', type='integer') ] backlog_ticket = \ Table('backlog_ticket', key=('name', 'pos', 'scope'))[ Column('name'), # The name of the Backlog Column('pos', type='integer'), # The position of the ticket Column('scope'), # The scope is the name of a Sprint or a Milestone Column('level', type='integer'), # The level in the hierarchy of this ticket Column('ticket_id', type='integer') # The id of the ticket ] calendar_entry = \ Table('calendar_entry', key=('date', 'teammember'))[ Column('date', type='integer'), Column('teammember'), Column('hours', type='real'), ] team = \ Table('team', key=('name'))[ Column('name'), Column('description'), ] team_member = \ Table('team_member', key=('name'))[ Column('name'), Column('team'), Column('description'), Column('ts_mon', type='real'), Column('ts_tue', type='real'), Column('ts_wed', type='real'), Column('ts_thu', type='real'), Column('ts_fri', type='real'), Column('ts_sat', type='real'), Column('ts_sun', type='real'), ] new_tables = [ backlog, backlog_ticket, calendar_entry, team, team_member, ] sprint = \ Table('sprint', key=('name'))[ Column('name'), Column('description'), Column('start', type='integer'), Column('end', type='integer'), Column('milestone'), Column('team'), ] if get_db_type(env) == 'postgres': # For PostgreSQL 'end' is a reserved word. Agilo 0.7 before final # (db version 3) therefore were unable to run with PostgreSQL. # # But we have to create sprints for every milestone in 0.6 and for that # we need a sprint table so we just use the table definition from # 0.7 final here. db3 will take not to recreate the table. end_column = sprint.columns[3] assert end_column.name == 'end' end_column.name = 'sprint_end' new_tables.append(sprint) if get_db_type(env) != 'mysql': # In MySQL 'key' is a reserved word. Agilo 0.7 before final # (db version 3) therefore were unable to run with MySQL. So we # just skip the table creation here. db3 will create the table with the # correct columns (and fixed column names) for us. team_metrics_entry = \ Table('team_metrics_entry', key=('team', 'sprint', 'key'))[ Column('team'), Column('sprint'), Column('key'), Column('value', type='real'), ] new_tables.append(team_metrics_entry) for table in new_tables: create_table_with_cursor(table, cursor, db_connector) return sprint
from trac.db import Table, Column, Index, DatabaseManager # Commont SQL statements tables = [ Table('download', key='id')[Column('id', type='integer', auto_increment=True), Column('file'), Column('description'), Column('size', type='integer'), Column('time', type='integer'), Column('count', type='integer'), Column('author'), Column('tags'), Column('component'), Column('version'), Column('platform', type='integer'), Column('type', type='integer'), Column('featured', type='tinyint')], Table('platform', key='id')[Column('id', type='integer', auto_increment=True), Column('name'), Column('description')], Table('download_type', key='id')[Column('id', type='integer', auto_increment=True), Column('name'), Column('description')] ] values = [ "INSERT INTO platform (name) VALUES ('Series 40')",
"""Utility function used to create report data in same syntax as the default data. This extra step is done to simplify editing the default reports.""" result = [] for report in reports: result.append((None, report[0], report[2], report[1])) return result ## ## Database schema ## schema = [ # Common Table('system', key='name')[Column('name'), Column('value')], Table('permission', key=('username', 'action'))[Column('username'), Column('action')], Table('auth_cookie', key=('cookie', 'ipnr', 'name'))[Column('cookie'), Column('name'), Column('ipnr'), Column('time', type='int')], Table('session', key=('sid', 'authenticated'))[Column('sid'), Column('authenticated', type='int'), Column('last_visit', type='int'), Index(['last_visit']), Index(['authenticated'])], Table('session_attribute',
class TracTicketChainedFields_List(object): """Represents a table.""" _schema = [ Table('tcf_list', key='id')[ Column('id', auto_increment=True), Column('tcf_define'), Column('tcf_time', type='int'), Index(['id']) ] ] def __init__(self, env): """Initialize a new entry with the specified attributes. To actually create this build log in the database, the `insert` method needs to be called. """ self.env = env # def delete(cls, env, col1, db=None): # """Remove the col1 from the database.""" # if not db: # db = env.get_db_cnx() # handle_ta = True # else: # handle_ta = False # cursor = db.cursor() # cursor.execute("DELETE FROM tcf_list WHERE col1=%s;", (col1,)) # if handle_ta: # db.commit() # delete = classmethod(delete) def insert(cls, env, tcf_define, db=None): """Insert a new col1 into the database.""" if not db: db = env.get_db_cnx() handle_ta = True else: handle_ta = False tcf_time = int(time.time()) cursor = db.cursor() cursor.execute("INSERT INTO tcf_list (tcf_define, tcf_time) VALUES (%s, %s)", (tcf_define, tcf_time)) if handle_ta: db.commit() insert = classmethod(insert) def get_tcf_define(cls, env, db=None): """Retrieve from the database that match the specified criteria. """ if not db: db = env.get_db_cnx() cursor = db.cursor() cursor.execute("SELECT tcf_define FROM tcf_list ORDER BY tcf_time DESC LIMIT 1") row = cursor.fetchone() if row: return row[0] else: return "" get_tcf_define = classmethod(get_tcf_define)