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. :) 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. :)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 :)> --Matt Jones > > > > >-- Cheers Koz
On Oct 14, 2009, at 6:30 PM, Michael Koziarski wrote:> >> 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. :) > > 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... :) --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 On Oct 15, 2009, at 1:38 AM, Matt Jones wrote:> > > On Oct 14, 2009, at 6:30 PM, Michael Koziarski wrote: > >> >>> 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. :) >> >> 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... :) > > --Matt Jones > > > >
On Oct 15, 2009, at 1:38 AM, Matt Jones wrote:> > On Oct 14, 2009, at 6:30 PM, Michael Koziarski wrote: > >> >>> 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. >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
On Oct 14, 2009, at 6:30 PM, Michael Koziarski wrote:> >> 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. :) > > 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 :) >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