Thinking Sphinx and MS-SQL via UnixODBC / FreeTDS

[Tonkatsufan]

Hi,

I have a running Rails 2.3.2 application on a Ubuntu box connected to
a SQL-Server 2005 database via UnixODBC/FreeTDS. I set up my Rails'
database.yml accordingly and can read and write to all tables. Of
course, tsql and isql also work on my unix machine, so the connection
to the SQL-Server is working as it should.

Now, I tried adding Thinking Sphinx for fulltext search to my Rails
app. Therefore, I compiled Sphinx 0.9.9-RC2 with unixodbc support.

I set up the model with a define_index like described in the docs and
ran rake ts:in which produced the following error:

(in /var/www/r_tools)
Generating Configuration to /var/www/r_tools/config/
development.sphinx.conf
Sphinx 0.9.9-rc2 (r1785)
Copyright (c) 2001-2009, Andrew Aksyonoff

using config file '/var/www/r_tools/config/development.sphinx.conf'...
FATAL: no indexes found in config file '/var/www/r_tools/config/
development.sphinx.conf'

here is the generated conf file:

indexer
{
}

searchd
{
listen = 127.0.0.1:3312
log = /var/www/r_tools/log/searchd.log
query_log = /var/www/r_tools/log/searchd.query.log
pid_file = /var/www/r_tools/log/searchd.development.pid
}

It seems Thinking Sphinx was not able to set up a configuration for my
scenario. What do I need to do in order to make this work? Apparently,
having a working connection in Rails / ActiveRecord does not mean TS
will also be able to use that connection for configuring. But where
and how do I set up Thinking Sphinx in a way that uses odbc ?

Thank you for any help!
Toni

[Wade Winningham]

I'm pretty sure ThinkingSphinx only works with MySQL and Postgres
right now unless someone's written other adapters.

I've been intending to write one for SQL Server, but ran into an issue
with SQL Server lacking a few functions MySQL has built-in and
Postgres can easily include with stored functions. The main one being
a comparable function for MySQL's GROUP_CONTACT function.

If you'd like to take a stab at writing an adapter for it, I think the
Postgres adapter is actually a bit closer to what you'd need than the
MySQL one. In the gem source for TS just look in the
lib/thinking-sphinx/adapters directory.

-Wade Winningham

[Pat Allan]

Wade's absolutely correct - there's no support for SQL Server in
Thinking Sphinx yet.

If you'd like to submit a patch, that'd be fantastic - and Wade's
suggestion for using the Postgres adapter as a starting point is a
good one. The adapters hold the unique database approaches to SQL
functions.

Cheers

--
Pat

[Tonkatsufan]

Hi Pat and Wade,

okay, thank you for your replies. I fear I am not a good enough coder
to come up with a MS-SQL adapter for Thinking Sphinx...sorry :-(

But, as I am trying to find a workaround for my situation, I thought I
could put the table I want TS to index on a MySQL server, and make a
new connection in database.yml for this MySQL-Server. So I went ahead
and did that. Then, in my model-to-be-indexed, I put
"establish_connection :mysql_conn".

Unfortunately, TS doesn't seem to pick this up, because when trying to
index, I get the
S1000 (0) [unixODBC][FreeTDS][SQL Server]Unable to connect to data
source
error. As there are no relationship defined on this model, I don't
understand why this happens. Is there a better workaround?

Thank you
Toni

On Jul 29, 10:44 am, Pat Allan <p...@freelancing-gods.com> wrote:
> Wade's absolutely correct - there's no support for SQL Server in  
> Thinking Sphinx yet.
>
> If you'd like to submit a patch, that'd be fantastic - and Wade's  
> suggestion for using the Postgres adapter as a starting point is a  
> good one. The adapters hold the unique database approaches to SQL  
> functions.
>
> Cheers
>
> --
> Pat
>
> On 29/07/2009, at 1:30 AM, Wade Winningham wrote:
>
>
>
>
>
> > I'm pretty sure ThinkingSphinx only works with MySQL and Postgres
> > right now unless someone's written other adapters.
>
> > I've been intending to write one for SQL Server, but ran into an issue
> > with SQL Server lacking a few functions MySQL has built-in and
> > Postgres can easily include with stored functions. The main one being
> > a comparable function for MySQL's GROUP_CONTACT function.
>
> > If you'd like to take a stab at writing an adapter for it, I think the
> > Postgres adapter is actually a bit closer to what you'd need than the
> > MySQL one. In the gem source for TS just look in the
> > lib/thinking-sphinx/adapters directory.
>
> > -Wade Winningham
>

> > On Tue, Jul 28, 2009 at 10:48 AM, Tonkatsufan<tonkatsu...@gmail.com>  

[Pat Allan]

Hi Toni

I'm not entirely sure how ActiveRecord works with secondary
databases... do you have the establish_connection call before or after
the define_index block? It should be before.

It might even be worth having a separate subclass of ActiveRecord for
your MySQL-based models, and then inherit from that for the actual
models.

One more thing: even though ActiveRecord may use ODBC drivers, I'm not
sure if Sphinx will, so all this may not work anyway. It's really not
something I know much about, I'm afraid.

Sorry I can't be more help.

--
Pat

[Wade Winningham]

From my brief research, I did determine that Sphinx 0.9.9 has a
'sql-server' and an 'odbc' option for database connectivity. The
'sql-server' one is Windows-only so if your app is running on Linux,
you would have to use 'odbc'.

TS sets the database connection information which is specified in the
production.sphinx.conf file for each table. For example:

source model_core_0
{
type = mysql
sql_host = localhost
sql_user = app_user
sql_pass = the_password
sql_db = app_production
...
}

This is done in the set_source_database_settings method in the TS
source. It looks like it gets the information from the model's config
info, so as long as the model is pointing to the right database, it
looks like Sphinx would get it, too.

[Pat Allan]

The issue with this is that while Sphinx may require 'odbc',
ActiveRecord will requre sql-server (or something similar - I'm not
exactly sure what it wants) - and so this might lead to some issues
getting TS to work with ODBC.

--
Pat