Mix query and string types?

[pbreit]

I'm starting to implement Postgres text search and am not quite sure how to build a complex query. The full text part is a string and then, of course, the other components are Query types. How would I combine them?

Example:

        items = db(("to_tsvector('english', description) @@ to_tsquery('english', '%s')" % q) &

                (db.item.status=='active')).select(db.item.ALL, orderby=orderby)

Errors:

TypeError: unsupported operand type(s) for &: 'str' and 'Query'

Do I have to write out the whole query as a string in SQL?

[Massimo Di Pierro]

Look how web2py handles db.table.field.belongs(list).
You can add a db.table.field.contains_text(....) using a similar text.
You will need something like this in dal.py

class Expression(object):
def contains_text(self, value):
return Query(self.db, self.db._adapter.CONTAINS_TEXT, self,
value) def contains_text(...)

class BaseAdapter(ConnectionPool):
def CONTAINS_TEXT(self,first,second):
raise NotImplementedError

class PostgreSQLAdapter(BaseAdapter):
def CONTAINS_TEXT(self,first,second):
a,b = self.expand(first),second
return "to_tsvector('%s', description) @@ to_tsquery('%s',
'%s')" % (a,a,b)

not sure about the last method. Will need testing.

[pbreit]

I'm taking a closer look at this again. Can I subclass or extend DAL or do I have to edit dal.py directly?

I was trying to do this with executesql and one problem I ran into is that my virtualfields did not seem to be coming through. Is that expected?

[pbreit]

Bump.

So are gluon modules generally extendable or subclassable? Is there any documentation on how to go about it?

[pbreit]

So is it possible to extend or subclass gluon modules? Is there a brief HowTo somewhere?

[Massimo Di Pierro]

Good point... You can subclass them. Problem is this is how they work:

Consider

db.table.field.something()

db.table.field is a Field with Extends expression

"something" is a method of Field or Expression and delegates to

self.table.field.db._adapter.SOMETHING(....)

which performs the action.

So adding a feature is not as easy as extending or subclassing a
class. Most actions involved two or more classes. Implementing a new
feature requires two steps:
- adding an API at the web2py layer (Field, Expression, Rows, etc).
- adding a translation for each supported adapter (they all extend
BaseAdapter).

[pbreit]

Would it make sense for this type of thing to be extendable in a generalized way?

Or I'm wondering if there is or could be a reasonable way to mix Query and String types in a DAL query?

[Massimo Di Pierro]

You can do that already but is clanky. I made a simplification in
trunk:

>>> db=DAL()
>>> db.define_table('t',Field('f'))
>>> print db(db.t)('t.f<15')._select('max(t.f)')

SELECT max(t.f) FROM t WHERE ((t.f>0) AND (t.f<15));

you still need a first db(db.table) or db(db.table.field) to determine
which table is needed. In the query you can merge queries and string.
same for fields.

[pbreit]

Cool, I'll try it out, thanks.

[pbreit]

I presume that I will be responsible for SQL injection protection, etc?