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
