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 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.
