New predicates: bitwise operators AND, OR and XOR

[JensDeJong]

There was no other way to use bitwise operators on WHERE and HAVING without modifying the library.

[immoh]

Bitwise operators are not predicates i.e. not returning boolean value. Can you provide a real use case for this?

You can always use raw to add any sql to your statement.

[JensDeJong]

They are indeed not returning a boolean value, so I'm also not sure about the usefulness in the case of the OR and XOR either. In the case of AND though: if the resulting value is a zero, the matching row will not be returned.

So the real use case (which I needed) lies in using enumerated types with bit flags (for those that come from a C++ or Objective-C background will surely know). Take for example: Monday = 1 Tuesday = 2 Wednesday = 4 Thursday = 8 Etc... Now you can make combinations of days in the week by just adding them up. Next, you can query with the bitwise AND operator for specific or combinations of days in the week.

It is quite limiting that it's only useful when the result is 0. The code might need some modification to make it less limiting and actually return a boolean.

As for using raw: ah, I didn't know you could also use raw instead of a key in a where function (I discovered just now). I was struggling with the value, in which case it's not possible since it automatically adds an = to the SQL-query.

[immoh]

So you want to generate SQL like this?

select * from table where field & 3;

Which db vendor allows this SQL? I checked with Postgres and it requires the where condition to be a boolean expression so I would need to write it like this:

select * from table where field & 3 != 0;

As far as I know there's no way to write to this with Korma without resorting to raw. Note that this is a larger problem and applies to all operators, not just bitwise ones. The generic solution for this could be something similar to sqlfn, maybe sqlop which would allow you to write

(select :table (where (not= (sqlop "&" :field 3) 0)))

Re: using raw - you can always write the whole condition with raw but you have to do quoting by yourself:

(sql-only (select :table (where (raw "\"field\" & 2"))))

[JensDeJong]

Thanks for the information.

I was using the first query you proposed on a MySQL-database, on which it worked.

All things considered, it's best to reject this pull request.