Storing/querying binary sequences and searching with masks

Question

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, :limit => 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.

Answer 1

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

Answer 2

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)