Menu
▾
▴
[phpBT-dev] CVS: phpbt/inc/db mssql.php,1.2,1.3 mysql.php,1.25,1.26 oci8.php,1.18,1.19 pgsql.php,1.26,1.27
|
From: Ulf E. <ulf...@us...> - 2005-08-23 21:09:50
|
Update of /cvsroot/phpbt/phpbt/inc/db In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv22034/phpbt/inc/db Modified Files: mssql.php mysql.php oci8.php pgsql.php Log Message: Sorting and reformatting to simplify updates in future Index: mssql.php =================================================================== RCS file: /cvsroot/phpbt/phpbt/inc/db/mssql.php,v retrieving revision 1.2 retrieving revision 1.3 diff -u -r1.2 -r1.3 --- mssql.php 19 Jul 2005 19:44:36 -0000 1.2 +++ mssql.php 23 Aug 2005 21:09:42 -0000 1.3 @@ -1,178 +1,414 @@ <?php +// mssql.php - Microsoft SQL Server queries + $QUERY = array( - 'admin-list-groups' => 'select ag.group_id, group_name, locked, '. - 'count(ug.group_id) as count, assignable '. - 'from '.TBL_AUTH_GROUP.' ag '. - 'left join '.TBL_USER_GROUP.' ug on ug.group_id = ag.group_id '. - 'left join '.TBL_AUTH_USER.' au on ug.user_id = au.user_id '. - 'group by ag.group_id, group_name, locked, assignable '. - 'order by %s %s', - 'admin-list-oses' => 'select s.os_id, os_name, regex, sort_order, '. - 'count(bug_id) as bug_count '. - 'from '.TBL_OS.' s '. - 'left join '.TBL_BUG.' b on s.os_id = b.os_id '. - 'group by s.os_id, os_name, regex, sort_order '. - 'order by %s %s', - 'admin-show-version' => 'select v.*, p.project_name as project_name '. - 'from '.TBL_VERSION.' v left join '.TBL_PROJECT.' p on p.project_id = v.project_id'. - 'where version_id = \'%s\'', - 'admin-show-component' => 'select c.*, p.project_name as project_name '. - 'from '.TBL_COMPONENT.' c left join '.TBL_PROJECT.' p on p.project_id = c.project_id '. - 'where component_id = \'%s\'', - 'admin-list-resolutions' => 'select s.resolution_id, resolution_name, '. - 'resolution_desc, sort_order, count(bug_id) as bug_count '. - 'from '.TBL_RESOLUTION. ' s left join '.TBL_BUG.' b on b.resolution_id = s.resolution_id '. - 'group by s.resolution_id, resolution_name, resolution_desc, sort_order '. - 'order by %s %s', - 'admin-list-severities' => 'select s.severity_id, severity_name, '. - 'severity_desc, severity_color, sort_order, count(bug_id) as bug_count '. - 'from '.TBL_SEVERITY. ' s left join '.TBL_BUG.' b on b.severity_id = s.severity_id '. - 'group by s.severity_id, severity_name, severity_desc, severity_color, '. - 'sort_order '. - 'order by %s %s', - 'admin-list-databases' => 'select d.database_id, database_name, '. - 'sort_order, count(bug_id) as bug_count '. - 'from '.TBL_DATABASE. ' d left join '.TBL_BUG.' b on b.database_id = d.database_id '. - 'group by d.database_id, database_name, sort_order '. - 'order by %s %s', - 'admin-list-sites' => 'select s.site_id, site_name, sort_order, '. - 'count(bug_id) as bug_count from '.TBL_SITE. ' s left join '. - TBL_BUG.' b on b.site_id = s.site_id group by s.site_id, site_name, sort_order '. - 'order by %s %s', - 'admin-list-statuses' => 'select s.status_id, status_name, status_desc, '. - 'sort_order, bug_open, count(bug_id) as bug_count '. - 'from '.TBL_STATUS.' s left join '. TBL_BUG.' b on b.status_id = s.status_id '. - 'group by s.status_id, status_name, status_desc, sort_order '. - 'order by %s %s', - 'admin-user-groups' => 'select ug.group_id '. - 'from '.TBL_USER_GROUP.' ug left join '.TBL_AUTH_GROUP.' g on g.group_id = ug.group_id '. - 'where user_id = %s and group_name <> \'User\'', - 'bug-history' => 'select bh.*, login '. - 'from '.TBL_BUG_HISTORY.' bh '. - 'left join '. TBL_AUTH_USER.' on bh.created_by = user_id '. - 'where bug_id = %s order by bh.created_date', - 'bug-cc-list' => 'select email '. - 'from '.TBL_BUG_CC.' b left join '. TBL_AUTH_USER.' u on u.user_id = b.user_id, '. - TBL_USER_PREF.' p '. - 'where bug_id = %s and u.user_id = p.user_id and email_notices = 1', - 'bug-printable' => 'select b.*, reporter.login as reporter, '. - 'owner.login as owner, project_name, component_name, version_name, '. - 'severity_name, os_name, status_name, resolution_name '. - 'from '.TBL_BUG.' b '. - 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. - 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. - 'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '. - TBL_SEVERITY.' sv, '.TBL_STATUS.' st, '.TBL_OS.' os, '. TBL_VERSION.' v, '. - TBL_COMPONENT.' c, '.TBL_PROJECT.' p '. - 'where bug_id = %s and b.project_id not in (%s) '. - 'and b.severity_id = sv.severity_id '. - 'and b.os_id = os.os_id and b.version_id = v.version_id '. - 'and b.component_id = c.component_id and b.project_id = p.project_id '. - 'and b.status_id = st.status_id', - 'bug-prev-next' => 'select bug_id, reporter.login as reporter, '. - 'owner.login as owner '. - 'from '.TBL_BUG.' b '. - 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. - 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. - 'left join '.TBL_AUTH_USER.' lastmodifier on b.last_modified_by = lastmodifier.user_id '. - 'left join '.TBL_RESOLUTION.' resolution on b.resolution_id = resolution.resolution_id '. - 'left join '.TBL_DATABASE.' db on b.database_id = db.database_id '. - 'left join '.TBL_VERSION.' version2 on b.to_be_closed_in_version_id = version2.version_id '. - 'left join '.TBL_VERSION.' version3 on b.closed_in_version_id = version3.version_id, '. - TBL_SEVERITY.' severity, '.TBL_STATUS.' status, '.TBL_OS.' os, '. - TBL_VERSION.' version, '.TBL_COMPONENT.' component, '. - TBL_PROJECT.' project, '.TBL_SITE.' site '. - 'where b.severity_id = severity.severity_id '. - 'and b.status_id = status.status_id and b.os_id = os.os_id '. - 'and b.version_id = version.version_id '. - 'and b.component_id = component.component_id '. - 'and b.project_id = project.project_id and %s '. - 'and b.site_id = site.site_id '. - 'and bug_id <> %s '. - 'order by %s %s, bug_id asc', - 'bug-show-bug' => 'select b.*, reporter.login as reporter, '. - 'owner.login as owner, status_name, resolution_name '. + 'admin-list-components' => + 'select '. + 'c.component_id, '. + 'c.component_name, '. + 'c.created_date, '. + 'c.active, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_COMPONENT.' c '. + 'left join '.TBL_BUG.' b on b.component_id = c.component_id '. + 'where '. + 'c.project_id = %s '. + 'group by '. + 'c.component_id, '. + 'c.component_name, '. + 'c.created_date, '. + 'c.active', + 'admin-list-databases' => + 'select '. + 'd.database_id, '. + 'database_name, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_DATABASE.' d '. + 'left join '.TBL_BUG.' b on b.database_id = d.database_id '. + 'group by '. + 'd.database_id, '. + 'database_name, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-groups' => + 'select '. + 'ag.group_id, '. + 'group_name, '. + 'locked, '. + 'count(ug.group_id) as count, '. + 'assignable '. + 'from '. + TBL_AUTH_GROUP.' ag '. + 'left join '.TBL_USER_GROUP.' ug on ug.group_id = ag.group_id '. + 'left join '.TBL_AUTH_USER.' au on ug.user_id = au.user_id '. + 'group by '. + 'ag.group_id, '. + 'group_name, '. + 'locked, '. + 'assignable '. + 'order by '. + '%s %s', + 'admin-list-oses' => + 'select '. + 's.os_id, '. + 'os_name, '. + 'regex, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_OS.' s '. + 'left join '.TBL_BUG.' b on s.os_id = b.os_id '. + 'group by '. + 's.os_id, '. + 'os_name, '. + 'regex, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-resolutions' => + 'select '. + 's.resolution_id, '. + 'resolution_name, '. + 'resolution_desc, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_RESOLUTION.' s '. + 'left join '.TBL_BUG.' b on b.resolution_id = s.resolution_id '. + 'group by '. + 's.resolution_id, '. + 'resolution_name, '. + 'resolution_desc, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-severities' => + 'select '. + 's.severity_id, '. + 'severity_name, '. + 'severity_desc, '. + 'severity_color, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_SEVERITY.' s '. + 'left join '.TBL_BUG.' b on b.severity_id = s.severity_id '. + 'group by '. + 's.severity_id, '. + 'severity_name, '. + 'severity_desc, '. + 'severity_color, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-sites' => + 'select '. + 's.site_id, '. + 'site_name, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_SITE.' s '. + 'left join '.TBL_BUG.' b on b.site_id = s.site_id '. + 'group by '. + 's.site_id, '. + 'site_name, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-statuses' => + 'select '. + 's.status_id, '. + 'status_name, '. + 'status_desc, '. + 'sort_order, '. + 'bug_open, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_STATUS.' s '. + 'left join '.TBL_BUG.' b on b.status_id = s.status_id '. + 'group by '. + 's.status_id, '. + 'status_name, '. + 'status_desc, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-versions' => + 'select '. + 'v.version_id, '. + 'v.version_name, '. + 'v.created_date, '. + 'v.active, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_VERSION.' v '. + 'left join '.TBL_BUG.' b on b.version_id = v.version_id '. + 'where '. + 'v.project_id = %s '. + 'group by '. + 'v.version_id, '. + 'v.version_name, '. + 'v.created_date, '. + 'v.active', + 'admin-show-component' => + 'select '. + 'c.*, '. + 'p.project_name as project_name '. + 'from '. + TBL_COMPONENT.' c '. + 'left join '.TBL_PROJECT.' p on p.project_id = c.project_id '. + 'where '. + 'component_id = \'%s\'', + 'admin-show-version' => + 'select '. + 'v.*, '. + 'p.project_name as project_name '. + 'from '. + TBL_VERSION.' v '. + 'left join '.TBL_PROJECT.' p on p.project_id = v.project_id'. + 'where '. + 'version_id = \'%s\'', + 'admin-user-groups' => + 'select '. + 'ug.group_id '. + 'from '. + TBL_USER_GROUP.' ug '. + 'left join '.TBL_AUTH_GROUP.' g on g.group_id = ug.group_id '. + 'where '. + 'user_id = %s '. + 'and group_name <> \'User\'', + 'bug-cc-list' => + 'select '. + 'email '. + 'from '. + TBL_BUG_CC.' b '. + 'left join '.TBL_AUTH_USER.' u on u.user_id = b.user_id, '. + TBL_USER_PREF.' p '. + 'where '. + 'bug_id = %s '. + 'and u.user_id = p.user_id '. + 'and email_notices = 1', + 'bug-history' => + 'select '. + 'bh.*, '. + 'login '. + 'from '. + TBL_BUG_HISTORY.' bh '. + 'left join '.TBL_AUTH_USER.' on bh.created_by = user_id '. + 'where '. + 'bug_id = %s '. + 'order by '. + 'bh.created_date', + 'bug-prev-next' => + 'select '. + 'bug_id, '. + 'reporter.login as reporter, '. + 'owner.login as owner '. + 'from '. + TBL_BUG.' b '. + 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. + 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. + 'left join '.TBL_AUTH_USER.' lastmodifier on b.last_modified_by = lastmodifier.user_id '. + 'left join '.TBL_RESOLUTION.' resolution on b.resolution_id = resolution.resolution_id '. + 'left join '.TBL_DATABASE.' db on b.database_id = db.database_id '. + 'left join '.TBL_VERSION.' version2 on b.to_be_closed_in_version_id = version2.version_id '. + 'left join '.TBL_VERSION.' version3 on b.closed_in_version_id = version3.version_id, '. + TBL_SEVERITY.' severity, '. + TBL_STATUS.' status, '. + TBL_OS.' os, '. + TBL_VERSION.' version, '. + TBL_COMPONENT.' component, '. + TBL_PROJECT.' project, '. + TBL_SITE.' site '. + 'where '. + 'b.severity_id = severity.severity_id '. + 'and b.status_id = status.status_id '. + 'and b.os_id = os.os_id '. + 'and b.version_id = version.version_id '. + 'and b.component_id = component.component_id '. + 'and b.project_id = project.project_id '. + 'and %s '. + 'and b.site_id = site.site_id '. + 'and bug_id <> %s '. + 'order by '. + '%s %s, '. + 'bug_id asc', + 'bug-printable' => + 'select '. + 'b.*, '. + 'reporter.login as reporter, '. + 'owner.login as owner, '. + 'project_name, '. + 'component_name, '. + 'version_name, '. + 'severity_name, '. + 'os_name, '. + 'status_name, '. + 'resolution_name '. + 'from '. + TBL_BUG.' b '. + 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. + 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. + 'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '. + TBL_SEVERITY.' sv, '. + TBL_STATUS.' st, '. + TBL_OS.' os, '. + TBL_VERSION.' v, '. + TBL_COMPONENT.' c, '. + TBL_PROJECT.' p '. + 'where '. + 'bug_id = %s '. + 'and b.project_id not in (%s) '. + 'and b.severity_id = sv.severity_id '. + 'and b.os_id = os.os_id '. + 'and b.version_id = v.version_id '. + 'and b.component_id = c.component_id '. + 'and b.project_id = p.project_id '. + 'and b.status_id = st.status_id', + 'bug-show-bug' => + 'select '. + 'b.*, '. + 'reporter.login as reporter, '. + 'owner.login as owner, '. + 'status_name, '. + 'resolution_name '. + 'from '. + TBL_BUG.' b '. + 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. + 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. + 'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '. + TBL_SEVERITY.' sv, '. + TBL_STATUS.' st, '. + TBL_SITE.' site '. + 'where '. + 'bug_id = %s '. + 'and b.project_id not in (%s) '. + 'and b.site_id = site.site_id '. + 'and b.severity_id = sv.severity_id '. + 'and b.status_id = st.status_id', + 'functions-bug-cc' => + 'select '. + 'b.user_id, '. + 'login '. + 'from '. + TBL_BUG_CC.' b '. + 'left join '.TBL_AUTH_USER.' a on a.user_id = b.user_id '. + 'where '. + 'bug_id = %s', + 'functions-project-js' => + 'select '. + 'p.project_id, '. + 'p.project_name '. + 'from '. + TBL_PROJECT.' p '. + 'left join '.TBL_PROJECT_GROUP.' on pg.project_id = p.project_id '. + 'where '. + 'active = 1 '. + 'and (pg.project_id is null or pg.group_id in (%s)) '. + 'group by '. + 'p.project_id, '. + 'p.project_name '. + 'order by '. + 'project_name', + 'include-template-owner' => + "SELECT ". + "sum(CASE WHEN s.status_id in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ), ". + "sum(CASE WHEN s.status_id not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ". + "from ". + TBL_BUG." b ". + "left join ".TBL_STATUS." s on s.status_id = b.status_id ". + "where ". + "assigned_to = %s", + 'include-template-reporter' => + "SELECT ". + "sum(CASE WHEN s.status_id in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ), ". + "sum(CASE WHEN s.status_id not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ". + "from ". + TBL_BUG." b ". + "left join ".TBL_STATUS." s on s.status_id = b.status_id ". + "where ". + "created_by = %s", + 'index-projsummary-1' => + 'select project_name as "Project", '. + 'sum(case when resolution_id = 0 then 1 else 0 end) as "Open"', + 'index-projsummary-2' => + "select resolution_name, ", + 'index-projsummary-3' => + "', sum(case when resolution_id = '", + 'index-projsummary-4' => + "' then 1 else 0 end) as \"'", + 'index-projsummary-5' => + " from ".TBL_RESOLUTION, + 'index-projsummary-6' => + '%s, count(bug_id) as "Total" '. 'from '.TBL_BUG.' b '. - 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. - 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. - 'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '. - TBL_SEVERITY.' sv, '.TBL_STATUS.' st, '.TBL_SITE. ' site '. - 'where bug_id = %s and b.project_id not in (%s) '. - 'and b.site_id = site.site_id '. - 'and b.severity_id = sv.severity_id and b.status_id = st.status_id', - 'functions-bug-cc' => 'select b.user_id, login '. - 'from '.TBL_BUG_CC.' b left join '. TBL_AUTH_USER.' a on a.user_id = b.user_id '. - 'where bug_id = %s', - 'functions-project-js' => 'select p.project_id, p.project_name '. - 'from '.TBL_PROJECT. ' p '. - 'left join '.TBL_PROJECT_GROUP.' on pg.project_id = p.project_id '. - 'where active = 1 and (pg.project_id is null or pg.group_id in (%s)) '. - 'group by p.project_id, p.project_name '. + 'left join '.TBL_PROJECT.' p on b.project_id = p.project_id '. + 'where b.project_id not in (%s) '. + 'group by b.project_id, project_name '. 'order by project_name', - 'include-template-owner' => "SELECT sum(CASE WHEN s.status_id ". - "in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) , ". - "sum(CASE WHEN s.status_id ". - "not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ". - "from ".TBL_BUG." b left join ".TBL_STATUS." s on s.status_id = b.status_id ". - "where assigned_to = %s", - 'include-template-reporter' => "SELECT sum(CASE WHEN s.status_id in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) , ". - "sum(CASE WHEN s.status_id not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ". - "from ".TBL_BUG." b left join ".TBL_STATUS." s on s.status_id = b.status_id ". - "where created_by = %s", - 'index-projsummary-1' => 'select project_name as "Project", '. + 'join-where' => + 'where', + 'query-list-bugs' => + 'select '. + '%s '. + 'from '. + TBL_BUG.' b '. + 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. + 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. + 'left join '.TBL_AUTH_USER.' lastmodifier on b.last_modified_by = lastmodifier.user_id '. + 'left join '.TBL_RESOLUTION.' resolution on b.resolution_id = resolution.resolution_id '. + 'left join '.TBL_DATABASE.' on b.database_id = '.TBL_DATABASE.'.database_id '. + 'left join '.TBL_VERSION.' version2 on b.to_be_closed_in_version_id = version2.version_id '. + 'left join '.TBL_VERSION.' version3 on b.closed_in_version_id = version3.version_id, '. + TBL_SEVERITY.' severity, '. + TBL_STATUS.' status, '. + TBL_OS.' os, '. + TBL_SITE.' site, '. + TBL_VERSION.' version, '. + TBL_COMPONENT.' component, '. + TBL_PROJECT.' project '. + 'where '. + 'b.severity_id = severity.severity_id '. + 'and b.status_id = status.status_id '. + 'and b.os_id = os.os_id '. + 'and b.site_id = site.site_id '. + 'and b.version_id = version.version_id '. + 'and b.component_id = component.component_id '. + 'and b.project_id = project.project_id %s '. + 'order by '. + '%s %s, '. + 'bug_id asc', + 'query-list-bugs-count' => + 'select '. + 'count(*) '. + 'from '. + TBL_BUG.' b '. + 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. + 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id ', + 'query-list-bugs-count-join' => + 'where ', + 'report-resbyeng-1' => + 'select u.email as "Assigned To", '. 'sum(case when resolution_id = 0 then 1 else 0 end) as "Open"', - 'index-projsummary-2' => "select resolution_name, ", - 'index-projsummary-3' => "', sum(case when resolution_id = '", - 'index-projsummary-4' => "' then 1 else 0 end) as \"'", - 'index-projsummary-5' => " from ".TBL_RESOLUTION, - 'index-projsummary-6' => '%s, count(bug_id) as "Total" '. - 'from '.TBL_BUG. ' b left join '.TBL_PROJECT.' p on b.project_id = p.project_id '. - 'where b.project_id not in (%s) group by b.project_id, project_name '. - 'order by project_name', - 'query-list-bugs-count' => 'select count(*) '. - 'from '.TBL_BUG.' b '. - 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. - 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id ', - 'query-list-bugs-count-join' => 'where ', - 'query-list-bugs' => 'select %s '. + 'report-resbyeng-2' => + "select resolution_name, ", + 'report-resbyeng-3' => + "', sum(case when resolution_id = '", + 'report-resbyeng-4' => + "' then 1 else 0 end) as \"'", + 'report-resbyeng-5' => + " from ".TBL_RESOLUTION, + 'report-resbyeng-6' => + '%s, count(bug_id) as "Total" '. 'from '.TBL_BUG.' b '. - 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. - 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. - 'left join '.TBL_AUTH_USER.' lastmodifier on b.last_modified_by = lastmodifier.user_id '. - 'left join '.TBL_RESOLUTION.' resolution on b.resolution_id = resolution.resolution_id '. - 'left join '.TBL_DATABASE.' on b.database_id = '.TBL_DATABASE.'.database_id '. - 'left join '.TBL_VERSION.' version2 on b.to_be_closed_in_version_id = version2.version_id '. - 'left join '.TBL_VERSION.' version3 on b.closed_in_version_id = version3.version_id, '. - TBL_SEVERITY.' severity, '.TBL_STATUS.' status, '.TBL_OS.' os, '.TBL_SITE.' site, '. - TBL_VERSION.' version, '.TBL_COMPONENT.' component, '.TBL_PROJECT.' project '. - 'where b.severity_id = severity.severity_id '. - 'and b.status_id = status.status_id and b.os_id = os.os_id '. - 'and b.site_id = site.site_id and b.version_id = version.version_id '. - 'and b.component_id = component.component_id '. - 'and b.project_id = project.project_id %s '. - 'order by %s %s, bug_id asc', - 'report-resbyeng-1' => 'select u.email as "Assigned To", '. - 'sum(case when resolution_id = 0 then 1 else 0 end) as "Open"', - 'report-resbyeng-2' => "select resolution_name, ", - 'report-resbyeng-3' => "', sum(case when resolution_id = '", - 'report-resbyeng-4' => "' then 1 else 0 end) as \"'", - 'report-resbyeng-5' => " from ".TBL_RESOLUTION, - 'report-resbyeng-6' => '%s, count(bug_id) as "Total" '. - 'from '.TBL_BUG. ' b '. 'left join '.TBL_AUTH_USER.' u on assigned_to = user_id %s '. 'group by assigned_to, u.email', - 'join-where' => 'where', - 'admin-list-components' => 'select c.component_id, c.component_name, '. - 'c.created_date, c.active, count(bug_id) as bug_count '. - 'from '.TBL_COMPONENT.' c left join '.TBL_BUG.' b on b.component_id = c.component_id '. - 'where c.project_id = %s '. - 'group by c.component_id, c.component_name, c.created_date, c.active', - 'admin-list-versions' => 'select v.version_id, v.version_name, '. - 'v.created_date, v.active, count(bug_id) as bug_count '. - 'from '.TBL_VERSION.' v left join '.TBL_BUG.' b on b.version_id = v.version_id '. - 'where v.project_id = %s '. - 'group by v.version_id, v.version_name, v.created_date, v.active', ); ?> Index: mysql.php =================================================================== RCS file: /cvsroot/phpbt/phpbt/inc/db/mysql.php,v retrieving revision 1.25 retrieving revision 1.26 diff -u -r1.25 -r1.26 --- mysql.php 22 Aug 2005 20:44:16 -0000 1.25 +++ mysql.php 23 Aug 2005 21:09:42 -0000 1.26 @@ -1,208 +1,471 @@ <?php + +// mysql.php - MySQL queries + $QUERY = array( - 'admin-list-groups' => 'select ag.group_id, group_name, locked, '. - 'count(ug.group_id) as count '. - 'from '.TBL_AUTH_GROUP.' ag '. - 'left join '.TBL_USER_GROUP.' ug using (group_id) '. - 'left join '.TBL_AUTH_USER.' using (user_id) '. - 'group by ag.group_id, group_name, locked '. - 'order by %s %s', - 'admin-list-oses' => 'select s.os_id, os_name, regex, sort_order, '. - 'count(bug_id) as bug_count '. - 'from '.TBL_OS.' s '. - 'left join '.TBL_BUG.' using (os_id) '. - 'group by s.os_id, os_name, regex, sort_order '. - 'order by %s %s', - 'admin-show-version' => 'select v.*, p.project_name as project_name '. - 'from '.TBL_VERSION.' v left join '.TBL_PROJECT.' p using(project_id) '. - 'where version_id = \'%s\'', - 'admin-show-component' => 'select c.*, p.project_name as project_name '. - 'from '.TBL_COMPONENT.' c left join '.TBL_PROJECT.' p using (project_id) '. - 'where component_id = \'%s\'', - 'admin-list-resolutions' => 'select s.resolution_id, resolution_name, '. - 'resolution_desc, sort_order, count(bug_id) as bug_count '. - 'from '.TBL_RESOLUTION. ' s left join '.TBL_BUG.' using (resolution_id) '. - 'group by s.resolution_id, resolution_name, resolution_desc, sort_order '. - 'order by %s %s', - 'admin-list-severities' => 'select s.severity_id, severity_name, '. - 'severity_desc, severity_color, sort_order, count(bug_id) as bug_count '. - 'from '.TBL_SEVERITY. ' s left join '.TBL_BUG.' using (severity_id) '. - 'group by s.severity_id, severity_name, severity_desc, severity_color, '. - 'sort_order '. - 'order by %s %s', - 'admin-list-priorities' => 'select p.priority_id, priority_name, '. - 'priority_desc, priority_color, sort_order, count(bug_id) as bug_count '. - 'from '.TBL_PRIORITY. ' p left join '.TBL_BUG.' b on b.priority = p.priority_id '. - 'group by p.priority_id, priority_name, priority_desc, priority_color, '. - 'sort_order '. - 'order by %s %s', - 'admin-list-databases' => 'select d.database_id, database_name, '. - 'sort_order, count(bug_id) as bug_count '. - 'from '.TBL_DATABASE. ' d left join '.TBL_BUG.' using (database_id) '. - 'group by d.database_id, database_name, sort_order '. - 'order by %s %s', - 'admin-list-sites' => 'select s.site_id, site_name, sort_order, '. - 'count(bug_id) as bug_count from '.TBL_SITE. ' s left join '. - TBL_BUG.' using (site_id) group by s.site_id, site_name, sort_order '. - 'order by %s %s', - 'admin-list-statuses' => 'select s.status_id, status_name, status_desc, '. - 'sort_order, bug_open, count(bug_id) as bug_count '. - 'from '.TBL_STATUS.' s left join '. TBL_BUG.' using (status_id) '. - 'group by s.status_id, status_name, status_desc, sort_order '. - 'order by %s %s', - 'admin-user-groups' => 'select ug.group_id '. - 'from '.TBL_USER_GROUP.' ug left join '.TBL_AUTH_GROUP.' g using (group_id) '. - 'where user_id = %s and group_name <> \'User\'', - 'bug-history' => 'select bh.*, login '. - 'from '.TBL_BUG_HISTORY.' bh '. - 'left join '. TBL_AUTH_USER.' on bh.created_by = user_id '. - 'where bug_id = %s order by bh.created_date', - 'bug-cc-list' => 'select email '. - 'from '.TBL_BUG_CC.' left join '. TBL_AUTH_USER.' u using(user_id), '. - TBL_USER_PREF.' p '. - 'where bug_id = %s and u.user_id = p.user_id and email_notices = 1', - 'bug-printable' => 'select b.*, reporter.login as reporter, '. - 'owner.login as owner, project_name, component_name, version_name, '. - 'severity_name, priority_name, os_name, status_name, resolution_name '. - 'from '.TBL_BUG.' b '. - 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. - 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. - 'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id '. - 'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '. - TBL_SEVERITY.' sv, '.TBL_STATUS.' st, '.TBL_OS.' os, '. TBL_VERSION.' v, '. - TBL_PRIORITY.' priority, '.TBL_COMPONENT.' c, '.TBL_PROJECT.' p '. 'where b.bug_id = %s and b.project_id not in (%s) '. - 'and b.severity_id = sv.severity_id '. - 'and b.priority = priority.priority_id '. - 'and b.os_id = os.os_id and b.version_id = v.version_id '. - 'and b.component_id = c.component_id and b.project_id = p.project_id '. - 'and b.status_id = st.status_id', - 'bug-prev-next' => 'select b.bug_id, reporter.login as reporter, '. - 'owner.login as owner, '. - 'count(distinct comment.comment_id) as comments, '. - 'count(distinct attachment.attachment_id) as attachments, '. - 'count(distinct vote.user_id) as votes '. - 'from '.TBL_BUG.' b '. - 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. - 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. - 'left join '.TBL_AUTH_USER.' lastmodifier on b.last_modified_by = lastmodifier.user_id '. - 'left join '.TBL_COMMENT.' comment on b.bug_id = comment.bug_id '. - 'left join '.TBL_ATTACHMENT.' attachment on b.bug_id = attachment.bug_id '. - 'left join '.TBL_BUG_VOTE.' vote on b.bug_id = vote.bug_id '. - 'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id '. - 'left join '.TBL_RESOLUTION.' resolution on b.resolution_id = resolution.resolution_id '. - 'left join '.TBL_DATABASE.' on b.database_id = '.TBL_DATABASE.'.database_id '. - 'left join '.TBL_VERSION.' version2 on b.to_be_closed_in_version_id = version2.version_id '. - 'left join '.TBL_VERSION.' version3 on b.closed_in_version_id = version3.version_id, '. - TBL_SEVERITY.' severity, '.TBL_STATUS.' status, '.TBL_OS.' os, '. - TBL_PRIORITY.' priority, '.TBL_VERSION.' version, '.TBL_COMPONENT.' component, '. - TBL_PROJECT.' project, '.TBL_SITE.' site '. - 'where b.severity_id = severity.severity_id '. - 'and b.priority = priority.priority_id '. - 'and b.status_id = status.status_id and b.os_id = os.os_id '. - 'and b.version_id = version.version_id '. - 'and b.component_id = component.component_id '. - 'and b.project_id = project.project_id and %s '. - 'and b.site_id = site.site_id '. - 'and b.bug_id <> %s '. - 'GROUP BY b.bug_id '. - 'order by %s %s, b.bug_id asc', - 'bug-show-bug' => 'select b.*, reporter.login as reporter, '. - 'owner.login as owner, status_name, resolution_name '. + 'admin-list-components' => + 'select '. + 'c.component_id, '. + 'component_name, '. + 'c.created_date, '. + 'active, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_COMPONENT.' c '. + 'left join '.TBL_BUG.' b using(component_id) '. + 'where '. + 'c.project_id = %s '. + 'group by '. + 'c.component_id, '. + 'c.component_name, '. + 'c.created_date, '. + 'c.active', + 'admin-list-databases' => + 'select '. + 'd.database_id, '. + 'database_name, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_DATABASE.' d '. + 'left join '.TBL_BUG.' using (database_id) '. + 'group by '. + 'd.database_id, '. + 'database_name, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-groups' => + 'select '. + 'ag.group_id, '. + 'group_name, '. + 'locked, '. + 'count(ug.group_id) as count '. + 'from '. + TBL_AUTH_GROUP.' ag '. + 'left join '.TBL_USER_GROUP.' ug using (group_id) '. + 'left join '.TBL_AUTH_USER.' using (user_id) '. + 'group by '. + 'ag.group_id, '. + 'group_name, '. + 'locked '. + 'order by '. + '%s %s', + 'admin-list-oses' => + 'select '. + 's.os_id, '. + 'os_name, '. + 'regex, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_OS.' s '. + 'left join '.TBL_BUG.' using (os_id) '. + 'group by '. + 's.os_id, '. + 'os_name, '. + 'regex, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-priorities' => + 'select '. + 'p.priority_id, '. + 'priority_name, '. + 'priority_desc, '. + 'priority_color, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_PRIORITY.' p '. + 'left join '.TBL_BUG.' b on b.priority = p.priority_id '. + 'group by '. + 'p.priority_id, '. + 'priority_name, '. + 'priority_desc, '. + 'priority_color, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-resolutions' => + 'select '. + 's.resolution_id, '. + 'resolution_name, '. + 'resolution_desc, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_RESOLUTION.' s '. + 'left join '.TBL_BUG.' using (resolution_id) '. + 'group by '. + 's.resolution_id, '. + 'resolution_name, '. + 'resolution_desc, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-severities' => + 'select '. + 's.severity_id, '. + 'severity_name, '. + 'severity_desc, '. + 'severity_color, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_SEVERITY.' s '. + 'left join '.TBL_BUG.' using (severity_id) '. + 'group by '. + 's.severity_id, '. + 'severity_name, '. + 'severity_desc, '. + 'severity_color, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-sites' => + 'select '. + 's.site_id, '. + 'site_name, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_SITE.' s '. + 'left join '.TBL_BUG.' using (site_id) '. + 'group by '. + 's.site_id, '. + 'site_name, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-statuses' => + 'select '. + 's.status_id, '. + 'status_name, '. + 'status_desc, '. + 'sort_order, '. + 'bug_open, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_STATUS.' s '. + 'left join '.TBL_BUG.' using (status_id) '. + 'group by '. + 's.status_id, '. + 'status_name, '. + 'status_desc, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-versions' => + 'select '. + 'v.version_id, '. + 'version_name, '. + 'v.created_date, '. + 'active, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_VERSION.' v '. + 'left join '.TBL_BUG.' b using(version_id) '. + 'where '. + 'v.project_id = %s '. + 'group by '. + 'v.version_id, '. + 'v.version_name, '. + 'v.created_date, '. + 'v.active', + 'admin-show-component' => + 'select '. + 'c.*, '. + 'p.project_name as project_name '. + 'from '. + TBL_COMPONENT.' c '. + 'left join '.TBL_PROJECT.' p using (project_id) '. + 'where '. + 'component_id = \'%s\'', + 'admin-show-version' => + 'select '. + 'v.*, '. + 'p.project_name as project_name '. + 'from '. + TBL_VERSION.' v '. + 'left join '.TBL_PROJECT.' p using(project_id) '. + 'where '. + 'version_id = \'%s\'', + 'admin-user-groups' => + 'select '. + 'ug.group_id '. + 'from '. + TBL_USER_GROUP.' ug '. + 'left join '.TBL_AUTH_GROUP.' g using (group_id) '. + 'where '. + 'user_id = %s '. + 'and group_name <> \'User\'', + 'bug-cc-list' => + 'select '. + 'email '. + 'from '. + TBL_BUG_CC.' '. + 'left join '.TBL_AUTH_USER.' u using(user_id), '. + TBL_USER_PREF.' p '. + 'where '. + 'bug_id = %s '. + 'and u.user_id = p.user_id '. + 'and email_notices = 1', + 'bug-history' => + 'select '. + 'bh.*, '. + 'login '. + 'from '. + TBL_BUG_HISTORY.' bh '. + 'left join '.TBL_AUTH_USER.' on bh.created_by = user_id '. + 'where '. + 'bug_id = %s '. + 'order by '. + 'bh.created_date', + 'bug-prev-next' => + 'select '. + 'b.bug_id, '. + 'reporter.login as reporter, '. + 'owner.login as owner, '. + 'count(distinct comment.comment_id) as comments, '. + 'count(distinct attachment.attachment_id) as attachments, '. + 'count(distinct vote.user_id) as votes '. + 'from '. + TBL_BUG.' b '. + 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. + 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. + 'left join '.TBL_AUTH_USER.' lastmodifier on b.last_modified_by = lastmodifier.user_id '. + 'left join '.TBL_COMMENT.' comment on b.bug_id = comment.bug_id '. + 'left join '.TBL_ATTACHMENT.' attachment on b.bug_id = attachment.bug_id '. + 'left join '.TBL_BUG_VOTE.' vote on b.bug_id = vote.bug_id '. + 'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id '. + 'left join '.TBL_RESOLUTION.' resolution on b.resolution_id = resolution.resolution_id '. + 'left join '.TBL_DATABASE.' on b.database_id = '.TBL_DATABASE.'.database_id '. + 'left join '.TBL_VERSION.' version2 on b.to_be_closed_in_version_id = version2.version_id '. + 'left join '.TBL_VERSION.' version3 on b.closed_in_version_id = version3.version_id, '. + TBL_SEVERITY.' severity, '. + TBL_STATUS.' status, '. + TBL_OS.' os, '. + TBL_PRIORITY.' priority, '. + TBL_VERSION.' version, '. + TBL_COMPONENT.' component, '. + TBL_PROJECT.' project, '. + TBL_SITE.' site '. + 'where '. + 'b.severity_id = severity.severity_id '. + 'and b.priority = priority.priority_id '. + 'and b.status_id = status.status_id '. + 'and b.os_id = os.os_id '. + 'and b.version_id = version.version_id '. + 'and b.component_id = component.component_id '. + 'and b.project_id = project.project_id '. + 'and %s '. + 'and b.site_id = site.site_id '. + 'and b.bug_id <> %s '. + 'GROUP BY '. + 'b.bug_id '. + 'order by '. + '%s %s, '. + 'b.bug_id asc', + 'bug-printable' => + 'select '. + 'b.*, '. + 'reporter.login as reporter, '. + 'owner.login as owner, '. + 'project_name, '. + 'component_name, '. + 'version_name, '. + 'severity_name, '. + 'priority_name, '. + 'os_name, '. + 'status_name, '. + 'resolution_name '. + 'from '. + TBL_BUG.' b '. + 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. + 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. + 'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id '. + 'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '. + TBL_SEVERITY.' sv, '. + TBL_STATUS.' st, '. + TBL_OS.' os, '. + TBL_VERSION.' v, '. + TBL_PRIORITY.' priority, '. + TBL_COMPONENT.' c, '. + TBL_PROJECT.' p '. + 'where '. + 'b.bug_id = %s '. + 'and b.project_id not in (%s) '. + 'and b.severity_id = sv.severity_id '. + 'and b.priority = priority.priority_id '. + 'and b.os_id = os.os_id '. + 'and b.version_id = v.version_id '. + 'and b.component_id = c.component_id '. + 'and b.project_id = p.project_id '. + 'and b.status_id = st.status_id', + 'bug-show-bug' => + 'select '. + 'b.*, '. + 'reporter.login as reporter, '. + 'owner.login as owner, '. + 'status_name, '. + 'resolution_name '. + 'from '. + TBL_BUG.' b '. + 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. + 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. + 'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id '. + 'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '. + TBL_SEVERITY.' sv, '. + TBL_STATUS.' st, '. + TBL_SITE.' site, '. + TBL_PRIORITY.' prio '. + 'where '. + 'b.bug_id = %s '. + 'and b.project_id not in (%s) '. + 'and b.site_id = site.site_id '. + 'and b.severity_id = sv.severity_id '. + 'and b.status_id = st.status_id ', + 'and b.priority = prio.priority_id', + 'functions-bug-cc' => + 'select '. + 'b.user_id, '. + 'login '. + 'from '. + TBL_BUG_CC.' b '. + 'left join '.TBL_AUTH_USER.' using(user_id) '. + 'where '. + 'bug_id = %s', + 'functions-project-js' => + 'select '. + 'p.project_id, '. + 'project_name '. + 'from '. + TBL_PROJECT.' p '. + 'left join '.TBL_PROJECT_GROUP.' pg using(project_id) '. + 'where '. + 'active = 1 '. + 'and (pg.project_id is null or pg.group_id in (%s)) '. + 'group by '. + 'p.project_id, '. + 'p.project_name '. + 'order by '. + 'project_name', + 'include-template-bookmark' => + "SELECT ". + "sum(CASE WHEN s.status_id in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ), ". + "sum(CASE WHEN s.status_id not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ". + "from ". + TBL_BUG." b ". + "left join ".TBL_STATUS." s using(status_id), ". + TBL_BOOKMARK." w ". + "where ". + "w.user_id=%s ". + "AND w.bug_id = b.bug_id", + 'include-template-owner' => + "SELECT ". + "sum(CASE WHEN s.status_id in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ), ". + "sum(CASE WHEN s.status_id not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ". + "from ". + TBL_BUG." b ". + "left join ".TBL_STATUS." s using(status_id) ". + "where ". + "assigned_to = %s", + 'include-template-reporter' => + "SELECT ". + "sum(CASE WHEN s.status_id in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ), ". + "sum(CASE WHEN s.status_id not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ". + "from ". + TBL_BUG." b ". + "left join ".TBL_STATUS." s using(status_id) ". + "where ". + "created_by = %s", + 'index-projsummary-1' => + 'select project_name as "Project", '. + 'sum(case when resolution_id = 0 then 1 else 0 end) as "Open"', + 'index-projsummary-2' => + "select resolution_name, ", + 'index-projsummary-3' => + "', sum(case when resolution_id = '", + 'index-projsummary-4' => + "' then 1 else 0 end) as \"'", + 'index-projsummary-5' => + " from ".TBL_RESOLUTION, + 'index-projsummary-6' => + '%s, count(bug_id) as "Total" '. 'from '.TBL_BUG.' b '. - 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. - 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. - 'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id '. - 'left join '.TBL_RESOLUTION.' r on b.resolution_id = r.resolution_id, '. - TBL_SEVERITY.' sv, '.TBL_STATUS.' st, '.TBL_SITE.' site, '. - TBL_PRIORITY.' prio '. - 'where b.bug_id = %s and b.project_id not in (%s) '. - 'and b.site_id = site.site_id '. - 'and b.severity_id = sv.severity_id and b.status_id = st.status_id ', - 'and b.priority = prio.priority_id', - 'functions-bug-cc' => 'select b.user_id, login '. - 'from '.TBL_BUG_CC.' b left join '. TBL_AUTH_USER.' using(user_id) '. - 'where bug_id = %s', - 'functions-project-js' => 'select p.project_id, project_name '. - 'from '.TBL_PROJECT. ' p '. - 'left join '.TBL_PROJECT_GROUP.' pg using(project_id) '. - 'where active = 1 and (pg.project_id is null or pg.group_id in (%s)) '. - 'group by p.project_id, p.project_name '. + 'left join '.TBL_PROJECT.' p using (project_id) '. + 'where b.project_id not in (%s) '. + 'group by b.project_id, project_name '. 'order by project_name', - 'include-template-bookmark' => "SELECT sum(CASE WHEN s.status_id in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) , ". - "sum(CASE WHEN s.status_id not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ". - "from ".TBL_BUG." b left join ".TBL_STATUS." s using(status_id), ". - TBL_BOOKMARK." w where w.user_id=%s AND w.bug_id = b.bug_id", - 'include-template-owner' => "SELECT sum(CASE WHEN s.status_id ". - "in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) , ". - "sum(CASE WHEN s.status_id ". - "not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ". - "from ".TBL_BUG." b left join ".TBL_STATUS." s using(status_id) ". - "where assigned_to = %s", - 'include-template-reporter' => "SELECT sum(CASE WHEN s.status_id in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) , ". - "sum(CASE WHEN s.status_id not in (".OPEN_BUG_STATUSES.") THEN 1 ELSE 0 END ) ". - "from ".TBL_BUG." b left join ".TBL_STATUS." s using(status_id) ". - "where created_by = %s", - 'index-projsummary-1' => 'select project_name as "Project", '. + 'join-where' => + 'where', + 'query-list-bugs' => + 'select '. + '%s '. + 'from '. + TBL_BUG.' b '. + 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. + 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. + 'left join '.TBL_AUTH_USER.' lastmodifier on b.last_modified_by = lastmodifier.user_id '. + 'left join '.TBL_COMMENT.' comment on b.bug_id = comment.bug_id '. + 'left join '.TBL_ATTACHMENT.' attachment on b.bug_id = attachment.bug_id '. + 'left join '.TBL_BUG_VOTE.' vote on b.bug_id = vote.bug_id '. + 'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id '. + 'left join '.TBL_RESOLUTION.' resolution on b.resolution_id = resolution.resolution_id '. + 'left join '.TBL_DATABASE.' on b.database_id = '.TBL_DATABASE.'.database_id '. + 'left join '.TBL_VERSION.' version2 on b.to_be_closed_in_version_id = version2.version_id '. + 'left join '.TBL_VERSION.' version3 on b.closed_in_version_id = version3.version_id, '. + TBL_SEVERITY.' severity, '. + TBL_STATUS.' status, '. + TBL_OS.' os, '. + TBL_SITE.' site, '. + TBL_VERSION.' version, '. + TBL_COMPONENT.' component, '. + TBL_PROJECT.' project, '. + TBL_PRIORITY.' priority '. + 'where '. + 'b.severity_id = severity.severity_id '. + 'and b.priority = priority.priority_id '. + 'and b.status_id = status.status_id '. + 'and b.os_id = os.os_id '. + 'and b.site_id = site.site_id '. + 'and b.version_id = version.version_id '. + 'and b.component_id = component.component_id '. + 'and b.project_id = project.project_id %s '. + 'GROUP BY '. + 'b.bug_id '. + 'order by '. + '%s %s, '. + 'b.bug_id asc', + 'query-list-bugs-count' => + 'select '. + 'count(*) '. + 'from '. + TBL_BUG.' b '. + 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. + 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. + 'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id ', + 'query-list-bugs-count-join' => + 'where ', + 'report-resbyeng-1' => + 'select email as "Assigned To", '. 'sum(case when resolution_id = 0 then 1 else 0 end) as "Open"', - 'index-projsummary-2' => "select resolution_name, ", - 'index-projsummary-3' => "', sum(case when resolution_id = '", - 'index-projsummary-4' => "' then 1 else 0 end) as \"'", - 'index-projsummary-5' => " from ".TBL_RESOLUTION, - 'index-projsummary-6' => '%s, count(bug_id) as "Total" '. - 'from '.TBL_BUG. ' b left join '.TBL_PROJECT.' p using (project_id) '. - 'where b.project_id not in (%s) group by b.project_id, project_name '. - 'order by project_name', - 'query-list-bugs-count' => 'select count(*) '. + 'report-resbyeng-2' => + "select resolution_name, ", + 'report-resbyeng-3' => + "', sum(case when resolution_id = '", + 'report-resbyeng-4' => + "' then 1 else 0 end) as \"'", + 'report-resbyeng-5' => + " from ".TBL_RESOLUTION, + 'report-resbyeng-6' => + '%s, count(bug_id) as "Total" '. 'from '.TBL_BUG.' b '. - 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. - 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. - 'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id ', - 'query-list-bugs-count-join' => 'where ', - 'query-list-bugs' => 'select %s '. - 'from '.TBL_BUG.' b '. - 'left join '.TBL_AUTH_USER.' owner on b.assigned_to = owner.user_id '. - 'left join '.TBL_AUTH_USER.' reporter on b.created_by = reporter.user_id '. - 'left join '.TBL_AUTH_USER.' lastmodifier on b.last_modified_by = lastmodifier.user_id '. - 'left join '.TBL_COMMENT.' comment on b.bug_id = comment.bug_id '. - 'left join '.TBL_ATTACHMENT.' attachment on b.bug_id = attachment.bug_id '. - 'left join '.TBL_BUG_VOTE.' vote on b.bug_id = vote.bug_id '. - 'left join '.TBL_BOOKMARK.' bookmark on b.bug_id = bookmark.bug_id '. - 'left join '.TBL_RESOLUTION.' resolution on b.resolution_id = resolution.resolution_id '. - 'left join '.TBL_DATABASE.' on b.database_id = '.TBL_DATABASE.'.database_id '. - 'left join '.TBL_VERSION.' version2 on b.to_be_closed_in_version_id = version2.version_id '. - 'left join '.TBL_VERSION.' version3 on b.closed_in_version_id = version3.version_id, '. - TBL_SEVERITY.' severity, '.TBL_STATUS.' status, '.TBL_OS.' os, '.TBL_SITE.' site, '. - TBL_VERSION.' version, '.TBL_COMPONENT.' component, '.TBL_PROJECT.' project, '. - TBL_PRIORITY.' priority '. - 'where b.severity_id = severity.severity_id '. - 'and b.priority = priority.priority_id '. - 'and b.status_id = status.status_id and b.os_id = os.os_id '. - 'and b.site_id = site.site_id and b.version_id = version.version_id '. - 'and b.component_id = component.component_id '. - 'and b.project_id = project.project_id %s '. - 'GROUP BY b.bug_id '. - 'order by %s %s, b.bug_id asc', - 'report-resbyeng-1' => 'select email as "Assigned To", '. - 'sum(case when resolution_id = 0 then 1 else 0 end) as "Open"', - 'report-resbyeng-2' => "select resolution_name, ", - 'report-resbyeng-3' => "', sum(case when resolution_id = '", - 'report-resbyeng-4' => "' then 1 else 0 end) as \"'", - 'report-resbyeng-5' => " from ".TBL_RESOLUTION, - 'report-resbyeng-6' => '%s, count(bug_id) as "Total" '. - 'from '.TBL_BUG. ' b '. 'left join '.TBL_AUTH_USER.' u on assigned_to = user_id %s '. 'group by assigned_to, u.email', - 'join-where' => 'where', - 'admin-list-components' => 'select c.component_id, component_name, '. - 'c.created_date, active, sort_order, count(bug_id) as bug_count '. - 'from '.TBL_COMPONENT.' c left join '.TBL_BUG.' b using(component_id) '. - 'where c.project_id = %s '. - 'group by c.component_id, c.component_name, c.created_date, c.active', - 'admin-list-versions' => 'select v.version_id, version_name, '. - 'v.created_date, active, sort_order, count(bug_id) as bug_count '. - 'from '.TBL_VERSION.' v left join '.TBL_BUG.' b using(version_id) '. - 'where v.project_id = %s '. - 'group by v.version_id, v.version_name, v.created_date, v.active', ); ?> Index: oci8.php =================================================================== RCS file: /cvsroot/phpbt/phpbt/inc/db/oci8.php,v retrieving revision 1.18 retrieving revision 1.19 diff -u -r1.18 -r1.19 --- oci8.php 30 May 2005 19:59:35 -0000 1.18 +++ oci8.php 23 Aug 2005 21:09:42 -0000 1.19 @@ -1,187 +1,457 @@ <?php +// oci8.php - Oracle queries + $QUERY = array( - 'admin-list-groups' => 'select ag.group_id, ag.group_name, ag.locked, '. - 'count(ug.group_id) as count, assignable '. - 'from '.TBL_AUTH_GROUP.' ag, '.TBL_USER_GROUP.' ug,'.TBL_AUTH_USER.' au '. - 'where ag.group_id = ug.group_id(+) and ug.user_id = au.user_id(+) '. - 'group by ag.group_id, ag.group_name, ag.locked, ag.assignable '. - 'order by %s %s', - 'admin-list-oses' => 'select s.os_id, s.os_name, s.regex, s.sort_order, '. - 'count(b.bug_id) as bug_count '. - 'from '.TBL_OS.' s, '.TBL_BUG.' b '. - 'where s.os_id = b.os_id(+) '. - 'group by s.os_id, s.os_name, s.regex, s.sort_order '. - 'order by s.%s %s', - 'admin-show-version' => 'select v.*, p.project_name as project_name '. - 'from '.TBL_VERSION.' v, '.TBL_PROJECT.' p '. - 'where p.project_id = v.project_id(+) and version_id = \'%s\'', - 'admin-show-component' => 'select c.*, p.project_name as project_name '. - 'from '.TBL_COMPONENT.' c, '.TBL_PROJECT.' p '. - 'where p.project_id = c.project_id(+) and component_id = \'%s\'', - 'admin-list-resolutions' => 'select s.resolution_id, resolution_name, '. - 'resolution_desc, sort_order, count(bug_id) as bug_count '. - 'from '.TBL_RESOLUTION.' s, '.TBL_BUG.' b '. - 'where s.resolution_id = b.resolution_id(+) '. + 'admin-list-components' => + 'select '. + 'c.component_id, '. + 'component_name, '. + 'c.created_date, '. + 'active, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_COMPONENT.' c, '. + TBL_BUG.' b '. + 'where '. + 'c.project_id = %s '. + 'and c.component_id = b.component_id(+) '. + 'group by '. + 'c.component_id, '. + 'c.component_name, '. + 'c.created_date, '. + 'c.active', + 'admin-list-databases' => + 'select '. + 'd.database_id, '. + 'database_name, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_DATABASE.' d, '. + TBL_BUG.' b '. + 'where '. + 'd.database_id = b.database_id(+) '. + 'group by '. + 'd.database_id, '. + 'database_name, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-groups' => + 'select '. + 'ag.group_id, '. + 'ag.group_name, '. + 'ag.locked, '. + 'count(ug.group_id) as count, '. + 'assignable '. + 'from '. + TBL_AUTH_GROUP.' ag, '. + TBL_USER_GROUP.' ug,'. + TBL_AUTH_USER.' au '. + 'where '. + 'ag.group_id = ug.group_id(+) '. + 'and ug.user_id = au.user_id(+) '. + 'group by '. + 'ag.group_id, '. + 'ag.group_name, '. + 'ag.locked, '. + 'ag.assignable '. + 'order by '. + '%s %s', + 'admin-list-oses' => + 'select '. + 's.os_id, '. + 's.os_name, '. + 's.regex, '. + 's.sort_order, '. + 'count(b.bug_id) as bug_count '. + 'from '. + TBL_OS.' s, '. + TBL_BUG.' b '. + 'where '. + 's.os_id = b.os_id(+) '. + 'group by '. + 's.os_id, '. + 's.os_name, '. + 's.regex, '. + 's.sort_order '. + 'order by '. + 's.%s %s', + 'admin-list-resolutions' => + 'select '. + 's.resolution_id, '. + 'resolution_name, '. + 'resolution_desc, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_RESOLUTION.' s, '. + TBL_BUG.' b '. + 'where '. + 's.resolution_id = b.resolution_id(+) '. 'group by s.resolution_id, resolution_name, resolution_desc, sort_order '. 'order by %s %s', - 'admin-list-severities' => 'select s.severity_id, severity_name, '. - 'severity_desc, severity_color, sort_order, count(bug_id) as bug_count '. - 'from '.TBL_SEVERITY.' s, '.TBL_BUG.' b '. - 'where s.severity_id = b.severity_id(+) '. - 'group by s.severity_id, severity_name, severity_desc, severity_color, '. - 'sort_order '. - 'order by %s %s', - 'admin-list-databases' => 'select d.database_id, database_name, '. - 'sort_order, count(bug_id) as bug_count '. - 'from '.TBL_DATABASE.' d, '.TBL_BUG.' b '. - 'where d.database_id = b.database_id(+) '. - 'group by d.database_id, database_name, sort_order '. - 'order by %s %s', - 'admin-list-sites' => 'select s.site_id, site_name, sort_order, '. - 'count(bug_id) as bug_count from '.TBL_SITE. ' s, '.TBL_BUG.' b '. - 'where s.site_id = b.site_id(+) group by s.site_id, site_name, '. - 'sort_order order by %s %s', - 'admin-list-statuses' => 'select s.status_id, status_name, status_desc, '. - 'sort_order, bug_open, count(bug_id) as bug_count '. - 'from '.TBL_STATUS.' s, '.TBL_BUG.' b '. - 'where s.status_id = b.status_id(+) '. - 'group by s.status_id, status_name, status_desc, sort_order '. - 'order by %s %s', - 'admin-user-groups' => 'select ug.group_id '. - 'from '.TBL_USER_GROUP.' ug, ' . TBL_AUTH_GROUP.' g '. - 'where g.group_id = ug.group_id(+) and user_id = %s '. - 'and group_name <> \'User\'', - 'bug-history' => 'select bh.*, login '. - 'from '.TBL_BUG_HISTORY.' bh, '.TBL_AUTH_USER . - ' where user_id = bh.created_by(+) and bug_id = %s order by bh.created_date', - 'bug-cc-list' => 'select email '. - 'from '.TBL_BUG_CC.' bc, ' . TBL_AUTH_USER.' u, ' . TBL_USER_PREF.' p '. - 'where u.user_id = bc.user_id(+) and u.user_id = p.user_id '. - 'and email_notices = 1 and bug_id = %s', - 'bug-printable' => 'select b.*, reporter.login as reporter, '. - 'owner.login as owner, p.project_name, c.component_name, '. - 'v.version_name, s.severity_name, o.os_name, s.status_name, '. - 'r.resolution_name '. - 'from '.TBL_BUG.' b, '.TBL_AUTH_USER.' owner, '. - TBL_AUTH_USER.' reporter, '.TBL_RESOLUTION.' r, '.TBL_SEVERITY.' sv, '. - TBL_STATUS. ' st, ' . TBL_OS.' os, '.TBL_VERSION.' v, '. - TBL_COMPONENT.' c, ' . TBL_PROJECT.' p ', - 'where b.assigned_to = owner.user_id(+) '. - 'and b.created_by = reporter.user_id(+) '. - 'and b.resolution_id = r.resolution_id(+) and b.os_id = os.os_id '. - 'and b.version_id = v.version_id and b.component_id = c.component_id '. - 'and b.project_id = p.project_id and b.severity_id = sv.severity_id '. - 'and b.status_id = st.status_id and bug_id = %s and b.project_id not in (%s)', - 'bug-prev-next' => 'select b.bug_id, reporter.login as reporter, '. - 'owner.login as owner '. - 'from '.TBL_BUG.' b, '.TBL_AUTH_USER.' owner, '.TBL_AUTH_USER.' reporter, '. - TBL_AUTH_USER.' lastmodifier, ' . TBL_RESOLUTION.' resolution, '. - TBL_DATABASE.' database, '.TBL_VERSION.' version2, '. - TBL_VERSION.' version3, '.TBL_SEVERITY.' severity, '. - TBL_STATUS.' status, '.TBL_OS.' os, '. - TBL_VERSION.' version, '.TBL_COMPONENT.' component, '. - TBL_PROJECT.' project, '.TBL_SITE.' site '. - 'where b.assigned_to = owner.user_id(+) '. - 'and b.created_by = reporter.user_id(+) '. - 'and b.last_modified_by = lastmodifier.user_id(+) '. - 'and b.resolution_id = resolution.resolution_id(+) '. - 'and b.database_id = database.database_id(+) '. - 'and b.to_be_closed_in_version_id = version2.version_id(+) '. - 'and b.closed_in_version_id = version3.version_id(+) '. - 'and b.severity_id = severity.severity_id '. - 'and b.status_id = status.status_id and b.os_id = os.os_id '. - 'and b.version_id = version.version_id '. - 'and b.component_id = component.component_id '. - 'and b.site_id = site.site_id '. - 'and b.project_id = project.project_id and %s and bug_id <> %s '. - 'order by %s %s, bug_id asc', - 'bug-show-bug' => 'select b.*, reporter.login as reporter, '. - 'owner.login as owner, r.resolution_name, st.status_name '. - 'from '.TBL_BUG.' b, '.TBL_AUTH_USER.' owner, '.TBL_AUTH_USER.' reporter, '. - TBL_RESOLUTION.' r, '.TBL_SEVERITY.' sv, '. - TBL_STATUS.' st, '.TBL_SITE.' site '. - 'where b.resolution_id = r.resolution_id(+) '. - 'and b.assigned_to = owner.user_id(+) '. - 'and b.created_by = reporter.user_id(+) '. - 'and b.severity_id = sv.severity_id and b.status_id = st.status_id '. - 'and b.site_id = site.site_id '. - 'and bug_id = %s and b.project_id not in (%s)', - 'functions-bug-cc' => 'select b.user_id, login '. - 'from '.TBL_BUG_CC.' b, '. TBL_AUTH_USER. - ' where phpbt_auth_user.user_id = b.user_id(+) and bug_id = %s', - 'functions-project-js' => 'select p.project_id, project_name '. - 'from ' . TBL_PROJECT . ' p, ' . TBL_PROJECT_GROUP . ' pg '. - 'where p.project_id = pg.project_id(+) and active = 1 '. - 'and (pg.project_id is null or pg.group_id in (%s)) '. - 'group by p.project_id, p.project_name order by project_name', - 'include-template-owner' => SELECT sum(decode( s.status_id in (".OPEN_BUG_STATUSES.") , 1, 1, 0 )), ". - "sum(decode( s.status_id not in (".OPEN_BUG_STATUSES.") , 1, 1, 0 )), ". - 'from '.TBL_BUG.' b, '.TBL_STATUS.' s '. - 'where b.status_id = s.status_id (+) and b.assigned_to = %s', - 'include-template-reporter' => SELECT sum(decode( s.status_id in (".OPEN_BUG_STATUSES.") , 1, 1, 0 )) , ". - "sum(decode( s.status_id not in (".OPEN_BUG_STATUSES.") , 1, 1, 0 )) ". - 'from '.TBL_BUG.' b, ' . TBL_STATUS.' s '. - 'where b.status_id = s.status_id (+) and b.created_by = %s', - 'index-projsummary-1' => 'select b.project_id, p.project_name as "Project", '. + 'admin-list-severities' => + 'select '. + 's.severity_id, '. + 'severity_name, '. + 'severity_desc, '. + 'severity_color, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_SEVERITY.' s, '. + TBL_BUG.' b '. + 'where '. + 's.severity_id = b.severity_id(+) '. + 'group by '. + 's.severity_id, '. + 'severity_name, '. + 'severity_desc, '. + 'severity_color, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-sites' => + 'select '. + 's.site_id, '. + 'site_name, '. + 'sort_order, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_SITE.' s, '. + TBL_BUG.' b '. + 'where '. + 's.site_id = b.site_id(+) '. + 'group by '. + 's.site_id, '. + 'site_name, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-statuses' => + 'select '. + 's.status_id, '. + 'status_name, '. + 'status_desc, '. + 'sort_order, '. + 'bug_open, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_STATUS.' s, '. + TBL_BUG.' b '. + 'where '. + 's.status_id = b.status_id(+) '. + 'group by '. + 's.status_id, '. + 'status_name, '. + 'status_desc, '. + 'sort_order '. + 'order by '. + '%s %s', + 'admin-list-versions' => + 'select '. + 'v.version_id, '. + 'version_name, '. + 'v.created_date, '. + 'active, '. + 'count(bug_id) as bug_count '. + 'from '. + TBL_VERSION.' v, '. + TBL_BUG.' b '. + 'where '. + 'v.project_id = %s '. + 'and v.version_id = b.version_id(+) '. + 'group by '. + 'v.version_id, '. + 'v.version_name, '. + 'v.created_date, '. + 'v.active', + 'admin-show-component' => + 'select '. + 'c.*, '. + 'p.project_name as project_name '. + 'from '. + TBL_COMPONENT.' c, '. + TBL_PROJECT.' p '. + 'where '. + 'p.project_id = c.project_id(+) '. + 'and component_id = \'%s\'', + 'admin-show-version' => + 'select '. + 'v.*, '. + 'p.project_name as project_name '. + 'from '. + TBL_VERSION.' v, '. + TBL_PROJECT.' p '. + 'where '. + 'p.project_id = v.project_id(+) '. + 'and version_id = \'%s\'', + 'admin-user-groups' => + 'select '. + 'ug.group_id '. + 'from '. + TBL_USER_GROUP.' ug, '. + TBL_AUTH_GROUP.' g '. + 'where '. + 'g.group_id = ug.group_id(+) '. + 'and user_id = %s '. + 'and group_name <> \'User\'', + 'bug-cc-list' => + 'select '. + 'email '. + 'from '. + TBL_BUG_CC.' bc, '. + TBL_AUTH_USER.' u, '. + TBL_USER_PREF.' p '. + 'where '. + 'u.user_id = bc.user_id(+) '. + 'and u.user_id = p.user_id '. + 'and email_notices = 1 '. + 'and bug_id = %s', + 'bug-history' => + 'select '. + 'bh.*, '. + 'login '. + 'from '. + TBL_BUG_HISTORY.' bh, '. + TBL_AUTH_USER.' '. + 'where '. + 'user_id = bh.created_by(+) '. + 'and bug_id = %s '. + 'order by '. + 'bh.created_date', + 'bug-prev-next' => + 'select '. + 'b.bug_id, '. + 'reporter.login as reporter, '. + 'owner.login as owner '. + 'from '. + TBL_BUG.' b, '. + TBL_AUTH_USER.' owner, '. + TBL_AUTH_USER.' reporter, '. + TBL_AUTH_USER.' lastmodifier, '. + TBL_RESOLUTION.' resolution, '. + TBL_DATABASE.' database, '. + TBL_VERSION.' version2, '. + TBL_VERSION.' version3, '.... [truncated message content] |
