sqlitetableproperties.class.php

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

<?php
/**
* Web based SQLite management
* Show and manage 'TABLE' properties
* @package SQLiteManager
* @author Fr閐閞ic HENNINOT
* @version $Id: SQLiteTableProperties.class.php,v 1.115 2006/04/16 20:06:37 freddy78 Exp $ $Revision: 1.115 $
*/

include_once INCLUDE_LIB.'TableIndex.class.php';

class SQLiteTableProperties {

	/**
	* reference to the connection object
	*
	* @access public
	* @var object
	*/
	var $connId;
	
	/**
	* TABLE name
	*
	* @access private
	* @var string
	*/
	var $table;
	
	/**
	* this TABLE exist?
	*
	* @access private
	* @var bool
	*/
	var $isExist;
	
	/**
	* if this table have a PRIMARY KEY -> true
	*
	* @access private
	* @var bool
	*/
	var $tablePrimary;
	
	/**
	* TABLE properties
	*
	* @access private
	* @var array
	*/
	var $infoTable;
	
	/**
	* Class constructor
	*
	* @access public
	* @param string $conn reference to the connection object
	*/
	function SQLiteTableProperties(&$conn, $table=""){
		// constructeur de la classe
		$this->connId = $conn;
		if(!empty($table)){
			$this->table = $table;
		} else if($GLOBALS['table']) {
			$this->table = $GLOBALS['table'];
		} elseif($GLOBALS['TableName']){
			$this->table = $GLOBALS['TableName'];
		} else return false;
		if($GLOBALS["action"] != "delete") {		
			$this->isExist = $this->tableExist($this->table);
		} else {
			$this->isExist = true;
		}
		return $this->isExist;
	}
	
	
	/**
	* Get all the table properties
	*
	* @access public
	* @param string $table table name
	*/
	function getTableProperties($table=''){
		if(empty($table)) $table = $this->table;
		$this->connId->getResId('PRAGMA table_info('.brackets($table).');');
		$this->infoTable = $this->connId->getArray();
		$this->searchPrimaryKey($table);
		return $this->infoTable;
	}
	
	/**
	 * Get index SQL of table
	 * 
	 * @access private
	 */
	function getIndexSQL() {
		$query = "SELECT sql FROM sqlite_master WHERE tbl_name LIKE '".$this->table."';";
		$this->connId->getResId($query);
		$listIndex = $this->connId->getArray();
		return $listIndex;
	}

	/**
	* Verify if this TABLE exist
	*
	* @access public
	* @param string $table Table name
	*/
	function tableExist($table){
		if(empty($table)) $table = $this->table;
		$this->connId->getResId("SELECT count(*) FROM sqlite_master WHERE type='table' AND name=".quotes($table).";");
		if($this->connId->connId->fetch_single()>0) return true;
		else return false;			
	}
	
	/**
	* search of all the index
	*
	* @access public
	* @param string $table Table name
	*/
	function searchPrimaryKey($table){
		if(empty($table)) $table = $this->table;
		$this->tablePrimary = false;
		$this->connId->getResId("SELECT sql FROM sqlite_master WHERE type='table' and name=".quotes($table).";");
		$sql = $this->connId->connId->fetch_single();
		$firstPar 	= strpos($sql, '(');
		$endPar 	= strrpos($sql, ')')-1;
		$sql = substr($sql, ($firstPar+1), ($endPar - $firstPar));
		$sql = str_replace("\n", '', $sql);
		$ligne = explode(',', $sql);
		while(list($ligneNum, $cont) = each($ligne)){
			if(eregi('PRIMARY[[:space:]]KEY', $cont)){
				$tempCont = eregi_replace('PRIMARY[[:space:]]KEY', '', $cont);
				$tempCont = eregi_replace('\(|\)', '', $tempCont);
				$tabColName = explode(',', $tempCont);
				if(is_array($tabColName)) {
					foreach($tabColName as $colName) {
						$primaryKey = $this->numCol(trim($colName));
						$this->infoTable[$primaryKey]['primary'] = true;
						$this->tablePrimary = true;
					}
					return $primaryKey;
				} else {
					return false;
				}
			}
		}
	}
	
