You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@bloodhound.apache.org by ju...@apache.org on 2013/01/17 15:14:01 UTC
svn commit: r1434677 -
/incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py
Author: jure
Date: Thu Jan 17 14:14:01 2013
New Revision: 1434677
URL: http://svn.apache.org/viewvc?rev=1434677&view=rev
Log:
#288 unit tests ... more will follow
Added:
incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py
Added: incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py
URL: http://svn.apache.org/viewvc/incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py?rev=1434677&view=auto
==============================================================================
--- incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py (added)
+++ incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py Thu Jan 17 14:14:01 2013
@@ -0,0 +1,1116 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+"""Tests for multiproduct/dbcursor.py"""
+
+import unittest
+from multiproduct.dbcursor import BloodhoundProductSQLTranslate, SKIP_TABLES, TRANSLATE_TABLES, PRODUCT_COLUMN
+
+# Test case data, each section consists of list of tuples of original and correctly translated SQL statements
+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
+ FROM repository
+ WHERE name IN ('alias',
+ 'description',
+ 'dir',
+ 'hidden',
+ 'name',
+ 'type',
+ 'url')""",
+"""SELECT id,
+ name,
+ value
+FROM repository
+WHERE name IN ('alias',
+ 'description',
+ 'dir',
+ 'hidden',
+ 'name',
+ 'type',
+ 'url')"""
+ ),
+ ],
+
+ # translated SELECTs
+ 'system_select_translated': [
+ (
+"""SELECT name,
+ due,
+ completed,
+ description
+ FROM milestone
+ WHERE name=%s""",
+"""SELECT name,
+ due,
+ completed,
+ description
+FROM
+ (SELECT *
+ FROM milestone
+ WHERE product="PRODUCT") AS milestone
+WHERE name=%s"""
+ ),
+ (
+"""SELECT COALESCE(component, ''),
+ count(COALESCE(component, ''))
+ FROM ticket
+ GROUP BY COALESCE(component, '')""",
+"""SELECT COALESCE(component, ''),
+ count(COALESCE(component, ''))
+FROM
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS ticket
+GROUP BY COALESCE(component, '')"""
+ ),
+ (
+"""SELECT id, time, reporter, TYPE, summary,
+ description
+ FROM ticket
+ WHERE time>=%s
+ AND time<=%s""",
+"""SELECT id, time, reporter, TYPE, summary,
+ description
+FROM
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS ticket
+WHERE time>=%s
+ AND time<=%s"""
+ ),
+ (
+"""SELECT t.id,
+ tc.time,
+ tc.author,
+ t.type,
+ t.summary,
+ tc.field,
+ tc.oldvalue,
+ tc.newvalue
+ FROM ticket_change tc
+ INNER JOIN ticket t ON t.id = tc.ticket
+ AND tc.time>=1351375199999999
+ AND tc.time<=1354057199999999
+ ORDER BY tc.time""",
+"""SELECT t.id,
+ tc.time,
+ tc.author,
+ t.type,
+ t.summary,
+ tc.field,
+ tc.oldvalue,
+ tc.newvalue
+FROM ticket_change tc
+INNER JOIN
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS t ON t.id = tc.ticket
+AND tc.time>=1351375199999999
+AND tc.time<=1354057199999999
+ORDER BY tc.time"""
+ ),
+ (
+"""SELECT COUNT(*)
+ FROM
+ (SELECT t.id AS id,
+ t.summary AS summary,
+ t.owner AS OWNER,
+ t.status AS status,
+ t.priority AS priority,
+ t.milestone AS milestone,
+ t.time AS time,
+ t.changetime AS changetime,
+ priority.value AS priority_value
+ FROM ticket AS t
+ LEFT OUTER JOIN enum AS priority ON (priority.type='priority'
+ AND priority.name=priority)
+ LEFT OUTER JOIN milestone ON (milestone.name=milestone)
+ WHERE ((COALESCE(t.status,'')!=%s)
+ AND (COALESCE(t.OWNER,'')=%s))
+ ORDER BY COALESCE(t.milestone,'')='',
+ COALESCE(milestone.completed,0)=0,
+ milestone.completed,
+ COALESCE(milestone.due,0)=0,
+ milestone.due,
+ t.milestone,
+ COALESCE(priority.value,'')='' DESC,CAST(priority.value AS integer) DESC,t.id) AS x""",
+"""SELECT COUNT(*)
+FROM
+ (SELECT t.id AS id,
+ t.summary AS summary,
+ t.owner AS OWNER,
+ t.status AS status,
+ t.priority AS priority,
+ t.milestone AS milestone,
+ t.time AS time,
+ t.changetime AS changetime,
+ priority.value AS priority_value
+ FROM
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS t
+ LEFT OUTER JOIN
+ (SELECT *
+ FROM enum
+ WHERE product="PRODUCT") AS priority ON (priority.type='priority'
+ AND priority.name=priority)
+ LEFT OUTER JOIN
+ (SELECT *
+ FROM milestone
+ WHERE product="PRODUCT") AS milestone ON (milestone.name=milestone)
+ WHERE ((COALESCE(t.status,'')!=%s)
+ AND (COALESCE(t.OWNER,'')=%s))
+ ORDER BY COALESCE(t.milestone,'')='',
+ COALESCE(milestone.completed,0)=0,
+ milestone.completed,
+ COALESCE(milestone.due,0)=0,
+ milestone.due,
+ t.milestone,
+ COALESCE(priority.value,'')='' DESC,CAST(priority.value AS integer) DESC,t.id) AS x"""
+ ),
+ (
+"""SELECT t.id AS id,
+ t.summary AS summary,
+ t.owner AS OWNER,
+ t.status AS status,
+ t.priority AS priority,
+ t.milestone AS milestone,
+ t.time AS time,
+ t.changetime AS changetime,
+ priority.value AS priority_value
+ FROM ticket AS t
+ LEFT OUTER JOIN enum AS priority ON (priority.type='priority'
+ AND priority.name=priority)
+ LEFT OUTER JOIN milestone ON (milestone.name=milestone)
+ WHERE ((COALESCE(t.status,'')!=%s)
+ AND (COALESCE(t.OWNER,'')=%s))
+ ORDER BY COALESCE(t.milestone,'')='',
+ COALESCE(milestone.completed,0)=0,
+ milestone.completed,
+ COALESCE(milestone.due,0)=0,
+ milestone.due,
+ t.milestone,
+ COALESCE(priority.value,'')='' DESC,
+ CAST(priority.value AS integer) DESC,t.id""",
+"""SELECT t.id AS id,
+ t.summary AS summary,
+ t.owner AS OWNER,
+ t.status AS status,
+ t.priority AS priority,
+ t.milestone AS milestone,
+ t.time AS time,
+ t.changetime AS changetime,
+ priority.value AS priority_value
+FROM
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS t
+LEFT OUTER JOIN
+ (SELECT *
+ FROM enum
+ WHERE product="PRODUCT") AS priority ON (priority.type='priority'
+ AND priority.name=priority)
+LEFT OUTER JOIN
+ (SELECT *
+ FROM milestone
+ WHERE product="PRODUCT") AS milestone ON (milestone.name=milestone)
+WHERE ((COALESCE(t.status,'')!=%s)
+ AND (COALESCE(t.OWNER,'')=%s))
+ORDER BY COALESCE(t.milestone,'')='',
+ COALESCE(milestone.completed,0)=0,
+ milestone.completed,
+ COALESCE(milestone.due,0)=0,
+ milestone.due,
+ t.milestone,
+ COALESCE(priority.value,'')='' DESC, CAST(priority.value AS integer) DESC,t.id"""
+ ),
+ (
+"""SELECT COUNT(*)
+ FROM
+ (SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
+ time AS created,
+ changetime AS _changetime,
+ description AS _description,
+ reporter AS _reporter
+ FROM ticket t
+ LEFT JOIN enum p ON p.name = t.priority
+ AND p.TYPE = 'priority'
+ WHERE status <> 'closed'
+ ORDER BY CAST(p.value AS integer),
+ milestone,
+ t.TYPE, time ) AS tab""",
+"""SELECT COUNT(*)
+FROM
+ (SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
+ time AS created,
+ changetime AS _changetime,
+ description AS _description,
+ 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'
+ WHERE status <> 'closed'
+ ORDER BY CAST(p.value AS integer),
+ milestone,
+ t.TYPE, time) AS tab"""
+ ),
+ (
+"""SELECT COUNT(*)
+ FROM
+ (SELECT t.id AS id,
+ t.summary AS summary,
+ t.status AS status,
+ t.type AS TYPE,
+ t.priority AS priority,
+ t.product AS product,
+ t.milestone AS milestone,
+ t.time AS time,
+ t.changetime AS changetime,
+ t.owner AS OWNER,
+ priority.value AS priority_value
+ FROM ticket AS t
+ LEFT OUTER JOIN enum AS priority ON (priority.TYPE='priority'
+ AND priority.name=priority)
+ WHERE ((COALESCE(t.status,'')!=%s)
+ AND (COALESCE(t.OWNER,'')=%s))
+ ORDER BY COALESCE(priority.value,'')='',
+ CAST(priority.value AS integer),
+ t.id) AS x""",
+"""SELECT COUNT(*)
+FROM
+ (SELECT t.id AS id,
+ t.summary AS summary,
+ t.status AS status,
+ t.type AS TYPE,
+ t.priority AS priority,
+ t.product AS product,
+ t.milestone AS milestone,
+ t.time AS time,
+ t.changetime AS changetime,
+ t.owner AS OWNER,
+ priority.value AS priority_value
+ FROM
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS t
+ LEFT OUTER JOIN
+ (SELECT *
+ FROM enum
+ WHERE product="PRODUCT") AS priority ON (priority.TYPE='priority'
+ AND priority.name=priority)
+ WHERE ((COALESCE(t.status,'')!=%s)
+ AND (COALESCE(t.OWNER,'')=%s))
+ ORDER BY COALESCE(priority.value,'')='',
+ CAST(priority.value AS integer),
+ t.id) AS x"""
+ ),
+ (
+"""SELECT t.id AS id,
+ t.summary AS summary,
+ t.status AS status,
+ t.type AS TYPE,
+ t.priority AS priority,
+ t.product AS product,
+ t.milestone AS milestone,
+ t.time AS time,
+ t.changetime AS changetime,
+ t.owner AS OWNER,
+ priority.value AS priority_value
+ FROM ticket AS t
+ LEFT OUTER JOIN enum AS priority ON (priority.TYPE='priority'
+ AND priority.name=priority)
+ WHERE ((COALESCE(t.status,'')!=%s)
+ AND (COALESCE(t.OWNER,'')=%s))
+ ORDER BY COALESCE(priority.value,'')='',
+ CAST(priority.value AS integer),
+ t.id""",
+"""SELECT t.id AS id,
+ t.summary AS summary,
+ t.status AS status,
+ t.type AS TYPE,
+ t.priority AS priority,
+ t.product AS product,
+ t.milestone AS milestone,
+ t.time AS time,
+ t.changetime AS changetime,
+ t.owner AS OWNER,
+ priority.value AS priority_value
+FROM
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS t
+LEFT OUTER JOIN
+ (SELECT *
+ FROM enum
+ WHERE product="PRODUCT") AS priority ON (priority.TYPE='priority'
+ AND priority.name=priority)
+WHERE ((COALESCE(t.status,'')!=%s)
+ AND (COALESCE(t.OWNER,'')=%s))
+ORDER BY COALESCE(priority.value,'')='',
+ CAST(priority.value AS integer),
+ t.id"""
+ ),
+ (
+"""SELECT *
+ FROM
+ (SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
+ time AS created,
+ changetime AS _changetime,
+ description AS _description,
+ reporter AS _reporter
+ FROM ticket t
+ LEFT JOIN enum p ON p.name = t.priority
+ AND p.TYPE = 'priority'
+ WHERE status <> 'closed'
+ ORDER BY CAST(p.value AS integer),
+ milestone,
+ t.TYPE, time ) AS tab LIMIT 1""",
+"""SELECT *
+FROM
+ (SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
+ time AS created,
+ changetime AS _changetime,
+ description AS _description,
+ 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'
+ WHERE status <> 'closed'
+ ORDER BY CAST(p.value AS integer),
+ milestone,
+ t.TYPE, time) AS tab LIMIT 1"""
+ ),
+ (
+"""SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
+ time AS created,
+ changetime AS _changetime,
+ description AS _description,
+ reporter AS _reporter
+ FROM ticket t
+ LEFT JOIN enum p ON p.name = t.priority
+ AND p.TYPE = 'priority'
+ WHERE status <> 'closed'
+ ORDER BY CAST(p.value AS integer),
+ milestone,
+ t.TYPE, time""",
+"""SELECT p.value AS __color__, id AS ticket, summary, component, VERSION, milestone, t.type AS TYPE, OWNER, status,
+ time AS created,
+ changetime AS _changetime,
+ description AS _description,
+ 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'
+WHERE status <> 'closed'
+ORDER BY CAST(p.value AS integer),
+ milestone,
+ t.TYPE, time"""
+ ),
+ (
+"""SELECT COALESCE(version, '') ,
+ count(COALESCE(version, ''))
+ FROM
+ (SELECT t.id AS id,
+ t.summary AS summary,
+ t.owner AS owner,
+ t.type AS type,
+ t.status AS status,
+ t.priority AS priority,
+ t.milestone AS milestone,
+ t.version AS version,
+ t.time AS time,
+ t.changetime AS changetime,
+ t.product AS product,
+ priority.value AS priority_value
+ FROM
+ (SELECT *
+ FROM ticket
+ WHERE product="default") AS t
+ LEFT OUTER JOIN
+ (SELECT *
+ FROM enum
+ WHERE product="default") AS priority ON (priority.type='priority'
+ AND priority.name=priority)
+ LEFT OUTER JOIN
+ (SELECT *
+ FROM version
+ WHERE product="default") AS version ON (version.name=version)
+ WHERE ((COALESCE(t.product,'')='default'))
+ ORDER BY COALESCE(t.version,'')='',
+ COALESCE(version.time,0)=0,version.time,
+ t.version,COALESCE(priority.value,'')='',
+ CAST(priority.value AS integer),
+ t.id) AS foo
+ GROUP BY COALESCE(version, '')""",
+"""SELECT COALESCE(version, '') ,
+ count(COALESCE(version, ''))
+FROM
+ (SELECT t.id AS id,
+ t.summary AS summary,
+ t.owner AS owner,
+ t.type AS type,
+ t.status AS status,
+ t.priority AS priority,
+ t.milestone AS milestone,
+ t.version AS version,
+ t.time AS time,
+ t.changetime AS changetime,
+ t.product AS product,
+ priority.value AS priority_value
+ FROM
+ (SELECT *
+ FROM
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS ticket
+ WHERE product="default") AS t
+ LEFT OUTER JOIN
+ (SELECT *
+ FROM
+ (SELECT *
+ FROM enum
+ WHERE product="PRODUCT") AS enum
+ WHERE product="default") AS priority ON (priority.type='priority'
+ AND priority.name=priority)
+ LEFT OUTER JOIN
+ (SELECT *
+ FROM
+ (SELECT *
+ FROM version
+ WHERE product="PRODUCT") AS version
+ WHERE product="default") AS version ON (version.name=version)
+ WHERE ((COALESCE(t.product,'')='default'))
+ ORDER BY COALESCE(t.version,'')='',
+ COALESCE(version.time,0)=0,version.time,
+ t.version,COALESCE(priority.value,'')='',
+ CAST(priority.value AS integer),
+ t.id) AS foo
+GROUP BY COALESCE(version, '')"""
+ ),
+ (
+"""SELECT w1.name, w1.time, w1.author, w1.text
+ FROM wiki w1,(SELECT name, max(version) AS ver
+ FROM wiki GROUP BY name) w2
+ WHERE w1.version = w2.ver AND w1.name = w2.name
+ AND (w1.name LIKE %s ESCAPE '/' OR w1.author LIKE %s ESCAPE '/' OR w1.text LIKE %s ESCAPE '/')""",
+"""SELECT w1.name,
+ w1.time,
+ w1.author,
+ w1.text
+FROM
+ (SELECT *
+ FROM wiki
+ WHERE product="PRODUCT") AS w1,
+
+ (SELECT name,
+ max(version) AS ver
+ FROM
+ (SELECT *
+ FROM wiki
+ WHERE product="PRODUCT") AS wiki
+ GROUP BY name) AS w2
+WHERE w1.version = w2.ver
+ AND w1.name = w2.name
+ AND (w1.name LIKE %s ESCAPE '/'
+ OR w1.author LIKE %s ESCAPE '/'
+ OR w1.text LIKE %s ESCAPE '/')"""
+ ),
+ ],
+
+ # custom table SELECTs
+ 'custom_select' :
+ [
+ (
+"""SELECT bklg_id, count(*) as total
+ FROM backlog_ticket
+ WHERE tkt_order IS NULL OR tkt_order > -1
+ GROUP BY bklg_id
+""",
+"""SELECT bklg_id,
+ count(*) as total
+FROM
+ (SELECT *
+ FROM PRODUCT_backlog_ticket) AS backlog_ticket
+WHERE tkt_order IS NULL
+ OR tkt_order > -1
+GROUP BY bklg_id
+"""
+ ),
+ (
+"""SELECT bt.bklg_id, t.status, count(*) as total
+ FROM backlog_ticket bt, ticket t
+ WHERE t.id = bt.tkt_id
+ AND (bt.tkt_order IS NULL OR bt.tkt_order > -1)
+ GROUP BY bklg_id, status""",
+"""SELECT bt.bklg_id,
+ t.status,
+ count(*) as total
+FROM
+ (SELECT *
+ FROM PRODUCT_backlog_ticket) AS bt,
+
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS t
+WHERE t.id = bt.tkt_id
+ AND (bt.tkt_order IS NULL
+ OR bt.tkt_order > -1)
+GROUP BY bklg_id,
+ status"""
+ ),
+ ],
+
+ # 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)"""
+ ),
+ ],
+
+ # translated INSERTs
+ '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)
+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 (sid, last_visit, authenticated)
+ SELECT distinct s.sid,COALESCE(%s,0),s.authenticated
+ FROM session_old AS s LEFT JOIN session_old AS s2
+ ON (s.sid=s2.sid AND s2.var_name='last_visit')
+ WHERE s.sid IS NOT NULL""",
+"""INSERT INTO session (sid,
+ last_visit,
+ authenticated)
+SELECT distinct s.sid,
+ COALESCE(%s,0),
+ s.authenticated
+FROM
+ (SELECT *
+ FROM PRODUCT_session_old) AS s
+LEFT JOIN
+ (SELECT *
+ FROM PRODUCT_session_old) AS s2 ON (s.sid=s2.sid
+ AND s2.var_name='last_visit')
+WHERE s.sid IS NOT NULL"""
+ ),
+ (
+"""INSERT INTO session_attribute (sid, authenticated, name, value)
+ SELECT s.sid, s.authenticated, s.var_name, s.var_value
+ FROM session_old s
+ WHERE s.var_name <> 'last_visit' AND s.sid IS NOT NULL""",
+"""INSERT INTO session_attribute (sid, authenticated, name, value)
+SELECT s.sid,
+ s.authenticated,
+ s.var_name,
+ s.var_value
+FROM
+ (SELECT *
+ FROM PRODUCT_session_old) AS s
+WHERE s.var_name <> 'last_visit'
+ AND s.sid IS NOT NULL"""
+ ),
+ (
+"""INSERT INTO wiki(version, name, time, author, ipnr, text)
+ SELECT 1 + COALESCE(max(version), 0), %s, %s, 'trac',
+ '127.0.0.1', %s FROM wiki WHERE name=%s""",
+"""INSERT INTO wiki(product, version, name, time, author, ipnr, text)
+SELECT product,
+ 1 + COALESCE(max(version), 0),
+ %s,
+ %s,
+ 'trac',
+ '127.0.0.1',
+ %s
+FROM
+ (SELECT *
+ FROM wiki
+ WHERE product="PRODUCT") AS wiki
+WHERE name=%s"""
+ ),
+ ],
+
+ 'custom_insert' : [
+ (
+"""INSERT INTO node_change (rev,path,kind,change,base_path,base_rev)
+ SELECT rev,path,kind,change,base_path,base_rev FROM node_change_old""",
+"""INSERT INTO node_change (rev,path,kind,change,base_path,base_rev)
+SELECT rev,
+ path,
+ kind,
+ change,
+ base_path,
+ base_rev
+FROM
+ (SELECT *
+ FROM PRODUCT_node_change_old) AS node_change_old"""
+ ),
+ ],
+
+ # translated UPDATEs
+ 'system_update_translated' : [
+ (
+"""UPDATE ticket SET changetime=%s WHERE id=%s""",
+"""UPDATE ticket
+SET changetime=%s
+WHERE product='PRODUCT'
+ AND id=%s"""
+ ),
+ (
+"""UPDATE ticket SET changetime=(
+ SELECT time FROM ticket_change WHERE ticket=%s
+ UNION
+ SELECT time FROM (
+ SELECT time FROM ticket WHERE id=%s LIMIT 1) AS t
+ ORDER BY time DESC LIMIT 1)
+ WHERE id=%s""",
+"""UPDATE ticket
+SET changetime=
+ ( SELECT time
+ FROM 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
+ ORDER BY time DESC LIMIT 1)
+WHERE product='PRODUCT'
+ AND id=%s"""
+ ),
+ (
+"""UPDATE component SET name=%s,owner=%s, description=%s
+ WHERE name=%s""",
+"""UPDATE component
+SET name=%s,owner=%s,
+ description=%s
+WHERE product='PRODUCT'
+ AND name=%s"""
+ ),
+
+ (
+"""UPDATE milestone
+ SET name=%s, due=%s, completed=%s, description=%s
+ WHERE name=%s""",
+"""UPDATE milestone
+SET name=%s,
+ due=%s,
+ completed=%s,
+ description=%s
+WHERE product='PRODUCT'
+ AND name=%s"""
+ ),
+ (
+"""UPDATE wiki
+ SET text=%s
+ WHERE name=%s""",
+"""UPDATE wiki
+SET text=%s
+WHERE product='PRODUCT'
+ AND name=%s"""
+ ),
+ (
+"""UPDATE ticket SET product=%s
+ WHERE product=%s""",
+"""UPDATE ticket
+SET product=%s
+WHERE product='PRODUCT'
+ AND product=%s"""
+ ),
+ (
+"""UPDATE ticket set changetime=%s where id=%s""",
+"""UPDATE ticket
+set changetime=%s
+where product='PRODUCT'
+ AND id=%s"""
+ ),
+ (
+"""UPDATE
+ milestone
+ SET
+ id_project='%s' WHERE milestone='%s'""",
+"""UPDATE milestone
+SET id_project='%s'
+WHERE product='PRODUCT'
+ AND milestone='%s'"""
+ ),
+ ],
+
+ # non-translated UPDATEs
+ 'system_update_nontranslated' : [
+ (
+"""UPDATE session_attribute
+ SET value='1'
+ WHERE sid=%s
+ AND name='password_refreshed'""",
+"""UPDATE session_attribute
+SET value='1'
+WHERE sid=%s
+ AND name='password_refreshed'"""
+ ),
+ (
+"""UPDATE session_attribute
+ SET value=%s""",
+"""UPDATE session_attribute
+SET value=%s"""
+ ),
+ (
+"""UPDATE auth_cookie
+ SET time=%s
+ WHERE cookie=%s""",
+"""UPDATE auth_cookie
+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
+ 'custom_update' : [
+ (
+"""UPDATE subscription
+ SET format=%s
+ WHERE distributor=%s
+ AND sid=%s
+ AND authenticated=%s""",
+"""UPDATE PRODUCT_subscription
+SET format=%s
+WHERE distributor=%s
+ AND sid=%s
+ AND authenticated=%s"""
+ ),
+ (
+"""UPDATE subscription
+ SET changetime=CURRENT_TIMESTAMP,
+ priority=%s
+ WHERE id=%s""",
+"""UPDATE PRODUCT_subscription
+SET changetime=CURRENT_TIMESTAMP, priority=%s
+WHERE id=%s"""
+ ),
+ (
+"""UPDATE backlog_ticket SET tkt_order = NULL WHERE tkt_id = %s""",
+"""UPDATE PRODUCT_backlog_ticket
+SET tkt_order = NULL
+WHERE tkt_id = %s"""
+ ),
+ (
+"""UPDATE backlog_ticket SET tkt_order = -1
+ WHERE bklg_id = %s
+ AND tkt_id IN
+ (SELECT id FROM ticket
+ WHERE status = 'closed')""",
+"""UPDATE PRODUCT_backlog_ticket
+SET tkt_order = -1
+WHERE bklg_id = %s
+ AND tkt_id IN
+ (SELECT id
+ FROM
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS ticket
+ WHERE status = 'closed')"""
+ ),
+ (
+"""UPDATE backlog_ticket SET tkt_order = -1
+ WHERE bklg_id = %s
+ AND tkt_id IN (SELECT id FROM ticket
+ WHERE status = 'closed')""",
+"""UPDATE PRODUCT_backlog_ticket
+SET tkt_order = -1
+WHERE bklg_id = %s
+ AND tkt_id IN
+ (SELECT id
+ FROM
+ (SELECT *
+ FROM ticket
+ WHERE product="PRODUCT") AS ticket
+ WHERE status = 'closed')"""
+ ),
+ (
+"""UPDATE estimate SET rate=%s, variability=%s, communication=%s, tickets=%s, comment=%s
+ WHERE id=%s""",
+"""UPDATE PRODUCT_estimate
+SET rate=%s,
+ variability=%s,
+ communication=%s,
+ tickets=%s, comment=%s
+WHERE id=%s"""
+ ),
+ (
+"""UPDATE estimate_line_item SET estimate_id=%s ,
+ description=%s, low=%s, high=%s
+ WHERE id=%s""",
+"""UPDATE PRODUCT_estimate_line_item
+SET estimate_id=%s ,
+ description=%s,
+ low=%s,
+ high=%s
+WHERE id=%s"""
+ ),
+ (
+"""UPDATE estimate SET rate=%s, variability=%s, communication=%s, tickets=%s, comment=%s,
+ diffcomment=%s, saveepoch=%s
+ WHERE id=%s""",
+"""UPDATE PRODUCT_estimate
+SET rate=%s,
+ variability=%s,
+ communication=%s,
+ tickets=%s, comment=%s,
+ diffcomment=%s,
+ saveepoch=%s
+WHERE id=%s"""
+ ),
+ (
+"""UPDATE estimate_line_item SET estimate_id=%s ,
+ description=%s, low=%s, high=%s
+ WHERE id=%s""",
+"""UPDATE PRODUCT_estimate_line_item
+SET estimate_id=%s ,
+ description=%s,
+ low=%s,
+ high=%s
+WHERE id=%s"""
+ ),
+ (
+"""UPDATE estimate SET rate=%s, variability=%s, communication=%s, tickets=%s, comment=%s,
+ diffcomment=%s, saveepoch=%s
+ WHERE id=%s""",
+"""UPDATE PRODUCT_estimate
+SET rate=%s,
+ variability=%s,
+ communication=%s,
+ tickets=%s, comment=%s,
+ diffcomment=%s,
+ saveepoch=%s
+WHERE id=%s"""
+ ),
+
+ ],
+
+ # custom CREATE TABLE
+ 'custom_create_table' : [
+ (
+"""CREATE TABLE estimate(
+ id integer PRIMARY KEY,
+ rate DECIMAL,
+ variability DECIMAL,
+ communication DECIMAL,
+ tickets VARCHAR(512),
+ comment VARCHAR(8000)
+ )""",
+"""CREATE TABLE PRODUCT_estimate( id integer PRIMARY KEY, rate DECIMAL, variability DECIMAL, communication DECIMAL, tickets VARCHAR(512), comment VARCHAR(8000) )"""
+ ),
+ (
+"""CREATE TABLE estimate_line_item(
+ id integer PRIMARY KEY,
+ estimate_id integer,
+ description VARCHAR(2048),
+ low DECIMAL,
+ high DECIMAL
+ )""",
+"""CREATE TABLE PRODUCT_estimate_line_item( id integer PRIMARY KEY, estimate_id integer, description VARCHAR(2048), low DECIMAL, high DECIMAL )"""
+ ),
+ (
+"""CREATE TABLE backlog_ticket (bklg_id INTEGER NOT NULL,"
+ " tkt_id INTEGER NOT NULL,"
+ " tkt_order REAL,"
+ " PRIMARY KEY(bklg_id, tkt_id))""",
+"""CREATE TABLE PRODUCT_backlog_ticket (bklg_id INTEGER NOT NULL," " tkt_id INTEGER NOT NULL," " tkt_order REAL," " PRIMARY KEY(bklg_id, tkt_id))"""
+ ),
+ ],
+
+ # custom ALTER TABLE
+ 'custom_alter_table' : [
+ (
+"""ALTER TABLE estimate ADD COLUMN diffcomment text""",
+"""ALTER TABLE PRODUCT_estimate ADD COLUMN diffcomment text"""
+ ),
+ (
+"""ALTER TABLE estimate ADD COLUMN saveepoch int""",
+"""ALTER TABLE PRODUCT_estimate ADD COLUMN saveepoch int"""
+ ),
+ ]
+}
+
+class DbCursorTestCase(unittest.TestCase):
+ """Unit tests covering the BloodhoundProductSQLTranslate"""
+ def setUp(self):
+ self.translator = BloodhoundProductSQLTranslate(SKIP_TABLES, TRANSLATE_TABLES, PRODUCT_COLUMN, 'PRODUCT')
+ for section in data.keys():
+ if not getattr(self, 'test_%s' % section, None):
+ raise Exception("Section '%s' not covered in test case" % section)
+
+ def tearDown(self):
+ pass
+
+ def _run_test(self, section):
+ for (sql, translated_sql_check) in data[section]:
+ translated_sql = self.translator.translate(sql)
+ self.assertEqual(translated_sql.strip(), translated_sql_check.strip())
+
+ def test_system_select_nontranslated(self):
+ self._run_test('system_select_nontranslated')
+
+ def test_system_select_translated(self):
+ self._run_test('system_select_translated')
+
+ def test_custom_select(self):
+ self._run_test('custom_select')
+
+ def test_system_insert_nontranslated(self):
+ self._run_test('system_insert_nontranslated')
+
+ def test_system_insert_translated(self):
+ self._run_test('system_insert_translated')
+
+ def test_custom_insert(self):
+ self._run_test('custom_insert')
+
+ def test_system_update_translated(self):
+ self._run_test('system_update_translated')
+
+ def test_system_update_nontranslated(self):
+ self._run_test('system_update_nontranslated')
+
+ def test_custom_update(self):
+ self._run_test('custom_update')
+
+ def test_custom_create_table(self):
+ self._run_test('custom_create_table')
+
+ def test_custom_alter_table(self):
+ self._run_test('custom_alter_table')
+
+if __name__ == '__main__':
+ unittest.main()
+
+