3 declare(strict_types
=1);
8 use function array_pop
;
10 use function htmlspecialchars
;
13 * Index manipulation class
17 public const PRIMARY
= 1;
18 public const UNIQUE
= 2;
19 public const INDEX
= 4;
20 public const SPATIAL
= 8;
21 public const FULLTEXT
= 16;
24 * Class-wide storage container for indexes (caching, singleton)
26 * @var array<string, array<string, array<string, Index>>>
28 private static array $registry = [];
30 /** @var string The name of the schema */
31 private string $schema = '';
33 /** @var string The name of the table */
34 private string $table = '';
36 /** @var string The name of the index */
37 private string $name = '';
42 * @var array<string|int, IndexColumn>
44 private array $columns = [];
47 * The index method used (BTREE, HASH, RTREE).
49 private string $type = '';
52 * The index choice (PRIMARY, UNIQUE, INDEX, SPATIAL, FULLTEXT)
54 private string $choice = '';
59 private string $remarks = '';
62 * Any comment provided for the index with a COMMENT attribute when the
65 private string $comment = '';
67 /** @var bool false if the index cannot contain duplicates, true if it can. */
68 private bool $nonUnique = false;
71 * Indicates how the key is packed. NULL if it is not.
73 private string|
null $packed = null;
76 * Block size for the index
78 private int $keyBlockSize = 0;
81 * Parser option for the index
83 private string $parser = '';
85 /** @param mixed[] $params parameters */
86 public function __construct(array $params = [])
92 * Creates (if not already created) and returns the corresponding Index object
94 * @return Index corresponding Index object
96 public static function singleton(
97 DatabaseInterface
$dbi,
100 string $indexName = '',
102 self
::loadIndexes($dbi, $table, $schema);
103 if (isset(self
::$registry[$schema][$table][$indexName])) {
104 return self
::$registry[$schema][$table][$indexName];
107 $index = new Index();
108 if ($indexName !== '') {
109 $index->setName($indexName);
110 self
::$registry[$schema][$table][$index->getName()] = $index;
117 * returns an array with all indexes from the given table
121 public static function getFromTable(DatabaseInterface
$dbi, string $table, string $schema): array
123 self
::loadIndexes($dbi, $table, $schema);
125 return self
::$registry[$schema][$table] ??
[];
129 * Returns an array with all indexes from the given table of the requested types
131 * @param string $table table
132 * @param string $schema schema
133 * @param int $choices choices
135 * @return Index[] array of indexes
137 public static function getFromTableByChoice(string $table, string $schema, int $choices = 31): array
140 foreach (self
::getFromTable(DatabaseInterface
::getInstance(), $table, $schema) as $index) {
141 if (($choices & self
::PRIMARY
) && $index->getChoice() === 'PRIMARY') {
145 if (($choices & self
::UNIQUE
) && $index->getChoice() === 'UNIQUE') {
149 if (($choices & self
::INDEX
) && $index->getChoice() === 'INDEX') {
153 if (($choices & self
::SPATIAL
) && $index->getChoice() === 'SPATIAL') {
157 if ((($choices & self
::FULLTEXT
) === 0) ||
$index->getChoice() !== 'FULLTEXT') {
167 public static function getPrimary(DatabaseInterface
$dbi, string $table, string $schema): Index|
null
169 self
::loadIndexes($dbi, $table, $schema);
171 return self
::$registry[$schema][$table]['PRIMARY'] ??
null;
175 * Load index data for table
177 private static function loadIndexes(DatabaseInterface
$dbi, string $table, string $schema): void
179 if (isset(self
::$registry[$schema][$table])) {
183 $rawIndexes = $dbi->getTableIndexes($schema, $table);
184 foreach ($rawIndexes as $eachIndex) {
185 $eachIndex['Schema'] = $schema;
186 $keyName = $eachIndex['Key_name'];
187 if (! isset(self
::$registry[$schema][$table][$keyName])) {
188 $key = new Index($eachIndex);
189 self
::$registry[$schema][$table][$keyName] = $key;
191 $key = self
::$registry[$schema][$table][$keyName];
194 $key->addColumn($eachIndex);
199 * Add column to index
201 * @param array<string, string|null> $params column params
203 public function addColumn(array $params): void
205 $key = $params['Column_name'] ??
$params['Expression'] ??
'';
206 if (isset($params['Expression'])) {
207 // The Expression only does not make the key unique, add a sequence number
208 $key .= $params['Seq_in_index'];
215 $this->columns
[$key] = new IndexColumn($params);
219 * Adds a list of columns to the index
221 * @param mixed[] $columns array containing details about the columns
223 public function addColumns(array $columns): void
227 if (isset($columns['names'])) {
230 // $columns[sub_parts][]
231 foreach ($columns['names'] as $key => $name) {
232 $subPart = $columns['sub_parts'][$key] ??
'';
233 $addedColumns[] = ['Column_name' => $name, 'Sub_part' => $subPart];
236 // coming from SHOW INDEXES
238 // $columns[][sub_part]
240 $addedColumns = $columns;
243 foreach ($addedColumns as $column) {
244 $this->addColumn($column);
249 * Returns true if $column indexed in this index
251 * @param string $column the column
253 public function hasColumn(string $column): bool
255 return isset($this->columns
[$column]);
261 * @param mixed[] $params index details
263 public function set(array $params): void
265 if (isset($params['columns'])) {
266 $this->addColumns($params['columns']);
269 if (isset($params['Schema'])) {
270 $this->schema
= $params['Schema'];
273 if (isset($params['Table'])) {
274 $this->table
= $params['Table'];
277 if (isset($params['Key_name'])) {
278 $this->name
= $params['Key_name'];
281 if (isset($params['Index_type'])) {
282 $this->type
= $params['Index_type'];
285 if (isset($params['Comment'])) {
286 $this->remarks
= $params['Comment'];
289 if (isset($params['Index_comment'])) {
290 $this->comment
= $params['Index_comment'];
293 if (isset($params['Non_unique'])) {
294 $this->nonUnique
= (bool) $params['Non_unique'];
297 if (isset($params['Packed'])) {
298 $this->packed
= $params['Packed'];
301 if (isset($params['Index_choice'])) {
302 $this->choice
= $params['Index_choice'];
303 } elseif ($this->name
=== 'PRIMARY') {
304 $this->choice
= 'PRIMARY';
305 } elseif ($this->type
=== 'FULLTEXT') {
306 $this->choice
= 'FULLTEXT';
308 } elseif ($this->type
=== 'SPATIAL') {
309 $this->choice
= 'SPATIAL';
311 } elseif (! $this->nonUnique
) {
312 $this->choice
= 'UNIQUE';
314 $this->choice
= 'INDEX';
317 if (isset($params['Key_block_size'])) {
318 $this->keyBlockSize
= (int) $params['Key_block_size'];
321 if (! isset($params['Parser'])) {
325 $this->parser
= $params['Parser'];
329 * Returns the number of columns of the index
331 * @return int the number of the columns
333 public function getColumnCount(): int
335 return count($this->columns
);
339 * Returns the index comment
341 * @return string index comment
343 public function getComment(): string
345 return $this->comment
;
349 * Returns index remarks
351 * @return string index remarks
353 public function getRemarks(): string
355 return $this->remarks
;
359 * Return the key block size
361 public function getKeyBlockSize(): int
363 return $this->keyBlockSize
;
369 public function getParser(): string
371 return $this->parser
;
375 * Returns concatenated remarks and comment
377 * @return string concatenated remarks and comment
379 public function getComments(): string
381 $comments = $this->getRemarks();
382 if ($comments !== '') {
386 $comments .= $this->getComment();
392 * Returns index type (BTREE, HASH, RTREE)
394 * @return string index type
396 public function getType(): string
402 * Returns index choice (PRIMARY, UNIQUE, INDEX, SPATIAL, FULLTEXT)
404 * @return string index choice
406 public function getChoice(): string
408 return $this->choice
;
412 * Returns a lit of all index types
414 * @return string[] index types
416 public static function getIndexTypes(): array
418 return ['BTREE', 'HASH'];
421 public function hasPrimary(): bool
423 return self
::getPrimary(DatabaseInterface
::getInstance(), $this->table
, $this->schema
) !== null;
427 * Returns how the index is packed
429 * @return string|null how the index is packed
431 public function getPacked(): string|
null
433 return $this->packed
;
437 * Returns 'No' if the index is not packed,
438 * how the index is packed if packed
440 public function isPacked(): string
442 if ($this->packed
=== null) {
446 return htmlspecialchars($this->packed
);
450 * Returns bool false if the index cannot contain duplicates, true if it can
452 * @return bool false if the index cannot contain duplicates, true if it can
454 public function getNonUnique(): bool
456 return $this->nonUnique
;
460 * Returns whether the index is a 'Unique' index
462 * @param bool $asText whether to output should be in text
464 * @return string|bool whether the index is a 'Unique' index
466 public function isUnique(bool $asText = false): string|
bool
469 return $this->nonUnique ?
__('No') : __('Yes');
472 return ! $this->nonUnique
;
476 * Returns the name of the index
478 * @return string the name of the index
480 public function getName(): string
486 * Sets the name of the index
488 public function setName(string $name): void
494 * Returns the columns of the index
496 * @return IndexColumn[]
498 public function getColumns(): array
500 return $this->columns
;
504 * Gets the properties in an array for comparison purposes
506 * @return array<string, array<int, array<string, int|string|null>>|string|null>
507 * @psalm-return array{
508 * Packed: string|null,
509 * Index_choice: string,
510 * columns?: list<array{
511 * Column_name: string,
513 * Collation: string|null,
514 * Sub_part: int|null,
519 public function getCompareData(): array
521 $data = ['Packed' => $this->packed
, 'Index_choice' => $this->choice
];
523 foreach ($this->columns
as $column) {
524 $data['columns'][] = $column->getCompareData();
531 * Function to check over array of indexes and look for common problems
533 * @param string $table table name
534 * @param string $schema schema name
536 * @return string Output HTML
538 public static function findDuplicates(string $table, string $schema): string
540 $indexes = self
::getFromTable(DatabaseInterface
::getInstance(), $table, $schema);
544 // count($indexes) < 2:
545 // there is no need to check if there less than two indexes
546 if (count($indexes) < 2) {
550 // remove last index from stack and ...
551 while ($whileIndex = array_pop($indexes)) {
552 // ... compare with every remaining index in stack
553 foreach ($indexes as $eachIndex) {
554 if ($eachIndex->getCompareData() !== $whileIndex->getCompareData()) {
558 // did not find any difference
559 // so it makes no sense to have this two equal indexes
561 $message = Message
::notice(
563 'The indexes %1$s and %2$s seem to be equal and one of them could possibly be removed.',
566 $message->addParam($eachIndex->getName());
567 $message->addParam($whileIndex->getName());
568 $output .= $message->getDisplay();
570 // there is no need to check any further indexes if we have already
571 // found that this one has a duplicate