Couple questions re: database indexes

I'm working on some automatic index generation stuff over in Hobo, and ran into a couple issues relating to indexes.

First off, is there a programmatic way to get the DB's limits for index name length? It's easy enough to look them up for a particular DB (in manuals), but it's unpleasant when the first time you find out what they are is when a migration goes "kaboom". Logically, this would be a constant in the various AbstractAdapter classes. The only guaranteed strategy otherwise is to limit to the lowest common limit, which is a measly 30 characters for Oracle.

Second, is there a cross-DB reliable way to rename an index? Right now, the table name is included within the index name (presumably to keep Postgres sane, where index names need to be unique across the DB rather than within a table). This means that a table that gets renamed now has indexes that don't match. There's no runtime consequence to this, but it drives my code crazy. :slight_smile:

Finally, would it make sense to add an option to add_index that checks for a name collision *before* attempting to create the index? Currently, running a migration to add indexes to a production DB can leave things "stuck" if one of the indexes already exists. The migration can't run to completion, and the only solution is to manually reverse the whole migration and try again. Note that this is only an issue for DBs without transactional DDL, but that covers some of the popular ones...

Thoughts on these issues are appreciated.

--Matt Jones

First off, is there a programmatic way to get the DB's limits for
index name length? It's easy enough to look them up for a particular
DB (in manuals), but it's unpleasant when the first time you find out
what they are is when a migration goes "kaboom". Logically, this would
be a constant in the various AbstractAdapter classes. The only
guaranteed strategy otherwise is to limit to the lowest common limit,
which is a measly 30 characters for Oracle.

If you want to do the investigation and send us a patch, I can't see
why we wouldn't want ThatAdapter.index_name_limit. Similarly
table_name_limit etc could be useful.

Second, is there a cross-DB reliable way to rename an index? Right
now, the table name is included within the index name (presumably to
keep Postgres sane, where index names need to be unique across the DB
rather than within a table). This means that a table that gets renamed
now has indexes that don't match. There's no runtime consequence to
this, but it drives my code crazy. :slight_smile:

Not in the current migration apis, you could take a look at it and
submit a patch. However off-hand I can't see a 'rename index' or
similar for mysql...

Finally, would it make sense to add an option to add_index that checks
for a name collision *before* attempting to create the index?
Currently, running a migration to add indexes to a production DB can
leave things "stuck" if one of the indexes already exists. The
migration can't run to completion, and the only solution is to
manually reverse the whole migration and try again. Note that this is
only an issue for DBs without transactional DDL, but that covers some
of the popular ones...

Yes, this sounds like a good idea, check for name collision and names
which exceed the adapter limit would let us give relatively nice error
messages.

Thoughts on these issues are appreciated.

All three sound like nice little patches, look forward to you submitting them :slight_smile:

First off, is there a programmatic way to get the DB's limits for
index name length? It's easy enough to look them up for a particular
DB (in manuals), but it's unpleasant when the first time you find out
what they are is when a migration goes "kaboom". Logically, this would
be a constant in the various AbstractAdapter classes. The only
guaranteed strategy otherwise is to limit to the lowest common limit,
which is a measly 30 characters for Oracle.

If you want to do the investigation and send us a patch, I can't see
why we wouldn't want ThatAdapter.index_name_limit. Similarly
table_name_limit etc could be useful.

Anyone else have a favorite DB limit that should probably be accessible? Here's a list of ones I can think of quickly:

- column name length
- table name length
- index name length
- number of indexes
- number of columns in an multicolumn index
- array length in an IN clause (Oracle, I'm looking at you...)
- total SQL query length
- maximum number of joins in a single query (? - could see it being an issue)

Others? This might be better as an OpenStruct or something. We'll also need a way to pick reasonable defaults for adapters that don't define these values.

Second, is there a cross-DB reliable way to rename an index? Right
now, the table name is included within the index name (presumably to
keep Postgres sane, where index names need to be unique across the DB
rather than within a table). This means that a table that gets renamed
now has indexes that don't match. There's no runtime consequence to
this, but it drives my code crazy. :slight_smile:

Not in the current migration apis, you could take a look at it and
submit a patch. However off-hand I can't see a 'rename index' or
similar for mysql...

There isn't one for MySQL, to the best of my knowledge. Postgres supports an 'ALTER INDEX' statement, though. Maybe the best way would be to have a generically valid way (drop old / add new) in AbstractAdapter and override as needed?

Looks like I've got a busy weekend of reading DBMS manuals ahead of me... :slight_smile:

--Matt Jones

From the SQL Server adapter perspective, this is from my own search
vs testing all this myself in 2000/2005/2008. This was from 2000, and
I'm sure 2005/2008 might be higher. So LCD is good.

Column Name Length 128
Index Name Length 128
Table Name Length 128
Max Index Per Table 250
SQL Query Length 16777216
Max Columns Per Table 1024

I could look up more? Would love to see the ticket.

  - Ken

Working on this, but the values are harder to find than I'd expected. Postgres further muddies the waters by having some of the limits configurable by recompiling... ugh. Anybody with more adapter expertise willing to fill in some of these values? Note that not all of these will be immediately *used*, but I'm figuring that it makes sense to add these in a more structured fashion rather than just finding them when needed (for instance, the maximum length of a table alias is already used in the code).

Thanks in advance!

--Matt Jones

A rough draft of the patch is up:

https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/3452

Feedback is appreciated, especially from users of non-MySQL or SQLite3 DBs, as those were the two I had handy to test against.

Thanks!

--Matt Jones