	/**
	* save current TABLE properties, add or modify
	*
	* @access public
	*/
	function saveProp(){
		$query = 'CREATE TABLE '.brackets($this->table).' ('."\n";
		if(!$this->isExist) {
			$error = false;
			while(list($key, $value) = each($_POST['fieldName'])){
				if(!empty($_POST['fieldName'][$key])){
					$query .= brackets(cleanFieldName($value)).' '.$_POST['fieldType'][$key].(($_POST['fieldLength'][$key])? '('.stripslashes($_POST['fieldLength'][$key]).') ' : ' ' );
					$query .= $_POST['fieldNull'][$key];
					if(isset($_POST['primary']) && ($_POST['primary']==$key)) 
						$query .= ' PRIMARY KEY';
					$query .= (($_POST['fieldDefault'][$key] && ($_POST['fieldNull'][$key]=='NOT NULL'))? ' DEFAULT '.quotes($_POST['fieldDefault'][$key]) : '' ).",\n";
				}
			}
			$query = substr($query, 0, strlen($query)-2)."\n);";
			$res = $this->connId->getResId($query);		
			if($res) {
				$this->isExist = true;
				$this->getTableProperties();
				displayQuery($query);
				$this->tablePropView();
				echo "<script type=\"text/javascript\">parent.left.location='left.php?dbsel=".$GLOBALS['dbsel']."';</script>";
			}				
			return;			
		} else {
			$listIndexSQL = $this->getIndexSQL();
			$oldColumn = array();
			$newColumn = array();
			$nullToNotNull	= array();
			if(isset($_POST['after']) && ($_POST['after']=='START')){
				while(list($key, $trash) = each($_POST['fieldName'])) if(!empty($_POST['fieldName'][$key])) $query .= $this->getPostProp($key);
			}
			if(is_array($this->infoTable)) {
				reset($this->infoTable);
				while(list($cid, $champ)=each($this->infoTable)){				
					if(isset($_POST['fieldName'][$cid]) && !isset($_POST['after'])) {
						$query .= $this->getPostProp($cid);	
						$oldColumn[] = $this->infoTable[$cid]['name'];
						$newColumn[] = 	cleanFieldName($_POST['fieldName'][$cid]);
					} else{
						if(($GLOBALS['action'] == 'delete') && (isset($_POST['modify'][$cid]) && $_POST['modify'][$cid])) continue;
						$oldColumn[] = $this->infoTable[$cid]['name'];
						$query .= brackets($this->infoTable[$cid]['name']).' '.strtoupper($this->infoTable[$cid]['type']);
						$query .= (($this->infoTable[$cid]['notnull'])? ' NOT NULL' : '' );
						$noprimary 		= ( (isset($GLOBALS['action']) && ($GLOBALS['action'] == 'noprimary'))  && isset($_POST['modify'][$cid]) && $_POST['modify'][$cid] );
						$addprimary 	= ( (isset($GLOBALS['action']) && ($GLOBALS['action'] == 'addprimary')) && isset($_POST['modify'][$cid]) && $_POST['modify'][$cid] );
						if(!$noprimary && ( $addprimary || (isset($this->infoTable[$cid]['primary']) && $this->infoTable[$cid]['primary']) ) ) $query .= ' PRIMARY KEY';
						$query .= (($this->infoTable[$cid]['dflt_value'] && $this->infoTable[$cid]['notnull'])? ' DEFAULT '.quotes($this->infoTable[$cid]['dflt_value']) : '' ).",\n";
						$newColumn[] = $this->infoTable[$cid]['name'];
						if(isset($_POST['after']) && ($_POST['after']==(string)$cid)) {
							while(list($key, $trash) = each($_POST['fieldName'])){
								if(!empty($_POST['fieldName'][$key])) $query .= $this->getPostProp($key);
							}
						}
					}
					if(isset($_POST['fieldNull'][$cid]) && !$this->infoTable[$cid]['notnull'] && ($_POST['fieldNull'][$cid]=='NOT NULL')) {
						$nullToNotNull[] = $this->infoTable[$cid]['name'];
					}				
				}
			}
			if(isset($_POST['after']) && ($_POST['after']=='END')){
				while(list($key, $trash) = each($_POST['fieldName'])) if(!empty($_POST['fieldName'][$key])) $query .= $this->getPostProp($key);
			}
			$query = substr($query, 0, strlen($query)-2)."\n);";
		}
		$condDrop = ( ($GLOBALS['action']=='delete') && !isset($_POST['modify']) );
		$displayError = false;
		if( !$condDrop && count($newColumn)>0) {
			$GLOBALS['phpSQLiteError'] = '';
			set_error_handler('phpSQLiteErrorHandling');			
			$displayError = $this->connId->alterTable($this->table, $query, $oldColumn, $newColumn, $nullToNotNull);
			restore_error_handler();
			if(!$displayError) {
				// rebuild index
				if(is_array($listIndexSQL)) {
					foreach($listIndexSQL as $numIndex=>$indexSQL) {
						$res = @$this->connId->getResId($indexSQL['sql']);
					}		
				}
			}
		} else {
			$this->connId->connId->query("BEGIN;", true, false);
			$query = 'DROP TABLE '.brackets($this->table).';';
			$res = $this->connId->connId->query($query, true, false);
			$this->connId->connId->query("COMMIT;", true, false);
		}

		$this->getTableProperties();
		if($displayError) displayError($this->connId->errorMessage);
		displayQuery($query);
		if($GLOBALS['action'] != 'delete')  {
			$this->tablePropView();
			echo "<script type=\"text/javascript\">parent.left.location='left.php?dbsel=".$GLOBALS['dbsel']."';</script>";
		} else {
			echo "<script type=\"text/javascript\">parent.left.location='left.php?dbsel=".$GLOBALS['dbsel']."'; parent.main.location='main.php?dbsel=".$GLOBALS['dbsel']."';</script>";
		}
	}

