Date: Sat, 27 Oct 2012 11:43:06 -0700 (PDT) From: Jan Bromberger <jan.bromber...@gmail.com> To: thinking-sphinx@googlegroups.com Message-Id: <82224cc9-c004-426a-8a2e-f6a74c8bb15b@googlegroups.com> In-Reply-To: <92A86786-CC29-4A13-8BEB-E54B9B80744C@freelancing-gods.com> References: <cc99f8b1-603f-4606-a58e-179fa7eedbe2@googlegroups.com> <92A86786-CC29-4A13-8BEB-E54B9B80744C@freelancing-gods.com> Subject: Re: [ts] Composite primary keys MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_2010_25226506.1351363386387" ------=_Part_2010_25226506.1351363386387 Content-Type: multipart/alternative; boundary="----=_Part_2011_21653188.1351363386388" ------=_Part_2011_21653188.1351363386388 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Hi Pat, seems nobody else on the interwebs is using composite primary keys. For reference and anyone else trying this: I created a new BIGINT UNSIGNED column uiid and computed a 60bit hash from my concatenated primary id columns. See: http://greenash.net.au/thoughts/2010/03/generating-unique-integer-ids-from-strings-in-mysql/ but I had to substring 15 instead of 16 hex digits for it to work. It works fine in rails console, but throws an error when I do the same search over apache/passenger: riddle (1.5.3) lib/riddle/client/response.rb:25:in `next_int' riddle (1.5.3) lib/riddle/client.rb:264:in `block (2 levels) in run' riddle (1.5.3) lib/riddle/client.rb:263:in `each' riddle (1.5.3) lib/riddle/client.rb:263:in `map' riddle (1.5.3) lib/riddle/client.rb:263:in `block in run' riddle (1.5.3) lib/riddle/client.rb:228:in `collect' riddle (1.5.3) lib/riddle/client.rb:228:in `run' riddle (1.5.3) lib/riddle/client.rb:338:in `query' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:429:in `block (2 levels) in populate' activesupport (3.2.3) lib/active_support/notifications.rb:123:in `block in instrument' activesupport (3.2.3) lib/active_support/notifications/instrumenter.rb:20:in `instrument' activesupport (3.2.3) lib/active_support/notifications.rb:123:in `instrument' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:556:in `log' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:565:in `log' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:428:in `block in populate' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:606:in `call' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:606:in `retry_on_stale_index' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:426:in `populate' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:301:in `total_entries' app/views/shops/configure.html.erb:26:in `_app_views_shops_configure_html_erb___2617149341727481671_2501897120' It is fixed by creating an initializer with require 'riddle/2.1.0'. See your post at: https://github.com/pat/riddle/issues/51 I don't understand how this works, as I only see a 2.0.1, but it does work. Thanks for your work and support Pat! Maybe you could see that this extension is loaded automagically. Kind regards Jan Am Freitag, 19. Oktober 2012 09:31:53 UTC+2 schrieb Pat Allan: > > Hi Jan > > At this point, I don't think I'll add the option for a computed document > id - mainly due to the fact you're the first person to require this in the > life of TS (at least, I can't remember anyone else making this request). > > What could be a viable workaround though is to use a single integer column > in your model that'll be unique for each record in that model, and tell > Sphinx to use that as its primary key with this line in your model: > > set_sphinx_primary_key :unique_id_column > > Cheers > > -- > Pat > > On 12/10/2012, at 12:34 AM, Jan Bromberger wrote: > > > Hi Pat, > > > > when using thinking sphinx with the composite primary keys gem, one gets > an exception like this when defining an index: > > > > NoMethodError (undefined method `to_sym' for [:source, > :source_id]:CompositePrimaryKeys::CompositeKeys) > > > > I realize that sphinx needs to have an unsigned int as a primary key, > but as long as one can compute one with SQL, it's okay. Could you provide a > configuration option to compute the id? In my case, I have a finite set of > sources - currently three -, so that (source_id * 3) + source would > generate an unsigned unique id. > > > > The options would more or less directly be inserted into the sql_query > and sql_query_info options of the generated configuration file. > > > > Without going into detail about my case, it would not be feasible for me > to migrate to a single column primary key. > > > > Thanks Jan > > > > -- > > You received this message because you are subscribed to the Google > Groups "Thinking Sphinx" group. > > To view this discussion on the web visit > https://groups.google.com/d/msg/thinking-sphinx/-/nfoGaH9JTOYJ. > > To post to this group, send email to thinkin...@googlegroups.com<javascript:> > . > > To unsubscribe from this group, send email to > thinking-sphi...@googlegroups.com <javascript:>. > > For more options, visit this group at > http://groups.google.com/group/thinking-sphinx?hl=en. > > > > ------=_Part_2011_21653188.1351363386388 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi Pat,<div><br></div><div>seems nobody else on the interwebs is using comp= osite primary keys. For reference and anyone else trying this:</div><div><b= r></div><div>I created a new BIGINT UNSIGNED column uiid and computed a 60b= it hash from my concatenated primary id columns. See: http://greenash.= net.au/thoughts/2010/03/generating-unique-integer-ids-from-strings-in-mysql= / but I had to substring 15 instead of 16 hex digits for it to work.</div><= div><br></div><div>It works fine in rails console, but throws an error when= I do the same search over apache/passenger:</div><div><br></div><div><pre = style=3D"background-color: rgb(238, 238, 238); padding: 10px; font-size: 11= px; white-space: pre-wrap; color: rgb(51, 51, 51); line-height: 18px; "><co= de>riddle (1.5.3) lib/riddle/client/response.rb:25:in `next_int' riddle (1.5.3) lib/riddle/client.rb:264:in `block (2 levels) in run' riddle (1.5.3) lib/riddle/client.rb:263:in `each' riddle (1.5.3) lib/riddle/client.rb:263:in `map' riddle (1.5.3) lib/riddle/client.rb:263:in `block in run' riddle (1.5.3) lib/riddle/client.rb:228:in `collect' riddle (1.5.3) lib/riddle/client.rb:228:in `run' riddle (1.5.3) lib/riddle/client.rb:338:in `query' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:429:in `block (2 lev= els) in populate' activesupport (3.2.3) lib/active_support/notifications.rb:123:in `block in = instrument' activesupport (3.2.3) lib/active_support/notifications/instrumenter.rb:20:i= n `instrument' activesupport (3.2.3) lib/active_support/notifications.rb:123:in `instrumen= t' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:556:in `log' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:565:in `log' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:428:in `block in pop= ulate' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:606:in `call' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:606:in `retry_on_sta= le_index' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:426:in `populate' thinking-sphinx (2.0.13) lib/thinking_sphinx/search.rb:301:in `total_entrie= s' app/views/shops/configure.html.erb:26:in `_app_views_shops_configure_html_e= rb___2617149341727481671_2501897120'</code></pre></div><div><br></div><div>= It is fixed by creating an initializer with require 'riddle/2.1.0'.</d= iv><div>See your post at: https://github.com/pat/riddle/issues/51 </di= v><div>I don't understand how this works, as I only see a 2.0.1, but it doe= s work.</div><div><br></div><div>Thanks for your work and support Pat! Mayb= e you could see that this extension is loaded automagically.</div><div><br>= </div><div>Kind regards</div><div>Jan</div><div><br><br>Am Freitag, 19. Okt= ober 2012 09:31:53 UTC+2 schrieb Pat Allan:<blockquote class=3D"gmail_quote= " style=3D"margin: 0;margin-left: 0.8ex;border-left: 1px #ccc solid;padding= -left: 1ex;">Hi Jan<p>At this point, I don't think I'll add the option for = a computed document id - mainly due to the fact you're the first person to = require this in the life of TS (at least, I can't remember anyone else maki= ng this request).</p><p>What could be a viable workaround though is to use = a single integer column in your model that'll be unique for each record in = that model, and tell Sphinx to use that as its primary key with this line i= n your model:</p><p> set_sphinx_primary_key :unique_id_column</p><p>C= heers</p><p>-- <br>Pat</p><p>On 12/10/2012, at 12:34 AM, Jan Bromberger wro= te:</p><p>> Hi Pat,<br>> <br>> when using thinking sphinx with the= composite primary keys gem, one gets an exception like this when defining = an index:<br>> <br>> NoMethodError (undefined method `to_sym' for [:s= ource, :source_id]:<wbr>CompositePrimaryKeys::<wbr>CompositeKeys)<br>> <= br>> I realize that sphinx needs to have an unsigned int as a primary ke= y, but as long as one can compute one with SQL, it's okay. Could you provid= e a configuration option to compute the id? In my case, I have a finite set= of sources - currently three -, so that (source_id * 3) + source would gen= erate an unsigned unique id.<br>> <br>> The options would more or les= s directly be inserted into the sql_query and sql_query_info options of the= generated configuration file.<br>> <br>> Without going into detail a= bout my case, it would not be feasible for me to migrate to a single column= primary key.<br>> <br>> Thanks Jan<br>> <br>> -- <br>> You = received this message because you are subscribed to the Google Groups "Thin= king Sphinx" group.<br>> To view this discussion on the web visit <a hre= f=3D"https://groups.google.com/d/msg/thinking-sphinx/-/nfoGaH9JTOYJ" target= =3D"_blank">https://groups.google.com/d/<wbr>msg/thinking-sphinx/-/<wbr>nfo= GaH9JTOYJ</a>.<br>> To post to this group, send email to <a href=3D"java= script:" target=3D"_blank" gdf-obfuscated-mailto=3D"AGf7QJIah70J">thinkin..= .@googlegroups.<wbr>com</a>.<br>> To unsubscribe from this group, send e= mail to <a href=3D"javascript:" target=3D"_blank" gdf-obfuscated-mailto=3D"= AGf7QJIah70J">thinking-sphi...@<wbr>googlegroups.com</a>.<br>> For more = options, visit this group at <a href=3D"http://groups.google.com/group/thin= king-sphinx?hl=3Den" target=3D"_blank">http://groups.google.com/<wbr>group/= thinking-sphinx?hl=3Den</a>.</p><p> <br> <br></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p></blockquote></div= > ------=_Part_2011_21653188.1351363386388-- ------=_Part_2010_25226506.1351363386387--
Message from discussion Composite primary keys
| Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy |
| ©2013 Google |