Re: ready for some testing

On Nov 26, 2003, at 21:47, Russell Valentine wrote:

>> Basically, we have two really convoluted SQL queries for
>> the text reports and the advanced query, neither of which work with
>> postgresql...
>
> 1)
>
> In the advanced query. Atleast in PostgreSQL < 7.3 you need to use 
> 'AS'.
>
> Example:
>
> SELECT products.name AS product FROM products;

Ahhh.... I was looking at that and thought that might be a problem.

> 2)
>
> DATE_FORMAT should be to_char instead. Also the patterns are different.
> http://www.postgresql.org/docs/7.3/static/functions-formatting.html
>
> Example:
>
> Something like this:
> Note: I usualy store dates as timestamps type instead of string.

Yeah, the string should be identical to the timestamp type (well, it is 
in mysql anyways).  I chose to use a varchar rather than a timestamp 
because it seemed more flexible, but in retrospect it might not have 
been necessary.

> SELECT to_char(to_date(creation, 'YYYYMMDDHH24MISS'), 'Mon/DD/YY') 
> from bugs;
>
> Note: I had to use single quotes here to.
>
> 3)
>
> With the joins it seems like it doesn't want to take AS names when 
> doing
> it (Perhaps something that should be put/fixed into PostgreSQL);
>
> So I made the query work although (without $user_options, or
> $ORDER_BY_LIST). This is the query I came up with.I didn't check
> $user_options or $ORDER_BY_LIST but I'll check on that later hope this
> helps for now.
>
> SELECT bid, cc, private, url, products.name as product, 
> components.name as component,
>        versions.name as version, shortdesc, severity_d.name as 
> severity,
>        priority_d.name as priority, status_d.name as status, 
> resolve_d.name as resolution,
>        u1.name as assigned, u2.name as reporter ,
>        to_char(to_date(creation, 'YYYYMMDDHH24MISS'), 'Mon/DD/YY') as 
> creation,
>        to_char(to_date(modified, 'YYYYMMDDHH24MISS'), 'Mon/DD/YY') as 
> modified
> FROM bugs,products,components,versions,status_d,priority_d,severity_d, 
> users u1, users u2
> LEFT JOIN resolve_d on resolve_d.id = resolve_d.name
> WHERE status_d.id = status and bugs.severity = severity_d.id and 
> priority = priority_d.id
> and bugs.product = products.pid and component = components.cid and 
> version = versions.vid
> and u1.uid = bugs.assigned and u2.uid = bugs.reporter
> and ($user_options)
> $ORDER_BY_LIST

Ok, I changed functions.inc.php with this (well, I wrapped it in 
testing for the db_type so that we're not mucking with the mysql 
query).  This is the patch to the current revision in CVS (not going to 
commit it until I can check it).

--- functions.inc.php~  Thu Nov 27 11:26:55 2003
+++ functions.inc.php   Thu Nov 27 11:26:55 2003
@@ -1454,16 +1454,31 @@
      }

      // PGSQL FIX: this query is not valid for postgres
-    $query = "\nselect bid, cc, private, url, products.name product, 
components.name component, versions.name version, shortdesc, 
severity_d.name severity,  "
-           . "\n priority_d.name priority, status_d.name status, 
resolve_d.name resolution, u1.name assigned, u2.name reporter ,  "
-           . "\n DATE_FORMAT(creation,\"%b/%e/%y\") creation, 
DATE_FORMAT(modified,\"%b/%e/%y\") modified "
-           . "\n from 
bugs,products,components,versions,status_d,priority_d,severity_d, users 
u1, users u2 "
-           . "\n left join resolve_d on resolve_d.id = resolution "
-           . "\n where status_d.id = status and bugs.severity = 
severity_d.id and priority = priority_d.id "
-           . "\n and bugs.product = products.pid and component = 
components.cid and version = versions.vid "
-           . "\n and u1.uid = bugs.assigned and u2.uid = bugs.reporter 
"
-           . "\n and ($user_options) "
-           . "\n $ORDER_BY_LIST ";
+    if ($_CONF['db_type'] == "pgsql")
+    {
+        $query = "\nselect bid, cc, private, url, products.name AS 
product, components.name AS component, versions.name AS version, 
shortdesc, severity_d.name AS severity,  "
+               . "\n priority_d.name AS priority, status_d.name AS 
status, resolve_d.name AS resolution, u1.name AS assigned, u2.name AS 
reporter ,  "
+               . "\n to_char(to_date(creation, 'YYYYMMDDHH24MISS'), 
'Mon/DD/YY') AS creation, "
+               . "\n to_char(to_date(modified, 'YYYYMMDDHH24MISS'), 
'Mon/DD/YY') AS modified "
+               . "\n from 
bugs,products,components,versions,status_d,priority_d,severity_d, users 
u1, users u2 "
+               . "\n left join resolve_d on resolve_d.id = 
resolve_d.name "
+               . "\n where status_d.id = status and bugs.severity = 
severity_d.id and priority = priority_d.id "
+               . "\n and bugs.product = products.pid and component = 
components.cid and version = versions.vid "
+               . "\n and u1.uid = bugs.assigned and u2.uid = 
bugs.reporter "
+               . "\n and ($user_options) "
+               . "\n $ORDER_BY_LIST ";
+    } else {
+        $query = "\nselect bid, cc, private, url, products.name 
product, components.name component, versions.name version, shortdesc, 
severity_d.name severity,  "
+               . "\n priority_d.name priority, status_d.name status, 
resolve_d.name resolution, u1.name assigned, u2.name reporter ,  "
+               . "\n DATE_FORMAT(creation,\"%b/%e/%y\") creation, 
DATE_FORMAT(modified,\"%b/%e/%y\") modified "
+               . "\n from 
bugs,products,components,versions,status_d,priority_d,severity_d, users 
u1, users u2 "
+               . "\n left join resolve_d on resolve_d.id = resolution "
+               . "\n where status_d.id = status and bugs.severity = 
severity_d.id and priority = priority_d.id "
+               . "\n and bugs.product = products.pid and component = 
components.cid and version = versions.vid "
+               . "\n and u1.uid = bugs.assigned and u2.uid = 
bugs.reporter "
+               . "\n and ($user_options) "
+               . "\n $ORDER_BY_LIST ";
+    }
      return($query);
  }

---
MandrakeSoft Security; http://www.mandrakesecure.net/
Online Security Resource Book; http://linsec.ca/
"lynx -source http://linsec.ca/vdanen.asc | gpg --import"
{FE6F2AFD : 88D8 0D23 8D4B 3407 5BD7  66F9 2043 D0E5 FE6F 2AFD}

_______________________________________________
Anthill-workers mailing list
Anthill-workers-+mQDA36h6dWw5LPnMra/[email protected]
http://vmlinuz.ca/mailman/listinfo/anthill-workers