From: Roland Mas <lolando@debian.org>
Date: Thu, 10 Dec 2009 13:05:04 +0000 (+0000)
Subject: First implementation of db_query_qpa
X-Git-Tag: v5_0_rc1~372
X-Git-Url: https://scm.fusionforge.org/anonscm/gitweb?p=fusionforge%2Ffusionforge.git;a=commitdiff_plain;h=99164d781cfe7b2923d204ef32e8612cc4b2320f

First implementation of db_query_qpa
---

diff --git a/gforge/common/include/database-pgsql.php b/gforge/common/include/database-pgsql.php
index 9383189524..0968e149cc 100644
--- a/gforge/common/include/database-pgsql.php
+++ b/gforge/common/include/database-pgsql.php
@@ -164,6 +164,21 @@ function db_query_params($qstring,$params,$limit='-1',$offset=0,$dbserver=SYS_DB
 	return $res;
 }
 
+/**
+ *  db_query_params() - Query the database, with a query+params array
+ *
+ *  @param array array(query, array(parameters...))
+ *  @param int How many rows do you want returned.
+ *  @param int Of matching rows, return only rows starting here.
+ *  @param int ability to spread load to multiple db servers.
+ *  @return int result set handle.
+ */
+function db_query_qpa ($qpa,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
+	$sql = $qpa[0] ;
+	$params = $qpa[1] ;
+	return db_query_params ($sql, $params, $limit, $offset, $dbserver) ;
+}
+
 /**
  *  db_mquery() - Query the database.
  *
@@ -455,6 +470,31 @@ function db_string_array_to_any_clause ($arr) {
 	return $res ;
 }
 
+function db_construct_qpa ($old_qpa = false, $new_sql = '', $new_params = array ()) {
+	if (!is_array($old_qpa), count ($old_qpa) < 3) {
+		$old_qpa = array ('', array(), 0) ;
+	}
+	$old_sql = $old_qpa[0] ;
+	$old_params = $old_qpa[1] ;
+	$old_max = $old_qpa[2] ;
+
+	$sql = $old_sql ;
+	$params = $old_params ;
+	$max = $old_max ;
+
+	foreach ($new_params as $index => $value) {
+		$sql = strreplace ($new_sql, '$'.$index, '$'.($index + $old_max)) ;
+		$params[] = $value ;
+		$max++ ;
+	}
+	
+	return array ($sql, $params, $max) ;
+}
+	
+function db_join_qpa ($old_qpa = false, $new_qpa = false) {
+	return db_construct_qpa ($old_qpa, $new_qpa[0], $new_qpa[1]) ;
+}
+
 // Local Variables:
 // mode: php
 // c-file-style: "bsd"
diff --git a/gforge/www/softwaremap/trove_list.php b/gforge/www/softwaremap/trove_list.php
index 7dc95d3592..7abd5c1e48 100644
--- a/gforge/www/softwaremap/trove_list.php
+++ b/gforge/www/softwaremap/trove_list.php
@@ -91,9 +91,10 @@ $discrim = getStringFromRequest('discrim');
 $discrim_url = '';
 $discrim_desc = '';
 
+$qpa_alias = db_construct_qpa () ;
+$qpa_and = db_construct_qpa () ;
+
 if ($discrim) {
-	$discrim_queryalias = '';
-	$discrim_queryand = '';
 	$discrim_url_b = array();
 
 	// commas are ANDs
@@ -109,16 +110,13 @@ if ($discrim) {
 		$expl_discrim[$i] = intval($expl_discrim[$i]);
 
 		// need one aliased table for everything
-//[CB]		$discrim_queryalias .= ', trove_group_link trove_group_link_'.$i.' ';
-		$discrim_queryalias .= ', trove_agg trove_agg_'.$i.' ';
+		$qpa_alias = db_construct_qpa ($qpa_alias,
+					       ', trove_agg trove_agg_'.$i) ;
 		
 		// need additional AND entries for aliased tables
-//[CB]		$discrim_queryand .= 'AND trove_group_link_'.$i.'.trove_cat_id='
-//[CB]			.$expl_discrim[$i].' AND trove_group_link_'.$i.'.group_id='
-//[CB]			.'trove_group_link.group_id ';
-		$discrim_queryand .= 'AND trove_agg_'.$i.'.trove_cat_id='
-			.$expl_discrim[$i].' AND trove_agg_'.$i.'.group_id='
-			.'trove_agg.group_id ';
+		$qpa_and = db_construct_qpa ($qpa_and,
+					     sprintf (' AND trove_agg_%d.trove_cat_id=$%d AND trove_agg_%d.group_id=trove_agg.group_id ', $i, $i+1, $i),
+					     array ($expl_discrim[$i])) ;
 
 		$expl_discrim_b = array () ;
 		for ($j=0;$j<sizeof($expl_discrim);$j++) {
@@ -235,14 +233,14 @@ if(!isset($discrim_queryand)) {
 	$discrim_queryand = '';
 }
 
-$res_grp = db_query("
-	SELECT * 
-	FROM trove_agg
-	$discrim_queryalias
-	WHERE trove_agg.trove_cat_id='$form_cat'
-	$discrim_queryand
-	ORDER BY trove_agg.trove_cat_id ASC, trove_agg.ranking ASC
-", $TROVE_HARDQUERYLIMIT, 0, SYS_DB_TROVE);
+$qpa = db_construct_qpa () ;
+$qpa = db_construct_qpa ($qpa, 'SELECT * FROM trove_agg') ;
+$qpa = db_join_qpa ($qpa, $qpa_alias) ;
+$qpa = db_construct_qpa ($qpa, ' WHERE trove_agg.trove_cat_id=$1', array ($form_cat)) ;
+$qpa = db_join_qpa ($qpa, $qpa_and) ;
+$qpa = db_construct_qpa ($qpa, ' ORDER BY trove_agg.trove_cat_id ASC, trove_agg.ranking ASC') ;
+$res_grp = db_query_qpa ($qpa, $TROVE_HARDQUERYLIMIT, 0, SYS_DB_TROVE);
+
 echo db_error();
 $querytotalcount = db_numrows($res_grp);
 	
@@ -333,4 +331,9 @@ if ($querytotalcount > $TROVE_BROWSELIMIT) {
 
 $HTML->footer(array());
 
+// Local Variables:
+// mode: php
+// c-file-style: "bsd"
+// End:
+
 ?>