1<?php
2/**
3* Web based SQLite management
4* Index management class
5* @package SQLiteManager
6* @author Fr�d�ric HENNINOT
7* @version $Id: TableIndex.class.php,v 1.28 2006/04/14 15:16:52 freddy78 Exp $ $Revision: 1.28 $
8*/
9
10/**
11* Web based SQLite management
12* Index management class
13* @package SQLiteManager
14* @author Fr�d�ric HENNINOT
15* @version $Id: TableIndex.class.php,v 1.28 2006/04/14 15:16:52 freddy78 Exp $ $Revision: 1.28 $
16*/
17class TableIndex {
18
19	/**
20	* Table name
21	* @access public
22	* @var string
23	*/
24	var $table;
25
26	/**
27	* reference with table properties
28	* @access private
29	* @var array
30	*/
31	var $tableInfo;
32
33	/**
34	* Index properties
35	* @access public
36	* @var array
37	*/
38	var $indexInfo;
39
40	/**
41	* Class constructor
42	*
43	* @param string $table Table name
44	* @param array &$propTable reference of the table properties
45	*/
46	function __construct($table, &$propTable){
47		$this->table = $table;
48		if(is_array($propTable)) $this->tableInfo = $propTable;
49		$this->getIndexList();
50		if(!isset($GLOBALS['index_action'])) $GLOBALS['index_action'] = '';
51		switch($GLOBALS['index_action']){
52			case '':
53				$this->indexPropView();
54				break;
55			case 'addIndex':
56			case 'modify':
57				$this->indexPropForm();
58				break;
59			case 'save':
60			case 'delete':
61				if(!empty($_POST['addCols'])) $this->indexPropForm();
62				else $this->save();
63				break;
64			default:
65				break;
66		}
67	}
68
69	/**
70	* Save the index properties
71	* manage new or update
72	*
73	*/
74	function save(){
75		if($GLOBALS['index_action'] != 'delete'){
76			foreach($this->indexInfo as $listIndex) {
77				if(isset($listIndex['name']) && ($listIndex['name'] == $_REQUEST['name'])){
78					$query[] = 'DROP INDEX '.$_POST['name'].';';
79				}
80			}
81		} else {
82			$query[] = $GLOBALS['DisplayQuery'] = 'DROP INDEX '.$this->indexInfo[$_GET['indexSeq']]['name'].';';
83		}
84		if($GLOBALS['index_action'] == 'save') {
85			if(is_array($_POST['columnName'])){
86				while(list($id, $cont) = each($_POST['columnName'])) {
87				  if($cont == '') unset($_POST['columnName'][$id]);
88				  $_POST['columnName'][$id] = brackets($_POST['columnName'][$id]);
89				}
90			}
91			$query[] = $GLOBALS['DisplayQuery'] = 'CREATE '.(($_POST['indexType'])? 'UNIQUE ' : '' ).'INDEX '.str_replace(' ','_',$_POST['name']).' ON '.brackets($this->table).'('.implode(',', $_POST['columnName']).');';
92		}
93
94		foreach($query as $req) {
95
96			$GLOBALS['workDb']->connId->query($req, false, false);
97
98		}
99		return;
100	}
101
102	/**
103	* Display all index properties
104	*/
105	function indexPropView(){
106		echo '<form name="indexprop" action="main.php?dbsel='.$GLOBALS['dbsel'].'&amp;table='.$this->table.'" method="POST" target="main">';
107		echo '<table class="Index" cellpadding="0" cellspacing="0" width="90%">
108					<thead>
109					<tr>
110						<td align="center" class="Browse">'.$GLOBALS['traduct']->get(91).'</td>
111						<td align="center" class="Browse">'.$GLOBALS['traduct']->get(28).'</td>
112						<td align="center" class="Browse">'.$GLOBALS['traduct']->get(27).'</td>
113						<td align="center" class="Browse" colspan="2">'.$GLOBALS['traduct']->get(33).'</td>
114					</tr>
115					</thead>';
116		if(is_array($this->indexInfo)) while(list($i, $info) = each($this->indexInfo)){
117			$this->linePropView($i, $info);
118		}
119		echo '</table>';
120		echo '<input type="hidden" name="action" value="">'."\n";
121		echo '</form>'."\n";
122		if(!$GLOBALS['workDb']->isReadOnly() && displayCondition('properties')) $this->formAddIndex();
123		return;
124	}
125
126	/**
127	* Display one index properties
128	*/
129	function linePropView($i, $info){
130		static $indexI;
131		if($indexI == '') $indexI = 0;
132		if($indexI % 2) $localBgColor = $GLOBALS['browseColor1'];
133		else $localBgColor = $GLOBALS['browseColor2'];
134
135		echo '	<tr bgcolor="'.$localBgColor.'" '.
136		     'onMouseOver="'."setRowColor(this, $indexI, 'over', '$localBgColor', '".$GLOBALS['browseColorOver']."', '".$GLOBALS["browseColorClick"]."')\" ".
137			 'onMouseOut="'."setRowColor(this, $indexI, 'out', '$localBgColor', '".$GLOBALS["browseColorOver"]."', '".$GLOBALS["browseColorClick"]."')\">\n".'
138					<td align="left" class="Index">'.$info['name'].'</td>
139					<td align="left" class="Index">'.$info['type'].'</td>
140					<td align="right" class="Index">'.implode('<br>', $info['champ']).'</td>';
141		if(strtoupper($i) != 'PRIMARY'){
142			echo '		<td align="center" class="Index">';
143			if(!$GLOBALS['workDb']->isReadOnly() && displayCondition('properties')) echo '<a href="main.php?dbsel='.$GLOBALS['dbsel'].'&amp;table='.$this->table.'&amp;index_action=modify&amp;indexSeq='.$i.'" class="Browse" target="main">'.displayPics('edit.png', $GLOBALS['traduct']->get(14)).'</a>';
144			else echo '<i>'.displayPics('edit_off.png', $GLOBALS['traduct']->get(14)).'</i>';
145			echo '</td>';
146			echo '		<td align="center" class="Index">';
147			if(!$GLOBALS['workDb']->isReadOnly() && displayCondition('properties'))
148				echo "<a href=\"#\" onClick=\"javascript:if(confirm('".addslashes($GLOBALS['traduct']->get(92))." \'".$info['name']."\'')) parent.main.location='main.php?dbsel=".$GLOBALS['dbsel']."&amp;table=".$this->table."&amp;index_action=delete&amp;indexSeq=".$i."';".'" class="Browse">'.
149				displayPics('edittrash.png', $GLOBALS['traduct']->get(15)).'</a>';
150			else echo '<i>'.displayPics('edittrash_off.png', $GLOBALS['traduct']->get(15)).'</i>';
151			echo '</td>';
152		} else {
153			echo '		<td align="center" class="Index">&nbsp;</td>';
154			echo '		<td align="center" class="Index">&nbsp;</td>';
155		}
156		echo '	</tr>';
157		$indexI++;
158	}
159
160	/**
161	* Display form to add index
162	*/
163	function formAddIndex(){
164		echo '<form name="addIndex" action="main.php?dbsel='.$GLOBALS['dbsel'].'&amp;table='.$this->table.'" method="POST" target="main">'."\n";
165		echo '	<span style="font-size: 12px;">'.$GLOBALS['traduct']->get(93).'
166				<input type="text" name="nbCols" value=1 size="2" class="small-input" /> '.
167				$GLOBALS['traduct']->get(94).'
168				<input class="button" type="submit" value="'.$GLOBALS['traduct']->get(69).'" />'."\n";
169		echo '<input type="hidden" name="index_action" value="addIndex">'."\n";
170		echo '</span></form>'."\n";
171	}
172
173	/**
174	* Display index Form
175	* Add or modif
176	*/
177	function indexPropform(){
178		if( isset($GLOBALS['indexSeq']) && ($GLOBALS['indexSeq'] != '') ){
179			$seq = $GLOBALS['indexSeq'];
180			$nbCols = count($this->indexInfo[$seq]['champ']);
181		} else {
182			$GLOBALS['indexSeq'] = '';
183			$nbCols = $GLOBALS['nbCols'];
184			$seq = '';
185		}
186		if(isset($_POST['addCols']) && $_POST['addCols']) $nbCols += $GLOBALS['addCols'];
187		echo '<form name="addIndex" action="main.php?dbsel='.$GLOBALS['dbsel'].'&amp;table='.$this->table.'" method="POST" target="main">'."\n";
188		echo '<span style="font-size: 12px;"><b>'.$GLOBALS['traduct']->get(19).' : </b>
189				<input type="text" class="text" name="name" value="'.((isset($_POST['name']))? $_POST['name'] : ((isset($this->indexInfo[$seq]['name']))? $this->indexInfo[$seq]['name'] : '' ) ).'" class="small-input">'.str_repeat('&nbsp;', 5).'<b>'.$GLOBALS['traduct']->get(20).' : </b>';
190		if(isset($_POST['indexType']) && !empty($_POST['indexType'])) $currentType = $_POST['indexType'];
191		elseif(isset($this->indexInfo[$seq]['type'])) $currentType = $this->indexInfo[$seq]['type'];
192		else $currentType = '';
193		echo '<select name="indexType" class="small-input"><option value="UNIQUE"'.(($currentType == 'UNIQUE')? ' selected="selected"' : '' ).'>UNIQUE</option><option value=""'.(($currentType != 'UNIQUE')? ' selected="selected"' : '' ).'>KEY</option></select><br/>';
194		echo '<center><table width="98%"><tr><td align="right" valign="top"><b>'.$GLOBALS['traduct']->get(27).' :&nbsp;</b></td><td align="center">';
195		for($i=0 ; $i<$nbCols ; $i++){
196			echo '<select name="columnName[]" class="small-input"><option value="">-- '.$GLOBALS['traduct']->get(95).' --</option>';
197			foreach($this->tableInfo as $champInfo) {
198				echo '<option value="'.$champInfo['name'].'"'.((($this->indexInfo[$seq]['champ'][$i] == $champInfo['name']) || ($_POST['columnName'][$i] == $champInfo['name']))? ' selected="selected"' : '' ).'>';
199				echo $champInfo['name'].'</option>';
200			}
201			echo '</select><br/>';
202		}
203		echo '</td></tr>';
204		echo '<tr><td align="center" colspan="2"><input class="button" type="submit" value="'.$GLOBALS['traduct']->get(51).'" class="small-input"></td></tr>';
205		echo '</table></center>';
206		echo '</span>';
207		echo '<hr width="90%">'."\n";
208		echo '<span style="font-size: 12px;">'.$GLOBALS['traduct']->get(96).' <input type="text" class="text" name="addCols" value="" class="small-input" size="2"> '.$GLOBALS['traduct']->get(94).'.<input class="button" type="submit" value="'.$GLOBALS['traduct']->get(69).'" class="small-input"></span>'."\n";
209		echo '<input type="hidden" name="nbCols" value="'.$nbCols.'"><input type="hidden" name="index_action" value="save"><input type="hidden" name="indexSeq" value="'.$GLOBALS['indexSeq'].'"></form>'."\n";
210
211	}
212
213	/**
214	* Return the index list
215	*/
216	function getIndexList(){
217		if(is_array($this->tableInfo)){
218			foreach($this->tableInfo as $propTable) {
219				if(isset($propTable['primary'])){
220					$temp['name'] = $temp['type'] = 'PRIMARY';
221					$temp['champ'][] = $propTable['name'];
222					$this->indexInfo['primary'] = $temp;
223				}
224			}
225		}
226		$tabIndexList = $GLOBALS['workDb']->connId->array_query('PRAGMA index_list('.brackets($this->table).');', SQLITE_ASSOC);
227		if(is_array($tabIndexList)) {
228			foreach($tabIndexList as $indexInfo){
229				if(preg_match('#autoindex#i', $indexInfo['name'])) continue;
230				if(!empty($indexInfo)){
231					$tabInfoIndex = $GLOBALS['workDb']->connId->array_query('PRAGMA index_info('.brackets($indexInfo['name']).');', SQLITE_ASSOC);
232					if(is_array($tabInfoIndex)){
233						$indexNum = $indexInfo['seq'];
234						foreach($tabInfoIndex as $indexChamp) $this->indexInfo[$indexNum]['champ'][] = $indexChamp['name'];
235						$this->indexInfo[$indexNum]['name'] = $indexInfo['name'];
236						$this->indexInfo[$indexNum]['type'] = (($indexInfo['unique'])? 'UNIQUE' : 'KEY' );
237					}
238				}
239			}
240		}
241		return $this->indexInfo;
242	}
243}
244?>
245