Re: [sqlite] How do bitwise operators work? - Example of schema and request

[Igor Tandetnik]

Le Hyaric Bruno

wrote:
> My wish is to be able to select an item like that :
>
> $SELECT id FROM item WHERE bits & 1<<'X';
> (where X is a random value from 0 to 20...)

If you go custom function route for this, you may benefit from 
incremental blob API - see

http://sqlite.org/c3ref/blob_open.html
http://sqlite.org/c3ref/blob_read.html

This would allow you to check individual bits (well, bytes) without 
having to read the whole blob into memory.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do bitwise operators work? - Example of schema and request

[Le Hyaric Bruno]

>Can you provide examples of your schema, data, and the types of queries
> you want to run? This would make it easier to offer suggestions.
>
> Rich
Of course Richard,

In my test I was simply doind something like that :

$sqlite3.exe test.db
$CREATE TABLE item(id INTEGER PRIMARY KEY AUTOINCREMENT, bits BLOB);
$INSERT INTO item(id,bits) VALUES (NULL, RANDOMBLOB(25000));
$INSERT INTO item(id,bits) VALUES (NULL, RANDOMBLOB(25000));
$INSERT INTO item(id,bits) VALUES (NULL, RANDOMBLOB(25000));
 ... 

My wish is to be able to select an item like that :

$SELECT id FROM item WHERE bits & 1<<'X';
(where X is a random value from 0 to 20...)


I know this is a bit tricky and not a good practice in Database,
but we have a such amount of data, we need to pack it in bit arrays,
and we need to keep the solution very simple... so sqlite should be a good 
candidate.


> You cannot provide your own operations, but you can provide your own 
> functions:
>
> http://sqlite.org/c3ref/create_function.html
>
> Then you can write something like
>
> select blob_OR(blob1, blob2) from mytable;
>
> where blob_OR is your custom function.
>
> Igor Tandetnik 

Thanks, Igor, I will take a look on that.


Bruno.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users