[Tikiwiki-cvs] tiki/lib/trackers newtrackerlib.php,1.1.2.5,1.1.2.6

[<rv...@us...>]

Update of /cvsroot/tikiwiki/tiki/lib/trackers
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv8131

Modified Files:
      Tag: BRANCH-1-9
	newtrackerlib.php 
Log Message:
option for using column names in clear in values tables

Index: newtrackerlib.php
===================================================================
RCS file: /cvsroot/tikiwiki/tiki/lib/trackers/Attic/newtrackerlib.php,v
retrieving revision 1.1.2.5
retrieving revision 1.1.2.6
diff -u -d -r1.1.2.5 -r1.1.2.6
--- newtrackerlib.php	11 May 2005 16:29:55 -0000	1.1.2.5
+++ newtrackerlib.php	12 May 2005 16:21:57 -0000	1.1.2.6
@@ -23,7 +23,7 @@
 	
 	The table looks like this :
 	
-	tiki_tracker_values_n
+	tiki_trk_n
 	+------+-------+-------+-------+...+-------+
 	|itemId|field_1|field_2|field_3|...|field_n|
 	+------+-------+-------+-------+...+-------+
@@ -53,16 +53,23 @@
 	var $TABLE_PREFIX;
 	var $COL_PREFIX;
 	var $EXPLICIT_PREFIX;
-	//var $extDB;
+	var $explicit;
 	
 	function NewtrackerLib($db) {
 		parent::TrackerLib($db);
 		
-		$this->TABLE_PREFIX	= "tiki_tracker_values_";
+		$this->TABLE_PREFIX	= "tiki_trk_";
 		$this->COL_PREFIX	= "field_";
-		$this->EXPLICIT_PREFIX	= "tiki_trk_";
-		
-		//$this->extDB = null;
+		$this->EXPLICIT_PREFIX	= $this->TABLE_PREFIX;
+		$this->explicit = false;
+	}
+	
+	function set_explicit($explicit) {
+		$this->explicit = $explicit;
+	}
+	
+	function get_explicit() {
+		return $this->explicit;
 	}
 	
 	// return a name for a tracker value table
@@ -117,38 +124,64 @@
 		$query.= " where `trackerId`=?";
 		$r = $this->query($query, $trackerId);*/
 		$tableDsn = 'tiki';//$r['valuesTableDsn'];
-		$explicit = false;//$r['explicitNames'];
+		$explicit = $this->get_explicit();//$r['explicitNames'];
 	}
 	
