smartfile / django-sphinx-db Goto Github PK
View Code? Open in Web Editor NEWDjango database backend for SphinxQL.
License: BSD 3-Clause "New" or "Revised" License
Django database backend for SphinxQL.
License: BSD 3-Clause "New" or "Revised" License
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)
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.
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?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.