Hi Tiago With Thinking Sphinx v3, you can do the following within an index definition:
set_property :sql_query_pre => ['CUSTOM SQL'] Keep in mind it must be an array of strings, even if there's only one string. If you put a loop around the index definition, then you could have this working easily enough without any need for patches. And if you've not given TS v3 a go yet, make sure you read these first: https://groups.google.com/d/msg/thinking-sphinx/QM0BlS3gg3k/s61pfCBBTUoJ https://github.com/pat/thinking-sphinx/blob/edge/README.textile Cheers -- Pat On 06/01/2013, at 1:21 AM, Tiago wrote: > Did you have any success on this? > > I'm trying to build a gem to help this process, but I've a doubt. > Is there already a way to define a sql_query_pre per index basis? > > I'm trying to create a new 'schema' method in the index definition, so I > could use like: > > define_index do > schema "tenantX" > ... > end > > And this 'shema' would append the new schema search path to the sql_query_pre. > This way would be possible to define an index for each tenant/schema. > > On Thursday, December 6, 2012 3:17:42 PM UTC-2, Tair Assimov wrote: > Oh, I almost forgot about this discussion and did not have notifications > enabled. Pat, thanks for your hints. I will try the second option suggested. > If I manage to find an elegant solution, I will submit a pull request. I > think this is quite common use case when dealing with postgresql schemas. > > Cheers. > > On Monday, October 29, 2012 2:08:47 PM UTC+2, Pat Allan wrote: > Hi Tair > You could specify an index per tenant: > > Tenant.each do |tenant| > define_index "#{class.name}_#{tenant.id}" do > # ... > end > end > > While this provides a index and source per model per tenant, it doesn't take > care of the sql_query_pre value. You could manually add that yourself and > then use the ts:reindex task (which doesn't overwrite the configuration), but > that's not particularly elegant. > > Another option - mind you, this isn't elegant either, but could keep things > far easier over time - is to override the generate method in > ThinkingSphinx::Configuration. Keep the existing implementation, but also > traverse through each source in the configuration tree and add the additional > sql_query_pre statement in. Here's the current method for reference: > https://github.com/pat/thinking-sphinx/blob/master/lib/thinking_sphinx/configuration.rb#L156 > > Have a look at the enforce_common_attribute_type method, which could be used > as a starting point: > https://github.com/pat/thinking-sphinx/blob/master/lib/thinking_sphinx/configuration.rb#L329 > > Hope this helps! > > -- > Pat > > On 28/10/2012, at 1:09 AM, Tair Assimov wrote: > > > Hi all. I bumped into the same problem when creating multi-tenant Rails > > application with PostgreSQL schemas. I tried what Pat suggested and added: > > > > sql_query_pre = SET search_path TO 2,public > > > > Where, 2 is the ID of my tenant. However, when I ran rake ts:in, Thinking > > Sphinx has rewritten configuration file and indexed my default (public) > > schema instead. > > > > Anyway, I do not think its the correct approach to update the > > Rails.env.sphinx.conf, since there are many tenants and you need some kind > > of rotation to index each tenants data. I think the easier is to create a > > custom script/Rake task/whatever, which will loop through all your tenants > > and somehow add the schema selection to the configuration file, and then > > finally use indexer directly: > > > > indexer --all --rotate -c PATH_TO_CONF > > > > However, in this case one tenants data will overwrite others, unless we > > also set the location of the index, which needs more tweaking on the Rails > > side to use the correct one. > > > > Pat, what is the best approach to tackle this problem? I think ideally I > > would want Sphinx to index all my schemas. Or in the worst case to have > > per-tenant sphinx configuration files with own index store and schema > > selection. Is there any way Thinking Sphinx can assist? Is there anything > > similar on the roadmap? Or am I missing something completely? > > > > Thanks all! > > > > > > > > > > > > On Saturday, January 22, 2011 2:02:41 PM UTC+2, Pat Allan wrote: > > Hi Praveen > > Can you select a schema via a SQL statement? If so, you could add a line in > > your sources something like the following: > > sql_query_pre = SQL STATEMENT TO CHOOSE A SCHEMA > > > > You will find existing sql_query_pre lines in your sources - you don't need > > to replace these, Sphinx will accept as many as you put in. Unfortunately, > > there's no way to automatically set this via Thinking Sphinx at the moment, > > though. > > > > -- > > Pat > > > > On 21/01/2011, at 6:16 PM, praveen wrote: > > > > > Thanks Pat. > > > > > > We can create schemas in a postgresql database to group our tables in > > > that database. By default postgresql has a schema "public" which is > > > set by default and contains all the tables created in the database. > > > > > > So , for my multitenant application , i try to create different > > > schamas in a database for different tenants created. So when a user > > > requests for a tenant , the application set the schema to the > > > particular tenant. so the data for the tenant is pulled from the > > > tables in that particular schema. So when i use sphinx to indes my > > > database tables, the indexer indexes only the public schema and i have > > > no options to specify which schema to be indexed or the sphinx itself > > > does not have the feature to select all the schemas available and > > > index them , which i can filter in my application to show search > > > results depending on from which tenant is the the search requested. > > > > > > Can you tell if there is any option to atleast specify the schema > > > along with the host, usr,pass,db details in the config file?? > > > > > > On Jan 21, 4:53 am, Pat Allan <[email protected]> wrote: > > >> Hi Praveen > > >> > > >> When you talk about different schemas, do you mean different databases, > > >> each one for a different tenant? If so, there's no simple way to do this > > >> with Thinking Sphinx. You may need to create your own script to modify > > >> the generated configuration file, or run separate searchd instances for > > >> each tenant and change the port and/or address depending on the tenant. > > >> > > >> Or do you mean something else when you say schema? I'm not sure. > > >> > > >> Cheers > > >> > > >> -- > > >> Pat > > >> > > >> On 20/01/2011, at 7:28 PM, praveen wrote: > > >> > > >>> Hi Everyone, > > >> > > >>> I would like to know , how can i use thinking sphinx/sphinx to index > > >>> all the schemas in a postgres database. > > >> > > >>> I am using Postgresql Database for my Multitenant application, where i > > >>> differentiate each tenant by Postgresql shema , which means , I > > >>> connect to the particular schema , when the user requests for the > > >>> particular tenant. > > >> > > >>> To explain in detail, I have one rails application which will connect > > >>> to different schema in my postgresql database according to the tenant > > >>> id requested, to behave as a multitenant application. > > >> > > >>> Since i have only one application , and i have to use the same > > >>> thinking sphinx plugin for indexing, i am not able configure to index > > >>> tables from all the schemas i have in my postgresql database. > > >> > > >>> Please help. Thanks in advance. > > >> > > >>> -- > > >>> You received this message because you are subscribed to the Google > > >>> Groups "Thinking Sphinx" group. > > >>> To post to this group, send email to [email protected]. > > >>> To unsubscribe from this group, send email to > > >>> [email protected]. > > >>> For more options, visit this group > > >>> athttp://groups.google.com/group/thinking-sphinx?hl=en. > > > > > > -- > > > You received this message because you are subscribed to the Google Groups > > > "Thinking Sphinx" group. > > > To post to this group, send email to [email protected]. > > > To unsubscribe from this group, send email to > > > [email protected]. > > > For more options, visit this group at > > > http://groups.google.com/group/thinking-sphinx?hl=en. > > > > > > > > > > > > > > > > > > > > > -- > > 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/-/zIVsCnFwZNQJ. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group at > > http://groups.google.com/group/thinking-sphinx?hl=en. > > > > > > > > > > > > > -- > 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/-/mRB-fvyrTzIJ. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/thinking-sphinx?hl=en. -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