-	//
-	function check_col_name($colName) {
+	// check_col_name and check_table_name are used
+	// when using explicit names in db values tables
 	
-		$preg = preg_match("/[a-zA-Z][a-zA-Z0-9_]+/", $colName);
-		if ($preg == 0) {
-			// erreur nom de colonne
-			return false;
-		} elseif ($preg > 0) {
+	function check_col_name($colName, $trackerId) {
+	
+		$preg = preg_match("/^[a-zA-Z][a-zA-Z0-9_]+$/", $colName);
+		if ($preg > 0) {
+			$query = "select distinct `name` from `tiki_tracker_fields` where `trackerId`=?";
+			$res = $this->query($query, array((int)$trackerId));
+			while($r = $res->fetchRow()) {
+				if($colName == $r['name'])
+					return false;
+			}
 			// nom de colonne ok
 			return true;
-		} elseif ($preg == false) {
+		}
+		elseif ($preg == 0) {
+			// erreur nom de colonne
+			return false;
+		} 
+		elseif ($preg == false) {
 			// erreur sur l'expression régulière
 			return false;
 		}
 	}
 	
 	function check_table_name($tableName) {
-		
-		$preg = preg_match("/[a-zA-Z][a-zA-Z0-9_]+/", $tableName);
-		if ($preg == 0) {
-			// erreur nom de colonne
-			return false;
-		} elseif ($preg > 0) {
-			// nom de colonne ok
+	
+		$preg = preg_match("/^[a-zA-Z][a-zA-Z0-9_]+$/", $tableName);
+		if ($preg > 0) {
+			$query = "select distinct `name` from `tiki_trackers`";
+			$res = $this->query($query);
+			while($r = $res->fetchRow()) {
+				if($tableName == $r['name'])
+					return false;
+			}
+			// nom de table ok
 			return true;
-		} elseif ($preg == false) {
+		}
+		elseif ($preg == 0) {
+			// erreur nom de table
+			return false;
+		} 
+		elseif ($preg == false) {
 			// erreur sur l'expression régulière
 			return false;
-		}		
+		}
+	}
+	
+	function first_field_name($trackerId) {
+		$query  = "select distinct `name` from tiki_tracker_fields where `trackerId`=?";
+		$res = $this->query($query, array((int)$trackerId));
+		while($r = $res->fetchRow()) {
+			return $r['name'];
+		}
 	}
 	
 	// this method create the tables of values
@@ -165,12 +198,15 @@
 		$result = $this->query($preQuery, array((int)$trackerId));
 
 		if($result) {
-			$tableName = $this->get_table_id($trackerId, $explicit);
-			
+			$tableName = $this->get_table_id($trackerId, $explicit);	
 			$query = "create table $tableName(itemId INT NOT NULL, ";
 			while ($res = $result->fetchRow()) {
+			
 				if($explicit) {
-					$query.= $res['name'];
+					if($this->check_col_name($res['name'], $trackerId))
+						$query.= $res['name'];
+					else
+						return;
 				}
 				else {
 					$query.= $this->get_col_id($res['fieldId']);
@@ -178,7 +214,7 @@
 				$query.= " ".$this->get_sql_type($res['type']).", ";
 			}
 			$query.= "PRIMARY KEY(itemId))";
-			
+		
 			//echo "REQ : ".$query;
 			$result = $this->query($query);
 		}
@@ -202,6 +238,10 @@
 	// instead of the namming convention for the column names.
 	/*function export_tracker($trackerId) {
 	
+		$this->get_tracker_values_specs($trackerId, $explicit, $dsn);
+		if($explicit)
+			return;
+	
 		if(!($trackerId && $this->getOne("select * from `tiki_trackers` where `trackerId`=?", array((int)$trackerId))))
 			return;
 	
@@ -440,9 +480,9 @@
 				
 				$colId = $this->get_col_id($csort_mode, $explicit);
 				$mid.= "and $tableId.$colId=?";
-			// if you enter a value for a field value
+			// if you enter a value for a field
 			// that you want to filter.
-			} elseif ($filtervalue && $filterfield) { // v�rifier
+			} elseif ($filtervalue && $filterfield) {
 			
 				if (substr($filtervalue,0,1) == '*') {
 					$bindvars[] = '%'. substr($filtervalue,1);
@@ -459,6 +499,9 @@
 			} else {
 				list($a,$csort_mode,$corder) = split('_',$sort_mode);
 				$colId = $this->get_col_id($csort_mode, $explicit);
+				if($colId == "") {
+					$colId = $this->first_field_name($trackerId);
+				}
 			}
 			
 			if (substr($sort_mode,0,2) == "f_") {
@@ -495,7 +538,7 @@
 		$cant = $this->getOne($query_cant,$bindvars);
 		
 		if(isset($colId)) {
-			$fid = $this->get_id_from_name($this->COL_PREFIX, $colId);
+			$fid = $this->get_id_from_name($this->COL_PREFIX, $colId, $explicit, $trackerId);
 			$qFid = "select `type` from `tiki_tracker_fields` where `fieldId`=$fid";
 			$ftype = $this->getOne($qFid);
 		}
@@ -849,19 +892,29 @@
 	// When inserting a new tracker,
 	// trackerId must be set to false.
 	// ########## OK ##########
-	// ##############################	
+	// ##############################
 	function replace_tracker($trackerId, $name, $description, $options) {
 		
-		// delegation a tracklib
-		$trackerIdNew = parent::replace_tracker($trackerId, $name, $description, $options);
+		$this->get_tracker_values_specs($trackerId, $explicit, $dsn);
+		if(!$explicit || $this->check_table_name($name)) {
+			
+			if($explicit && $trackerId) {
+				$oldName = $this->get_table_id($trackerId, $explicit);
+				$query = "alter table $oldName rename to ".$this->EXPLICIT_PREFIX.$name;
+				$this->query($query);
+			}
 		
-		// creation de la table des items
-		// si elle n'existe pas deja.
-		if($trackerId == false && $trackerIdNew) {
-			$this->get_tracker_values_specs($trackerId, $explicit, $dsn);
-			$this->create_value_table($trackerIdNew, $explicit, $dsn);
+			// delegation a tracklib
+			$trackerIdNew = parent::replace_tracker($trackerId, $name, $description, $options);
+			
+			// creation de la table des items
+			// si elle n'existe pas deja.
+			if($trackerId == false && $trackerIdNew) {
+				$this->create_value_table($trackerIdNew, $explicit, $dsn);
+			}			
+			return $trackerIdNew;
 		}
-		return $trackerIdNew;
+		return false;
 	}
 
 
@@ -872,30 +925,41 @@
 	
 		if(!$trackerId)
 			return;
+			
+		$this->get_tracker_values_specs($trackerId, $explicit, $dsn);
+		if(!$explicit || $this->check_col_name($name, $trackerId)) {
+		
+			if ($fieldId) {
+				if($explicit) {
+					$oldName = $this->get_col_id($fieldId, $explicit);
+					$tableName = $this->get_table_id($trackerId, $explicit);
+					$sqlType = $this->get_sql_type($type);
+					$query = "alter table $tableName change column $oldName $name $sqlType";
+					$this->query($query);
+				}
+				$query = "update `tiki_tracker_fields` set `name`=? ,`type`=?,`isMain`=?,`isSearchable`=?,
+					`isTblVisible`=?,`isPublic`=?,`isHidden`=?,`isMandatory`=?,`position`=?,`options`=? where `fieldId`=?";
+				$bindvars=array($name,$type,$isMain,$isSearchable,$isTblVisible,$isPublic,$isHidden,$isMandatory,(int)$position,$options,(int) $fieldId);
 	
-		if ($fieldId) {
-			$query = "update `tiki_tracker_fields` set `name`=? ,`type`=?,`isMain`=?,`isSearchable`=?,
-				`isTblVisible`=?,`isPublic`=?,`isHidden`=?,`isMandatory`=?,`position`=?,`options`=? where `fieldId`=?";
-			$bindvars=array($name,$type,$isMain,$isSearchable,$isTblVisible,$isPublic,$isHidden,$isMandatory,(int)$position,$options,(int) $fieldId);
-
-			$result = $this->query($query, $bindvars);
-		} else {
-			$this->getOne("delete from `tiki_tracker_fields` where `trackerId`=? and `name`=?",
-				array((int) $trackerId,$name),false);
-			$query = "insert into `tiki_tracker_fields`(`trackerId`,`name`,`type`,`isMain`,`isSearchable`,`isTblVisible`,`isPublic`,`isHidden`,`isMandatory`,`position`,`options`)
-                values(?,?,?,?,?,?,?,?,?,?,?)";
-
-			$result = $this->query($query,array((int) $trackerId,$name,$type,$isMain,$isSearchable,$isTblVisible,$isPublic,$isHidden,$isMandatory,$position,$options));
-			$fieldId = $this->getOne("select max(`fieldId`) from `tiki_tracker_fields` where `trackerId`=? and `name`=?",array((int) $trackerId,$name));
-
-			$this->get_tracker_values_specs($trackerId, $explicit, $dsn);
-			$tableId = $this->get_table_id($trackerId, $explicit);
-			$colId = $this->get_col_id($fieldId, $explicit);
-			$sqlType = $this->get_sql_type($type);
-			$query = "alter table $tableId add column $colId $sqlType";
-			$this->query($query);
+				$result = $this->query($query, $bindvars);
+			} else {
+				$this->getOne("delete from `tiki_tracker_fields` where `trackerId`=? and `name`=?",
+					array((int) $trackerId,$name),false);
+				$query = "insert into `tiki_tracker_fields`(`trackerId`,`name`,`type`,`isMain`,`isSearchable`,`isTblVisible`,`isPublic`,`isHidden`,`isMandatory`,`position`,`options`)
+			values(?,?,?,?,?,?,?,?,?,?,?)";
+	
+				$result = $this->query($query,array((int) $trackerId,$name,$type,$isMain,$isSearchable,$isTblVisible,$isPublic,$isHidden,$isMandatory,$position,$options));
+				$fieldId = $this->getOne("select max(`fieldId`) from `tiki_tracker_fields` where `trackerId`=? and `name`=?",array((int) $trackerId,$name));
+				
+				$tableId = $this->get_table_id($trackerId, $explicit);
+				$colId = $this->get_col_id($fieldId, $explicit);
+				$sqlType = $this->get_sql_type($type);
+				$query = "alter table $tableId add column $colId $sqlType";
+				$this->query($query);
+			}
+			return $fieldId;
 		}
-		return $fieldId;
+		return false;
 	}
 
 	// ########## OK ##########