Storing/querying binary sequences and searching with masks

I've found a good way to store some data in the database with a binary sequence, like 0b0101000 and find rows that give a positive result after applying a mask.

For exemple : SELECT (0b0101010 & (1<<3 | 1<<5))>0; allows me to get rows with the 3rd or 5th bit on, no matter if the others bits are on or off.

The problem is when I want to do this with ActiveRecord. This migration add_column :table, :column, :binary, :l开发者_Python百科imit => 8.bytes creates in fact a TINYBLOBcolumn and not a BINARY or VARBINARY and I can't apply my mask to its value because it is not considered a binary value.

I know that I could make the right column format in the migration by executing a raw SQL statement and then query my table with raw SQL segments for this part, but it doesn't seems like "the Rails Way".

Thanks for any idea.

In fact, it's not optimal, but at least it works to store this sequence in the TINYBLOB column.

I can query the database like this

SELECT * FROM table WHERE (column & mask) = mask

For example, with a value in the column of 10110110 and a mask with 128 (100000000) the row is selected.

But I had to build the conditions part of the query with a string ; no has-based conditions, and no placeholder.

Here is a full (dummy) exemple, in Ruby :

find_conditions = []

find_conditions[0] = 'string_col = ?'
find_conditions << 'a_value_for_the_string_col'

binary_mask = "01100101"
find_conditions[0] += ' AND '
find_conditions << "(bin_col & #{binary_mask.to_i(2)}) = #{binary_mask.to_i(2)}"

results = Model.all(:conditions => find_conditions)

you cant via activerecord:

http://www.packtpub.com/article/Working-with-Rails-ActiveRecord-Migrations-Models-Scaffolding-and-Database-Completion

see in the table :

Migration column type | Converts to MySQL field type | Available options1

:binary | TINYBLOB, BLOB, MEDIUMBLOB, or LONGBLOB2 | limit => 1 to 4294967296 (default = 65536)2