Issue when creating multi-column Indexes using Vanilla's Database Class

businessdad

I was working with Vanilla's Database Class to create some tables for my plugin, and I noticed two issues in the mechanism that builds DDL statements.

1- It doesn't allow to create multi-column Indexes.
I followed the code that applies schema changes (namely Gdn_MySQLStructure->_Create()) and I noticed there's no way to specify an index name and to include more than columns into it. By declaring a column index, a new index containing only that column will be created on the database.

Example
Gdn::Structure() ->Table('MyTable') ->PrimaryKey('MyTableID') ->Column('SomeUniqueField', 'int', FALSE, 'unique') ->Column('FirstName', 'varchar(100)', FALSE, 'index') ->Column('LastName', 'varchar(100)', FALSE, 'index') ->Set();

The resulting table will have, besides the Primary Key, the following indexes:

• Ix_MyTable_SomeUniqueField -> Unique Index
• Ix_MyTable_FirstName -> Index
• Ix_MyTable_LastName -> Index

There's no way to create a single index containing FirstName and LastName.

2- It forces composite indexes for Unique Keys
It may seem a contradiction of point 1, but it's not. It's not possible to explicitly create a multi-column index, but Vanilla creates one single Unique index, no matter how many columns have been indicated as unique.

Example
Gdn::Structure() ->Table('MyTable') ->PrimaryKey('MyTableID') ->Column('SomeUniqueField', 'int', FALSE, 'unique') ->Column('FirstName', 'varchar(100)', FALSE, 'unique') ->Column('LastName', 'varchar(100)', FALSE, 'unique') ->Set();

This time, the resulting table will have one single unique key, containing SomeUniqueField, FirstName and LastName. Again, it's not possible to explicitly create a multi-column index containing only the desired columns.

The above are serious limitations in the Schema functions, as designing Indexes properly is critical for optimal performances in a database. This is especially true for multi-column indexes, where the position of a column in the Index definition can make a significant difference.

Currently, as a workaround, I create the affected indexes using a manually written SQL Statement. This implies having to manually deal with schema changes between versions, which is not optimal. I could also try to modify the Core class and submit it to the Community for review, but I'd first have to find how the process to follow.

UnderDog

Thanks for the information.

I honestly thought that the DataBase class of Vanilla was a thin wrapper around the PDO class. Might be mistaking though.

businessdad

Correction: the class I'm referring to is Gdn_DatabaseStructure.

x00

Looking at the code

....
   protected function _IndexSql($Columns, $KeyType = FALSE) {
      $Result = array();
      $Keys = array();
      $Prefixes = array('key' => 'FK_', 'index' => 'IX_', 'unique' => 'UX_', 'fulltext' => 'TX_');
      
      // Gather the names of the columns.
      foreach ($Columns as $ColumnName => $Column) {
         $ColumnKeyTypes = (array)$Column->KeyType;

         foreach ($ColumnKeyTypes as $ColumnKeyType) {
            if(!$ColumnKeyType || ($KeyType && $KeyType != $ColumnKeyType))
               continue;

            // Don't add a fulltext if we don't support.
            if ($ColumnKeyType == 'fulltext' && !$this->_SupportsFulltext())
               continue;

            if($ColumnKeyType == 'key' || $ColumnKeyType == 'index') {
               $Name = $Prefixes[$ColumnKeyType].$this->_TableName.'_'.$ColumnName;
               $Result[$Name] = $ColumnKeyType." $Name (`$ColumnName`)";
            } else {
               // This is a multi-column key type so just collect the column name.
               $Keys[$ColumnKeyType][] = $ColumnName;
            }
         }
      }

      
      // Make the multi-column keys into sql statements.
      foreach($Keys as $KeyType2 => $Columns) {
         if($KeyType2 == 'primary') {
            $Result['PRIMARY'] = 'primary key (`'.implode('`, `', $Columns).'`)';
         } else {
            $Name = $Prefixes[$KeyType2].$this->_TableName;
            $Result[$Name] = "$KeyType2 index $Name (`".implode('`, `', $Columns).'`)';
         }
      }
      
      return $Result;
   }

It is 90% there you just need a way of specify the index name

I wonder if this will work:

