def setUp(self): self.env = EnvironmentStub() self.dbm = DatabaseManager(self.env) self.schema = [ Table('table1', key='col1')[ Column('col1', auto_increment=True), Column('col2'), Column('col3'), ], Table('table2', key='col1')[ Column('col1'), Column('col2'), ], Table('table3', key='col2')[ Column('col1'), Column('col2', type='int'), Column('col3') ] ] self.dbm.create_tables(self.schema) self.new_schema = copy.deepcopy([self.schema[0], self.schema[2]]) self.new_schema[0].remove_columns(('col2',)) self.new_schema[1].columns.append(Column('col4')) self.new_schema.append( Table('table4')[ Column('col1'), ] )
def setUp(self): self.env = EnvironmentStub() self.schema = [ Table('test_simple', key='id')[Column('id', auto_increment=True), Column('username'), Column('email'), Column('enabled', type='int'), Column('extra'), Index(['username'], unique=True), Index(['email'], unique=False), ], Table('test_composite', key=['id', 'name'])[Column('id', type='int'), Column('name'), Column('value'), Column('enabled', type='int'), Index(['name', 'value'], unique=False), Index(['enabled', 'name'], unique=True), ], ] self.dbm = DatabaseManager(self.env) self.dbm.drop_tables(self.schema) self.dbm.create_tables(self.schema) self.dbm.insert_into_tables([ ('test_simple', ('username', 'email', 'enabled'), [('joe', '*****@*****.**', 1), (u'joé', '*****@*****.**', 0)]), ('test_composite', ('id', 'name', 'value', 'enabled'), [(1, 'foo', '42', 1), (1, 'bar', '42', 1), (2, 'foo', '43', 0), (2, 'bar', '43', 0)]), ])
def setUp(self): self.env = EnvironmentStub() self.schema = [ Table('HOURS', key='ID')[Column('ID', auto_increment=True), Column('AUTHOR')], Table('blog', key='bid')[Column('bid', auto_increment=True), Column('author')] ] self.env.global_databasemanager.drop_tables(self.schema) self.env.global_databasemanager.create_tables(self.schema)
def setUp(self): self.env = EnvironmentStub() self.schema = [ Table('HOURS', key='ID')[Column('ID', auto_increment=True), Column('AUTHOR')], Table('blog', key='bid')[Column('bid', auto_increment=True), Column('author'), Column('comment')] ] self.dbm = DatabaseManager(self.env) self.dbm.drop_tables(self.schema) self.dbm.create_tables(self.schema)
def test_remove_columns(self): """Method removes columns and key entries from Table object.""" table = Table('table1', key=['col1', 'col2'])[Column('col1'), Column('col2'), Column('col3'), Column('col4'), ] table.remove_columns(('col2', 'col3')) self.assertEqual(2, len(table.columns)) self.assertEqual('col1', table.columns[0].name) self.assertEqual('col4', table.columns[1].name) self.assertEqual([], table.key)
def do_upgrade(env, version, cursor): """Add `description` column to `enum` table.""" new_schema = [ Table('enum', key=('type', 'name'))[Column('type'), Column('name'), Column('value'), Column('description'), ] ] with env.db_transaction: DatabaseManager(env).upgrade_tables(new_schema) failures = [] for id_ in [1, 2, 3, 4, 5, 7, 8]: try: r = Report(env, id_) except ResourceNotFound: pass else: query = replace_sql_fragment(r.query) if query: r.query = query r.update() else: failures.append(unicode(id_)) if failures: printout( _("""\ Report(s) %(ids)s could not be upgraded and may need to be manually edited to avoid an "ambiguous column name" error. See %(url)s for more information. """, ids=', '.join(failures), url=url))
def _get_table_schema(self, table): key = None items = [] cursor = self.cursor() cursor.execute("PRAGMA table_info(%s)" % self.quote(table)) for row in cursor: column = row[1] type_ = row[2] pk = row[5] if pk == 1 and type_ == 'integer': key = [column] auto_increment = True else: auto_increment = False items.append( Column(column, type=type_, auto_increment=auto_increment)) cursor.execute("PRAGMA index_list(%s)" % self.quote(table)) for row in cursor.fetchall(): index = row[1] unique = row[2] cursor.execute("PRAGMA index_info(%s)" % self.quote(index)) columns = [row[2] for row in cursor] if key is None and index.startswith('sqlite_autoindex_'): key = columns else: items.append(Index(columns, unique=bool(unique))) return Table(table, key=key or [])[items]
def do_upgrade(env, ver, cursor): """Add an auto-increment primary key to `node_change` table and indices (repos, rev, path) and (repos, path, rev) (#3676). """ db_connector, _ = DatabaseManager(env)._get_connector() table = Table('node_change', key='id')[Column('id', auto_increment=True), Column('repos', type='int'), Column('rev', key_size=40), Column('path', key_size=255), Column('node_type', size=1), Column('change_type', size=1), Column('base_path'), Column('base_rev'), Index(['repos', 'rev', 'path']), Index(['repos', 'path', 'rev'])] with env.db_transaction: cursor.execute("""CREATE TEMPORARY TABLE node_change_old AS SELECT * FROM node_change""") cursor.execute("DROP TABLE node_change") for stmt in db_connector.to_sql(table): cursor.execute(stmt) cursor.execute("""\ INSERT INTO node_change (repos,rev,path,node_type,change_type,base_path,base_rev) SELECT repos,rev,path,node_type,change_type,base_path,base_rev FROM node_change_old""") cursor.execute("DROP TABLE node_change_old")
def test_quote_column_names(self): table = Table('foo') table[Column('my"name'), ] sql_generator = PostgreSQLConnector(self.env).to_sql(table) sql_commands = self._normalize_sql(sql_generator) self.assertEqual(1, len(sql_commands)) self.assertEqual('CREATE TABLE "foo" ( "my""name" text)', sql_commands[0])
def add_query_table(self): time_query_table = Table('ticket_time_query', key='id')[ Column('id', auto_increment=True), Column('title'), Column('description'), Column('query')] create_table(self.env, time_query_table)
def test_quote_index_declaration_for_multiple_indexes(self): table = Table('foo') table[Column('a'), Column('b"c'), Index(['a', 'b"c'])] sql_generator = PostgreSQLConnector(self.env).to_sql(table) sql_commands = self._normalize_sql(sql_generator) self.assertEqual(2, len(sql_commands)) self.assertEqual('CREATE TABLE "foo" ( "a" text, "b""c" text)', sql_commands[0]) index_sql = 'CREATE INDEX "foo_a_b""c_idx" ON "foo" ("a","b""c")' self.assertEqual(index_sql, sql_commands[1])
def test_upgrade_tables_no_common_columns(self): schema = [ Table('table1', key='id')[Column('id', auto_increment=True), Column('name'), Column('value'), ], ] self.dbm.upgrade_tables(schema) self.assertEqual(['id', 'name', 'value'], self.dbm.get_column_names('table1')) self.assertEqual([], list(self.env.db_query("SELECT * FROM table1")))
def test_quote_compound_primary_key_declaration(self): table = Table('foo"bar', key=['my name', 'your"name']) table[Column('my name'), Column('your"name'), ] sql_generator = PostgreSQLConnector(self.env).to_sql(table) sql_commands = self._normalize_sql(sql_generator) self.assertEqual(1, len(sql_commands)) expected_sql = 'CREATE TABLE "foo""bar" ( "my name" text, ' + \ '"your""name" text, CONSTRAINT "foo""bar_pk" ' +\ 'PRIMARY KEY ("my name","your""name"))' self.assertEqual(expected_sql, sql_commands[0])
def test_quote_index_declaration(self): table = Table('foo') table[Column('my"name'), Index(['my"name'])] sql_generator = PostgreSQLConnector(self.env).to_sql(table) sql_commands = self._normalize_sql(sql_generator) self.assertEqual(2, len(sql_commands)) self.assertEqual('CREATE TABLE "foo" ( "my""name" text)', sql_commands[0]) index_sql = 'CREATE INDEX "foo_my""name_idx" ON "foo" ("my""name")' self.assertEqual(index_sql, sql_commands[1])
def test_upgrade_v1_to_current(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. with self.env.db_transaction as db: cursor = 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()) target = 6 db_default.schema_version = target self.assertTrue(self.an_sys.environment_needs_upgrade()) self.an_sys.upgrade_environment() self._verify_curr_schema()
def test_to_sql(self): connector = MySQLConnector(self.env) tab = Table('blah', key=('col1', 'col2'))[Column('col1'), Column('col2'), Index(['col2'])] sql = list(connector.to_sql(tab, utf8_size=3)) self.assertEqual(2, len(sql)) self.assertIn(' PRIMARY KEY (`col1`(166),`col2`(166))', sql[0]) self.assertIn(' blah_col2_idx ON blah (`col2`(255))', sql[1]) sql = list(connector.to_sql(tab, utf8_size=4)) self.assertEqual(2, len(sql)) self.assertIn(' PRIMARY KEY (`col1`(125),`col2`(125))', sql[0]) self.assertIn(' blah_col2_idx ON blah (`col2`(191))', sql[1])
def test_upgrade_to_schema_v3(self): # Schema from r3047 - 13-Jan-2008 for announcer-0.2 by Stephen Hansen. 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']), ] ] self._schema_init(schema) # Populate tables with test data. with self.env.db_transaction as db: cursor = db.cursor() cursor.executemany( """ INSERT INTO session_attribute (sid,authenticated,name,value) VALUES (%s,1,%s,%s) """, (('user', 'announcer_email_format_ticket', 'text/html'), ('user', 'announcer_email_format_wiki', 'text/plain'), ('user', 'announcer_specified_email', ''))) cursor.executemany( """ INSERT INTO subscriptions (sid,authenticated,enabled,managed, realm,category,rule,transport) VALUES (%s,%s,1,%s,%s,%s,%s,%s) """, (('user', 1, 'watcher', 'ticket', 'changed', '1', 'email'), ('user', 1, 'watcher', 'wiki', '*', 'WikiStart', 'email'))) self.assertEquals(2, self.an_sys.get_schema_version()) target = 3 db_default.schema_version = target self.assertTrue(self.an_sys.environment_needs_upgrade()) # From r9116 - 25-Sep-2010 for announcer-0.12.1 by Robert Corsaro. # + table 'subscription', 'subscription_attribute' self.an_sys.upgrade_environment() self.assertEquals(target, self.an_sys.get_schema_version())
def create_db(self): ticket_time_table = Table('ticket_time', key='id')[ Column('id', auto_increment=True), Column('ticket', type='int'), Column('time_submitted', type='int'), Column('worker'), Column('submitter'), Column('time_started', type='int'), Column('seconds_worked', type='int'), Column('comments'), Index(['ticket']), Index(['worker']), Index(['time_started'])] create_table(self.env, ticket_time_table) execute_non_query(self.env, """ INSERT INTO system (name, value) VALUES ('trachours.db_version', '1') """)
def test_to_sql(self): connector = MySQLConnector(self.env) tab = Table('blah', key=('col1', 'col2', 'col3', 'col4', 'col5')) \ [Column('col1'), Column('col2'), Column('col3'), Column('col4'), Column('col5'), Column('col6'), Index(['col2', 'col3', 'col4', 'col5'])] sql = list(connector.to_sql(tab, max_bytes=3)) self.assertEqual(2, len(sql)) self.assertIn(' PRIMARY KEY (`col1`(204),`col2`(204),`col3`(204),' '`col4`(204),`col5`(204))', sql[0]) self.assertIn(' `blah_col2_col3_col4_col5_idx` ON `blah` (`col2`(255),' '`col3`(255),`col4`(255),`col5`(255))', sql[1]) sql = list(connector.to_sql(tab, max_bytes=4)) self.assertEqual(2, len(sql)) self.assertIn(' PRIMARY KEY (`col1`(153),`col2`(153),`col3`(153),' '`col4`(153),`col5`(153))', sql[0]) self.assertIn(' `blah_col2_col3_col4_col5_idx` ON `blah` (`col2`(191),' '`col3`(191),`col4`(191),`col5`(191))', sql[1])
def do_db_upgrade(self): db = self.env.get_db_cnx() cursor = db.cursor() try: if self.db_installed_version < 1: contacts_table = Table('contact', key=( 'id', ))[Column('id', type='int', auto_increment=True), Column('first'), Column('last'), Column('position'), Column('email'), Column('phone')] for stmt in to_sql(self.env, contacts_table): cursor.execute(stmt) cursor.execute("UPDATE system SET value = %s WHERE name = %s", (self.db_version, self.db_version_key)) except Exception, e: import traceback print traceback.format_exc(e) db.rollback()
def __init__(self): self.schema1 = [ Table('buildqueue', key=('id', 'owner', 'status'))[ Column('id', type='varchar(25)', size=25), Column('owner', type='varchar(50)', size=50), Column('repository', type='int'), Column('revision', type='varchar(40)', size=40), Column('status', type='int'), Column('priority', type='int'), Column('startdate', type='bigint'), Column('enddate', type='bigint'), Column('description', type='varchar(4096)', size=4096)], Table('builds', key=('id', 'queueid', 'backendkey', 'buildgroup', 'status', 'backendid' ))[Column('id', type='int', auto_increment=True), Column('queueid', type='varchar(25)', size=25), Column('backendkey', type='varchar(25)', size=25), Column('buildgroup', type='varchar(50)', size=50), Column('portname', type='varchar(100)', size=100), Column('pkgversion', type='varchar(25)', size=25), Column('status', type='int'), Column('buildstatus', type='varchar(25)', size=25), Column('buildreason', type='varchar(255)', size=255), Column('buildlog', type='varchar(100)', size=100), Column('wrkdir', type='varchar(100)', size=100), Column('backendid', type='int'), Column('startdate', type='bigint'), Column('enddate', type='bigint'), Column('checkdate', type='bigint')], Table('backends', key=( 'id', 'status'))[Column('id', type='int', auto_increment=True), Column('host', type='varchar(50)', size=50), Column('protocol', type='varchar(10)', size=10), Column('uri', type='varchar(25)', size=25), Column('credentials', type='varchar(50)', size=50), Column('maxparallel', type='int'), Column('status', type='int'), Column('type', type='varchar(25)', size=25)], Table('backendbuilds', key=('id', 'status', 'backendid', 'buildgroup' ))[Column('id', type='int', auto_increment=True), Column('buildgroup', type='varchar(25)', size=25), Column('backendid', type='int'), Column('priority', type='int'), Column('status', type='int'), Column('buildname', type='varchar(30)', size=30)], Table('buildgroups', key=('name'))[ Column('name', type='varchar(50)', size=50), Column('version', type='varchar(25)', size=25), Column('arch', type='varchar(10)', size=10), Column('type', type='varchar(25)', size=25), Column('description', type='varchar(255)', size=255), ], Table('automaticbuildgroups')[ Column('username', type='varchar(50)', size=50), Column('buildgroup', type='varchar(25)', size=25), Column('priority', type='int'), ], ] self.schema2 = [ Table( 'portrepositories', key=('id'))[Column('id', type='int', auto_increment=True), Column('name', type='varchar(255)', size=255), Column('type', type='varchar(25)', size=25), Column('url', type='varchar(255)', size=255), Column('browseurl', type='varchar(255)', size=255), Column('username', type='varchar(50)', size=50)], ] self.db_version_key = 'redports_version' # Increment this whenever there are DB changes self.db_version = 2 self.db_installed_version = None # Check DB to see if we need to add new tables, etc. db = self.env.get_db_cnx() cursor = db.cursor() cursor.execute("SELECT value FROM system WHERE name=%s", (self.db_version_key, )) try: self.db_installed_version = int(cursor.fetchone()[0]) except: # Version did not exists, so the plugin was just installed self.db_installed_version = 0 cursor.execute("INSERT INTO system (name, value) VALUES (%s, %s)", (self.db_version_key, self.db_installed_version)) db.commit() db.close()
import unittest from trac.db.api import DatabaseManager from trac.db.schema import Column, Index, Table from trac.test import EnvironmentStub, mkdtemp from trac.upgrades import db42 from trac.util.datefmt import datetime_now, to_utimestamp, utc VERSION = 42 old_attachment_schema = \ Table('attachment', key=('type', 'id', 'filename'))[ Column('type'), Column('id'), Column('filename'), Column('size', type='int'), Column('time', type='int64'), Column('description'), Column('author'), Column('ipnr')] old_wiki_schema = \ Table('wiki', key=('name', 'version'))[ Column('name'), Column('version', type='int'), Column('time', type='int64'), Column('author'), Column('ipnr'), Column('text'), Column('comment'), Column('readonly', type='int'),
from trac.db.schema import Table, Column, Index from trac.env import IEnvironmentSetupParticipant # Database version identifier for upgrades. db_version = 2 db_version_key = 'fullblog_version' # Database schema schema = [ # Blog posts Table('fullblog_posts', key=('name', 'version'))[Column('name'), Column('version', type='int'), Column('title'), Column('body'), Column('publish_time', type='int'), Column('version_time', type='int'), Column('version_comment'), Column('version_author'), Column('author'), Column('categories'), Index(['version_time'])], # Blog comments Table('fullblog_comments', key=('name', 'number')) [Column('name'), Column('number', type='int'), Column('comment'), Column('author'), Column('time', type='int'), Index(['time'])], ]
from trac.db.schema import Table, Column, Index from trac.env import IEnvironmentSetupParticipant from trac.db.api import DatabaseManager # Database version identifier for upgrades. db_version = 3 # Database schema schema = { 'code_comments': Table('code_comments', key=('id', 'version'))[ Column('id', auto_increment=True), Column('version', type='int'), Column('text'), Column('path'), Column('revision', type='int'), Column('line', type='int'), Column('author'), Column('time', type='int'), Column('type'), Index(['path']), Index(['author']), ], 'code_comments_subscriptions': Table('code_comments_subscriptions', key=('id', 'user', 'type', 'path', 'repos', 'rev'))[ Column('id', auto_increment=True), Column('user'), Column('type'), Column('path'), Column('repos'), Column('rev'),
""" from trac.core import * from trac.db.schema import Table, Column from trac.env import IEnvironmentSetupParticipant __all__ = ['AnotherTracSetup'] # Database version identifier for upgrades. db_version = 1 # Database schema schema = [ # Another tracs tickets Table('anothertrac_tickets', key='ticket')[Column('ticket'), Column('another_trac'), Column('another_ticket')] ] # Create tables def to_sql(env, table): """ Convenience function to get the to_sql for the active connector.""" from trac.db.api import DatabaseManager dm = env.components[DatabaseManager] dc = dm._get_connector()[0] return dc.to_sql(table) def create_tables(cursor, env):
# # This software is licensed as described in the file COPYING, which # you should have received as part of this distribution. # from trac.db.api import DatabaseManager from trac.db.schema import Column, Index, Table 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']), ] ] def do_upgrade(env, ver, cursor): """Changes to subscription db table: - 'subscriptions.destination', 'subscriptions.format' + 'subscriptions.authenticated', 'subscriptions.transport' 'subscriptions.managed' type='int' --> (default == char)
""" from trac.core import * from trac.db.schema import Table, Column, Index from trac.env import IEnvironmentSetupParticipant __all__ = ['ActiveDirectoryAuthPluginSetup'] # Database version identifier for upgrades. db_version = 1 # Database schema schema = [ # Blog posts Table('ad_cache', key=('id'))[Column('id', type='varcahar(32)'), Column('lut', type='int'), Column('data', type='binary'), Index(['id'])], ] # Create tables def to_sql(env, table): """ Convenience function to get the to_sql for the active connector.""" from trac.db.api import DatabaseManager dc = DatabaseManager(env)._get_connector()[0] return dc.to_sql(table) def create_tables(env, db): """ Creates the basic tables as defined by schema.
# # Author: Team5 # from trac.core import * from trac.db.schema import Table, Column, Index # Version of Code Review schema version = 1 #The tables for the Code Review Plugin tables = [ Table('CodeReviews', key='IDReview')[Column('IDReview', auto_increment=True), Column('Author'), Column('Status'), Column('DateCreate', type='int'), Column('Name'), Column('Notes'), ], Table('Reviewers', key=('IDReview', 'Reviewer'))[Column('IDReview', type='int'), Column('Reviewer'), Column('Status', type='int'), Column('Vote', type='int'), ], Table('ReviewFiles', key='IDFile')[Column('IDFile', auto_increment=True), Column('IDReview', type='int'), Column('Path'), Column('LineStart', type='int'), Column('LineEnd', type='int'), Column('Version', type='int'), ], Table('ReviewComments',
"""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', key=('sid', 'authenticated',
def construct_dummy_table(self, n_custom_fields=1): fields = [Column('id')] + [ Column('v%d' % (i + 1)) for i in range(n_custom_fields) ] return Table('dummy_table')[fields]