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'].'&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'].'&table='.$this->table.'&index_action=modify&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']."&table=".$this->table."&index_action=delete&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"> </td>'; 154 echo ' <td align="center" class="Index"> </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'].'&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'].'&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(' ', 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).' : </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