smartfile / django-sphinx-db Goto Github PK

I'm using django-shpinx-db to index companies. I have a Company model which queries a PostgreSQL database as well as a CompanyIndex model.

To use the Company model as the id field of the Sphinx index, I used the following definition:

Company = models.ForeignKey(Company, db_column='id', primary_key=True)

This seems to work. I can create a CompanyIndex instance using a Company object and I can query the Company_id attribute.

>>> CompanyIndex.objects.create(Company=mycompany, name='my company', description='hello thar')
<CompanyIndex: CompanyIndex object>
>>> ci = CompanyIndex.objects.all()[0]
>>> ci.Company_id
1L

But querying the Company object directly does not work.

>>> ci.Company
...
DatabaseError: (1064, "sphinxql: syntax error, unexpected CONST_FLOAT, expecting BETWEEN (or 8 other tokens) near '.id = 1 '")

The problem seems to be that the wrong column name quoting is used. This is the executed query (as shown by using manage.py shell_plus --print-sql):

SELECT `id`, `customer_id`, `company_name`, ... FROM front_company WHERE front_company.id = 1 

In this query, backtick quoting is used. Postgres uses double quotes though.

Is this a bug in the router or in the database backend? Or do you know where else I can find the solution?

Because the MySQL backend is used as a base, for DateTime and Time columns the data is formatted in the MySQL way: "'2012-07-20 14:21:28'". However, the timestamp column type in Sphinx expects a unix timestamp: 1342814107.5690041. I know database backends in Django can do conversions to/from python objects, so we need to figure out how to make the Sphinx backend treat dates/times as Sphinx expects them.

q = EntryIndex.objects.filter(content__search='title')

In [42]:      q.model
Out[42]:    nfeeder.models.EntryIndex

In [43]: print q.query
SELECT `nfeeder_entryindex`.`id`, `nfeeder_entryindex`.`content`, `nfeeder_entryindex`.`date` 
FROM `nfeeder_entryindex` WHERE MATCH (`nfeeder_entryindex`.`content`) AGAINST
 (title IN BOOLEAN MODE);

I am trying to search the index with the orm. It works fine from the mysql command line with:

select * from nfeeder_entryindex where MATCH('title');

I am not sure if I am doing something wrong.

Project hasn't been updated lately not sure if it's me or it just isn't supported.

sphinx_model.objects.filter(name__search='foo')

fails with:
sql_for_columns() takes exactly 4 arguments (5 given)

Problem

When filtering by a specific column, the column is ignored.

>>> CompanyIndex.objects.filter(company_name__search='my company')
SELECT `id`, `customer_id` FROM companies_rt WHERE MATCH ('@* my company') LIMIT 21

In this case, the correct SphinxQL query would be:

SELECT `id`, `customer_id` FROM companies_rt WHERE MATCH ('@company_name my company') LIMIT 21

This default @* at the beginning of the query causes problems when using the @-Syntax in the filtering query itself.

>>> CompanyIndex.objects.filter(company_name__search='@company_name my company')
SELECT `id`, `customer_id` FROM companies_rt WHERE MATCH ('@* @company_name my company') LIMIT 21

This fails of course.

Suggestion

I'd suggest that the standard query filtering accepts only basic syntax, no field names. It should also limit the filtering to the specified column name.

>>> CompanyIndex.objects.filter(company_name__search='my company')
SELECT `id`, `customer_id` FROM companies_rt WHERE MATCH ('@company_name my company') LIMIT 21

Additionally, you could add an unfiltered matching function, e.g.

>>> CompanyIndex.objects.match('@* software @city new york')

I guess this could be done by adding the function to the custom manager, but I'm not 100% sure about that.

sphinx again has issues with the sql statement.

In [19]: EntryIndex.objects.all().order_by('-date')

errors:

DatabaseError: (1064, "sphinxql: syntax error, unexpected CONST_FLOAT, expecting $end near '.`date` A
SC LIMIT 21'")

i debugged the query sent to sphinx a bit:

In [27]: d.queries[0]['sql']

Out[27]: 'SELECT `id`, `date` FROM nfeeder_entryindex ORDER BY nfeeder_entryindex.`date` ASC LIMIT 21'

This doesn't work:

mysql> select `id`, `date` from app_modelindex order by app_MODELindex.date ASC LIMIT 2000;

These do however:

mysql> select `id`, `date` from app_modelindex order by date ASC LIMIT 2000;

mysql> select `id`, `date` from app_modelindex order `by date` ASC LIMIT 2000;

The default sphinx configuration returns 20 results, it would be nice to be able to configure that from django methods on the fly.

something like:

MyIndex.objects.filter(content__serach, limit=40)

I was thinking something similar for the offset functionality, but I don't know if the RT indexes support that yet.

https://code.djangoproject.com/browser/django/trunk/django/db/models/sql/compiler.py#L919

Model deletion has an SQL syntax issue i think, I am still working this out more details on this, just tracking the feature.

EntryIndex.objects.all().delete()

DatabaseError: (1064, "sphinxql: syntax error, unexpected IDENT, expecting ID near '`id` IN (20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1)'")

Currently, it's not possible to delete all items in the index.

In [8]: CompanyIndex.objects.all()[:].delete()
SELECT `id`, `customer_id` FROM sphinx_company_rt

DELETE FROM sphinx_company_rt WHERE id IN (22554, 22553, 22552, 22551, 22550, 22549, 22548, 22547, 22546, 22545, 22544, 22543, 22542, 22541, 22540, 22539, 22538, 22537, 22536, 22535)

The first SELECT without a limit returns only 20 entries, so only those 20 entries are removed.

Current trunk version added support for the TRUNCATE INDEX indexname command. That could help.

Maybe it's possible to alter the function of the .delete() statement? Otherwise, could we add a .truncate() option to execute a TRUNCATE INDEX statement?