Author: jure
Date: Mon Feb 25 14:56:15 2013
New Revision: 1449736
URL: http://svn.apache.org/r1449736
Log:
Translate ticket_custom, ticket_change and attachment tables, updated test
cases for database cursor
Modified:
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/api.py
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/dbcursor.py
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/db/cursor.py
Modified:
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/api.py
URL:
http://svn.apache.org/viewvc/incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/api.py?rev=1449736&r1=1449735&r2=1449736&view=diff
==============================================================================
---
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/api.py
(original)
+++
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/api.py
Mon Feb 25 14:56:15 2013
@@ -69,7 +69,11 @@ class MultiProductSystem(Component):
for mcls in (Product, ProductResourceMap)]
# Tables which should be migrated (extended with 'product' column)
- MIGRATE_TABLES = ['enum', 'component', 'milestone', 'version',
'permission', 'wiki']
+ MIGRATE_TABLES = ['enum', 'component', 'milestone', 'version',
+ 'permission',
+ 'wiki',
+ 'report',
+ ]
def get_version(self):
@@ -139,9 +143,13 @@ class MultiProductSystem(Component):
import trac.db_default
DEFAULT_PRODUCT = 'default'
+ TICKET_TABLES = ['ticket_change', 'ticket_custom',
+ 'attachment',
+ ]
# extend trac default schema by adding product column and
extending key with product
- table_defs = [copy.deepcopy(t) for t in trac.db_default.schema
if t.name in self.MIGRATE_TABLES]
+ table_defs = [copy.deepcopy(t) for t in trac.db_default.schema
+ if t.name in
self.MIGRATE_TABLES + TICKET_TABLES]
for t in table_defs:
t.columns.append(Column('product'))
if isinstance(t.key, list):
@@ -154,10 +162,9 @@ class MultiProductSystem(Component):
(t.name, t.key, PLUGIN_NAME,
db_installed_version, 3))
table_columns = dict()
for table in table_defs:
- table_columns[table.name] = filter(lambda column: column
!= 'product',
- [column.name for
column in
- list(filter(lambda
t: t.name == table.name,
-
table_defs)[0].columns)])
+ table_columns[table.name] = [c for c in [column.name for
column in
+ [t for t in
table_defs if t.name == table.name][0].columns]
+ if c !=
'product']
self.log.info("Creating default product")
default_product = Product(self.env)
default_product.update_field_dict({'prefix': DEFAULT_PRODUCT,
@@ -171,27 +178,68 @@ class MultiProductSystem(Component):
db("""UPDATE ticket SET product='%s'
WHERE product=''""" % DEFAULT_PRODUCT)
- self.log.info("Migrating tables to a new schema")
- for table in self.MIGRATE_TABLES:
- cols = ','.join(table_columns[table])
+ def create_temp_table(table):
+ table_temp_name = '%s_temp' % table
+ if table == 'report':
+ cols = ','.join([c for c in table_columns[table] if c
!= 'id'])
+ else:
+ cols = ','.join(table_columns[table])
self.log.info("Migrating table '%s' to a new schema",
table)
- db("CREATE TABLE %s_temp AS SELECT %s FROM %s" %
- (table, cols, table))
+ db("CREATE TABLE %s AS SELECT %s FROM %s" %
+ (table_temp_name, cols, table))
db("DROP TABLE %s" % table)
db_connector, _ =
DatabaseManager(self.env)._get_connector()
- table_schema = filter(lambda t: t.name == table,
table_defs)[0]
+ table_schema = [t for t in table_defs if t.name ==
table][0]
for sql in db_connector.to_sql(table_schema):
db(sql)
- products = Product.select(self.env)
- for product in products:
- self.log.info("Populating table '%s' for product '%s'
('%s')", table, product.name, product.prefix)
- db("INSERT INTO %s (%s, product) SELECT %s,'%s' FROM
%s_temp" %
- (table, cols, cols, product.prefix, table))
+ return table_temp_name, cols
+
+ def drop_temp_table(table):
+ db("DROP TABLE %s" % table)
+
+ self.log.info("Migrating system tables to a new schema")
+ for table in self.MIGRATE_TABLES:
+ temp_table_name, cols = create_temp_table(table)
+ if table == 'wiki':
+ self.log.info("Populating table '%s'", table)
+ db("INSERT INTO %s (%s, product) SELECT %s,'' FROM %s"
%
+ (table, cols, cols, temp_table_name))
+ else:
+ products = Product.select(self.env)
+ for product in products:
+ self.log.info("Populating table '%s' for product
'%s' ('%s')",
+ table, product.name, product.prefix)
+ db("INSERT INTO %s (%s, product) SELECT %s,'%s'
FROM %s" %
+ (table, cols, cols, product.prefix,
temp_table_name))
if table == 'permission':
self.log.info("Populating table '%s' for global
scope", table)
- db("INSERT INTO %s (%s, product) SELECT %s,'%s' FROM
%s_temp" %
- (table, cols, cols, '', table))
- db("DROP TABLE %s_temp" % table)
+ db("INSERT INTO %s (%s, product) SELECT %s,'%s' FROM
%s" %
+ (table, cols, cols, '', temp_table_name))
+ drop_temp_table(temp_table_name)
+
+ # Update ticket related tables
+ # Upgrade schema
+ self.log.info("Migrating ticket tables to a new schema")
+ for table in TICKET_TABLES:
+ temp_table_name, cols = create_temp_table(table)
+ db("INSERT INTO %s (%s, product) SELECT %s,'' FROM %s" %
+ (table, cols, cols, temp_table_name))
+ drop_temp_table(temp_table_name)
+
+ # Update product column based on ticket product
+ for table in TICKET_TABLES:
+ if table == 'attachment':
+ db("""UPDATE attachment
+ SET product=(SELECT ticket.product FROM ticket
WHERE ticket.id=attachment.id)
+ WHERE type='ticket'""")
+ else:
+ db("""UPDATE %s
+ SET product=(SELECT ticket.product FROM ticket
WHERE ticket.id=%s.ticket)""" %
+ (table, table))
+ db("""UPDATE attachment
+ SET product=(SELECT wiki.product FROM wiki WHERE
wiki.name=attachment.id)
+ WHERE type='wiki'""")
+
db_installed_version = self._update_db_version(db, 3)
if db_installed_version < 4:
Modified:
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/dbcursor.py
URL:
http://svn.apache.org/viewvc/incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/dbcursor.py?rev=1449736&r1=1449735&r2=1449736&view=diff
==============================================================================
---
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/dbcursor.py
(original)
+++
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/multiproduct/dbcursor.py
Mon Feb 25 14:56:15 2013
@@ -28,13 +28,17 @@ __all__ = ['BloodhoundIterableCursor', '
SKIP_TABLES = ['system', 'auth_cookie',
'session', 'session_attribute',
'cache',
- 'attachment', 'repository', 'revision', 'node_change',
- 'ticket_change', 'ticket_custom',
- 'report',
+ 'repository', 'revision', 'node_change',
'bloodhound_product', 'bloodhound_productresourcemap',
'bloodhound_productconfig',
'sqlite_master'
]
-TRANSLATE_TABLES = ['ticket', 'enum', 'component', 'milestone', 'version',
'permission', 'wiki']
+TRANSLATE_TABLES = ['ticket', 'ticket_change', 'ticket_custom',
+ 'attachment',
+ 'enum', 'component', 'milestone', 'version',
+ 'permission',
+ 'wiki',
+ 'report',
+ ]
PRODUCT_COLUMN = 'product'
GLOBAL_PRODUCT = ''
@@ -310,7 +314,7 @@ class BloodhoundProductSQLTranslate(obje
def _select_expression_tokens(self, parent, first_token, end_words):
if isinstance(first_token, Types.IdentifierList):
- return first_token, [first_token]
+ return first_token, [list(first_token.flatten())]
tokens = list()
current_list = list()
current_token = first_token
Modified:
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/db/cursor.py
URL:
http://svn.apache.org/viewvc/incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/db/cursor.py?rev=1449736&r1=1449735&r2=1449736&view=diff
==============================================================================
---
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/db/cursor.py
(original)
+++
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/db/cursor.py
Mon Feb 25 14:56:15 2013
@@ -26,26 +26,6 @@ data = {
# non-translated SELECTs
'system_select_nontranslated' : [
(
-"""SELECT TYPE, id,
- filename,
- time,
- description,
- author
- FROM attachment
- WHERE time > %s
- AND time < %s
- AND TYPE = %s""",
-"""SELECT TYPE, id,
- filename,
- time,
- description,
- author
- FROM attachment
- WHERE time > %s
- AND time < %s
- AND TYPE = %s"""
- ),
- (
"""SELECT id,
name,
value
@@ -74,6 +54,26 @@ data = {
# translated SELECTs
'system_select_translated' : [
(
+"""SELECT TYPE, id,
+ filename,
+ time,
+ description,
+ author
+ FROM attachment
+ WHERE time > %s
+ AND time < %s
+ AND TYPE = %s""",
+"""SELECT TYPE, id,
+ filename,
+ time,
+ description,
+ author
+ FROM (SELECT * FROM attachment WHERE product="PRODUCT") AS attachment
+ WHERE time > %s
+ AND time < %s
+ AND TYPE = %s"""
+ ),
+ (
"""SELECT name,
due,
completed,
@@ -131,7 +131,7 @@ data = {
tc.field,
tc.oldvalue,
tc.newvalue
- FROM ticket_change tc
+ FROM (SELECT * FROM ticket_change WHERE product="PRODUCT") AS tc
INNER JOIN (SELECT * FROM ticket WHERE product="PRODUCT") AS t ON t.id
= tc.ticket
AND tc.time>=1351375199999999
AND tc.time<=1354057199999999
@@ -576,7 +576,7 @@ data = {
reporter AS _reporter
FROM (SELECT * FROM ticket WHERE product="PRODUCT") AS t
LEFT JOIN (SELECT * FROM enum WHERE product="PRODUCT") AS p ON
p.name = t.priority AND p.type = 'priority'
- LEFT JOIN ticket_change ON tc.ticket = t.id AND tc.author = %s
+ LEFT JOIN (SELECT * FROM ticket_change WHERE product="PRODUCT") AS
tc ON tc.ticket = t.id AND tc.author = %s
AND tc.field = 'comment'
WHERE t.status <> 'closed'
AND (owner = %s OR reporter = %s OR author = %s)
@@ -618,16 +618,6 @@ data = {
# non-translated INSERTs
'system_insert_nontranslated' : [
(
-"""INSERT INTO ticket_custom (ticket, name, value)
- SELECT id, 'totalhours', '0' FROM ticket WHERE id NOT IN (
- SELECT ticket from ticket_custom WHERE name='totalhours'
- )""",
-"""INSERT INTO ticket_custom (ticket, name, value)
- SELECT id, 'totalhours', '0' FROM (SELECT * FROM ticket WHERE
product="PRODUCT") AS ticket WHERE id NOT IN (
- SELECT ticket from ticket_custom WHERE name='totalhours'
- )"""
- ),
- (
"""INSERT INTO session VALUES (%s,%s,0)""",
"""INSERT INTO session VALUES (%s,%s,0)"""
),
@@ -637,11 +627,21 @@ data = {
'system_insert_translated' : [
(
"""INSERT INTO ticket_custom (ticket, name, value)
+ SELECT id, 'totalhours', '0' FROM ticket WHERE id NOT IN (
+ SELECT ticket from ticket_custom WHERE name='totalhours'
+ )""",
+"""INSERT INTO ticket_custom (ticket, name, value, product)
+ SELECT id, 'totalhours', '0', product FROM (SELECT * FROM ticket
WHERE product="PRODUCT") AS ticket WHERE id NOT IN (
+ SELECT ticket from (SELECT * FROM ticket_custom WHERE
product="PRODUCT") AS ticket_custom WHERE name='totalhours'
+ )"""
+ ),
+ (
+"""INSERT INTO ticket_custom (ticket, name, value)
SELECT id, 'totalhours', '0' FROM ticket WHERE id NOT IN (
SELECT ticket from ticket_custom WHERE
name='totalhours')""",
-"""INSERT INTO ticket_custom (ticket, name, value)
- SELECT id, 'totalhours', '0' FROM (SELECT * FROM ticket
WHERE product="PRODUCT") AS ticket WHERE id NOT IN (
- SELECT ticket from ticket_custom WHERE
name='totalhours')"""
+"""INSERT INTO ticket_custom (ticket, name, value, product)
+ SELECT id, 'totalhours', '0', product FROM (SELECT *
FROM ticket WHERE product="PRODUCT") AS ticket WHERE id NOT IN (
+ SELECT ticket from (SELECT * FROM ticket_custom WHERE
product="PRODUCT") AS ticket_custom WHERE name='totalhours')"""
),
(
"""INSERT INTO session (sid, last_visit, authenticated)
@@ -707,7 +707,7 @@ data = {
ORDER BY time DESC LIMIT 1)
WHERE id=%s""",
"""UPDATE ticket SET changetime=(
- SELECT time FROM ticket_change WHERE ticket=%s
+ SELECT time FROM (SELECT * FROM ticket_change WHERE
product="PRODUCT") AS ticket_change WHERE ticket=%s
UNION
SELECT time FROM (
SELECT time FROM (SELECT * FROM ticket WHERE
product="PRODUCT") AS ticket WHERE id=%s LIMIT 1) AS t
@@ -756,6 +756,32 @@ data = {
SET
id_project='%s' WHERE product='PRODUCT' AND
milestone='%s'"""
),
+ (
+"""UPDATE ticket_change SET newvalue=%s
+ WHERE ticket=%s and author=%s and time=%s and
field=%s""",
+"""UPDATE ticket_change SET newvalue=%s
+ WHERE product='PRODUCT' AND ticket=%s and
author=%s and time=%s and field=%s"""
+ ),
+ (
+"""UPDATE ticket_change SET oldvalue=%s, newvalue=%s
+ WHERE ticket=%s and author=%s and time=%s and
field=%s""",
+"""UPDATE ticket_change SET oldvalue=%s, newvalue=%s
+ WHERE product='PRODUCT' AND ticket=%s and
author=%s and time=%s and field=%s"""
+ ),
+ (
+"""UPDATE
+ ticket_custom
+ SET
+ value = '%s'
+ WHERE
+ name = 'project' AND value = '%s'""",
+"""UPDATE
+ ticket_custom
+ SET
+ value = '%s'
+ WHERE
+ product='PRODUCT' AND name = 'project' AND
value = '%s'"""
+ ),
],
# non-translated UPDATEs
@@ -784,32 +810,6 @@ data = {
SET time=%s
WHERE cookie=%s"""
),
- (
-"""UPDATE ticket_change SET newvalue=%s
- WHERE ticket=%s and author=%s and time=%s and
field=%s""",
-"""UPDATE ticket_change SET newvalue=%s
- WHERE ticket=%s and author=%s and time=%s and
field=%s"""
- ),
- (
-"""UPDATE ticket_change SET oldvalue=%s, newvalue=%s
- WHERE ticket=%s and author=%s and time=%s and
field=%s""",
-"""UPDATE ticket_change SET oldvalue=%s, newvalue=%s
- WHERE ticket=%s and author=%s and time=%s and
field=%s"""
- ),
- (
-"""UPDATE
- ticket_custom
- SET
- value = '%s'
- WHERE
- name = 'project' AND value = '%s'""",
-"""UPDATE
- ticket_custom
- SET
- value = '%s'
- WHERE
- name = 'project' AND value = '%s'"""
- ),
],
# custom (plugin) table UPDATEs
