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 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_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_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 _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 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 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_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_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_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])
# 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'), Column('notify', type='bool'), Index(['user']), Index(['path']), ],
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. with self.env.db_transaction as db: cursor = 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()) target = 4 db_default.schema_version = target self.assertTrue(self.an_sys.environment_needs_upgrade()) # 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.assertEquals(target, self.an_sys.get_schema_version()) # Check type of priority value. with self.env.db_query as db: cursor = 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
# Database version identifier for upgrades. db_version = 1 # 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'), Index(['path']), Index(['author']), ], } def to_sql(env, table): """ Convenience function to get the to_sql for the active connector.""" dc = DatabaseManager(env)._get_connector()[0] return dc.to_sql(table) def create_tables(env, db): cursor = db.cursor() for table_name in schema: for stmt in to_sql(env, schema[table_name]): cursor.execute(stmt)
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', 'name'))[Column('sid'), Column('authenticated', type='int'), Column('name'), Column('value')], Table('cache', key='id')[Column('id', type='int'), Column('generation', type='int'), Column('key')], # Attachments Table('attachment', key=('type', 'id', 'filename')) [Column('type'), Column('id'),
# idea: # maybe use a view for abstraction; then it is not needed to regex about SQL-Where clause #=============================================================================== # xmail table object # see trac/db_default.py for samples and trac/db/schema.py for implementation of objects XMAIL_TABLE = Table('xmail', key='id')[Column('id', auto_increment=True), Column('filtername'), Column('username'), Column('nextexe', type='int64'), Column('lastsuccessexe', type='int64'), Column('selectfields', type='text'), Column('whereclause', type='text'), Column('interval', type='int'), Column('active', type='int'), Index(['filtername', 'username'], True), #Column('change'), ] #=============================================================================== # Create Table xmail #=============================================================================== def create_table(env): ''' Constructor, see trac/db/postgres_backend.py:95 (method init_db) ''' conn, dummyArgs = DatabaseManager(env).get_connector() db = env.get_read_db() cursor = db.cursor()
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. using the active database connector. """
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. 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 = 2 db_default.schema_version = target self.assertTrue(self.an_sys.environment_needs_upgrade()) # 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.assertEquals(target, self.an_sys.get_schema_version()) self._verify_version_unregistered() with self.env.db_query as db: cursor = db.cursor() cursor.execute("SELECT * FROM subscriptions") columns = [col[0] for col in cursor.cursor.description] self.assertEquals([ 'id', 'sid', 'authenticated', 'enabled', 'managed', 'realm', 'category', 'rule', 'transport' ], columns)
# 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'])], ] # Upgrades def add_timeline_time_indexes(env): """ Add time-based indexes to blog post and comment tables. """
# Database schema schema = [ Table('qa_testplan', key=('id'))[ Column('id', type='int', auto_increment=True), Column('title'), Column('description')], Table('qa_testsuite', key=('id'))[ Column('id', type='int', auto_increment=True), Column('title'), Column('description')], Table('qa_tp_tc_rel', key=('id'))[ Column('id', type='int', auto_increment=True), Column('testplan_id', type='int'), Column('testcase_id', type='int'), Index(['testplan_id', 'testcase_id'], unique=True)], Table('qa_testrun', key=('id'))[ Column('id', type='int', auto_increment=True), Column('testplan_id', type='int'), Column('active', type = 'int'), Column('title'), Column('description'), Index(['testplan_id'])], Table('qa_testcase', key=('id'))[ Column('id', type='int', auto_increment=True), Column('testsuite_id', type='int'), Column('isnegative', type='int'), Column('title'), Column('steps'), Column('acceptance'), Index(['testsuite_id'])],
import coderev.compat from coderev.model import get_reviews_for_ticket DB_NAME = 'coderev' DB_VERSION = 3 schema = [ Table('codereviewer')[ Column('repo', type='text'), Column('changeset', type='text'), Column('status', type='text'), Column('reviewer', type='text'), Column('summary', type='text'), Column('time', type='int64'), Index(['repo', 'changeset', 'time']), ], Table('codereviewer_map', key=['repo', 'changeset', 'ticket'])[ Column('repo', type='text'), Column('changeset', type='text'), Column('ticket', type='text'), Column('time', type='int64'), ], ] class CodeReviewerSystem(Component): """System management for codereviewer plugin.""" implements(IEnvironmentSetupParticipant, IPermissionRequestor)
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'), Index(['time'])] old_schema = (old_attachment_schema, old_wiki_schema) new_attachment_schema = copy.deepcopy(old_attachment_schema) new_attachment_schema.remove_columns(('ipnr', )) new_wiki_schema = copy.deepcopy(old_wiki_schema) new_wiki_schema.remove_columns(('ipnr', )) new_schema = (new_attachment_schema, new_wiki_schema) class UpgradeTestCase(unittest.TestCase): def setUp(self): self.env = EnvironmentStub(path=mkdtemp()) self.dbm = DatabaseManager(self.env) with self.env.db_transaction: self.dbm.drop_tables(new_schema)
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) """ cursor.execute(""" CREATE TEMPORARY TABLE subscriptions_old AS SELECT * FROM subscriptions