silverstripe/sapphire — pull request #782 by tractorcow (2012-09-11 02:38:31+00:00)
Title: FIXED: Generation of table indexes

When working with a fulltext search enabled site I noticed that there was a lot of unnecessary DDL operations being invoked during dev/build.

Every fullteach search index change would appear similar to the below:

```
Index File.SearchFields: changed to fulltext (Filename,Title,Content) (from (SearchFields,Filename,Title,Content))
```

Apparently, due to line 424 of Database.php any fulltext index would be transformed into a "spec string" which includes the index name. Apparently the intention of this was to cause a change in index in case the existing fulltext index had changed or been renamed.

However, there was a flaw in the code which would break the logic. If you follow the code you'll notice that, in this case, $array_spec is generated entirely from the $spec variable, rather than considering any currently existing database index.

This $array_spec is then compared later to DB::getConn()->convertIndexSpec($spec).

In other words, the variable is simply transformed twice in two different ways, and is compared against itself, and permanantly finding a difference.

Removing this check reduces the unnecessary recreation of the fulltext index every dev build.

However, I do note that there are benefits to having a fulltext index rebuilt every dev/build, however the benefits should not be the result of a bug, but rather should be coded to intentionally rebuild the index and to correctly report this operation to the user at this time. E.g. something along the lines of "Fulltext Index File.SearchFields (Filename, Title, Content) rebuilt".

I have added additional test cases to DataObjectSchemaGenerationTest to test that schema updates for both fields and indexes are correctly initatiated when necessary, and not when not.

I have updated various references to indexes across the code to make sure that they uniformly have double-quoted field names, preventing additional unnecessary index regeneration.

There is also the need to develop a better robust schema change detection mechanism; Quoted field lists should be distinguished from unquoted lists.