[basedb-cvs] basedb/www acquisition.inc.php,1.14,1.15 image.inc.php,1.12,1.13 job.inc.php,1.36,1.37 mysql.inc.php,1.14,1.15 pgsql.inc.php,1.10,1.11 plot.inc.php,1.21,1.22 raw.inc.php,1.35,1.36 ware.inc.php,1.11,1.12

[<tr...@us...>]

Update of /cvsroot/basedb/basedb/www
In directory usw-pr-cvs1:/tmp/cvs-serv30948

Modified Files:
	acquisition.inc.php image.inc.php job.inc.php mysql.inc.php 
	pgsql.inc.php plot.inc.php raw.inc.php ware.inc.php 
Log Message:
More fixes for postgresql

Index: acquisition.inc.php
===================================================================
RCS file: /cvsroot/basedb/basedb/www/acquisition.inc.php,v
retrieving revision 1.14
retrieving revision 1.15
diff -C2 -d -r1.14 -r1.15
*** acquisition.inc.php	9 Sep 2002 06:29:33 -0000	1.14
--- acquisition.inc.php	10 Sep 2002 09:29:44 -0000	1.15
***************
*** 99,103 ****
  	function removeImages()
  	{
! 		$query = "SELECT id FROM Image WHERE imageAcquisition = $this->id";
  		$res = query($query);
  		$rc = true;
--- 99,103 ----
  	function removeImages()
  	{
! 		$query = "SELECT id FROM Image WHERE `imageAcquisition` = $this->id";
  		$res = query($query);
  		$rc = true;
***************
*** 158,162 ****
  
  		$query = "SELECT COUNT(*) FROM ImageAcquisition ia ".
! 			"LEFT JOIN Image im ON im.imageAcquisition = ia.id ".
  			"WHERE ".db_true()." ".
  			$search->getWhere()." ".$search->getExtraWhere();
--- 158,162 ----
  
  		$query = "SELECT COUNT(*) FROM ImageAcquisition ia ".
! 			"LEFT JOIN Image im ON im.`imageAcquisition` = ia.id ".
  			"WHERE ".db_true()." ".
  			$search->getWhere()." ".$search->getExtraWhere();
***************
*** 173,190 ****
  	{
  		// Add more fields here
! 		$sortfields = array("name", "scannerName", "scannerVersion",
! 			"scanDate", "images", "raws");
  
  		$ob = $search->makeOrderBy($sortfields);
  
! 		$query = "SELECT ia.*, COUNT(im.id) AS images, ".
! 			"hw.name AS scannerName, hw.version AS scannerVersion, ".
  			"COUNT(r.id) AS raws ".
! 			"FROM ImageAcquisition ia, Hardware hw ".
! 			"LEFT JOIN Image im ON im.imageAcquisition = ia.id ".
! 			"LEFT JOIN RawBioAssay r ON r.imageAcquisition = ia.id ".
  			"WHERE hw.id = ia.scanner ".
  			$search->getWhere()." ".$search->getExtraWhere()." ".
! 			"GROUP BY ia.id $ob ".$search->makeLimit();
  		$arr = array();
  		if(!($res = query($query)))
--- 173,199 ----
  	{
  		// Add more fields here
! 		$sortfields = array("name", "`scannerName`", "`scannerVersion`",
! 			"`scanDate`", "images", "raws");
  
  		$ob = $search->makeOrderBy($sortfields);
  
! 		if(db_has_subselects())
! 		{
! 			$ss1 = "t.* FROM ImageAcquisition ia, ".
! 				"(SELECT ia.id,";
! 			$ss2 = ", hw.name, hw.version) AS t WHERE ia.id = t.id";
! 		}
! 		else
! 			$ss1 = $ss2 = "";
! 
! 		$query = "SELECT ia.*, $ss1 COUNT(im.id) AS images, ".
! 			"hw.name AS `scannerName`, hw.version AS `scannerVersion`, ".
  			"COUNT(r.id) AS raws ".
! 			"FROM Hardware hw, ImageAcquisition ia ".
! 			"LEFT JOIN Image im ON im.`imageAcquisition` = ia.id ".
! 			"LEFT JOIN RawBioAssay r ON r.`imageAcquisition` = ia.id ".
  			"WHERE hw.id = ia.scanner ".
  			$search->getWhere()." ".$search->getExtraWhere()." ".
! 			"GROUP BY ia.id $ss2 $ob ".$search->makeLimit();
  		$arr = array();
  		if(!($res = query($query)))

Index: image.inc.php
===================================================================
RCS file: /cvsroot/basedb/basedb/www/image.inc.php,v
retrieving revision 1.12
retrieving revision 1.13
diff -C2 -d -r1.12 -r1.13
*** image.inc.php	9 Sep 2002 06:29:34 -0000	1.12
--- image.inc.php	10 Sep 2002 09:29:44 -0000	1.13
***************
*** 66,71 ****
  	function write()
  	{
! 		return parent::write(array("`imageAcquisition`", "channels",
! 			"`fileSize`"));
  	}
  
--- 66,71 ----
  	function write()
  	{
! 		return parent::write(array("imageAcquisition", "channels",
! 			"fileSize"));
  	}
  

Index: job.inc.php
===================================================================
RCS file: /cvsroot/basedb/basedb/www/job.inc.php,v
retrieving revision 1.36
retrieving revision 1.37
diff -C2 -d -r1.36 -r1.37
*** job.inc.php	9 Sep 2002 06:29:34 -0000	1.36
--- job.inc.php	10 Sep 2002 09:29:44 -0000	1.37
***************
*** 222,226 ****
  	{
  		$query = "UPDATE Job ".
! 			"SET `resultText` = CONCAT(`resultText`, '\n".addslashes($text)."') ".
  			"WHERE id = $this->id";
  		query($query);
--- 222,227 ----
  	{
  		$query = "UPDATE Job ".
! 			"SET `resultText` = ".db_string_concat(
! 				array("`resultText`", "'\n".addslashes($text)."'"))." ".
  			"WHERE id = $this->id";
  		query($query);
***************
*** 261,265 ****
  
  /*
! 	// Counts the number of hits and sets up the search object with that information
  	function prepareSearch(&$search, $expid, $hpp)
  	{
--- 262,266 ----
  
  /*
! 	// Counts the number of hits and sets up the search object
  	function prepareSearch(&$search, $expid, $hpp)
  	{

Index: mysql.inc.php
===================================================================
RCS file: /cvsroot/basedb/basedb/www/mysql.inc.php,v
retrieving revision 1.14
retrieving revision 1.15
diff -C2 -d -r1.14 -r1.15
*** mysql.inc.php	9 Sep 2002 19:55:27 -0000	1.14
--- mysql.inc.php	10 Sep 2002 09:29:44 -0000	1.15
***************
*** 122,125 ****
--- 122,126 ----
  	for(reset($keyvalues); list(, $v) = each($keyvalues); )
  		$arr[] = "'".addslashes($v)."'";
+ 	$arr[] = "NULL";
  	for(reset($values); list(, $v) = each($values); )
  		$arr[] = "'".addslashes($v)."'";
***************
*** 296,299 ****
--- 297,313 ----
  {
  	return $expr;
+ }
+ 
+ function db_string_concat($args)
+ {
+ 	return "CONCAT(".implode(",", $args).")";
+ }
+ 
+ function db_func_log($arg, $base = 0)
+ {
+ 	if($base)
+ 		return "LOG($arg)/LOG($base)";
+ 	else
+ 		return "LOG($arg)";
  }
  

Index: pgsql.inc.php
===================================================================
RCS file: /cvsroot/basedb/basedb/www/pgsql.inc.php,v
retrieving revision 1.10
retrieving revision 1.11
diff -C2 -d -r1.10 -r1.11
*** pgsql.inc.php	9 Sep 2002 19:55:27 -0000	1.10
--- pgsql.inc.php	10 Sep 2002 09:29:44 -0000	1.11
***************
*** 250,256 ****
  	global $curDb;
  	$f = file($filename);
! 	if(!pg_copy_from($curDb->serv, $table, $f))
  		return -1;
! 	return count($f);
  }
  
--- 250,275 ----
  	global $curDb;
  	$f = file($filename);
! 	if(!$f)
! 		return 0;
! 	$query = "COPY $table FROM stdin";
! 	if(!query($query))
  		return -1;
! 	$added = 0;
! 	for($i = 0, $err = 0; $i < count($f) && !$err; $i++)
! 	{
! 		if(pg_put_line($f[$i]))
! 			$added++;
! 		else
! {
! 			$err = 1;
! error_log("offending line: $f[$i]");
! }
! 	}
! 	pg_put_line("\\.\n");
! 	pg_end_copy();
! 	return $err ? -1 : $added;
! /*	if(!pg_copy_from($curDb->serv, strtolower($table), $f))
! 		return -1;
! 	return count($f);*/
  }
  
***************
*** 365,368 ****
--- 384,401 ----
  {
  	return "CASE WHEN $expr THEN 1 ELSE 0 END";
+ }
+ 
+ function db_string_concat($args)
+ {
+ 	return implode("||", $args);
+ }
+ 
+ function db_func_log($arg, $base = 0)
+ {
+ 	if($base)
+ 		$e = "LOG(".(float)$base.", float8($arg))";
+ 	else
+ 		$e = "LOG(float8($arg))";
+ 	return "CASE WHEN $arg > 0. THEN $e ELSE NULL END";
  }
  

Index: plot.inc.php
===================================================================
RCS file: /cvsroot/basedb/basedb/www/plot.inc.php,v
retrieving revision 1.21
retrieving revision 1.22
diff -C2 -d -r1.21 -r1.22
*** plot.inc.php	9 Sep 2002 10:56:08 -0000	1.21
--- plot.inc.php	10 Sep 2002 09:29:44 -0000	1.22
***************
*** 96,100 ****
  			else if($this->item == 2) $s = "$table.ratio";
  			else $s = "$table.intensity1*$table.intensity2";
! 			if($log) $s = "LOG($s)/LOG($log)";
  			if($this->item == 3)
  			{
--- 96,101 ----
  			else if($this->item == 2) $s = "$table.ratio";
  			else $s = "$table.intensity1*$table.intensity2";
! 			if($log)
! 				$s = db_func_log($s, $log);
  			if($this->item == 3)
  			{
***************
*** 118,122 ****
  			else $s = "($table.`FCh1$mn`-$table.`BCh1$mn`)*".
  				"($table.`FCh2$mn`-$table.`BCh2$mn`)";
! 			if($log) $s = "LOG($s)/LOG($log)";
  			if($this->item == 3)
  			{
--- 119,124 ----
  			else $s = "($table.`FCh1$mn`-$table.`BCh1$mn`)*".
  				"($table.`FCh2$mn`-$table.`BCh2$mn`)";
! 			if($log)
! 				$s = db_func_log($s, $log);
  			if($this->item == 3)
  			{

Index: raw.inc.php
===================================================================
RCS file: /cvsroot/basedb/basedb/www/raw.inc.php,v
retrieving revision 1.35
retrieving revision 1.36
diff -C2 -d -r1.35 -r1.36
*** raw.inc.php	9 Sep 2002 15:49:59 -0000	1.35
--- raw.inc.php	10 Sep 2002 09:29:44 -0000	1.36
***************
*** 285,294 ****
  		$ob = $search->makeOrderBy($sortfields);
  
! 		$query = "SELECT r.*, ".
! 			"CONCAT(sw.name, ' ', sw.version) AS software ".
  			"FROM RawBioAssay r, Software sw ".
  			"WHERE sw.id = r.`featureSoftware` ".
  			$search->getWhere()." ".$search->getExtraWhere()." ".
! 			"GROUP BY r.id $ob ".$search->makeLimit();
  
  		$arr = array();
[...62 lines suppressed...]
! 			"FLOOR(LOG((rbad.`FCh1Mean`-rbad.`BCh1Mean`)/".
! 			"(rbad.`FCh2Mean`-rbad.`BCh2Mean`))/LOG(2)+.5)");
  		if($histbox == -1)
  		{
! 			$groups[2] = "FLOOR(LOG((rbad.`FCh1Mean`-rbad.`BCh1Mean`)/".
! 				"(rbad.`FCh2Mean`-rbad.`BCh2Mean`))*30+.5)";
  			$extra = "rbad.dia,";
  		}
--- 658,668 ----
  		$third = (int)$third;
  		$groups = array("rbad.block", "rbad.flags",
! 			"FLOOR(.5+".db_func_log("(rbad.`FCh1Mean`-rbad.`BCh1Mean`)/".
! 			"(rbad.`FCh2Mean`-rbad.`BCh2Mean`)", 2).")");
  		if($histbox == -1)
  		{
! 			$groups[2] = "FLOOR(.5+30.*".
! 				db_func_log("(rbad.`FCh1Mean`-rbad.`BCh1Mean`)/".
! 				"(rbad.`FCh2Mean`-rbad.`BCh2Mean`)").")";
  			$extra = "rbad.dia,";
  		}

Index: ware.inc.php
===================================================================
RCS file: /cvsroot/basedb/basedb/www/ware.inc.php,v
retrieving revision 1.11
retrieving revision 1.12
diff -C2 -d -r1.11 -r1.12
*** ware.inc.php	9 Sep 2002 06:29:34 -0000	1.11
--- ware.inc.php	10 Sep 2002 09:29:44 -0000	1.12
***************
*** 87,91 ****
  		$fields = array();
  
! 		if($onlyShown) $search->addExtraWhere("w.shown");
  		$search->makeWhere($fields);
  
--- 87,91 ----
  		$fields = array();
  
! 		if($onlyShown) $search->addExtraWhere("w.shown <> 0");
  		$search->makeWhere($fields);
  
***************
*** 168,172 ****
  	function getBrief()
  	{
! 		$query = "SELECT id, name, version FROM Software WHERE shown";
  		$res = query($query);
  		$arr = array();
--- 168,172 ----
  	function getBrief()
  	{
! 		$query = "SELECT id, name, version FROM Software WHERE shown <> 0";
  		$res = query($query);
  		$arr = array();
***************
*** 236,240 ****
  	function getBrief()
  	{
! 		$query = "SELECT id, name, version FROM Hardware WHERE shown";
  		$res = query($query);
  		$arr = array();
--- 236,240 ----
  	function getBrief()
  	{
! 		$query = "SELECT id, name, version FROM Hardware WHERE shown <> 0";
  		$res = query($query);
  		$arr = array();