incubator-bloodhound-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From j...@apache.org
Subject svn commit: r1434677 - /incubator/bloodhound/branches/bep_0003_multiproduct/bloodhound_multiproduct/tests/dbcursor.py
Date Thu, 17 Jan 2013 14:14:01 GMT
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()
+
+



Mime
View raw message