	/**
	* create column propertie from Form
	*
	* @access private
	* @param integer $index Number of column
	*/
	function getPostProp($index){
		$prop = brackets(cleanFieldName($_POST['fieldName'][$index])).' '.$_POST['fieldType'][$index].(($_POST['fieldLength'][$index])? '('.$_POST['fieldLength'][$index].') ' : ' ' );
		$prop .= $_POST['fieldNull'][$index].((isset($_POST['primary'][$index]) && $_POST['primary'][$index])? ' PRIMARY KEY' : '' );
		if($_POST['fieldDefault'][$index]!='') $prop .= ' DEFAULT '.quotes($_POST['fieldDefault'][$index]);
		elseif($_POST['fieldNull'][$index]=='NOT NULL') $prop .= ' DEFAULT "'.$GLOBALS["SQLiteType"][$_POST['fieldType'][$index]].'"';		
		$prop .= ",\n";
		return $prop;
	}
	
	/**
	* Display TABLE form
	*
	* @access private
	*/
	function tableEditForm(){
		if(isset($_POST['modify']) && is_array($_POST['modify'])){
			$nbChamp = count($_POST['modify']);
			$tabIndex = array_keys($_POST['modify']);
		} elseif($this->isExist && empty($GLOBALS['action'])) $nbChamp = count($this->infoTable);
		else $nbChamp = $GLOBALS['nbChamps'];
		if($nbChamp){ 
		  echo '<!-- SQLiteTableProperties.class.php : tableEditForm() -->'."\n";
			echo '<br><center>';
			if(!$this->isExist) echo '<h4>'.$GLOBALS['traduct']->get(25).' : ';
			else echo '<h4>'.$GLOBALS['traduct']->get(26).' : ';
			echo $this->table.'</h4>';
			echo '<form name="tabprop" action="main.php?dbsel='.$GLOBALS['dbsel'].'" method="POST" target="main">';
			echo '	<table class="Browse" cellpadding=0 cellspacing=0 width=80%>
						<thead>
						<tr>
							<td align="center" class="Browse">'.$GLOBALS['traduct']->get(27).'</td>
							<td align="center" class="Browse">'.$GLOBALS['traduct']->get(28).'</td>
							<td align="center" class="Browse">'.$GLOBALS['traduct']->get(29).'</td>
							<td align="center" class="Browse">'.$GLOBALS['traduct']->get(30).'</td>
							<td align="center" class="Browse">'.$GLOBALS['traduct']->get(31).'</td>
							<td align="center" class="Browse">'.$GLOBALS['traduct']->get(32).'</td>
						</tr>
						</thead>'."\n";
			for($i=0 ; $i<$nbChamp ; $i++){
				if(isset($tabIndex)) $index = $tabIndex[$i];
				else $index = $i;