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]
