On 24/02/09 20:41, Luke Kanies wrote: > On Feb 24, 2009, at 11:10 AM, Brice Figureau wrote: > >> On 24/02/09 9:01, Luke Kanies wrote: >>> On Feb 23, 2009, at 1:52 PM, Brice Figureau wrote: >>>>>> I suggest you add an unique index on (title,restype), and wait for >>>>>> it >>>>>> to fail. >>>>> Hmm, title and restype imho wouldn't be sufficient. It should be >>>>> title,restype,host_id not? >>>> Yes, you're right of course. >>>> >>>> I'm wondering if we shouldn't add this constraint by default in the >>>> schema, it would be a safeguard against such corruption. >>>> >>>> What do other think about this? >>>> On one hand it adds more integrity, on the other hand it will impose >>>> more load on the database (the index will be fairly large). >>> I think this is a good idea. From all of the testing I've done, the >>> time is all spent in ActiveRecord, not in the database, so it pays to >>> have the right model in the database, rather than relying on >>> ActiveRecord or whatever ORM someone uses to get things right. >> Unfortunately it proves to be harder than I first thought because: >> * resources.title is a TEXT column >> * mysql can't create an index over a (complete) TEXT column >> >> However, since MySQL 5.0.3 (an old version), it is possible to: >> * define larger than 255 character VARCHARs >> * use those on index as long as the index is not more than 767 chars >> on innodb or 1000 chars on MyISAM. >> >> Since any version back to 3.23 it is possible to have index on TEXT >> columns as long as we use a prefix length (50 for the current index). >> >> That means, either we had specific code for MySQL (and even specific >> version) or we get rid of the idea of such index. >> >> Maybe I'm raising warnings for nothing as the longest title length I >> have in my environment is 109 characters. >> >> I'd be interested if other people could send me their longest title >> length, which can be computed by: >> SELECT MAX(LENGTH(title)) FROM resources; >> >> Something along the line of: >> CREATE UNIQUE INDEX tth_idx ON resources(title(255),restype,host_id); >> should work on most platform I think (at least it works on mine). > > I can tell you that I switched it from VARCHAR to TEXT because people > were having errors because their titles were too long. > > Thus, I don't think it'd be ok to switch back to varchar.
I wasn't really proposing to move to 255 chars titles, just that if we want to have this feature on MySQL the index has to use a prefix length for the title. We have to make sure every title is unique among this length. I proposed 255, but MySQL can go as far as 767. Still there is a risk of non-unicity on the first 767 characters of title. In the light of this, I don't think it is worth going that direction. Knowledgeable DBA can have this index by themselves if they care (that could be something we had to the wiki storedconfig page). Or we add the index only on non-MySQL engines... Choose your poison :-) -- Brice Figureau Days of Wonder http://www.daysofwonder.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Puppet Developers" group. To post to this group, send email to puppet-dev@googlegroups.com To unsubscribe from this group, send email to puppet-dev+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/puppet-dev?hl=en -~----------~----~----~----~------~----~------~--~---
