/** * * @param WOOOF $wo * @param string $databaseName * @param string $tableName * @param bool $execute // Optional, default is false. Set to true to actually execute the statements * @return false|true|array // true on successful execution of statements. array if execute=false */ public static function buildIndexesForTable(WOOOF $wo, $databaseName, $tableName, $execute = false) { $lc_legalPattern = '/^([piuts])([A-Z])([1-9])([ad])?$/'; // Type of index: p -> primary, i -> index, u -> unique, t -> fullText, s -> spatial // 'Name' of index: just a different letter for each index // Position: of column to the specified index // Collation: (optional) a -> ASC, d -> DESC (not implemented in MySQL!!) //echo '<h2>'.__CLASS__.'.'.__FUNCTION__.'</h2>'; $sql = "\n\t\t\tselect c.name, c.indexParticipation\n\t\t\tfrom __tableMetaData t, __columnMetaData c\n\t\t\twhere t.tableName = '{$tableName}' and c.tableId = t.id and c.indexParticipation is not null and c.indexParticipation != ''\n\t\t"; $ipResults = $wo->db->query($sql); if ($ipResults === FALSE) { $wo->logError(self::_ECP . "0135 You may need to WOOOF_MetaData:selfUpgradeMetaData your db first!"); return FALSE; } $indexesArray = array(); // hold the columns: array( iName => array( [0] => array( colName, collation ), ... ), ... ) $indexesArray2 = array(); // hold the type: array( iName => iType ) $dbIndexesArray = array(); $dbIndexesArray2 = array(); while (($aResult = $wo->db->fetchAssoc($ipResults)) !== NULL) { // $aResult: array( name, indexParticipation ) //var_dump($aResult); $colName = $aResult['name']; $indexParticipationsArray = explode(',', $aResult['indexParticipation']); foreach ($indexParticipationsArray as $anIndexParticipationString) { $anIndexParticipationString = trim($anIndexParticipationString); if (!$wo->hasContent($anIndexParticipationString)) { continue; } $matches = null; $matchOk = preg_match($lc_legalPattern, $anIndexParticipationString, $matches); if ($matchOk === 0 or $matchOk === FALSE) { $wo->logError(self::_ECP . "0100 Bad IndexParticipation value [{$anIndexParticipationString}] for column [{$tableName}.{$colName}]"); return FALSE; } // var_dump($matches); list($dummy, $iType, $iName, $iSeq) = $matches; $iCollation = $wo->getFromArray($matches, 4); if (isset($indexesArray[$iName][$iSeq])) { $wo->logError(self::_ECP . "0105 Multiple columns ([{$colName}], [{$indexesArray[$iName][$iSeq][0]}]) with same sequence number [{$iSeq}] for index [{$iName}] on column [{$tableName}.{$colName}]"); return FALSE; } $indexesArray[$iName][$iSeq] = array($colName, $iCollation); if (!isset($indexesArray2[$iName])) { $indexesArray2[$iName] = $iType; } else { if ($indexesArray2[$iName] != $iType) { $wo->logError(self::_ECP . "0110 Index [{$iName}] of column [{$tableName}.{$colName}] defined with multiple types: [{$iType}] and [{$indexesArray2[$iName]}]"); return false; } } } // foreach one of the column's participations } // foreach column with indexParticipation(s) //var_dump($indexesArray); if (count($indexesArray) == 0) { return $execute ? true : array(); } // Load existing indexes // $dbIndTemp = self::getDBIndexesForTable($wo, $databaseName, $tableName); if ($dbIndTemp === FALSE) { return FALSE; } list($dbIndexesArray2, $dbIndexesArray) = $dbIndTemp; $sqlStatements = array(); foreach ($indexesArray as $anIndexCode => &$anIndexColumns) { //echo "$anIndexCode<br>"; $sqlOut = ''; $indexName = $tableName . '_idx' . $anIndexCode; ksort($anIndexColumns); // sort according to specified position and not leave according to order of entry in the array // Check if already built/exists in DB // $needToRecreateIndex = false; $needToCreateIndex = false; if (isset($dbIndexesArray2[$anIndexCode])) { if ($dbIndexesArray2[$anIndexCode] == $indexesArray2[$anIndexCode]) { if (count($dbIndexesArray[$anIndexCode]) == count($indexesArray[$anIndexCode])) { $i = 1; foreach ($anIndexColumns as $aColumn) { if ($aColumn[0] == $dbIndexesArray[$anIndexCode][$i][0]) { // ignore collation differences as collation is a joke (ASC only) in MySQL } else { $needToRecreateIndex = true; break; } // same column or not in that position $i++; } // foreach column in index } else { $needToRecreateIndex = true; } // count of cols same or not } else { $needToRecreateIndex = true; } // index type same or not } else { $needToCreateIndex = true; } // var_dump($needToRecreateIndex, $needToCreateIndex); if ($needToRecreateIndex) { $sqlStatements[] = "ALTER TABLE `{$tableName}` DROP INDEX `{$indexName}`;"; } if ($needToCreateIndex or $needToRecreateIndex) { $sqlOut .= "ALTER TABLE `{$tableName}` ADD "; switch ($indexesArray2[$anIndexCode]) { case 'p': $sqlOut .= "CONSTRAINT PRIMARY KEY "; break; case 'u': $sqlOut .= "UNIQUE KEY `{$indexName}` "; break; case 'i': $sqlOut .= "INDEX `{$indexName}` "; break; case 's': $sqlOut .= "SPATIAL INDEX `{$indexName}` "; break; case 't': $sqlOut .= "FULLTEXT INDEX `{$indexName}` "; break; default: $sqlOut .= " " . $indexesArray2[$anIndexCode] . " ***not implemented*** "; } $sqlOut .= '( '; foreach ($anIndexColumns as $aColumn) { $sqlOut .= "`" . $aColumn[0] . "` "; if (isset($aColumn[1])) { $sqlOut .= ' ' . ($aColumn[1] == 'd' ? 'DESC' : 'ASC'); } $sqlOut .= ', '; } // foreach column $sqlOut = substr($sqlOut, 0, -2); $sqlOut .= ' ) '; $sqlOut .= ';'; $sqlStatements[] = $sqlOut; } // create index } // foreach index // var_dump($sqlStatements); if ($execute) { if (count($sqlStatements) > 0) { $succ = $wo->db->queryMultiple($sqlStatements); return $succ; } else { return true; } } return $sqlStatements; }