[Anthill-pro] Current Activity tab

[Ryan Smith]

Uldis,

I think we actually found a bug in Oracle that creates a unnecessary Cartesian product when joining tables. We have found a way around it though.

You will need to replace a SQL statement in the com.urbancode.anthill3.dashboard.Dashboard.sql.xml file in the server's conf/sql directory. Search for 'BUILD_LIFE_ACTIVITY_SUMMARY' and replace the SQL statement with:


      SELECT DISTINCT
          WC.BUILD_LIFE_ID,
          P.ID AS PROJECT_ID,
          P.NAME AS PROJECT_NAME,
          WC.ID AS CASE_ID,
          WC.WORKFLOW_ID,
          W.NAME AS WORKFLOW_NAME,
          WC.STATUS AS STATUS,
          WC.START_DATE,
          UR.USER_ID AS HAS_PERMISSION
      FROM
          WORKFLOW_CASE WC
              JOIN WORKFLOW W ON WC.WORKFLOW_ID = W.ID
              JOIN PROJECT P ON W.PROJECT_ID = P.ID
              LEFT OUTER JOIN SEC_RESOURCE SR ON SR.HANDLE_ID = W.ID
                AND SR.RESOURCE_TYPE_ID = 4
              LEFT OUTER JOIN SEC_PERMISSION SP ON SP.RESOURCE_ID = SR.ID
               AND SP.RESOURCE_ACTION = 'execute'
              LEFT OUTER JOIN SEC_USER_ROLE UR ON SP.ROLE_ID = UR.ROLE_ID
               AND UR.USER_ID = ?
      WHERE
          WC.WORKFLOW_ID = W.ID AND
          W.PROJECT_ID = P.ID AND
          WC.STATUS IN ('Running', 'Suspended', 'Waiting on Agents', 'Queued', 'Aborting' )
      ORDER BY WC.START_DATE DESC, WC.BUILD_LIFE_ID, HAS_PERMISSION




Save the file and restart the server.


Ryan

Steve Boone wrote:
> Uldis,
> 
> There seems to be an issue with one of the SQL queries we are running in 
> Oracle, we are trying to come up with a workaround for the issue. 
> 
> Regards,
> Steve Boone
> 
> On 1/14/08, *Uldis Karlovs-Karlovskis* <uldisk at ctco.lv 
> <mailto:uldisk at ctco.lv>> wrote:
> 
>     Hello!
> 
>      
> 
>     Does Current Activity tab still should don`t work or Oracle? I`m on
>     3.4.7 but it is still hunging up :(
> 
>      
> 
>     //Uldis Karlovs - Karlovskis//
> 
>     //Build System and Deployment Administrator//
> 
>     //System Configuration Management//
> 
>     //C.T.Co <http://C.T.Co>//
> 
>     //Mobile////: +371 29 345 210//
> 
>      
> 
> 
>     _______________________________________________
>     Anthill-pro mailing list
>     Anthill-pro at lists.urbancode.com
>     <mailto:Anthill-pro at lists.urbancode.com>
>     http://lists.urbancode.com/mailman/listinfo/anthill-pro
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Anthill-pro mailing list
> Anthill-pro at lists.urbancode.com
> http://lists.urbancode.com/mailman/listinfo/anthill-pro

-- 
===========================================================
Ryan Smith.           		2044 Euclid Ave., Suite 600
Lead Developer                  Cleveland, Ohio 44115
Urbancode, Inc.
                                email:  rws at urbancode.com
web:     www.urbancode.com      phone:  216-858-9000
web:     www.anthillpro.com     fax:    216-858-9602
===========================================================