sqlitetableproperties.class.php

架設ROSE私服必備之物 ROSE數據庫

			 $CheckNull = ' ';
		}
		echo $CheckNull.'</td>
					<td align="left" class="Insert">';
		echo SQLiteInputType($infoTable, $tabValue[$infoTable['name']]);
		echo '</td>
				</tr>';
		return;
	}
	
	/**
	* save record
	*
	* @access private
	*/
	function saveElement(){
		if(isset($_GET['pos'])) $GLOBALS['numId'] = $_GET['pos'];
		if(isset($_REQUEST['numId'])) $GLOBALS['numId'] = $_REQUEST['numId'];
		if(isset($_GET['query'])) $GLOBALS['req'] = urldecode($_GET['query']);
		elseif(isset($_POST['req'])) $GLOBALS['req'] = urldecode($_POST['req']);
		if(isset($GLOBALS['req']) && isset($GLOBALS['numId'])){
			$oldValue = $this->recupElement($GLOBALS['req'], $GLOBALS['numId']);
		}

		if(isset($_POST['valField']) && is_array($_POST['valField'])){
			while(list($champ, $value) = each($_POST['valField'])){
				$value = stripslashes($value);
				$cid = $this->getCID($champ);
				$tempType = $this->infoTable[$cid]['type'];
				if(isset($_POST['funcs'][$champ]) && !empty($_POST['funcs'][$champ])){
					if(eregi('CHAR|TEXT|LOB|DATE', $tempType)) $funcVal = quotes($value);
					else $funcVal = $value;
					$value = applyFunction($_POST['funcs'][$champ], $funcVal);
				} elseif(!isset($_POST['nullField'][$champ]) || !$_POST['nullField'][$champ]) {
					if($tempType) {
						if(eregi('CHAR|TEXT|LOB|DATE', $tempType)) $value = quotes($value);
					} else
						$value = quotes($value);
				}

				if(isset($_POST['nullField'][$champ])) {
					$value = 'NULL';
				}
				if(!isset($_POST['numId']) || $_POST['save_type']=="as_new_row"){
					$listColumn[] 	= brackets($champ);
					$listValue[]	= $value;
				} else {
					if((isset($oldValue[$champ]) && ($value != quotes($oldValue[$champ])) || (!isset($oldValue[$champ])&& ($value != "NULL")))){
						$listColumn[]	= brackets($champ).'='.$value;
					}
				}
				
			}
		}

		$query = '';
		if($GLOBALS['action']=='deleteElement'){
			$query = 'DELETE FROM '.brackets($GLOBALS['table']).' WHERE ROWID='.$oldValue['ROWID'];
		} elseif(isset($_POST['numId']) && $_POST['save_type']!="as_new_row"){
			if(isset($listColumn) && !empty($listColumn)){ 			    
				$query = 'UPDATE '.brackets($GLOBALS['table']).' SET '.implode(', ', $listColumn).' WHERE ROWID='.$oldValue['ROWID'];
			}
		} else {
			if(isset($listColumn) && isset($listValue)) 
				$query = 'INSERT INTO '.brackets($GLOBALS['table']).' ('.implode(', ', $listColumn).') VALUES ('.implode(', ', $listValue).')';
		}		
		displayQuery($query);
		$errorCode = false;
		if(isset($query) && !empty($query)){
			$this->connId->getResId('BEGIN;');
			if(!$this->connId->getResId($query)){
				echo '<center><span style="color: red;">'.$GLOBALS['traduct']->get(9).' : '.@$this->connId->connId->getError().'</span></center>';
				$this->formElement($GLOBALS['req'], $GLOBALS['numId'], true);
			}
			$this->connId->getResId('COMMIT;');
		}
		// return management		
		if(!isset($_REQUEST['after_save']) && isset($_REQUEST['currentPage'])) $_REQUEST['after_save'] = $_REQUEST['currentPage'];
		if(!$errorCode && isset($_REQUEST['after_save'])){
			if($_REQUEST['after_save'] == '') $this->formElement(((isset($GLOBALS['req']))? $GLOBALS['req'] : '' ), ((isset($GLOBALS['numId']))? $GLOBALS['numId'] : '' ));
			else 
				switch($_REQUEST['after_save']){
					case '':
					case 'properties':
						$this->tablePropView();
						break;
					case 'browseItem':
						if(isset($GLOBALS['numId'])){
							$GLOBALS['noDisplay'] = true;
							include_once INCLUDE_LIB.'ParsingQuery.class.php';
							$tabRes = ParsingQuery::noLimit($GLOBALS['req']);
							$GLOBALS['DisplayQuery'] = $tabRes['query'];
							$GLOBALS['pageBrowse'] = $_GET['pageBrowse'] = $tabRes['page'];
						}
						$GLOBALS['reBrowse'] = true;
						break;
				}
		}
	}
	
	/**
	* Form for insert data from text file formatted
	*
	* @access public
	*/
	function formFromFile(){
    echo '<!-- SQLiteTableProperties.class.php : formFromFile() -->'."\n";		
		echo '<div align="center">'."\n";
		echo '<br/><h4>'.$GLOBALS['traduct']->get(140).'</h4><br/>';
		echo '<form name="fromfile" action="main.php?dbsel='.$GLOBALS['dbsel'].'&amp;table='.$GLOBALS['table'].'" method="POST" ENCTYPE="multipart/form-data" target="main">'."\n";
		echo '<table border="1" width="70%">'."\n";
		echo '<tr><td>'.$GLOBALS['traduct']->get(137).'</td><td>&nbsp;<input type="file" class="file" name="fileInsert"></td></tr>';
		echo '<tr><td>'.$GLOBALS['traduct']->get(138).'</td><td><input type="checkbox" name="replaceAll"></td></tr>';
		echo '<tr><td>'.$GLOBALS['traduct']->get(139).'</td><td>&nbsp;<input type="text" class="text" name="separator" value="\t" size=5></td></tr>';
		echo '</table>'."\n";
		echo '<input class="button" type="submit" value="'.$GLOBALS['traduct']->get(69).'">'."\n";
		echo '<input type="hidden" name="action" value="saveFromFile">';
		echo '</form>';
		echo '</div>'."\n";
	}
	
	/**
	* Save data from text file formatted
	*
	* @access private
	*/
	function saveFromFile(){
		if($_POST['separator'] != '\\\t') $useDelim = ' USING DELIMITERS '.quotes($_POST['separator']);
		else $useDelim = '';
		$GLOBALS['DisplayQuery'] = $copyQuery = 'COPY '.brackets($this->table).' FROM '.quotes($_FILES['fileInsert']['tmp_name']).$useDelim.';';
		if($this->connId->connId->getVersion()!=3){
			$query[] = "BEGIN;";
			if(!empty($_FILES['fileInsert']['tmp_name'])){
				if(isset($_POST['replaceAll']) && $_POST['replaceAll']){
					$query[] = 'DELETE FROM '.brackets($this->table).';';
				}
				$query[] = $copyQuery;
				$query[] = "COMMIT;";
			}
		} else {
			// build save from file for SQLITE3
			$fileToLine = file($_FILES['fileInsert']['tmp_name']);
			if(is_array($fileToLine)){
				$query[] = "BEGIN;";
				if(isset($_POST['replaceAll']) && $_POST['replaceAll']){
					$query[] = 'DELETE FROM '.brackets($this->table).';';
				}
				if($_POST['separator'] == '\\\t') $sep = "\t";
				else $sep = $_POST['separator'];
				foreach($fileToLine as $record){
					$recordElement = explode($sep, rtrim($record));
					$query[] = "INSERT INTO ".brackets($this->table)." VALUES ('".implode("', '", $recordElement)."');";
				}
				$query[] = "COMMIT;";
			}			
		}
		$execError = false;
		$GLOBALS['phpSQLiteError'] = '';
		set_error_handler('phpSQLiteErrorHandling');					
		foreach($query as $q){
			if(!$this->connId->getResId($q)){			
				$execError = true;
				$this->connId->getResId("ROLLBACK TRANSACTION;");
				$errorMessage = '<table style="color: red;"><tr><td>'.$GLOBALS['traduct']->get(9).' :</td><td>'.$this->connId->connId->getError().'</td></tr>';
				if($GLOBALS['phpSQLiteError'] != '') $errorMessage .= '<tr><td>&nbsp;</td><td>'.$GLOBALS['phpSQLiteError'].'</td></tr>';
				$errorMessage .= '</table>';
				break;
			}		
		}
		restore_error_handler();
		if($execError) {
			displayError($errorMessage);		
			displayQuery($GLOBALS['DisplayQuery']);
			$this->formFromFile();
		} else {
			displayQuery($GLOBALS['DisplayQuery']);
		}
	}
	/**
	* Retrive Record from current query and numId
	*
	* @access public
	* @param string $req current query
	* @param integer $numId Number of record from current query
	* @param boolean $error if true return POST value
	*/
	function recupElement($req, $numId, $error=false){
		include_once INCLUDE_LIB.'ParsingQuery.class.php';
		$tabQueryElement = ParsingQuery::explodeSelect($req);

		$tabQueryElement['SELECT'] = 'ROWID, '.$tabQueryElement['SELECT'];

		if(eregi('FROM', $req)){
			$tabFrom = explode(',', $tabQueryElement['FROM']);
			foreach($tabFrom as $key=>$value) $tabFrom[$key] = brackets($value);
			$tabQueryElement['FROM'] = implode(',', $tabFrom);
		}

		if(eregi('LIMIT', $req)){
			$tabLimit = explode(',', $tabQueryElement['LIMIT']);
			$tabQueryElement['LIMIT'] = ((int)$tabLimit[0]+$numId).',1';
		} else {
			$tabQueryElement['LIMIT'] = $numId.',1';
		}
		
		$querySearch = '';
		foreach($tabQueryElement as $clause=>$contentClause) $querySearch .= $clause.' '.$contentClause.' ';

		$this->connId->connId->query($querySearch);
		$tabData = $this->connId->connId->fetch_array(null, (($this->connId->connId->getVersion()==3)? SQLITE_BOTH : SQLITE_ASSOC ));
		if($this->connId->connId->getVersion()==3) $tabData["ROWID"] = $tabData[0];
		if($error){
			foreach($tabData as $fieldname => $fieldvalue) 
				if(isset($_POST[$fieldname])) $tabData[$fieldname] = $_POST[$fieldname];
		}
		return $tabData;
	}
	
	/**
	* Retrive 'cid' from champ name
	*
	* @access public
	* @param string $name
	*/
	function getCID($name){
		foreach($this->infoTable as $cid => $info)
			if($info['name']==$name) return $cid;
	}
	
	/**
	*
	*/
	function saveKey(){
		$cid = key($_POST['modify']);
		$columnName = $this->infoTable[$cid]['name'];
		if($_POST['action']=='unique') $type = 'UNIQUE ';
		else $type = '';
		$query = 'CREATE '.$type.'INDEX '.str_replace(' ','_',$this->table.'_'.$columnName).' ON '.brackets($this->table).'('.brackets($columnName).');';
		$GLOBALS['phpSQLiteError'] = '';
		set_error_handler('phpSQLiteErrorHandling');
		if(!$this->connId->getResId($query)){			
				echo '<table align="center" style="color: red;"><tr><td>'.$GLOBALS['traduct']->get(9).' :</td><td>'.@$this->connId->connId->getError().'</td></tr>';
				if($GLOBALS['phpSQLiteError'] != '') echo '<tr><td>&nbsp;</td><td>'.$GLOBALS['phpSQLiteError'].'</td></tr>';
				echo '</table>';
		}
		restore_error_handler();
		displayQuery($query);
		$this->tablePropView();
	}
    
    
    /**
    * Generate SQL query for 'select'
    * @author Maur韈io M. Maia <[e-mail]>
    *
    * @param string $table
    */
    function selectElement($table) {
        $showField = $_REQUEST['showField'];
        $valField = $_REQUEST['valField'];
        $operats = $_REQUEST['operats'];
		$error = false;
        $selectQuery = 'SELECT ';
        $condQuery = '';
		if(is_array($_REQUEST['showField']) && !empty($_REQUEST['showField'])){
			$selectQuery .= implode(", ", array_keys($_REQUEST['showField']));
	    } else $selectQuery .= '*';
	    
        $fromQuery = ' FROM '.brackets($table).' ';
		if(is_array($_REQUEST['valField']) && !empty($_REQUEST['valField'])){
	        foreach($valField as $key => $value) {
	            if (	(isset($value) && !empty($value)) 
	            		|| (isset($operats[$key])
	            		&& !empty($operats[$key]))) {
	
					if($operats[$key] == 'ISNULL' || $operats[$key] == 'NOTNULL'){
	            		$condQuery .= $key.' '.$operats[$key];
	            	} else if($operats[$key]=="fulltextsearch"){
	            		if($selectQuery == "SELECT *"){
		            		$condQuery .= 'fulltextsearch('.$key.', '.quotes($value).', 0) > 0';
		            	} else {
		            		$selectQuery .= ', fulltextsearch('.$key.', '.quotes($value).', 0) AS '.$key.'Match';
		            		$condQuery .= $key.'Match > 0'; 
		            	}
	            	} else {
	            		$condQuery .= $key.' '.$operats[$key].' '.quotes($value);
	            	}
	            }
	        }
	    }
	    if(!empty($_REQUEST['CondSuppl'])){
	    	if($condQuery) $condQuery .= ' '.$_REQUEST['operSuppl'].' ';
	    	$condQuery .= $_REQUEST['CondSuppl'];
	    }
	    return $selectQuery.$fromQuery.(($condQuery)? 'WHERE '.$condQuery : '' );
    }

}

?>