/**
*
* @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;
}
