[AppDB] First step to integrating the AppDB with Bugzilla

[Tony Lambregts]

Over two years ago this was disccused and the concensus at the time was that 
integrating the AppDB and Bugzilla would be a Good Idea (TM). This patch is my 
attempt to get it started.

I am submitting this to wine devel first because I KNOW this patch can be 
controversial. I have tried to make this patch as small as possible but it still 
covers quite a few files. I am useing Globals and that should be improved. and I 
am sure there are other things that are wrong with it. ;^) Also the code in 
preferences.php does not do anything except show that this works.

Finally in order for it to work Jeremy Newman will need to set up the 
include/config.php in the appdb directory.

Please try it out and get back to me..

Change Log: Integrate AppDB with Bugzilla to use the versions table from Buzilla.

Files changed: preferences.php include/config.php.sample include/util.php

Files Added: tables/bugzilla_tables.sql tables/create_bugzilla_tables

Index: preferences.php
===================================================================
RCS file: /home/wine/appdb/preferences.php,v
retrieving revision 1.2
diff -u -r1.2 preferences.php
--- preferences.php	10 Dec 2004 01:07:45 -0000	1.2
+++ preferences.php	11 Dec 2004 04:45:44 -0000
@@ -50,6 +50,16 @@
    $ext_email = $user->lookup_email($_SESSION['current']->userid);
    
    include(BASE."include/"."form_edit.php");
+        $version = "unspecified";
+        echo "<tr><td>&nbsp; wine version </td><td>";
+        make_bugzilla_version_list("version", $version);
+        echo "</td></tr>";
+/*      <tr>
+	<td> &nbsp; Email Address </td>
+	<td> <input type="text" name="ext_email" value="<?=$ext_email;?>"> </td>
+      </tr>
+*/
+
 }

if($HTTP_POST_VARS)
Index: include/config.php.sample
===================================================================
RCS file: /home/wine/appdb/include/config.php.sample,v
retrieving revision 1.2
diff -u -r1.2 config.php.sample
--- include/config.php.sample	24 Mar 2004 15:51:49 -0000	1.2
+++ include/config.php.sample	11 Dec 2004 04:45:45 -0000
@@ -24,4 +24,14 @@
 $userdb_dbhost = "localhost";
 $userdb_db     = "apidb";

+/*
+ * Bugzilla database info
+ */
+$bugzilla_dbuser = "root";
+$bugzilla_dbpass = "";
+$bugzilla_dbhost = "localhost";
+$bugzilla_db     = "bugs";
+/* $bugzilla_db_port = 3306; is this needed? I dont think so.*/
+$bugzilla_product_id = 1;
+
 ?>
Index: include/util.php
===================================================================
RCS file: /home/wine/appdb/include/util.php,v
retrieving revision 1.7
diff -u -r1.7 util.php
--- include/util.php	10 Dec 2004 01:07:45 -0000	1.7
+++ include/util.php	11 Dec 2004 04:45:45 -0000
@@ -171,6 +171,69 @@
     }
 }

