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.
