Posted by davidwhthomas on March 5, 2008 at 9:03pm
| Project: | Faceted Search |
| Version: | 6.x-1.0-beta1 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs work |
Issue Summary
It's nice to see you've added some postgres support during install by creating some of the tables, however this interesting module is still unfortunately unusable on postgres with many sql errors.
For example, installing the taxonomy facet module results in errors as does installing the base module and the ui module when saving an environment and when performing a search.
Are there any plans to make it PostgreSQL compatible?
DT
Comments
#1
Unfortunately PostgreSQL support has not been tested yet. There is no plan at the moment, but patches or other forms of incentives are welcome. ;)
#2
Here are a few specific things that would have to be fixed:
GROUP BY n.nid ASC. This is not supported by Postgres and it's probably not ANSI compliant.The problem is that whenever you use a GROUP BY, all columns you refer to in the SELECT or ORDER BY must also be in the GROUP BY statement (or be used in an aggregate function rather than being referred to directly). The reason for that is that you would otherwise get arbitrary results from the query.
More info: #150684: Postgres problem... and fix!
The last two are possibly kind of large issues to fix.
#3
First of all... thanks for building this module David.
I can help you with PostgreSQL support for your module (for qualifications see MAINTAINERS.TXT in D5/D6 core).
I've done some small changes to the code and got it working on PostgreSQL (for some of the functionality). At this stage it's only the search results stuff and i'm using str_replace() at this point. Thus it isn't patch worthy. I also need to check a couple things for MySQL before rolling the patch.
The changes were what zoo33 wrote above and also revolving around your use of:
CREATE TEMPORARY TABLE <tablename> SELECT....PostgreSQL supports:
SELECT <fields> INTO TEMPORARY TABLE <tablename> FROM...Hopefully we can use the latter in MySQL.
I'll take a look into this later today and try to get a patch rolled for you. Are you planning to get PostgreSQL support in before you release version 1?
#4
PostgreSQL support would be great and patches are very much welcome!
A 1.0 release could happen with or without PostgreSQL, but I'd certainly prefer with it. :-)
#5
Here's the patch I put together before in case someone else has more time than I have. It was only tested with node type facets.
The patch just removes the pieces of the SQL that break on PostgreSQL. You can see what needs to be done in order for it to work.
#6
This patch seems to replace MySQL-specific with PostgreSQL-specific code... We'll need code that works with both. :-)
#7
Here's a patch I rolled for my own use. I think it should retain compatibility with MySQL as well.
In faceted_search.inc I've taken the index definition out of the "CREATE TEMPORARY TABLE" and added it in in another statement. This allows us to specify none of the columns and PostgreSQL will take them all from the query. It does not support specifying part of the table and getting the rest from the SELECT statement like MySQL.
Also in faceted_search.inc I've removed the order clause from the GROUP BY field. I really have no idea what this is supposed to do but I can't imagine it is necessary. Anyone?
In taxonomy_facets.module I fully qualify group by fields and add missing ones in two places. I also add an implicit cast where text and integer values are compared.
#8
Problem with case sensitive search solved (partially).
To make faceted_search to search case insensitive when using PostgeSQL, it is necessary to change
in file "faceted_search.inc" this line (about 1121):
$words_where[] = '('. substr(str_repeat("{$type}_search_index.word = '%s' OR ", count($type_words)), 0, -4) .") AND {$type}_search_index.type = '%s'";with:
$words_where[] = '('. substr(str_repeat("{$type}_search_index.word ILIKE '%s' OR ", count($type_words)), 0, -4) .") AND {$type}_search_index.type = '%s'";add for searching parts of the words -
$words_where[] = '('. substr(str_repeat("{$type}_search_index.word ILIKE '%%%s%%' OR ", count($type_words)), 0, -4) .") AND {$type}_search_index.type = '%s'";The user can of course to write own hook_form, for choosing the type of search.
The problem is in this case, the searched word (or part of word) is no highlighted.
I'll try to find how to solve this problem, but I will appreciate any ideas.
#9
Marked #722726: Is this postgres support for this module? as duplicate.