Gdn::Structure()
       ->Table('MyTable')
       ->PrimaryKey('MyTableID') 
       ->Column('SomeUniqueField', 'int', FALSE, 'unique')
       ->Column('FirstName', 'varchar(100)', FALSE, ' ') 
       ->Column('LastName', 'varchar(100)', FALSE, ' ')
       ->Set();

or

Gdn::Structure()
       ->Table('MyTable')
       ->PrimaryKey('MyTableID') 
       ->Column('SomeUniqueField', 'int', FALSE, 'unique')
       ->Column('FirstName', 'varchar(100)', FALSE, ' unique') 
       ->Column('LastName', 'varchar(100)', FALSE, ' unique')
       ->Set();

x00

Another possible hack

Gdn::Structure()
       ->Table('MyTable')
       ->PrimaryKey('MyTableID') 
       ->Column('SomeUniqueField', 'int', FALSE, 'unique')
       ->Column('FirstName', 'varchar(100)', FALSE, '/*myindex*/') 
       ->Column('LastName', 'varchar(100)', FALSE, '/*myindex*/')
       ->Set();

x00

you could create a wrapper round this class create your own more complete _IndexSql

as UnderDog said this is just a wrapper. I seen full blown ORM libraries that still doesn't do everything. I gave up on coding idealism years ago.

UnderDog

x00 said:
I gave up on coding idealism years ago.

LOL!

Oh, I love this one too. Time for another line on a t-shirt. Or another line in a signature.

businessdad

@x00: Thanks, prompt reply as usual. Unfortunately, specifying the index name alone would not be enough, as the order of the columns matters, as well as the ordering of the data itself (ASC, DESC). Indexes are much more complex than it seems, and all this "tuning" is needed more often than most people think.

I agree with the fact that covering everything via code is not easy, that's one of the reasons why I tend to avoid ORM libraries (they have their place, but there are things than handwritten SQL can do much better).

Back to the issue, I'll attempt to extend Gdn_DatabaseStructure with an Index() method, so that it will still be possible to use current Table definition mechanism for simpler indexes, and create complex ones separately (which is what I do via SQL anyway, it's easier to read and maintain).

Thanks to all for the replies/comments/contributions.

UnderDog

I also saw a post recently that showed that someone used literal SQL query instead of the DataBase class. He sent his exact SQL query to the database using the class, but did not use the class to build his query, if you know what I mean :-)

Todd

We've recently added the ability to create indexes on multiple keys. You use the following notation:

Gdn::Structure()
       ->Table('MyTable')
       ->PrimaryKey('MyTableID') 
       ->Column('SomeUniqueField', 'int', FALSE, 'unique')
       ->Column('FirstName', 'varchar(100)', FALSE, 'index.1') 
       ->Column('LastName', 'varchar(100)', FALSE, 'index.1')
       ->Set();

Notice the dot notation after the word "index". If you want more fine grained control over the order of the columns without ordering them in the structure definition, well that really goes beyond the goal of Vanilla.

If you need to do that and are sure that the design of your application hasn't gone of the ropes somewhere I recommend just calling your create index statement directly.

Todd

Alright, full disclosure. Right after posting this I now need to create an index and change the order. I'm going to just do it the query route, but my crow is very tasty right now

businessdad

Todd said:
We've recently added the ability to create indexes on multiple keys. You use the following notation:

If you need to do that and are sure that the design of your application hasn't gone of the ropes somewhere I recommend just calling your create index statement directly.

Thanks for the new feature, and for noticing that requirement for indexes can be fairly complex. The most common case is when fields appearing in several multi-column indexes, sometimes with different sorting (ASC/DESC), then we can go deep into the realms of optimization (partitioning, partial indexes, clustered indexes, etc).

All the above don't necessarily mean that the "application has gone to the ropes"; actually, databases are the first step of application design, followed by the business layer.
I'm aware that Vanilla can't cover everything as it would be beyond its scope, but I think I'd still like to add an explicit "Index" function to allow more complex indexes via the Database class.

businessdad

By the way, I didn't get the idea about the crow... Is that some sort of idiomatic expression?

Todd

Eating crow means I was wrong and I'm admitting it.

I think that the structure table method and columns are fine as they are right now, but I may add an index method for these additional indexes that are more complex.