+/* bugzilla functions */
+
+function openbugzilladb()
+{
+    global $bugzilla_dbuser, $bugzilla_dbpass, $bugzilla_dbhost, $bugzilla_db, $bugzilla_product_id;
+    global $dbcon, $dbref;
+
+    $dbref++;
+
+    if($dbcon)
+	return $dbcon;
+
+    $dbcon = mysql_connect($bugzilla_dbhost, $bugzilla_dbuser, $bugzilla_dbpass);
+    if(!$dbcon) 
+	{
+	    echo "An error occurred: ".mysql_error()."<p>\n";
+	    exit;
+	}
+    mysql_select_db($bugzilla_db);
+    return $dbcon;
+}
+
+function closebugzilladb()
+{
+    global $dbcon, $dbref;
+
+    if(--$dbref)
+	return;
+
+    mysql_close($adbcon);
+}
+
+function make_bugzilla_version_list($varname, $cvalue)
+{
+    global $bugzilla_db, $bugzilla_product_id;
+
+    $table = $bugzilla_db.".versions";
+    $where = "WHERE product_id=".$bugzilla_product_id;
+    $query = "SELECT value FROM $table $where ORDER BY value";
+
+    openbugzilladb();
+
+    $result = mysql_query($query);
+
+    if(!$result)
+    {
+        closebugzilladb();
+
+        return; // Oops
+    }
+    echo "<select name='$varname'>\n";
+    echo "<option value=0>Choose ...</option>\n";
+    while(list($value) = mysql_fetch_row($result))
+    {
+        if($id == $cvalue)
+            echo "<option value=$value selected>$value\n";
+        else
+            echo "<option value=$value>$value\n";
+    }
+    echo "</select>\n";
+    closebugzilladb();
+}
+
 /* get the number of applications in the appQueue table */
 function getQueuedAppCount()
 {
--- /dev/null	2003-01-30 03:24:37.000000000 -0700
+++ tables/create_bugzilla_tables	2004-12-10 13:41:28.000000000 -0700
@@ -0,0 +1,4 @@
+#! /bin/sh
+
+echo Creating fake Bugzilla tables
+mysql -u root < bugzilla_tables.sql
--- /dev/null	2003-01-30 03:24:37.000000000 -0700
+++ tables/bugzilla_tables.sql	2004-12-10 14:10:49.000000000 -0700
@@ -0,0 +1,62 @@
+create database if not exists bugs;
+
+use bugs;
+
+drop table if exists versions;
+
+
+/*
+ * versions information
+ */
+create table versions (
+       value tinytext,
+       product_id smallint not null
+);
+
+INSERT INTO versions VALUES ('unspecified', 1 );
+INSERT INTO versions VALUES ('20010112', 1 );
+INSERT INTO versions VALUES ('20010216', 1 );
+INSERT INTO versions VALUES ('20010305', 1 );
+INSERT INTO versions VALUES ('20010326', 1 );
+INSERT INTO versions VALUES ('20010418', 1 );
+INSERT INTO versions VALUES ('20010510', 1 );
+INSERT INTO versions VALUES ('20010629', 1 );
+INSERT INTO versions VALUES ('20010824', 1 );
+INSERT INTO versions VALUES ('20011004', 1 );
+INSERT INTO versions VALUES ('20011108', 1 );
+INSERT INTO versions VALUES ('20020228', 1 );
+INSERT INTO versions VALUES ('20020310', 1 );
+INSERT INTO versions VALUES ('20020411', 1 );
+INSERT INTO versions VALUES ('20020509', 1 );
+INSERT INTO versions VALUES ('20020605', 1 );
+INSERT INTO versions VALUES ('20020710', 1 );
+INSERT INTO versions VALUES ('20020804', 1 );
+INSERT INTO versions VALUES ('20020904', 1 );
+INSERT INTO versions VALUES ('20021007', 1 );
+INSERT INTO versions VALUES ('20021031', 1 );
+INSERT INTO versions VALUES ('20021125', 1 );
+INSERT INTO versions VALUES ('20021219', 1 );
+INSERT INTO versions VALUES ('20030115', 1 );
+INSERT INTO versions VALUES ('20030219', 1 );
+INSERT INTO versions VALUES ('20030318', 1 );
+INSERT INTO versions VALUES ('20030408', 1 );
+INSERT INTO versions VALUES ('20030508', 1 );
+INSERT INTO versions VALUES ('20030618', 1 );
+INSERT INTO versions VALUES ('20030709', 1 );
+INSERT INTO versions VALUES ('20030813', 1 );
+INSERT INTO versions VALUES ('20030911', 1 );
+INSERT INTO versions VALUES ('20031016', 1 );
+INSERT INTO versions VALUES ('20031118', 1 );
+INSERT INTO versions VALUES ('20031212', 1 );
+INSERT INTO versions VALUES ('20040121', 1 );
+INSERT INTO versions VALUES ('20040213', 1 );
+INSERT INTO versions VALUES ('20040309', 1 );
+INSERT INTO versions VALUES ('20040408', 1 );
+INSERT INTO versions VALUES ('20040505', 1 );
+INSERT INTO versions VALUES ('20040615', 1 );
+INSERT INTO versions VALUES ('20040716', 1 );
+INSERT INTO versions VALUES ('20040813', 1 );
+INSERT INTO versions VALUES ('20040914', 1 );
+INSERT INTO versions VALUES ('20041019', 1 );
+INSERT INTO versions VALUES ('20041201', 1 );
+INSERT INTO versions VALUES ('CVS', 1 );
Index: README
===================================================================
RCS file: /home/wine/appdb/README,v
retrieving revision 1.3
diff -u -r1.3 README
--- README	25 Mar 2004 16:29:47 -0000	1.3
+++ README	11 Dec 2004 05:10:49 -0000
@@ -1,32 +1,70 @@
-WineHQ Application Database
-------------------------------------------------------------------------
-Authors:
-    Jeremy Newman 
-    Charles Leop 
-    
-Contributors:
-    Paul van Schayck 
-    Chris Morgan 
+WineHQ Application Database
+------------------------------------------------------------------------
+Authors:
+    Jeremy Newman 
+    Charles Leop 
+    
+Contributors:
+    Paul van Schayck 
+    Chris Morgan 
+
+
+To install locally for testing/hacking:
+------------------------------------------------------------------------
+
+- Symlink from /var/www to the appdb directory
+
+- Copy include/config.php.sample to include/config.php
+
+- Edit include/config.php as you see fit, the default name of the database 
+  used in the table creation step below is "apidb", you'll have to modify 
+  these files if you change this in config.php
+
+- cd tables, run ./create_tables to create the database tables
+
+- Try to open up localhost/appdb, if you get a directory listing
+  Edit your /etc/apache/httpd.conf "DirectoryIndex" to include index.php 
+  so apache will open index.php after not finding index.html/index.htm etc
+
+- Check your /etc/php/php.ini to ensure that
+  'register_globals = On' (variables won't get passed)
+  'magic_quotes_gpc = Off' (you would end up with \ everywhere)
+  'session.bug_compat_42' = On' (Problem with auto-globals and session variables)

+------------------------------------------------------------------------
+You can create ether set up a real bugzilla database or a fake one 
+
+- cd tables, run ./create_bugzilla_tables to create the fake bugzilla tables

-To install locally for testing/hacking:
-------------------------------------------------------------------------
+- installing a real bugzilla database (hope this helps)

-- Symlink from /var/www to the appdb directory
+# download buzilla  (password for cvs is cvs)

-- Copy include/config.php.sample to include/config.php
+cd /var/www/html
+export CVSROOT=:pserver:[e-mail]:/home/wine
+cvs login
+cvs -z 0 checkout bugzilla

-- Edit include/config.php as you see fit, the default name of the database 
-  used in the table creation step below is "apidb", you'll have to modify 
-  these files if you change this in config.php
+# change directory to bugzilla and change the group ownership to apache.

-- cd tables, run ./create_tables to create the database tables
+cd bugzilla
+chgrp -R apache .
+chmod -R g+rX .

-- Try to open up localhost/appdb, if you get a directory listing
-  Edit your /etc/apache/httpd.conf "DirectoryIndex" to include index.php 
-  so apache will open index.php after not finding index.html/index.htm etc

-- Check your /etc/php/php.ini to ensure that
-  'register_globals = On' (variables won't get passed)
-  'magic_quotes_gpc = Off' (you would end up with \ everywhere)
-  'session.bug_compat_42' = On' (Problem with auto-globals and session variables)
+# change to su and run ./checksetup.pl and fix up what it finds wrong.
+# this may take several runs 
+
+su
+./checksetup.pl
+
+# in the /etc/httpd/conf/hppt.conf file find "AddHandler cgi-script .cgi" and add the following.
+
+<Directory "/var/www/html/bugzilla">
+Options ExecCGI
+AllowOverride Limit
+Order allow,deny
+Allow from all
+</Directory>
+
+# edit local config as nessary to allow access.

[Paul van Schayck]

Hey Tony,

A few comments.

On Fri, 10 Dec 2004 21:52:45 -0700, Tony Lambregts
 wrote:
> -
>   <td> <input type="text" name="ext_email" value="<?=$ext_email;?>"> 

Shorttags. I don't like them. They can be turned of in php.ini to
allow the (now more often appearing) <?xml tag.
> +/* $bugzilla_db_port = 3306; is this needed? I dont think so.*/

3306 is default. So, no not really needed.
> +Contributors:
> - Paul van Schayck

Can this be changed to [e-mail]? Or just removed.

Kind regards,
Paul van Schayck

Paul van Schayck wrote:
> Hey Tony,
>
> A few comments.
>
> On Fri, 10 Dec 2004 21:52:45 -0700, Tony Lambregts
>  wrote:
> > -
> >   <td> <input type="text" name="ext_email" value="<?=$ext_email;?>"> 
>
> Shorttags. I don't like them. They can be turned of in php.ini to
> allow the (now more often appearing) <?xml tag.

Debugging Cruft (from the original code)
> > +/* $bugzilla_db_port = 3306; is this needed? I dont think so.*/
>
> 3306 is default. So, no not really needed.
> > +Contributors:
> > - Paul van Schayck
>
> Can this be changed to [e-mail]? Or just removed.

Can do.
> Kind regards,
> Paul van Schayck